Goldengate unidirectional with errors and solutions

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/