Re-Synchronize/Refresh a Table in Streams Environment

Purpose:

The purpose of this article is to outline the steps for Re-Synchronize/Refresh a single table if it is out of sync with source site in one-way replication between two ORACLE databases in Streams environment.

Example:
Schema:SCOTT
Table:EMP

Steps for Refreshing a Table

If one of the tables on the Target site is out of Sync with Source site then follow below steps to re-synchronize both:

1. Stop Capture Process on Source(S)
2. Stop Propagation Process on Source(S)
3. Stop Apply Process on Target(T)
4. Delete the apply errors on Target(T)
5. Truncate the problem table on Target(T)
6. Re-instantiate the problem table on Source(S)
7. Synchronize the problem table through exp/imp
8. Start the apply Process on Target(T)
9. Start the propagation Process on the Source(S)
10.Start the Capture Process on the Source(S)

1. Stop Capture Process on Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL> BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/

2. Stop Propagation Process on Source(S)

Make sure all the changes are propagated to the Target site, then stop the propagation process on source using following sql

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL>BEGIN
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
'STREAMS_PROPAGATE',FORCE=>TRUE);
END;
/

3. Stop Apply Process on Target(T)

Make sure all changes are applied on the target, then stop apply process on target using following sql

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

SQL> BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'STREAMS_APPLY');
END;
/

4. Delete the apply errors on Target

As we will re-synchronize, we can delete the errors that are in the error_queue of the apply process. To do so, we can execute either DBMS_APPLY_ADM.DELETE_ERROR or DBMS_APPLY_ADM.DELETE_ALL_ERRORS at the apply site.

You need to make sure that you are not deleting any needed transaction, specially if you are not synchronizing all the replicated for that apply process.

Target(MYDEV.ORACLEDB2.KRISH.COM):

Check the errors in the error queue:

SQL> select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;

To delete specific transaction:

SQL> exec DBMS_APPLY_ADM.DELETE_ERROR('transaction_id');

To delete all errors:

SQL> exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS('apply_name');

5. Truncate the problem table on Target

Truncate the problem table if you are using traditional export/import for copying the data.

If you are using datapump exp/imp then ignore step 5 and go to step 7(use parameter TABLE_EXISTS_ACTION=truncate)

Target(MYDEV.ORACLEDB2.KRISH.COM):

Connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

SQL> Truncate table SCOTT.EMP;

6. Re-instantiate the problem table on Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN

DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.EMP');

END;

/

DECLARE iscn NUMBER; BEGIN iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM ( source_object_name => 'SCOTT.EMP', source_database_name => 'ORCL.ORACLEDB1.KRISH.COM', instantiation_scn => iscn);

END;

/

7. Synchronize the problem table through datapump exp/imp

Now synchronize the problem table on source and target so they are identical, Here we are copying the data through DP exp/imp from source to target.

6a) Export the source Table:

For example we are replicating table EMP of SCOTT, so we should do the following:

Source(ORCL.ORACLEDB1.KRISH.COM):

expdp parfile=expdp.par

expdp.par

userid=system/pwd@ORCL.ORACLEDB1.KRISH.COM
tables=SCOTT.EMP
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:expdpScott.log
JOB_NAME=exp_job
CONTENT=DATA_ONLY

6b) Import the source table to target:

Target(MYDEV.ORACLEDB2.KRISH.COM):

impdp parfile=impdp.par

impdp.par

userid=system/pwd@MYDEV.ORACLEDB2.KISH.COM
tables=SCOTT.EMP
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:impdpScott.log
JOB_NAME=imp_job
TABLE_EXISTS_ACTION=truncate

8. Start the Apply Process on Target(T)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

SQL> BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STREAMS_APPLY');
END;
/
Make sure apply process is started

SQL> select apply_name, status from dba_apply;

9. Start the propagation Process on the Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL> BEGIN
DBMS_PROPAGATION_ADM.START_PROPAGATION('STREAMS_PROPAGATE');
END;
/

10. Start the Capture Process on the Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL> BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/

Make sure capture process is started

SQL> select capture_name, status from dba_capture;

Conclusion:

The Target database is not up to date till the refresh is completed.