Oracle Streams problems ... Check the Alert Log first!

Some days I just don’t have luck.
Whatever I tried to do, I couldn’t achieve it. But, fortunately it is only one day and not whole day :-).
I was investigating Oracle Streams whole month. I got good results and everything seemed to be going so well until, I decide to add new destination site in my Oracle Streams environment.
I was wondering why all those people on forums have issues with Oracle Streams, it is much better than Advanced Replication, and with Oracle 10g R2 you can use OEM to monitor your Streams environment.
One thing what bothered me, while I was learning and investigating streams, was lack of resources and practice examples how to setup streams environment. There is only one book about Oracle Streams available “Oracle Streams - High Speed Replication and Data Sharing” by Madhu Tumma., and off course I bought it. I found some good information there, but most of the scripts what I found inside were not so helpful, because the information what I got from them can be found very easy using some dictionary views.
Also, there is no chapter about how the existing Oracle Streams environment can be extended with new destination database.
No, don’t get me wrong, I don’t like to criticize this book, it is quite good for a beginner to understand Oracle Streams.
So, I continued to find the way to add another database to my destination databases set.
I created new propagation process in the source database.
I crated streams administrator in the destination database, instantiated all tables, created apply handlers and apply process.
But, when I started the apply process, the changes from the source database were not being applied to the target tables.
The first thing what I got in mind was to check whether some errors occurred in some of the processes (capture, propagation, apply). But, I didn’t find any errors there. I thought, here comes the difficult part.
I checked the propagation jobs aq$_schedules. I notice that all propagation jobs have LAST_RUN value, except the propagation job for the new database. Oh, there you go, I said, there my problem is.
I checked everything related to the propagation process, I was dropping and re-creating it several times. Nothing happened…still nothing.
I was searching on the net, trying to find something related to the problem I have, but unfortunately except many old posts (most of them related to Oracle Streams for Oracle 9i) I didn’t find anything useful.
I open the OEM, and tried to monitor the Streams process from there. I notice that the propagation process is propagating messages. Then I decide to check and monitor Apply process. I saw messages are arriving, but nothing being applied.
This is good, this is progress, I thought and continued to check all components of the apply process (queue, queue tables, handlers, etc).
I spent almost whole day checking, researching, investigating, and trying to figure out what is the reason for my issue. During that time, I notice many OEM “bugs” related to Streams monitoring features: if one propagation process is disabled, there is no way to enable it again if it is not first listed in the web page :-); you can not create propagation rule if some of the global names are longer than 30 characters :-):-) .

Finally, I decide to check the alert log of the destination database, and I found this:

knllgobjinfo: MISSING Streams multi-version data dictionary!!!

I found that I that the tables at the source database are not instantiated.
I did it using DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION, after that re-instantiated the tables in the destination database, and that was it … It worked.

TIP:
Check your alert log first, anytime when you have a problem with your Oracle Streams environment, and that problem is not a recognized error stored in DBA_CAPTURE, DBA_PROPAGATION or DBA_APPLY_ERROR views.

However, these are the steps what should be taken if you want to add new destination site in your Oracle Streams environment:

1. Stop Capture Process
2. Create Propagation Process, add propagation rules for all tables which are going to be replicated.
3. Create Streams Queue for receiving changes from source queue
4. Create AQ Agent and enable its privileges on STREAM ADMINISTRATOR
5. Add the AQ Agent as subscriber to Streams Queue
6. Create a dml handler procedure for DML statements issued on the tables
7. Set the DML Handlers for each table in the environment
8. Create apply process for the destination database
9. Set instantiation SCN for tables at the source database
10. Set instantiation SCN for tables at the destination database
11. Run apply process
12. Run capture process

For the end, I must say that from my perspective Oracle Streams are much better and easier to work with than Materialized Views (Advanced Replication).

But, nothing is perfect …

Thanks,
Mihajlo Tekic