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
