Oracle 的Data Guard環(huán)境中, 邏輯備庫(kù)應(yīng)用進(jìn)程停止，日志顯示錯(cuò)誤為ORA-01403 not data found 。

它具體信息如下：

Mon May 21 10:39:31 2012

LOGSTDBY status: ORA-01403: no data found

LOGSTDBY Apply process P004 pid=143 OS id=13936 stopped

Mon May 21 10:39:32 2012

Errors in file /u01/app/Oracle/admin/db/bdump/rdb_lsp0_13922.trc:

ORA-12801: error signaled in parallel query server P004

ORA-01403: no data found

LOGSTDBY Analyzer process P003 pid=38 OS id=13932 stopped

這種錯(cuò)誤在logical standby 環(huán)境中很常見(jiàn)。

通過(guò)dba_logstdby_events 視圖查詢失敗的事務(wù)信息。

查詢SQL 如下所示：

select event_time,

       xidusn,

       xidslt,

       xidsqn,

       status,

       status_code,

       event,

       'exec dbms_logstdby.skip_transaction(' || xidusn || ',' || xidslt || ',' ||

       xidsqn || ');' as exec_sql,

       a.*

  from dba_logstdby_events a

 where event_time = (select max(event_time) from dba_logstdby_events);

從event 列中我們查詢出失敗的具體信息，是更新一張表失敗。

解決方法

這類(lèi)問(wèn)題的解決方法有兩種：

第一種，可以使用skip_transaction 方法忽略掉該事務(wù)。

 exec dbms_logstdby.skip_transaction(487,12,951001);

但這個(gè)方法有后遺癥，接下來(lái)的同步過(guò)程中會(huì)有不斷的麻煩，數(shù)據(jù)沒(méi)找到的問(wèn)題會(huì)層出不窮。

第二種，使用skip 方法忽略掉該表的所有的操作。

alter database stop logical standby apply;

execute dbms_logstdby.skip('DML','username','T_ZHUSHOU_ORDER');

execute dbms_logstdby.skip('SCHEMA_DDL','username','T_ZHUSHOU_ORDER');

alter database start logical standby apply immediate;

忽略掉出問(wèn)題的表的DML 操作和SCHEMA_DDL 操作。

數(shù)據(jù)復(fù)制停止的問(wèn)題是解決了，但這里造成了一張表事實(shí)上不能同步了。

新問(wèn)題：如何將已skip 的表再加入logical standby 中？

解決這個(gè)新問(wèn)題主要是靠DBMS_LOGSTDBY.INSTANTIATE_TABLE 方法。

官方對(duì)它的使用說(shuō)明：

This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink) as an input parameter. If the table already exists in the logical standby database, it will be dropped and re-created based on the table definition at the primary database. This procedure only brings over the data associated with the table, and not the associated indexes and constraints.

操作步驟如下：

1 、在邏輯備庫(kù)創(chuàng)建指向主庫(kù)的數(shù)據(jù)庫(kù)鏈路

2 、在邏輯備庫(kù)上unskip 掉該表。

unskip 之前需停止邏輯備庫(kù)應(yīng)用日志

alter database stop logical standby apply;

execute dbms_logstdby.unskip('DML','username','T_ZHUSHOU_ORDER');

execute dbms_logstdby.unskip('SCHEMA_DDL','username','T_ZHUSHOU_ORDER');

3 、使用INSTANTIATE_TABLE 重新實(shí)例化一張表。

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('username', 'T_ZHUSHOU_ORDER', 'DB_RAC');

注意，源庫(kù)上的用戶必須有  SELECT_CATALOG_ROLE 權(quán)限，否則會(huì)報(bào)錯(cuò)ORA-16276

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('username', 'T_ZHUSHOU_ORDER', 'DB_RAC');

begin DBMS_LOGSTDBY.INSTANTIATE_TABLE('username', 'T_ZHUSHOU_ORDER', 'DB_RAC'); end;

ORA-16276: specified database link does not correspond to primary database

ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 5361

ORA-02019: connection description for remote database not found

ORA-06512: at "SYS.DBMS_LOGSTDBY", line 636

ORA-06512: at line 2