Dans cet article, nous verrons quelques nouveautés apportés à Datapump avec la sortie de la version 21c d'Oracle. A titre personnel, je ne considère pas cette version de "majeure" et vous conseille de l'utiliser uniquement si des nouveautés vous intéressent. De manière générale la version Oracle 19c est encore à prioriser.

CHECKSUM (expdp)

Cette option permet de confirmer que les fichiers dump sont valides à la fin de l'export et ainsi confirmer que le dump n'a pas été corrompu. Avec cette option, de nouvelles informations sur les blocks sont ajoutées dans le header des fichiers dumps.

[oracle]# export ORACLE_PDB_SID=PDB1
[oracle]# expdp "/ as sysdba" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_PDB1.dmp CHECKSUM=Y FULL=Y

Export: Release 21.0.0.0.0 - Production on Thu Sep 2 15:58:23 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_PDB1.dmp CHECKSUM=Y FULL=Y [...] Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded Generating checksums for dump file set ****************************************************************************** Dump file set for SYS.SYS_EXPORT_FULL_01 is: /u02/oracle/export/expdp_full_21c_PDB1.dmp Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Sep 2 16:01:29 2021 elapsed 0 00:03:03 L'algorithme par défaut via cette méthode est le SHA256 (256 bits). Il est possible d'effectuer un checksum avec un autre algorithme en utilisant l'option CHECKSUM_ALGORITHM.

CHECKSUM_ALGORITHM (expdp)

En utilisant cette option il est possible d'outrepasser l'algorithme de base et d'effectuer un checksum en :

• CRC32 (32-bit)
• SHA256 (256-bit)
• SHA384 (384-bit)
• SHA512 (512-bit)

[oracle]# export ORACLE_PDB_SID=PDB1
[oracle]# expdp "/ as sysdba" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_algo.dmp CHECKSUM_ALGORITHM=CRC32 FULL=Y

Export: Release 21.0.0.0.0 - Production on Fri Sep 3 12:35:25 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_algo.dmp CHECKSUM_ALGORITHM=CRC32 FULL=Y [...] Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded Generating checksums for dump file set ****************************************************************************** Dump file set for SYS.SYS_EXPORT_FULL_01 is: /u02/oracle/export/expdp_full_21c_algo.dmp Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Fri Sep 3 12:37:35 2021 elapsed 0 00:02:08

VERIFY_ONLY (impdp)

Avec cette option, lors d'un import (impdp), il est possible de vérifier uniquement si le dump n'est pas corrompu.

[oracle]# export ORACLE_PDB_SID=PDB1
[oracle]# impdp "/ as sysdba" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_PDB1.dmp VERIFY_ONLY=Y

Import: Release 21.0.0.0.0 - Production on Wed Sep 8 12:20:04 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production Verifying dump file checksums Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded dump file set is complete verified checksum for dump file "/u02/oracle/export/expdp_full_21c_PDB1.dmp" dump file set is consistent Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed Sep 8 12:20:11 2021 elapsed 0 00:00:03 Et si jamais le dump est corrompu :

[oracle]# export ORACLE_PDB_SID=PDB1
[oracle]# impdp "/ as sysdba" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_algo.dmp VERIFY_ONLY=Y

Import: Release 21.0.0.0.0 - Production on Wed Sep 8 12:24:11 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39411: header checksum error in dump file "/u02/oracle/export/expdp_full_21c_algo.dmp"

VERIFY_CHECKSUM (impdp)

Cette option permet de vérifier l'intégrité du dump et si OK d'effectuer l'import dans la foulée. Si corrompu (comme le VERIFY ONLY) :

[oracle]# export ORACLE_PDB_SID=PDB1
[oracle]# impdp "/ as sysdba" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_algo.dmp VERIFY_CHECKSUM=Y

Import: Release 21.0.0.0.0 - Production on Wed Sep 8 12:51:37 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39411: header checksum error in dump file "/u02/oracle/export/expdp_full_21c_algo.dmp" Et si pas de soucis sur le dump, il effectue une vérification puis importe :

[oracle]# export ORACLE_PDB_SID=PDB1
[oracle]# impdp "/ as sysdba" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_PDB1_EXPDP_21C.dmp VERIFY_CHECKSUM=Y

Import: Release 21.0.0.0.0 - Production on Wed Sep 8 13:25:04 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production Verifying dump file checksums Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_PDB1_EXPDP_21C.dmp VERIFY_CHECKSUM=Y Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "EXPDP_21C"."EASYTEAM" 82.99 KB 10000 rows Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed Sep 8 13:25:27 2021 elapsed 0 00:00:21

EXCLUDE + INCLUDE (expdp)

Dans les anciennes versions, il n'était pas possible d'utiliser simultanément l'option INCLUDE avec l'option EXCLUDE. On rencontrait une erreur de type :

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-39071: Value for EXCLUDE is badly formed.

Alors que maintenant, c'est faisable :

[oracle]# export ORACLE_PDB_SID=PDB1
[oracle]# expdp "/ as sysdba" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_PDB1_EXC_INC.dmp CHECKSUM=Y INCLUDE=TABLE EXCLUDE=STATISTICS,INDEX SCHEMAS=EXPDP_21C

Export: Release 21.0.0.0.0 - Production on Wed Sep 8 13:32:30 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" DIRECTORY=EXPDP_21C DUMPFILE=expdp_full_21c_PDB1_EXC_INC.dmp CHECKSUM=Y INCLUDE=TABLE EXCLUDE=STATISTICS,INDEX SCHEMAS=EXPDP_21C Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/TABLE . . exported "EXPDP_21C"."EASYTEAM" 82.99 KB 10000 rows Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded Generating checksums for dump file set ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is: /u02/oracle/export/expdp_full_21c_PDB1_EXC_INC.dmp Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Wed Sep 8 13:32:42 2021 elapsed 0 00:00:10