PK重複導致Logical Standby Apply process stop - ORA-00001

tolywang發表於2010-08-30
Oracle 10.2.0.4 ,  Linux AS5.3 , 64Bit  

Production + Logical Standby  ,  其中Logical Standby 用於報表查詢 。  


報錯資訊如下 :   

Mon Aug  2 09:39:04 2010
RFS LogMiner: Registered logfile [/u01/product/oradata/delll10/arch/1_10903_717422003.arc] to LogMiner session id [1]
Mon Aug  2 09:39:05 2010
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 10903, /u01/product/oradata/delll10/arch/1_10903_717422003.arc
Mon Aug  2 09:39:05 2010
LOGMINER: End mining logfile: /u01/product/oradata/delll10/arch/1_10903_717422003.arc
Mon Aug  2 09:39:08 2010
LOGMINER: Log Auto Delete - deleting: /u01/product/oradata/delll10/arch/1_10902_717422003.arc
Deleted file /u01/product/oradata/delll10/arch/1_10902_717422003.arc
Mon Aug  2 09:49:04 2010
RFS[1]: Successfully opened standby log 4: '/data/delll10/redo01s.log'
Mon Aug  2 09:49:04 2010
RFS LogMiner: Client enabled and ready for notification
Mon Aug  2 09:49:04 2010
RFS LogMiner: Registered logfile [/u01/product/oradata/delll10/arch/1_10904_717422003.arc] to LogMiner session id [1]
Mon Aug  2 09:49:05 2010
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 10904, /u01/product/oradata/delll10/arch/1_10904_717422003.arc
Mon Aug  2 09:49:05 2010
LOGMINER: End mining logfile: /u01/product/oradata/delll10/arch/1_10904_717422003.arc
Mon Aug  2 09:49:06 2010
LOGSTDBY status: ORA-00001: unique constraint (DFMS.WIP_D_SHIP_BIN_DETAIL_PK) violated
LOGSTDBY Apply process P007 pid=35 OS id=10791 stopped
Mon Aug  2 09:49:07 2010
Errors in file /u01/product/admin/delll10/bdump/delll10_lsp0_10803.trc:
ORA-12801: error signaled in parallel query server P007
ORA-00001: unique constraint (DFMS.WIP_D_SHIP_BIN_DETAIL_PK) violated
LOGSTDBY Analyzer process P003 pid=32 OS id=10783 stopped
LOGSTDBY Apply process P005 pid=34 OS id=10787 stopped
LOGSTDBY Apply process P008 pid=36 OS id=10793 stopped
LOGSTDBY Apply process P004 pid=33 OS id=10785 stopped
LOGSTDBY Apply process P006 pid=31 OS id=10789 stopped
Mon Aug  2 09:49:07 2010
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGSTDBY status: ORA-16111: log mining and apply setting up
Mon Aug  2 09:49:07 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=28 OS id=10775
LOGMINER: session# = 1, preparer process P002 started with pid=30 OS id=10779
LOGMINER: session# = 1, builder process P001 started with pid=29 OS id=10777
Mon Aug  2 09:49:07 2010
LOGSTDBY ERROR: krvxgov failed for SYS AUD$ with status 1403
Mon Aug  2 09:49:07 2010
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 10904, /u01/product/oradata/delll10/arch/1_10904_717422003.arc
Mon Aug  2 09:49:07 2010
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Analyzer process P003 started with pid=32 OS id=10783
Mon Aug  2 09:49:07 2010
LOGMINER: Log Auto Delete - deleting: /u01/product/oradata/delll10/arch/1_10903_717422003.arc
Deleted file /u01/product/oradata/delll10/arch/1_10903_717422003.arc
Mon Aug  2 09:49:07 2010
LOGMINER: End mining logfile: /u01/product/oradata/delll10/arch/1_10904_717422003.arc



出現3次這樣的現象, 沒有辦法,只有忽略這個表先, 然後將這個表初始化 。

SQL> alter database stop logical standby apply;
SQL> exec dbms_logstdby.skip('DML','DFMS','WIP_D_SHIP_BIN_DETAIL');
SQL> alter database start logical standby apply;

SQL> alter database stop logical standby apply;


SQL> exec dbms_logstdby.unskip('DML','DFMS','WIP_D_SHIP_BIN_DETAIL');


SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('DFMS','WIP_D_SHIP_BIN_DETAIL','db_linkto_orcl');
SQL> alter database start logical standby apply;


我們在Logical Standby上這個表中建立了一個trigger , 如果有人工的更新這個table ,那麼會觸發寫log 表, 發現登入這個
logical standby的賬號根本沒有許可權更改這個表, 只有檢視許可權 。 為什麼還會發生PK重複值的情況呢  ?   難道是SQL Apply
使用了並行導致的 ?   但是Oracle也不會同時在一個物件上進行並行apply吧 ?  它應該控制的很好才對 。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-672093/,如需轉載,請註明出處,否則將追究法律責任。

相關文章