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.*