All entries for Thursday 23 February 2012

Instantiate table in Oracle Streams

Writing about web page http://krish-dba.blogspot.com/2009/01/re-synchronizingrefresh-table-in.html

Credit to this article

1. Stop Capture
EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'CAP_HSUN');

2. Stop propagation
EXEC DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROP_HSUN',FORCE=>TRUE);

3. Stop Apply Process on Target(T)

EXEC DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'AAPL_HSUN');

4. Delete the apply errors on Target

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('AAPL_HSUN');

5. Truncate table 

ALTER TABLE  UWTABS.UW_TPRS ADD CONSTRAINT "C_TPRS_1" PRIMARY KEY ("TPRS_CODE");
Truncate table UWTABS.UW_TPRS

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

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('UWTABS.UW_TPRS');

DECLARE iscn NUMBER; 
BEGIN iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); 
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@target.warwick.ac.uk(
 source_object_name => 'UWTABS.UW_TPRS', 
 source_database_name => 'source.warwick.ac.uk', 
 instantiation_scn => iscn);
END;
/

7.
Synchronize the problem table through datapump exp/imp

8. Start the Apply Process on Target(T)

EXEC DBMS_APPLY_ADM.START_APPLY(apply_name => 'AAPL_HSUN');

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

EXEC DBMS_PROPAGATION_ADM.START_PROPAGATION('PROP_HSUN');

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

EXEC DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'CAP_HSUN');

Hongfeng Sun : 23 Feb 2012 10:30 |  Tags: Oracle |  Comments (0) |  Close comments |  Report a problem