RDS Oracle 雑多なメモ#20 / DBMS_DATAPUMPパッケージ Schema mode de expdp/impdpの準備 SQL_FILEモードでDDL抜き出し
RDS Oracle 雑多なメモ#20 / DBMS_DATAPUMPパッケージ Schema mode de expdp/impdpの準備
Previously, Mac De Oracle...
RDS Oracle 雑多なメモ#19 FAQ / DBMS_DATAPUMPパッケージ de ジョブの停止
でした。
今回は、RDS Oracleへスキーマ単位でインポートを行う準備に必要などを。
実は、RDS Oracleでは、フルインポートはしないようにと記載されています。ということは、つまりスキーマモードでのインポートと、それ以下の単位でのインポートのみを行うこと、となっています。ここがポイント
詳細は以下マニュアルを参照ください。
Amazon RDS での Oracle へのデータのインポート
フルモードでインポートできない場合の注意点として、スキーマレベルでインポートする際に必要となる可能性のあるオブジェクトは事前に何がしかの方法で作成しておく必要があるということを意味します。
代表的なオブジェクトは以下、
- ユーザーのデフォルトTABLESPACE（USERS表をそのまま利用しているケースは希だと思います）
- ユーザーのPROFILE（パスワード有効期限管理などで利用しているケースは多いかも）
- ROLE（直接付与権限以外はロールを付与して権限を管理することは多いはずです）
- DIRECTORY（Oracle側で事前に作成しているディレクトリ以外のディレクトリへのアクセスが必要な場合にはディレクトリオブジェクトも事前に作成しておく必要があります）
さらに、スキーマ間で依存している場合、特に、他のスキーマのオブジェクト権限を付与する必要がある場合は、スキーマをインポートする順番にも気を遣う必要も出てきます。
インポート時にエラーが出ないものもありますが。。それは今回のテーマではないのでこの辺でw
（依存しているオブジェクトは事前にALL/DBA_DEPENDENCIESビューを利用して調査しておくとよいでしょうね）
では、DDLを取得する前に、ネタを仕込んでおきましょう。
表領域 fizzbuzz をデフォルト表領域とし、プロファイル fizzbuzz が設定されたユーザー hoge を作成します。
また、connectロールとresourceロールを付与された fizzbuzz ロールが hoge に付与されているとします。
表領域の追加
BILL> create tablespace fizzbuzz datafile size 100m autoextend on maxsize 1g;
Tablespace created.
プロファイルの追加
BILL> create profile fizzbuzz limit idle_time unlimited;
Profile created.
ロールの追加
BILL> create role fizzbuzz;
Role created.
BILL> grant connect to fizzbuzz;
Grant succeeded.
BILL> grant resource to fizzbuzz;
Grant succeeded.
ユーザーの作成（デフォルト表領域とプロファイルの指定）
BILL> create user hoge identified by xxxxxxxxxxxxxxxx
2 default tablespace fizzbuzz
3 temporary tablespace temp
4 quota unlimited on fizzbuzz
5 profile fizzbuzz;
User created.
ロールの付与
BILL> grant fizzbuzz to hoge;
Grant succeeded.
上記のようなユーザーだとするとスキーマ単位のインポート前に表領域の作成、プロファイルの作成、ロールの作成が必要になりますよね。
（スキーマレベルのインポートでは依存オブジェクトが自動的にインポートされるわけでは無いので）
また、元のデータベースでOracle Managed Fileを利用した表領域では無い場合にはOracle Managed Fileを利用した表領域として再作成してあげる必要あります。（ここも重要。元のデータベースもOracle Managed fileを利用していればそのまま利用できます）
ということで、DBMS_METADATA.GET_DDL()または、フルエクスポートしたData Pumpのダンプファイルファイルから上記に該当するDDLを抜き出し、事前にオブジェクトを作成しておく必要がありますよね。
（DDLソースコード管理されているのであれば、それらを再利用することも可能だとは思いますが、世の中広いのでそんな管理されていないところも多いでしょうし。DDLを取り出す複数の方法は覚えてて損は無いですよ :）
今回は、フルエクスポートダンプファイルがあるので、そこからData Pump APIを利用して取り出してみようと思います。(DBMS_METADATA.GET_DDL()の方が楽だとは思います。個人的にはw)
フルダンプからDDLを取り出す際でも、重要なのがオブジェクトパス（このパスの階層を意識していないと思わぬ失敗をするのは昔のエントリの通り）ですが、今回は比較的シンプルなので確認してみましょう。
事前にエクスポートのログから以下のパスを探し出しておきます。
（今回の例では、表領域とプロファイル、それにロールへのオブジェクトパスです。以下がそのオブジェクトパス）
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/ROLE
余談ですが、同じ階層オブジェクトパスにスキーマオブジェクトのパス(DATABASE_EXPORT/SCHEMA)が存在しています。同じ階層なので、スキーマレベルのインポートではTABLESPACE/ROLE/PROFILEはインポートされることはありません。（この階層構造を理解できれば楽）
DATABASE_EXPORT/SCHEMA以下がインポートの対象になるので、メタデータフィルターでEXCLUDE または INCLUDEするか制御できることになります。TABLESPACE,PROFILEやROLEはSCHEMAと同レベルなのでそもそも対象外となりフィルタすることはできません。
Processing object type DATABASE_EXPORT/SCHEMA/
理屈がわかれば癖の強いDATAPUMPなんてw
では、DDLを取り出してみましょう。
前提のオブジェクトを含むフルエクスポートダンプファイル fullexp.dmp は事前に取得済みであるとします。
BILL> @extract_ddl data_pump_dir fullexp DATABASE_EXPORT/TABLESPACE'',''DATABASE_EXPORT/PROFILE'',''DATABASE_EXPORT/ROLE
Master table "BILL"."SYS_SQL_FILE_FULL_17" successfully loaded/unloaded
Starting "BILL"."SYS_SQL_FILE_FULL_17":
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/ROLE
Job "BILL"."SYS_SQL_FILE_FULL_17" successfully completed at Sun Jul 19 19:12:00 2020 elapsed 0 00:00:02
PL/SQL procedure successfully completed.
取り出したDDLを確認します
BILL> @cat_file data_pump_dir fullexp.txt
TEXT
-----------------------------------------------------------------------------
...中略...
-- new object type path: DATABASE_EXPORT/TABLESPACE
...中略...
CREATE BIGFILE TABLESPACE "FIZZBUZZ" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT 104857600 MAXSIZE 1073741824
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
-- new object type path: DATABASE_EXPORT/PROFILE
...中略...
CREATE PROFILE "FIZZBUZZ"
LIMIT
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME UNLIMITED
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS DEFAULT
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
INACTIVE_ACCOUNT_TIME DEFAULT ;
-- new object type path: DATABASE_EXPORT/ROLE
...中略...
CREATE ROLE "FIZZBUZZ";
...中略...
スキーマ hogeインポート前に作成が必要なオブジェクトのDDLを取得できました。
これでスキーマレベルのインポート準備完了。
つづく。
参考）
DDLをダンプファイルから取り出すスクリプト例は以下の通り。DBMS_DATAPUMP.OPEN()関数でoperationに’SQL_FILE'を渡しているところとDBMS_DATAPUMP.METADATA_FILTER()プロシージャで取り出すDDLのオブジェクトパスリストを渡している箇所がポイントです
PL/SQLパッケージおよびタイプ・リファレンス - 47 DBMS_DATAPUMP
BILL> !cat extract_ddl.sql
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v4Debug VARCHAR2(50);
cDirectory CONSTANT VARCHAR2(30) := UPPER('&1');
cDumpFileName CONSTANT VARCHAR2(64) := '&2'||'.dmp';
cLogFileName CONSTANT VARCHAR2(64) := '&2'||'.log';
cDdlFileName CONSTANT VARCHAR2(64) := '&2'||'.txt';
cPathList CONSTANT VARCHAR2(4000) := UPPER('&3');
i NUMBER;
vDataPumpJobHandle NUMBER;
vProgress_ratio NUMBER;
vJobState VARCHAR2(30);
oLogEntry ku$_LogEntry;
oStatus ku$_Status;
BEGIN
DBMS_OUTPUT.ENABLE;
v4Debug := 'OPEN';
vDataPumpJobHandle
:= DBMS_DATAPUMP.OPEN (
operation => 'SQL_FILE'
,job_mode => 'FULL'
,remote_link => NULL
);
v4Debug := 'ADD_FILE - dumpfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cDumpFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);
v4Debug := 'ADD_FILE - logfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cLogFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
v4Debug := 'ADD_FILE - Ddlfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cDdlFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE
);
v4Debug := 'METADATA_FILTER - schema name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'INCLUDE_PATH_LIST'
,value => '''' || cPathList || ''''
);
v4Debug := 'START_JOB';
DBMS_DATAPUMP.START_JOB (
handle => vDataPumpJobHandle
);
v4Debug := 'JOB_STATE';
vProgress_ratio := 0;
vJobState := 'UNDEFINED';
WHILE (vJobState != 'COMPLETED') AND (vJobState != 'STOPPED') LOOP
DBMS_DATAPUMP.GET_STATUS (
vDataPumpJobHandle
,DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR
+ DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS
+ DBMS_DATAPUMP.KU$_STATUS_WIP
,-1
,vJobState
,oStatus
);
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_WIP) != 0)
THEN
oLogEntry := oStatus.wip;
ELSE
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0)
THEN
oLogEntry := oStatus.error;
ELSE
oLogEntry := NULL;
END IF;
END IF;
IF oLogEntry IS NOT NULL
THEN
i := oLogEntry.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(oLogEntry(i).LogText);
i := oLogEntry.NEXT(i);
END LOOP;
END IF;
END LOOP;
DBMS_DATAPUMP.DETACH(vDataPumpJobHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm());
DBMS_OUTPUT.PUT_LINE(v4Debug);
RAISE;
END;
/
UNDEFINE 1
UNDEFINE 2
UNDEFINE 3
SET SERVEROUTPUT OFF
SET VERIFY ON
やっと、いろいろ落ち着いたかと思ったら次から次に似たような事案が落ちてくる今日この頃w。
ということで、しばらくは機嫌悪いかも、ガルーーーーーっw
・Data Pumpも癖モノだよね〜w その１ - queryパラメーターの解析タイミング・Data Pumpも癖モノだよね〜w その２ - Materialized ViewをTableとして移行する・Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係・Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製・Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw）
・Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製（オプジェクトパス de 絞り込み）
・Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製（オプジェクトパスが不足すると...
・RDS Oracle 雑多なメモ#1 / FAQ
・RDS Oracle 雑多なメモ#2 / FAQ
・RDS Oracle 雑多なメモ#3 / FAQ
・RDS Oracle 雑多なメモ#4 / FAQ
・RDS Oracle 雑多なメモ#5 / FAQ
・RDS Oracle 雑多なメモ#6 / FAQ
・RDS Oracle 雑多なメモ#7 / FAQ
・RDS Oracle 雑多なメモ#8 / FAQ
・RDS Oracle 雑多なメモ#9 / FAQ
・RDS Oracle 雑多なメモ#10 / FAQ
・RDS Oracle 雑多なメモ#11 / FAQ
・RDS Oracle 雑多なメモ#12 / FAQ
・RDS Oracle 雑多なメモ#13 / FAQ
・RDS Oracle 雑多なメモ#14 - おまけ / FAQ
・RDS Oracle 雑多なメモ#15 - おまけのおまけ / FAQ
・RDS Oracle 雑多なメモ#16 - 再び:) / FAQ
・RDS Oracle 雑多なメモ#17/ FAQ
・RDS Oracle 雑多なメモ#18 / DBMS_DATAPUMPパッケージ de expdp/impdp
・RDS Oracle 雑多なメモ#19 FAQ / DBMS_DATAPUMPパッケージ de ジョブの停止
最近のコメント