Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製 Tweet

一回、おやすみしましたが、Previously on Mac De Oracle

前々回は、Data Pump、dbms_jobとdbms_schedulerの複雑な関係を紐解いてみました。

今回は、箸休めとして、schemaモードより上位のモードならMviewはMviewのまま複製や移行することは簡単に行えるよね。という確認だけしておこうと思います。
シンプルだと思わせておいて、細けーことやろうとすると一癖ある、まさに癖モノw ということで　:)

---

まずは、おさらいから

data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSにはオブジェクトパスの定義でMviewに関連するオブジェクト全てが定義されています。

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

一方、tableモード向けTABLE_EXPORT_OBJECTSには、Mview、リフレッシュに必要なJOBやREFRESH GROUPなどのオブジェクトパスが定義されていません。
tableモードでMviewを複製しようとすること自体に無理があるのは明らかですね。MViewとしてエクスポートしたくてもオプジェクトパスが定義されていないのですから。
表を対象としているモードだからそれ以外のオブジェクトパスが定義されていないんだよね〜と、無理やり納得しています:)

orcl@SYSTEM> r
  1  select * from TABLE_EXPORT_OBJECTS where
  2    object_path like '%/JOB'
  3    or object_path like '%/MATERIALIZED_VIEW'
  4*   or object_path like '%/REFRESH_GROUP'

no rows selected

おさらいはこれくらいにして、schemaモードエクスポートで以下の図に示したようなMviewの複製が行えるか確認しておきます。

ポンチ絵のとおりではあるのですが、簡単に説明すると、
PDB:ORCLの異なるschema間で高速リフレッシュ可能なMviewがあり、Data Pumpのschemaモードを利用して異なるPDBにMviewを複製するというシナリオです。
複製後はMviewサイトが2つになります。

なぜ、異なるDB（PDB)にMVIEWを複製するシナリオにしたかって？
理由は、DBMS_JOBのジョブは、同一データベース（MTA構成であればPDB毎)でJOB番号により一意に管理されています。
DBMS_JOBのジョブを同一データベース内でexport/importした場合、ジョブが単純に複製される事になりJOB番号の一意制約エラーとなりimportに失敗します。
importできなければ再作成すれば問題ないわけですが、MTA環境なのでわざわざ同一PDB内に作成する必要はないわけです。（手数を減らせるならその方が楽ですから）

エクスポートする前にリフレッシュジョブを一時停止する理由は？
一時停止している理由は静止点を作りたいこともありますが、それをサボると、なかなか理解しにくいタイミングイシューと言われてる事象に遭遇しやすくなるんですよ。(感覚的に）
前述の状況になると高速リフレッシュを再開するには、一度、完全リフレッシュする必要があります。
ここで利用するMviewは2行しかないので完全リフレッシュは苦でもないですが、巨大サイズの表だったら完全リフレッシュはできることなら避けたいですよね。

PDB：ORCLの異なるschema間でmaster表→mv_master表で基本レプリケーション環境作成からData PumpでOmv_master表のオーナースキーマごとPDB:ORCL2へ複製までをつらつらと記録してあります。:)

MTA環境となっています。PDB:ORCL内で基本レプリケーション環境を作り、PDB:ORCL2にDBリンクやMVIEW等関連オブジェクトをData Pumpを利用して複製します。

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

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

PDB:ORCLデータベースにディレクトリオブジェクトを作成します。（Data Pump向け作業ディレクトリ）

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

Directory created.

PDB:ORCLデータベースにマスターサイト向けユーザを作成し、必要最低限の権限を付与

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.

PDB:ORCL、作成したユーザに接続し、マスター表を作成

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.

PDB:ORCL、高速リフレッシュに必要なMaterialized View LogをMaster表に作成します。

orcl@MASTER_SCHEMA> create materialized view log on master;

Materialized view log created.

同じくPDB:ORCLデータベースにMviewサイト向けユーザを作成し、必要最低限の権限を付与します。

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.

PDB:ORCL、Mviewサイト向けに作成したユーザに接続し、マスターサイトへのデータベースリンクとMaterialized Viewを作成します。

orcl@SYS> 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.

PDB:ORCL、MViewサイトのオブジェクト確認からDBMS_JOBとリフレッシュグループの高速リフレッシュされているところまでを確認しています。

Mviewを作成すると、作成したMviewは、TABLEでもあり、MVIEWでもある。ということが確認できます。これ重要ですよ！
data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSビューにTABLEとMATERIALIZED_VIEWの２つのオブジェクトパスがあるにも関わらず、
tableモードのTABLE_EXPORT_OBJECTSビューにはTABLEオブジェクトパスは定義されているのにMATERIALIZED_VIEWオブジェクトパスされていないからMVIEWとしてはエクポートできないと言っていた理由なんですよ！！！！

orcl@MVIEW_SCHEMA1> select object_name,object_type from user_objects;

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

次はuser_jobsからリフレッシュジョブを確認しておきます。これもMviewをリフレッシュするのに必要なオブジェクトです。
data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSビューでJOBオブジェクトパスとして定義されています。これも重要なんです。
前回JOBオブジェクトパスに対応するオブジェクトは、DBMS_JOBのJOBだということを書きましたが、思い出していただけましたか？　

orcl@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/15 00:09:04 2017/04/15 00:14:04 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

最後にリフレッシュグループの確認、意外とこれの存在を忘れてしまうんですよ。私もそうでした。
単一MVIEWのリフレッシュでもリフレッシュグループが自動的に作成されてしまうことを...複数のMVIEWをリフレッシュする時だけに必要なのかと思ったら大間違いw
いつも、つい、忘れちゃいますw　影薄過ぎ！　ですが、前述のWHAT列の内容を見れば、なるほど！　となるはずです。
dbms_refresh.refreshプロシージャは、リフレッシュグループ名が引数！！

orcl@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

ROWNER               RNAME                  REFGROUP        JOB B INTERVAL             NEXT_DATE
-------------------- -------------------- ---------- ---------- - -------------------- -------------------
MVIEW_SCHEMA1        MV_MASTER                    61         81 N sysdate+5/1440       2017/04/15 00:29:29

PDB:ORCL、リフレッシュジョブを一時停止し、expdpのschemaモードでMviewサイト向けスキーマを丸ごとエクスポートします。

orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>true);

PL/SQL procedure successfully completed.

orcl@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

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


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

無事エクスポートできたようなので、次は、
PDB:ORCL2データベースで、ディレクトリオブジェクトを作成します。２つめのMviewサイトの準備です。

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

Directory created.

PDB:ORCL2、schemaモードでインポート。（対象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=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

と、すんなり終了w

PDB:ORCL2、インポートで複製されたMVIEW_SCHEMA1ユーザに接続しオブジェクトを確認
リフレッシュジョブは、エクスポート元と同一ジョブ番号でインポートされ、停止状態。想定通りで一安心：）

orcl2@MVIEW_SCHEMA1> select object_name,object_type from user_objects;

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

orcl2@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

       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"');

orcl2@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

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

PDB:ORCLとORCL2、両MViewサイトのリフレッシュジョブを再開

orcl2@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> 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.

最後に、containers句を使って全体を確認っと！

orcl2@MVIEW_SCHEMA1> conn sys/oracle@orcl12c as sysdba
Connected.
orcl12c@SYS> select rowner,rname,refgroup,job,broken,interval,next_date,con_id from containers(dba_refresh) where rowner='MVIEW_SCHEMA1';

ROWNER               RNAME                  REFGROUP        JOB B INTERVAL             NEXT_DATE               CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1        MV_MASTER                    61         81 N sysdate+5/1440       2017/04/15 22:25:47          5
MVIEW_SCHEMA1        MV_MASTER                    61         81 N sysdate+5/1440       2017/04/15 22:26:52          3

orcl12c@SYS> select mview_name,refresh_mode,refresh_method,last_refresh_type,after_fast_refresh,compile_state from containers(dba_mviews) where mview_name='MV_MASTER';
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

orcl12c@SYS> select job,log_user,schema_user,last_date,next_date,interval,failures,what from containers(dba_jobs) where log_user='MVIEW_SCHEMA1';
       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/15 22:20:47 2017/04/15 22:25:47 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
        81 MVIEW_SCHEMA1        MVIEW_SCHEMA1        2017/04/15 22:21:52 2017/04/15 22:26:52 sysdate+5/1440                0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

何事もなく複製できたところで、次回へ続く。

あ、その前に、環境を初期状態に戻さないと。。

---

Data Pumpも癖モノだよね〜w　その１ - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w　その２ - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w　その3 - dbms_job と dbms_scheduler との複雑な関係