【ORACLE 高可用】 ORACLE STREAM 基于单表的流复制 配置 案例

ORACLE STREAM 演示基于单表的流复制案例

        主数据库：

　　操作系统：REDHAT 5 　　IP地址：192.168.10.87
　　数据库：Oracle 11.2.0.3
　　ORACLE_SID：DB1 　Global_name：DB1

　　从数据库：
　　操作系统：REDHAT 5 　　IP地址：192.168.10.88
　　数据库：Oracle 11.2.0.3
　　ORACLE_SID：DB2 　Global_name：DB2

一、配置
 1.1设置初始化参数：
    SQL> alter system set aq_tm_processes=2 scope=both;
    　　--alter system set global_names=true scope=both;
    　　alter system set job_queue_processes=10 scope=both;
    　　alter system set parallel_max_servers=20 scope=both;
    　　alter system set undo_retention=3600 scope=both;
    　　alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
    　　alter system set streams_pool_size=200M scope=spfile;
    　　alter system set utl_file_dir='*' scope=spfile;
    　　alter system set open_links=4 scope=spfile;
    System altered.




 1.2 检查数据库是否为归档模式：
    SQL> archive log list;
    Database log mode           Archive Mode
    Automatic archival           Enabled
    Archive destination           /opt/app/oracle/oradata/DB1
    Oldest online log sequence     313
    Next log sequence to archive   315
    Current log sequence           315
    SQL>
    如果不是归档模式，先配置：
        startup mount;
    　　alter database archivelog;
    　　alter database open;

1.3 启用追加日志
　　可以基于Database级别或Table级别，启用追加日志(Supplemental Log)。在建立根据Schema粒度进行复制的Oracle Stream环境中，如果确认Schema下所有Table都有合理的主键(Primary Key)，则不再需要启用追加日志。


    　　#启用Database 追加日志
    　　alter database add supplemental log data;
    　　#启用Table追加日志
    　　alter table add supplement log group log_group_name(table_column_name) always;




1.4 建立stream 管理用户

    1.4.1创建主环境的Stream专用表空间
    　　create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB2/stream_tbs01.dbf'
        size 100M autoextend on maxsize unlimited segment space management auto;
    1.4.2将logminer的数据字典从system表空间转移到新建的表空间，防止撑满system表空间
    　　execute dbms_logmnr_d.set_tablespace('stream_tbs');
    1.4.3创建Stream管理用户
    　　create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
    1.4.4授权Stream管理用户
    　　grant connect,resource,dba,aq_administrator_role to strmadmin;


        SQL> begin
          2  dbms_streams_auth.grant_admin_privilege(
          3  grantee=>'strmadmin',
          4  grant_privileges=>true);
          5  end;
          6  /

        PL/SQL procedure successfully completed.

        SQL>

 1.5 在从环境中建立stream 管理用户

    SQL> archive log file;
    SP2-0718: illegal ARCHIVE LOG option
    SQL> archive log list;
    Database log mode           Archive Mode
    Automatic archival           Enabled
    Archive destination           /opt/app/oracle/oradata/DB1
    Oldest online log sequence     39
    Next log sequence to archive   41
    Current log sequence           41
    SQL> create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB1/stream_tbs01.dbf'
      2  size 100M autoextend on maxsize unlimited segment space management auto;
    Tablespace created.
    SQL> execute dbms_logmnr_d.set_tablespace('stream_tbs');
    PL/SQL procedure successfully completed.
    SQL> create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
    User created.
    SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;
    Grant succeeded.
    SQL> begin
      2  dbms_streams_auth.grant_admin_privilege(
      3  grantee=>'strmadmin',
      4  grant_privileges=>true);
      5  end;
      6  /
    PL/SQL procedure successfully completed.
    SQL>

    1.6 配置网络连接
        主从数据库(tnsnames.ora)中添加从数据库的配置
        DB1 =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.87)(PORT = 1521))
            (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = DB1)
            )
          )

        DB2 =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.10.88)(PORT = 1521))
            (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = DB2)
            )
          )


    1.7 创建DBlink

　　根据Oracle 10gR2 Stream官方文档，针对主数据库建立的数据库链的名字必须和从数据库的global_name相同。
　　如果需要修改global_name，执行“alter database rename global_name to xxx”。

　　1.7.1创建主数据库数据库链
　　#以strmadmin身份，登录主数据库。
　　connect strmadmin/oracle

　　#建立数据库链

　　create database link db2_link connect to strmadmin identified by oracle using 'DB2';

　　1.7.1创建从数据库数据库链

　　#以strmadmin身份，登录从数据库。

　　connect strmadmin/oracle

　　#建立数据库链

    SQL> create database link db2_link connect to strmadmin identified by oracle using 'DB2';

    Database link created.

    SQL> select name from v$database@db2_link;
    select name from v$database@db2_link
                                *
    ERROR at line 1:
    ORA-02085: database link DB2_LINK connects to DB1


    SQL> alter system set global_names=false;

    System altered.

    #测试连接是否成功
    SQL> select name from v$database@db2_link;

    NAME
    ---------------------------
    DB2
    

1.8 创建流队列
　　1.8.1创建Master流队列


    　　#以strmadmin身份，登录主数据库。
        SQL> connect strmadmin/oracle
        Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=>'db1_queue_table',
queue_name=>'db1_queue',
queue_user=>'strmadmin');
end;
/

        PL/SQL procedure successfully completed.

    查询队列：
    SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';

    OWNER        QUEUE_TABLE            NAME
    ---------------------------------------------------------
    STRMADMIN    DB1_QUEUE_TABLE        DB1_QUEUE
    STRMADMIN    DB1_QUEUE_TABLE        AQ$_DB1_QUEUE_TABLE_E


    1.8.2创建Backup流队列

    　　#以strmadmin身份，登录从数据库。
        SQL> connect strmadmin/oracle
        Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=> 'db2_queue_table',
queue_name=>'db2_queue',
queue_user=>'strmadmin');
end;
/

        PL/SQL procedure successfully completed.

        SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';

        OWNER       QUEUE_TABLE    NAME
        -------------------------------------------------
        STRMADMIN  DB2_QUEUE_TABLE   DB2_QUEUE
        STRMADMIN   DB2_QUEUE_TABLE  AQ$_DB2_QUEUE_TABLE_E



    1.9 创建捕获进程 (对单表进行同步)
    　　#以strmadmin身份，登录主数据库。(同样以hr用户做示例)。
    　　connect strmadmin/oracle
begin
dbms_streams_adm.add_table_rules(
table_name => 'HR.TST',
streams_type => 'capture',
streams_name =>'capture_stream',
queue_name => 'STRMADMIN.DB1_QUEUE',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;

        PL/SQL procedure successfully completed.


SQL> select capture_name,status from dba_capture;

CAPTURE_NAME          STATUS
-------------------- ------------------------
CAPTURE_STREAM            DISABLED



    1.10.创建传播进程
        connect strmadmin/oracle

begin
   dbms_streams_adm.add_table_propagation_rules(
   table_name =>'hr.tst',
   streams_name => 'db1_to_db2',
   source_queue_name =>'strmadmin.db1_queue',
   destination_queue_name => 'strmadmin.db2_queue@db2_link',
   include_dml => true,
   include_ddl => true,
   source_database =>'db1',
   inclusion_rule => true,
   queue_to_queue => true);
 end;
/


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_DBLINK                STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
DB1_TO_DB2                                           DB1_QUEUE                            DB2_QUEUE                                          DB2_LINK                        ENABLED

SQL>


        #修改propagation休眠时间为0，表示实时传播LCR。
begin
dbms_aqadm.alter_propagation_schedule(
queue_name =>'db1_queue',
destination=>'db2_link',
destination_queue=>'db2_queue',
latency => 0
);
end;
/

        PL/SQL procedure successfully completed.


    1.11 创建应用进程
        　　#以strmadmin身份，登录从数据库。
        　　connect strmadmin/oracle

begin
dbms_streams_adm.add_table_rules(
table_name => 'hr.tst',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'strmadmin.db2_queue',
include_dml => true,
include_ddl => true,
source_database =>'db1',
inclusion_rule => true);
end;
/

SQL> select apply_name,queue_name,status from dba_apply;

APPLY_NAME
--------------------------------------------------------------------------------
QUEUE_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
DB2_QUEUE
DISABLED



    1.12 实例复制
        可以使用EXP/IMP 把用户HR 数据导入到从库中。
        因我的机子前面做了GGS，这里不再进行实例复制。
        exp userid= hr/hr@db1 file='/tmp/hr.dmp'
        imp userid=system/sa file='/tmp/hr.dmp' ignore=y fromuser=hr touser=hr


        因为只同步一个表，我在DB2中也建立了一个连接。并使用以下SQL 建立一个表：
        SQL> create table hr.tst as select * from hr.tst@db1_link;
        Table created.
        SQL> select * from hr.tst;
        no rows selected
        SQL>

    1.13 直接设置SCN的方式进行实例化：

    ---获取源库互置用户的SCN
    connect strmadmin/oracle@DB1     
    SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;

    DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
    -----------------------------------------
                      4486128

    ---设置为目标库互置用户的SCN
    connect strmadmin/oracle@db2       
    
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name    => 'hr.tst',
source_database_name  => 'db1',
instantiation_scn     => 4605643);
END;
/

在目标端修改SCN前后查询：


SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                  4597643

SQL> BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name    => 'hr.tst',
source_database_name  => 'db1',
instantiation_scn     => 4597643);
END;
/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                  4597431

SQL>


从库启动 APPLY:

SQL> exec dbms_apply_adm.start_apply('apply_stream');

PL/SQL procedure successfully completed.

SQL> select apply_name,status from dba_apply;

APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED


主库启动CAPTURE

SQL> select capture_name,status from dba_capture;

CAPTURE_NAME                                           STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM                                           DISABLED

SQL> execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');

PL/SQL procedure successfully completed.

SQL> select capture_name,status from dba_capture;

CAPTURE_NAME                                           STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM                                           ENABLED

SQL>



    1.13  启动STREAM
    　　#以strmadmin身份，登录从数据库。
    　　connect strmadmin/oracle
    　　#启动Apply进程
begin
dbms_apply_adm.start_apply(
apply_name => 'APPLY_STREAM');
end;
/
    　　#以strmadmin身份，登录主数据库。
    　　connect strmadmin/oracle
    　　#启动Capture进程
begin
execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');
end;
/

　    1.14 停止STREAM
    　　#以strmadmin身份，登录主数据库。
    　　connect strmadmin/oracle
    　　#停止Capture进程
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_db1');
end;
/

    　　#以strmadmin身份，登录从数据库。
    　　connect strmadmin/oracle
    　　#停止Apply进程
begin
dbms_apply_adm.stop_apply(
apply_name => 'APPLY_STREAM');
end;
/



    1.15 清除所有配置信息
    　　要清除Stream配置信息，需要先执行1.14，先停止Stream进程。

    　　#以strmadmin身份，登录主数据库。
    　　connect strmadmin/oracle

    　　exec DBMS_STREAMS_ADM.remove_streams_configuration();

    2#方法
    exeucte dbms_streams_adm.remove_streams_queue(queue_name=>'db2_queue',cascade=>true,drop_unused_queue_table=>true);

    　　#以strmadmin身份，登录从数据库。
    　　connect strmadmin/oracle
    　　exec DBMS_STREAMS_ADM.remove_streams_configuration();
    2#方法
      exec dbms_apply_adm.stop_apply(apply_name=>'apply_db2');
      exec dbms_streams_adm.remove_queue(queue_name=>'db1_queue',cascade=>true,drop_unused_queue_table=>true);
            
        

二、测试数据同步

    查询状态：

    1.主库

    SQL> conn strmadmin/oracle
    Connected.
    SQL> select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;

    CAPTURE_NAME                                           QUEUE_NAME                                  STATUS               CAPTURED_SCN APPLIED_SCN
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------ ------------ -----------
    CAPTURE_STREAM                                           DB1_QUEUE                                  ENABLED                4480864    4480864

    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_DBLINK                      STATUS
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
    DB1_TO_DB2                                           DB1_QUEUE                                  DB2_QUEUE                                          DB2_LINK                              ENABLED


    SQL> select apply_name,error_number,apply_captured,status from dba_apply;

    APPLY_NAME
    --------------------------------------------------------------------------------
    ERROR_NUMBER APPLY_CAP STATUS
    ------------ --------- ------------------------
    APPLY_STREAM
           26714 YES       ABORTED


    测试：插入，修改，删除，查看目标服务器，数据同步完成。

在源库中删除表HR.TST，目标端出错：alert_db2.log    
后，再目标库中重启APPLY进程。看到数据已同步过来了。


SQL> execute dbms_apply_adm.stop_apply(apply_name => 'APPLY_STREAM');

PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.start_apply('apply_stream');

PL/SQL procedure successfully completed.

SQL> select apply_name,status from dba_apply;

APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED

DB2_QUEUE
       26687
ORA-26687: no instantiation SCN provided for "HR"."" in source database "DB1"
2013-09-08 17:34:31


QUEUE_NAME
--------------------------------------------------------------------------------
ERROR_NUMBER
------------
ERROR_MESSAGE
--------------------------------------------------------------------------------
ERROR_CREATION_TIME
-------------------
DB2_QUEUE
      54
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2013-09-08 17:43:11


SQL> select * from hr.tst;

    ID
----------
     1