Home » Server Options » Replication » Replicate "A" schema objects to "B" schema (Oracle 10G, windows)
icon7.gif  Replicate "A" schema objects to "B" schema [message #501603] Wed, 30 March 2011 01:36 Go to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
Hi all,

I told about Oracle multi master replication below.

Can I replicate some objects of "X" schema at A server to "Y" schema at B server using MM replication?

A years ago, I read some materials about replicated objects schema name should be same at both side. Is it correct story or not?
I can't remember which document write this story.

Thanks
Re: Replicate "A" schema objects to "B" schema [message #501610 is a reply to message #501603] Wed, 30 March 2011 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Yes.
2) This is not correct.

I don't see any multi-master replication in your example.

Regards
Michel
Re: Replicate "A" schema objects to "B" schema [message #501612 is a reply to message #501610] Wed, 30 March 2011 02:15 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
Thanks Michel.
I did write only one side process. Of course, I do implement some objects of "Y" schema at B server to "X" schema at A server.
Re: Replicate "A" schema objects to "B" schema [message #501859 is a reply to message #501610] Fri, 01 April 2011 01:59 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
Hi Michel,

I can't implement MM replication above scenario.

Let me explain again:

USE CASE 1 Smile
On Master site:
Table name=ORDERS
Schema=SUPER
SID=PROD

On replicated site:
Table name=ORDERS
Schema=SUPER
SID=PROD_REP

I can implement MM replication above environment.
In this case, just different SID on both site.

I can't implement MM replication case below. In this case schema and SID is different!

USE CASE 2 Sad
On Master site:
Table name=ORDERS
Schema=SUPER
SID=PROD

On Replicated site:
Table name=ORDERS
Schema=SUPER_REP
SID=PROD_REP


How to implement second use case when replicated object schema is different on replicated site?

Please help.
Re: Replicate "A" schema objects to "B" schema [message #501863 is a reply to message #501859] Fri, 01 April 2011 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which way you use for case 1?

Regards
Michel
Re: Replicate "A" schema objects to "B" schema [message #501865 is a reply to message #501863] Fri, 01 April 2011 02:18 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
I would like use for case 2.
Re: Replicate "A" schema objects to "B" schema [message #501867 is a reply to message #501863] Fri, 01 April 2011 02:32 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
I use MM replication case 1.
use URL below:
http://www.akadia.com/services/ora_replication_guide.html
Re: Replicate "A" schema objects to "B" schema [message #501874 is a reply to message #501867] Fri, 01 April 2011 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I will not read all this article, summarize it.

Regards
Michel
Re: Replicate "A" schema objects to "B" schema [message #501876 is a reply to message #501874] Fri, 01 April 2011 03:07 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
Summarize:

How can I copy a table records to same table at different SCHEMA using Oracle Advanced Replication approach?

SUPER.ORDERS -> SUPER_REP.ORDERS
Re: Replicate "A" schema objects to "B" schema [message #501884 is a reply to message #501876] Fri, 01 April 2011 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
create materialized view SUPER_REP.ORDERS as select ... from SUPER.ORDERS@... ...

Regards
Michel
Re: Replicate "A" schema objects to "B" schema [message #501889 is a reply to message #501884] Fri, 01 April 2011 04:36 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
Thank Michel,

I understand this way.

In Multi Master Replication case, How to do this?
Re: Replicate "A" schema objects to "B" schema [message #522004 is a reply to message #501889] Mon, 05 September 2011 06:58 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

I have the same issue as above. i.e. Same table name with different schema in different database. Have already tried with same table name with same schema name in different db's.
code is attached with this. Now I am trying to implement replication for same table name with different source and destination schema name.
connect sys/rfc12345@vms as sysdba

create user STRMADMIN identified by STRMADMIN;

ALTER USER STRMADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN'); 

connect sys/moneta@moneta as sysdba

create user STRMADMIN identified by STRMADMIN;

ALTER USER STRMADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN'); 

conn STRMADMIN/STRMADMIN@vms

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_QUEUE',
queue_table =>'STREAMS_QUEUE_TABLE',
queue_user => 'STRMADMIN');
END;
/
conn STRMADMIN/STRMADMIN@vms

create database link moneta connect to STRMADMIN identified by STRMADMIN using 'moneta'; 

select sysdate from dual@moneta;


conn STRMADMIN/STRMADMIN@moneta

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_QUEUE',
queue_table =>'STREAMS_QUEUE_TABLE',
queue_user => 'STRMADMIN');
END;
/

conn STRMADMIN/STRMADMIN@vms


BEGIN 
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( 
table_name => 'RFC_DB.TBL_VOICECHAT', 
streams_name => 'STRMADMIN_PROP', 
source_queue_name => 'STRMADMIN.STREAMS_QUEUE', 
destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@moneta', 
include_dml => true, 
include_ddl => true, 
source_database => 'vms'); 
END; 
/ 

BEGIN   
DBMS_STREAMS_ADM.ADD_TABLE_RULES(   
  table_name     => 'RFC_DB.TBL_VOICECHAT',   
  streams_type    => 'CAPTURE',   
  streams_name    => 'STRMADMIN_CAPTURE',   
  queue_name      => 'STRMADMIN.STREAMS_QUEUE',   
  include_dml     => true,   
  include_ddl     => true,   
  source_database => 'vms');   
END;   
/  
conn STRMADMIN/STRMADMIN@moneta

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'RFC_DB.TBL_VOICECHAT',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'vms');
END;
/ 

BEGIN 
  DBMS_APPLY_ADM.ALTER_APPLY( 
    apply_name => 'STRMADMIN_APPLY', 
    apply_user => 'RFC_DB'); 
END; 
/  

BEGIN 
  DBMS_APPLY_ADM.SET_PARAMETER( 
    apply_name => 'STRMADMIN_APPLY', 
    parameter  => 'disable_on_error', 
    value      => 'n'); 
END; 
/ 



As I Read this post this is possible. Can someone tell me where Should I change in script for destination schema

Thanks is Advance.

Pradeep
Re: Replicate "A" schema objects to "B" schema [message #522009 is a reply to message #522004] Mon, 05 September 2011 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your Oracle version?

Regards
Michel
Re: Replicate "A" schema objects to "B" schema [message #522011 is a reply to message #522009] Mon, 05 September 2011 07:47 Go to previous message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi Michal,

Thanks for your reply. My Oracle version is 11g Rel1 (11.1.0.6.0)


Regards

Pradeep Sharma
Previous Topic: Oracle Streams vs SharePlex
Next Topic: Apply Process is not working
Goto Forum:
  


Current Time: Thu Mar 28 15:41:11 CDT 2024