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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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);

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;

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;
/

本文网址：http://www.ningoo.net/html/2007/oracle10gr2_streams_database_level_replication_1.html

订阅到Google | 收藏到Del.icio.us | 推荐到鲜果

Posted by NinGoo 2007-11-03 14:48 星期六

相关文章	随机文章
Oracle10gR2 Streams(九)配置过程中的错误处理及清除配置 Oracle10gR2 Streams(八)表复制 Oracle10gR2 Streams(七)用户级复制 Oracle10gR2 Streams(六)表空间复制 Oracle10gR2 Streams(五)全库复制之三 Oracle10gR2 Streams(四)全库复制之二	生死狙击，阴谋与英雄关于parameter_max_servers参数 Oracle11g新特性：server result cache续二 sqlplus小窍门：通过http执行远程脚本升级wordpress到2.7 迷你围棋