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 Go to next message
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;
/

Re: what's wrong in my script! my replication dosnt work without error [message #410524 is a reply to message #410167] Sun, 28 June 2009 00:36 Go to previous message
narges
Messages: 11
Registered: December 2004
Junior Member
who can help me?i need your help as soon as possible!thanks alot!
narges
Previous Topic: two way for renaming schema (how use?)
Next Topic: streams and dataguard
Goto Forum:
  


Current Time: Fri Dec 09 05:47:04 CST 2016

Total time taken to generate the page: 0.47610 seconds