|
首先，我们需要了解一下Oracle流，Oracle流提供了一种在数据库之间共享消息和数据的简单而灵活的方法。比如, 你可以使用流, 作为事件捕获对数据库对象所进行的 DML 和 DDL 更改。然后你可以将这些事件传播到其他数据库, 从而有效地将数据库对象复制到其他数据库。
Oracle流主要包括三个主要的过程:
捕获:用来捕获对重做日志中数据库对象的更改。这些更改将放置在一个队列中。
传播: 用来将更改从源数据库中的队列传播到目标数据库中的队列。
应用: 用来从目标队列区域检索更改并应用于数据库。
使用stream流复制环境作表级或模式schema级甚至DB级的数据复制，
注释：此示例脚本大家可以根据各自的实际情况酌情更改，详细的脚本可以通过来OEM生成。
/*************************************
设置stream流复制环境脚本(表级或模式级流复制)
Created by xsb on 2006-9-8
**************************************/
--目标库：
ACCEPT dest_dba_passwd PROMPT '请输入目标数据库 xsb2 中的用户 SYS 的口令 : ' HIDE
ACCEPT dest_strmadmin_passwd PROMPT '请输入目标数据库 xsb2 中的用户 STRMADMIN 的口令 : ' HIDE
connect SYS/&dest_dba_passwd@xsb2 as SYSDBA
drop user strmadmin cascade;
--drop table xsb.t1 purge;
drop user xsb cascade;
grant dba to xsb identified by a;
CREATE USER strmadmin IDENTIFIED BY &dest_strmadmin_passwd
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
GRANT DBA TO strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
pause ...
conn strmadmin/&dest_strmadmin_passwd@xsb2
drop DATABASE LINK ORCL;
CREATE DATABASE LINK ORCL CONNECT TO strmadmin IDENTIFIED BY &dest_strmadmin_passwd
USING 'xsb';
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.strm_queue',
queue_name => 'strmadmin.strm_queue',
queue_user => 'strmadmin');
END;
/
/*********************************************************************
--表级strm
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'xsb.t1',
streams_type => 'apply',
streams_name => 'strm_apply',
queue_name => 'strmadmin.strm_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM',
inclusion_rule => true);
END;
/
*********************************************************************/
--模式级strm
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => '"XSB"',
streams_type => 'APPLY',
streams_name => 'strm_apply',
queue_name => 'strmadmin.strm_queue',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
--源库：
ACCEPT source_dba_passwd PROMPT '请输入源数据库 XSB 中的用户 SYS 的口令 : ' HIDE
connect SYS/&source_dba_passwd@XSB as SYSDBA
/************************************************
startup mount
alter database archivelog;
alter database open;
archive log list;
************************************************/
--ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
--ALTER SYSTEM SWITCH LOGFILE;
drop user strmadmin cascade;
CREATE USER strmadmin IDENTIFIED BY &dest_strmadmin_passwd
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
GRANT DBA TO strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
pause ...
conn strmadmin/&dest_strmadmin_passwd@xsb
drop DATABASE LINK orcl2 ;
CREATE DATABASE LINK orcl2 CONNECT TO strmadmin IDENTIFIED BY &dest_strmadmin_passwd
USING 'xsb2';
/***************************************
CREATE DIRECTORY admin_dir AS 'e:';
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => false,
file_name => 'grant_strms_privs.sql',
directory_name => 'admin_dir');
END;
/
***************************************/
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.strm_queue',
queue_name => 'strmadmin.strm_queue',
queue_user => 'strmadmin');
END;
/
/*********************************************************************
--表级strm
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'xsb.t1',
streams_type => 'capture',
streams_name => 'strm_capture',
queue_name => 'strmadmin.strm_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => NULL,
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'xsb.t1',
streams_name => 'strm_propagation',
source_queue_name => 'strmadmin.strm_queue',
destination_queue_name => 'strmadmin.strm_queue@orcl2',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM',
inclusion_rule => true,
queue_to_queue => true);
END;
/
*********************************************************************/
--模式级strm
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => '"XSB"',
streams_type => 'CAPTURE',
streams_name => 'strm_capture',
queue_name => 'strmadmin.strm_queue',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => '"XSB"',
streams_name => 'STRM_PROPAGATE',
source_queue_name => 'strmadmin.strm_queue',
destination_queue_name => 'strmadmin.strm_queue@orcl2',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
--导出源
--表级
--host exp USERID="STRMADMIN"@XSB TABLES="XSB"."T1" FILE=tables.dmp GRANTS=Y ROWS=Y
LOG=exportTables.log OBJECT_CONSISTENT=Y INDEXES=Y
--模式级
host exp USERID="STRMADMIN"@XSB OWNER="XSB" FILE=schemas.dmp GRANTS=Y ROWS=Y
LOG=exportSchemas.log OBJECT_CONSISTENT=Y
--导入目标
--表级
--host imp USERID="STRMADMIN"@xsb2 FULL=Y CONSTRAINTS=Y FILE=tables.dmp IGNORE=Y
GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log STREAMS_CONFIGURATION=N STREAMS_INSTANTIATION=Y
--模式级
host imp USERID="STRMADMIN"@xsb2 FULL=Y CONSTRAINTS=Y FILE=schemas.dmp IGNORE=Y GRANTS=Y
ROWS=Y COMMIT=Y LOG=importSchemas.log STREAMS_CONFIGURATION=N STREAMS_INSTANTIATION=Y
pause ...
--目标库
conn strmadmin/&dest_strmadmin_passwd@xsb2
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'STRM_APPLY';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRM_APPLY');
end if;
END;
/
--源库：
conn strmadmin/&dest_strmadmin_passwd@xsb
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'STRM_CAPTURE';
if (v_started = 0) then
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STRM_CAPTURE');
end if;
END;
/
pause ...
--测试：
conn strmadmin/&dest_strmadmin_passwd@xsb
delete xsb.t1 where id1> (select id1 from (select row_number() over(order by id1)
rn ,id1 from xsb.t1 ) where rn=5);
insert into xsb.t1 select id1+(select max(id1) from xsb.t1),id2 from xsb.t1;
commit;
select * from xsb.t1;
conn strmadmin/&dest_strmadmin_passwd@xsb2
select * from xsb.t1;
select * from xsb.t1; | |
