- データベース管理者のための主要20c新機能の学習
- データベース管理者のための主要20c新機能の学習
- アップグレード、パッチ適用および移行
- Oracle Databaseユーティリティ
- Oracle Data Pumpのチェックサムによるクラウド移行のサポート
- 演習: Oracle Data Pumpダンプ・ファイルの妥当性のチェック
演習: Oracle Data Pumpダンプ・ファイルの妥当性のチェック
この演習では、チェックサムを使用して、オブジェクト・ストアとの間での転送後とオンプレミスでのダンプ・ファイルの保存後にもOracle Data Pumpダンプ・ファイルが有効であることを確認する方法を示します。チェックサムにより、誤ったまたは悪意のある変更が行われていないことが保証されます。
- 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
- 演習を開始する前に、
/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 $
- 表
HR.EMPLOYEES
をエクスポートし、ダンプ・ファイルにチェックサムを追加して、エクスポート後もダンプ・ファイルがまだ有効であり、データが元のままで破損していないことを確認できるようにします。Oracle Data Pumpのエクスポートでは、制御情報がダンプ・ファイルのヘッダー・ブロックに書き込まれます。Oracle Database 20cでは、Oracle Data Pumpおよび外部表のダンプ・ファイル内のヘッダー以外の残りのすべてのブロックについてチェックサムをさらに追加することで、データ整合性チェックが拡張されています。- エクスポート操作時に
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ビットです。 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 $
- エクスポート操作時に
- インポートする前に表を削除します。
$ 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 $
- 表をインポートする前に、ダンプ・ファイルが破損しているかどうかを確認します。
/home/oracle/labs/M104786GC10/corrupt.sh
シェル・スクリプトを実行して、ダンプ・ファイルのいずれかを破損させます。$ /home/oracle/labs/M104786GC10/corrupt.sh $
- 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. $
- 表をインポートします。
- 破損したダンプ・ファイルを使用して表をインポートします。エクスポート・ダンプ・ファイルの完了時にチェックサムが生成された場合、インポート中にチェックサムが検証されます。
$ 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" $
- 破損していないダンプ・ファイルを使用して表をインポートします。エクスポート・ダンプ・ファイルの完了時にチェックサムが生成された場合、パラメータ
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 $
- 破損していないダンプファイルを使用して検証せずにインポートします。まず、表を削除します。
$ 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 $
- 破損したダンプ・ファイルを使用して表をインポートします。エクスポート・ダンプ・ファイルの完了時にチェックサムが生成された場合、インポート中にチェックサムが検証されます。