演習: Oracle Data Pumpダンプ・ファイルの妥当性のチェック

この演習では、チェックサムを使用して、オブジェクト・ストアとの間での転送後とオンプレミスでのダンプ・ファイルの保存後にもOracle Data Pumpダンプ・ファイルが有効であることを確認する方法を示します。チェックサムにより、誤ったまたは悪意のある変更が行われていないことが保証されます。

  1. 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
  2. 演習を開始する前に、/home/oracle/labs/M104786GC10/DP.shシェル・スクリプトを実行します。このシェル・スクリプトでは、表HR.EMPLOYEESを作成し、PDB20にエクスポートします。
    $ cd /home/oracle/labs/M104786GC10
    $ /home/oracle/labs/M104786GC10/DP.sh
    SQL*Plus: Release 20.0.0.0.0 - Production on Thu Feb 6 06:57:22 2020
    Version 20.2.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
        
    Connected to:
    Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
    Version 20.2.0.0.0
    
    specify password for HR as parameter 1:
    
    specify default tablespeace for HR as parameter 2:
    
    specify temporary tablespace for HR as parameter 3:
    
    specify log path as parameter 4:
    
    PL/SQL procedure successfully completed.
    
    User created.
    
    User altered.
    
    User altered.
    
    Grant succeeded.
    
    Grant succeeded.
    
    Session altered.
    
    Session altered.
    
    Session altered.
    
    ******  Creating REGIONS table ....
    
    Table created.
    
    Index created.
    
    Table altered.
    
    ******  Creating COUNTRIES table ....
    
    Table created.
    
    Table altered.
    
    ******  Creating LOCATIONS table ....
    
    Table created.
    
    Index created.
    
    Table altered.
    
    Sequence created.
    
    ******  Creating DEPARTMENTS table ....
    
    Table created.
    
    Index created.
    
    Table altered.
    
    Sequence created.
    
    ******  Creating JOBS table ....
    
    Table created.
    
    Index created.
    
    Table altered.
    
    ******  Creating EMPLOYEES table ....
    
    Table created.
    
    Index created.
    
    Table altered.
    
    Table altered.
    
    Sequence created.
    
    ******  Creating JOB_HISTORY table ....
    
    Table created.
    
    Index created.
    
    Table altered.
    
    ******  Creating EMP_DETAILS_VIEW view ...
    
    View created.
    
    Commit complete.
    
    Session altered.
    
    ******  Populating REGIONS table ....
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    ******  Populating COUNTIRES table ....
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    ******  Populating LOCATIONS table ....
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    ******  Populating DEPARTMENTS table ....
    
    Table altered.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    ******  Populating JOBS table ....
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    ******  Populating EMPLOYEES table ....
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    ******  Populating JOB_HISTORY table ....
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    1 row created.
    
    Table altered.
    
    Commit complete.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Index created.
    
    Commit complete.
    
    Procedure created.
    
    Trigger created.
    
    Trigger altered.
    
    Procedure created.
    
    Trigger created.
    
    Commit complete.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Comment created.
    
    Commit complete.
    
    SQL> Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
    Version 20.2.0.0.0
    
    SQL*Plus: Release 20.0.0.0.0 - Production on Fri Feb 7 05:24:12 2020
    Version 20.2.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
    Version 20.2.0.0.0
    
    
    Directory created.
    
    Grant succeeded.
    
    Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
    Version 20.2.0.0.0
    $
  3. HR.EMPLOYEESをエクスポートし、ダンプ・ファイルにチェックサムを追加して、エクスポート後もダンプ・ファイルがまだ有効であり、データが元のままで破損していないことを確認できるようにします。Oracle Data Pumpのエクスポートでは、制御情報がダンプ・ファイルのヘッダー・ブロックに書き込まれます。Oracle Database 20cでは、Oracle Data Pumpおよび外部表のダンプ・ファイル内のヘッダー以外の残りのすべてのブロックについてチェックサムをさらに追加することで、データ整合性チェックが拡張されています。
    1. エクスポート操作時にCHECKSUMパラメータを使用します。
      $ expdp system@PDB20 TABLES=hr.employees DUMPFILE=dp_dir:emp.dmp CHECKSUM=yes REUSE_DUMPFILES=yes
      
      Export: Release 20.0.0.0.0 - Production on Thu Feb 6 07:14:45 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@PDB20 TABLES=hr.employees dump file=dp_dir:emp.dmp CHECKSUM=YES
      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
      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
      Processing object type TABLE_EXPORT/TABLE/TABLE
      Processing object type TABLE_EXPORT/TABLE/COMMENT
      Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/TRIGGER
      . . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
      ORA-39173: Encrypted data has been stored unencrypted in dump file set.
      Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
      Generating checksums for dump file set
      ******************************************************************************
      Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
        /home/oracle/labs/M104786GC10/emp.dmp
      Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Thu Feb 6 07:15:15 2020 elapsed 0 00:00:26
      $

      チェックサム・アルゴリズムのデフォルトはSHA256 256ビットです。

    2. SHA384 384ビット・ハッシュ・アルゴリズム、SHA512 512ビット・ハッシュ・アルゴリズムまたはCRC32 32ビット・チェックサムを使用する場合は、SHA256 256ビット・ハッシュ・アルゴリズムを使用するCHECKSUMパラメータではなく、CHECKSUM_ALGORITHMパラメータを使用します。
      $ expdp system@PDB20 TABLES=hr.employees DUMPFILE=dp_dir:emp384.dmp CHECKSUM_ALGORITHM=SHA384 CHECKSUM=no REUSE_DUMPFILES=yes
      
      Export: Release 20.0.0.0.0 - Production on Thu Feb 6 07:14:45 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      ORA-39002: invalid operation
      ORA-39050: parameter CHECKSUM=NO is incompatible with parameter CHECKSUM_ALGORITHM
      
      $
      $ expdp system@PDB20 TABLES=hr.employees DUMPFILE=dp_dir:emp512.dmp CHECKSUM_ALGORITHM=SHA512 REUSE_DUMPFILES=yes
      
      Export: Release 20.0.0.0.0 - Production on Thu Feb 6 07:50:05 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@PDB20 TABLES=hr.employees dump file=dp_dir:emp512.dmp CHECKSUM_ALGORITHM=SHA512
      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
      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
      Processing object type TABLE_EXPORT/TABLE/TABLE
      Processing object type TABLE_EXPORT/TABLE/COMMENT
      Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/TRIGGER
      . . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
      ORA-39173: Encrypted data has been stored unencrypted in dump file set.
      Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
      Generating checksums for dump file set
      ******************************************************************************
      Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
        /home/oracle/labs/M104786GC10/emp512.dmp
      Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Thu Feb 6 07:46:51 2020 elapsed 0 00:00:09
      $
  4. インポートする前に表を削除します。
    $ sqlplus hr@PDB20
    
    SQL*Plus: Release 20.0.0.0.0 - Production on Thu Feb 6 08:09:49 2020
    Version 20.2.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Enter password: password
    
    Connected to:
    Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
    Version 20.2.0.0.0
    
    SQL> DROP TABLE employees CASCADE CONSTRAINTS;
    
    Table dropped.
    
    SQL> EXIT
    Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
    Version 20.2.0.0.0
    $
  5. 表をインポートする前に、ダンプ・ファイルが破損しているかどうかを確認します。
    1. /home/oracle/labs/M104786GC10/corrupt.shシェル・スクリプトを実行して、ダンプ・ファイルのいずれかを破損させます。
      $ /home/oracle/labs/M104786GC10/corrupt.sh
      $
    2. 2つのダンプ・ファイルのどちらが破損しているかを確認します。
      $ impdp system@PDB20 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_ONLY=YES
      
      Import: Release 20.0.0.0.0 - Production on Thu Feb 6 07:21:37 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Verifying dump file checksums
      Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
      dump file set is complete
      verified checksum for dump file "/home/oracle/labs/M104786GC10/emp512.dmp"
      dump file set is consistent
      Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Feb 7 05:42:40 2020 elapsed 0 00:00:01
      
      $
      $ impdp system@PDB20 FULL=yes DUMPFILE=dp_dir:emp.dmp VERIFY_ONLY=YES
      
      Import: Release 20.0.0.0.0 - Production on Thu Feb 6 07:21:37 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      ORA-39001: invalid argument value
      ORA-39000: bad dump file specification
      ORA-39411: header checksum error in dump file "/home/oracle/labs/M104786GC10/emp.dmp"
      
      $ oerr ora 39411
      39411, 00000, "header checksum error in dump file \"%s\""
      // *Cause:  The header block for the Data Pump dump file contained a
      //          header checksum that did not match the value calculated from the
      //          header block as read from disk. This indicates that the header
      //          was tampered with or otherwise corrupted due to transmission or
      //          media failure.
      // *Action: Contact Oracle Support Services.
      $
  6. 表をインポートします。
    1. 破損したダンプ・ファイルを使用して表をインポートします。エクスポート・ダンプ・ファイルの完了時にチェックサムが生成された場合、インポート中にチェックサムが検証されます。
      $ impdp system@PDB20 FULL=yes DUMPFILE=dp_dir:emp.dmp
      
      Import: Release 20.0.0.0.0 - Production on Tue Mar 17 07:19:24 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      ORA-39001: invalid argument value
      ORA-39000: bad dump file specification
      ORA-39411: header checksum error in dump file "/home/oracle/labs/M104786GC10/emp.dmp"
      
      $
    2. 破損していないダンプ・ファイルを使用して表をインポートします。エクスポート・ダンプ・ファイルの完了時にチェックサムが生成された場合、パラメータVERIFY_CHECKSUMを指定していると、インポート時にチェックサムが検証されます。索引の作成に関連するエラー・メッセージは無視してください。この演習で重要なのは、表は再インポートできることです。
      $ impdp system@PDB20 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=YES
      
      Import: Release 20.0.0.0.0 - Production on Thu Feb 6 09:48:44 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Verifying dump file checksums
      Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
      Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@PDB20 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=YES
      Processing object type TABLE_EXPORT/TABLE/TABLE
      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
      . . imported "HR"."EMPLOYEES"                            17.08 KB     107 rows
      Processing object type TABLE_EXPORT/TABLE/COMMENT
      Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/TRIGGER
      Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
      Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Mar 17 07:20:29 2020 elapsed 0 00:00:20
      
      $
    3. 破損していないダンプファイルを使用して検証せずにインポートします。まず、表を削除します。
      $ sqlplus hr@pdb20
      
      SQL*Plus: Release 20.0.0.0.0 - Production on Thu Feb 6 08:09:49 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2019, Oracle.  All rights reserved.
      
      Enter password: password
      
      Connected to:
      Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Version 20.2.0.0.0
      
      SQL> DROP TABLE employees CASCADE CONSTRAINTS;
      
      Table dropped.
      
      SQL> EXIT
      Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Version 20.2.0.0.0
      $ impdp hr@PDB20 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=NO
      
      Import: Release 20.0.0.0.0 - Production on Thu Feb 6 07:21:37 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password: password
      Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
      Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Warning: dump file checksum verification is disabled
      Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
      Starting "HR"."SYS_IMPORT_FULL_01":  system/********@PDB20 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=NO
      Processing object type TABLE_EXPORT/TABLE/TABLE
      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
      . . imported "HR"."EMPLOYEES"                          17.08 KB     107 rows
      Processing object type TABLE_EXPORT/TABLE/COMMENT
      Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
      Processing object type TABLE_EXPORT/TABLE/TRIGGER
      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 Tue Mar 17 07:22:04 2020 elapsed 0 00:00:20
      $