Home » Server Options » Replication » replication for dual schemas in same database (9i)
replication for dual schemas in same database [message #561095] Wed, 18 July 2012 15:27 Go to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi all. I was asked for ideas to do a certain type of replication. I still have questions out to people for why they want to do this; eg. what problems are they trying to solve etc. But in the mean time I would like to hear from anyone who has actually done this before so that I can evaluate what has worked for you. I have been asked about:

creating two schemas in the same database which are duplicates of each other.  One schema will be live and the other readonly.  A reporting process will read off the readonly schema while changes are made to the live schema.

(so far so good, but here is the catch):

At some point, the application wants to swap schemas.  This means making schema A readonly, then resynching schema B to schema A, then making schema B the live schema.  the assocatied applications will now do their things by swapping their use of the schemas.
This sounds unusual to me. I can't figure out at the moment the value of "swapping" so I have asked that question to my people here at work as to why they think they need this.

In the meantime I am asking you, has anyone done this? If so how did you achieve it? What would you like to offer up as significant observations about your solution, particularly if you felt is did what it was supposed to do (which was what by the way?)?

I am familiar with all of the following:

materialized views
master/master replication

CDC
SHAREPLEX
GOLDEND GATE

home grown trigger based replication
historical schemas
I am really again asking for your experiences with this dual schema design.

Thanks, Kevin

[Updated on: Wed, 18 July 2012 15:28]

Report message to a moderator

Re: replication for dual schemas in same database [message #561112 is a reply to message #561095] Thu, 19 July 2012 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Considering your sentence "At some point, the application wants to swap schemas. This means making schema A readonly, then resynching schema B to schema A, then making schema B the live schema." I think about exp/imp.

If schemas must be always (almost) in sync then, given your version, I will go to master/master replication. Read-only mviews way is possible but means a long downtime to swap schema roles.

(By the way, I don't think CDC was available in 9i.)

Regards
Michel
Re: replication for dual schemas in same database [message #561200 is a reply to message #561112] Thu, 19 July 2012 08:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Michel for the quick reply.

Yes, I have the same thoughts so far. Was hoping someone had actual experience with this problem and a confirmed way to handle it.

CDC is in fact available in 9i. I use it. But my company does not like it. We use the asynchronous version (logminer approach) and continuously have issues with it so no one wants to go that way.

Kevin
Re: replication for dual schemas in same database [message #561201 is a reply to message #561200] Thu, 19 July 2012 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree with you about Log Miner approch. we tested Streams in 9i and there were many problems especially with bulk updates/inserts (and other ones you will not encounter in your case related to transformations on the fly).

On anoher side, one of my clients has used for years Sybase Replication Server with this version of Oracle to replicate a reference schema to many using reference databases and was happy. The time lag is generally less than one minute but the updates on the reference schema were not numerous. Now there is the cost side...

Regards
Michel
Re: replication for dual schemas in same database [message #575143 is a reply to message #561201] Sun, 20 January 2013 02:31 Go to previous messageGo to next message
qwerty12345678910
Messages: 3
Registered: January 2013
Location: Philippines
Junior Member
Hi All.

What would it be possible to replicate same schema on the same database?
existing schema name is card and want to create another schema on the same database namely card_bos having the same DDL and objects just like the existing card schema.


Can anyone help me please. Thank You
Re: replication for dual schemas in same database [message #575145 is a reply to message #575143] Sun, 20 January 2013 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
expdp + impdp with remap_schema option

Regards
Michel
Re: replication for dual schemas in same database [message #575146 is a reply to message #575145] Sun, 20 January 2013 03:08 Go to previous messageGo to next message
qwerty12345678910
Messages: 3
Registered: January 2013
Location: Philippines
Junior Member
Thanks for the quick reply Sir Michael. Smile

is it possible to migrate an existing schema to new schema? and the new schema should have all the objects of the existing schema?
Re: replication for dual schemas in same database [message #575148 is a reply to message #575146] Sun, 20 January 2013 03:27 Go to previous messageGo to next message
qwerty12345678910
Messages: 3
Registered: January 2013
Location: Philippines
Junior Member
Thanks for the quick reply Sir Michael.

is it possible to migrate an existing schema to new schema? and the new schema should have all the objects of the existing schema?
Re: replication for dual schemas in same database [message #575149 is a reply to message #575148] Sun, 20 January 2013 03:41 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Previous Topic: Goldengate
Next Topic: update database on daily basis..
Goto Forum:
  


Current Time: Thu Mar 28 13:14:28 CDT 2024