ORA-12034: materialized view log on xx.xx younger than last refresh when a mview fast refresh after Impdp

张维照 2019-05-31
19
0 0
摘要:快速刷新前进行一次完全刷新(注意:为了提高速度,最好先删除mview的索引再刷,后手动建索引)

问题描述

继续前面写的用数据泵迁移数据库的案例,迁移后发现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”.


「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部