ORA-12034 #2 Tweet
前回の通り、高速リフレッシュの間にdata pump export / importを完了させれば、ORA-12034なんて起きないはず。
VirtualBoxの環境は試験開始前のスナップショットで戻してあります
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 20:57:49 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
orcl@MVIEW_SCHEMA1> @mview_info_c
Connected.
¥Session altered.
ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/08/28 21:00:54 3
MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 20:55:54 2017/08/28 21:00:54 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
前回は5分間隔だったので、余裕をもたせて30分にしてあります。
本番環境で5分間隔の高速リフレッシュを30分にしたり、止めたりってことはかなり敷居が高いとは思いますが、そのあたりは空気を読んで対応する必要があるかと。 :)
orcl12c@SYS> conn mview_schema1@orcl
Connected.
orcl@MVIEW_SCHEMA1> alter materialized view mv_master refresh next sysdate+30/1440;
Materialized view altered.
orcl@MVIEW_SCHEMA1> @mview_info_c
Connected.
Session altered.
ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 21:30:36 3
MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 20:55:54 2017/08/28 21:30:36 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
準備は整ったので、高速リフレッシュが終わったタイミングでexportからimportまでをやっつけちゃいます！
まずはエクスポート
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=mview_schema1.dmp logfile=exp_mview_schema1.log schemas=mview_schema1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
. . exported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/mview_schema1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Aug 28 21:05:00 2017 elapsed 0 00:00:56
[oracle@vbgeneric ˜]$ sqlplus master_schema@orcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 21:05:19 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
orcl@MASTER_SCHEMA> select count(*) from mlog$_master;
COUNT(*)
----------
0
orcl@MASTER_SCHEMA> conn mview_schema1@orcl
Connected.
orcl@MVIEW_SCHEMA1> @mview_info_c
Connected.
Session altered.
ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 21:30:36 3
MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 20:55:54 2017/08/28 21:30:36 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
次にインポートしてMviewを複製！
orcl12c@SYS> conn system@orcl2
Connected.
orcl2@SYSTEM> create directory workdir as '/u01/userhome/oracle';
Directory created.
orcl2@SYSTEM> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@vbgeneric ˜]$ impdp system@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp
Import: Release 12.1.0.2.0 - Production on Mon Aug 28 21:07:13 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Aug 28 21:07:40 2017 elapsed 0 00:00:23
複製完了！！！！
結果はいかに...
[oracle@vbgeneric ˜]$ sqlplus mview_schema1@orcl2
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 21:08:09 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
orcl2@MVIEW_SCHEMA1> @mview_info_c
Connected.
Session altered.
ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 22:00:46 5
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 22:00:46 3
MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 21:30:46 2017/08/28 22:00:46 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 21:30:46 2017/08/28 22:00:46 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
予想通り成功しました！
エクスポート〜インポートによるMVIEW複製までを高速リフレッシュの間に行うことが可能ならば例のエラーは回避できることが確認できました。
が、
小さなMViewならまだしも、巨大なMView、巨大なMViewが複数あるリフレッシュグループだったりすると、それはもう大変な作業になることは想像できます。
大人の事情でMViewの高速リフレッシュを止められない、とか高速リフレッシュ間隔が非常短い場合には無理しないで、ほかの手を考えた方が良いと思います。
ただし、他の手を進めるにも時間には余裕を持った方がいいですよね。なんでギリギリなんだろうねと、よく思います。
夏休みの最後の1週間で宿題全部やりきるみたいなのは嫌ですよねw
Data Pumpも癖モノだよね〜w その１ - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その２ - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係
Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製
Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw）
Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製（オプジェクトパス de 絞り込み）
Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製（オプジェクトパスが不足すると...）
ORA-12034
| 固定リンク | 0
コメント