用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 setup streams at the destination database
REM
ACCEPT dest_strmadmin_passwd PROMPT '请输入目标数据库
CLONE 中的用户 STRMADMIN 的口令 : 'HIDE
connect STRMADMIN/&dest_strmadmin_passwd@CLONE
REM
REM Addapply rules fortables 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 setup streams at the source database
REM
ACCEPT source_strmadmin_passwd PROMPT '请输入源数据库
GATES 中的用户 STRMADMIN 的口令 : 'HIDE
connect SYS/&source_dba_passwd@GATESasSYSDBA
REM
REM Turn onsupplemental logging
REM
ALTERDATABASEADDSUPPLEMENTAL LOGDATA (PRIMARYKEY,UNIQUEINDEX)
COLUMNS;
REM
REM Switch logfile
REM
ALTERSYSTEM SWITCH LOGFILE;
REM
REM Createlogminer tablespace
REM
CREATETABLESPACE LOGMNRTS DATAFILE 'logmnrts_GATES.dbf'
SIZE 25M REUSE
AUTOEXTEND ONMAXSIZE UNLIMITED;
REM
REM Setlogminer tablespace
REM
BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/
connect STRMADMIN/&source_strmadmin_passwd@GATES
REM
REM Addcapture rules fortables 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 Addpropagation rules fortables 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:
expUSERID="STRMADMIN"@GATESTABLES="SCOTT"."BONUS"
FILE=tables.dmp GRANTS=Y ROWS=Y LOG=exportTables.log
OBJECT_CONSISTENT=Y INDEXES=Y
imp USERID="STRMADMIN"@CLONEFULL=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
SELECTdecode(status, 'ENABLED', 1, 0) INTOv_started
FROMDBA_APPLY WHEREAPPLY_NAME ='STRMADMIN_GATES_VONGATES_';
if(v_started =0) then
DBMS_APPLY_ADM.START_APPLY(apply_name =>'STRMADMIN_GATES_VONGATES_');
endif;
END;
/
ACCEPT source_strmadmin_passwd PROMPT '请输入源数据库
GATES 中的用户 STRMADMIN 的口令 : 'HIDE
connect STRMADMIN/&source_strmadmin_passwd@GATES
DECLARE
v_started number;
BEGIN
SELECTdecode(status, 'ENABLED', 1, 0) INTOv_started
FROMDBA_CAPTURE WHERECAPTURE_NAME ='CAPTURE';
if(v_started =0) then
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name =>'CAPTURE');
endif;
END;
/
本文转自
http://oracle.itpub.net/post/20957/146515
前几天都是用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 setup streams at the destination database
REM
ACCEPT dest_strmadmin_passwd PROMPT '请输入目标数据库
CLONE 中的用户 STRMADMIN 的口令 : 'HIDE
connect STRMADMIN/&dest_strmadmin_passwd@CLONE
REM
REM Addapply rules fortables 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 setup streams at the source database
REM
ACCEPT source_strmadmin_passwd PROMPT '请输入源数据库
GATES 中的用户 STRMADMIN 的口令 : 'HIDE
connect SYS/&source_dba_passwd@GATESasSYSDBA
REM
REM Turn onsupplemental logging
REM
ALTERDATABASEADDSUPPLEMENTAL LOGDATA (PRIMARYKEY,UNIQUEINDEX)
COLUMNS;
REM
REM Switch logfile
REM
ALTERSYSTEM SWITCH LOGFILE;
REM
REM Createlogminer tablespace
REM
CREATETABLESPACE LOGMNRTS DATAFILE 'logmnrts_GATES.dbf'
SIZE 25M REUSE
AUTOEXTEND ONMAXSIZE UNLIMITED;
REM
REM Setlogminer tablespace
REM
BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/
connect STRMADMIN/&source_strmadmin_passwd@GATES
REM
REM Addcapture rules fortables 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 Addpropagation rules fortables 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:
expUSERID="STRMADMIN"@GATESTABLES="SCOTT"."BONUS"
FILE=tables.dmp GRANTS=Y ROWS=Y LOG=exportTables.log
OBJECT_CONSISTENT=Y INDEXES=Y
imp USERID="STRMADMIN"@CLONEFULL=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
SELECTdecode(status, 'ENABLED', 1, 0) INTOv_started
FROMDBA_APPLY WHEREAPPLY_NAME ='STRMADMIN_GATES_VONGATES_';
if(v_started =0) then
DBMS_APPLY_ADM.START_APPLY(apply_name =>'STRMADMIN_GATES_VONGATES_');
endif;
END;
/
ACCEPT source_strmadmin_passwd PROMPT '请输入源数据库
GATES 中的用户 STRMADMIN 的口令 : 'HIDE
connect STRMADMIN/&source_strmadmin_passwd@GATES
DECLARE
v_started number;
BEGIN
SELECTdecode(status, 'ENABLED', 1, 0) INTOv_started
FROMDBA_CAPTURE WHERECAPTURE_NAME ='CAPTURE';
if(v_started =0) then
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name =>'CAPTURE');
endif;
END;
/
本文转自
http://oracle.itpub.net/post/20957/146515
