PK重複導致Logical Standby Apply process stop - ORA-00001
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吧 ? 它應該控制的很好才對 。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sqlldr 匯入重複資料導致PK INDEX失效SQLIndex
- 同事寫的監控Logical Standby SQL apply 程式stop的監控報警指令碼SQLAPP指令碼
- oracle LOGICAL standby ORA-04030: out of process memoryOracle
- 建立 Logical Standby DatabaseDatabase
- manage logical standby databaseDatabase
- DataGuard:Logical Standby Switchover
- AIX下nfs故障導致oracle process hangAINFSOracle
- DataGuard:Logical Standby FailoverAI
- 監控Logical standby databaseDatabase
- 【ASK_ORACLE】因process用盡導致的rac重啟的解決方法Oracle
- sqlplus註釋導致語句重複執行SQL
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- 物理Standby資料庫及邏輯Standby資料庫(Physical Standby & Logical Standby)資料庫
- Handling ORA-1403 ora-12801 on logical standby apply [ID 1178284.1]APP
- 建立Oracle 11g logical standbyOracle
- Logical Standby Database的配置步驟.Database
- Oracle10g logical standby 建立Oracle
- Create Logical Standby For Oracle 10GOracle 10g
- Logical Standby的維護操作_SKIP
- Logical Standby中Job的處理
- MAC address(實體地址)重複導致的網路故障Mac
- 重用或複用會導致耦合,微服務是寧可重複也不耦合 - Victor Rentea微服務
- Oracle10gR2 Logical Standby (zt)Oracle
- 建立Data guard logical standby database須知Database
- [江楓]In Memory Undo與logical standby databaseDatabase
- [zt] Logical standby維護命令手冊
- ORA-00001: UNIQUE CONSTRAINT (SYS.WRI$_SQLSET_PLANS_PK) VIOLATEDAISQL
- Oracle Logical Standby 維護常用命令Oracle
- Logical Standby常見問題解決方式
- oracle 10g logical standby db creationOracle 10g
- RAC環境LOGICAL STANDBY的SWITCHOVER切換
- Production DB expdp 導致standby歸檔恢復時混亂
- Oracle 9i Logical Standby與Physical standby歸檔恢復區別Oracle
- 【MySQL】Last_IO_Errno: 1593 server-uuid重複導致slave報錯MySqlASTServerUI
- logical standby DG同步錯誤問題總結
- 配置 Oracle 10g RAC primary + RAC logical standbyOracle 10g
- Step by Step Guide on How to Create Logical Standby [ID 738643.1]GUIIDE
- Oracle 9i R2 配置 Logical StandbyOracle