首页 » ORACLE 9i-23ai » ORA-12034: materialized view log on xx.xx younger than last refresh when a mview fast refresh after Impdp
ORA-12034: materialized view log on xx.xx younger than last refresh when a mview fast refresh after Impdp
继续前面写的用数据泵迁移数据库的案例，迁移后发现alert 日志中出下了下面的错误信息：
Errors in file /u01/app/oracle/admin/topbox/bdump/topbox_j000_4067.trc:
ORA-12012: error on auto execute of job 88
ORA-12034: materialized view log on “TOPBOX”.”TOPBOX_COURSESTUDY” younger than last refresh
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2256
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2462
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2431
ORA-06512: at “TOPBOX.TOPBOX_JOB”, line 14
ORA-06512: at line 1
[oracle@db231 ~]$ oerr ora 12034
12034, 0000, “materialized view log on \”%s\”.\”%s\” younger than last refresh”
// *Cause: The materialized view log was younger than the last refresh.
// *Action: A complete refresh is required before the next fast refresh.
//
refresh fast的物化视图快速刷新依赖于如下几个条件：
1. A snapshot log exists.
2. if mview base on PK
SNAP_REFTIME$.SNAPTIME >= MLOG$.OLDEST_PK
if mview base on SEQ
SNAP_REFTIME$.SNAPTIME >= MLOG$.OLDEST_SEQ
3. SNAP_REFTIME$.SNAPTIME = SLOG$.SNAPTIME
4. Current refresh timestamp >= MLOG$.YOUNGEST + 1second
Note: A complete refresh can be done using the command:
execute dbms_mview.refresh(‘”CORP”.”NM_SV_RANGE”‘,’C’);
可以建一个数据库job在晚上去刷新，也可以手动放到OS后台去处理。下面是手动放到后台处理
[oracle@dbserver42 ~]$ cat jobs.sh
echo 'begin job running..'
date '+%Y-%m-%d %H:%M:%S'
sqlplus / as sysdba << EOF
exec DBMS_MVIEW.REFRESH('TOPBOX.TOPBOX_COURSESTUDY_M_VIEW','C');
EOF
echo 'end job.'
date '+%Y-%m-%d %H:%M:%S'
echo '========================='
[oracle@dbserver42 ~]$ nohup ./jobs.sh >> nohup_job.log 2>&1 &
[oracle@dbserver42 ~]$ jobs -l
[1]+ 27535 Running nohup ./jobs.sh >> nohup_job.log 2>&1 &
下面模拟一下这个错误，以两个解决方法
1，快速刷新前进行一次完全刷新（注意：为了提高速度，最好先删除mview的索引再刷，后手动建索引）
2，重建MVIEW或建一TABLE用prebuilt table指定手动建的表
以上方法都是要用parallel技术来提高速度。
weejar@OEM12C>create table test as select rownum id,mod(rownum,5) userid, sysdate-rownum sdate from dual connect by rownum<1000;
Table created.
weejar@OEM12C>create materialized view log on test with rowid,sequence(userid) including new values;
Materialized view log created.
weejar@OEM12C>create materialized view test_mv REFRESH fast ON DEMAND as select userid ,count(*) cnt from test group by userid;
Materialized view created.
weejar@OEM12C>select * from test_mv;
USERID CNT
-------------------- --------------------
1 200
2 200
4 200
3 200
0 199
weejar@OEM12C>insert into test select level id,mod(rownum,5) userid, sysdate-rownum sdate from dual connect by rownum<1000
2 ;
999 rows created.
weejar@OEM12C>select * from test_mv;
USERID CNT
-------------------- --------------------
1 200
2 200
4 200
3 200
0 199
weejar@OEM12C>commit;
Commit complete.
weejar@OEM12C>exec DBMS_MVIEW.REFRESH('TEST_MV');
PL/SQL procedure successfully completed.
weejar@OEM12C>select * from test_mv;
USERID CNT
-------------------- --------------------
1 400
2 400
4 400
3 400
0 398
sys@OEM12C>host mkdir /home/oracle/datapump
sys@OEM12C>create directory datapump as '/home/oracle/datapump';
Directory created.
[oracle@oem ~]$ expdp system/oracle schemas=weejar directory='DATAPUMP' dumpfile=weejar.dump
Export: Release 10.2.0.5.0 - 64bit Production on Sunday, 28 April, 2013 17:23:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=weejar directory=DATAPUMP dumpfile=weejar.dump
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
. . exported "WEEJAR"."TEST" 41.98 KB 1998 rows
. . exported "WEEJAR"."MV_CAPABILITIES_TABLE" 8.609 KB 14 rows
. . exported "WEEJAR"."TEST_MV" 5.578 KB 5 rows
. . exported "WEEJAR"."MLOG$_TEST" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/datapump/weejar.dump
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:23:47
[oracle@oem ~]$ impdp system/oracle remap_schema=weejar:weejar1 directory='DATAPUMP' dumpfile=weejar.dump
Import: Release 10.2.0.5.0 - 64bit Production on Sunday, 28 April, 2013 17:27:19
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** remap_schema=weejar:weejar1 directory=DATAPUMP dumpfile=weejar.dump
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "WEEJAR1"."TEST" 41.98 KB 1998 rows
. . imported "WEEJAR1"."MV_CAPABILITIES_TABLE" 8.609 KB 14 rows
. . imported "WEEJAR1"."TEST_MV" 5.578 KB 5 rows
. . imported "WEEJAR1"."MLOG$_TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:27:21
sys@OEM12C>alter user weejar1 identified by weejar1;
User altered.
sys@OEM12C>grant connect,resource to weejar1;
Grant succeeded.
weejar1@OEM12C>exec DBMS_MVIEW.REFRESH('TEST_MV');
BEGIN DBMS_MVIEW.REFRESH('TEST_MV'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "WEEJAR1"."TEST" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1
weejar1@OEM12C>exec DBMS_MVIEW.REFRESH('TEST_MV','C');
PL/SQL procedure successfully completed.
weejar1@OEM12C>exec DBMS_MVIEW.REFRESH('TEST_MV');
PL/SQL procedure successfully completed.
第二种
[oracle@oem ~]$ impdp system/oracle remap_schema=weejar:weejar2 directory='DATAPUMP' dumpfile=weejar.dump
Import: Release 10.2.0.5.0 - 64bit Production on Sunday, 28 April, 2013 17:34:38
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** remap_schema=weejar:weejar2 directory=DATAPUMP dumpfile=weejar.dump
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "WEEJAR2"."TEST" 41.98 KB 1998 rows
. . imported "WEEJAR2"."MV_CAPABILITIES_TABLE" 8.609 KB 14 rows
. . imported "WEEJAR2"."TEST_MV" 5.578 KB 5 rows
. . imported "WEEJAR2"."MLOG$_TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:34:40
sys@OEM12C>alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
sys@OEM12C>select sowner,vname,snaptime from SNAP_REFTIME$;
SOWNER VNAME SNAPTIME
------------------------------ ------------------------------ -------------------
SYSMAN MGMT$ARU_PATCH_RECOM_MD 2012-11-06 12:34:39
WEEJAR TEST_MV 2013-04-28 17:20:38
WEEJAR1 TEST_MV 2013-04-28 17:28:54
WEEJAR2 TEST_MV 2013-04-28 17:20:38
sys@OEM12C>select mowner,log,oldest,oldest_pk,oldest_seq,youngest from MLOG$;
MOWNER LOG OLDEST OLDEST_PK OLDEST_SEQ YOUNGEST
------------------------------ ------------------------------ ------------------- ------------------- ------------------- -------------------
ANBOB MLOG$_TESTMV 2013-03-19 17:24:55 4000-01-01 00:00:00 4000-01-01 00:00:00 2013-03-19 17:24:55
WEEJAR MLOG$_TEST 2013-04-28 17:20:38 4000-01-01 00:00:00 2013-04-28 17:20:38 2013-04-28 17:20:38
WEEJAR1 MLOG$_TEST 2013-04-28 17:28:54 4000-01-01 00:00:00 2013-04-28 17:28:54 2013-04-28 17:28:54
WEEJAR2 MLOG$_TEST 2013-04-28 17:34:40 4000-01-01 00:00:00 2013-04-28 17:34:40 2013-04-28 17:20:38
sys@OEM12C>select mowner,master,snaptime from slog$;
MOWNER MASTER SNAPTIME
------------------------------ ------------------------------ -------------------
WEEJAR TEST 2013-04-28 17:20:38
WEEJAR1 TEST 2013-04-28 17:28:54
WEEJAR2 TEST 2013-04-28 17:20:38
sys@OEM12C>SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2013-04-28 17:42:20
sys@OEM12C>insert into weejar1.test select level id,mod(rownum,5) userid, sysdate-rownum sdate from dual connect by rownum<1000;
999 rows created.
sys@OEM12C>insert into weejar2.test select level id,mod(rownum,5) userid, sysdate-rownum sdate from dual connect by rownum<1000;
999 rows created.
sys@OEM12C>conn weejar2/weejar2
Connected.
weejar2@OEM12C>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- --------------------
MV_CAPABILITIES_TABLE TABLE
TEST TABLE
MLOG$_TEST TABLE
TEST_MV TABLE
weejar2@OEM12C>exec DBMS_MVIEW.REFRESH('TEST_MV');
BEGIN DBMS_MVIEW.REFRESH('TEST_MV'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "WEEJAR2"."TEST" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1
weejar2@OEM12C>drop materialized view test_mv;
Materialized view dropped.
weejar2@OEM12C>select * from MLOG$_TEST;
no rows selected
weejar2@OEM12C>create table test_mv as select userid ,count(*) cnt from test group by userid;
Table created.
weejar2@OEM12C>create materialized view test_mv on prebuilt table
2 REFRESH fast ON DEMAND as select userid ,count(*) cnt from test group by userid;
Materialized view created.
weejar2@OEM12C>select * from test_mv;
USERID CNT
-------------------- --------------------
1 600
2 600
4 600
3 600
0 597
weejar2@OEM12C>exec DBMS_MVIEW.REFRESH('TEST_MV');
PL/SQL procedure successfully completed.
sys@OEM12C>insert into weejar2.test select level id,mod(rownum,5) userid, sysdate-rownum sdate from dual connect by rownum<1000;
999 rows created.
weejar2@OEM12C>select * from MLOG$_TEST;
--had 999 enties log data of above insert.
weejar2@OEM12C>exec DBMS_MVIEW.REFRESH('TEST_MV');
PL/SQL procedure successfully completed.
Notice:
enq: JI – contention wait event
Sessions waiting on this event are waiting on locks held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view.
Solutions
A materialized view cannot be fast refreshed more than once in a given period because it is serialized during the commit phase. Ensure that only one session at a time is performing the refreshes. If there is more than one session, the first session will work normally but the subsequent sessions will wait on “enq: JI – contention”.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。