Inscrivez-vous à la newsletter

Inscrivez-vous à la newsletter

Abonnez-vous maintenant et nous vous tiendrons au courant.
Nous respectons votre vie privée. Vous pouvez vous désabonner à tout moment.

Nouvelles fonctionnalités datapump [Oracle 21c]

Partager sur linkedin
Partager sur twitter
Partager sur facebook

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

 

 

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.