ORA-01403: no data found For ORACLE 10G

Mon Sep 20 13:20:28 2010
Errors in file /ora104/admin/FAB2STB/bdump/fab2stb_lsp0_999530.trc:
ORA-12801: error signaled in parallel query server P008
ORA-01403: no data found
LOGSTDBY Analyzer process P003 pid=15 OS id=622756 stopped
LOGSTDBY Apply process P005 pid=26 OS id=626840 stopped
LOGSTDBY Apply process P006 pid=27 OS id=290858 stopped
LOGSTDBY Apply process P007 pid=28 OS id=741466 stopped
LOGSTDBY Apply process P004 pid=25 OS id=823728 stopped
Mon Sep 20 13:20:29 2010
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGSTDBY status: ORA-16111: log mining and apply setting up
Mon Sep 20 13:20:31 2010
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: session# = 1, reader process P000 started with pid=22 OS id=299308
LOGMINER: session# = 1, builder process P001 started with pid=23 OS id=1073330
LOGMINER: session# = 1, preparer process P002 started with pid=24 OS id=139520
Mon Sep 20 13:20:31 2010
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 8257, /ora104/oradata/FAB2STB/redostb02.log
Mon Sep 20 13:20:31 2010
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Analyzer process P003 started with pid=15 OS id=622756
LOGSTDBY Apply process P008 started with pid=29 OS id=295208
LOGSTDBY Apply process P006 started with pid=27 OS id=290858
LOGSTDBY Apply process P005 started with pid=26 OS id=626840
LOGSTDBY Apply process P004 started with pid=25 OS id=823728
LOGSTDBY Apply process P007 started with pid=28 OS id=741466
Mon Sep 20 13:20:32 2010
LOGSTDBY status: ORA-01403: no data found
LOGSTDBY Apply process P004 pid=25 OS id=823728 stopped
Mon Sep 20 13:20:34 2010
Errors in file /ora104/admin/FAB2STB/bdump/fab2stb_lsp0_999530.trc:
ORA-12801: error signaled in parallel query server P004
ORA-01403: no data found
LOGSTDBY Analyzer process P003 pid=15 OS id=622756 stopped
LOGSTDBY Apply process P007 pid=28 OS id=741466 stopped
LOGSTDBY Apply process P006 pid=27 OS id=290858 stopped
LOGSTDBY Apply process P005 pid=26 OS id=626840 stopped
LOGSTDBY Apply process P008 pid=29 OS id=295208 stopped
Mon Sep 20 13:28:51 2010


SQL> select event,status from dba_logstdby_events;
EVENT STATUS

(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16128: User initiated stop apply successfully completed
(CLOB) ORA-16226: DDL skipped due to lack of support
(CLOB) ORA-01403: no data found

(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16128: User initiated stop apply successfully completed
(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16222: automatic Logical Standby retry of last action
(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16111: log mining and apply setting up
(CLOB) ORA-01403: no data found

(Clob) ORA-16222: automatic Logical Standby retry of last action
(Clob) ORA-16111: log mining and apply setting up
(CLOB) ORA-01403: no data found

(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16128: User initiated stop apply successfully completed
(Clob) ORA-16111: log mining and apply setting up
(CLOB) ORA-01403: no data found

(Clob) ORA-16222: automatic Logical Standby retry of last action
(Clob) ORA-16111: log mining and apply setting up
(CLOB) ORA-01403: no data found

(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16128: User initiated stop apply successfully completed
(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16128: User initiated stop apply successfully completed
(Clob) ORA-16111: log mining and apply setting up
(Clob) ORA-16128: User initiated stop apply successfully completed
(Clob) ORA-16111: log mining and apply setting up
(CLOB) ORA-16205: DDL skipped due to skip setting
(Clob) ORA-16128: User initiated stop apply successfully completed
(CLOB) ORA-16226: DDL skipped due to lack of support
(CLOB) ORA-01403: no data found[@more@]

Solutions:

On Primary

1.Restore archivelog From Tape Library

RMAN> RUN{
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
set archivelog destination to '/ora102/archive/temp';
restore archivelog from logseq 8257 until logseq 8257;
RELEASE CHANNEL ch00;
}

2.Find startscn and endscn

SQL> select to_char(first_change#), to_char(next_change#) from v$archived_log where thread#=1 and sequence#=8257;

TO_CHAR(FIRST_CHANGE#)
----------------------------------------
TO_CHAR(NEXT_CHANGE#)
----------------------------------------
5974282330424
5974282469634

3.Logmnr archivelog

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(startscn=>5974282330424,endscn=>5974282469634,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.COMMITTED_DATA_ONLY +DBMS_LOGMNR.PRINT_PRETTY_SQL +DBMS_LOGMNR.CONTINUOUS_MINE);

4.檢視可疑的sql

select sql_redo from v$logmnr_contents where xidusn=110 and xidslt=5 and xidsqn = 7560;

SQL_REDO
--------------------------------------------------------------------------------
"COMPONENT_QUANTITY" = 1 and
"COMPONENT_OPERATION_SEQ_NUM" = 40 and
"CREATE_TIME" = TO_DATE('20-SEP-10', 'DD-MON-RR') and
ROWID = 'AAAYBfABzAABv/lABN';

delete from "SMPERP"."SMP_BOM_EXPLOSION_TEMP"
where
"REQUEST_ID" = 1 and
"BOM_LEVEL" = 0 and
"TOP_ASSEMBLY_ITEM_ID" = 3373366 and
"ASSEMBLY_ITEM_ID" = 3373366 and

SQL_REDO
--------------------------------------------------------------------------------
"COMPONENT_ITEM_ID" = 3377384 and
"ORGANIZATION_ID" = 110 and
"ASSEMBLY_WIP_SUPPLY_TYPE" = 1 and
"COMPONENT_WIP_SUPPLY_TYPE" = 1 and
"COMPONENT_QUANTITY" = 1 and
"COMPONENT_OPERATION_SEQ_NUM" = 40 and
"CREATE_TIME" = TO_DATE('20-SEP-10', 'DD-MON-RR') and
ROWID = 'AAAYBfABzAABv/lABO';

5.以上存在問題的TABLE在備端不會用到，跳過這個table的ddl&dml操作。

exec dbms_logstdby.skip('SCHEMA_DDL','SMPERP','SMP_BOM_EXPLOSION_TEMP',NULL);
exec dbms_logstdby.skip('DML','SMPERP','SMP_BOM_EXPLOSION_TEMP',NULL);

6.Restart Logical standby apply

SQL> alter database start logical standby apply immediate;

參考網址：

http://dev.firnow.com/course/7_databases/oracle/oraclejs/2008515/116714_4.html

http://space.itpub.net/9338/viewspace-15390

http://blog.chinaunix.net/u3/109532/showart_2212998.html

http://dev.firnow.com/course/7_databases/oracle/oraclejs/20100628/213454.html