OGG Auto Script For Windows

1) For Startup :

> cat start.txt :

dblogin userid ggs_owner password ggs_owner
start mgr

> start_ogg.bat :
echo obey C:\Users\Administrator\Desktop\start.txt |C:\oracle\ggs\ggsci

2) For Stop :

> cat stop.txt
dblogin userid ggs_owner password ggs_owner
kill *
kill mgr

> cat stop_ogg.bat
echo obey C:\Users\Administrator\Desktop\stop.txt |C:\oracle\ggs\ggsci



Note :
--------------------------------------------------------------------------------------------------------

for example Obey file obey_cmds.txt:
dblogin userid ggsadmin password pwd
info all

echo obey /apps/ggsadmin/goldengate/monitoring/scripts/obey_cmds.txt |$GG_HOME/ggsci

Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 AIX 5L, ppc, 64bit (optimized), Oracle 11g on Apr 23 2012 05:03:51
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (aixorad02) 1> GGSCI (aixorad02) 2> dblogin userid ggsadmin password g01d3ng5t3au
Successfully logged into database.
GGSCI (aixorad02) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT RUNNING EQBTTC 00:00:00 00:00:01 EXTRACT RUNNING PQBTTC 00:00:00 00:00:01
GGSCI (aixorad02) 4> exit

-----------------------------------------------------------------------------------------

INSERTMISSINGUPDATES

            source

INSERTMISSINGUPDATES

This parameter should be added to the Replicat. It means, this is VALID only for REPLICAT Process and should be used only when the Source database logs all column values.

ADD SCHEMATRANDATA SCOTT ALLCOLS

ALLCOLS – Enables the unconditional supplemental logging of all supported key and non-key columns for all current and future tables in the given schema.

When this parameter is added to the Replicat file, it inserts a record based on the Source record when the target record does not exist.

This means, If a record is updated in Source, On the target the same record will be inserted if the record does not exists instead a missing record error or REPLICAT ABEND.
This parameter also supports the database which is using the COMPRESSED form of updates (where only changed values are logged instead of logging all columns) provided that the NULL can be used for missing column values on the target database.

NOINSERTMISSINGUPDATES

This parameter is a default one. When this is in place, whenever a record is updated on the Source side, and that record is not present in the Target then this leads to a Missing Record Error and the Transaction might ABEND depending on the REPERROR parameter setting in the REPLICAT file.

In General, these parameters are specific to tables. A single parameter will be in effect for all the MAP statements until the other parameter is detected or encountered by the Process.

The below example shows how the INSERTMISSINGUPDATES works.,

GoldenGate Version – 12.1.2.0.0
Oracle Version – 11.2.0.4.0
OS Version – Oracle Enterprise Linux 6
Replicat Type – Integrated
Schema Used – SCOTT
Table Used – DEPT

I have used the INSERTMISSINGUPDATES in my Replicat Parameter File.

Replicat REP1
userid ggadmin, password *******
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
ASSUMETARGETDEFS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE RETRYOP
DiscardFile /ogg/dirrpt/rep1.dsc, Purge
INSERTMISSINGUPDATES
Map scott.*, target scott.*;

ON SOURCE

Check the records on the Source table.

SQL> select * from dept; DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     INDIA

ON TARGET

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     INDIA

Now I am going to delete a record on the Target Side,.

SQL> delete from dept where deptno=50;
1 row deleted.
SQL> commit;
Commit complete.

Check if the record has been deleted or not.,

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON

Now in Target we have only 4 rows. One row is deleted. Now I am going to process an update in the source side in the row which I have deleted in the Target side.

ON SORUCE

The records on the Source is as below,

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     INDIA

Now update the record.,

SQL> update dept set LOC=’WASHINGTON’ where deptno=50;
1 row updated.
SQL> commit;
Commit complete.

Check if the update has been reflected.,

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     WASHINGTON

ON TARGET

Check the rows on the target table.

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
50          IT                     WASHINGTON
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON

You can see the row which I have updated in Source has been inserted in Target side..

CONCLUSION

INSERTMISSINGUPDATES – Using this parameter we can handle the Missing record error on the TARGET – REPLICAT.

We can clearly see that, whenever an update is made in the SOURCE, the record got inserted in the TARGET table, eventhough it was not present in the TARGET table.

Oracle GoldenGate: Best Practices and Security

For replication to be robust, well tuned and secure, some of the out-of-the box settings need to modified. Here is a list of these items, focusing on the Goldengate Best Practices and Security, in areas such as the Database, Parameters, processes, etc.

Oracle GoldenGate: Best Practices and Security

Oracle Database

• Ensure that all the tables have Primary keys.
• Do not grant the DBA privilege to the GG user, give only the required privileges.
• Supplemental logging should be enabled using TRANDATA

Oracle Golden Gate Parameter files

• Use USECHECKPOINTS with the PURGEOLDEXTRACTS clause in the mgr.prm file.
PURGEOLDEXTRACTS , USECHECKPOINTS, MINKEEPHOURS 24 MINKEEPFILES 30 • Do not use HANDLECOLLISIONS unless it is absolutely required and temporary. Read more on effects of using handle collisions
• If HANDLECOLLISIONS is being used it should only be set for specific tables ONLY and the remainder tables set to NOHANDLECOLLISIONS.

Oracle Golden Gate Performance

• Increase read buffer size of dblogreader to 4M
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 4096000
TRANLOGOPTIONS DBLOGREADERBUFSIZE 4096000 • Use datapump compression

Oracle Golden Gate High Availability

• Add autostart for mgr process
• Add auto restart specifying at least 3 minimum restart attempts
AUTORESTART EXTRACT *, RETRIES , WAITMINUTES , RESETMINUTES • Use checkpoint table not checkpoint file for checkpoint recovery.

Oracle GoldenGate Security

• In PCI environments personal sensitive data should be excluded by the extract process, to avoid any of this data in the trail files.
• Used password encryption
• Use datapump encryption
• Use trail file encryption

Troubleshooting/Monitoring

• Write lag messages and warnings to the ggserror.log file for all the Oracle Goldengate processes.
LAGREPORTMINUTES
LAGCRITICALMINUTES
• Use WARNLONGTRANS 2h, CHECKINTERVAL 10m (example) on extract side to write warnings to the alert log for long running transactions
• Write record count of transactions every 1 hour or so to report log.
REPORTCOUNT EVERY HOURS, RATE • Rollover the report for the individual processes daily.

OGG Tip: HANDLECOLLISIONS is slow car crash

K.Gan Feb 25, 2016 10:27 AM

Copied from manual:

Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors, called collisions, occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes that are being made to those tables. When Oracle GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS provides Replicat with error-handling logic for these collisions.

Comments:

If you are capturing from an Oracle database you should never ever need to use HANDLECOLLISIONS for initial load or otherwise. I highlighted 'initial load' and should never be continually used for any databases that is initial load phase is deem completed. The detail mechanics of  handlecollisions is extensively documented in the manual. Initial loads from an Oracle database should use a flashback SCN for accurate initial load syncs.

Using this parameter for continually used will almost certainly to make your target database out of sync with the source silently thus the comparison with a slow car crash.

If your replicat abends due to constraint or row not found error then you need to ask yourself why. Using handlecollisions to basically ignore this error is like a bank customer depositing money into an account that does not exist and basically ignoring it. I am sure you won't want to be that customer.

When you get a database error, try to find out why and if you cannot resolve this in a timely manner consider excluding the problem table for now and resolve this later. You can at least recover. If you use handlecollsions to consider it will very likely to be unrecoverable.

If you think that this may be a once off for one or a few records, use REPERROR (, discard) option on the MAP statement for that table. If you don't have a specific one because the MAP table names are wildcarded, create one for this table.

Oracle DB Auto Startup Script

1) script :

#!/bin/bash
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORACLE_OWNER to the user id of the owner of the
# Oracle database in ORACLE_HOME.

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_OWNER=oracle

if [ ! -f $ORACLE_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
        su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbstart
          touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbshut
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
         rm -f /var/lock/subsys/dbora
        ;;
esac
                       

2)cat /etc/inittab  or.  /var/opt/oracle/oratab

  *:/u01/app/oracle/product/10.2.0/db_1:N
orcl:/u01/app/oracle/product/10.2.0/db_1:Y


3)[root@server ~]# cd /var/opt/oracle/
[root@server oracle]# ls
oratab


4)
chmod 750 /etc/init.d/dbora
chkconfig --level 345 dbora on


Check following Oracle Documentation link , it explain all in details

http://download-uk.oracle.com/docs/html/B10812_01/chapter2.htm#sthref210

OGG Auto Startup Script in Linux

source : https://setijoagus.wordpress.com/2010/09/04/auto-start-goldengate-on-linux-centos-release-5-2-final/

1)

[oracle@pbltest ggs]$ cat startGoldenGate
start manager

start extract *
--start replicat *

exit


2)
[oracle@pbltest ggs]$ cat startgoldengate
#!/bin/sh
# Start the oracle listener
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=PRIMPBL
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export GGATE=/u02/oracle/ggs
export PATH=$ORACLE_HOME/bin:$PATH:$GGATE
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/u02/oracle/ggs
$GGATE/ggsci PARAMFILE  /u02/oracle/ggs/startGoldenGate




-------
chmod a+x /u02/oracle/ggs/startgoldengate
--------------------------------------------


3)

 vi /u02/oracle/ggs/stopGoldenGate

stop extract *
--stop replicat *

STOP MANAGER !
exit

------




testing :

 ./ggsci  PARAMFILE /u02/oracle/ggs/stopGoldenGate



4)  vi /u02/oracle/ggs/stopgoldengate

#!/bin/sh
# Start the oracle listener
#!/bin/sh
# Start the oracle listener
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=PRIMPBL
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export GGATE=/u02/oracle/ggs
export PATH=$ORACLE_HOME/bin:$PATH:$GGATE
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/u02/oracle/ggs

$GGATE/ggsci PARAMFILE /u02/oracle/ggs/stopGoldenGate



----

chmod a+x /u02/oracle/ggs/stopgoldengate
--



5) vi /u02/oracle/ggs/xgoldengate
[root@pbltest init.d]# cat /u02/oracle/ggs/xgoldengate
#!/bin/sh
#
# chkconfig: 345 51 49
# description: startup and shutdown the Oracle 11g goldengate
#
echo "Goldengate start/stop"
ORA_OWNER=oracle
GG_HOME=/u02/oracle/ggs

case "$1" in
'start')
# Start the goldengate
echo -n "Starting the goldengate : "
su - $ORA_OWNER -c $GG_HOME/startgoldengate
echo
;;

'stop')
# Stop the goldengate
echo -n "Shutting down goldengate : "
su - $ORA_OWNER -c $GG_HOME/stopgoldengate
echo
;;

"restart")
# Restart the Oracle databases:
echo -n "Restarting goldengate : "
$0 stop
$0 start
echo
;;

*)
echo "Usage: xgoldengate [ start | stop | restart }"
exit 1

esac
exit 0



6) Script automatic startup & shutdown goldengate


      cp /u02/oracle/ggs/xgoldengate /etc/rc.d/init.d/

      cd /etc/rc.d/init.d/

      chmod 750 /etc/rc.d/init.d/xgoldengate



7) Finally Add with chkconfig :


[root@pbltest init.d]# chkconfig --add xgoldengate


[root@pbltest init.d]# chkconfig --list xgoldengate
xgoldengate     0:off   1:off   2:off   3:on    4:on    5:on    6:off

[root@pbltest init.d]# chkconfig xgoldengate on



=====================================================================

[root@pbltest ~]# cat /etc/rc.local
#!/bin/sh
sh /home/oracle/ggs_start.sh

[root@pbltest ~]# cat /home/oracle/ggs_start.sh
#!/bin/bash
su - oracle -c /u02/oracle/ggs/ggsci << !
start mgr
exit;

===============================================
[oracle@db02 ggs]$ crontab -e
#OGG start
#30 20 * * *  sh  /u03/oracle/ggs/startgoldengate >>/dev/null 2>&1
#OGG stop
#30 07 * * *  sh  /u03/oracle/ggs/stopgoldengate >>/dev/null 2>&1

Exclude Table During MAP !

EXT1 :

GGSCI (pbltest.pbl.com) 14> edit params ext1
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = "PRIMPBL")
SETENV (NLS_LANG=".WE8MSWIN1252")
EXTTRAIL /u02/oracle/software/goldengate/dirdat/lt
WILDCARDRESOLVE IMMEDIATE
TRANLOGOPTIONS INCLUDEREGIONID
--DYNAMICRESOLUTION
--EOFDELAY 60
TABLEEXCLUDE ULTIMUSISL.CLEARING_HOUSE_NO
TABLEEXCLUDE ULTIMUSISL.GTMP_MIS_GL_SUPPL
TABLEEXCLUDE ULTIMUSISL.TMP_OUT_DBF_FILE
TABLEEXCLUDE ULTIMUSISL.TMP_RPT_CL1
TABLEEXCLUDE ULTIMUSISL.TMP_SMS_FUNCTION_ACCESS
TABLEEXCLUDE ULTIMUSISL.TMP_SMS_MENU_ACCESS
TABLEEXCLUDE ULTIMUSISL.TMP_YEAR_CLD
TABLEEXCLUDE ULTIMUSISL.TODAY_SALES
TABLEEXCLUDE ULTIMUSISL.TMP_CALENDAR
TABLEEXCLUDE ULTIMUSISL.TMP_COR_LON_COMMIT_TRAN
TABLE ULTIMUSISL.*