Wednesday, May 8, 2013

Goldengate Implementation Automated - I

THis is part of First script to Install DDL/SEQUENCES and Create HeartBeat Tables.

It also create GoldenGate parameter file for Hearbeat tables and Update GLOBALS file for GG schema.

Parden my editing because HTML does not like ">" .

###########################################################################
#!/bin/ksh


######################################

###functions.

checksid ()

{

ORACLE_SID=""

ORACLE_HOME=""

TNS_ADMIN=""

cat /etc/oratab
cut -d ":" -f1

#echo "SID must be in oratab file"

echo "Enter ORACLE_SID of Database from Above:\c "

read ORACLE_SID

while :

do

if [[ $( grep -wc "$ORACLE_SID" /etc/oratab ) -eq 1 ]]

then

echo "ORACLE SID Found"

echo "$ORACLE_SID is instance"

ORACLE_HOME=`grep -w $ORACLE_SID /etc/oratab
cut -d ":" -f2`

TNS_ADMIN=$ORACLE_HOME/network/admin

PATH=$ORACLE_HOME/bin:$PATH

GGHOME=$PWD

echo "Current Oracle Instance :-------------: $ORACLE_SID"

echo "Current Oracle Home :-----------------: $ORACLE_HOME"

echo "Current TNS admin :-------------------: $TNS_ADMIN"

echo "Current Goldengate Home :-------------: $GGHOME"

break;

#exit 1

else

echo "Enter valid ORACLE_SID or press ctl+c to cancel script:\c"

read ORACLE_SID

continue;

fi

done

}

###############################################################



checksid

echo "Enter Goldengate Home directory:\c"

read GGHOME

echo "$GGHOME"

echo "----Create Install directory-----"

mkdir -p $GGHOME/admin/etc

mkdir -p $GGHOME/admin/logs

mkdir -p $GGHOME/admin/scripts

mkdir -p $GGHOME/dirprm/parameterfiles

mkdir -p $GGHOME/admin/install

echo "----copy sqls to Install dir-----"

cp $GGHOME/*.sql $GGHOME/admin/install

export GGINSTALL=$GGHOME/admin/install

echo $GGINSTALL

echo "------start processing ----------"

for i in $GGINSTALL/ddl_remove.sql $GGINSTALL/marker_setup.sql $GGINSTALL/ddl_setup.sql

do

echo "----- $i file Processing Now --------"

sed -e '/new_value gg_user/d' $i > $GGINSTALL/temp1.sql

sed -e '/accept/d' $GGINSTALL/temp1.sql > $GGINSTALL/temp2.sql

mv $GGINSTALL/temp2.sql $i

done

echo "verify files"

echo "---------File processing is Done -------"

echo ""

echo ""

echo "----------------------------------------"

echo "Enter Goldengate Admin User for DDL and sequence Installation :\c"

read GOLDENGATE_ADMIN_USER

echo $GOLDENGATE_ADMIN_USER

echo "DISABLE DDL and remove if any Existing Installation"

$ORACLE_HOME/bin/sqlplus -s / as sysdba <
PROMPT "=========================DISABLE DDL TRIGGER =========================================="

@$GGINSTALL/ddl_disable.sql

PROMPT "===========================REMOVING DDL ==============================================="

@$GGINSTALL/ddl_remove.sql $GOLDENGATE_ADMIN_USER

EOF

echo "Now Installing Sequence and DDL"

$ORACLE_HOME/bin/sqlplus -s / as sysdba <
PROMPT "============================INSTALL SEQUENCE==========================================="

@$GGINSTALL/sequence.sql $GOLDENGATE_ADMIN_USER

PROMPT "============================MARKER SCRIPT=============================================="

@$GGINSTALL/marker_setup.sql $GOLDENGATE_ADMIN_USER

PROMPT "============================INSTALL DDL================================================"

@$GGINSTALL/ddl_setup.sql $GOLDENGATE_ADMIN_USER

PROMPT "============================ENABLE DDL================================================="

@$GGINSTALL/ddl_enable.sql

PROMPT "============================RUN DDL PIN ==============================================="

@$GGINSTALL/ddl_pin.sql $GOLDENGATE_ADMIN_USER

EOF

echo "-----------DDL and sequence installation completed for user $GOLDENGATE_ADMIN_USER--------"

echo ""

echo ""

sleep 2

echo "--------------------creating Heartbeat Table for $GOLDENGATE_ADMIN_USER-------------------"

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF

spool GG_HEARTBEAT_SETUP.log

set VERIFY OFF;

drop table "$GOLDENGATE_ADMIN_USER".heartbeat;

-- Create table statement

prompt "--creating HEARTBEAT TABLE "

CREATE TABLE "$GOLDENGATE_ADMIN_USER".HEARTBEAT

( ID NUMBER ,

SRC_DB VARCHAR2(30),

EXTRACT_NAME varchar2(8),

SOURCE_COMMIT TIMESTAMP,

TARGET_COMMIT TIMESTAMP,

CAPTIME TIMESTAMP,

CAPLAG NUMBER,

PMPTIME TIMESTAMP,

PMPGROUP VARCHAR2(8 BYTE),

PMPLAG NUMBER,

DELTIME TIMESTAMP,

DELGROUP VARCHAR2(8 BYTE),

DELLAG NUMBER,

TOTALLAG NUMBER,

thread number,

update_timestamp timestamp,

EDDLDELTASTATS number,

EDMLDELTASTATS number,

RDDLDELTASTATS number,

RDMLDELTASTATS number,

CONSTRAINT HEARTBEAT_PK PRIMARY KEY (thread) ENABLE

)

/

prompt "--DROPING SEQUENCE "

DROP SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_ID ;

CREATE SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_ID INCREMENT BY 1 START WITH 1 ORDER ;

DROP TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT;

prompt "--Creating GGS_HEARTBEAT TABLE "

CREATE TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT

(ID NUMBER ,

SRC_DB VARCHAR2(30),

EXTRACT_NAME varchar2(8),

SOURCE_COMMIT TIMESTAMP,

TARGET_COMMIT TIMESTAMP,

CAPTIME TIMESTAMP,

CAPLAG NUMBER,

PMPTIME TIMESTAMP,

PMPGROUP VARCHAR2(8 BYTE),

PMPLAG NUMBER,

DELTIME TIMESTAMP,

DELGROUP VARCHAR2(8 BYTE),

DELLAG NUMBER,

TOTALLAG NUMBER,

thread number,

update_timestamp timestamp,

EDDLDELTASTATS number,

EDMLDELTASTATS number,

RDDLDELTASTATS number,

RDMLDELTASTATS number,

CONSTRAINT GGS_HEARTBEAT_PK PRIMARY KEY (DELGROUP) ENABLE

);



prompt "--Creating GGS_HEARTBEAT_TRIG TRIGGER "



CREATE OR REPLACE TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG

BEFORE INSERT OR UPDATE ON "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT

FOR EACH ROW

BEGIN

select seq_ggs_HEARTBEAT_id.nextval

into :NEW.ID

from dual;

select systimestamp

into :NEW.target_COMMIT

from dual;

select trunc(to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT ),1, instr(:NEW.CAPTIME - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.CAPLAG

from dual;

select trunc(to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000

into :NEW.PMPLAG

from dual;

select trunc(to_number(substr((:NEW.DELTIME - :NEW.PMPTIME),1, instr(:NEW.DELTIME - :NEW.PMPTIME,' ')))) * 86400

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+7,2))

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+10,6)) / 1000000

into :NEW.DELLAG

from dual;

select trunc(to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),1, instr(:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.TOTALLAG

from dual;

end ;

/



prompt "--Enable GGS_HEARTBEAT TABLE "

ALTER TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG ENABLE;



--

-- This is for the History heartbeat table

--

prompt "--Creating GGS_HEARTBEAT_HIST sequence "



DROP SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_HIST ;

CREATE SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_HIST INCREMENT BY 1 START WITH 1 ORDER ;

DROP TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_HISTORY;



prompt "--Creating GGS_HEARTBEAT_HIST TABLE "



CREATE TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_HISTORY

( ID NUMBER ,

SRC_DB VARCHAR2(30),

EXTRACT_NAME varchar2(8),

SOURCE_COMMIT TIMESTAMP,

TARGET_COMMIT TIMESTAMP,

CAPTIME TIMESTAMP,

CAPLAG NUMBER,

PMPTIME TIMESTAMP,

PMPGROUP VARCHAR2(8 BYTE),

PMPLAG NUMBER,

DELTIME TIMESTAMP,

DELGROUP VARCHAR2(8 BYTE),

DELLAG NUMBER,

TOTALLAG NUMBER,

thread number,

update_timestamp timestamp,

EDDLDELTASTATS number,

EDMLDELTASTATS number,

RDDLDELTASTATS number,

RDMLDELTASTATS number,

CONSTRAINT GGS_HEARTBEAT_HIST_PK PRIMARY KEY (ID) ENABLE

);



prompt "--Creating GGS_HEARTBEAT_TRIG_HIST Trigger. "



CREATE OR REPLACE TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG_HIST

BEFORE INSERT OR UPDATE ON "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_HISTORY

FOR EACH ROW

BEGIN

select seq_ggs_HEARTBEAT_HIST.nextval

into :NEW.ID

from dual;

select systimestamp

into :NEW.target_COMMIT

from dual;

select trunc(to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT ),1, instr(:NEW.CAPTIME - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.CAPLAG

from dual;

select trunc(to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000

into :NEW.PMPLAG

from dual;

select trunc(to_number(substr((:NEW.DELTIME - :NEW.PMPTIME),1, instr(:NEW.DELTIME - :NEW.PMPTIME,' ')))) * 86400

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+7,2))

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+10,6)) / 1000000

into :NEW.DELLAG

from dual;

select trunc(to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),1, instr(:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.TOTALLAG

from dual;

end ;

/



prompt "--Enable GGS_HEARTBEAT_HIST TABLE"



ALTER TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG_HIST ENABLE;





Prompt "--Setup DBMS_SCHEDULER for Heart Beat"



prompt "--grant select on v_$instance to GOLDENGATE_ADMIN_USER is already given. "

prompt "--grant select on v_$database to GOLDENGATE_ADMIN_USER is already given. "



PROMPT "--CREEATE PROCEDURE TO UPDATE HEARTBEAT TABLE"

CREATE OR REPLACE PROCEDURE "$GOLDENGATE_ADMIN_USER".gg_update_hb_tab IS

v_thread_num NUMBER;

v_db_unique_name VARCHAR2 (128);

BEGIN

SELECT thread#, db_unique_name

INTO v_thread_num, v_db_unique_name

FROM v\$instance, v\$database;

UPDATE "$GOLDENGATE_ADMIN_USER".heartbeat

SET update_timestamp = SYSTIMESTAMP

,src_db = v_db_unique_name

WHERE thread = v_thread_num;

COMMIT;

END;

/

PROMPT "INSERT INTO HEARTBEAT TABLE"

truncate table "$GOLDENGATE_ADMIN_USER".HEARTBEAT;



INSERT INTO "$GOLDENGATE_ADMIN_USER".HEARTBEAT (thread) select thread# from GV\$instance;

commit;



---FOR 10G USE THIS



PROMPT "CREATE GOLDENGATE JOBS"



BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => '"$GOLDENGATE_ADMIN_USER".OGG_HB',

job_type => 'STORED_PROCEDURE',

job_action => '"$GOLDENGATE_ADMIN_USER".GG_UPDATE_HB_TAB',

number_of_arguments => 0,

repeat_interval => 'FREQ=MINUTELY',

job_class => 'SYS.DEFAULT_JOB_CLASS',

start_date => NULL,

end_date => NULL,

enabled => FALSE,

auto_drop => FALSE,

comments => 'GoldenGate'

);

END;

/





BEGIN

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

name => '"$GOLDENGATE_ADMIN_USER"."OGG_HB"',

attribute => 'restartable', value => TRUE);

END;

/



BEGIN

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

name => '"$GOLDENGATE_ADMIN_USER"."OGG_HB"',

attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

END;

/



BEGIN

SYS.DBMS_SCHEDULER.enable(

name => '"$GOLDENGATE_ADMIN_USER"."OGG_HB"');

END;

/



SPOOL OFF;

EOF

echo "-------------------------Hearbeat setup completed --------------------------------"

echo ""

echo ""



echo "----------creating parameterfile for $GOLDENGATE_ADMIN_USER FOR EXTRACT---------"

echo "TABLE GGADMIN.HEARTBEAT," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "TOKENS (" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "CAPGROUP = @GETENV (\"GGENVIRONMENT\", \"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "CAPTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\"))," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "EDDLDELTASTATS = @GETENV (\"DELTASTATS\", \"DDL\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "EDMLDELTASTATS = @GETENV (\"DELTASTATS\", \"DML\")" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo ");" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo ""

echo ""

echo "----EXTRACT Hearbeat mapping parameterfile for $GOLDENGATE_ADMIN_USER is ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm--"

echo ""

echo ""

cat ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "----------creating parameterfile for $GOLDENGATE_ADMIN_USER FOR PUMP---------"

echo "TABLE GGADMIN.heartbeat," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "TOKENS (" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "PMPGROUP = @GETENV (\"GGENVIRONMENT\",\"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "PMPTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\")));" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo ""

echo ""

echo "----PUMP Hearbeat mapping parameterfile for $GOLDENGATE_ADMIN_USER is ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm--"

echo ""

echo ""

cat ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "----------creating parameterfile for $GOLDENGATE_ADMIN_USER FOR REPLICAT---------"

echo "MAP GGADMIN.HEARTBEAT, TARGET GGADMIN.GGS_HEARTBEAT," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "KEYCOLS (DELGROUP)," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "INSERTMISSINGUPDATES," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "COLMAP (USEDEFAULTS," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "ID = 0," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "SOURCE_COMMIT = @GETENV (\"GGHEADER\", \"COMMITTIMESTAMP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EXTRACT_NAME = @TOKEN (\"CAPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "CAPTIME = @TOKEN (\"CAPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPGROUP = @TOKEN (\"PMPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPTIME = @TOKEN (\"PMPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELGROUP = @GETENV (\"GGENVIRONMENT\", \"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\"))," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDDLDELTASTATS = @TOKEN (\"EDDLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDMLDELTASTATS = @TOKEN (\"EDMLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDDLDELTASTATS = @GETENV (\"DELTASTATS\", \"DDL\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDMLDELTASTATS = @GETENV (\"DELTASTATS\", \"DML\")" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo ");" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "----------Mapping into history table------------------" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "MAP GGADMIN.HEARTBEAT, TARGET GGADMIN.GGS_HEARTBEAT_HISTORY," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "KEYCOLS (ID)," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "INSERTALLRECORDS," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "COLMAP (USEDEFAULTS," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "ID = 0," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "SOURCE_COMMIT = @GETENV (\"GGHEADER\", \"COMMITTIMESTAMP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EXTRACT_NAME = @TOKEN (\"CAPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "CAPTIME = @TOKEN (\"CAPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPGROUP = @TOKEN (\"PMPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPTIME = @TOKEN (\"PMPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELGROUP = @GETENV (\"GGENVIRONMENT\", \"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\"))," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDDLDELTASTATS = @TOKEN (\"EDDLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDMLDELTASTATS = @TOKEN (\"EDMLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDDLDELTASTATS = @GETENV (\"DELTASTATS\", \"DDL\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDMLDELTASTATS = @GETENV (\"DELTASTATS\", \"DML\")" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo ");" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo ""

echo ""

echo "----REPLICAT Hearbeat mapping parameterfile for $GOLDENGATE_ADMIN_USER is ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm--"

echo ""

echo ""

cat ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

#########################################################################################################

echo "------------Adding Goldengate Admin schema $GOLDENGATE_ADMIN_USER to GLOBALS file ----------------"

echo "GGSCHEMA $GOLDENGATE_ADMIN_USER" >> ${GGHOME}/GLOBALS

echo "------------Creating Checkpoint Table for $GOLDENGATE_ADMIN_USER ---------------------------------"

echo "Enter New checkpoint table for $GOLDENGATE_ADMIN_USER :\c"

read CKPTABLE

echo "Enter Goldengate admin password :\c"

read GOLDENGATE_ADMIN_USER_PWD

$GGHOME/ggsci << EOF

DBLOGIN USERID $GOLDENGATE_ADMIN_USER,PASSWORD $GOLDENGATE_ADMIN_USER_PWD

ADD CHECKPOINTTABLE $GOLDENGATE_ADMIN_USER.$CKPTABLE

exit

EOF

echo ""

echo ""

echo "------------Apend $GOLDENGATE_ADMIN_USER.$CKPTABLE to Globals file -------------------------------"

echo "CHECKPOINTTABLE $GOLDENGATE_ADMIN_USER.$CKPTABLE" >> ${GGHOME}/GLOBALS

cat ${GGHOME}/GLOBALS

###############################################################################

No comments:

Post a Comment