oracle 流技术(转),用于学习

Oracle 流提供了一种在数据库之间共享消息和数据的简单而灵活的方法。例如, 您可以使用流, 作为事件捕获对数据库对象所进行的 DML 和 DDL 更改。然后您可以将这些事件传播到其他数据库, 从而有效地将数据库对象复制到其他数据库。 Oracle 流包括三个主要过程:
捕获, 用来捕获对重做日志中数据库对象的更改。这些更改将放置在一个队列中。
传播, 用来将更改从源数据库中的队列传播到目标数据库中的队列。
应用, 用来从目标队列区域检索更改并应用于数据库。

使用stream流复制环境作表级或模式schema级甚至DB级的数据复制，本脚本仅供参考(详细脚本可通过OEM生成)。
(流的核心技术：Logminer+Queue)：

/*************************************
设置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;

http://zhouwf0726.itpub.net/post/9689/407281
一个简单的表级复制的创建过程

Streams散记之一-如何清除流配置
Ref: http://www.eygle.com/archives/2007/10/remove_streams_configuration.html

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11423276/viewspace-994762/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11423276/viewspace-994762/