Home » Server Options » Streams & AQ » what's wrong in my script! my replication dosnt work without error (10.2.0)
| what's wrong in my script! my replication dosnt work without error [message #410167] |
Thu, 25 June 2009 09:12  |
narges
Messages: 11
Registered: December 2004
|
Junior Member |
|
|
hi,
I prepared a stream script setup as you could see in below,after runing this script apply&capture are enable without any error but after inserting data in TEST1.testa data isn't replicated TO TEST2.testa Schema is different and i use rename_schema!!!what is wrong in my script?
My senario:
SRC (TEST1.testa) ==> DESDB (TEST2.testa)
****my script*************************************
set echo on
set serveroutput on
spool example.out
connect SYS/&source_dba_passwd@SRC as SYSDBA
rem exec dbms_propagation_adm.stop_propagation('STREAMS_PROPAGATION')
exec dbms_streams_adm.remove_streams_configuration;
drop user strmadmin cascade;
Rem ***************************************************
create user strmadmin identified by STRMADMIN;
grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to strmadmin;
grant CREATE DATABASE LINK to strmadmin;
grant CREATE ANY DIRECTORY to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
ALTER USER strmadmin DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
drop user test1 cascade;
create user test1 identified by test1;
grant connect, resource to test1;
alter user test1 default tablespace users;
connect test1/test1@src
CREATE TABLE TESTA ( COL1A VARCHAR(4) PRIMARY KEY);
grant select, update, delete, insert on test1.testA to strmadmin;
REM *******************************************************************
connect SYS/&dest_dba_passwd@DESDB as SYSDBA
exec dbms_streams_adm.remove_streams_configuration;
drop user strmadmin cascade;
create user strmadmin identified by STRMADMIN;
grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to strmadmin;
grant CREATE DATABASE LINK to strmadmin;
grant CREATE ANY DIRECTORY to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
ALTER USER strmadmin DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
drop user test2 cascade;
create user test2 identified by test2;
grant connect, resource to test2;
alter user test2 default tablespace users;
connect test2/test2@desdb
CREATE TABLE TESTA ( COL1A VARCHAR(4) PRIMARY KEY);
grant select, update, delete, insert on test2.testa to strmadmin;
rem ***********************************************
connect STRMADMIN/STRMADMIN@src;
CREATE DATABASE LINK DESDB connect to strmadmin identified by STRMADMIN using 'DESDB';
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'streams_capture_qt',
queue_name => 'streams_capture_q',
queue_user => 'strmadmin');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'test1.testa',
streams_name => 'STREAMS_PROPAGATION',
source_queue_name => 'STRMADMIN.STREAMS_CAPTURE_Q',
destination_queue_name => 'STRMADMIN.STREAMS_APPLY_Q@DESDB.REGRESS.RDBMS.DEV.US.ORACLE.COM',
include_dml => true,
include_ddl => false,
source_database => 'SRC.REGRESS.RDBMS.DEV.US.ORACLE.COM',
inclusion_rule => true,
queue_to_queue => true );
END;
/
DECLARE
v_dml_rule VARCHAR2(80);
v_ddl_rule VARCHAR2(80);
v_src_db VARCHAR2(120);
BEGIN
SELECT global_name INTO v_src_db FROM global_name;
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'test1.testa',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_CAPTURE_Q',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => v_src_db,
dml_rule_name => v_dml_rule,
ddl_rule_name => v_ddl_rule,
inclusion_rule => true,
and_condition => NULL);
END;
/
REM*******************************************************************
connect STRMADMIN/STRMADMIN@DESDB;
CREATE DATABASE LINK SRC connect to STRMADMIN identified by STRMADMIN using 'SRC';
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_APPLY_QT',
queue_name => 'STREAMS_APPLY_Q',
queue_user => 'STRMADMIN');
END;
/
DECLARE
v_dml_rule VARCHAR2(80);
v_ddl_rule VARCHAR2(80);
v_src_db VARCHAR2(120);
BEGIN
SELECT global_name INTO v_src_db FROM global_name;
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'test1.testa',
streams_type => 'apply',
streams_name => 'STREAMS_APPLY',
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => v_src_db,
dml_rule_name => v_dml_rule,
ddl_rule_name => v_ddl_rule,
inclusion_rule => true,
and_condition => NULL);
DBMS_STREAMS_ADM.RENAME_SCHEMA(
rule_name => v_dml_rule,
from_schema_name =>'TEST1',
to_schema_name => 'TEST2');
END;
/
REM*******************************************************
connect STRMADMIN/STRMADMIN@SRC;
DECLARE
iSCN NUMBER;
v_src_db VARCHAR2(120);
BEGIN
iSCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
SELECT global_name INTO v_src_db FROM global_name;
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DESDB.REGRESS.RDBMS.DEV.US.ORACLE.COM(
source_object_name => 'test1.testa',
source_database_name => v_src_db,
instantiation_scn => iSCN);
COMMIT;
END;
/
REM*********************************************
connect STRMADMIN/STRMADMIN@DESDB;
begin
dbms_apply_adm.start_apply('STREAMS_APPLY');
end;
/
connect STRMADMIN/STRMADMIN@SRC;
begin
dbms_capture_adm.start_capture('STREAMS_CAPTURE');
end;
/
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 31 08:57:46 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
