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)
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;
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
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:
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.
Posts
