February 23, 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');

- No comments Not publicly viewable


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXI