ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 物化视图日志的重建 与 ORA-12034

物化视图日志的重建 与 ORA-12034

原创 Linux操作系统 作者:westzq1984 时间:2009-07-17 22:03:17 0 删除 编辑

最近发现客户的数据库db file scattered read很高,做了个statspacktop5里面的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

 

23条指出的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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle10g 静默安装
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    960813