Oracle10gR2 Streams(三)全库复制之一

作者：NinGoo | 【转载须以超链接形式标明文章原始出处和作者信息】

在Oracle10gR2中，配置全库复制有两种方式，都是使用dbms_streams_adm中的过程来配置：

一种是pre_instantiation_setup/post_instantiation_setup结合rman来做初始化；另外一种方式是通过maintain_global结合expdp/impdp来做初始化。这一篇展示第一种配置方式，下一篇再来试验第二种方式。

pre_instantiation_setup/post_instantiation_setup这种方式只能配置本地捕获的全库复制，采用rman的duplicate来进行目标库的初始化，所以一开始目标库是不存在的。这两个过程都在源库中执行。

源库：ning.test
目标库：dest.test

一、执行pre_instantiation_setup

STRMADM@ning>DECLARE
2 empty_tbs DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
3 BEGIN
4 DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
5 maintain_mode => ‘GLOBAL’,
6 tablespace_names => empty_tbs,
7 source_database => ‘ning.test’,
8 destination_database => ‘dest.test’,
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 exclude_schemas => NULL,
14 exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +
15 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
16 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
17 END;
18 /

PL/SQL procedure successfully completed.

简单解释一下参数的含义：

maintain_mode：复制模式，可以是全库复制(global)或者表空间复制(transportable tablespaces)。
tablespace_names：如果是表空间复制则需要指定要复制的表空间的名字。
source_database：源库global name
destination_database：目标库global name
perform_actions：true直接执行配置，false的话则生成配置脚本到script_directory_object/script_name参数指定的位置
bi_directional： true双向复制，false单向复制
include_ddl：是否复制ddl操作
start_processes：配置完成后是否启动捕获/应用进程
exclude_schemas：指定不需要复制的schema，用逗号分割多个schema，*号则排除所有schema，NULL则不排除任何schema(sys/system/stxsys始终不复制)。该参数只在全库复制时有效。
exclude_flags：指定排除哪些对象的操作不进行复制。本例中排除streams不支持的对象上的dml和ddl操作。可以从dba_streams_unsupported查询不支持的对象。

这里只列出了部分参数，实际上该过程还有很多的参数，比如capture/propagation/apply进程的名字，捕获队列和应用队列的名字等，都可以通过参数指定。详细请参考官方文档。

二、使用rman初始化目标库

首先备份源库

backup database format=’f:\bak%u.ora’;

获得源库当前scn，目标库只需要恢复到该scn即可

STRMADM@ning>DECLARE
2 until_scn NUMBER;
3 BEGIN
4 until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
5 DBMS_OUTPUT.PUT_LINE(‘Until SCN: ‘ || until_scn);
6 END;
7 /
Until SCN: 463362

然后源库归档当前日志

alter system archive log current;

将备份和归档日志复制到目标主机的响应位置，本实验在同一台主机上进行，故不需要这一步了。

手工创建目标库的instance并启动到nomount状态

连接源库和目标库

C:\>rman target /@ning auxiliary /@dest

Recovery Manager: Release 10.2.0.1.0 – Production on Sat Nov 3 14:09:57 2007

connected to target database: NING (DBID=1224831792)
connected to auxiliary database: DEST (not mounted)

执行duplicate

RMAN> run
2> {
3> set until scn 463362;
4> duplicate target database to ‘dest’ nofilenamecheck open restricted;
5> }

创建目标库到源库的database link

create database link ning.test connect to strmadm identified by strmadm using ‘ning’;

三、执行post_instantiation_setup

STRMADM@ning>DECLARE
2 empty_tbs DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
3 BEGIN
4 DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(
5 maintain_mode => ‘GLOBAL’,
6 tablespace_names => empty_tbs,
7 source_database => ‘ning.test’,
8 destination_database => ‘dest.test’,
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 instantiation_scn => 463361,
14 exclude_schemas => NULL,
15 exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +
16 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
17 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
18 END;
19 /

PL/SQL procedure successfully completed.

其中instantiation_scn就是前面取得的当前SCN 463362-1。

由于前面rman复制的时候将目标库置于restricted session状态，执行post_instantaition_setup后目标库上的capture和apply进程都不会启动，需要解除restricted session

STRMADM@dest>alter system disable restricted session;

System altered.

四、验证复制是否成功

在源库上创建table并插入数据

STRMADM@ning>create table ning.teststrm(i int);

Table created.

STRMADM@ning>insert into ning.teststrm values(1);

1 row created.

STRMADM@ning>commit;

Commit complete.

稍等片刻在目标库查询

STRMADM@dest>select * from ning.teststrm;

I
———-
1

在目标库插入数据

STRMADM@dest>insert into ning.teststrm values(2);

1 row created.

STRMADM@dest>commit;

Commit complete.

在源库查询

STRMADM@ning>select * from ning.teststrm;

I
———-
1
2

可见全库双向复制已经配置成功。后续还需要进行冲突解决方案的配置，这里就不再深入下去了。

五、其他

前面有提到，pre_instantiation_setup/post_instantiation_setup过程实际上是先生成一个配置脚本，然后再执行脚本进行配置。这个两个步骤可以是一次完成，也可以分开完成，具体就是通过设置perform_actions参数来实现。假如我们只生成配置脚本：

STRMADM@ning>create directory dir_script as ‘f:\oracle’;

Directory created.

STRMADM@ning>DECLARE
2 empty_tbs DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
3 BEGIN
4 DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
5 maintain_mode => ‘GLOBAL’,
6 tablespace_names => empty_tbs,
7 source_database => ‘ning.test’,
8 destination_database => ‘dest.test’,
9 perform_actions => false,
10 script_directory_object =>’DIR_SCRIPT’,
11 script_name => ‘pre_script.sql’,
12 bi_directional => true,
13 include_ddl => true,
14 start_processes => true,
15 exclude_schemas => NULL,
16 exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +
17 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
18 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
19 END;
20 /

PL/SQL procedure successfully completed.

生成的f:\oracle\pre_script.sql脚本如下：

SET ECHO ON
SET VERIFY OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE;

– This script was generated with the following call:
– dbms_streams_adm.pre_instantiation_setup(
– maintain_mode=> ‘GLOBAL’,
– tablespace_names=> ,
– source_database=> ‘ning.test’,
– destination_database=> ‘dest.test’,
– perform_actions => FALSE,
– script_name=> ‘pre_script.sql’,
– script_directory_object=> ‘DIR_SCRIPT’,
– capture_queue_table=> ”,
– capture_queue_name=> ”,
– capture_queue_user=> ”,
– apply_queue_table=> ”,
– apply_queue_name=> ”,
– apply_queue_user=> ”,
– capture_name=> ”
– propagation_name=> ”,
– apply_name=> ”,
– bi_directional=> TRUE,
– include_ddl=> TRUE,
– start_processes=> TRUE,
– exclude_schemas=> ”,
– exclude_flags=> 14);
——————————————————————-
– get TNSNAME and streams admin user details for both the databases
——————————————————————–
PROMPT
PROMPT ‘Enter TNS Name of site 1 as parameter 1:’
DEFINE db1 = &1
PROMPT
PROMPT ‘Enter streams admin username for site 1 as parameter 2:’
DEFINE strm_adm_db1 = &2
PROMPT
PROMPT ‘Enter streams admin password for site 1 as parameter 3:’
DEFINE strm_adm_pwd_db1 = &3
PROMPT
PROMPT ‘Enter TNS Name of site 2 as parameter 4:’
DEFINE db2 = &4
PROMPT
PROMPT ‘Enter streams admin username for site 2 as parameter 5:’
DEFINE strm_adm_db2 = &5
PROMPT
PROMPT ‘Enter streams admin password for site 2 as parameter 6:’
DEFINE strm_adm_pwd_db2 = &6

– connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONNECT &strm_adm_db1/&strm_adm_pwd_db1@&db1
–
– Add database level supplemental logging
–
BEGIN
EXECUTE IMMEDIATE ‘ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX, FOREIGN KEY) COLUMNS’;
END;
/
–
– Set up queue “STRMADM”.”DEST$APPQ”
–
BEGIN
dbms_streams_adm.set_up_queue(
queue_table => ‘”STRMADM”.”DEST$APPQT”‘,
storage_clause => NULL,
queue_name => ‘”STRMADM”.”DEST$APPQ”‘,
queue_user => ”);
END;
/
–
– APPLY changes for all objects in the database
–
BEGIN
dbms_streams_adm.add_global_rules(
streams_type => ‘APPLY’,
streams_name => ”,
queue_name => ‘”STRMADM”.”DEST$APPQ”‘,
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => ‘DEST.TEST’,
inclusion_rule => TRUE,
and_condition => NULL);
END;
/
–
– Get tag value to be used for Apply
–
DECLARE
found BINARY_INTEGER := 0;
tag_num NUMBER;
apply_nm VARCHAR2(30);
apply_nm_dqt VARCHAR2(32);
BEGIN
SELECT apply_name INTO apply_nm
FROM dba_apply_progress
WHERE source_database = ‘DEST.TEST’;

apply_nm_dqt := ‘”‘ || apply_nm || ‘”‘;
— Use the apply object id as the tag
SELECT o.object_id INTO tag_num
FROM dba_objects o
WHERE o.object_name= apply_nm AND
o.object_type=’APPLY’;
LOOP
BEGIN
found := 0;
SELECT 1 INTO found FROM dba_apply
WHERE apply_name != apply_nm AND
apply_tag = hextoraw(tag_num);
EXCEPTION WHEN no_data_found THEN
EXIT;
END;
EXIT WHEN (found = 0);
tag_num := tag_num + 1;
END LOOP;
— alter apply
dbms_apply_adm.alter_apply(
apply_name => apply_nm_dqt,
apply_tag => hextoraw(tag_num));
END;
/
–
– Set up queue “STRMADM”.”NING$CAPQ”
–
BEGIN
dbms_streams_adm.set_up_queue(
queue_table => ‘”STRMADM”.”NING$CAPQT”‘,
storage_clause => NULL,
queue_name => ‘”STRMADM”.”NING$CAPQ”‘,
queue_user => ”);
END;
/
–
– PROPAGATE changes for all objects in the database
–
DECLARE
version_num NUMBER := 0;
release_num NUMBER := 0;
pos NUMBER;
initpos NUMBER;
q2q BOOLEAN;
stmt VARCHAR2(100);
ver VARCHAR2(30);
compat VARCHAR2(30);

BEGIN
BEGIN
stmt := ‘BEGIN dbms_utility.db_version@DEST.TEST(:ver, :compat); END;’;
EXECUTE IMMEDIATE stmt USING OUT ver, OUT compat;
— Extract version number
initpos := 1;
pos := INSTR(compat, ‘.’, initpos, 1);
IF pos > 0 THEN
version_num := TO_NUMBER(SUBSTR(compat, initpos, pos – initpos));
initpos := pos + 1;

— Extract release number
pos := INSTR(compat, ‘.’, initpos, 1);
IF pos > 0 THEN
release_num := TO_NUMBER(SUBSTR(compat, initpos,
pos – initpos));
initpos := pos + 1;
ELSE
release_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;
ELSE
version_num := TO_NUMBER(SUBSTR(compat, initpos));
END IF;

— use q2q propagation if compatibility >= 10.2
IF version_num > 10 OR
(version_num = 10 AND release_num >=2) THEN
q2q := TRUE;
ELSE
q2q := FALSE;
END IF;

EXCEPTION WHEN OTHERS THEN
q2q := FALSE;
END;

dbms_streams_adm.add_global_propagation_rules(
streams_name => ”,
source_queue_name => ‘”STRMADM”.”NING$CAPQ”‘,
destination_queue_name => ‘”STRMADM”.”NING$APPQ”@DEST.TEST’,
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => ‘NING.TEST’,
inclusion_rule => TRUE,
and_condition => NULL,
queue_to_queue => q2q);
END;
/
–
– Do not PROPAGATE back changes originating from DEST.TEST
–
DECLARE
and_condition VARCHAR2(2100);
tag raw(2000);
version_num NUMBER := 0;
release_num NUMBER := 0;
pos NUMBER;
initpos NUMBER;
q2q BOOLEAN;
stmt VARCHAR2(100);
ver VARCHAR2(30);
compat VARCHAR2(30);

— use q2q propagation if compatibility >= 10.2
IF version_num > 10 OR
(version_num = 10 AND release_num >=2) THEN
q2q := TRUE;
ELSE
q2q := FALSE;
END IF;

EXCEPTION WHEN OTHERS THEN
q2q := FALSE;
END;

SELECT a.apply_tag INTO tag
FROM dba_apply a, dba_apply_progress ap
WHERE a.apply_name = ap.apply_name AND
ap.source_database = ‘DEST.TEST’;

and_condition := ‘(:lcr.get_tag() = ”’ || tag || ”’)';
dbms_streams_adm.add_global_propagation_rules(
streams_name => ”,
source_queue_name => ‘”STRMADM”.”NING$CAPQ”‘,
destination_queue_name => ‘”STRMADM”.”NING$APPQ”@DEST.TEST’,
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => ‘NING.TEST’,
inclusion_rule => FALSE,
and_condition => and_condition,
queue_to_queue => q2q);
END;
/
–
– Disable propagation. Enable after destination has been setup
–
DECLARE
q2q VARCHAR2(10);
destn_q VARCHAR2(65);
BEGIN
SELECT queue_to_queue INTO q2q
FROM dba_propagation
WHERE source_queue_owner = ‘STRMADM’ AND
source_queue_name = ‘NING$CAPQ’ AND
destination_queue_owner = ‘STRMADM’ AND
destination_queue_name = ‘NING$APPQ’ AND
destination_dblink = ‘DEST.TEST’;

IF q2q = ‘TRUE’ THEN
destn_q := ‘”STRMADM”.”NING$APPQ”‘;
ELSE
destn_q := NULL;
END IF;

dbms_aqadm.disable_propagation_schedule(
queue_name => ‘”STRMADM”.”NING$CAPQ”‘,
destination => ‘DEST.TEST’,
destination_queue => destn_q);
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -24065 THEN NULL; — propagation already disabled
ELSE RAISE;
END IF;
END;
/
–
– CAPTURE changes for all objects in the database
–
BEGIN
dbms_streams_adm.add_global_rules(
streams_type => ‘CAPTURE’,
streams_name => ‘”NING$CAP”‘,
queue_name => ‘”STRMADM”.”NING$CAPQ”‘,
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => ‘NING.TEST’,
inclusion_rule => TRUE,
and_condition => NULL);
END;
/
–
– EXPORT: If export/import is the chosen instantiation method
– do the export here.
– Once export is complete, tranfer the export dump file(s) to the
– appropriate location on the system hosting the destination
– database.
–

–
– Start capture process NING$CAP
–
BEGIN
dbms_capture_adm.start_capture(
capture_name => ‘”NING$CAP”‘);
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -26666 THEN NULL; — CAPTURE process already running
ELSE RAISE;
END IF;
END;
/
–
– Start apply process applying changes from DEST.TEST
–
DECLARE
apply_nm VARCHAR2(32);
apply_nm_dqt VARCHAR2(32);
BEGIN
SELECT apply_name INTO apply_nm
FROM dba_apply_progress
WHERE source_database = ‘DEST.TEST’;

apply_nm_dqt := ‘”‘ || apply_nm || ‘”‘;
dbms_apply_adm.start_apply(
apply_name => apply_nm_dqt);
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -26666 THEN NULL; — APPLY process already running
ELSE RAISE;
END IF;
END;
/