Oracle Stream Replcation 配置

作者: 风亡小窝 | 来源:发表于2017-10-25 15:09 被阅读116次

Oracle Stream Replcation 配置
Oracle启动过程及常见错误
centos安装oracle11.2.0.4.0
Kafka常用资料
plsql配置连接远程oracle
启动和停止Oracle
php连接Oracle数据库
2.nodejs通过stream方式加载页面 / 通过fs模块加
Oracle知识点笔记
oracle数据库落地成本地文件

环境：

主数据库
操作系统：linux
IP地址：172.168.68.173
数据库：Oracle 11.2.0.3.0
ORACLE_SID：sm1
Global_name：sm1

从数据库
操作系统：linux
IP地址：172.168.68.172
数据库：Oracle 11.2.0.3.0
ORACLE_SID：sm2
Global_name：sm2

主数据库 oracle 版本不能高于从数据库 oracle 版本

Global_name 必须要跟 SID 相同(修改SID请看文章末的参考链接)

注意 oracle_home 不能以 / 结尾

--查看 oracle_sid
echo $ORACLE_SID
--查看 oracle_home
echo $ORACLE_HOME
--查看 oracle 版本
select * from v$version;

切换到 oracle 用户
su - oralce
登陆到 oracle
sqlplus / as sysdba

源数据库和目标数据库必须是归档的

SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list /* 查看修改结果 */

源数据库和目的数据库均需要设置的参数：

alter system set global_names=true scope = both;
#默认为 false,  Database Link 使用的是数据库的 global_name。

alter system set job_queue_processes = 10 scope=both;
alter system set sga_target = 300m scope=spfile;
alter system set open_links=4 scope=spfile;
alter system set statistics_level='TYPICAL' scope=both;
alter system set "_job_queue_interval"=1 scope=spfile;
alter system set aq_tm_processes=2 scope=both;
alter system set streams_pool_size=200m scope=both;

查看修改结果

show parameter processes;
show parameter session;
show parameter stream

在源数据库上启用追加日志

可以基于Database级别或Table级别，启用追加日志（Supplemental Log）。

/* 启用 Database 追加日志 */
alter database add supplemental log data;

/* 启用 Table 追加日志 */
alter table add supplement log group log_group_name(table_column_name) always;

重启数据库，使修改生效

源数据库和目的数据库创建相同表空间和用户并赋权

创建表空间：

create tablespace streams_tbs datafile '.\streams_tbs.dbf' size 100M reuse autoextend on maxsize unlimited;

/* 查看所有表空间名和状态 */
select tablespace_name, status from dba_tablespaces;
/* 查询当前表空间属性 */
select * from dba_tablespaces where tablespace_name='STREAMS_TBS';

创建用户：

CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;

赋权：

/* 授予 dba 权限简化配置 */
GRANT DBA to strmadmin;
/* 赋予流管理特权 */
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('strmadmin');

配置 listener.ora 和 tnsnames.ora
源数据库

# sm1/tnsnames.ora
sm1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.173)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sm1)
      (SERVER = DEDICATED)
    )
  )

sm2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.172)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sm2)
      (SERVER = DEDICATED)
    )
  )

# sm1/lisenter.ora
SID_LIST_LISTENER =
  (SID_LIST =   
    (SID_DESC =
        (GLOBAL_DBNAME = sm1)
        (ORACLE_HOME = /opt/oracle/product/11.2.0/dba_home)
        (SID_NAME = sm1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.173)(PORT = 1521))
    )
  )

目的数据库

# sm2/tnsnames.ora
sm1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.173)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sm1)
      (SERVER = DEDICATED)
    )
  )

sm2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.172)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sm2)
      (SERVER = DEDICATED)
    )
  )

# sm2/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =   
    (SID_DESC =
        (GLOBAL_DBNAME = sm2)
        (ORACLE_HOME = /opt/oracle/product/11.2.0/dba_home)
        (SID_NAME = sm2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.172)(PORT = 1521))
    )
  )

创建 database link
先用strmadmin登陆，在创建dblink

conn strmadmin/strmadmin@sm1;
create database link sm2 connect to strmadmin identified by strmadmin using 'sm2';

conn strmadmin/strmadmin@sm2;
create database link sm1 connect to strmadmin identified by strmadmin using 'sm1';

/*查看 db_link */
select * from dba_db_links;
/*移除 db_link */
drop public database link link_name;

当 global_names = true 时要注意以下几点：

源和目的数据库的global_name不能相同

你在本地建立的DBLINK的名称必须和远程数据库的Global_name必须相同
查看global_names：show parameter global_name;
查看global_name ：select * from global_name;
修改global_name ：update global_name set global_name='ORCL';
更多请参考==>这儿

修改global_name后请重启数据库使其生效

源库与目标库必须创建directory

create directory dir_local as './local_dir';

/*查看 directory*/
select * from dba_directories;
/*删除 directory*/
drop directory dir_name;

8.在源库执行MAINTAIN_xxxxx过程

-- 登陆到strmadmin账号
conn strmadmin/strmadmin

/* 全库级复制 */
begin
    dbms_streams_adm.maintain_global(
        source_directory_object         =>'dir_local',
        destination_directory_object    =>'dir_local',
        source_database                 =>'sm1',
        destination_database            =>'sm2',
        capture_name                    =>'DBXA_CAP',  
        capture_queue_name              =>'DBXA_CAP_Q',  
        capture_queue_table             =>'DBXA_CAP_Q_T',  
        propagation_name                =>'DBXA_TO_DBXB_PROP',  
        apply_name                      =>'DBXA_APP',  
        apply_queue_name                =>'DBXA_CAP_Q',  
        apply_queue_table               =>'DBXA_CAP_Q_T', 
        bi_directional                  =>FALSE,
        perform_actions                 =>TRUE,
        include_ddl                     =>TRUE,
        instantiation                   =>DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK
    );
end;
/

如果不需要用户为streams组件定义有意义的名称,例程可以得到简化，如下：

/* 全库级复制 */
begin
    dbms_streams_adm.maintain_global(
        source_database                 =>'sm1',
        destination_database            =>'sm2',
        source_directory_object         =>'dir_local',
        destination_directory_object    =>'dir_local',
        bi_directional                  => FALSE,
        perform_actions                 =>TRUE,
        include_ddl                     =>TRUE,
        instantiation                   =>DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK
    );
end;
/

省略stream组件的名称后，组件的名称由例程自行生成。

/* 表空间级复制 */
declare  
    ts_names dbms_streams_tablespace_adm.tablespace_set;  
begin  
    ts_names(1) := 'ts_name1';  
    ts_names(2) := 'ts_name2';  
    ts_names(3) := 'ts_name3';  
    dbms_streams_adm.maintain_tts(  
        tablespace_names                => ts_names ,  
        source_database                 => 'sm1',  
        destination_database            => 'sm2',  
        source_directory_object         => 'dir_local ',  
        destination_directory_object    => 'dir_local ',  
        bi_directional                  => FALSE,
        perform_actions                 => TRUE
    );  
end;  
/

/* scheme 级复制 */
declare  
    schemas dbms_utility.uncl_array;  
begin  
    schemas(1) := 'chenhao';  
    dbms_streams_adm.maintain_schemas (  
        schema_names                    => schemas,
        source_database                 => 'sm1',
        destination_database            => 'sm2',
        source_directory_object         => 'dir_local',
        destination_directory_object    => 'dir_local',
        bi_directional                  => FALSE,
        include_ddl                     => TRUE,
        instantiation               => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK  
    );  
end;  
/

/*表级别的复制*/
declare  
    tables dbms_utility.uncl_array;  
begin  
    tables(1) := 'SCOTT.DEPT';  
    tables(2) := 'SCOTT.EMP';  
    tables(3) := 'HR.SALGRADE';  
    tables(4) := 'HR.BONUS';  
  
    dbms_streams_adm.maintain_tables (  
        table_names                     => tables,  
        source_database                 => 'sm1',  
        destination_database            => 'sm2',  
        source_directory_object         => 'dir_local',  
        destination_directory_object    => 'dir_local',  
        bi_directional                  => FALSE,  
        include_ddl                     => TRUE,  
        perform_actions                 => TRUE,  
        instantiation                   => DBMS_STREAMS_ADM.INSTANTIATION_TABLE  
    );  
end;  
/

bi_directional默认为false，表示单向复制。如需要双向复制则设置为true。
更多详细的参数解释请参考 => here

/*修改传播时延，使其尽快的复制到目的数据库*/
begin  
    dbms_aqadm.alter_propagation_schedule(  
        queue_name => 'SM1$CAPQ',  
        destination => 'sm2',  
        destination_queue => 'SM2$APPQ',  
        latency => 5  
    );  
end;  
/

如果执行过程中遭遇错误，则使用以下例程清除错误，然后重新执行

begin
  dbms_streams_adm.recover_operation(
    script_id=>'5C342452BA72557DE050007F01001BCA',
    operation_mode=>'ROLLBACK'
);
end;
/

查看正在执行的 procedure，必要时清空 DBA_RECOVERABLE_SCRIPT

select * from DBA_RECOVERABLE_SCRIPT;

现在应该配置成功了，你可以进行测试了。

-- 查看捕获，传播，应用进程
select capture_name, status, queue_name from dba_capture;
select propagation_name, status, source_queue_name, destination_queue_name from dba_propagation;
select apply_name, status, queue_name from dba_apply;

-- 移除捕获，传播，应用进程
exec dbms_capture_adm.drop_capture('capture_name');
exec dbms_propagation_adm.drop_propagation('propagation_name');
exec dbms_apply_adm.drop_apply('apply_name');


-- 查看队列
select owner, name from dba_queues;
-- 移除对列
exec dbms_streams_adm.remove_queue(
                 queue_name => 'queue_name',
                 cascade => true,
                 drop_unused_queue_table => true);

--带数据完成源端exp和目端的import：
exp USERID=SYSTEM/manager@sm1 OWNER=SYSTEM FILE=./STRM.dmp LOG=./export.log OBJECT_CONSISTENT=Y STATISTICS = NONE    
imp USERID=SYSTEM/manager@sm2 FULL=Y CONSTRAINTS=Y FILE=./STRM.dmp IGNORE=Y COMMIT=Y LOG=./import.log STREAMS_INSTANTIATION=Y  
 
--或仅作实例化(不带数据)：
exp USERID=SYSTEM/manager@sm1 OWNER=SYSTEM FILE=./STRM.dmp LOG=./export.log OBJECT_CONSISTENT=Y STATISTICS = NONE ROWS=NO  
imp USERID=SYSTEM/manager@sm2 FULL=Y CONSTRAINTS=Y FILE=./STRM.dmp IGNORE=Y COMMIT=Y LOG=./import.log STREAMS_INSTANTIATION=Y

查看scn

set serveroutput on
DECLARE
        iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
        iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
        DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn); 
END;
/

设置为目标库互置用户的SCN

BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
        source_schema_name => 'SYSTEM',
        source_database_name => 'DBA',
        instantiation_scn => &iscn);
END;
/

启动apply进程

BEGIN
  DBMS_APPLY_ADM.START_APPLY(apply_name=>'APPLY$_SM1_181');
END;
/

启动capture进程

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
   capture_name=>'SM1$CAP');
END;
/

查询进程错误

-- capture进程错误
select error_number,error_message from dba_capture;
-- apply进程错误
select error_number,error_message,queue_name,error_creation_time from dba_apply_error;

清理stream配置

这会删除整个数据库中的Streams配置，如果有两个 streams的用户，会把这两个用户的进程删清楚掉

exec dbms_streams_adm.remove_streams_configuration;

参考链接：

Replicating Data Using Oracle Streams
一步一步学Streams
Database Vault 的禁用
 修改SID和DB_NAME
各种 name 的关系
 重建 redolog
oracle 11g streams搭建
 oracle 11g streams各种类型搭建主要步骤
 spfile 和 pfile的区别，生成，加载和修复
 ORA-01034和ORA-27101错误
 ORA-26687

Oracle Stream Replcation 配置
环境：主数据库操作系统：linuxIP地址：172.168.68.173数据库：Oracle 11.2.0.3....
Oracle启动过程及常见错误
Liunx下oracle安装完成后，就开始配置oracle环境。 1、配置oracle环境变量 Liunx环境...
centos安装oracle11.2.0.4.0
1.配置本地yum源和epel源 2.oracle配置 oracle下载链接配置主机名配置JDK VNC配置 ...
Kafka常用资料
配置大全 Broker配置Topic配置生产者配置消费者配置Stream配置Connect配置AdminClien...
plsql配置连接远程oracle
在这里简单记录下plsql链接远程oracle的配置步骤下载并配置Oracle Instant Client 官...
启动和停止Oracle
配置Oracle listener 启动Oracle 2.1 启动server 2.2 启动listener 停止...
php连接Oracle数据库
要想用php连接Oracle，需要： 1.安装并配置Oracle instant Client2.配置php的ph...
2.nodejs通过stream方式加载页面 / 通过fs模块加
nodejs通过stream方式加载页面（stream方式加载优化性能）配置index.js文件，创建服务，设置...
Oracle知识点笔记
1.1 Oracle基本配置 1.1.1 Oracle安装与启动 Oracle安装：用户种类及初始密码在orac...
oracle数据库落地成本地文件
使用场景：把Oracle数据库落地为本地文件。 1、安装oracle client 2、修改oracle配置文件，...