Oracle Data Pump によるバージョンアップ時のオブジェクト差分

この記事はJPOUG Advent Calendar 2023 18日目の記事です。

Data Pumpによるデータ移行で差分発生

　Data PumpでOracle Database 11g からOracle Database 19c へ移行したらオブジェクト数に差分が出たという相談を受けました。そこで差分が出た原因を調査しました。
　Data Pump を使ってスキーマ単位でデータの移行したところ Oracle Database 19c の DBA_OBJECTS ビューの方がオブジェクト数が増えていました。差分となったオブジェクトを確認すると OBJECT_TYPE = 'JOB' でした。このためOracle Database 11g で JOB を作成し、Oracle Database 19c への移行する検証を行いました。

Oracle Database 11g の操作

　旧バージョンでは DBMS_JOB パッケージを使ってジョブを作成していました。そこで STATSPACK の自動スナップショットジョブのスクリプトを使って検証を行います。${ORACLE_HOME}/rdbms/admin/spauto.sql スクリプトを実行すると以下のジョブが作成されます。

spauto.sql の一部

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;

次に Data Pumpを使って PERFSTAT ユーザーのデータをエクスポートします。

$ expdp userid=SYSTEM/{password} schemas=perfstat dumpfile=perfstat11g.dmp

Export: Release 11.2.0.4.0 - Production on 金 12月 1 23:24:19 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACKでは、データベース整合性が自動的に維持されます。
"SYSTEM"."SYS_EXPORT_SCHEMA_01"を起動しています: userid=SYSTEM/******** schemas=perfstat dumpfile=perfstat11g.dmp
BLOCKSメソッドを使用して見積り中です...
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 51.37 MB
・・・
オブジェクト型SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/JOBの処理中です
・・・
マスター表"SYSTEM"."SYS_EXPORT_SCHEMA_01"は正常にロード/アンロードされました
******************************************************************************
SYSTEM.SYS_EXPORT_SCHEMA_01に設定されたダンプ・ファイルは次のとおりです:
  /u01/app/oracle/admin/O11A/dpdump/perfstat11g.dmp
ジョブ"SYSTEM"."SYS_EXPORT_SCHEMA_01"が金 12月 1 23:24:34 2023 elapsed 0 00:00:14で正常に完了しました

出力されたダンプファイルとimpdpコマンドのSQLFILEパラメーターを指定して、ダンプされたSQL文を確認したところ以下のSQLを使ってジョブが作成されていました。

-- new object type path: SCHEMA_EXPORT/JOB
 BEGIN SYS.DBMS_IJOB.SUBMIT(
      JOB=> 41,
      LUSER=> 'PERFSTAT',
      PUSER=> 'PERFSTAT',
      CUSER=> 'PERFSTAT',
      NEXT_DATE=> TO_DATE('2023-12-02 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> 'trunc(SYSDATE+1/24,''HH'')',
      BROKEN=>  FALSE,
      WHAT=> 'statspack.snap;',
      NLSENV=> 'NLS_LANGUAGE=''JAPANESE'' NLS_TERRITORY=''JAPAN'' NLS_CURRENCY=''¥'' NLS_ISO_CURRENCY=''JAPAN'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''RR-MM-DD'' NLS_DATE_LANGUAGE=''JAPANESE'' NLS_SORT=''BINARY''',
      ENV=> '0102000200000000');
      END;
 /

 BEGIN SYS.DBMS_IJOB.INSTANCE(
      JOB=> 41,
      INSTANCE=> 1,
      FORCE=> TRUE);
      END;
 /

Oracle Database 19c の操作

ダンプファイルを Oracle Database 19c にコピーしてインポートを行います。

$ impdp userid=SYSTEM/{password}@pdb1 directory=dpdir dumpfile=perfstat11g.dmp schemas=perfstat

Import: Release 19.0.0.0.0 - Production on 金 12月 1 14:36:54 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SYSTEM"."SYS_IMPORT_SCHEMA_01"は正常にロード/アンロードされました
"SYSTEM"."SYS_IMPORT_SCHEMA_01"を起動しています: userid=SYSTEM/********@pdb1 directory=dpdir dumpfile=perfstat11g.dmp schemas=perfstat
オブジェクト型SCHEMA_EXPORT/USERの処理中です
・・・

　DBA_OBJECTS ビューを確認すると、Oracle Database 11g には存在しなかったオブジェクトが増えています。

SQL> SELECT object_name FROM dba_objects WHERE object_type='JOB' AND owner='PERFSTAT';

OBJECT_NAME
--------------------------------------------------------------------------------
DBMS_JOB$_41

オブジェクトが増えた原因

　DBA_OBJECTS ビューにオブジェクトが増えた原因は Oracle Database 19c で DMBS_JOBS パッケージが DBMS_SCHEDULER パッケージへのインターフェースとなり、両者が統合されたからでした。従来のバージョンでは DBMS_JOB パッケージで作成したオブジェクトは DBA_OBJECTS ビューに格納されず、DBMS_SCHEDULER パッケージで作成されたオブジェクトは DBA_OBJECTS ビューに格納されていました。

Oracle Database 19c データベース管理者ガイド には以下の記述があります。

A DBMS_JOBのサポート
DBMS_JOBパッケージは引き続きサポートされます。ただし、DBMS_JOBジョブを発行するデータベース・スキーマにCREATE JOB権限を付与する必要があります。
Oracle Schedulerは、DBMS_JOBパッケージを置換します。DBMS_JOBは下位互換性のために引き続きサポートされますが、DBMS_JOBからOracle Schedulerに切り替えることをお薦めします。
Oracle Database 19c以降のリリースでは、アップグレードでDBMS_SCHEDULERを使用して既存のDBMS_JOBジョブを再作成できる場合、下位互換性のために、アップグレード後も引き続きDBMS_JOBはDBMS_SCHEDULERジョブに対するレガシー・インタフェースとして機能します。メタデータの問題のため、DBMS_SCHEDULERを使用して既存のジョブを再作成できない場合は、アップグレードの事前チェックを実行した際にJOB_TABLE_INTEGRITY警告が表示されます。

このため DBMS_JOB パッケージで作成されたジョブが、Data Pump インポート時に DBMS_SCHEDULER パッケージに変換され、DBA_OBJECTS ビューに登録されたことでオブジェクト数の違いが発生しました。
　Oracle Database 19c で DBA_JOBS ビューを検索すると内部では SCHEDULER$_DBMSJOB_MAP テーブルを参照するように変更されています。

SQL> SET AUTOTRACE ON
SQL> SELECT job FROM dba_jobs;

       JOB
----------
        41

実行計画
----------------------------------------------------------
Plan hash value: 4052006460

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |     1 |   206 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                        |     1 |   206 |     5   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                        |     1 |   163 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | SCHEDULER$_DBMSJOB_MAP |     1 |   145 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| USER$                  |     1 |    18 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | I_USER1                |     1 |       |     0   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN            | I_OBJ2                 |     1 |    43 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

明日の JPOUG Advent Calendar 2023 は Hiroyuki Nakaie さんの予定です。

Author: Noriyoshi Shinoda / Date: December 18, 2023