Oracle双向Stream配置实践

摘要：本文描述了使用Oracle Stream流复制功能，进行双向流复制。主要用于实现实时的数据库同步和备份。 Oracle Stream功能是为提高数据库的高可用性而设计的。Oracle Stream利用高级队列技术，通过解析归档日志，将归档日志解析成DDL及DML语句，从而实现数据库之间的同步。这种技术可以将整个数据库、数据库中的对象复制到另一数据库中，通过使用Stream的技术，对归档日志的挖掘，可以在对主系统没有任何压力的情况下，实现对数据库对象级甚至整个数据库的同步。双向Stream配置以两台oracle10g服务器stream1（192.168.1.101）和stream2（192.168.1.102）配置双向stream为例。1. oracle归档模式，追加日志，global_name1.1. oracle归档模式确认oracle处于归档模式，如不是，则修改为归档模式。通过如下查看oracle是否处于归档模式：SQL> archive log list Database log mode Archive Mode如上则为归档模式，如为非归档模式，可通过如下命令修改为归档模式，sqlplus ‘/ as sysdba’ alter system set log_archive_dest_1=’location=/opt/test’ scope=spfile; alter system set log_archive_start=TRUE scope=spfile; alter system set log_archive_format=’ arch%t_%s_%r.arc’ scope=spfile; shutdown immediate; startup mount; alter database archivelog; alter database open;1.2. oracle追加日志使用管理员账户登录用于对没有主键（Primary Key）的Table进行修改等操作时记录日志用于流复制。可以基于Database级别或Table级别启用追加日志（Supplemental Log）。可以直接启用database基本的追加日志。sql为：alter database add supplemental log data;1.3. oracle global_name使用管理员账户登录在两台机器上修改global_namestream1:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to stream1.home;stream2:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to stream2.home;2. 配置tnsname在两台机器上分别配置tnsname，用来访问对方。在stream1：stream2 = （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 192.168.1.102）（PORT = 1521）））（CONNECT_DATA = （SID = orcl）（SERVER = DEDICATED）））在stream2：stream1= （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 192.168.1.101）（PORT = 1521）））（CONNECT_DATA = （SID = orcl）（SERVER = DEDICATED））） 3. 创建Stream管理用户对于两台机器都创建同样的管理用户。先创建表空间streams_tbs然后创建strmadmin管理用户和分配DBA，stream权限SQL> CREATE USER strmadmin IDENTIFIED BY pword DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;结果：User created.SQL> GRANT DBA TO strmadmin;结果：Grant succeeded.SQL> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE（ grantee => "strmadmin",
grant_privileges => true）;
END;
/ 结果：PL/SQL procedure successfully completed.4. 创建DB_LINK,STREAM队列使用strmadmin用户登录4.1. 创建DB_LINK在stream1上创建:CREATE DATABASE LINK stream2.home CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING "stream2";在stream2上创建:CREATE DATABASE LINK stream1.home CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING "stream1";4.2. 创建Stream队列在stream1，stream2上执行 SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE（）;PL/SQL procedure successfully completed.5. 同步stream1和stream2数据结构和数据主要为将主数据库user数据结构和数据，导入至从数据库。从而使两个数据库数据结构和数据一致。可以采用主数据库user导出dmp，从数据库导入或直接在从数据库通过db_link导入。6. stream1 -> stream2 配置使用strmadmin用户登录 6.1. 在stream1上创建propagationBEGINDBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES（schema_name => "c4", streams_name => "stream1_to_stream2", source_queue_name => "strmadmin.streams_queue",destination_queue_name => "strmadmin.streams_queue@stream2.home",include_dml => true,include_ddl => true,source_database => "stream1.home",inclusion_rule => true,queue_to_queue => true）;END; 其中schema_name => "c4", 为配置流复制的数据对象，也就是oracle user。以下配置中的schema_name都是同含义。可以通过dba_propagations查看结果：SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation;PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBL STATUS-------------------------------- ------------------------------ --------------------------------- --------------- --------STREAM1_TO_STREAM2 STREAMS_QUEUE STREAMS_QUEUE STREAM2.HOME ENABLED STATUS为ENABLED则表示创建成功 6.2. 在stream1上创建Capture进程BEGINDBMS_STREAMS_ADM.ADD_SCHEMA_RULES（schema_name => "hr",streams_type => "capture",streams_name => "capture_stream1",queue_name => "strmadmin.streams_queue",include_dml => true,include_ddl => true,inclusion_rule => true）;END;可以通过dba_capture查看：SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture; 查询到CAPTURE_NAME为CAPTURE_STREAM1 的记录则表明创建成功，此进程还未启动，所以STATUS会是DISABLED。 6.3. 在stream1进行与stream2同步SCNDECLAREv_scn NUMBER;BEGIN v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER（）;DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream2.home（ source_schema_name => "c4", source_database_name => "stream1.home", instantiation_scn => v_scn, recursive => true）; END; 6.4. stream2上创建apply进程BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES（ schema_name => "c4", streams_type => "apply", streams_name => "apply_stream2", queue_name => "strmadmin.streams_queue", include_dml => true, include_ddl => true, source_database => "stream1.home", inclusion_rule => true）; END; 通过查询语句 select * from dba_apply;可以查询到APPLY_NAME为APPLY_STREAM2的记录。7. stream2 -> stream1 配置使用strmadmin用户登录 7.1. 在stream2上创建propagationBEGINDBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES（schema_name => "c4", streams_name => "stream2_to_stream1", source_queue_name => "strmadmin.streams_queue",destination_queue_name => "strmadmin.streams_queue@stream1.home",include_dml => true,include_ddl => true,source_database => "stream2.home",inclusion_rule => true,queue_to_queue => true）;END; 其中schema_name => "c4", 为配置流复制的数据对象，也就是oracle user。以下配置中的schema_name都是同含义。可以通过dba_propagations查看结果：SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation;PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBL STATUS-------------------------------- ------------------------------ --------------------------------- --------------- --------STREAM2_TO_STREAM1 STREAMS_QUEUE STREAMS_QUEUE STREAM1.HOME ENABLED STATUS为ENABLED则表示创建成功 7.2. 在stream2上创建Capture进程BEGINDBMS_STREAMS_ADM.ADD_SCHEMA_RULES（schema_name => "hr",streams_type => "capture",streams_name => "capture_stream2",queue_name => "strmadmin.streams_queue",include_dml => true,include_ddl => true,inclusion_rule => true）;END;可以通过dba_capture查看：SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture; 查询到CAPTURE_NAME为CAPTURE_STREAM2 的记录则表明创建成功，此进程还未启动，所以STATUS会是DISABLED。 7.3. 在stream2进行与stream1同步SCNDECLAREv_scn NUMBER;BEGIN v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER（）;DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream1.home（ source_schema_name => "c4", source_database_name => "stream2.home", instantiation_scn => v_scn, recursive => true）; END; 7.4. stream1上创建apply进程BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES（ schema_name => "c4", streams_type => "apply", streams_name => "apply_stream1", queue_name => "strmadmin.streams_queue", include_dml => true, include_ddl => true, source_database => "stream2.home", inclusion_rule => true）; END; 通过查询语句 select * from dba_apply;可以查询到APPLY_NAME为APPLY_STREAM1的记录。 8. 启动8.1. stream1-> stream2均使用strmadmin用户登录在stream2上启动apply：SQL> BEGINDBMS_APPLY_ADM.SET_PARAMETER（apply_name => "apply_stream2", parameter => "disable_on_error", value => "n"）;END; 结果：PL/SQL procedure successfully completed. SQL> BEGINDBMS_APPLY_ADM.START_APPLY（apply_name => "apply_stream2"）;END; 结果：PL/SQL procedure successfully completed.通过如下sql查询状态：SQL> select apply_name,queue_name,status from dba_apply;APPLY_NAME QUEUE_NAME STATUS------------------------------ -------------------------- --------APPLY_STREAM2 STREAMS_QUEUE ENABLED 在stream1上启动capture： SQL>BEGINDBMS_CAPTURE_ADM.START_CAPTURE（capture_name => "capture_stream1"）;END; 通过如下sql查询启动状态：SQL> select capture_name,status from dba_capture;CAPTURE_NAME STATUS------------------------------ ------------CAPTURE_STREAM1 ENABLED 8.2. stream2 -> stream1在stream1上启动apply:SQL> BEGINDBMS_APPLY_ADM.SET_PARAMETER（apply_name => "apply_stream1", parameter => "disable_on_error", value => "n"）;END; PL/SQL procedure successfully completed. SQL> BEGINDBMS_APPLY_ADM.START_APPLY（apply_name => "apply_stream1"）;END; PL/SQL procedure successfully completed. SQL> select apply_name,queue_name,status from dba_apply;APPLY_NAME QUEUE_NAME STATUS------------------------------ -------------------------- --------APPLY_STREAM1 STREAMS_QUEUE ENABLED 在stream2上启动capture:SQL>BEGINDBMS_CAPTURE_ADM.START_CAPTURE（capture_name => "capture_stream2"）;END;SQL> select capture_name,status from dba_capture;CAPTURE_NAME STATUS------------------------------ ------------CAPTURE_STREAM2 ENABLED启动完成后，通过对两个库进行DDL，DML操作均可实时同步，验证配置成功。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址