江枫渔火愁对面

Oracle10gR2中，Streams配置做了很大的简化，基本上可以做到只要执行一个过程就能完成配置。实际上，这些过程都是分两步来进行工作的，第一步先生成配置脚本，第二步再执行脚本。这样在配置的过程中，如果出现一些错误，很难直接定位到具体的错误。Oracle提供了一些数据字典视图，可以进一步查看错误的详细信息。

DBA_RECOVERABLE_SCRIPT：配置脚本概要信息
DBA_RECOVERABLE_SCRIPT_PARAMS：配置脚本参数
DBA_RECOVERABLE_SCRIPT_ERRORS：执行过程中的错误信息
DBA_RECOVERABLE_SCRIPT_BLOCKS：配置脚本的详细步骤

一、配置过程中错误处理

假如我们配置本地捕获的表复制，源库执行过程中出现了一个错误：

STRMADM@ning>DECLARE
2 tables DBMS_UTILITY.UNCL_ARRAY;
3 BEGIN
4 tables(1) := ‘ning.test’;
5 DBMS_STREAMS_ADM.MAINTAIN_TABLES(
6 table_names => tables,
7 source_directory_object => NULL,
8 destination_directory_object => NULL,
9 source_database => ‘ning.test’,
10 destination_database => ‘dest.test’,
11 perform_actions => true,
12 bi_directional => false,
13 include_ddl => true,
14 instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
15 END;
16 /
DECLARE
*
ERROR at line 1:
ORA-23616: Failure in executing block 7 for script
D74179203F11445D8F3F3F77C0749A1D
ORA-06512: at “SYS.DBMS_RECOVERABLE_SCRIPT”, line 457
ORA-06512: at “SYS.DBMS_STREAMS_MT”, line 7631
ORA-06512: at “SYS.DBMS_STREAMS_ADM”, line 2476
ORA-06512: at line 5

根据报错信息，在执行脚本D74179203F11445D8F3F3F77C0749A1D的第7步(block 7)的时候出现错误

STRMADM@ning>select invoking_package_owner as owner,
2 invoking_package as package,
3 invoking_procedure as procedure,
4 status,
5 total_blocks,
6 done_block_num
7 from dba_recoverable_script
8 where script_id=’D74179203F11445D8F3F3F77C0749A1D’;

OWNER PACKAGE PROCEDURE STATUS TOTAL_BLOCKS DONE_BLOCK_NUM
———- ——————– ——————– ———— ———— ————–
SYS DBMS_STREAMS_ADM MAINTAIN_TABLES ERROR 12 6

说明目前我们执行的是dbms_streams_adm.maintain_tables配置过程，该配置过程一共有12步，已经成功完成6个步骤，在第7步遇到了麻烦。

来看看第7步具体是什么：

STRMADM@ning>select forward_block,forward_block_dblink
2 from dba_recoverable_script_blocks
3 where script_id=’D74179203F11445D8F3F3F77C0749A1D’
4 and block_num=7;

FORWARD_BLOCK FORWARD_BLOCK_DBLINK
————————————————– ——————–
– DEST.TEST
– Datapump TABLE MODE IMPORT (NETWORK)
–
DECLARE
h1 NUMB

原来是利用impdp网络导入特性执行表的初始化时出错。再来看看错误信息：

STRMADM@ning>select error_number,error_message
2 from dba_recoverable_script_errors
3 where script_id=’D74179203F11445D8F3F3F77C0749A1D’
4 and block_num=7;

ERROR_NUMBER ERROR_MESSAGE
———— —————————————————————————————————-
-6550 ORA-06550: line 1, column 9:
PLS-00352: Unable to access another database ‘DEST.TEST’
ORA-06550: line 1, column 9:
PLS-00201: identifier ‘DBMS_STREAMS_RPC@DEST.TEST’ must be declared
ORA-06550: line 1

发现原来是没有创建到目标库的database link，这个错误好解决，创建好数据库链接

STRMADM@ning>create database link dest.test connect to strmadm identified by strmadm using ‘dest’;

Database link created.

再重新从错误的步骤恢复执行配置过程：

begin
dbms_streams_adm.recover_operation(
script_id => ‘D74179203F11445D8F3F3F77C0749A1D’,
operation_mode => ‘FORWARD’);
end;
/

如果错误比较严重，无法再继续进行配置，那么可以将已经完成的步骤回滚掉

begin
dbms_streams_adm.recover_operation(
script_id => ‘D74179203F11445D8F3F3F77C0749A1D’,
operation_mode => ‘ROLLBACK’);
end;
/

或者强制清除配置脚本

begin
dbms_streams_adm.recover_operation(
script_id => ‘D74179203F11445D8F3F3F77C0749A1D’,
operation_mode => ‘PURGE’);
end;
/

二、清除Streams配置

有个时候，我们需要清除已经配置好的Streams环境，Oracle10gR2也提供了专门的清除过程，可以比较简单的去掉Streams复制。

首先停止并删除capture进程

STRMADM@ning>select capture_name,status from dba_capture;

CAPTURE_NAME STATUS
—————————— ——–
NING$CAP ENABLED

STRMADM@ning>exec dbms_capture_adm.stop_capture(‘NING$CAP’);

PL/SQL procedure successfully completed.

STRMADM@ning>exec dbms_capture_adm.drop_capture(‘NING$CAP’);

PL/SQL procedure successfully completed.

停止并删除propagation进程

STRMADM@ning>select propagation_name,status from dba_propagation;

PROPAGATION_NAME STATUS
—————————— ——–
PROPAGATION$_29 ENABLED

STRMADM@ning>exec dbms_propagation_adm.stop_propagation(‘PROPAGATION$_29′);

PL/SQL procedure successfully completed.

STRMADM@ning>exec dbms_propagation_adm.drop_propagation(‘PROPAGATION$_29′);

PL/SQL procedure successfully completed.

停止并删除apply进程

STRMADM@dest>select apply_name,status from dba_apply;

APPLY_NAME STATUS
—————————— ——–
APPLY$_NING_39 ENABLED

STRMADM@dest>exec dbms_apply_adm.stop_apply(‘APPLY$_NING_39′);

PL/SQL procedure successfully completed.

STRMADM@dest>exec dbms_apply_adm.drop_apply(‘APPLY$_NING_39′);

PL/SQL procedure successfully completed.

清除整个streams配置

STRMADM@ning>exec dbms_streams_adm.remove_streams_configuration;

PL/SQL procedure successfully completed.