stream复制日志

1. 修改主库初始化参数:

alter system set aq_tm_processes=4 scope=both;
alter system set job_queue_processes=5 scope=both;
alter system set global_names=true scope=both;
alter system set streams_pool_size=200m scope=both;
alter system set log_archive_format='arch%t_%s_%r.arc' scope=both;
alter system set log_archive_dest_1='Location=h:\alex\alex1\archive' scope=both;

2. 创建stream复制专用表空间strmtbs:

create tablespace strmtbs datafile 'h:\alex\alex1\strmtbs01.dbf' size 200M autoextend on maxsize unlimited segment space management auto;

3. 创建stream admin用户strmadmin:

create user strmadmin identified by strmadmin default tablespace strmtbs quota unlimited on strmtbs;

4. 给strmadmin用户授权:

grant connect,resource,dba to strmadmin;

5. 修改主库为归档模式:

shutdown immeidate;
startup mount;
alter database archivelog;
alter database open;

6. 用strmadmin用户连接到主库,创建db_link到从库:

create database link alex2 connect to strmadmin identified by strmadmin using 'alex2';                                

7. 修改从库初始化参数:

alter system set aq_tm_processes=4 scope=both;
alter system set job_queue_processes=5 scope=both;
alter system set global_names=true scope=both;
alter system set streams_pool_size=200m scope=both;
alter system set log_archive_format='arch%t_%s_%r.arc' scope=both;
alter system set log_archive_dest_1='Location=h:\alex\alex1\archive' scope=both;

8. 创建stream复制专用表空间strmtbs:

create tablespace strmtbs datafile 'h:\alex\alex2\strmtbs01.dbf' size 200M autoextend on maxsize unlimited segment space management auto;

9. 创建stream admin用户strmadmin:

create user strmadmin identified by strmadmin default tablespace strmtbs quota unlimited on strmtbs;

10. 给strmadmin用户授权:

grant connect,resource,dba to strmadmin;

11. 修改从库为归档模式:

shutdown immeidate;
startup mount;
alter database archivelog;
alter database open;

12. 用strmadmin用户连接到从库,创建db_link到主库:

create database link alex1 connect to strmadmin identified by strmadmin using 'alex1';

13. 关于dbms_streams_adm.pre_instantiation_setup的说明:

调用dbms_streams_adm.pre_instantiation_setup和dbms_streams_adm.post_instantiation_setup过程必须成对出现。
pre_instantiation_setup过程说明:
  maintain_mode        -> 'GLOBAL' - 全库复制
                          '' - 定义需要复制的表空间,eg.需要复制表空间test1和test2,则设为'TEST1,TEST2'
  perform_actions      -> 'TRUE' - 进行配置产生的源脚本将记录在dba_recoverable_*字典表,
  source_database      -> 主库上新建的db_link
  destination_database -> 从库上新建的db_link
  bi_directional       -> 'TRUE' - 表示是多源复制,即从库与主库双向同步,
                          'FALSE' - 只从源库向目标库同步数据;
  exclude_schemas      -> 需要排除在全库同步的表空间,多个表空间的话用逗号分开,
                          '*' - 表示当前已存在的所有表空间;
  start_processes      -> 配置完成后启动捕获及应用进程。

14. 以创建全库复制为例,调用dbms_streams_adm.pre_instantiation_setup:

declare
  empty_tbs dbms_streams_tablespace_adm.tablespace_set;
begin
  dbms_streams_adm.pre_instantiation_setup(
    maintain_mode        => 'GLOBAL',
    tablespace_names     => empty_tbs,
    source_database      => 'ALEX1',
    destination_database => 'ALEX2',
    perform_actions      => true,
    bi_directional       => true,
    include_ddl          => true,
    start_processes      => true,
    exclude_schemas      => NULL,
    exclude_flags        => dbms_streams_adm.exclude_flags_unsupported +
                            dbms_streams_adm.exclude_flags_dml +
                            dbms_streams_adm.exclude_flags_ddl);
end;
/


DECLARE
  empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
  DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
    maintain_mode        => 'GLOBAL',
    tablespace_names     => empty_tbs,
    source_database      => 'ning.test',
    destination_database => 'dest.test',
    perform_actions      => true,
    bi_directional       => true,
    include_ddl          => true,
    start_processes      => true,
    exclude_schemas      => NULL,
    exclude_flags        => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +
                            DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
                            DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
END;
/


出现错误:
ORA-23621: 正在执行与脚本 086FE19357484110943865F386A3F271 对应的操作。
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: 在 "SYS.DBMS_STREAMS_MT", line 2371
ORA-06512: 在 "SYS.DBMS_STREAMS_MT", line 7852
ORA-06512: 在 "SYS.DBMS_STREAMS_ADM", line 2779
ORA-06512: 在 line 4

15. ORA-23621错误的排查:
通过如下视图,查询错误信息:

SELECT * FROM DBA_RECOVERABLE_SCRIPT;
SELECT * FROM DBA_RECOVERABLE_SCRIPT_PARAMS;
SELECT * FROM DBA_RECOVERABLE_SCRIPT_BLOCKS;
SELECT * FROM DBA_RECOVERABLE_SCRIPT_ERRORS;

如果需要重新配置,可以先清除当前操作:

begin
  dbms_streams_adm.recover_operation(script_id      => '086FE19357484110943865F386A3F271',
                                     operation_mode => 'PURGE');
end;
/

修正db_link或者相关错误之后,可继续执行:

begin
  dbms_streams_adm.recover_operation(script_id      => '086FE19357484110943865F386A3F271',
                                     operation_mode => 'FORWARD');
end;
/

通过执行dbms_steams_adm的recover_operation过程,在更正错误后继续执行复制配置;

Trackback

no comment untill now

Add your comment now

切换到手机版