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.
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.
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;
/
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;
/
exec DBMS_APPLY_ADM.EXECUTE_ERROR ('local_transaction_id');
7. Check the invalid objects and compile them.
