We implemented Oracle Streams downstream capture in our PROD environment. Source and Target Databases are Oracle 11gR2.
The source database is a two node RAC. The target DB is a standalone DB.
We are doing table level replication.
When we implmented streams with this configuration in Production, we got "ORA-26687 No Instantiation SCN provided for Table" errors for all the tables.
We implemented streams with the same configuration in TEST environment and we did not have any problem.
When I compare the differences in the TEST and PROD environment/configuration, I can think of only one difference.
When we deployed it in PROD, the DBA of the source database had shutdown the RAC listener (Main listener on port 1521) to prevent anyone from accessing the DB while streams is being implemented. We were forced to use listener on another port (1523) to create database links.
When we deployed it in TEST environment, all the listeners (portv1521 and 1523) were up and runnig as no one was accessing the environment. We used port 1521 to create the DB link.We used the listener on port 1521 to connect to the DB.
Also, in PROD environment, we had some problems with the archive log shipping. We dropped the database link ( created using port 1523) and created a new one using port 1521 and then the archive logs started shipping to the shared drive.
My questios is: Could the main listener and or port 1521 not being up/available have anything to do with the errors we got?
ORA-26687 indicates that the apply does not know the scn of source table at which the target table was instantiated. If you used datapump to instantiate the data from the source to the target database the instantiation scn should have been set automatically as part of the import. The datapump export/import should take place after the capture is created and the rules are added to capture for those tables to be replicated.
If you used some other method to instantiate the data at the target database, then hopefully you know the source scn at which you copied the data. Then, using that scn you use the dbms_apply_adm.set_table_instantiation_scn to let the apply know the source table scn. This is how Streams identifies the point at which to start applying changes.