Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製（オプジェクトパス de 絞り込み） Tweet

Previously on Mac De Oracle

前回はschemaモードのエクスポートで、schemaごとまるっと別PDBへインポートしてMviewサイトを複製してみました。意外と簡単なんですよね。Mviewにハマりさえしなければ。
今回は前回同様の構成で、schemaモードでMviewを他のPDBへ複製するのですが、スキーマごとまるっとではなく、エクスポートするオプジェクトパスを必要最小限に絞ってMviewと関連するオブジェクトのみ複製できることを確認しておきます。

前回と同じポンチ絵ですが、
今回は、基本レプリケーション環境は構築済みの状態からスタートします。（つまり、Data Pumpでエクスポートするところから始めます）
細かい操作を確認したい方は、前々回や前回のログを参照ください。

---

Mviewの複製に必要な最小限のオブジェクトだけData Pumpで複製するために必要なオブジェクトパスはどれなのか確認しておきます。
前々回のエントリでほぼ見えてますが実際にやってみないと”不安”なので (^^;;;。

data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSにはオブジェクトパスの定義でMviewに関連するオブジェクト全てが定義されていることは何度か書きました。
また、前々回のexpdp/impdpの実行ログでどのようなオブジェクトがエクスポートされ、インポートされていたかも赤字で示しておきました。

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 Sat Apr 15 00:39:00 2017 elapsed 0 00:00:23

インポートされたオブジェクト、ジョブ、そして、リフレッシュグループの情報から、schemaモードでMVIEWの複製に必要な必要最小限のオブジェクトパスは以下と推測しました。
なお、CONSTRAINTはTABLEに紐づくので自動的にエクスポートされるのであえて選んでいません。
（SCHEMA_EXPORT_OBJECTSビューより）

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK           Private database links in the selected schemas  
SCHEMA_EXPORT/TABLE             Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB               Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP     Refresh groups in the selected schemas

上記オブジェクトパスをincludeパラメータに指定し、schemaモードで必要最小限のオブジェクトのみエクスポート可能か確認します。これがうまくできれば、成功することはほぼ確実！
PDB:ORCLにて、Data Pumpのschemaモードで必要最小限のオブジェクトのみexportするため以下のパラメータファイルを利用。
対象Mviewと関連オブジェクト以外はエクスポートしないようにするため対象Mviewのみに限定するようにしてあります。なお、パラメータファイルを利用している理由は、コマンドラインだとなんだかんだと面倒くさいData Pumpの癖を回避するためですw

[oracle@vbgeneric ˜]$ cat exp_mviewonly.par 
schemas=mview_schema1
include=DB_LINK
include=TABLE:"IN ('MV_MASTER')"
include=MATERIALIZED_VIEW:"IN ('MV_MASTER')"
include=JOB
include=REFRESH_GROUP

エクスポート！
ログを見る限り、想定通りのオブジェクトがエクスポートされています。オブジェクトにはユーザや権限を含めていないので、インポートする前にユーザ作成と必要最小限の権限を付与する必要があります。ログを見る限り狙い通りになっている模様です。

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=exp_mviewonly.dmp logfile=exp_mviewonly.log parfile=exp_mviewonly.par 
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/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/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/exp_mviewonly.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 15 23:56:33 2017 elapsed 0 00:00:35

では、複製の開始！

ユーザはロールや権限など含めてエクスポートしていないのでData Pump作業用ディレクトリオブジェクトの作成に加え、ユーザ作成、必要最小限の権限付与をインポート前に行っています。

PDB:ORCL2にて

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

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 15 23:57:17 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

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

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

Directory created.

PDB:ORCL2にて、Mviewと関連オブジェクトをインポートするユーザを作成と必要最小限の権限付与
このユーザ名はコピー元と同一ユーザ名にしてあります。異なる場合にはremap_schemaパラメータが必要になり一手間増えるので同一ユーザ名で作成してあります。

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

User created.

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

Grant succeeded.

PDB:ORCL2にて、インポート。エクスポート時に必要オブジェクトのみにしてあるので単純にインポートだけ！
想定しているオブジェクトがインポートされているように見えますね！！！！

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=exp_mviewonly.dmp logfile=imp_mviewonly.log 
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/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 15 23:58:25 2017 elapsed 0 00:00:04

PDB:ORCL2のMview向けユーザにて、インポート後のオブジェクトを確認
必要なオブジェクトは正しくインポートされており、停止状態でエクスポートされたリフレッシュジョブも停止状態であることが確認できます。

できてた！
この時点で、マスターサイトに対して、ORCLとORCL2という2つのMViewサイトができあがり!

[oracle@vbgeneric ˜]$ sqlplus mview_schema1/welcome1@orcl2

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 15 23:58:46 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> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
MV_MASTER                      MATERIALIZED VIEW
SYS_C0014888                   INDEX
MV_MASTER                      TABLE
TO_MASTER_SCHEMA               DATABASE LINK

orcl2@MVIEW_SCHEMA1> @mview_info

Session altered.

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

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1                            4000/01/01 00:00:00 sysdate+5/1440                  dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

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

最後に、高速リフレッシュが停止中である2つのMviewの高速リフレッシュの再開を確認しておきます。
schemaモードの単純な複製でも問題はなかったのでここでもジョブを再開するだけで、高速リフレッシュが再開されるはず。。。です。

事前に作成したContaners句を利用したスクリプト（mview_info_c.sql(前回のエントリの最後にあります））で確認します。
2つあるMviewサイトの高速リフレッシュジョブは停止中であることが確認できます。

CDB$RORRTのSYSユーザにて
LAST_REF列がCOMPELETEになっていますが、前回のエントリのとおりの紛らわしいステータスに設定されているだけで実際には完全リフレッシュされていません。勘違いのないように！

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          5
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
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER                      DEMAND FAST     COMPLETE 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                            4000/01/01 00:00:00 sysdate+5/1440                  dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/15 23:53:39 4000/01/01 00:00:00 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=>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.

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+5/1440       2017/04/16 00:08:24          3
MVIEW_SCHEMA1        MV_MASTER                    61         81 N sysdate+5/1440       2017/04/16 00:07:19          5

MVIEW_NAME                     REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH  COMPILE_STATE       BUILD_MOD
------------------------------ ------ -------- -------- ------------------- ------------------- ---------
MV_MASTER                      DEMAND FAST     FAST     UNDEFINED           VALID               IMMEDIATE
MV_MASTER                      DEMAND FAST     FAST     UNDEFINED           VALID               IMMEDIATE

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/16 00:02:19 2017/04/16 00:07:19 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/16 00:03:24 2017/04/16 00:08:24 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

本文中で利用していたスクリプト：　mview_info.sql

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
col mview_name for a30
select mview_name,refresh_mode,refresh_method,last_refresh_type,after_fast_refresh,compile_state from user_mviews;

col log_user for a20
col schema_user for a20
col interval for a20
col what for a60
select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

col rowner for a20
col rname for a20
select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

ということで、次回、癖モノシリーズ最終回？へ

---

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）