Golden Gate
Oracle Golden Gate configuration
On source system:-
[oracle@sec14-6]/oraapps/goldengate 49.24#./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Solaris, sparc, 64bit (optimized), Oracle 10g on Apr 24 2012 09:06:57
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Create the manager parameter file:-
Enter port number and save the file.
GGSCI (sec14-6) 2> edit param mgr
"/oraapps/goldengate/dirprm/mgr.prm" [New file]
PORT 7810
Create subdirectories for the GG:-
GGSCI (sec14-6) 3> create subdirs
Creating subdirectories under current directory /oraapps/goldengate
Parameter files /oraapps/goldengate/dirprm: already exists
Report files /oraapps/goldengate/dirrpt: created
Checkpoint files /oraapps/goldengate/dirchk: created
Process status files /oraapps/goldengate/dirpcs: created
SQL script files /oraapps/goldengate/dirsql: created
Database definitions files /oraapps/goldengate/dirdef: created
Extract data files /oraapps/goldengate/dirdat: created
Temporary files /oraapps/goldengate/dirtmp: created
Stdout files /oraapps/goldengate/dirout: created
Start the manager:-
GGSCI (sec14-6) 4> start mgr
Manager started.
Enable the supplemental logging for the database:-
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> alter database add SUPPLEMENTAL log data;
Database altered.
Create the extract parameter file for extraction:-
GGSCI (sec14-6) 1> edit param ext_test
"/oraapps/goldengate/dirprm/ext_test.prm" [New file]
EXTRACT EXT_TEST
INCLUDE ./dirprm/inc_reportat.inc
SETENV (ORACLE_SID="OICTEST")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
userid ggate@OICTEST password ggate
FETCHOPTIONS USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE ./dirrpt/EXT_TEST.dsc, APPEND, MEGABYTES 100
EXTTRAIL ./dirdat/ST
WARNLONGTRANS 6H, CHECKINTERVAL 1H
TABLE APPS.TEST;
Create a report parameter file for reporting the status in dirprm directory:-
Report param file name should have the same name which is mentioned in the extract param file
.
[oracle@sec14-6]/oraapps/goldengate/dirprm 49.24#vi inc_reportat.inc
"inc_reportat.inc" [New file]
-- reset report statistcs for every send report command
STATOPTIONS, RESETREPORTSTATS
-- Record count every X minutes
REPORTCOUNT EVERY 1 MINUTES, rate
REPORT AT 08:00
REPORT AT 12:00
REPORT AT 16:00
REPORT AT 20:00
REPORT AT 00:00
REPORT AT 04:00
REPORTROLLOVER ON SUNDAY
GGSCI (sec14-6) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
ADD the extract group:-
GGSCI (sec14-6) 2> add extract EXT_TEST, tranlog, begin now, threads 1;
EXTRACT added.
GGSCI (sec14-6) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_TEST 00:00:00 00:00:05
ADD the local trail declared in the EXT_TEST Extract parameters:-
GGSCI (sec14-6) 13> ADD EXTTRAIL ./dirdat/ST, EXTRACT EXT_TEST,MEGABYTES 1
EXTTRAIL added.
Start the extract process:-
GGSCI (sec14-6) 5> start EXT_TEST
Sending START request to MANAGER ...
EXTRACT EXT_TEST starting
GGSCI (sec14-6) 19> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_TEST 00:00:00 00:00:04
Add supplemental logging:-
Log into the database using ggate user
GGSCI (sec14-6) 26> dblogin userid ggate password ggate
Successfully logged into database.
Turn on supplemental logging for the TEST table
.
GGSCI (sec14-6) 29> add trandata APPS.TEST
2013-04-29 17:15:00 WARNING OGG-00869 No unique key is defined for table 'TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table APPS.TEST.
GGSCI (sec14-6) 53> info trandata apps.test
Logging of supplemental redo log data is enabled for table APPS.TEST.
Columns supplemental logged for table APPS.TEST: USER_NAME, USER_ID.
=============
Configure the data pump:-
Create parameter file for the data pump
GGSCI (sec14-6) 38> EDIT PARAM PM_TEST
"/oraapps/goldengate/dirprm/pm_test.prm" [New file]
EXTRACT PM_TEST
INCLUDE ./dirprm/inc_reportat.inc
PASSTHRU
DISCARDFILE ./dirrpt/PM_TEST.dsc, APPEND, MEGABYTES 100
RMTHOST 172.20.79.33, MGRPORT 7810
RMTTRAIL ./dirdat/SV
GETTRUNCATES
WARNLONGTRANS 6H, CHECKINTERVAL 1H
TABLE APPS.TEST;
GGSCI (sec14-6) 39> start pm_test
ERROR: Invalid command.
Above error will appears if you start the pump before adding the PM_TEST to group
GGSCI (sec14-6) 43> ADD EXTRACT PM_TEST , EXTTRAILSOURCE ./dirdat/ST
EXTRACT added.
ADD
remote trail:-
GGSCI (sec14-6) 44> ADD RMTTRAIL ./dirdat/SV, EXTRACT PM_TEST
RMTTRAIL added.
GGSCI (sec14-6) 45> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_TEST 00:00:00 00:00:06
EXTRACT STOPPED PM_TEST 00:00:00 00:01:25
Start the data pump Extract process:-
GGSCI (sec14-6) 46> start PM_TEST
Sending START request to MANAGER ...
EXTRACT PM_TEST starting
GGSCI (sec14-6) 52> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_TEST 00:00:00 00:00:03
EXTRACT RUNNING PM_TEST 00:00:00 00:00:01
=========================================+++++++++++++++++++++++++++++++++
=================================+++++++++++++++++++++++++++++++=========
On Target system:-
[oracle@sec40-6]/oraapps/goldengate 49.24#./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Solaris, sparc, 64bit (optimized), Oracle 10g on Apr 24 2012 09:06:57
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Create the manager parameter file:-
Enter port number and save the file.
GGSCI (sec40-6) 2> edit param mgr
"/oraapps/goldengate/dirprm/mgr.prm" [New file]
PORT 7810
Create subdirectories for the GG:-
GGSCI (sec40-6) 3> create subdirs
Creating subdirectories under current directory /oraapps/goldengate
Parameter files /oraapps/goldengate/dirprm: already exists
Report files /oraapps/goldengate/dirrpt: created
Checkpoint files /oraapps/goldengate/dirchk: created
Process status files /oraapps/goldengate/dirpcs: created
SQL script files /oraapps/goldengate/dirsql: created
Database definitions files /oraapps/goldengate/dirdef: created
Extract data files /oraapps/goldengate/dirdat: created
Temporary files /oraapps/goldengate/dirtmp: created
Stdout files /oraapps/goldengate/dirout: created
Start the manager:-
GGSCI (sec40-6) 4> start mgr
Manager started.
Enable the supplemental logging for the database:-
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> alter database add SUPPLEMENTAL log data;
Database altered.
=========================================+++++++++++++++++++++++++++++++++
=================================+++++++++++++++++++++++++++++++=========
Performing initial data load
On Source:-
1) Create the Initial data extract process ‘load1′
GGSCI (sec14-6) 91> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.
Since this is a one time data extract task, the source of the data is not the transaction log files of the RDBMS (in this case the online and archive redo log files) but the table data itself, that is why the keyword SOURCEISTABLE is used.
2) Create the parameter file for the extract group load1
EXTRACT: name of the extract group
USERID/PASSWORD: the database user which has been configured earlier for Extract ( this user is created in the source database)
RMTHOST: This will be the IP address or hostname of the target system
MGRPORT: the port where the Manager process is running
TABLE: specify the table which is being extracted and replicated. This can be specified in a number of ways using wildcard characters to include or exclude tables as well as entire schemas.
GGSCI (sec14-6) 92> EDIT PARAMS load1
"/oraapps/goldengate/dirprm/load1.prm" [New file]
EXTRACT load1
USERID ggate@OICTEST, PASSWORD ggate
RMTHOST 172.20.79.33, MGRPORT 7810
RMTTASK replicat, GROUP load2
TABLE APPS.users1;
On Target:-
3) Create the initial data load task ‘load2′
Since this is a one time data load task, we are using the keyword SPECIALRUN
GGSCI (sec40-3) 12> ADD REPLICAT load2, SPECIALRUN
REPLICAT added.
Create the parameter file for the Replicat group, load2
REPLICAT: name of the Replicat group created for the initial data load
USERID/PASSWORD: database credentials for the Replicat user (this user is created in the target database)
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
MAP: with GoldenGate we can have the target database structure entirely differ from that of the source in terms of table names as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which is same in this case
GGSCI (sec40-3) 13> EDIT PARAMS load2
"/oicappdb2/ggate/dirprm/load2.prm" [New file]
REPLICAT load2
USERID ggate@OIC, PASSWORD ggate
ASSUMETARGETDEFS
MAP APPS.users1, TARGET APPS.users1;
On source:-
SQL> select count(*) from users1;
COUNT(*)
----------
4457
On Target:-
SQL> select count(*) from users1;
COUNT(*)
----------
449
Start the initial load data extract task on the source system
We now start the initial data load task load 1 on the source. Since this is a one time task, we will initially see that the extract process is runningand after the data load is complete it will be stopped. We do not have to manually start the Replicat process on the target as that is done when the Extract task is started on the source system.
On Source
GGSCI (sec14-6) 3> start extract load1
Sending START request to MANAGER ...
EXTRACT LOAD1 starting
GGSCI (sec14-6) 6> info extract load1
EXTRACT LOAD1 Last Started 2013-04-30 12:36 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table APPS.USERS1
2013-04-30 12:37:12 Record 4457
Task SOURCEISTABLE
On Target:-
SQL> select count(*) from users1;
COUNT(*)
----------
4906
Errors:
Some time we may get the error when we start extract process :
2013-04-30 12:23:57 ERROR OGG-00664 OCI Error during OCIServerAttach (status = 12154-ORA-12154: TNS:could not resolve the connect identifier specified).
2013-04-30 12:23:57 ERROR OGG-01668 PROCESS ABENDING.
For this issue restart the manager and try to start the extract once again.