« 《深入解析Oracle》一书的销售情况 | Blog首页 | 新年有好运 - 2009中国邮政有奖明信片中奖 »

Logical Standby ORA-01425错误处理一则

今天客户的Logical Standby数据库出现如下错误，这个错误出现在使用skip跳过某个表的DML事务之后。
类似如下操作：

alter database stop logical standby apply;
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'EYGLE',object_name => 'SALES', proc_name => null);
alter database start logical standby apply;

此时数据库的日志如下：

Sat Feb 7 22:46:00 2009
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 6, Transaction Chunk Size = 201
LOGMINER: Memory Size = 1000M, Checkpoint interval = 5000M
LOGMINER: session# = 1, reader process P000 started with pid=24 OS id=626908
LOGMINER: session# = 1, builder process P001 started with pid=26 OS id=569542
LOGMINER: session# = 1, preparer process P002 started with pid=27 OS id=635368
LOGMINER: session# = 1, preparer process P003 started with pid=28 OS id=663834
LOGMINER: session# = 1, preparer process P004 started with pid=29 OS id=590192
LOGMINER: session# = 1, preparer process P005 started with pid=30 OS id=434476
LSP2 started with pid=32, OS id=557268
Sat Feb 7 22:46:01 2009
krvxerpt: Errors detected in process 19, role LOGICAL STANDBY COORDINATOR.
Sat Feb 7 22:46:01 2009
krvsqn2s: unhandled failure 604
Sat Feb 7 22:46:01 2009
Errors in file /u01/admin/cdr/bdump/cdr_lsp0_688564.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01425: escape character must be character string of length 1
LOGSTDBY status: ORA-00604: error occurred at recursive SQL level 1
ORA-01425: escape character must be character string of length 1
Sat Feb 7 22:46:01 2009
Errors in file /u01/admin/cdr/bdump/cdr_lsp0_688564.trc:

根据Metalink Note:748208.1 ,这是一个Bug：5108158，在 11g 中被修正。
在目前版本中可以通过如下步骤解决：

***Stop logical apply before proceeding ***

set echo on
set pagesize 100
spool workaround.log
select * from system.logstdby$skip;
select distinct nvl(esc, 'NULL') from system.logstdby$skip;
select * from system.logstdby$skip where esc is null;
update system.logstdby$skip
set esc = '\'
where esc is NULL;

-- Following should return no rows (due to update above)
select * from system.logstdby$skip where esc is null;
-- should no longer see any NULL in output
select distinct nvl(esc, 'NULL') from system.logstdby$skip;
-- Capture a snapshot of the final results
select * from system.logstdby$skip;

-- commit changes
commit;

After this is done, please attempt to restart apply and let us if error occurs again.

记录备忘，供参考。

By eygle on 2009-02-08 00:58 | Comments (1) | Advanced | Case | 2182 |

CaseDataGuardoerror