Home » Server Options » Data Guard » How to keep two databases in sink (Oracle 10G)
How to keep two databases in sink [message #437642] Tue, 05 January 2010 05:59 Go to next message
kskever
Messages: 2
Registered: January 2010
Location: chennai
Junior Member
Hi all,

I need a clarification for the below query.

I have a database which is for production server.

I need to create one more database which will act as disaster server (i.e once database 1 goes down or corrupted my 2nd database should work.. atleast in another url but the datas in database 2 should be same as database1).
So what i need is whatever changes im making in database 1 should reflect database 2.

How to achieve this? Do i need to create a dblink? In my under standing DB link is used only for accessing the table from another db, but i want the changes should get reflected into another db.

Pls help me.

Regards,
Selva.
Re: How to keep two databases in sink [message #437661 is a reply to message #437642] Tue, 05 January 2010 07:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Do you think this is the Oracle ApEx problem?

regards,
Delna
Re: How to keep two databases in sink [message #437678 is a reply to message #437642] Tue, 05 January 2010 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at standby database and Data Guard.

Regards
Michel
Re: How to keep two databases in sink [message #437694 is a reply to message #437642] Tue, 05 January 2010 10:09 Go to previous messageGo to next message
palazzi
Messages: 11
Registered: June 2009
Location: Toluca
Junior Member
Hi

This is not an apex problem but it doesn't matter.

You can use a db link for almost all operations in a database like insert, update, delete, etc.

For this case you can create a db link and a trigger, for example in your primary db "db1" you have a table called "table1", in your second db "db2" you have a table called "table2" owned by "user2"

First you must create a db link in "db1" to "db2":
CREATE DATABASE LINK link_db2 CONNECT TO user2 IDENTIFIED BY password;

After that, you can create a trigger on table1 after insert for example:
CREATE TRIGGER trig
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
INSERT INTO table2@link_db2 VALUES(:new.b, :new.a);
END trig;

Note the use of the db link to complete the insert on table2
INSERT INTO table2@link_db2 VALUES(:new.b, :new.a);

You have to create one or various triggers on every table, and could be a hard work for a large database.

There are other posibilities like data guard or oracle streams but the solution with links and triggers is a good candidate for small databases.

Saludos.
Re: How to keep two databases in sink [message #437696 is a reply to message #437694] Tue, 05 January 2010 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database link with triggers is a bad solution.
What happens if your backup database is down? Your primary no more works.

Regards
Michel
Re: How to keep two databases in sink [message #437697 is a reply to message #437696] Tue, 05 January 2010 10:41 Go to previous messageGo to next message
palazzi
Messages: 11
Registered: June 2009
Location: Toluca
Junior Member
Hi

That's why I stressed that is a good candidate for small environments, you can manage exceptions in order to have a robust mechanism, and this solution does not provide failover just copy the changes made in the database.

Obviously the best way is data guard or oracle streams but i understand you cannot use those techniques for example in an express edition.

Saludos.
Re: How to keep two databases in sink [message #437699 is a reply to message #437697] Tue, 05 January 2010 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use standby database (without dataguard) with express edition.

Small environment does not mean that you (or your client) accept to have database downtime just because the backup is offline.
Actually, if you need a backup ready to take the place of a primary this mean you accept a short database downtime and of course you don't accept downtime due to backup solution.
Just my experience of clients.

Regards
Michel
Re: How to keep two databases in sink [message #438521 is a reply to message #437642] Sun, 10 January 2010 03:55 Go to previous message
kskever
Messages: 2
Registered: January 2010
Location: chennai
Junior Member
Thanks for all who gave their valuable comments. Actually now i planned to use data guard since it robust.
Once again thanks to all.. will keep u bugged for more clarifications
Previous Topic: Testing Data Guard Implementation
Next Topic: Redo Transport Error
Goto Forum:
  


Current Time: Thu Mar 28 05:00:33 CDT 2024