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
###############################################################################
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
###############################################################################
