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.
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@as sysdba\" schemas=PERF directory=EXP_DIR dumpfile=PERF_ENPP.dmp logfile=PERF_ENPP.log encryption_password=test CLUSTER=N
Import: Release 12.1.0.2.0 - Production on :29:44 2020
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
No comments:
Post a Comment