How to keep two databases in sink [message #437642] |
Tue, 05 January 2010 05:59 |
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 #437694 is a reply to message #437642] |
Tue, 05 January 2010 10:09 |
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 #437697 is a reply to message #437696] |
Tue, 05 January 2010 10:41 |
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.
|
|
|
|
|