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://oracle.chinaitlab.com/backup/802898.html
http://dev.firnow.com/course/7_databases/oracle/oraclejs/2008515/116714_4.html
http://davis.bokee.com/254674.html
http://space.itpub.net/9338/viewspace-15390
http://www.sosdb.com/jdul/dispbbs.asp?boardID=1&ID=218
http://blog.chinaunix.net/u3/109532/showart_2212998.html
http://soaspx.com/dotnet/sql/oracle/oracle_20091122_1726.html
http://dev.firnow.com/course/7_databases/oracle/oraclejs/20100628/213454.html
http://unix-cd.com/vc/www/16/2009-02/13260.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/789833/viewspace-1038872/ ，如需转载，请注明出处，否则将追究法律责任。
