Before we start let’s talk What is Oracle Goldengate & what is the use for that, for short :
Oracle GoldenGate is a software product that allows you to replicate, filter, and transform data from one database to another database. It enables the replication of data between Oracle databases and other supported heterogeneous databases.
If you are using oracle golden gate(OGG) and don’t be shocked when you see the following error:
2014-01-27 11:51:19 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, REP1.prm: REPLICAT REP1 started.
2014-01-27 11:51:19 WARNING OGG-03504 Oracle GoldenGate Delivery for Oracle, REP1.prm: NLS_LANG character set WE8MSWIN1252 on the target is different from the source database character set AL32UTF8. Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set.
2014-01-27 11:51:19 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, REP1.prm: Aborted grouped transaction on 'TEST.CT1', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE "TEST"."CT1" SET "OBJECT_NAME" = :a1 WHERE "RNO" = :b0>).
2014-01-27 11:51:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, REP1.prm: Repositioning to rba 1216 in seqno 2.
2014-01-27 11:51:19 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, REP1.prm: SQL error 1403 mapping CTEST.CT1 to TEST.CT1 OCI Error ORA-01403: no data found, SQL <UPDATE "TEST"."CT1" SET "OBJECT_NAME" = :a1 WHERE "RNO" = :b0>.
2014-01-27 11:51:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, REP1.prm: Repositioning to rba 1216 in seqno 2.
2014-01-27 11:51:19 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, REP1.prm: Error mapping from CTEST.CT1 to TEST.CT1.
2014-01-27 11:51:19 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, REP1.prm: "PROCESS ABENDING".
And your replication is not working and cannot be started so i would suggest follow the steps below to fix your issue.
- Connect via ssh to the source Database
export ORACLE_SID=${ORACLE_SID}
- You have to find you Flachback system change number(FLASHBACK_SCN).
$ sqlplus / as sysdba
$ SQL> select dbms_flashback.get_system_change_number from dual or select current_scn from v$database;
GET_SYSTEM_CHANGE_NUMBER
------------------------
17262230 --ID is your select result
$ SQL> exit
- use exp/imp tool to export data
Note : use FLASHBACK_SCN number which you query from step 2.
$ exp system@${ORACLE_SID} owner=ABEND_process file=ABEND_process.dmp grants=n statistics=none triggers=n compress=n FLASHBACK_SCN=17262230 log=ABEND_process.log
- Copy the dump to Staging Database
$ scp /path/to/Dump_file user@staging_DB:/path/to/folder
- connect via ssh to the staging Database
$ export ORACLE_SID=${STAGING_ORACLE_SID}
- Drop Tables
$ sqlplus / as sysdba
$ SQL>set linesize 999
$ SQL>set pagesize 999
$ SQL>select 'drop table '||owner||'.'||object_name||' cascade constraint;' from dba_objects where object_type='TABLE' and owner in ('ABEND_process');
Note : Make sure you execute the drop generated script for ABEND_process.
- Importing Dump File Change Directory to the Dump directory
$ imp system@${ORACLE_STAGING_SID} file=ABEND_process.dmp fromuser= ABEND_process touser= ABEND_process ignore=y log=ABEND_process.log
- Disable Foreign key
$ sqlplus / as sysdba
$ SQL>set linesize 999
$ SQL>set pagesize 999
$ SQL>select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where owner in ('ABEND_process') and constraint_type='R';
$ SQL>exit
Note : Make sure you execute the Disable constraint generated script for ABEND_process.
- Go to stg_goldengate_home
$ ./ggsci
$ SQL>START RP_ABEND_process aftercsn $FLASHBACK_SCN
$ SQL>exit