物化视图日志的重建 与 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-609538/