User name: Cerberus
Registered on 13 Jul 07
Posts: 2
|
Posted 11 Nov 08 05:04
This spring I was charged with developing a replication using Oracle streams. The mission critical 24x7 production database was 400GB and run on two node RAC. The DB must be replicated to an auxiliary RAC in different city. I can not name the client company of mine, but the cities are LA and Denver. No downtime allowed during build of streams. Streams must be bidirectional. Not all tables are replicated so schema level streams are no good. Multiple tables from multiple schemas to be replicated.
Other technology challenges: LA RAC runs on ASM and Oracle 10.2.0.3 while Denver RAC runs on ocfs with the same version. Application is very complex doing lots of inserts/updates per second and running many dbms jobs besides.
First I build the streams pretty much by the book. Not that there is a book to use because there is none. Instead there are bad manuals and multiple, contradicting each other whitepapers on metalink. White papers on metalink about streams – different versions, different scenarios and all buggy. I did not found a single one which was clear for me. Perhaps I miss something fundamental, but doing oracle since 1989 (as I do) I think not.
So after combining multiple white papers into a single sequence of non-contradicting steps, I got my own “manual” which I followed and streams worked. That was easy I thought. But than they broke. And could not be fixed/re-started again. So I proceed to removing them in order to rebuild. And failed to remove either. The EXEC DBMS_streams_adm.REMOVE_STREAMS_CONFIGURATION never works. Not on my production database. Perhaps the database was build incorrectly, (I am sure it was) but it was before me.
Anyway, drop user cascade does the removal trick just fine.
The separate challenge was presented by existence of the complex functional indexes – done that too and had to upgrade oracle as well plus develop a workarounds even after upgrade.
Anyway 3 months later and multiple scenarios and approaches, I have developed my own sequence of steps for Creation, Monitoring and Maintenance of streams. Including removal if needed. And RAC to RAC bidirectional. Short result – streams work. Solid and do not break.
Special note about initial synchronization – since no down time was allowed, I had two options – using export/import (actually datapump) and make sure I keep the starting change number SCN so streams will catch-up, or another synchronization option standby. On the big databases (mine was 300GB) synchronization with data pump – not a good idea. Takes too long and SCN fells way behind. So I used a standby method as only possible. How to convert from standby to streams? – Well there is not single document about it. But I have developed a repeatable method.
Did anybody every build a standby on ocfs for the prime being on ASM? – well I had to develop a method to do that as well. I also made it switchable via data guard at some point but than client’s business no longer needed that functionality. (replaced by streams). There is no oracle papers on this kind of standby configuration either.
One more point – no enterprise manager was used. Scripts and scripts only. The best tool for oracle is still sqlplus.
|
Reply
|
