1. データベース管理者のための主要20c新機能の学習
  2. データベース管理者のための主要20c新機能の学習
  3. アップグレード、パッチ適用および移行
  4. Oracle Databaseユーティリティ
  5. Oracle Data Pumpのチェックサムによるクラウド移行のサポート
  6. 演習: Oracle Data Pumpダンプ・ファイルの妥当性のチェック

演習: 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
$

親トピック: Oracle Data Pumpのチェックサムによるクラウド移行のサポート