Source DB:
GG version: 11.2.1.0.1
ORACLE DB Version: Oracle EE 11.2.0.1
OS version: Linux 2.6.18-164.el5 x86_64
IP : 10.44.10.176
Target DB:
GG version: 11.2.1.0.1
ORACLE DB Version : Oracle EE 11.2.0.1
OS version: Linux 2.6.18-164.el5 x86_64
IP : 10.44.10.181
STEP 1 Database Pre requisites
Need to be carried out at both source and target
alter system set log_archive_dest='/oracle/flash_recovery_area' scope=spfile;
alter system set recyclebin=off scope=spfile;
alter system set undo_retention=86400 scope=spfile;
shut immediate;
startup mount;
alter database archivelog;
alter database open;
alter database add supplemental log data;
alter database force logging;
create tablespace ggs_data datafile '/oracle/oradata/orcl/ggs_data01.dbf' size 200m;
create user ggs_owner identified by ggs_owner
default tablespace ggs_data
temporary tablespace temp;
grant connect, resource to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;
grant create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;
STEP 2 GG Software Installation
Need to be carried out at both source and target
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oracle/gg
export LD_LIBRARY_PATH
bash-3.2$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (INCHOMDP005773) 1> CREATE SUBDIRS
Creating subdirectories under current directory /oracle/gg
Parameter files /oracle/gg/dirprm: already exists
Report files /oracle/gg/dirrpt: created
Checkpoint files /oracle/gg/dirchk: created
Process status files /oracle/gg/dirpcs: created
SQL script files /oracle/gg/dirsql: created
Database definitions files /oracle/gg/dirdef: created
Extract data files /oracle/gg/dirdat: created
Temporary files /oracle/gg/dirtmp: created
Stdout files /oracle/gg/dirout: created
STEP 3 Run scripts for creating all necessary objects for support DDL replication
Need to be carried out at source and target
As sys as sysdba run below sqls, provide GGS_OWNER if requested.
--if below sql hangs, change the path in the sql file @ OR invoke SQLPLUS from GG location
@/oracle/gg/marker_setup.sql GGS_OWNER
@/oracle/gg/ddl_setup.sql GGS_OWNER
@/oracle/gg/role_setup.sql GGS_OWNER
grant ggs_ggsuser_role to ggs_owner;
@/oracle/gg/ddl_enable GGS_OWNER
@/oracle/gg/ddl_pin GGS_OWNER
Note- We had earlier enabled additional supplemental logging at the database level.
Using the ADD TRANDATA command we now enable it at even the table level as this is required by Golden Gate for DDL support.
--Login to GGSCI
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
--From SQL generate the below command and run it in GGSCI
select 'add trandata '||owner||'.'||object_name||'' from dba_objects where owner='HR' and object_type='TABLE';
select 'add trandata '||owner||'.'||object_name||'' from dba_objects where owner='HR' and object_type='TABLE';
add trandata HR.TEST
add trandata HR.REGIONS
add trandata HR.LOCATIONS
add trandata HR.JOB_HISTORY
add trandata HR.JOBS_ROWIDTEST
add trandata HR.JOBS
add trandata HR.EMPLOYEES
add trandata HR.DEPARTMENTS
add trandata HR.COUNTRIES
GGSCI (INCHOMDP005773) 10> add trandata HR.TEST
add trandata HR.REGIONS
add trandata HR.LOCATIONS
add trandata HR.JOB_HISTORY
add trandata HR.JOBS_ROWIDTEST
add trandata HR.JOBS
add trandata HR.EMPLOYEES
add trandata HR.DEPARTMENTS
add trandata HR.COUNTRIES
2013-10-07 22:18:32 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 HR.TEST.
GGSCI (INCHOMDP005773) 11>
Logging of supplemental redo data enabled for table HR.REGIONS.
GGSCI (INCHOMDP005773) 12>
Logging of supplemental redo data enabled for table HR.LOCATIONS.
GGSCI (INCHOMDP005773) 13>
Logging of supplemental redo data enabled for table HR.JOB_HISTORY.
GGSCI (INCHOMDP005773) 14>
2013-10-07 22:18:41 WARNING OGG-00869 No unique key is defined for table 'JOBS_ROWIDTEST'. 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 HR.JOBS_ROWIDTEST.
GGSCI (INCHOMDP005773) 15>
Logging of supplemental redo data enabled for table HR.JOBS.
GGSCI (INCHOMDP005773) 16>
Logging of supplemental redo data enabled for table HR.EMPLOYEES.
GGSCI (INCHOMDP005773) 17>
Logging of supplemental redo data enabled for table HR.DEPARTMENTS.
GGSCI (INCHOMDP005773) 18>
Logging of supplemental redo data enabled for table HR.COUNTRIES.
STEP 4 Create and start manager on the source and the destination.
Need to be carried out at source and target
-bash-3.2$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (INCHOMDP005773) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
GGSCI (INCHOMDP005773) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (INCHOMDP005773) 3>
GGSCI (INCHOMDP005773) 3> edit params mgr
Paste the below and save the file (/oracle/gg/dirprm/mgr.prm)
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /oracle/gg/dirdat/ex, USECHECKPOINTS
GGSCI (INCHOMDP005773) 8> start manager
Manager started.
GGSCI (INCHOMDP005773) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
STEP 6 Create the EXTRACT group
Need to be carried out at source
add extract ext1, tranlog, begin now
add exttrail /oracle/gg/dirdat/lt, extract ext1
edit params ext1
extract ext1
userid ggs_owner, password ggs_owner
rmthost 10.44.10.181, mgrport 7809
rmttrail /oracle/gg/dirdat/lt
DDL INCLUDE ALL
table HR.*;
STEP 7 Create CHECKPOINT Table
Need to be carried out at both source and target
GGSCI (INCHOMDP005773)> edit params ./GLOBAL
GGSCHEMA ggs_owner
CHECKPOINTTABLE ggs_owner.Checkpoint
GGSCI (INCHOMDP005773) 2> dblogin userid ggs_owner password ggs_owner
Successfully logged into database.
GGSCI (INCHOMDP005773) 3> add checkpointtable ggs_owner.checkpoint
Successfully created checkpoint table ggs_owner.CHECKPOINT.
STEP 8 Create the REPLICAT group
Need to be carried out at target
cd /oracle/gg/
mkdir discard
GGSCI (DBTARGET) 4>add replicat rep1, exttrail /oracle/gg/dirdat/lt,checkpointtable ggs_owner.checkpoint
GGSCI (DBTARGET) 4> EDIT PARAMS rep1
replicat rep1
userid ggs_owner, password ggs_owner
discardfile /oracle/gg/discard/rep1_discard.txt, append, megabytes 10
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
ASSUMETARGETDEFS
MAP HR.*, TARGET HR.*;
TABLEEXCLUDE HR.SYS_EXPORT_SCHEMA_01
STEP 9 Initial Dataload
Need to be carried out at source
SQL> select TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
------------------------------
2908435
expdp HR/HR directory=data_pump_dir dumpfile=schema_gg1.dmp logfile=schema_gg1.log schemas=HR flashback_scn=2908435
Transfer schema_gg.dmp to target
scp schema_gg1.dmp oracle@10.44.10.181:/oracle/Ora11gdb/admin/orclgg/dpdump/
Need to be carried out at target
impdp HR/HR directory=data_pump_dir dumpfile=schema_gg1.dmp logfile=schema_imp_gg1.log schemas=hr
STEP 10 Start the EXTRACT
STATUS FOR SOURCE
GGSCI (INCHOMDP005773) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 02:23:37
STATUS FOR TARGET
GGSCI (INCHOMDP005891) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 02:00:07
GGSCI (INCHOMDP005891) 12> start extract ext1
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 02:23:37
GGSCI (INCHOMDP005773) 63> send extract ext1 status
Sending STATUS request to EXTRACT EXT1 ...
STEP 11 Start the REPLICAT
GGSCI (INCHOMDP005891) 12> start replicat rep1, aftercsn 2908435
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 02:00:07
Monitor using tail -f ggserr.log
STEP 12 Test Cases
CASE1
create table HR.Employee1(
ID VARCHAR2(4 BYTE) ,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
);
CASE2
Create table test_gg_1(Id number(10),Username varchar2(20));
Insert into test_gg_1 values(1,'USER1');
Insert into test_gg_1 values(2,'USER2');
Insert into test_gg_1 values(3,'USER3');
Insert into test_gg_1 values(4,'USER4');
Insert into test_gg_1 values(5,'USER5');
COMMIT;
CASE3
Update hr.test_gg_1 set username='USERDBA' where id=1;
Commit;
CASE4
Delete from hr.test_gg_1 where id=4;
Commit;
Errors & Solutions
Error1
Could not able to start the extract
Solution
Removed comments in prm file, -- is used for comment
Error2
Table xxx.SYS_IMPORT_FULL_01 does not exist in target database.
Solution
TABLEEXCLUDE HR.SYS_EXPORT_SCHEMA_01 in rep1.prm
Error3Could not able to start the replicat and replicat is ABENDING
Solution
Add this in ASSUMETARGETDEFS rep1.prm
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
Error4
MOS(OGG Extract 11.2 abends with ERROR OGG-01960 Failed to validate table The table is created with the NOLOGGING option, which is not supported. [ID 1465720.1])
Solution
As a temporary workaround, DBOPTIONS ALLOWNOLOGGING can be added after USERID parameter in the extract parameter
file. This parameter will cause the extract to log a warning message and continue to run. However, there is a cha
nce for data loss. Resync in required
ggserr file without errors
2013-10-08 23:10:15 INFO OGG-01677 Oracle GoldenGate Collector for Oracle: Waiting for connection (started dynamically).
2013-10-08 23:10:18 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, MGR.prm: Command received from SERVER on host localhost (REPORT 5788 7822).
Error5
ERROR OGG-01224 TCP/IP error 10061 (No connection could be made because the target machine actively refused it.); retries exceeded.
Solution :
1. check both database server golden gate manager process is start
2. check the firwal setting of both the databases.
3. If use different port in extract then specify the following on target server in Mgmt process parameter file:
dynamicportlist 7810-7820
4. Check the ping command for both the source and target machine.
5. Check the entries in hosts file of both source and target name.
Error6
Target server on start of replicate process:
2013-01-23 15:38:45 ERROR OGG-00446 Missing filename opening checkpoint file
Solution :
Added the following line in above of replication process parameter file.
REPLICAT REP1
Error7
ERROR OGG-00446 Checkpoint table ggs_owner.checkpoint does not exist. Please create the table or recreate the REP1 group using the correct table.
Solution :
1. dblogin userid OGG, password OGG
2. Delete CHECKPOINTTABLE GGS_OWNER.CHKPTAB
3. ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB
Error8
ERROR OGG-00403 There can be only one DDL filtering statement. If DDL filter is long, use ampersand (&) sign to continue it on another line.
Solution:
Check the Extract process and replicate process , DDL line having causing some error.Please correct the syntax of DDL line in parameter file.
Error9
2013-01-23 15:05:46 ERROR OGG-00446 Could not find archived log for sequence 33 thread 1 under default destinations SQL <SELECT name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO' AND name not like '+%' AND standby_dest = 'NO' >, error retrieving redo file name for sequence 33, archived = 1, use_alternate = 0Not able to establish initial position for begin time 2013-01-21 15:49:55.
Solution:
alter extract <name> begin now
Error10
ERROR OGG-01224 Address already in use.
Solution:
Use other port
OR
To release the port
/sbin/fuser -k 7809/tcp
Error11
ERROR: opening port for MGR MGR (Connection refused). while stopping mgr process
Solution :
1. Enter into dirpcs directory which is under OGG install directory
2. mv mgr.pcm mgr.pcm.bkp
3. ggsic> Start mgr
4. ggsci>Start extract <extract-name>
Errors occured with 10g database
Error12
[oracle@jzrashjrhel gg]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
Solution :
Softlink needs to be created as below .
[oracle@jzraomnrhel ~]$ cd $ORACLE_HOME/lib
[oracle@jzraomnrhel lib]$ ln -s $ORACLE_HOME/lib/libodm10.so libnnz11.so
[oracle@jzraomnrhel lib]$ ln -s $ORACLE_HOME/lib/libclntsh.so.10.1 libclntsh.so.11.1
[oracle@jzraomnrhel lib]$
Error13
SQL> @/home/oracle/gg/ddl_pin GGS_OWNER
BEGIN dbms_shared_pool.keep('GGS_OWNER .DDLReplication', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('GGS_OWNER .trace_put_line', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('SYS. GGS_DDL_TRIGGER_BEFORE', 'R'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Solution :
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
SQL> @/home/oracle/gg/ddl_pin GGS_OWNER
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Reference:
http://gssdba.wordpress.com/2011/07/12/oracle-golden-gate-tutorial-part-2-step-by-step-installation-of-oracle-golden-gate-one-way-replication/
http://gavinsoorma.com/2010/02/oracle-goldengate-tutorial-6-configuring-data-pump-process/
GG version: 11.2.1.0.1
ORACLE DB Version: Oracle EE 11.2.0.1
OS version: Linux 2.6.18-164.el5 x86_64
IP : 10.44.10.176
Target DB:
GG version: 11.2.1.0.1
ORACLE DB Version : Oracle EE 11.2.0.1
OS version: Linux 2.6.18-164.el5 x86_64
IP : 10.44.10.181
STEP 1 Database Pre requisites
Need to be carried out at both source and target
alter system set log_archive_dest='/oracle/flash_recovery_area' scope=spfile;
alter system set recyclebin=off scope=spfile;
alter system set undo_retention=86400 scope=spfile;
shut immediate;
startup mount;
alter database archivelog;
alter database open;
alter database add supplemental log data;
alter database force logging;
create tablespace ggs_data datafile '/oracle/oradata/orcl/ggs_data01.dbf' size 200m;
create user ggs_owner identified by ggs_owner
default tablespace ggs_data
temporary tablespace temp;
grant connect, resource to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;
grant create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;
STEP 2 GG Software Installation
Need to be carried out at both source and target
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oracle/gg
export LD_LIBRARY_PATH
bash-3.2$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (INCHOMDP005773) 1> CREATE SUBDIRS
Creating subdirectories under current directory /oracle/gg
Parameter files /oracle/gg/dirprm: already exists
Report files /oracle/gg/dirrpt: created
Checkpoint files /oracle/gg/dirchk: created
Process status files /oracle/gg/dirpcs: created
SQL script files /oracle/gg/dirsql: created
Database definitions files /oracle/gg/dirdef: created
Extract data files /oracle/gg/dirdat: created
Temporary files /oracle/gg/dirtmp: created
Stdout files /oracle/gg/dirout: created
STEP 3 Run scripts for creating all necessary objects for support DDL replication
Need to be carried out at source and target
As sys as sysdba run below sqls, provide GGS_OWNER if requested.
--if below sql hangs, change the path in the sql file @ OR invoke SQLPLUS from GG location
@/oracle/gg/marker_setup.sql GGS_OWNER
@/oracle/gg/ddl_setup.sql GGS_OWNER
@/oracle/gg/role_setup.sql GGS_OWNER
grant ggs_ggsuser_role to ggs_owner;
@/oracle/gg/ddl_enable GGS_OWNER
@/oracle/gg/ddl_pin GGS_OWNER
Note- We had earlier enabled additional supplemental logging at the database level.
Using the ADD TRANDATA command we now enable it at even the table level as this is required by Golden Gate for DDL support.
--Login to GGSCI
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
--From SQL generate the below command and run it in GGSCI
select 'add trandata '||owner||'.'||object_name||'' from dba_objects where owner='HR' and object_type='TABLE';
select 'add trandata '||owner||'.'||object_name||'' from dba_objects where owner='HR' and object_type='TABLE';
add trandata HR.TEST
add trandata HR.REGIONS
add trandata HR.LOCATIONS
add trandata HR.JOB_HISTORY
add trandata HR.JOBS_ROWIDTEST
add trandata HR.JOBS
add trandata HR.EMPLOYEES
add trandata HR.DEPARTMENTS
add trandata HR.COUNTRIES
GGSCI (INCHOMDP005773) 10> add trandata HR.TEST
add trandata HR.REGIONS
add trandata HR.LOCATIONS
add trandata HR.JOB_HISTORY
add trandata HR.JOBS_ROWIDTEST
add trandata HR.JOBS
add trandata HR.EMPLOYEES
add trandata HR.DEPARTMENTS
add trandata HR.COUNTRIES
2013-10-07 22:18:32 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 HR.TEST.
GGSCI (INCHOMDP005773) 11>
Logging of supplemental redo data enabled for table HR.REGIONS.
GGSCI (INCHOMDP005773) 12>
Logging of supplemental redo data enabled for table HR.LOCATIONS.
GGSCI (INCHOMDP005773) 13>
Logging of supplemental redo data enabled for table HR.JOB_HISTORY.
GGSCI (INCHOMDP005773) 14>
2013-10-07 22:18:41 WARNING OGG-00869 No unique key is defined for table 'JOBS_ROWIDTEST'. 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 HR.JOBS_ROWIDTEST.
GGSCI (INCHOMDP005773) 15>
Logging of supplemental redo data enabled for table HR.JOBS.
GGSCI (INCHOMDP005773) 16>
Logging of supplemental redo data enabled for table HR.EMPLOYEES.
GGSCI (INCHOMDP005773) 17>
Logging of supplemental redo data enabled for table HR.DEPARTMENTS.
GGSCI (INCHOMDP005773) 18>
Logging of supplemental redo data enabled for table HR.COUNTRIES.
STEP 4 Create and start manager on the source and the destination.
Need to be carried out at source and target
-bash-3.2$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (INCHOMDP005773) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
GGSCI (INCHOMDP005773) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (INCHOMDP005773) 3>
GGSCI (INCHOMDP005773) 3> edit params mgr
Paste the below and save the file (/oracle/gg/dirprm/mgr.prm)
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /oracle/gg/dirdat/ex, USECHECKPOINTS
GGSCI (INCHOMDP005773) 8> start manager
Manager started.
GGSCI (INCHOMDP005773) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
STEP 6 Create the EXTRACT group
Need to be carried out at source
add extract ext1, tranlog, begin now
add exttrail /oracle/gg/dirdat/lt, extract ext1
edit params ext1
extract ext1
userid ggs_owner, password ggs_owner
rmthost 10.44.10.181, mgrport 7809
rmttrail /oracle/gg/dirdat/lt
DDL INCLUDE ALL
table HR.*;
STEP 7 Create CHECKPOINT Table
Need to be carried out at both source and target
GGSCI (INCHOMDP005773)> edit params ./GLOBAL
GGSCHEMA ggs_owner
CHECKPOINTTABLE ggs_owner.Checkpoint
GGSCI (INCHOMDP005773) 2> dblogin userid ggs_owner password ggs_owner
Successfully logged into database.
GGSCI (INCHOMDP005773) 3> add checkpointtable ggs_owner.checkpoint
Successfully created checkpoint table ggs_owner.CHECKPOINT.
STEP 8 Create the REPLICAT group
Need to be carried out at target
cd /oracle/gg/
mkdir discard
GGSCI (DBTARGET) 4>add replicat rep1, exttrail /oracle/gg/dirdat/lt,checkpointtable ggs_owner.checkpoint
GGSCI (DBTARGET) 4> EDIT PARAMS rep1
replicat rep1
userid ggs_owner, password ggs_owner
discardfile /oracle/gg/discard/rep1_discard.txt, append, megabytes 10
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
ASSUMETARGETDEFS
MAP HR.*, TARGET HR.*;
TABLEEXCLUDE HR.SYS_EXPORT_SCHEMA_01
STEP 9 Initial Dataload
Need to be carried out at source
SQL> select TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
------------------------------
2908435
expdp HR/HR directory=data_pump_dir dumpfile=schema_gg1.dmp logfile=schema_gg1.log schemas=HR flashback_scn=2908435
Transfer schema_gg.dmp to target
scp schema_gg1.dmp oracle@10.44.10.181:/oracle/Ora11gdb/admin/orclgg/dpdump/
Need to be carried out at target
impdp HR/HR directory=data_pump_dir dumpfile=schema_gg1.dmp logfile=schema_imp_gg1.log schemas=hr
STEP 10 Start the EXTRACT
STATUS FOR SOURCE
GGSCI (INCHOMDP005773) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 02:23:37
STATUS FOR TARGET
GGSCI (INCHOMDP005891) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 02:00:07
GGSCI (INCHOMDP005891) 12> start extract ext1
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 02:23:37
GGSCI (INCHOMDP005773) 63> send extract ext1 status
Sending STATUS request to EXTRACT EXT1 ...
STEP 11 Start the REPLICAT
GGSCI (INCHOMDP005891) 12> start replicat rep1, aftercsn 2908435
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 02:00:07
Monitor using tail -f ggserr.log
STEP 12 Test Cases
CASE1
create table HR.Employee1(
ID VARCHAR2(4 BYTE) ,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
);
CASE2
Create table test_gg_1(Id number(10),Username varchar2(20));
Insert into test_gg_1 values(1,'USER1');
Insert into test_gg_1 values(2,'USER2');
Insert into test_gg_1 values(3,'USER3');
Insert into test_gg_1 values(4,'USER4');
Insert into test_gg_1 values(5,'USER5');
COMMIT;
CASE3
Update hr.test_gg_1 set username='USERDBA' where id=1;
Commit;
CASE4
Delete from hr.test_gg_1 where id=4;
Commit;
Errors & Solutions
Error1
Could not able to start the extract
Solution
Removed comments in prm file, -- is used for comment
Error2
Table xxx.SYS_IMPORT_FULL_01 does not exist in target database.
Solution
TABLEEXCLUDE HR.SYS_EXPORT_SCHEMA_01 in rep1.prm
Error3Could not able to start the replicat and replicat is ABENDING
Solution
Add this in ASSUMETARGETDEFS rep1.prm
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
Error4
MOS(OGG Extract 11.2 abends with ERROR OGG-01960 Failed to validate table The table is created with the NOLOGGING option, which is not supported. [ID 1465720.1])
Solution
As a temporary workaround, DBOPTIONS ALLOWNOLOGGING can be added after USERID parameter in the extract parameter
file. This parameter will cause the extract to log a warning message and continue to run. However, there is a cha
nce for data loss. Resync in required
ggserr file without errors
2013-10-08 23:10:15 INFO OGG-01677 Oracle GoldenGate Collector for Oracle: Waiting for connection (started dynamically).
2013-10-08 23:10:18 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, MGR.prm: Command received from SERVER on host localhost (REPORT 5788 7822).
Error5
ERROR OGG-01224 TCP/IP error 10061 (No connection could be made because the target machine actively refused it.); retries exceeded.
Solution :
1. check both database server golden gate manager process is start
2. check the firwal setting of both the databases.
3. If use different port in extract then specify the following on target server in Mgmt process parameter file:
dynamicportlist 7810-7820
4. Check the ping command for both the source and target machine.
5. Check the entries in hosts file of both source and target name.
Error6
Target server on start of replicate process:
2013-01-23 15:38:45 ERROR OGG-00446 Missing filename opening checkpoint file
Solution :
Added the following line in above of replication process parameter file.
REPLICAT REP1
Error7
ERROR OGG-00446 Checkpoint table ggs_owner.checkpoint does not exist. Please create the table or recreate the REP1 group using the correct table.
Solution :
1. dblogin userid OGG, password OGG
2. Delete CHECKPOINTTABLE GGS_OWNER.CHKPTAB
3. ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB
Error8
ERROR OGG-00403 There can be only one DDL filtering statement. If DDL filter is long, use ampersand (&) sign to continue it on another line.
Solution:
Check the Extract process and replicate process , DDL line having causing some error.Please correct the syntax of DDL line in parameter file.
Error9
2013-01-23 15:05:46 ERROR OGG-00446 Could not find archived log for sequence 33 thread 1 under default destinations SQL <SELECT name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO' AND name not like '+%' AND standby_dest = 'NO' >, error retrieving redo file name for sequence 33, archived = 1, use_alternate = 0Not able to establish initial position for begin time 2013-01-21 15:49:55.
Solution:
alter extract <name> begin now
Error10
ERROR OGG-01224 Address already in use.
Solution:
Use other port
OR
To release the port
/sbin/fuser -k 7809/tcp
Error11
ERROR: opening port for MGR MGR (Connection refused). while stopping mgr process
Solution :
1. Enter into dirpcs directory which is under OGG install directory
2. mv mgr.pcm mgr.pcm.bkp
3. ggsic> Start mgr
4. ggsci>Start extract <extract-name>
Errors occured with 10g database
Error12
[oracle@jzrashjrhel gg]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
Solution :
Softlink needs to be created as below .
[oracle@jzraomnrhel ~]$ cd $ORACLE_HOME/lib
[oracle@jzraomnrhel lib]$ ln -s $ORACLE_HOME/lib/libodm10.so libnnz11.so
[oracle@jzraomnrhel lib]$ ln -s $ORACLE_HOME/lib/libclntsh.so.10.1 libclntsh.so.11.1
[oracle@jzraomnrhel lib]$
Error13
SQL> @/home/oracle/gg/ddl_pin GGS_OWNER
BEGIN dbms_shared_pool.keep('GGS_OWNER .DDLReplication', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('GGS_OWNER .trace_put_line', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('SYS. GGS_DDL_TRIGGER_BEFORE', 'R'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Solution :
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
SQL> @/home/oracle/gg/ddl_pin GGS_OWNER
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Reference:
http://gssdba.wordpress.com/2011/07/12/oracle-golden-gate-tutorial-part-2-step-by-step-installation-of-oracle-golden-gate-one-way-replication/
http://gavinsoorma.com/2010/02/oracle-goldengate-tutorial-6-configuring-data-pump-process/
