2017年4月25日 (火)

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

Previously on Mac De Oracle

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

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

20170415_14044


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 その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - 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)

| | コメント (0) | トラックバック (0)

2017年4月23日 (日)

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

Previously on Mac De Oracle

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

エントリには記載していなかったログを見ていて驚いた!ことを調査してみる回として”その4と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


インポート後、どちらのリフレッシュジョブも停止中ですが、1箇所違いがみられます。
そう、インポートした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 その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係
Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製

| | コメント (0) | トラックバック (0)

2017年4月22日 (土)

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

一回、おやすみしましたが、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つになります。

20170415_14044


なぜ、異なる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の2つのオブジェクトパスがあるにも関わらず、
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データベースで、ディレクトリオブジェクトを作成します。2つめの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 その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係

| | コメント (0) | トラックバック (0)

2017年4月10日 (月)

Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係

Previously on Mac De Oracle

前回は、Data PumpとMaterialized Viewという癖モノ二大巨頭を絡ませて見ました。

今回は、三つ巴?な感じでお送りしたいと思います。:)



以下は、Materialized ViewをリフレッシュするDBMS_JOBです。

今頃気づいたか! という感じですが、dbms_job で作成されるJOBは、ALL/DBA/USER_OBJECTSには含まれないのです!!

orcl@USERS> 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
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
21 USERS USERS 2017/04/09 19:18:34 2017/04/09 19:19:34 sysdate+1/1440 0 dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

orcl@USERS> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER MATERIALIZED VIEW
SYS_C0014637 INDEX
MVIEW_MASTER TABLE
TOUSERM DATABASE LINK


ここで、注目!!
dbms_jobは、dba_objectsにはカウントされませんが、dbms_schedulerは、object_type=JOBとしてdba_objectsにカウントされるということ!!
以下にある、OBJECT_TYPE=JOBは、OBJECT_NAME=TESTという直前に作成されたdbms_schedulerのJOBだということ!!

ORCL@USERS> begin
dbms_scheduler.create_job (
job_name=>'test'
,job_type=>'PLSQL_BLOCK'
,job_action=>'BEGIN dbms_refresh.refresh(''USERS.MVIEW_MASTER''); END;'
,start_date=>systimestamp
,repeat_interval=>'FREQ=MONTHLY'
,end_date=>systimestamp + interval '1' year
,enabled=>true
,comments=>null
);
end;
/

PL/SQL procedure successfully completed.


なんとまぁ、面倒くさい、DBMS_JOBのJOBも、DBMS_SCHEDULERのJOBも、同じ初期化パラメータ(job_queue_processes)を利用するのにオブジェクト扱いされたりされなかったり、面倒くさい癖モノです。注意しましょうね!!

Oracle® Databaseリファレンス 12c リリース1 (12.1) 1.126 JOB_QUEUE_PROCESSES
https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-B8B68D16-00A3-43DD-BE39-01F877880955.htm

ORCL@USERS> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER TABLE
SYS_C0014637 INDEX
MVIEW_MASTER MATERIALIZED VIEW
TEST JOB
TOUSERM DATABASE LINK

という準備運動が終わったところで、本題の Data Pumpでのお話です。
癖モノData Pumpと癖モノ感たっぷりの2種類のJOB、2時間ドラマの複雑でドロドロした関係が予想される展開になってきましたw

orcl@USERS> 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
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
21 USERS USERS 2017/04/09 19:18:34 2017/04/09 19:19:34 sysdate+1/1440 0 dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

orcl@USERS> select job_name,job_action,repeat_interval,start_date from user_scheduler_jobs;

JOB_NAME JOB_ACTION REPEAT_INTERVAL START_DATE
---------- ------------------------------------------------------------------- -------------------- ----------------------------------------
TEST BEGIN dbms_refresh.refresh('USERS.MVIEW_MASTER'); END; FREQ=MONTHLY 09-APR-17 10.36.05.814317 PM +09:00


まず、Data Pumpで扱える"OBJECT”が定義されているSCHEMA_EXPORT_OBJECTSをみると以下の"OBJECT”定義が見つかります。
JOBというオブジェクトのコメントを読んでも、2種類あるJOBのどちらを指しているのか、はたまた、いずれか一つなのかさっぱりわかりません。

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

これは試して、ガッテン!! するしかありません!!!

schemaモードでエクスポートします。このとき、includeパラメータでJOBだけをエクスポートするよう指定します!! 

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=homedir dumpfile=jobs.dmp logfile=jobsexp.log schemas=users include=job
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/JOB
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/jobs.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Apr 9 23:13:47 2017 elapsed 0 00:00:04

SQLFILEパラメータを指定して内容を確認してみます。(SQLFILEパラメータを指定した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_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/********@orcl directory=homedir dumpfile=jobs.dmp logfile=jobsexp.log schemas=users sqlfile=jobsexp_ddl.txt
Processing object type SCHEMA_EXPORT/JOB
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Sun Apr 9 23:20:44 2017 elapsed 0 00:00:03


おおおおお、これは!!! 

なんということでしょう!
Data Pumpでは、JOBオブジェクトは、DBMS_JOBのJOBオブジェクト(dba_objectsではオブジェクト扱いされていないのに!!)

だとすると、dba_objectsでは”OBJECT"として扱われている、DBMS_SCHEDULERのJOBはの扱いはいかに。。。

以下のとおり、DBMS_IJOBと内部的プロリージャに置き換わっていますが、パラメータを見れば一目瞭然、DBMS_JOBの定義しかありません。
つまり、Data PumpのJOBオブジェクトは、DBMS_JOBのJOBであることがわかりました!!

[oracle@catfish ˜]$ cat jobsexp_ddl.txt 

・・・中略・・・

-- new object type path: SCHEMA_EXPORT/JOB
BEGIN SYS.DBMS_IJOB.SUBMIT(
JOB=> 21,
LUSER=> 'USERS',
PUSER=> 'USERS',
CUSER=> 'USERS',
NEXT_DATE=> TO_DATE('2017-04-09 23:13:51', 'YYYY-MM-DD:HH24:MI:SS'),
INTERVAL=> 'sysdate+1/1440',
BROKEN=> FALSE,
WHAT=> 'dbms_refresh.refresh(''"USERS"."MVIEW_MASTER"'');',

・・・中略・・・

では、DBMS_SCHEDULERのJOBは。。。それは。。。PROCOBJというOBJECT_PATHに含まれているようで(ほぼ誰にも読み取れないw)。。。

MOSにもありそうだけど、この辺りをまとめてて疲れて、めんどくさい病の発作がw 
もっと深掘りする気力があったら探すかもw
Export/Import Scheduler Jobs


手取り早く、試して、ガッテン! includeパラメータで”PROCOBJ”を指定します。

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=homedir dumpfile=scheduler_jobs.dmp logfile=scheduler_jobsexp.log schemas=users include=procobj
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scheduler_jobs.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Apr 9 23:49:20 2017 elapsed 0 00:00:04

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_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/********@orcl directory=homedir dumpfile=scheduler_jobs.dmp logfile=jobsddldump.log schemas=users sqlfile=jobsexp2_ddl.txt
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Sun Apr 9 23:50:05 2017 elapsed 0 00:00:02


確かに、PROCOBJにDBMS_SCHEDULERのJOBが含まれている!!

[oracle@catfish ˜]$ cat jobsexp2_ddl.txt

・・・中略・・・

BEGIN
dbms_scheduler.create_job('"TEST"',
job_type=>'PLSQL_BLOCK'
, job_action=>'BEGIN dbms_refresh.refresh(''USERS.MVIEW_MASTER''); END;'
, number_of_arguments=>0
, start_date=>TO_TIMESTAMP_TZ('09-APR-2017 10.36.05.814317000 PM +09:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english')
, repeat_interval=> 'FREQ=MONTHLY'
, end_date=>TO_TIMESTAMP_TZ('09-APR-2018 10.36.05.814344000 PM +09:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english')
, job_class=>'"DEFAULT_JOB_CLASS"'
, enabled=>FALSE
, auto_drop=>TRUE
,comments=>NULL
);
dbms_scheduler.enable('"TEST"');
COMMIT;
END;
/

・・・中略・・・

登場人物の関係が複雑すぎてよくわからなくなってきたので、まとめ。

DBMS_JOBのJOBは、DBA_OBJECTS上、オブジェクトとは扱われていない。
DBMS_SCHEDULEのJOBは、DBA_OBJECTS上、オブジェクトとして扱われている。
どちらのJOBも、初期化パラメータ、job_queue_processesにより制御されている。
Data PumpのSCHEMA_EXPORT_OBJECTSにて定義されているJOBオブジェクトは、DBMS_JOBのJOBのことである。
Data PumpのSCHEMA_EXPORT_OBJECTSにて定義されているPROCOBJオブジェクトが、DBMS_SCHEDULERのJOBのこと!?であるようだ。
(オブジェクト名からは想像できない可読性の悪さはなんとかしてくれ!)

DBMS_SCHEDULERへの移行が推奨されながらDBMS_JOBが未だに存在していることの弊害のようにも思えてきた。
このあたり、理解しやすいように改善してもらいたい癖モノの一つとしてリストに加えておこう。


満開の桜なのに残念な天気の日曜日終わってしまった!! ということで今日はここまで。




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する

| | コメント (0) | トラックバック (0)

2017年4月 9日 (日)

Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する

Previously on Mac De Oracle

前回はMac De OracleはData Pumpのqueryパラメーターの解析タイミングについて調べたメモでした。

今回はタイトルのとおり、Materialized ViewをTableとして移行していしまおうというお題。
実は、Materialized Viewとして移行しようとして失敗ったのがきっかけで知ったんですけど、あまり書かれていないので仕様だとは思うんですが(そうなんですよね?w)
(癖モノData Pumpと癖モノMaterialized Viewを扱おうとする時点で、すんなり行くわけがない、ぐらいの覚悟はしておいたほうが無難でしょうけど)


ちなみに、今回の内容とは関係ないですが、ViewをTableとしてエクスポートする機能も12cR1から提供されていますね(使ったことはまだないですが)
VIEWS_AS_TABLES
https://docs.oracle.com/database/121/SUTIL/GUID-E4E45E81-5391-43BE-B27D-B763EF79A885.htm#SUTIL3904

Exporting views as tables Oracle Database 12C release 1 (12.1)
http://dbaora.com/exporting-views-as-tables-oracle-database-12c-release-1-12-1/



以下のようなMVIEWがあります。
エクスポート側ではMaterialize Viewとして定義され、自動的にリフレッシュされています。

orcl@USERS>  select object_name,object_type from user_objects where object_name='MVIEW_MASTER';

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW_MASTER MATERIALIZED VIEW
VIEW_MASTER TABLE

orcl@USERS> select job,log_user,last_date,next_date,interval,failures,broken,what from user_jobs;

JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL FAILURES B WHAT
---------- ------------------------------ ------------------- ------------------- -------------------- ---------- - --------------------------------------------------
21 USERS 2017/04/09 14:16:53 2017/04/09 14:17:53 sysdate+1/1440 0 N dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

表モードでMviewからTableへの変換!
tablesパラメータでMviewを指定しているところがポイント! MVIEWでもありTABLEでもあるのでTABLEとしてエクスポートができます。

表モードで有効なオブジェクトをTABLE_EXPORT_OBJECTSで確認してみて気づいたのですが、TABLE_EXPORT_OBJECTSには MATERIALIZED_VIEW は存在しません。

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


つまり、表モードではMaterialized ViewをMaterizlized Viewとしてエクスポート/インポートすることはできないということ!!(?)のようです。

[oracle@catfish ˜]$ expdp users/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log tables=mview_master

Export: Release 12.1.0.2.0 - Production on Sun Apr 9 14:46:27 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
Starting "USERS"."SYS_EXPORT_TABLE_01": users/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log tables=mview_master
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "USERS"."MVIEW_MASTER" 5.492 KB 2 rows
Master table "USERS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USERS.SYS_EXPORT_TABLE_01 is:
/home/oracle/mv2table.dmp
Job "USERS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Apr 9 14:47:09 2017 elapsed 0 00:00:38

同一DB(PDB)の異なるschemaへ表としてインポート(異なるschemaへインポートするのでremap_schamaパラメータをお忘れなく)

[oracle@catfish ˜]$ impdp hr/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log remap_schema=users:hr

Import: Release 12.1.0.2.0 - Production on Sun Apr 9 14:49:56 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 "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log remap_schema=users:hr
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."MVIEW_MASTER" 5.492 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" successfully completed at Sun Apr 9 14:50:19 2017 elapsed 0 00:00:22


表としてインポートされています!

orcl@HR> select object_name,object_type from user_objects where object_name='MVIEW_MASTER';

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER TABLE

orcl@HR> select job,log_user,last_date,next_date,interval,failures,broken,what from user_jobs;

no rows selected

orcl@HR> select count(1) from mview_master;

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


さて、お次、
表モードとは異なり、schemaモードのオブジェクトを定義しているSCHEMA_EXPORT_OBJECTSには、Materialized ViewをMaterialized Viewとしてエクスポート/インポートするために必要な以下のオブジェクト定義が存在します。
ということは、schemaモードではMviewをMviewとして移行することも、MviewをTableとして移行することもできそうな設定になっているようです、

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


ということで、schemaモードでも同様の変換が可能かためしてみます。
schemaモードでinclude=TABLEとして表関連オブジェクトのみエクスポート。
schemaモードを選択した場合は、materialized view、job、refresh groupもスコープに入ってしまうので、余分なオブジェクトや定義を取り込まないようにするのがポイント(でした)

[oracle@catfish ˜]$ expdp users/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE

Export: Release 12.1.0.2.0 - Production on Wed Apr 5 00:40:17 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
Starting "USERS"."SYS_EXPORT_SCHEMA_01": users/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
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
. . exported "USERS"."MVIEW_MASTER" 5.484 KB 1 rows
Master table "USERS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USERS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/test.dmp
Job "USERS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Apr 5 00:40:43 2017 elapsed 0 00:00:25

同一DB(PDB)の異なるschemaへ表としてインポート(異なるschemaへインポートするのでremap_schamaパラメータをお忘れなく)

[oracle@catfish ˜]$ impdp users2/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE remap_schema=users:users2

Import: Release 12.1.0.2.0 - Production on Wed Apr 5 00:42:18 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 "USERS2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USERS2"."SYS_IMPORT_FULL_01": users2/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE remap_schema=users:users2
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USERS2"."MVIEW_MASTER" 5.484 KB 1 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
Job "USERS2"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 5 00:42:24 2017 elapsed 0 00:00:05


マスター表からDBリンク経由でCTASするという手もありますが、Materialized ViewではなくTableとして移行する場合、Data Pumpを利用する方法もあるなぁと。
そして、includeパラメータやエクスポートモードには注意しないと、いろいろハマりそうだなぁ、と思ったのでしたw


ということで、schemaモードでもMaterialized ViewをTableとして移行できるよ! めでたしめでたしw (失敗は気づきの母w)

[oracle@catfish ˜]$ sqlplus users2/********

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 5 00:42:39 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Wed Apr 05 2017 00:42:18 +09: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

ORCL@USERS2> select object_name,object_type from user_objects where object_name='MVIEW_MASTER';

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER TABLE

ORCL@USERS2> select * from user_mviews;
no rows selected

ORCL@USERS2> select job,log_user,last_date,next_date,interval,failures,broken,what from user_jobs;

no rows selected

ORCL@USERS2> select * from mview_master;

ID DATA
---------- ----------
1 test1


今日はここまで。


Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング

| | コメント (0) | トラックバック (0)

2017年3月 3日 (金)

号外:遅まきながら、Oracle Database 12c R2インストールフェスタ参戦w

日本のサイトにはまだ見当たりませんが、本家のサイトからOracle Database 12c Release 2がダウンロード可能となったのをTanelのつぶやきで知ったのはいいが、仕事疲れで帰宅後、爆睡して出遅れ感満載で参戦中w

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

20170303_53712

ダウンロードしてるだけなんで、まだ、遊ぶ余裕はないかもしれないのですが:)

20170303_53944


| | コメント (0) | トラックバック (0)