今天在学习物化视图时,遇到了ORA-12034问题,场景如下:
物化视图日志站点(11.2.0.1版本):
1、对于T1表创建了物化视图日志:
create materialized view log on t1;
2、又创建了一个本地的物化视图:
create materialized view tmv1
refresh fast
start with sysdate
next sysdate+1/1440
as
select * from t1;
远程站点(10.2.0.1版本):
1、通过数据库链路,也创建对于t1表的物化视图:
create materialized view tmv3
refresh fast
start with sysdate
next sysdate+1/1440
as
select * from t1@orcl.oracle.com;
结果远程站点在JOB刷新时报错了:
ERROR at line 1:
ORA-12034: materialized view log on “SCOTT”.”T1″ younger than last refresh
后来通过搜索网上的资料,临时解决了,但是感觉还不是最好的方法,目前没有找到更好的方法,暂时记录下来:
其实后来经过分析,可以简单的在sys.slog$表中插入一条记录,:
insert into sys.slog$ values(‘SCOTT’,’T1′,NULL,171,NULL,to_date(‘2014-01-07 15:44:18′,’yyyy-mm-dd hh24:mi:ss’),null,null);
commit;
插入的时间可以通过如下命令查看,查看哪个物化视图的SNAPTIME小,就插入哪个时间:
SELECT SOWNER, VNAME, MOWNER, MASTER, to_char(SNAPTIME,’yyyy-mm-dd hh24:mi:ss’) FROM SYS.SNAP_REFTIME$;
然后可以根据实际情况:exec dbms_mview.refresh(‘TMV1′,C’); exec dbms_mview.refresh(‘TMV1′,’F’);
就OK了。
----更新----
后来在同事的电脑上创建远程物化视图(11.2.0.1),发现会在sys.slog$中注册记录,并且刷新正常,在自己的11.2.0.3的RAC上测试,也正常,不同大版本之间,低版本向高版本创建物化视图还真是问题多,而高版本向低版本创建物化视图经过测试也没有问题。
原贴地址:http://blog.itpub.net/8242091/viewspace-609538
最近发现客户的数据库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的修改,也可以解决而不需要重建。但是客户的物化视图是直接刷的,没办法了
今天的文章今天在学习物化视图时,遇到了ORA-12034问题分享到此就结束了,感谢您的阅读。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/84722.html