ORACLE流复制(全库级复制)

来源：互联网发布：樱桃3000和3494 mac 编辑：程序博客网时间：2024/05/18 17:25

ORACLE流复制(全库级复制)

本文档介绍dbms_streams_adm的maintain_global过程如何配置stream全库复制方法，适用于10gR2及以后版本。需要注意的是不支持sys/system 用户的变更。

一.配置前检查（源库和目标库都要配置）

1.开启归档设置：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /log/archivelog

Oldest online log sequence 91

Next log sequence to archive 95

Current log sequence 95

2.修改golbal_names

SQL> alter system setglobal_names=true;

System altered.

SQL> showparameter global_names

NAME TYPE VALUE

----------------------------------------------- ------------------------------

global_names boolean TRUE

3.检查数据库版本号（必须大于10.2.0.2.0）

SQL> show parameter compatible

NAME TYPE VALUE

------------------------------------ -----------------------------------------

compatible string 11.2.0.0.0

4.检查job线程个数（必须大于2）：

SQL> showparameter job_queue

NAME TYPE VALUE

----------------------------------------------- ------------------------------

job_queue_processes integer 1000

5. 设置streams_pool_size，指定适当的大小 ( 建议不小于200M) ，当然如果设置了sga_target，该参数也可由系统自动调节

SQL> alter systemset streams_pool_size=200M;

System altered.

SQL> showparameter streams_pool_size

NAME TYPE VALUE

----------------------------------------------- ------------------------------

streams_pool_size big integer 200M

二、准备环境

1. 配置tnsname文件

源库：

node2=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =node2.sink.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = node2.sink.com)

)

目标库：

node1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =node1.sink.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = node1.sink.com)

)

2.创建流复制管理员(源库和目标库)

SQL>conn /as sysdba

create tablespace stream_tbs datafile'/opt/oracle/oradata/node1/stream_tbs.dbf' size 200m;

创建用户

create user strmadmin identified by strmadmin defaulttablespace stream_tbs;

授权用户

grant dba to strmadmin;

execdbms_streams_auth.grant_admin_privilege('strmadmin');

3.创建dblink

源库

SQL>conn strmadmin/strmadmin

create database link node2.sink.com connect tostrmadmin identified by stradmin using 'node2';

验证一下：

source>select sysdate from dual@node2.zqgame.com;

SYSDATE

---------

12-MAR-13 --取到数据，表示DBlink连接没有问题

目标库

SQL>conn / as sysdba

create database link node1.sink.com connect tostrmadmin identified by strmadmin using 'node1';

验证一下:

target>select sysdate from dual@node1.zqgame.com;

SYSDATE

---------

12-MAR-13 –取到数据，表示DBlink连接没有问题

注意：启用global_name后 DBlink的名字必须是global_name，否则无法连接

4.创建目录

源：

source>conn / as sysdba

CREATE DIRECTORY source_dir as‘/opt/oracle/source_dir’;

grant read,write on directory source_dir to strmadmin;

目标库：

target>conn / as sysdba

CREATE DIRECTORY target _dir as‘/opt/oracle/target_dir ’;

grantread,write on directory target _dir to strmadmin;

三. 开始流复制配置，在源库执行MAINTAIN_GLOBAL过程

begin

dbms_streams_adm.maintain_global(

3 source_directory_object=>'source_dir',

4 destination_directory_object=>'target_dir',

5 source_database=>'node1.sink.com',

6 destination_database =>'node2.sink.com',

7 perform_actions=>true,

8 include_ddl=>true,

9 instantiation=>DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK);

10 end;

11 /

PL/SQL procedure successfully completed.
　　

在执行maintain_global时,源库与目标库必须创建directory,然后在源库执行,目标库几乎什么都不用做,stream环境已经配置完毕

四、验证数据是否同步

源库：

SQL> create user testaccount identified bytestaccount;

User created.

SQL> grant connect,resource to testaccount;

Grant succeeded.

SQL>insert into scott.dept values(99,'test','abc');

1row created.

SQL>commit;

Commitcomplete.

SQL>select * from scott.dept;

DEPTNO DNAME LOC

------------------------ -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

99 test abc

SQL>conn testaccount/testaccount

Connected.

SQL>create table test1(name varchar2(20),birthdate date,hometown varchar2(50));

Tablecreated.

SQL>insert into test1 values('tom', to_date('1986-10-18','yyyy-mm-dd'),'beijing');

1row created.

SQL>commit;

Commitcomplete.

SQL>select * from test1;

NAME BIRTHDATE HOMETOWN

-------------------- ------------------------ --------------------------

tom 1986-10-18 beijing

目标库：

SQL>select * from scott.dept;

DEPTNO DNAME LOC

-------------------------------------- --------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

99 test abc

SQL>select * from testaccount.test1;

NAME BIRTHDATE HOMETOWN

-------------------- ------------------ ----------

tom 1986-10-18 beijing

数据已经同步，至此，流复制全库同步搭建成功。