Home » Server Options » Replication » Replicate tables (Oracle 10g Release 10.2.0.4.0)
Replicate tables [message #437080] Wed, 30 December 2009 23:17 Go to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi All,

I have a scenario like, am having two databases DB1 and DB2 in different locations where I need to replicate some of the tables(around 10 to 15 tables) from DB1 to DB2(i.e. Whenever I
update any table in DB1 it has to reflect in DB2.). Both DB1 and DB2 has the same database objects.
(DB version - Oracle 10g Release 10.2.0.4.0).

Please suggest me the steps how this can be done. Can it be done using Materialised View or else guide me for some optimal solution.

Thanks,
Marlon.
Re: Replicate tables [message #437083 is a reply to message #437080] Wed, 30 December 2009 23:22 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
And one more thing, it should be bidirectional.(DB1-> DB2 and vice-versa(DB2->DB1).

Thanks,
Marlon
Re: Replicate tables [message #437084 is a reply to message #437083] Wed, 30 December 2009 23:25 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Search for Publish Subscribe Model.
you can use Db Links ,Advance Queues for the same
Re: Replicate tables [message #437092 is a reply to message #437080] Thu, 31 December 2009 00:53 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi...

Please can you tell which one will be more appropriate and can anyone explain me the steps with an example.

Thanks,
Marlon.

Re: Replicate tables [message #437108 is a reply to message #437092] Thu, 31 December 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not something that can be explained in few lines.
Oracle wrote a whole book on it: Database Advanced Replication

Regards
Michel

[Updated on: Thu, 31 December 2009 03:27]

Report message to a moderator

Re: Replicate tables [message #437128 is a reply to message #437092] Thu, 31 December 2009 03:12 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
one short example
SQL> CREATE DATABASE LINK testlink
  2  CONNECT TO xxmitg IDENTIFIED BY ******
  3    USING 'GITGD';

Database link created.

SQL> create table test
  2  (
  3  a integer
  4  );

Table created.
--connect to other database
SQL> conn xxmitg/******@gitgd

SQL>  create table test
  2   (
  3   a integer
  4   );

Table created.
---connect to first database
SQL> conn ayush/pass

SQL> select * from test@testlink;

no rows selected



SQL>  CREATE or REPLACE TRIGGER testtrigger
  2   after insert on test
  3   FOR EACH ROW
  4   
  5   declare
  6   v_int number;
  7   
  8   begin
  9   select a into v_int from test@testlink where  a=:new.a;
 10   exception
 11   when NO_DATA_FOUND  then
 12   insert into test@testlink values(:new.a);
 13   end;
 14   /

Trigger created.

SQL> select * from test;

no rows selected

SQL> select * from test@testlink;

no rows selected

SQL> insert into test values(1);

1 row created.

SQL>  select * from test;

         A
----------
         1

SQL> select * from test@testlink;

         A
----------
         1


Also I Heard there are some ETL tools for achieving the same purpose like Oracle Data Integrator

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6985569854826

[Updated on: Thu, 31 December 2009 03:34]

Report message to a moderator

Re: Replicate tables [message #437131 is a reply to message #437128] Thu, 31 December 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very short example that does not cover the fact it is a 2-way replication, how to handle if the insert is due to direct insert or to replication and how to handle conflicts.

Regards
Michel

[Updated on: Thu, 31 December 2009 04:33]

Report message to a moderator

Re: Replicate tables [message #437138 is a reply to message #437131] Thu, 31 December 2009 03:37 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1213436164382

Donot reinvent the wheel and hear what Michel says Smile
Re: Replicate tables [message #437188 is a reply to message #437092] Thu, 31 December 2009 15:51 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Try This:

create table emp1 as ( select * from emp)

Re: Replicate tables [message #437203 is a reply to message #437188] Fri, 01 January 2010 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You win the most stupid answer of the year award! /forum/fa/1940/0/

Regards
Michel

[Updated on: Fri, 01 January 2010 01:54]

Report message to a moderator

Re: Replicate tables [message #437467 is a reply to message #437203] Mon, 04 January 2010 10:21 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Why do you think so?

Did you read the question, before commenting on the answer.

The dummest man is one who think he is brilliant.
Nod
Syed
Re: Replicate tables [message #437470 is a reply to message #437467] Mon, 04 January 2010 10:43 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course I read it but maybe you don't understand it.
Replication does not mean one-shot copy.

Regards
Michel

[Updated on: Mon, 04 January 2010 10:43]

Report message to a moderator

Previous Topic: How to replicate
Next Topic: Materiliezed View Replication Problem
Goto Forum:
  


Current Time: Thu Mar 28 12:50:06 CDT 2024