物化檢視日誌的重建 與 ORA-12034
最近發現客戶的資料庫db file scattered read很高，做了個statspack，top5裡面的SQL全是和物化檢視日誌相關的SQL語句，一查mlog$_xxxx這些表的大小，竟然後幾個高達3G的……
由於相關有問題的表數量比較多，而且很多大表，最主要的是，查詢機上面還建立有物化檢視日誌，後面還掛著N個資料庫在刷查詢機的資料，如果把物化檢視日誌重建了，必須對後續的站點全部重刷，工作量太大。而且最大的表有40G，而UNDO表空間只有15G，重刷連UNDO表空間都不夠……
還好客戶可以考慮停機，決定通過改後設資料的方法來處理這個重新重新整理的問題。於是決定看下物化檢視報ORA-12034的規則，以前沒metalink，做的時候是把mlog$在重建前給拷貝出來，重建物化檢視日誌完成後，用備份的mlog$.oldest_pk（基於PK）/oldest（基於rowid）去更新mlog$的對應列。
參考的文件時：Doc ID: 204127.1
refresh fast的物化檢視能否正常重新整理，依賴於如下幾個條件：
1. A snapshot log exists.
2. SNAP_REFTIME$.SNAPTIME >= MLOG$.OLDEST_PK
3. SNAP_REFTIME$.SNAPTIME = SLOG$.SNAPTIME
4. Current refresh timestamp >= MLOG$.YOUNGEST + 1second
第2，3條指出的SNAP_REFTIME$，是在物化檢視站點上的該檢視，記錄的是物化檢視的重新整理時間，該時間是以mview log站點上的時間為基礎
|
mview log site |
mview site |
|
SQL> host date Fri Jul 17 20:52:30 CST 2009 |
SQL> host date Sat Aug 1 01:25:03 CST 2009 |
|
|
SQL> BEGIN 2 dbms_mview.refresh('MV_TEST','F'); 3 END; 4 /
PL/SQL procedure successfully completed. |
|
SQL> DROP MATERIALIZED VIEW LOG ON ctais2.t_test; CREATE MATERIALIZED VIEW LOG ON ctais2.t_test;
Materialized view log dropped.
SQL> Materialized view log created. |
|
|
SQL> SELECT mowner,MASTER,oldest_pk FROM sys.mlog$;
MOWNER MASTER OLDEST_PK ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 20:32:34
SQL> SELECT mowner,MASTER,snaptime FROM sys.slog$;
no rows selected |
|
|
|
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- ------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 20:31:54 |
|
|
SQL> BEGIN 2 dbms_mview.refresh('MV_TEST','F'); 3 END; 4 / BEGIN * ERROR at line 1: ORA-12034: materialized view log on "CTAIS2"."T_TEST" younger than last refresh ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858 ORA-06512: at line 2
SNAP_REFTIME$.SNAPTIME < MLOG$.OLDEST_PK |
|
SQL> UPDATE sys.mlog$ SET oldest_pk=to_date('2009-07-17 20:31:54','yyyy-mm-dd hh24:mi:ss') WHERE mowner='CTAIS2' AND MASTER='T_TEST';
1 row updated.
SQL> commit;
Commit complete. |
|
|
|
SQL> /
PL/SQL procedure successfully completed. |
|
SQL> SELECT mowner,MASTER,oldest_pk FROM sys.mlog$;
MOWNER MASTER OLDEST_PK ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 20:56:30
SQL> SELECT mowner,MASTER,snaptime FROM sys.slog$;
MOWNER MASTER SNAPTIME ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 20:56:30 |
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- ------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 20:56:30 |
可以看到，根據SNAP_REFTIME$.SNAPTIME >= MLOG$.OLDEST_PK 可以判讀是否能refresh fast
重建物化檢視日誌後，沒有SLOG$的記錄，該條件被忽略，重新整理了一次後，該記錄被增加
只需要將mlog$.oldest_pk更新為snap_reftime$.snaptime
接下來測試一個表上註冊了多個物化檢視的情況，在物化檢視日誌站點的資料庫上，也建立了個MV_TEST的物化檢視
|
mview log site |
mview site |
|
SQL> SELECT mowner,MASTER,oldest_pk FROM sys.mlog$;
MOWNER MASTER OLDEST_PK ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 21:01:51
SQL> SELECT mowner,MASTER,snapid,snaptime FROM sys.slog$;
MOWNER MASTER SNAPID SNAPTIME ------- ------- ---------- ------------------- CTAIS2 T_TEST 24 2009-07-17 21:01:58 CTAIS2 T_TEST 23 2009-07-17 21:01:51
SQL> SELECT sowner,snapname,snapsite,snapshot_id FROM sys.reg_snap$;
SOWNER SNAPNAME SNAPSITE SNAPSHOT_ID ------- -------- ---------- ----------- CTAIS2 MV_TEST SOURCE9 23 --本地站點 CTAIS2 MV_TEST RAC9I 24 --遠端站點 |
|
|
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- -------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:01:51
SNAP_REFTIME$.SNAPTIME = MLOG$.OLDEST_PK SNAP_REFTIME$.SNAPTIME = SLOG$.SNAPTIME |
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- ------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:01:58
SNAP_REFTIME$.SNAPTIME > MLOG$.OLDEST_PK SNAP_REFTIME$.SNAPTIME = SLOG$.SNAPTIME |
|
SQL> DROP MATERIALIZED VIEW LOG ON ctais2.t_test;
Materialized view log dropped.
SQL> CREATE MATERIALIZED VIEW LOG ON ctais2.t_test;
Materialized view log created. |
|
|
SQL> exec dbms_mview.refresh('MV_TEST','F'); BEGIN dbms_mview.refresh('MV_TEST','F'); END;
* ERROR at line 1: ORA-12034: materialized view log on "CTAIS2"."T_TEST" younger than last refresh ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858 ORA-06512: at line 1 |
SQL> exec dbms_mview.refresh('MV_TEST','F'); BEGIN dbms_mview.refresh('MV_TEST','F'); END;
* ERROR at line 1: ORA-12034: materialized view log on "CTAIS2"."T_TEST" younger than last refresh ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858 ORA-06512: at line 1 |
|
SQL> SELECT mowner,MASTER,oldest_pk FROM sys.mlog$;
MOWNER MASTER OLDEST_PK ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 21:09:11
SQL> SELECT mowner,MASTER,snapid,snaptime FROM sys.slog$;
no rows selected |
|
|
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- -------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:01:51
SNAP_REFTIME$.SNAPTIME < MLOG$.OLDEST_PK |
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- ------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:01:58
SNAP_REFTIME$.SNAPTIME < MLOG$.OLDEST_PK |
|
SQL> UPDATE sys.mlog$ SET oldest_pk=to_date('2009-07-17 21:01:51','yyyy-mm-dd hh24:mi:ss') WHERE mowner='CTAIS2' AND MASTER='T_TEST' 2 ;
1 row updated.
SQL> commit;
Commit complete. |
|
|
SQL> exec dbms_mview.refresh('MV_TEST','F');
PL/SQL procedure successfully completed. |
SQL> exec dbms_mview.refresh('MV_TEST','F'); BEGIN dbms_mview.refresh('MV_TEST','F'); END;
* ERROR at line 1: ORA-12034: materialized view log on "CTAIS2"."T_TEST" younger than last refresh ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858 ORA-06512: at line 1 |
|
SQL> SELECT mowner,MASTER,oldest_pk FROM sys.mlog$;
MOWNER MASTER OLDEST_PK ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 21:12:58
SQL> SELECT mowner,MASTER,snapid,snaptime FROM sys.slog$;
MOWNER MASTER SNAPID SNAPTIME ------- ------- ---------- ------------------- CTAIS2 T_TEST 23 2009-07-17 21:12:58 |
|
|
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- -------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:12:58
SNAP_REFTIME$.SNAPTIME = MLOG$.OLDEST_PK SNAP_REFTIME$.SNAPTIME = SLOG$.SNAPTIME 可以看到，重新整理成功後，MLOG$.OLDEST_PK被更新為sys.slog$.snaptime的最小值，遠端站點無記錄，所以就沒有考慮。 |
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- ------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:01:58
SNAP_REFTIME$.SNAPTIME < MLOG$.OLDEST_PK
|
|
SQL> UPDATE sys.mlog$ SET oldest_pk=to_date('2009-07-17 21:01:58','yyyy-mm-dd hh24:mi:ss') WHERE mowner='CTAIS2' AND MASTER='T_TEST' 2 ;
1 row updated.
SQL> commit;
Commit complete. |
|
|
|
SQL> exec dbms_mview.refresh('MV_TEST','F');
PL/SQL procedure successfully completed. |
|
SQL> exec dbms_mview.refresh('MV_TEST','F');
PL/SQL procedure successfully completed. |
必須要先重新整理另外一個站點，否則mlog$的記錄還是會被更新為另一個註冊記錄 |
可以看到，如果表上註冊了多個物化檢視，那麼要更新為一個站點的snaptime一次，刷一次，在更新為另一個站點的snaptime一次，在刷一次，因為在slog$中沒有這個註冊站點的記錄。
|
mview log site |
mview site |
|
SQL> SELECT mowner,MASTER,oldest_pk FROM sys.mlog$;
MOWNER MASTER OLDEST_PK ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 21:36:45
SQL> SELECT mowner,MASTER,snapid,snaptime FROM sys.slog$;
MOWNER MASTER SNAPID SNAPTIME ------- ------- ---------- ------------------- CTAIS2 T_TEST 23 2009-07-17 21:36:56 CTAIS2 T_TEST 24 2009-07-17 21:36:45
SQL> SELECT sowner,snapname,snapsite,snapshot_id FROM sys.reg_snap$;
SOWNER SNAPNAME SNAPSITE SNAPSHOT_ID ------- -------- ---------- ----------- CTAIS2 MV_TEST SOURCE9 23 CTAIS2 MV_TEST RAC9I 24 |
|
|
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- -------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:36:56 |
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- ------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:36:45 |
|
SQL> DROP MATERIALIZED VIEW LOG ON ctais2.t_test;
Materialized view log dropped.
SQL> CREATE MATERIALIZED VIEW LOG ON ctais2.t_test;
Materialized view log created. |
|
|
SQL> SELECT mowner,MASTER,oldest_pk FROM sys.mlog$;
MOWNER MASTER OLDEST_PK ------- ------- ------------------- CTAIS2 T_TEST 2009-07-17 21:39:29 |
|
|
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- -------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:36:56 |
SQL> SELECT SOWNER, VNAME, MOWNER, MASTER, SNAPTIME FROM SYS.SNAP_REFTIME$;
SOWNER VNAME MOWNER MASTER SNAPTIME ------- ------- ------- ------- ------------------- CTAIS2 MV_TEST CTAIS2 T_TEST 2009-07-17 21:36:45 |
|
--更新為各個站點中的snap_reftime$.snaptime最小值，其實只需要是以前mlog$中該列的值 UPDATE sys.mlog$ SET oldest_pk=to_date('2009-07-17 21:36:45','yyyy-mm-dd hh24:mi:ss') WHERE mowner='CTAIS2' AND MASTER='T_TEST'
--slog$插入資料，主要snap_id.其實只需要是原來的slog$的資料 INSERT INTO SYS.Slog$ VALUES ('CTAIS2','T_TEST',NULL,23,NULL,to_date('2009-07-17 21:36:56','yyyy-mm-dd hh24:mi:ss'),NULL,NULL); INSERT INTO SYS.Slog$ VALUES ('CTAIS2','T_TEST',NULL,24,NULL,to_date('2009-07-17 21:36:45','yyyy-mm-dd hh24:mi:ss'),NULL,NULL); |
|
|
SQL> exec dbms_mview.refresh('MV_TEST','F');
PL/SQL procedure successfully completed. |
SQL> exec dbms_mview.refresh('MV_TEST','F');
PL/SQL procedure successfully completed. |
這樣看來，手工維護物化檢視日誌，通過修改後設資料，避免重新重新整理，0RA-12034的辦法為：
1. 停止業務(應用伺服器，JOB,CRONTAB),關閉監聽，從v$session中確定沒有其他連線，保持資料一致性
2. 重新整理所有相關物化檢視（dba_registered_mviews可以確認，同時確認下有沒現在已經沒有重新整理的註冊，有就unregister掉）
3. 備份mlog$,slog$
4. 刪除mview log並重建
5. 更新後設資料
UPDATE MLOG$ A SET A.OLDEST_PK = (SELECT B.OLDEST_PK FROM MLOG$_BAK B WHERE B.MOWNER = A.MOWNER AND B.MASTER = A.MASTER);
UPDATE MLOG$ A SET A.OLDEST = (SELECT B.OLDEST FROM MLOG$_BAK B WHERE B.MOWNER = A.MOWNER AND B.MASTER = A.MASTER);
INSERT INTO SLOG$ SELECT * FROM SLOG$_BAK WHERE MOWNER, MASTER,SNAPID NOT IN (SELECT MOWNER, MASTER, SNAPID FROM SLOG$);
另外提一下，如果把物化檢視建立成on prebuilt table的形式，可以通過刪除物化檢視定義在重建，就可以很簡單的維護這個問題。同樣，對於DDL的修改，也可以解決而不需要重建。但是客戶的物化檢視是直接刷的，沒辦法了
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-609538/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 物化檢視日誌結構與ORA-12034 Mview log younger than last refreshViewAST
- 物化檢視的快速重新整理測試與物化檢視日誌
- 【物化檢視】幾種物化檢視日誌分析
- 物化檢視日誌的維護
- 物化檢視日誌與增量重新整理
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- oracle物化檢視日誌系列(一)Oracle
- oracle物化檢視日誌系列(二)Oracle
- oracle物化檢視日誌系列(三)Oracle
- 物化檢視日誌表被DROP後建立物化檢視報錯
- 物化檢視匯出匯入可能導致物化檢視日誌的失效
- 物化檢視日誌對UPDATE的影響
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle
- 解決物化檢視ORA-12034'實體化檢視日誌比上次重新整理後的內容新'的問題
- 物化檢視重新整理並非完全根據物化檢視日誌記錄
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（三）
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（二）
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（一）
- 多個物化檢視導致物化日誌無法及時更新
- 物化檢視日誌無法正常清除的解決方法
- 用exp、imp遷移包含物化檢視日誌的資料
- SYS使用者的表無法建立物化檢視日誌
- 物化檢視comlete重新整理會產生大量的日誌
- 物化檢視日誌(materialized view log)引起大量Dfs Lock Handle等待ZedView
- Oracle11gr2物化檢視日誌新增PURGE語句Oracle
- 檢視日誌
- Oracle11gr2物化檢視日誌新增COMMIT SCN語句OracleMIT
- 物化檢視妙用__表同步使用物化檢視方法
- alertmanager: 檢視日誌
- 【Oracle日誌】- 日誌檔案重建Oracle
- 檢視docker映象的日誌Docker
- 檢視系統的日誌
- MYSQL啟用日誌和檢視日誌MySql
- hp-ux與linux日誌檢視Linux
- oracle物化檢視Oracle
- Linux 檢視日誌Linux
- Hyperf日誌檢視元件元件
- 錯誤日誌檢視