Home » Server Options » Replication » Replication of table for DML from source to destination DB (oracle 11g ,Windows)
Replication of table for DML from source to destination DB [message #488773] Tue, 11 January 2011 02:29 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
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

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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
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
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 Go to previous messageGo to next message
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 657 times)
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 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi Michel,

Its running now !!!!!! Smile

Thanks

pradeep
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 Go to previous messageGo to next message
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
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 Go to previous message
Michel Cadot
Messages: 67137
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
Previous Topic: Materialized view - background process
Next Topic: Error in DBMS_REPCAT.ADD_MASTER_DATABASE
Goto Forum:
  


Current Time: Sat May 30 10:55:28 CDT 2020