ITPUB论坛 » Oracle新技术/11g » [共享]三台主机streams复制的脚本
2006-3-10 10:31
teddyboy
[共享]三台主机streams复制的脚本
与大家共享，希望更多的人参与到研究streams的队伍中来。说明一下：
以下的配置是三台主机，bjdt3-->bjdt4-->bjdt5，通过流传输，另外bjdt4-->bjdt5也通过流传输，附件中有一个doc文档，把结构都说明了，如果希望在两台主机上测试把相关的去掉即可。
以下的脚本基本上是一个可以完全运行的脚本，可以单步执行，也可以存成sql一下子完全运行，前提是prerequisite中的都必须设置好，并且脚本中的connect都要连通的。
测试用户是repl，有两个表job, job2，配置成job可以通过streams传输，job2不传输。
[PHP]
--0. prerequisite:
--configure tnsnames.ora.
--ensure bjdt3, bjdt4, bjdt5 can access each other.
--configure init parameter:
--alter system set global_name=true scope=both;
--alter system set job_queue_processes=50 scope=both;
--alter system set compatible=10.2.0 scope=both;
--alter system set open_links=20 scope=spfile;
--alter system set streams_pool_size=200M scope=both;
--alter database rename global_name to xxxxx;
--ensure all database run in archivelog mode!!!
--
--create user and tablespace at every database
connect sys/oracle@bjdt3 as sysdba
create tablespace test01 datafile '/oracle/oradata/bjdt3/test01.dbf' size 100M autoextend on next 10M maxsize unlimited;
create tablespace strmadmin datafile '/oracle/oradata/bjdt3/strmadmin.dbf' size 100M autoextend on next 10M maxsize unlimited;
connect sys/oracle@bjdt4 as sysdba
create tablespace test01 datafile '/oracle/oradata/bjdt4/test01.dbf' size 100M autoextend on next 10M maxsize unlimited;
create tablespace strmadmin datafile '/oracle/oradata/bjdt4/strmadmin.dbf' size 100M autoextend on next 10M maxsize unlimited;
connect sys/oracle@bjdt5 as sysdba
create tablespace test01 datafile '/oracle/oradata/bjdt5/test01.dbf' size 100M autoextend on next 10M maxsize unlimited;
create tablespace strmadmin datafile '/oracle/oradata/bjdt5/strmadmin.dbf' size 100M autoextend on next 10M maxsize unlimited;
----------------------------Set Up Users and Create Queues and Database Links
-- 1. Show Output and Spool Results 1
SET ECHO ON
set serveroutput on
SPOOL streams_bjdt_setup.out
-- 2. Set Up Users at bjdt3
connect sys/oracle@bjdt3 as sysdba
create user strmadmin identified by strmadmin default tablespace strmadmin;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
-- create test user repl
create user repl identified by repl default tablespace test01;
grant connect, resource to repl;
connect repl/repl@bjdt3;
create table job(id number);
insert into job values(1);
commit;
create table job2(id number);
-- 3. Create the Queue at bjdt3
connect strmadmin/strmadmin@bjdt3
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_CAPTURE_BJDT3',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT3',
queue_user => 'STRMADMIN');
END;
/
-- 4. Set Up Users at bjdt4
connect sys/oracle@bjdt4 as sysdba
create user strmadmin identified by strmadmin default tablespace strmadmin;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
-- create test user repl
create user repl identified by repl default tablespace test01;
grant connect, resource to repl;
-- 5. Create the Queue at bjdt4
connect strmadmin/strmadmin@bjdt4
-- create queue from bjdt3:
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_FROM_BJDT3',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3',
queue_user => 'STRMADMIN');
END;
/
-- create local capture queue：
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_CAPTURE_BJDT4',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT4',
queue_user => 'STRMADMIN');
END;
/
-- 6. Set Up Users at bjdt5
connect sys/oracle@bjdt5 as sysdba
create user strmadmin identified by strmadmin default tablespace strmadmin;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
-- create test user repl
create user repl identified by repl default tablespace test01;
grant connect, resource to repl;
-- 7. Create the Queue at bjdt5
connect strmadmin/strmadmin@bjdt5
-- create queue from bjdt3:
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4',
queue_user => 'STRMADMIN');
END;
/
-- create from bjdt4 queue:
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_FROM_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT4',
queue_user => 'STRMADMIN');
END;
/
-- 8. Create the Database Links at bjdt3
connect strmadmin/strmadmin@bjdt3
create database link bjdt4 connect to strmadmin identified by strmadmin using 'bjdt4';
select sysdate from dual@bjdt4;
create database link bjdt5 connect to strmadmin identified by strmadmin using 'bjdt5';
select sysdate from dual@bjdt5;
-- 9. Create the Database Links at bjdt4
connect strmadmin/strmadmin@bjdt4
create database link bjdt3 connect to strmadmin identified by strmadmin using 'bjdt3';
select sysdate from dual@bjdt3;
create database link bjdt5 connect to strmadmin identified by strmadmin using 'bjdt5';
select sysdate from dual@bjdt5;
-- 10. Create the Database Links at bjdt5
connect strmadmin/strmadmin@bjdt5
create database link bjdt3 connect to strmadmin identified by strmadmin using 'bjdt3';
select sysdate from dual@bjdt3;
create database link bjdt4 connect to strmadmin identified by strmadmin using 'bjdt4';
select sysdate from dual@bjdt4;
-- 11. Check the Spool Results 1
SET ECHO OFF
SPOOL OFF
/*************************end of Set Up**************************/
----------------------------Script for Sharing Data from Multiple Databases
-- 1. Show Output and Spool Results 2
SET ECHO ON
SPOOL streams_bjdt.out
-- 2. Specify Supplemental Logging at bjdt3
connect strmadmin/strmadmin@bjdt3
-- 3. Create the Capture Process at bjdt3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'CAPTURE',
streams_name => 'CAP_BJDT3',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT3',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- 4. Set the Instantiation SCN for bjdt4 bjdt5
-- set for bjdt4
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT4(
source_schema_name => 'REPL',
source_database_name => 'BJDT3',
instantiation_scn => iscn,
recursive => true);
END;
/
-- set for bjdt5
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT5(
source_schema_name => 'REPL',
source_database_name => 'BJDT3',
instantiation_scn => iscn,
recursive => true);
END;
/
-- 5. Configure Propagation at bjdt3
-- create propagation from bjdt3 to bjdt4:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'REPL',
streams_name => 'PROP_BJDT3_BJDT4',
source_queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT3',
destination_queue_name => 'STRMADMIN.QUE_FROM_BJDT3@BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3',
inclusion_rule => true,
and_condition => ':lcr.get_object_name() NOT IN (''JOB2'')'
);
END;
/
-- 6. Create the Capture Process at bjdt4.
connect strmadmin/strmadmin@bjdt4
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'CAPTURE',
streams_name => 'CAP_BJDT4',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT4');
END;
/
-- 7. Set the Instantiation SCN for bjdt4 at bjdt5
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT5(
source_schema_name => 'REPL',
source_database_name => 'BJDT4',
instantiation_scn => iscn,
recursive => true);
END;
/
-- 8. Create One Apply Process at bjdt4 for bjdt3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'APPLY',
streams_name => 'APP_BJDT3',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- 9. Specify repl as the Apply User for Each Apply Process at bjdt4
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'APP_BJDT3',
apply_user => 'REPL');
END;
/
-- set apply parameter
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APP_BJDT3',
parameter => 'disable_on_error',
value => 'n');
END;
/
-- 10. Grant the repl User Execute Privilege on the Apply Process Rule Set at bjdt4
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME
INTO rs_name
FROM DBA_APPLY
WHERE APPLY_NAME='APP_BJDT3';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name,
grantee => 'REPL');
END;
/
-- 11. Configure Propagation at bjdt4
-- create propagation from bjdt3 to bjdt5：
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'REPL',
streams_name => 'PROP_BJDT3_BJDT4_BJDT5',
source_queue_name => 'STRMADMIN.QUE_FROM_BJDT3',
destination_queue_name => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4@BJDT5',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- create propagation to bjdt5:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'REPL',
streams_name => 'PROP_BJDT4_BJDT5',
source_queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT4',
destination_queue_name => 'STRMADMIN.QUE_FROM_BJDT4@BJDT5',
include_dml => true,
include_ddl => true,
source_database => 'BJDT4');
END;
/
-- 12. Create One Apply Process at bjdt5 for Each Source Database
connect strmadmin/strmadmin@bjdt5
-- create apply process for bjdt3 queue:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'APPLY',
streams_name => 'APP_BJDT3_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- create apply process for bjdt4：
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'APPLY',
streams_name => 'APP_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT4');
END;
/
-- 13. Specify repl as the Apply User for Each Apply Process at bjdt5
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'APP_BJDT3_BJDT4',
apply_user => 'REPL');
END;
/
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APP_BJDT3_BJDT4',
parameter => 'disable_on_error',
value => 'n');
END;
/
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'APP_BJDT4',
apply_user => 'REPL');
END;
/
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APP_BJDT4',
parameter => 'disable_on_error',
value => 'n');
END;
/
-- 14. Grant the repl User Execute Privilege on the Apply Process Rule Set at bjdt5
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME
INTO rs_name
FROM DBA_APPLY
WHERE APPLY_NAME='APP_BJDT3_BJDT4';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name,
grantee => 'REPL');
END;
/
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME
INTO rs_name
FROM DBA_APPLY
WHERE APPLY_NAME='APP_BJDT4';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name,
grantee => 'REPL');
END;
/
-- 15. Instantiate the repl Schema at bjdt4
connect strmadmin/strmadmin@bjdt3
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
schema_name => 'REPL');
END;
/
--create directory DPUMP_DIR as '/oracle/oradata/expdr';
--export NLS_LANG=American_america.ZHS16GBK
--expdp strmadmin/strmadmin schemas=repl DIRECTORY=DPUMP_DIR DUMPFILE=rep%U.dmp FILESIZE=1G FLASHBACK_SCN=<the above values (GET_SYSTEM_CHANGE_NUMBER())>
connect strmadmin/strmadmin@bjdt4
--create directory DPUMP_DIR as '/oracle/oradata/expdr';
--export NLS_LANG=American_america.ZHS16GBK
--impdp strmadmin/strmadmin schemas=repl DIRECTORY=DPUMP_DIR DUMPFILE=rep%U.dmp logfile=impdp.log
DECLARE
h1 NUMBER; -- data pump job handle
bjdt4_instantscn NUMBER; -- Variable to hold current source SCN
job_state VARCHAR2(30); -- job state
status ku$_Status; -- data pump status
job_not_exist exception;
pragma exception_init(job_not_exist, -31626);
BEGIN
h1 := DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => 'BJDT3',
job_name => 'dp_BJDT4');
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'SCHEMA_EXPR',
value => '=''REPL''');
-- A metadata filter is used to specify the tables that will be imported.
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'NAME_EXPR',
value => 'NOT IN (''JOB2'')');
bjdt4_instantscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@bjdt3();
DBMS_DATAPUMP.SET_PARAMETER(
handle => h1,
name => 'FLASHBACK_SCN',
value => bjdt4_instantscn);
dbms_datapump.start_job(h1);
job_state := 'UNDEFINED';
BEGIN
WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line('job finished. Instantiation SCN: '||bjdt4_instantscn);
END;
COMMIT;
END;
/
-- 16. Instantiate the repl Schema at bjdt5
connect strmadmin/strmadmin@bjdt5
--create directory DPUMP_DIR as '/oracle/oradata/expdr';
--export NLS_LANG=American_america.ZHS16GBK
--impdp strmadmin/strmadmin schemas=repl DIRECTORY=DPUMP_DIR DUMPFILE=rep%U.dmp logfile=impdp.log
DECLARE
h1 NUMBER; -- data pump job handle
bjdt5_instantscn NUMBER; -- Variable to hold current source SCN
job_state VARCHAR2(30); -- job state
status ku$_Status; -- data pump status
job_not_exist exception;
pragma exception_init(job_not_exist, -31626);
BEGIN
h1 := DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => 'BJDT3',
job_name => 'dp_BJDT5');
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'SCHEMA_EXPR',
value => '=''REPL''');
-- A metadata filter is used to specify the tables that will be imported.
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'NAME_EXPR',
value => 'NOT IN (''JOB2'')');
bjdt5_instantscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@bjdt3();
DBMS_DATAPUMP.SET_PARAMETER(
handle => h1,
name => 'FLASHBACK_SCN',
value => bjdt5_instantscn);
dbms_datapump.start_job(h1);
job_state := 'UNDEFINED';
BEGIN
WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line('job finished. Instantiation SCN: '||bjdt5_instantscn);
END;
COMMIT;
END;
/
-- 16.5 set schema instantiation scn:
connect strmadmin/strmadmin@bjdt4
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT5(
source_schema_name => 'REPL',
source_database_name => 'BJDT4',
instantiation_scn => iscn,
recursive => true);
END;
/
-- 17. Start the Apply Processes at bjdt4
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'APP_BJDT3';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APP_BJDT3');
end if;
END;
/
-- 18. Start the Apply Processes at bjdt5
connect strmadmin/strmadmin@bjdt5
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'APP_BJDT4';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APP_BJDT4');
end if;
END;
/
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'APP_BJDT3_BJDT4';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APP_BJDT3_BJDT4');
end if;
END;
/
-- 19. Start the Capture Process at bjdt3
connect strmadmin/strmadmin@bjdt3
begin
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'CAP_BJDT3');
end;
/
-- 20. Start the Capture Process at bjdt4
connect strmadmin/strmadmin@bjdt4
begin
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'CAP_BJDT4');
end;
/
-- 21. Check the Spool Results 2
SET ECHO OFF
SPOOL OFF
/*******************************The End**********************************/
-- 22. remove streams configuration
--connect / as sysdba
--exec dbms_streams_adm.remove_streams_configuration();
--drop user strmadmin cascade;
-- 23. set table compare old values
[/PHP]
与大家共享，希望更多的人参与到研究streams的队伍中来。说明一下：
以下的配置是三台主机，bjdt3-->bjdt4-->bjdt5，通过流传输，另外bjdt4-->bjdt5也通过流传输，附件中有一个doc文档，把结构都说明了，如果希望在两台主机上测试把相关的去掉即可。
以下的脚本基本上是一个可以完全运行的脚本，可以单步执行，也可以存成sql一下子完全运行，前提是prerequisite中的都必须设置好，并且脚本中的connect都要连通的。
测试用户是repl，有两个表job, job2，配置成job可以通过streams传输，job2不传输。
[PHP]
--0. prerequisite:
--configure tnsnames.ora.
--ensure bjdt3, bjdt4, bjdt5 can access each other.
--configure init parameter:
--alter system set global_name=true scope=both;
--alter system set job_queue_processes=50 scope=both;
--alter system set compatible=10.2.0 scope=both;
--alter system set open_links=20 scope=spfile;
--alter system set streams_pool_size=200M scope=both;
--alter database rename global_name to xxxxx;
--ensure all database run in archivelog mode!!!
--
--create user and tablespace at every database
connect sys/oracle@bjdt3 as sysdba
create tablespace test01 datafile '/oracle/oradata/bjdt3/test01.dbf' size 100M autoextend on next 10M maxsize unlimited;
create tablespace strmadmin datafile '/oracle/oradata/bjdt3/strmadmin.dbf' size 100M autoextend on next 10M maxsize unlimited;
connect sys/oracle@bjdt4 as sysdba
create tablespace test01 datafile '/oracle/oradata/bjdt4/test01.dbf' size 100M autoextend on next 10M maxsize unlimited;
create tablespace strmadmin datafile '/oracle/oradata/bjdt4/strmadmin.dbf' size 100M autoextend on next 10M maxsize unlimited;
connect sys/oracle@bjdt5 as sysdba
create tablespace test01 datafile '/oracle/oradata/bjdt5/test01.dbf' size 100M autoextend on next 10M maxsize unlimited;
create tablespace strmadmin datafile '/oracle/oradata/bjdt5/strmadmin.dbf' size 100M autoextend on next 10M maxsize unlimited;
----------------------------Set Up Users and Create Queues and Database Links
-- 1. Show Output and Spool Results 1
SET ECHO ON
set serveroutput on
SPOOL streams_bjdt_setup.out
-- 2. Set Up Users at bjdt3
connect sys/oracle@bjdt3 as sysdba
create user strmadmin identified by strmadmin default tablespace strmadmin;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
-- create test user repl
create user repl identified by repl default tablespace test01;
grant connect, resource to repl;
connect repl/repl@bjdt3;
create table job(id number);
insert into job values(1);
commit;
create table job2(id number);
-- 3. Create the Queue at bjdt3
connect strmadmin/strmadmin@bjdt3
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_CAPTURE_BJDT3',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT3',
queue_user => 'STRMADMIN');
END;
/
-- 4. Set Up Users at bjdt4
connect sys/oracle@bjdt4 as sysdba
create user strmadmin identified by strmadmin default tablespace strmadmin;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
-- create test user repl
create user repl identified by repl default tablespace test01;
grant connect, resource to repl;
-- 5. Create the Queue at bjdt4
connect strmadmin/strmadmin@bjdt4
-- create queue from bjdt3:
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_FROM_BJDT3',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3',
queue_user => 'STRMADMIN');
END;
/
-- create local capture queue：
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_CAPTURE_BJDT4',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT4',
queue_user => 'STRMADMIN');
END;
/
-- 6. Set Up Users at bjdt5
connect sys/oracle@bjdt5 as sysdba
create user strmadmin identified by strmadmin default tablespace strmadmin;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
-- create test user repl
create user repl identified by repl default tablespace test01;
grant connect, resource to repl;
-- 7. Create the Queue at bjdt5
connect strmadmin/strmadmin@bjdt5
-- create queue from bjdt3:
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4',
queue_user => 'STRMADMIN');
END;
/
-- create from bjdt4 queue:
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STRMADMIN.QUE_FROM_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT4',
queue_user => 'STRMADMIN');
END;
/
-- 8. Create the Database Links at bjdt3
connect strmadmin/strmadmin@bjdt3
create database link bjdt4 connect to strmadmin identified by strmadmin using 'bjdt4';
select sysdate from dual@bjdt4;
create database link bjdt5 connect to strmadmin identified by strmadmin using 'bjdt5';
select sysdate from dual@bjdt5;
-- 9. Create the Database Links at bjdt4
connect strmadmin/strmadmin@bjdt4
create database link bjdt3 connect to strmadmin identified by strmadmin using 'bjdt3';
select sysdate from dual@bjdt3;
create database link bjdt5 connect to strmadmin identified by strmadmin using 'bjdt5';
select sysdate from dual@bjdt5;
-- 10. Create the Database Links at bjdt5
connect strmadmin/strmadmin@bjdt5
create database link bjdt3 connect to strmadmin identified by strmadmin using 'bjdt3';
select sysdate from dual@bjdt3;
create database link bjdt4 connect to strmadmin identified by strmadmin using 'bjdt4';
select sysdate from dual@bjdt4;
-- 11. Check the Spool Results 1
SET ECHO OFF
SPOOL OFF
/*************************end of Set Up**************************/
----------------------------Script for Sharing Data from Multiple Databases
-- 1. Show Output and Spool Results 2
SET ECHO ON
SPOOL streams_bjdt.out
-- 2. Specify Supplemental Logging at bjdt3
connect strmadmin/strmadmin@bjdt3
-- 3. Create the Capture Process at bjdt3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'CAPTURE',
streams_name => 'CAP_BJDT3',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT3',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- 4. Set the Instantiation SCN for bjdt4 bjdt5
-- set for bjdt4
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT4(
source_schema_name => 'REPL',
source_database_name => 'BJDT3',
instantiation_scn => iscn,
recursive => true);
END;
/
-- set for bjdt5
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT5(
source_schema_name => 'REPL',
source_database_name => 'BJDT3',
instantiation_scn => iscn,
recursive => true);
END;
/
-- 5. Configure Propagation at bjdt3
-- create propagation from bjdt3 to bjdt4:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'REPL',
streams_name => 'PROP_BJDT3_BJDT4',
source_queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT3',
destination_queue_name => 'STRMADMIN.QUE_FROM_BJDT3@BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3',
inclusion_rule => true,
and_condition => ':lcr.get_object_name() NOT IN (''JOB2'')'
);
END;
/
-- 6. Create the Capture Process at bjdt4.
connect strmadmin/strmadmin@bjdt4
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'CAPTURE',
streams_name => 'CAP_BJDT4',
queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT4');
END;
/
-- 7. Set the Instantiation SCN for bjdt4 at bjdt5
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT5(
source_schema_name => 'REPL',
source_database_name => 'BJDT4',
instantiation_scn => iscn,
recursive => true);
END;
/
-- 8. Create One Apply Process at bjdt4 for bjdt3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'APPLY',
streams_name => 'APP_BJDT3',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- 9. Specify repl as the Apply User for Each Apply Process at bjdt4
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'APP_BJDT3',
apply_user => 'REPL');
END;
/
-- set apply parameter
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APP_BJDT3',
parameter => 'disable_on_error',
value => 'n');
END;
/
-- 10. Grant the repl User Execute Privilege on the Apply Process Rule Set at bjdt4
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME
INTO rs_name
FROM DBA_APPLY
WHERE APPLY_NAME='APP_BJDT3';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name,
grantee => 'REPL');
END;
/
-- 11. Configure Propagation at bjdt4
-- create propagation from bjdt3 to bjdt5：
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'REPL',
streams_name => 'PROP_BJDT3_BJDT4_BJDT5',
source_queue_name => 'STRMADMIN.QUE_FROM_BJDT3',
destination_queue_name => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4@BJDT5',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- create propagation to bjdt5:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'REPL',
streams_name => 'PROP_BJDT4_BJDT5',
source_queue_name => 'STRMADMIN.QUE_CAPTURE_BJDT4',
destination_queue_name => 'STRMADMIN.QUE_FROM_BJDT4@BJDT5',
include_dml => true,
include_ddl => true,
source_database => 'BJDT4');
END;
/
-- 12. Create One Apply Process at bjdt5 for Each Source Database
connect strmadmin/strmadmin@bjdt5
-- create apply process for bjdt3 queue:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'APPLY',
streams_name => 'APP_BJDT3_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT3_BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT3');
END;
/
-- create apply process for bjdt4：
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'REPL',
streams_type => 'APPLY',
streams_name => 'APP_BJDT4',
queue_name => 'STRMADMIN.QUE_FROM_BJDT4',
include_dml => true,
include_ddl => true,
source_database => 'BJDT4');
END;
/
-- 13. Specify repl as the Apply User for Each Apply Process at bjdt5
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'APP_BJDT3_BJDT4',
apply_user => 'REPL');
END;
/
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APP_BJDT3_BJDT4',
parameter => 'disable_on_error',
value => 'n');
END;
/
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'APP_BJDT4',
apply_user => 'REPL');
END;
/
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APP_BJDT4',
parameter => 'disable_on_error',
value => 'n');
END;
/
-- 14. Grant the repl User Execute Privilege on the Apply Process Rule Set at bjdt5
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME
INTO rs_name
FROM DBA_APPLY
WHERE APPLY_NAME='APP_BJDT3_BJDT4';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name,
grantee => 'REPL');
END;
/
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME
INTO rs_name
FROM DBA_APPLY
WHERE APPLY_NAME='APP_BJDT4';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name,
grantee => 'REPL');
END;
/
-- 15. Instantiate the repl Schema at bjdt4
connect strmadmin/strmadmin@bjdt3
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
schema_name => 'REPL');
END;
/
--create directory DPUMP_DIR as '/oracle/oradata/expdr';
--export NLS_LANG=American_america.ZHS16GBK
--expdp strmadmin/strmadmin schemas=repl DIRECTORY=DPUMP_DIR DUMPFILE=rep%U.dmp FILESIZE=1G FLASHBACK_SCN=<the above values (GET_SYSTEM_CHANGE_NUMBER())>
connect strmadmin/strmadmin@bjdt4
--create directory DPUMP_DIR as '/oracle/oradata/expdr';
--export NLS_LANG=American_america.ZHS16GBK
--impdp strmadmin/strmadmin schemas=repl DIRECTORY=DPUMP_DIR DUMPFILE=rep%U.dmp logfile=impdp.log
DECLARE
h1 NUMBER; -- data pump job handle
bjdt4_instantscn NUMBER; -- Variable to hold current source SCN
job_state VARCHAR2(30); -- job state
status ku$_Status; -- data pump status
job_not_exist exception;
pragma exception_init(job_not_exist, -31626);
BEGIN
h1 := DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => 'BJDT3',
job_name => 'dp_BJDT4');
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'SCHEMA_EXPR',
value => '=''REPL''');
-- A metadata filter is used to specify the tables that will be imported.
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'NAME_EXPR',
value => 'NOT IN (''JOB2'')');
bjdt4_instantscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@bjdt3();
DBMS_DATAPUMP.SET_PARAMETER(
handle => h1,
name => 'FLASHBACK_SCN',
value => bjdt4_instantscn);
dbms_datapump.start_job(h1);
job_state := 'UNDEFINED';
BEGIN
WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line('job finished. Instantiation SCN: '||bjdt4_instantscn);
END;
COMMIT;
END;
/
-- 16. Instantiate the repl Schema at bjdt5
connect strmadmin/strmadmin@bjdt5
--create directory DPUMP_DIR as '/oracle/oradata/expdr';
--export NLS_LANG=American_america.ZHS16GBK
--impdp strmadmin/strmadmin schemas=repl DIRECTORY=DPUMP_DIR DUMPFILE=rep%U.dmp logfile=impdp.log
DECLARE
h1 NUMBER; -- data pump job handle
bjdt5_instantscn NUMBER; -- Variable to hold current source SCN
job_state VARCHAR2(30); -- job state
status ku$_Status; -- data pump status
job_not_exist exception;
pragma exception_init(job_not_exist, -31626);
BEGIN
h1 := DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => 'BJDT3',
job_name => 'dp_BJDT5');
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'SCHEMA_EXPR',
value => '=''REPL''');
-- A metadata filter is used to specify the tables that will be imported.
DBMS_DATAPUMP.METADATA_FILTER(
handle => h1,
name => 'NAME_EXPR',
value => 'NOT IN (''JOB2'')');
bjdt5_instantscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@bjdt3();
DBMS_DATAPUMP.SET_PARAMETER(
handle => h1,
name => 'FLASHBACK_SCN',
value => bjdt5_instantscn);
dbms_datapump.start_job(h1);
job_state := 'UNDEFINED';
BEGIN
WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line('job finished. Instantiation SCN: '||bjdt5_instantscn);
END;
COMMIT;
END;
/
-- 16.5 set schema instantiation scn:
connect strmadmin/strmadmin@bjdt4
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BJDT5(
source_schema_name => 'REPL',
source_database_name => 'BJDT4',
instantiation_scn => iscn,
recursive => true);
END;
/
-- 17. Start the Apply Processes at bjdt4
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'APP_BJDT3';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APP_BJDT3');
end if;
END;
/
-- 18. Start the Apply Processes at bjdt5
connect strmadmin/strmadmin@bjdt5
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'APP_BJDT4';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APP_BJDT4');
end if;
END;
/
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'APP_BJDT3_BJDT4';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APP_BJDT3_BJDT4');
end if;
END;
/
-- 19. Start the Capture Process at bjdt3
connect strmadmin/strmadmin@bjdt3
begin
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'CAP_BJDT3');
end;
/
-- 20. Start the Capture Process at bjdt4
connect strmadmin/strmadmin@bjdt4
begin
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'CAP_BJDT4');
end;
/
-- 21. Check the Spool Results 2
SET ECHO OFF
SPOOL OFF
/*******************************The End**********************************/
-- 22. remove streams configuration
--connect / as sysdba
--exec dbms_streams_adm.remove_streams_configuration();
--drop user strmadmin cascade;
-- 23. set table compare old values
[/PHP]
2006-3-10 14:56
teddyboy
居然没有一个人顶，真伤心，:cry: ，好歹我也整理了一晚上呢，:cry: :cry: :cry:
2006-3-10 18:55
it_caribe
辛苦了...
2006-3-11 18:29
blackgirl2002
顶, 这个技术太新了,用的人还不多,所以反应可能不够热烈吧.能否问一下楼主是不是HC的?
2006-3-11 21:19
teddyboy
[QUOTE][i]最初由 blackgirl2002 发布[/i]
[B]顶, 这个技术太新了,用的人还不多,所以反应可能不够热烈吧.能否问一下楼主是不是HC的? [/B][/QUOTE]
:xu:给你发消息了
[B]顶, 这个技术太新了,用的人还不多,所以反应可能不够热烈吧.能否问一下楼主是不是HC的? [/B][/QUOTE]
:xu:给你发消息了
2006-3-12 13:36
xzh2000
项一下
2006-3-12 21:19
ruochen0926
顶把!
2006-3-13 09:49
xjg
确实是没有用过，所以如果能稍微介绍一下背景，实现目标，实现方式，这样对大家更有帮助。
2006-3-13 14:09
jiangxuyong
好,学习
2006-3-13 19:10
vongates
以前测试过，不过听说10G更简单了
2006-3-14 06:24
ocpmi
Streams Global, Schema, Table, and Subset Replication Wizard
[url]http://www.itpub.net/showthread.php?s=&threadid=500749&highlight=streams[/url]
有人试过用 Streams Global, Schema, Table, and Subset Replication Wizard 来配置Streams Replication 吗？
Streams Global, Schema, Table, and Subset Replication Wizard
[url]http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14228/config_simple.htm[/url]
[url]http://www.itpub.net/showthread.php?s=&threadid=500749&highlight=streams[/url]
有人试过用 Streams Global, Schema, Table, and Subset Replication Wizard 来配置Streams Replication 吗？
Streams Global, Schema, Table, and Subset Replication Wizard
[url]http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14228/config_simple.htm[/url]
2006-3-14 06:28
ocpmi
Oracle 有现成的例子，为什么不试一试？
2006-3-14 06:44
ocpmi
请问对一个安装使用完好的Streams Replication，怎样才能产生一个安装脚本呢？
2007-9-13 11:38
howard_zhang
呵呵,下了帮顶一下
2007-9-13 14:41
liyongdong
顶
2007-9-13 19:36
terry_wu040802
good
2007-9-15 00:50
小人物
支持
2008-6-16 15:10
zluo
顶一下,我这几天也测试这个东东....
当测试完后,写一个文档和心得.
当测试完后,写一个文档和心得.
2008-6-23 10:24
wisdomone1
楼主强,我还在看oracle stream document,好好学下.谢谢楼主.
2008-7-21 12:12
zhangfei__
是生产环境下的配置吗
页: [1]
查看完整版本: [共享]三台主机streams复制的脚本
Powered by ITPUB论坛