Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use expdp to encrypt the dumpfile containing a encrypted transportable tablespace

User337601Sep 28 2014 — edited Oct 4 2014

As we know we can use these values of encryption parameter in expdp to ensure that the content of the dumpfile generated by expdp is encrypted

ENCRYPTED_COLUMNS_ONLY : specifies that only encrypted columns are written to the dump file set in encrypted format

ALL : enables encryption for all data and metadata in the export operation

METADATA_ONLY  : specifies that only metadata is  written to the dump file set in encrypted format

DATA_ONLY : specifies that only data is written to  the dump file set in encrypted format.

But how these values of encryption parameter works about  dumpfile of  encrypted transportable tablespace ? I tried several commands the test it

--first create the encrypted tablespace and create a table in it

create tablespace ttstbs1 datafile '/oradata06/ttstbs1.dbf' size 64m encryption using 'AES128' default storage(encrypt);

create table scott.ttstab1 tablespace ttstbs1 as select * from all_users;

alter tablespace ttstbs1 read only;

--commands that will receive ORA-39005 and it is expected  since dmpfile of transportable tablespace contents only metadata

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstab1.log dumpfile=ttstab1.dmp reuse_dumpfiles=yes transport_tablespaces=ttstbs1 encryption=all 

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstab1.log dumpfile=ttstab1.dmp reuse_dumpfiles=yes transport_tablespaces=ttstbs1 encryption_password=abcd_1234

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstab1.log dumpfile=ttstab1.dmp reuse_dumpfiles=yes transport_tablespaces=ttstbs1 encryption=data_only encryption_mode=transparent

--some commands i think that should encrypt the dumpfile

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstabt.log dumpfile=ttstabt.dmp reuse_dumpfiles=yes transport_tablespaces=ttstbs1 encryption_mode=transparent

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstabmp.log dumpfile=ttstabtmp.dmp reuse_dumpfiles=yes transport_tablespaces=ttstbs1 encryption=metadata_only encryption_mode=password;

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstab1.log dumpfile=ttstab1.dmp reuse_dumpfiles=yes transport_tablespaces=ttstbs1 encryption=metadata_only encryption_mode=transparent ENCRYPTION_ALGORITHM=AES256

--some commands i think that should not encrypt the dumpfile

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstabnt.log dumpfile=ttstabnt.dmp reuse_dumpfiles=yes transport_tablespaces=ttstbs1 encryption=none

expdp scott/abcd_1234@shzwbcv2 directory=hisdmp logfile=exp_ttstab_def.log reuse_dumpfiles=yes dumpfile=ttstab_def.dmp transport_tablespaces=ttstbs1

But the generated dumpfile are all the same size 94208 bytes. So i suspect whether they are encrypted or non-encrypted like what i expected.

-rw-r-----    1 oracle   oinstall      94208 Sep 28 13:47 ttstabt.dmp

-rw-r-----    1 oracle   oinstall      94208 Sep 28 13:47 ttstab_def.dmp

-rw-r-----    1 oracle   oinstall      94208 Sep 28 14:12 ttstab1.dmp

-rw-r-----    1 oracle   oinstall      94208 Sep 28 14:25 ttstabtmp.dmp

-rw-r-----    1 oracle   oinstall      94208 Sep 28 14:28 ttstabnt.dmp

My question is how to verify whether these dumpfile is in encrypted or decrypted format ?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 27 2014
Added on Sep 28 2014
1 comment
1,274 views