Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw） Tweet

Previously on Mac De Oracle

前回は、Data PumpのschemaモードでschemaごとまるっとMviewサイトを複製してみたところまで、でした。

エントリには記載していなかったログを見ていて驚いた！ことを調査してみる回として”その４と1/2”にしてみましたw

ほんと、癖モノですよねーMviewも！w

---

なに驚いたのか、その内容から

Data Pumpを利用しMviewを複製する今回の目的の一つである、完全リフレッシュしないでMviewを複製するという目論見が外れていた！！！？

と思われるログが残っていました。

以下のログは、Data Pumpのインポートログとその後のMview関連ビューをリストしたものですが、赤字部分に注目

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/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/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Apr 23 20:30:19 2017 elapsed 0 00:00:28

インポート直後、2つのMviewの状態を確認したログですが、B列（DBA_REFRESH.BROKEN列）はどちらも”N"となっておりリフレッシュジョブは停止中です。問題は下のLAST_REF列（DBA_MVIEWS.LAST_REFRESH_TYPE列）がCOMPLETEとなっているというところです。
CON_ID=5は、PDB:ORCL2なのでインポートを行ったPDBを指します。DBA_MVIEWS.LAST_REFRESH_TYPE列は、「最新のリフレッシュに使用されるメソッド:COMPLETE- 最新のリフレッシュが完了した。」と説明されています。？？
完全リフレッシュされちゃったの？？？　

こうなったらData PumpでMviewが作成された時に完全リフレッシュされちゃうのか、されないのか確認するしかありませんねw。

ROWNER               RNAME                  REFGROUP        JOB B INTERVAL             NEXT_DATE               CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1        MV_MASTER                    61         81 N sysdate+5/1440       2017/04/23 20:38:31          5
MVIEW_SCHEMA1        MV_MASTER                    61         81 N sysdate+5/1440       2017/04/23 20:43:30          3

MVIEW_NAME                     REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH  COMPILE_STATE           CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER                      DEMAND FAST     COMPLETE UNDEFINED           VALID                        5
MV_MASTER                      DEMAND FAST     FAST     UNDEFINED           VALID                        3

ということで、確認してみました。

最初は、前回と同じPDB:ORCLのスキーマ間で基本レプリケーション環境を作成し、リフレッシュジョブを停止するところまで（何をやっているかはDDLやSQL文を見ればわかると思うので略。わからないと言う方は前回の内容で確認ください。）

orcl12c@SYS> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORDS                           READ WRITE NO
         5 ORCL2                          READ WRITE NO

orcl@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.

orcl@SYS> create user master_schema identified by welcome1
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

orcl@SYS> grant create session, create table to master_schema;

Grant succeeded.

orcl@SYS> create user mview_schema1 identified by welcome1
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

orcl@SYS> grant create session, create table, create database link, create materialized view to mview_schema1;

Grant succeeded.

orcl@SYS> conn master_schema/welcome1@orcl
Connected.
orcl@MASTER_SCHEMA> create table master (
  2  id number primary key
  3  ,foo varchar2(100));

Table created.

orcl@MASTER_SCHEMA> insert into master values(1,'foo');

1 row created.

orcl@MASTER_SCHEMA> insert into master values(2,'bar');

1 row created.

orcl@MASTER_SCHEMA> commit;

Commit complete.

orcl@MASTER_SCHEMA> create materialized view log on master;

Materialized view log created.

orcl@MASTER_SCHEMA> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> create database link to_master_schema
  2  connect to master_schema identified by welcome1
  3  using 'ORCL';

Database link created.

orcl@MVIEW_SCHEMA1> select count(*) from master@to_master_schema;

  COUNT(*)
----------
         2

orcl@MVIEW_SCHEMA1> create materialized view mv_master
  2  refresh fast on demand
  3  start with sysdate next sysdate+5/1440
  4  as select * from master@to_master_schema;

Materialized view created.

orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

  COUNT(1)
----------
         2

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/04/23 20:12:06          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/04/23 20:07:06 2017/04/23 20:12:06 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

orcl12c@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>true);

PL/SQL procedure successfully completed.

orcl@MVIEW_SCHEMA1> @mview_info_c

Session altered.

ROWNER               RNAME                  REFGROUP        JOB B INTERVAL             NEXT_DATE               CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1        MV_MASTER                    61         81 Y sysdate+5/1440       4000/01/01 00:00:00          3

MVIEW_NAME                     REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH  COMPILE_STATE           CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER                      DEMAND FAST     FAST     UNDEFINED           VALID                        3

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT                                                         　　 CON_ID
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------ ----------
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/23 20:07:06 4000/01/01 00:00:00 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');                  3

ここまでは同じなのですが、Data PumpでMviewをインポートする際に完全リフレッシュしているのか？どうかを確認するため、リフレッシュジョブ停止後、マスター表を100行に増幅しておきます。
こうしておけば、Data PumpでMviewがインポートされる際、完全リフレッシュが行われていたとしたら、インポート後のMviewは100行になっているはずだし、
　逆に2行のままなら、LAST_REFRESH_TYPE=COMPLETEにはなっているものの実際には完全リフレッシュしなていない、という、なんともわかりづらい状況になるから気にすんな、ってことが見えてくるはず。

orcl12c@SYS> conn master_schema/welcome1@orcl
Connected.
orcl@MASTER_SCHEMA> begin for i in 3..100 loop insert into master values(i,'data#'||i); end loop; end;
  2  /

PL/SQL procedure successfully completed.

orcl@MASTER_SCHEMA> commit;

Commit complete.

orcl@MASTER_SCHEMA> select count(1) from master;

  COUNT(1)
----------
       100

orcl@MASTER_SCHEMA> conn mview_schema1/welcome1@orcl 
Connected.
orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

  COUNT(1)
----------
          2

エクスポートは2行となっているので、Mviewから2行エクスポートされているのは間違いない！(マスター表は100行！）

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 Sun Apr 23 20:28:25 2017 elapsed 0 00:00:54

PDB:ORCL2にて、Data Pump作業向けディレクトリオブジェクトを作成

[oracle@vbgeneric oracle]$ sqlplus sys/oracle@orcl2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 23 20:29:07 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@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.

インポートでも見ての通り、2行だけインポートされています！（ステータスが紛らわしい説が強くなってきました！！！）

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 Sun Apr 23 20:30:19 2017 elapsed 0 00:00:28

インポート後、どちらのリフレッシュジョブも停止中ですが、１箇所違いがみられます。
そう、インポートしたMviewのLAST_REF(DBA_MVIEWS.LAST_REFRESH_TYPE)がCOMPLETEになっています。前半に書いたとおりの状態が再現！！！　なにっ！！

orcl12c@SYS> @mview_info_c
Connected.

Session altered.

ROWNER               RNAME                  REFGROUP        JOB B INTERVAL             NEXT_DATE               CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1        MV_MASTER                    61         81 Y sysdate+5/1440       4000/01/01 00:00:00          3
MVIEW_SCHEMA1        MV_MASTER                    61         81 Y sysdate+5/1440       4000/01/01 00:00:00          5

MVIEW_NAME                     REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH  COMPILE_STATE           CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER                      DEMAND FAST     FAST     UNDEFINED           VALID                        3
MV_MASTER                      DEMAND FAST     COMPLETE UNDEFINED           VALID                        5

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT                                                             CON_ID
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------ ----------
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/23 20:07:06 4000/01/01 00:00:00 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');                  3
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1                            4000/01/01 00:00:00 sysdate+5/1440                  dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');                  5

PDB_NAME                           CON_ID
------------------------------ ----------
PDB$SEED                                2
ORCL                                    3
ORDS                                    4
ORCL2                                   5

DBA＿MVIEWSビューのLAST_REFRESH_TYPEはCOMPLETE(完全リフレッシュされた）と設定されていますが、本当なのでしょうか？ PDB:ORCL2のMV＿MASTER表が本当に完全リフレッシュされていたとしたら100行になっているはずですが。。。。

PDB:ORCLのMViewの行数をカウント、2行です（リフレッシュジョブが停止しているので変化なし）

orcl12c@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> show con_id

CON_ID
------------------------------
3

orcl@MVIEW_SCHEMA1> show con_name      

CON_NAME
------------------------------
ORCL

orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

  COUNT(1)
----------
         2

さて、問題のPDB:ORCL2。。。。2行のまま！！！！！　
ということは、Data PumpによるMviewのインポートでは完全リフレッシュは行われず、エクスポートされた行数がそのままインポートされている！！　ステータスが紛らわしい！！　だけというのが真相のようです！

orcl@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> show con_id

CON_ID
------------------------------
5

orcl2@MVIEW_SCHEMA1> show con_name

CON_NAME
------------------------------
ORCL2

orcl2@MVIEW_SCHEMA1> select count(1) from mv_master;

  COUNT(1)
----------
         2

ジョブを再開すると。。。マスターサイトと同期され各MViewは100行なっています。めでたしめでたし。

@> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

orcl@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

orcl12c@SYS> @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/04/23 20:44:05          5
MVIEW_SCHEMA1        MV_MASTER                    61         81 N sysdate+5/1440       2017/04/23 20:43:30          3

MVIEW_NAME                     REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH  COMPILE_STATE           CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER                      DEMAND FAST     FAST     UNDEFINED           VALID                        3
MV_MASTER                      DEMAND FAST     FAST     UNDEFINED           VALID                        5

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT                                                             CON_ID
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------ ----------
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/23 20:38:30 2017/04/23 20:43:30 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');                  3
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/23 20:39:05 2017/04/23 20:44:05 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');                  5

orcl12c@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

  COUNT(1)
----------
       100

orcl@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> select count(1) from mv_master;

  COUNT(1)
----------
       100

やれやれ、お騒がせすぎるw　

次回へつづく。

参考：ログ中で利用したSQLスクリプト(mview_info_c.sql)のソース

conn sys/oracle@orcl12c as sysdba

col rowner for a20
col rname for a20
col mview_name for a30
col log_user for a20
col schema_user for a20
col interval for a20
col what for a60
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select rowner,rname,refgroup,job,broken,interval,next_date,con_id from containers(dba_refresh) where rowner='MVIEW_SCHEMA1';
select mview_name,refresh_mode,refresh_method,last_refresh_type,after_fast_refresh,compile_state from containers(dba_mviews) where mview_name='MV_MASTER';
select job,log_user,schema_user,last_date,next_date,interval,failures,what from containers(dba_jobs) where log_user='MVIEW_SCHEMA1';

---

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へ複製