OraFAQ Forum: Replication » Replication of table for DML from source to destination DB

Home » Server Options » Replication » Replication of table for DML from source to destination DB (oracle 11g ,Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replication of table for DML from source to destination DB [message #488773]

Tue, 11 January 2011 02:29

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

As per requirement, I want to replicate single table data/modification from source db to destination DB.

Can someone tell me the procedure or steps how to create replication between source and dest db for single table.

pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488790 is a reply to message #488773]

Tue, 11 January 2011 02:58

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Materialized view?
Streams?
Logical Standby?
Golden Gate?
Sybase Replication Server?
Schedule export/import?
Transportable tablespace?
...

All depend on many information you didn't give like source and target platforms, source and target OS and version, source and target oracle version, frequency of replication, lag you admit between both tables, total number of rows, number of modified/inserted/deleted rows per day/hour/minute...

Regards
Michel

[Updated on: Tue, 11 January 2011 03:00]

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488799 is a reply to message #488790]

Tue, 11 January 2011 04:05

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

As you needed

Both the source and destination will have oracle 11g (11.1) ,windows 2003 server ,replication at all dml level on table, total number of rows is in source table is very high, number of rows 6000/min is currently inserting (approx).

Thankx

Pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488800 is a reply to message #488799]

Tue, 11 January 2011 04:14

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Admissible lag between source and target?
Distance between source and target?
Network bandwidth between both?

Regards
Michel

[Updated on: Tue, 11 January 2011 04:14]

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488801 is a reply to message #488800]

Tue, 11 January 2011 04:31

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

I m not clear with "Admissible lag" . Network is same for suurce and destination db.

Thanx

Pradep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488802 is a reply to message #488801]

Tue, 11 January 2011 04:34

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How much time do you accept the target can be late from the source?

Regards
Michel

[Updated on: Tue, 11 January 2011 04:35]

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488805 is a reply to message #488800]

Tue, 11 January 2011 04:39

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

It can be at daily time rate because MIS Reporting will be done at destination db.

pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488807 is a reply to message #488805]

Tue, 11 January 2011 04:42

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In this case, a simple export/import scheduled each day should do the trick with minimal impact on source db and server.

Regards
Michel

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488809 is a reply to message #488807]

Tue, 11 January 2011 04:50

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

We are planning this to a single server right now and export import will not solve our issue.

1. export and import is a manual job.
2. Some DSS is going on the same tables.
3. Currently we are working with single DB then we have to work with other db's also.
4. Currently we are working with single table, if this works then we will try to replicate most of the tables where report purpose is going on.

thanks

Pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488816 is a reply to message #488809]

Tue, 11 January 2011 05:17

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I give answer from the information you give. If you hide some information then you have inapropriate answer.

1. It is manual unless you script it.
2. And then?
3. If it is as source then use Streams. If it is as target then this does not change the answer.
4. If you have to replicate most tables then use logical standby.

If I ask questions to give you a solution there are reasons. If you say you have one table and actually you have dozen then don't be surprised you have not a solution that works for you.
If you say your doctor you have a flu when you have yellow fever do you think you will have the correct remedy?

Regards
Michel

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488824 is a reply to message #488816]

Tue, 11 January 2011 06:01

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

In Fact My Senior told me to the Learn about replication. we want to impleament this for most of servers. first about single direction then bi dierctional. So That's why I am asking fr this .

Sad

Thanks

Pradeep.

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488829 is a reply to message #488824]

Tue, 11 January 2011 06:37

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is what I said, if you don't post your actual issue, you have not the answer you expect.
If you want to learn about Replication then you can read:
Database Advanced Replication
Streams Replication Administrator's Guide

Regards
Michel

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488875 is a reply to message #488829]

Tue, 11 January 2011 22:55

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi Michel,

Thanks for links.

I will be back if I will face any error/prob

pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488915 is a reply to message #488829]

Wed, 12 January 2011 04:27

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,
I am facing the issue during the configuration of replication

SQL> exec dbms_streams_adm.add_table_rules(table_name=>'hr.jobs',streams_type=>'sync_capture', streams_name=>'sync_capture', queue_name=>'strmadmin.capture_queue');
BEGIN dbms_streams_adm.add_table_rules(table_name=>'hr.jobs',streams_type=>'sync_capture', streams_name=>'sync_capture', queue_name=>'strmadmin.capture_queue'); END;

*
ERROR at line 1:
ORA-23602: Invalid streams process type SYNC_CAPTURE
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 372
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 312
ORA-06512: at line 1

My Whole Script for the same below

=================================
declare
cscn NUMBER;
begin
dbms_streams_adm.set_up_queue(queue_name=>'capture_queue', queue_user=>'strmadmin');

dbms_streams_adm.set_up_queue(queue_name=>'apply_queue', queue_user=>'strmadmin');

dbms_apply_adm.create_apply(queue_name=>'strmadmin.apply_queue', apply_name=>'apply_hr_jobs',apply_captured=>FALSE);

dbms_streams_adm.add_table_rules(table_name=>'hr.jobs', streams_type=>'apply', streams_name=>'apply_hr_jobs', queue_name=>'strmadmin.apply_queue', source_database=>'db2.com');

dbms_streams_adm.add_table_propagation_rules(table_name=>'hr.jobs',streams_name=> 'send_hr_jobs',source_queue_name=>'strmadmin.capture_queue', destination_queue_name=>'strmadmin.apply_queue@db2.com', source_database=>'db1.com', queue_to_queue=>TRUE);

dbms_streams_adm.add_table_rules(table_name=>'hr.jobs',streams_type=>'sync_capture', streams_name=>'sync_capture', queue_name=>'strmadmin.capture_queue');

cscn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DB2.COM(source_object_name=>'hr.jobs',
source_database_name =>'db1.com',
instantiation_scn=>cscn);
DBMS_APPLY_ADM.START_APPLY( apply_name =>'apply_hr_jobs');

end;
/

==============================================
can some one tell me what is this error and where I am wrong?

regards

pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488922 is a reply to message #488915]

Wed, 12 January 2011 04:52

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-23602: Invalid streams process type %s
 *Cause: Specified streams process type is not valid.
 *Action: Specify either capture or apply.

This one is wrong: "streams_type=>'sync_capture'", it should be either "capture" or "apply".

Regards
Michel

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488928 is a reply to message #488922]

Wed, 12 January 2011 05:40

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

As you suggested I tried with the same the same steps on both source and target db.

Refer attachement of scripts running.

After successful execution of procedure I am not able to see changes done in referenced table.

Is there any problem in scripting or any other thing is there?

regards
pradeep

Attachment: repli.txt
(Size: 4.67KB, Downloaded 925 times)

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488932 is a reply to message #488928]

Wed, 12 January 2011 05:58

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi Michel,

Its running now !!!!!! Smile

Thanks

pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488934 is a reply to message #488932]

Wed, 12 January 2011 06:17

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

Some times its working some time its not !!! I m confuse with this. Confused

Michal Can You tell me what is going on with the replication process.

Thanks
pradeep

Report message to a moderator

Re: Replication of table for DML from source to destination DB [message #488939 is a reply to message #488934]

Wed, 12 January 2011 06:55

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As I have no information on what you have I can't say anything.
Check DBA_APPLY_ERROR.
Check your parameters, maybe you just have a lag in capture, propagation or apply (dba_capture_parameters, dba_apply_parameters)...

The book I posted the link explains how to investigate in its chapter 13 "Troubleshooting Streams Replication".

Regards
Michel

Report message to a moderator

Previous Topic:	Materialized view - background process
Next Topic:	Error in DBMS_REPCAT.ADD_MASTER_DATABASE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 01 18:21:25 CDT 2023