Practice: Checking Oracle Data Pump Dump Files for Validity

Overview

This practice shows how to use a checksum to confirm that an Oracle Data Pump dump file is valid after a transfer to or from the object store, and also after saving dump files on on-premises. The checksum ensures that no accidental or malicious changes occurred.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Set up the environment

  • Execute the /home/oracle/labs/M104786GC10/DP.sh shell script. The shell script creates the HR.EMPLOYEES table to use in an export on PDB21.

    
    $ cd /home/oracle/labs/M104786GC10
    $ /home/oracle/labs/M104786GC10/DP.sh
    ...
    specify password for HR as parameter 1:
    
    specify default tablespace 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.
    ...
    ******  Creating EMPLOYEES table ....
    
    Table created.
    
    Index created.
    
    Table altered.
    
    Table altered.
    
    Sequence created.
    ...
    Commit complete.
    
    Session altered.
    ...
    ******  Populating EMPLOYEES table ....
    
    1 row created.
    ...
    
    Commit complete.
    
    Index created.
    ...
    Commit complete.
    
    Procedure created.
    
    Trigger created.
    
    Trigger altered.
    
    Procedure created.
    
    Trigger created.
    
    Commit complete.
    ...
    Directory created.
    
    Grant succeeded.
    
    $

Step 2 : Export the table using the checksum

  • Export the HR.EMPLOYEES table and add a checksum to the dump file to be able to confirm that the dump file is still valid after the export, and that the data is intact and has not been corrupted. An Oracle Data Pump export writes control information into the header block of a dump file. Oracle Database 21c extends the data integrity checks by adding an additional checksum for all the remaining blocks beyond the header within Oracle Data Pump and external table dump files. Use the CHECKSUM parameter during the export operation.

    
    $ expdp system@PDB21 TABLES=hr.employees DUMPFILE=dp_dir:emp.dmp CHECKSUM=yes REUSE_DUMPFILES=yes
    
    Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    Password:
    
    Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@PDB21 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
    $


    The checksum algorithm defaults to SHA256, a 256-bit hash algorithm.

  • If you want to use the SHA384 384-bit hash algorithm, the SHA512 512-bit hash algorithm, or the CRC32 32-bit checksum, use the CHECKSUM_ALGORITHM parameter and not the CHECKSUM parameter. The CHECKSUM parameter uses the SHA256 256-bit hash algorithm.

    
    $ expdp system@PDB21 TABLES=hr.employees DUMPFILE=dp_dir:emp384.dmp CHECKSUM_ALGORITHM=SHA384 CHECKSUM=no REUSE_DUMPFILES=yes
    
    Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    Password:
    
    ORA-39002: invalid operation
    ORA-39050: parameter CHECKSUM=NO is incompatible with parameter CHECKSUM_ALGORITHM
    
    $
    
    $ expdp system@PDB21 TABLES=hr.employees DUMPFILE=dp_dir:emp512.dmp CHECKSUM_ALGORITHM=SHA512 REUSE_DUMPFILES=yes
    
    Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    Password:
    
    Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@PDB21 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
    $

Step 3 : Import the table

  • Drop the table before importing it.

    
    $ sqlplus hr@PDB21
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    
    SQL> DROP TABLE employees CASCADE CONSTRAINTS;
    
    Table dropped.
    
    SQL> EXIT
    $
  • Before importing the table, determine whether the dump files are corrupted or not.
    • Corrupt one of the dump files by executing the /home/oracle/labs/M104786GC10/corrupt.sh shell script.

      
      $ /home/oracle/labs/M104786GC10/corrupt.sh
      $
    • Determine which of the two dump files is corrupted.

      
      $ impdp system@PDB21 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_ONLY=YES
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password:
      
      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@PDB21 FULL=yes DUMPFILE=dp_dir:emp.dmp VERIFY_ONLY=YES
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password:
      
      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.
      $
  • Import the table.

    • Import the table using the corrupted dump file. If checksums were generated when the export dump files were completed, the checksum is verified during the import.

      
      $ impdp system@PDB21 FULL=yes DUMPFILE=dp_dir:emp.dmp
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password:
      
      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"
      $
    • Import the table using the non-corrupted dump file. If checksums were generated when the export dump files were completed, the checksum is verified during the import if you include the VERIFY_CHECKSUM parameter. Ignore the error messages related to indexes creation. The point of this practice is that the table can be reimported.

      
      $ impdp system@PDB21 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=YES
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      Password:
      
      Verifying dump file checksums
      Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
      Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@PDB21 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
      $
    • Import using the non-corrupted dumpfile, avoiding the verification. Drop the table first.

      
      $ sqlplus hr@pdb21
      
      Copyright (c) 1982, 2019, Oracle.  All rights reserved.
      
      Enter password:
      
      Connected to:
      
      SQL> DROP TABLE employees CASCADE CONSTRAINTS;
      
      Table dropped.
      
      SQL> EXIT
      
      $ impdp hr@PDB21 FULL=yes DUMPFILE=dp_dir:emp512.dmp VERIFY_CHECKSUM=NO
      
      Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
      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/********@PDB21 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
      $