oracle question

[Michael Murphy_2]

Had a question about oracle database - posted to the database forum, but posted here as well just in case. Anyone had experience with the following: we have data that we want to move into two seperate oracle instances (with different schemas). We want to keep the schemas in sync - however one would be OLTP and one would be more reporting. What would be the best tool to keep the schemas in sync (data guard? replication?)It seems like these tools are more for keeping databases in sync when they are under the same schema. Or is there a better way to do this?

[R. Allan Hicks]

You are correct. Advanced replication is used to replicate selected database objects in the same schema and the dataguard product keeps an entrie database in sync.

If you did not have the requirement for different schemas, you could use advanced replicaton or dataguard using a logical database.

Replication can be used for reporting and/or load balancing.

Logical standby databases can be used for reporting.

The only advice I can offer if the different schema is an absolute requirement, would be to write triggers to update the reporting database. This can be a major pain, so think hard about the different schema requirement.

If you write your own consider:

1. The case when the reporting database is down. What will your triggers do if they can't update the remote table?

2. How will you do the initialize sync of the database objects? The hardest part of replication is to get the databases back into sync without chewing up alot of resources and/or taking the primary database down. You'll have to solve this problem on your own if you write triggers.

Can you use synonyms to meet the different schema requirements? A.table is the OLTP table and B.table is the reporting table. Create synomyn b.table for a.table? Then lock account a to keep anyone from messing with the schema (good practice anyway). The replication keeps the a.tables in sync between databases. All of your reports are against b.table. So, for all practical purposes they are in different schemas.

I started with the write your own approach. I used triggers and sequences to generate primary keys and found some problems doing it with triggers and still maintain a primary key across the databases. I made one machine use odd sequence numbers and the other use even.

Later, I discovered advanced replication. I used it for about 6 months. It worked fine, but I still had issues with sequence numbers on the different machines. I also had trouble resyncing the databases if one of the machines went off-line for a period. If your trigger attempts to update a remote table, your application is blocked until the remote table updates or the link times out. The result is you've more than erased your performance gains by spliting out the reporting function. Advanced replication sets up job queues so that the changes are spooled up while the remote database is unavailable. (Provided you selected async replication. Synchronous replication will put your instance into a 'polite' wait until the remote machine processes the update.)

However, I wanted the user accounts and the data dictionary objects updated. I went to dataguard to accomplish that.

In all of the above cases, the goal was to have a hot standby database which is a little different from your goal.

You may want to read Chapters 14 and 15 of Oracle Built-in Packages by Steven Feuerstein, Charles Dye and John Beresniewicz published by O'Reilly ISBN 1-56592-375-8

BTW don't forget to send Larry Ellison your extra licensing money.

--Good Luck