oracle LOGICAL standby ORA-04030: out of process memory
今天下午發現邏輯備庫有延遲,於是檢查了一下,發現一直在應用一個很大的事物,期間不時有page out,最後竟然出現了ORA-04030: out of process memory :
LOGSTDBY Apply process P005 pid=33 OS id=22584 stopped
Fri Jun 10 16:51:37 CST 2011
Errors in file /data/oracle/admin/bdump/orcl_lsp0_25503.trc:
ORA-12801: error signaled in parallel query server P005
ORA-04030: out of process memory when trying to allocate 4040 bytes (knas:shtrans,knas: rpc arguments)
ORA-04030: out of process memory when tryin
LOGSTDBY Analyzer process P003 pid=31 OS id=22580 stopped
LOGSTDBY Apply process P007 pid=37 OS id=22588 stopped
LOGSTDBY Apply process P008 pid=38 OS id=22590 stopped
LOGSTDBY Apply process P004 pid=32 OS id=22582 stopped
LOGSTDBY Apply process P006 pid=34 OS id=22586 stopped
Fri Jun 10 16:51:37 CST 2011
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGSTDBY status: ORA-16111: log mining and apply setting up
Fri Jun 10 16:51:37 CST 2011
Errors in file /data/oracle/admin/bdump/orcl_lsp0_25503.trc:
ORA-12801: error signaled in parallel query server P005
ORA-04030: out of process memory when trying to allocate 4040 bytes (knas:shtrans,knas: rpc arguments)
ORA-04030: out of process memory when tryin
LOGSTDBY Analyzer process P003 pid=31 OS id=22580 stopped
LOGSTDBY Apply process P007 pid=37 OS id=22588 stopped
LOGSTDBY Apply process P008 pid=38 OS id=22590 stopped
LOGSTDBY Apply process P004 pid=32 OS id=22582 stopped
LOGSTDBY Apply process P006 pid=34 OS id=22586 stopped
Fri Jun 10 16:51:37 CST 2011
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGSTDBY status: ORA-16111: log mining and apply setting up
於是想知道到底是什麼事物導致的,從v$logstdby_process,v$session_longopt 都知道一直在進行一個事物,但是查不到是什麼object上的。突然靈機一動,在邏輯備庫上做了一個awr,發現一個語句執行了大概750萬次update操作,終於知道了是什麼表。另外,在primary也做了一個awr,發現是一個update語句更新了大概750萬記錄,估計是應用的動態sql沒有控制好條件。
原本以為這個操作沒有完成,但是後來檢查了一下資料,發現資料全部被改了,還好這個欄位不怎麼變化的,要是這個是金錢怎麼辦????設計、開發、測試??how can this happen?? 不過已發生,我們還得擦屁股。
1 還好我們的undo比較大,保留時間比較長,如果發現不及時,估計會被顧客給投訴死。恢復也可能就只能logminer了:
create table xxxx_0610 as select * from xxxx as of TIMESTAMP sysdate-0.1;
2 迴圈更新(要是這個是金錢怎麼辦?):
DECLARE
COUNt_v NUMBER:=0;
BEGIN
FOR c IN (SELECT * FROM xxxx_0610 a WHERE a.ined=1)
LOOP
UPDATE xxxx
SET xxxx.exptime=c.exptime
WHERE xxxx.id=c.id;
COUNt_v:=COUNt_v+1;
IF(MOD(count_v,5000)=0)
THEN
COMMIT;
dbms_lock.sleep(2);
END IF;
END LOOP;
COMMIT;
END;
COUNt_v NUMBER:=0;
BEGIN
FOR c IN (SELECT * FROM xxxx_0610 a WHERE a.ined=1)
LOOP
UPDATE xxxx
SET xxxx.exptime=c.exptime
WHERE xxxx.id=c.id;
COUNt_v:=COUNt_v+1;
IF(MOD(count_v,5000)=0)
THEN
COMMIT;
dbms_lock.sleep(2);
END IF;
END LOOP;
COMMIT;
END;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/758322/viewspace-697637/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE ORA-04030之 out of process memory when trying to allocateOracle
- ORA-04030: out of process memory ...(initSubHeap:qk...)的錯誤解決
- AIX平臺下報ORA-04030: out of process memory when trying to allocate string bytesAI
- [江楓]In Memory Undo與logical standby databaseDatabase
- A significant part of sql server process memory has been paged outNifiSQLServer
- Out of memory: Kill process 2249 (nginx) score 1 or sacrifice childNginx
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- 建立Oracle 11g logical standbyOracle
- Oracle10g logical standby 建立Oracle
- Create Logical Standby For Oracle 10GOracle 10g
- OOM(Out Of Memory)OOM
- java out of memoryJava
- Oracle10gR2 Logical Standby (zt)Oracle
- PK重複導致Logical Standby Apply process stop - ORA-00001APP
- Oracle Logical Standby 維護常用命令Oracle
- oracle 10g logical standby db creationOracle 10g
- 建立 Logical Standby DatabaseDatabase
- manage logical standby databaseDatabase
- DataGuard:Logical Standby Switchover
- OOM--OUT OF MEMORYOOM
- 配置 Oracle 10g RAC primary + RAC logical standbyOracle 10g
- Oracle 9i R2 配置 Logical StandbyOracle
- DataGuard:Logical Standby FailoverAI
- 監控Logical standby databaseDatabase
- Oracle 9i Logical Standby與Physical standby歸檔恢復區別Oracle
- oracle10g R2 logical standby switchover to primaryOracle
- oracle LOGICAL standby 日誌無法應用處理Oracle
- Oracle Data Gurad -- Logical Standby 相關說明Oracle
- oracle啟動遭遇ORA-27102: out of memoryOracle
- An out of memory error has occurred.Error
- oracle實驗記錄 (oracle 10G dataguard(11)建立logical standby)Oracle
- Oracle10g Logical Standby的開啟與關閉Oracle
- oracle9204(9i)_logical standby_ddl_relatedOracle
- 物理Standby資料庫及邏輯Standby資料庫(Physical Standby & Logical Standby)資料庫
- Logical Standby Database的配置步驟.Database
- Logical Standby的維護操作_SKIP
- Logical Standby中Job的處理
- [zt] Oracle如何配置邏輯備用資料庫(Logical Standby)Oracle資料庫