How to use TDE Encryption for Database Export in Oracle

November 21, 2019

You can use Oracle encryption password to encrypt export dumpfile. You can use TDE encryption feature for full database export using expdp impdp utility. For this oracle database must be configured with wallet for encryption. There are different modes of encryption i., dual, transparent and password.

ENCRYPTION_MPDE= DUAL | TRANSPARENT | PASSWORD

ENCRYPTION_ALGORITHM parameter specifies the encryption algorithm to be used during the export, with the default being “AES128” other value includes AES128, AES192 and AES256.

Encryption parameters: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

  • ALL: Both metadata and data will be encrypted.
  • DATA_ONLY: Only data is encrypted.
  • METADATA_ONLY: Only metadata is encrypted.
  • ENCRYPTED_COLUMNS_ONLY: Only encrypted columns are written to the dump file in an encrypted format.
  • NONE: Nothing is encrypted.
How to Speed Up EXPDP/IMPDP Data Pump Jobs Performance in Oracle


ENCRYPTION_MODE

DUAL mode creates a dump file set that can later be imported either transparently or by specifying a password that was used when the dual-mode encrypted dump file set was created. When you later import the dump file set created in DUAL mode, you can use either the wallet or the password that was specified with the ENCRYPTION_PASSWORD parameter. DUAL mode is best suited for cases in which the dump file set will be imported on-site using the wallet, but which may also need to be imported offsite where the wallet is not available.

PASSWORD mode: In this mode password is required at the time of export to encrypt the dumpfile. You will need to provide the same password when you import the dump file set. PASSWORD mode requires that you also specify the ENCRYPTION_PASSWORD parameter. The PASSWORD mode is best suited for cases in which the dump file set will be imported into a different or remote database, but which must remain secure in transit.

TRANSPARENT mode: This mode allows an encrypted dump file set to be created without any intervention from a database administrator (DBA), provided the required wallet is available. Therefore, the ENCRYPTION_PASSWORD parameter is not required, and will in fact, cause an error if it is used in TRANSPARENT mode. This encryption mode is best suited for cases in which the dump file set will be imported into the same database from which it was exported.

Restrictions

  • To use DUAL or TRANSPARENT mode, the COMPATIBLE initialization parameter must be set to at least 11.0.0.
  • When you use the ENCRYPTION_MODE parameter, you must also use either the ENCRYPTION or ENCRYPTION_PASSWORD parameter. Otherwise, an error is returned.
  • When you use the ENCRYPTION=ENCRYPTED_COLUMNS_ONLY, you cannot use the ENCRYPTION_MODE parameter. Otherwise, an error is returned.
  • This parameter is valid only in the Enterprise Edition of Oracle Database 11g or later.

Example:

Schema export using expdp and encryption_password :

 Check wallet status, it should be open on both source and destination.

SQL> select WRL_TYPE , WRL_PARAMETER, STATUS from v$encryption_wallet;
Expdp Parfile contents:
  directory=EXP_DIR 
schemas=QAPERF  
dumpfile=QAPERF_ENPP.dmp 
logfile=QAPERF_ENPP.log 
ENCRYPTION=all 
ENCRYPTION_ALGORITHM=AES128 
encryption_mode=password 
encryption_password=test
SQLPLUS> create or replace directory  EXP_DIR  as '/u01/dumps';
SQLPLUS> grant read, write on directory to username;
nohup expdp \"sys/pass@CTQA as sysdba\" schemas=QAPERF directory=EXP_DIR dumpfile= QAPERF_ENPP.dmp logfile= QAPERF_ENPP.log ENCRYPTION=all ENCRYPTION_ALGORITHM=AES128 encryption_mode=password encryption_password=test &

Schema import using impdp and encryption_password:

Don’t use CLUSTER=N for standalone database. We are using this parameter for RAC database.

[oracle@orahow EXP_DIR]$ impdp \"sys/pass@CTQA as sysdba\" schemas=QAPERF directory=EXP_DIR dumpfile=QAPERF_ENPP.dmp logfile=QAPERF_ENPP.log encryption_password=test CLUSTER=N
 Import: Release 12.1.0.2.0 - Production on Sun Nov 17 23:29:44 2019
 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Advanced Analytics and Real Application Testing options
 Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
 import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
 export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
 WARNING: possible data loss in character set conversions
 Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "sys/@CTQA AS SYSDBA" schemas=QAPERF directory=EXP_DIR dumpfile=QAPERF_ENPP.dmp logfile=QAPERF_ENPP.log encryption_password= CLUSTER=N
 Processing object type SCHEMA_EXPORT/USER
 ORA-31684: Object type USER:"QAPERF" already exists
 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
 Processing object type SCHEMA_EXPORT/TABLE/TABLE
 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
 . . imported "QAPERF"."SQL_WORK_EFFORT_DATA"  18.68 GB   40461 rows
 . . imported "QAPERF"."SQL_TIME_SERIES"       1.911 GB   40461 rows
 . . imported "QAPERF"."SQL_MON_SCHEDULE_DATA"  919.3 MB   40461 rows
 . . imported "QAPERF"."SQL_MISC_COST_DIST_DATA"  891.2 MB   40461 rows
 . . imported "QAPERF"."RFP_BID"                6.914 MB      10 rows
 . . imported "QAPERF"."SQL_MISC_COST"         490.5 MB 6237574 rows
 . . imported "QAPERF"."SQL_SUBJ_ENROLL_PERIOD"  521.6 MB 15177852 rows
 . . imported "QAPERF"."PTR_LOE_ALG_DATA"       464.4 MB   40461 rows
 . . imported "QAPERF"."SQL_INFLATION_RATE"    314.5 MB 8800184 rows
 . . imported "QAPERF"."SQL_MISC_COST_DEF"     249.3 MB 2020434 rows
 . . imported "QAPERF"."SQL_TASK_GROUP"        270.3 MB 6354020 rows
 . . imported "QAPERF"."PTR_OVERRIDE_DATA"      132.0 MB   40461 rows
 . . imported "QAPERF"."SQL_EXCHANGE_RATE"     232.8 MB 5634408 rows
 . . imported "QAPERF"."MAPRED_SQL_LABOR_DATA"  126.7 MB      70 rows
 . . imported "QAPERF"."MAPRED_PORT_LABOR_DATA"  124.6 MB      14 rows
 . . imported "QAPERF"."SQL_MTG_ATTENDEE"      162.1 MB 2200130 rows
 . . imported "QAPERF"."SQL_SITE_APPROVAL_PERIOD"  159.3 MB 5337250 rows
 . . imported "QAPERF"."SQL_INFLATION_DATA"    109.1 MB   98291 rows
 . . imported "QAPERF"."ACTIVE_STUDIES"         153.2 MB 1420621 rows
 . . imported "QAPERF"."SQL_CUSTOM_ASSUMPTION"  133.8 MB 2533954 rows
 . . imported "QAPERF"."RATE_DATA"              108.4 MB   95260 rows
 . . imported "QAPERF"."TASK_RESOURCE"          115.8 MB 1904139 rows
 . . imported "QAPERF"."TASK"                   96.27 MB  670241 rows
 . . imported "QAPERF"."SQL_MILESTONE"         66.70 MB 2619666 rows
 . . imported "QAPERF"."ACTIVE_SQLS"           63.82 MB  493200 rows
 . . imported "QAPERF"."SQL_MONITORING_PERIOD"  67.64 MB 1241516 rows
 . . imported "QAPERF"."SQL_TREATMENT_SCHEDULE_DATA"  51.30 MB   40416 rows
 . . imported "QAPERF"."SQL_MAJOR_TASK_DIST_DATA"  47.60 MB   40461 rows

Must Read: