OGG 故障處理一例

huzhichengforce發表於2014-07-10
今天檢視想要關閉一個OGG 程式報出：
There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT GGEX, FORCESTOP command.
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 104, RBA 44632080.
檢視ggserr.log
2014-07-10 19:36:20  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 20:06:20  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 20:07:11  INFO    OGG-01738  Oracle GoldenGate Capture for Oracle, ggex.prm:  BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p52103_extr: start=SeqNo: 104, RBA: 49890320, SCN: 0.2925683 (2925683), Timestamp: 2014-07-06 22:05:18.000000, Thread: 1, end=SeqNo: 104, RBA: 50181632, SCN: 0.2925683 (2925683), Timestamp: 2014-07-06 22:05:18.000000, Thread: 1.
2014-07-10 20:36:21  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 21:06:21  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 21:36:22  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 22:06:22  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 22:36:22  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 23:06:23  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
2014-07-10 23:36:24  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, ggex.prm:  Long Running Transaction: XID 9.3.1688, Items 0, Extract GGEX, Redo Thread 1, SCN 0.2925613 (2925613), Redo Seq #104, Redo RBA 44632080.
-----------------------------------------------------------------------------------------------------------------------
怎麼會有長事物呢?  我自己的實驗平臺一直沒有業務跑，怎麼還有長事物呢？
於是想到是不是 資料庫flash_recover 區滿了導致 資料庫的事物提交不了，造成資料庫錯誤 。
在源端檢視select * from v$flash_recovery_area_usage;
SQL> select * from v$flash_recovery_area_usage;


FILE_TYPE     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE      0 0 0
REDO LOG      0 0 0
ARCHIVED LOG     99.8 0 97
BACKUP PIECE      0 0 0
IMAGE COPY      0 0 0
FLASHBACK LOG      0 0 0
FOREIGN ARCHIVED LOG      0 0 0
果然是，進入RMAN 刪除
delete archivelog all completed before 'sysdate-2';

然後去關閉 stop er *
報錯消失。

-------------------------------------------------------------
如果你是生產系統 你最好 查處是那些事物在跑，用下面的語句查出來。
SELECT s.sid,s.serial#,s.status,s.username,t.start_time,s.WAIT_TIME, s.osuser, s.sql_id, s.program
FROM gv$session s,gv$transaction t
WHERE s.INST_ID=t.INST_ID and s.saddr=t.ses_addr
order by  t.start_time desc;

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

相關文章