Replicate "A" schema objects to "B" schema [message #501603] |
Wed, 30 March 2011 01:36 |
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 #522004 is a reply to message #501889] |
Mon, 05 September 2011 06:58 |
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
|
|
|
|
|