STRM–用Oracle Streams wizard生成配置脚本「建议收藏」

前几天都是用Package来完成streams的配置，但在管理上用OEM会感觉更为直观，当然对所有相关的Package很了解的话，也就都差不多了。在9i的OEM中看上去对streams的功能还不是很强。只是简单的一些应用。下面有时间就要搞10gR2，想跳过10gR1的。下面帖一个在OEM建立单表复制的过程脚本。

源数据库: GATES
目标数据库: CLONE

1. 设置操作
– Turn on supplemental logging and switch log file at source database.
– Create and set default tablespace for LogMiner at source database.
– 在源数据库中捕获以下各表, 然后传播并将更改应用于目标数据库。
“SCOTT”.”BONUS”

2. 导出/导入操作
– 导出从源数据库选择的所有对象。
– 将它们导入到目标数据库。

3. 启动操作
– 首先在目标数据库中启动应用进程。
– 在源数据库中启动捕获进程。

— Setup Operations:

REM
REM The following sections set up streams at the destination database
REM
ACCEPT dest_strmadmin_passwd PROMPT ‘请输入目标数据库 CLONE 中的用户 STRMADMIN 的口令 : ‘ HIDE
connect STRMADMIN/&dest_strmadmin_passwd@CLONE

REM
REM Add apply rules for tables at the destination database
REM
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘”SCOTT”.”BONUS”‘,
streams_type => ‘APPLY’,
streams_name => ‘STRMADMIN_GATES_VONGATES_’,
queue_name => ‘”STRMADMIN”.”STREAMS_QUEUE”‘,
include_dml => true,
include_ddl => true,
source_database => ‘GATES.VONGATES.COM’);
END;
/

REM
REM The following sections set up streams at the source database
REM
ACCEPT source_strmadmin_passwd PROMPT ‘请输入源数据库 GATES 中的用户 STRMADMIN 的口令 : ‘ HIDE
connect SYS/&source_dba_passwd@GATES as SYSDBA

REM
REM Turn on supplemental logging
REM
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;

REM
REM Switch log file
REM
ALTER SYSTEM SWITCH LOGFILE;

REM
REM Create logminer tablespace
REM
CREATE TABLESPACE LOGMNRTS DATAFILE ‘logmnrts_GATES.dbf’ SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

REM
REM Set logminer tablespace
REM
BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMNRTS’);
END;
/

connect STRMADMIN/&source_strmadmin_passwd@GATES

REM
REM Add capture rules for tables at the source database
REM
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘”SCOTT”.”BONUS”‘,
streams_type => ‘CAPTURE’,
streams_name => ‘STRMADMIN_CAPTURE’,
queue_name => ‘”STRMADMIN”.”STREAMS_QUEUE”‘,
include_dml => true,
include_ddl => true,
source_database => ‘GATES.VONGATES.COM’);
END;
/

REM
REM Add propagation rules for tables at the source database
REM
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ‘”SCOTT”.”BONUS”‘,
streams_name => ‘STRMADMIN_PROPAGATE’,
source_queue_name => ‘”STRMADMIN”.”STREAMS_QUEUE”‘,
destination_queue_name => ‘”STRMADMIN”.”STREAMS_QUEUE”@CLONE.VONGATES.COM’,
include_dml => true,
include_ddl => true,
source_database => ‘GATES.VONGATES.COM’);
END;
/

— Export/Import Operations:
exp USERID=”STRMADMIN”@GATES TABLES=”SCOTT”.”BONUS” FILE=tables.dmp GRANTS=Y ROWS=Y LOG=exportTables.log OBJECT_CONSISTENT=Y INDEXES=Y

imp USERID=”STRMADMIN”@CLONE FULL=Y CONSTRAINTS=Y FILE=tables.dmp IGNORE=Y GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log STREAMS_CONFIGURATION=Y STREAMS_INSTANTIATION=Y

— Startup Operations:
ACCEPT dest_strmadmin_passwd PROMPT ‘请输入目标数据库 CLONE 中的用户 STRMADMIN 的口令 : ‘ HIDE
connect STRMADMIN/&dest_strmadmin_passwd@CLONE

REM
REM Start apply process at the destination database
REM
DECLARE
v_started number;
BEGIN
SELECT decode(status, ‘ENABLED’, 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = ‘STRMADMIN_GATES_VONGATES_’;

if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => ‘STRMADMIN_GATES_VONGATES_’);
end if;
END;
/

ACCEPT source_strmadmin_passwd PROMPT ‘请输入源数据库 GATES 中的用户 STRMADMIN 的口令 : ‘ HIDE
connect STRMADMIN/&source_strmadmin_passwd@GATES

DECLARE
v_started number;
BEGIN
SELECT decode(status, ‘ENABLED’, 1, 0) INTO v_started
FROM DBA_CAPTURE WHERE CAPTURE_NAME = ‘CAPTURE’;

if (v_started = 0) then
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => ‘CAPTURE’);
end if;
END;
/

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