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

source

How to install and configure Oracle Golden Gate – Initial Setup

A while ago I was presented with the challenge of synchronizing several databases in order to move data from a source database to a stage database and finally to a target database.  Although there are several ways to achive this, it was decided to use Oracle Golden Gate to migrate the data, in real time, to a stage server and then to a target server.

There was also a need of creating some interfaces in order to mask the database structure on source server and fullfil the customer’s demands. There the Oracle Data Integrator comes in, but we will discuss the ODI on another blog.

A.- Topology

I have installed Oracle VirtualBox 5.0.2 on my computer. I have created two virtual servers which contain Oracle Enterprise Linux 6.7 x86-64 and Oracle Database 12c, version: 12.1.0.2. The Oracle Golden Gate I installed is version: 12.1.2.1.

• Source server: dbsrc
• Stage server: stgdb
• Target server: dbtgt

The stage and target databases reside on the same server for simplicity. In this blog I will focus on Oracle Golden Gate initial setup on both source database dbsrcand target database stgdb. The database dbtgt will be used as an example in my future blog on Oracle Data Integrator.

I downloaded the Oracle Golden Gate file:
121210_fbo_ggs_Linux_x64_shiphome.zip from http://otn.oracle.com. There are several download files to choose from. One has to choose the one corresponding to the relevant technology. In this case Oracle Golden Gate for Oracle Linux x86-64.

B.- Installing Oracle Golden Gate

I logged on source and target servers as oracle user that owns the software of Oracle Database and Oracle Golden Gate.

I moved the file 121210_fbo_ggs_Linux_x64_shiphome.zip to source and target server andunzipped the file.

The folder created on source and target servers is then: fbo_ggs_Linux_x64_shiphome

Then change directory to fbo_ggs_Linux_x64_shiphome/Disk1.

At this point one has to check that the following environment variables are set on source server:

- ORACLE_HOME = /db0/oracle/product/12.1.0.2/db_1
- ORACLE_SID = dbsrc

and on target server:

- ORACLE_HOME = /db0/oracle/product/12.1.0.2/db_1
- ORACLE_SID = stgdb

The images below show the installation of Oracle Golden Gate on source server and the same installation should be performed on target server.

Press Next:

Specify the home of Oracle Golden Gate. Choose an Oracle_Home different from the one of the Oracle database and press Next:

Then press Install.

Press Close.

At this point the OGG software is installed on source and target servers.

C.- Configuring Oracle Golden Gate for initial setup

I will show here the initial configuration on source and target servers for initial extract and replicat. There are several steps to be performed on the source and target databases.

1.- Update LD_LIBRARY_PATH in the profile file on both source and target servers.

For example my file .bash_profile on source server looks like this:

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=srcdb.localdomain
export ORACLE_UNQNAME=dbsrc
export ORACLE_BASE=/db0/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_GG_HOME=$ORACLE_BASE/product/12.1.2.1/ogg_1
export ORACLE_SID=dbsrc

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_GG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

And the .bash_profile on target server should look similar, but the variables ORACLE_UNQNAME and ORACLE_SID have to be set to stgdb to reflect the target database.

2. - Enabling Supplemental logging on source database dbsrc in order to log all transactions and loads. This step is not required for target database stgdb.

Log on source database as sys. I use Oracle SQLDeveloper which gives a nice graphical interface against the database:

SELECT supplemental_log_data_min, force_logging FROM v$database;
If the result is NO then run:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;

Then run again:
SELECT supplemental_log_data_min, force_logging FROM v$database;
The result should be YES now.

Then switch the log files:

ALTER SYSTEM SWITCH LOGFILE;

3.- Enable archivelog. The Oracle source database must have archivelog enabled. This step is not required for target database.

Log on source database dbsrc as sys and run:

SQL> archive log list;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

4.- Create Oracle Golden Gate user on source and target databases:

Log on source and target databases as sys:

a.- Create tablespace for OGG user:

CREATE TABLESPACE ogg_data LOGGING
DATAFILE '/db0/oracle/oradata/dbsrc/ogg_data_01.dbf'
SIZE 1024M REUSE AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Here I have changed the location of datafile on target server to:
DATAFILE '/db0/oracle/oradata/stgdb/ogg_data_01.dbf'

b.- Create Golden Gate user:

CREATE USER ogg_user IDENTIFIED BY oracle
DEFAULT TABLESPACE ogg_data
TEMPORARY TABLESPACE temp;

c.- Grant relevant privileges to user ogg_user:

GRANT CONNECT, RESOURCE TO ogg_user;
GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO ogg_user;
GRANT CREATE TABLE TO ogg_user;
GRANT FLASHBACK ANY TABLE TO ogg_user;
GRANT EXECUTE ON dbms_flashback TO ogg_user;
GRANT EXECUTE ON utl_file TO ogg_user;
GRANT CREATE ANY TABLE TO ogg_user;
GRANT INSERT ANY TABLE TO ogg_user;
GRANT UPDATE ANY TABLE TO ogg_user;
GRANT DELETE ANY TABLE TO ogg_user;
GRANT DROP ANY TABLE TO ogg_user;
GRANT ALTER ANY TABLE TO ogg_user;
GRANT ALTER SYSTEM TO ogg_user;
GRANT LOCK ANY TABLE TO ogg_user;
GRANT SELECT ANY TRANSACTION to ogg_user;
ALTER USER ogg_user QUOTA UNLIMITED ON ogg_data;

d.- Authenticate the user ogg_user with Golden Gate package:

exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ogg_user');

e.- Enable Golden Gate on both databases:

ALTER SYSTEM SET enable_goldengate_replication=TRUE;

5a.- Edit manager parameter on source server:

Go to Golden Gate home:
$ cd $ORACLE_GG_HOME 
Then run:
$ ./ggsci

Then type:
ggsci > edit param mgr

Then the parameter manager file on source server should look like this:

PORT 7809
PurgeOldExtracts /db0/oracle/product/12.1.2.1/ogg_1/dirdat/* UseCheckPoints, MinKeepHours 48

5b.- Edit manager parameter on target server:

ggsci > info mgr
ggsci > edit param mgr

Then the parameter manager file on target server should look like this:
PORT 7809

Save the changes and then return to ggsci command interface.
Then run the following commands:

ggsci > stop mgr
ggsci > start mgr 
ggsci > info mgr detail

6.- Storing credentials on wallet. This step is performed on both source and target servers.

Run the following commands on Golden Gate interface ggsci. The connect string dbsrc below is the tnsnames of the source database.

ggsci > create wallet
ggsci > Add CredentialStore
ggsci > alter CredentialStore Add User ogg_user@dbsrc Password oracle Alias ogg_user
ggsci > info CredentialStore

Similarly the commands on Golden Gate interface ggsci on target server are:

ggsci > create wallet
ggsci > Add CredentialStore
ggsci > alter CredentialStore Add User ogg_user@stgdb Password oracle Alias ogg_user
ggsci > info CredentialStore

Then we test the credentials. Run the following command on ggsci interface on both source and target servers:

ggsci > DBLogin UserIDAlias ogg_user

Successfully logged into database.

At this point the inital Oracle Golden Gate setup is completed and ready on source and target database servers.
On the next blogs I will cover examples of Oracle Golden Gate initial load and Oracle Data Integrator.

Sources:

*Photo: "GoldenGateBridge-001" by Rich Niewiroski Jr. - http://www.projectrich.com/gallery. Licensed under CC BY 2.5 via Wikimedia Commons

ALTER REPLICAT

Use ALTER REPLICAT to change the attributes of a Replicat group that was created with the ADD REPLICAT command. Before using this command, stop Replicat by issuing the STOP REPLICAT command. If this is a coordinated Replicat group, the ALTER takes effect for all threads unless thethreadID option is used.

Note:

ALTER REPLICAT does not support switching from regular Replicat mode to coordinated mode. You must stop processes, make certain all of the en route data is applied to the target, roll the trail to a new trail, drop and recreate the Replicat group in coordinated mode, and then start the processes again.

Syntax

ALTER REPLICAT group_name[threadID], {
   ADD REPLICAT option [, ...] |
   INTEGRATED | NONINTEGRATED, CHECKPOINTTABLE owner.table 
}
[, CPU number]
[, PRI number]
[, HOMETERM device_name]
[, PROCESSNAME process_name]

group_name[threadID]

The name of the Replicat group or a thread of a coordinated Replicat that is to be altered. To specify a thread, use the full thread name, such asALTER REPLICAT fin003, EXTSEQNO 53. If a thread ID is not specified, the ALTER takes effect for all threads of the Replicat group.

ADD REPLICAT option

An ADD REPLICAT option. For a non-integrated Replicat, you can change the description or any service option that was configured with the ADD REPLICAT command, except for the CHECKPOINT and NODBCHECKPOINT options.

INTEGRATED

Switches Replicat from non-integrated mode to integrated mode. Transactions currently in process are applied before the switch is made. SeeAdministering Oracle GoldenGate for Windows and UNIX for the full procedure for performing the transition from non-integrated to integrated Replicat.

NONINTEGRATED, CHECKPOINTTABLE owner.table

(Oracle) Switches Replicat from integrated mode to non-integrated mode.

For CHECKPOINTTABLE, specify the owner and name of a checkpoint table. This table must be created with the ADD CHECKPOINTTABLE command before issuing ALTER EXTRACT with NONINTEGRATED.

See Administering Oracle GoldenGate for Windows and UNIX for the full procedure for performing the transition from integrated Replicat to non-integrated Replicat.

See Installing and Configuring Oracle GoldenGate for Oracle Database for more information about integrated Replicat.

CPU number

Valid for SQL/MX. Specifies the number of the CPU to be used for the process. Valid values are numbers 1 - 15 and -1 is default, which is assigned 1 higher than the last Manager started.

PRI number

Valid for SQL/MX. Specifies the Extract process priority. Valid values are numbers are 1 - 199 and -1 is the default, and is the same as the manager process priority.

HOMETERM device_name

Valid for SQL/MX. Specifies the name of the device to be used and must be a terminal or process. It can be entered in either Guardian $ or OSS/G/xxxxx form. The default is $zhome or the current session HOMETERM when $zhome is not defined.

PROCESSNAME process_name

Valid for SQL/MX. Specifies the name of the process as alphanumeric string up to five characters and can be entered in either Guardian $ or OSS/G/xxxxx form. The default is a system generated process name.

Examples

Example 1   

ALTER REPLICAT finance, EXTSEQNO 53

Example 2   

ALTER REPLICAT finance, EXTRBA 0

Example 3   

ALTER REPLICAT finance, BEGIN 2011-01-07 08:00:00

Example 4   

ALTER REPLICAT finance, INTEGRATED

Example 5   

ALTER REPLICAT finance, NONINTEGRATED, CHECKPOINTTABLE ogg.checkpt

Example 6   

ALTER REPLICAT fin001, EXTSEQNO 53

Example 7   

The following alters a Replicat on a SQL/MX NonStop platform.

ALTER REPLICAT reptcp, CPU 3, PRI 150, HOMETERM /G/zhome,  PROCESSNAME default