Streams Table Instantiation Rules

 Streams Table Instantiation Rules

• If your streams configuration one-way, table-level; Source to Destination replication, you have to instantiate objects at Destination site.
• If your streasms configuration bi-directional, table-level;  Source to Dest and Dest to Source,  you have to instantiate objects both Source and Destination databases.
• If your streams configuration one-way,schema level;  Source to Destination replication, you have to instantiate SCHEMAS at Destination site.
• If your streasms configuration bi-directional, schema level;  Source to Dest and Dest to Source,  you have to instantiate SCHEMAS both Source and Destination databases.

1. If you forget to instantiate tables or schemas you will get this error message.

ORA-26687: no instantiation SCN provided for "HR"."EMPLOYEE" in source database "SOURCE" ;

2. You could check your error messages from  DBA_APPLY_ERROR  view.  You must control this view attentively especially these columns:

 

ERROR_MESSAGE column,
ERROR_CREATION_TIME column,
LOCAL_TRANSACTION_ID column

SELECT * FROM DBA_APPLY_ERROR where error_message like '%ORA-26687%';

 NOTE : save your error messages before re-execute the transactions. You could save dba_apply_error to excel file.

3. Check the tables or schemas that are really not instantiated  Follow the instructions above and check out the rules.

select * from DBA_APPLY_INSTANTIATED_OBJECTS where source_object_name='EMPLOYEE' AND source_object_owner='HR'

Select * from DBA_APPLY_INSTANTIATED_OBJECTS@SOURCE where source_object_name='EMPLOYEE' AND source_object_owner='HR' ;


4. Stop the Streams Services  on both side. Because replication cause a lock on objects and if its still try to working we couldnt instantiate the objects.

• You could stop the service Oracle Enterprise Manager Console , Click the Data Movement and Under Streams Click Manage Replication. DISABLE ALL SERVICES.

        OR

•    You could stop the services with STRMADMIN account with this script.

exec dbms_capture_adm.stop_capturE(capture_name=>'SOURCE');
exec dbms_apply_adm.stop_apply (apply_name=>'APPLY_SOURCE');
exec DBMS_PROPAGATION_ADM.STop_PROPAGATION(propagation_name=>'PROP_SOURCE');

5.  Instantiating objects with this script table-level. IF you want to instantiate schemas you could use DBMS_APPLY_ADM.set_schema_instantiation_scn procedure.

 

DECLARE
v_scn NUMBER;

BEGIN
v_scn := DBMS_FLASHBACK.get_system_change_number@SOURCE ();

DBMS_APPLY_ADM.set_table_instantiation_scn (

source_object_name => 'HR.EMPLOYEE',

source_database_name => 'SOURCE',

instantiation_scn => v_scn);

END;

/

 

6. Back sto Step 2 and check the error messages which are getting  no instantiation SCN provided error message. Execute these messages from LOCAL_TRANSACTION_ID  with this script until no rows retuned  from DBA_APPLY_ERROR.


exec DBMS_APPLY_ADM.EXECUTE_ERROR ('local_transaction_id');

7. Check the invalid objects and compile them.