使用datapump傳輸加密表請先確認TDE Encryption wallet狀態

dbhelper發表於2014-12-03

這裡討論的是列加密模式,即具有TDE encrypted column的表如何在源庫、目標庫之間透過expdpimpdp進行傳輸。前提是源庫和目標庫上的encryption wallet都必須處於open狀態,如果源庫或者目標庫有任何一側的wallet沒有open,都會引起匯入或者匯出操作的失敗。以下列舉了容易引起匯入匯出失敗的一些場景,幫我們更進一步的理解TDE的工作過程。

 

場景1:匯出時源庫encryption wallet處於open狀態,不對匯出的dumpfile檔案進行加密;匯入時目標庫wallet處於open狀態

 

---源庫wallet處於open狀態下進行匯出

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

create table t13 (c1 varchar2(1) encrypt,c2 varchar2(2)) tablespace ts_info_dat_01;

insert into t13 values('A','11');

commit;

 

expdp scott/abcd_1234 directory=hisdmp dumpfile=t13.dmp tables=t13 logfile=exp_t13.log reuse_dumpfiles=yes

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t13.dmp tables=t13 logfile=exp_t13.log reuse_dumpfiles=yes

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."T13"                               5.406 KB       1 rows

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oradata01/hisdmp/monthly/t13.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 16:21:16

 

scp /oradata01/hisdmp/monthly/t13.dmp oracle@10.10.141.221:/oradata01/hisdmp/monthly/

 

---目標庫wallet處於open狀態,成功匯入

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

 

impdp scott/abcd_1234 directory=hisdmp dumpfile=t13.dmp tables=t13 logfile=imp_t13.log

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t13.dmp tables=t13 logfile=imp_t13.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."T13"                               5.406 KB       1 rows

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:20:47

 

 

場景2:匯出時源庫encryption wallet處於open狀態,不對匯出的dumpfile檔案進行加密;匯入時目標庫wallet處於close狀態

---源庫wallet處於open狀態下進行匯出

步驟同場景1

--目標庫wallet處於close狀態,匯入失敗

alter system set encryption wallet close identified by "tde_1234";

 

select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

---ORA-28365wallet close所以無法建立encrypted column

impdp scott/abcd_1234 directory=hisdmp dumpfile=t13.dmp tables=t13 logfile=imp_t13.log;

 

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t13.dmp tables=t13 logfile=imp_t13.log

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"SCOTT"."T13" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "SCOTT"."T13" ("C1" VARCHAR2(1 BYTE) ENCRYPT USING 'AES192' 'SHA-1', "C2" VARCHAR2(2 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TS_

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:22:17

 

場景3:匯出時源庫encryption wallet處於close狀態,不對匯出的dumpfile檔案進行加密;匯入時目標庫wallet處於open狀態

---源庫匯出時wallet處於close狀態

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

create table t14 (c1 varchar2(1) encrypt,c2 varchar2(2)) tablespace ts_info_dat_01;

insert into t14 values('B','22');

commit;

 

alter system set encryption wallet close identified by "tde_1234";

 

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

---因為wallet處於close,所以無法對錶中加密列的資料進行解密,在接下來匯入的時候可以看到僅匯入了表結構

expdp scott/abcd_1234 directory=hisdmp dumpfile=t14.dmp tables=t14 logfile=exp_t14.log reuse_dumpfiles=yes;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t14.dmp tables=t14 logfile=exp_t14.log reuse_dumpfiles=yes

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-31693: Table data object "SCOTT"."T14" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-28365: wallet is not open

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oradata01/hisdmp/monthly/t14.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 16:31:12

 

 

scp /oradata01/hisdmp/monthly/t14.dmp oracle@10.10.141.221:/oradata01/hisdmp/monthly/

 

---目標庫匯入

alter system set encryption wallet open identified by "tde_1234";

 

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

---匯入部分成功,欄位維持加密狀態

impdp scott/abcd_1234 directory=hisdmp dumpfile=t14.dmp tables=t14 logfile=imp_t14.log

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t14.dmp tables=t14 logfile=imp_t14.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:32:50

 

select owner,table_name,column_name from dba_encrypted_columns where table_name='T14';

OWNER                          TABLE_NAME                     COLUMN_NAME

------------------------------ ------------------------------ ------------------------------

SCOTT                          T14                            C1

 

---但查詢無內容,只把表結構匯入了進來,沒有任何資料

select * from scott.t14

 

no rows selected

 

---檢查t14表的加密key並和orapki命令輸出的相比較,impdp後表encrypted column自動使用了目標庫的masterkey進行加密,證明源和目標庫上的masterkey無需保持一致

col object_name format a13

col owner format a13

set linesize 120

select obj#,mkeyid,object_name,owner from enc$,dba_objects where object_id=obj#;

      OBJ# MKEYID                                                           OBJECT_NAME   OWNER

---------- ---------------------------------------------------------------- ------------- -------------

   5553580 AZHXajyQJ08Iv5X80kXWLBQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA             T14           SCOTT

 

orapki wallet display -wallet /oradata06/wallet

Requested Certificates:

Subject:        CN=oracle

User Certificates:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.AZHXajyQJ08Iv5X80kXWLBQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BS8N9QmwrZrPOcpY6aJPnZYCAwAAAAAAAAAAAAAAAAAAAAAAAAAA

Trusted Certificates:

  

 

場景4:匯出時源庫encryption wallet處於close狀態,不對匯出的dumpfile檔案進行加密;匯入時目標庫wallet處於close狀態

---源庫匯出時wallet處於close狀態

匯出步驟同場景3,

 

---目標庫匯入

alter system set encryption wallet close identified by "tde_1234";

 

select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

drop table scott.t14;

 

***目標庫的encryption wallet close,出現ORA-28353在意料之中

impdp scott/abcd_1234 directory=hisdmp dumpfile=t14.dmp tables=t14 logfile=imp_t14.log

 

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t14.dmp tables=t14 logfile=imp_t14.log

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"SCOTT"."T14" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "SCOTT"."T14" ("C1" VARCHAR2(1 BYTE) ENCRYPT USING 'AES192' 'SHA-1', "C2" VARCHAR2(2 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TS_

Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:37:21

 

場景5:匯出時源庫encryption wallet處於open狀態,使用ENCRYPTION=ENCRYPTED_COLUMNS_ONLYdumpfile中對加密列以加密方式儲存;匯入時目標庫wallet處於open狀態

---源庫匯出,wallet處於open狀態

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

create table t15 (c1 varchar2(1) encrypt,c2 varchar2(2)) tablespace ts_info_dat_01;

insert into t15 values('C','33');

commit;

 

---注意這裡只能使用password模式,不能使用transparentdual模式

expdp scott/abcd_1234 directory=hisdmp dumpfile=t15.dmp tables=t15 logfile=exp_t15.log reuse_dumpfiles=yes encryption=ENCRYPTED_COLUMNS_ONLY encryption_password=tde_1234;

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t15.dmp tables=t15 logfile=exp_t15.log reuse_dumpfiles=yes encryption=ENCRYPTED_COLUMNS_ONLY encryption_password=********

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."T15"                               5.460 KB       1 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oradata01/hisdmp/monthly/t15.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:27:06

 

scp /oradata01/hisdmp/monthly/t15.dmp oracle@10.10.141.221:/oradata01/hisdmp/monthly/

 

---目標庫wallet處於open狀態,成功匯入

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

 

impdp scott/abcd_1234 directory=hisdmp dumpfile=t15.dmp tables=t15 logfile=imp_t15.log encryption_password=tde_1234

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t15.dmp tables=t15 logfile=imp_t15.log encryption_password=********

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."T15"                               5.460 KB       1 rows

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 17:28:58

 

場景6:匯出時源庫encryption wallet處於open狀態,使用ENCRYPTION=ENCRYPTED_COLUMNS_ONLYdumpfile中對加密列以加密方式儲存;匯入時目標庫wallet處於close狀態

---源庫匯出,wallet處於open狀態

匯出步驟同場景5

 

---關閉目標庫的encryption wallet

alter system set encryption wallet close identified by "tde_1234";

 

select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

---ORA-28365wallet close所以無法建立encrypted column

impdp scott/abcd_1234 directory=hisdmp dumpfile=t15.dmp tables=t15 logfile=imp_t15.log encryption_password=tde_1234

ORA-39002: invalid operation

ORA-39180: unable to encrypt ENCRYPTION_PASSWORD

ORA-28365: wallet is not open

 

場景7:匯出時源庫encryption wallet處於close狀態,使用ENCRYPTION=ENCRYPTED_COLUMNS_ONLYdumpfile中對加密列以加密方式儲存;因匯出即失敗所以無法繼續進行匯入

 

---源庫匯出,匯出時wallet處於close狀態

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

create table t16 (c1 varchar2(1) encrypt,c2 varchar2(2)) tablespace ts_info_dat_01;

insert into t16 values('C','33');

commit;

 

alter system set encryption wallet close identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

---注意這裡只能使用password模式,不能使用transparentdual模式,之所以報錯是因為使用passwordencrypted column在匯出時進行加密之前必須先用masterkeyencrypted列進行解密,對解密的結果再進行加密,而這時wallet close無法獲取到masterkey,所以加密過程就無法繼續

expdp scott/abcd_1234 directory=hisdmp dumpfile=t16.dmp tables=t16 logfile=exp_t16.log reuse_dumpfiles=yes encryption=ENCRYPTED_COLUMNS_ONLY encryption_password=tde_1234;

ORA-39001: invalid argument value

ORA-39180: unable to encrypt ENCRYPTION_PASSWORD

ORA-28365: wallet is not open

 

場景8:匯出時源庫encryption wallet處於open狀態,使用ENCRYPTION=ALLdumpfile中對所有列以加密方式儲存,又分別以encryption_mode=transparentpassword兩種模式生成兩個dumpfile;匯入時目標庫wallet處於open狀態,並分別對上述兩種模式下匯出的dumpfile進行匯入

 

---源庫匯出,wallet處於open狀態

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

create table t17 (c1 varchar2(1) encrypt,c2 varchar2(2)) tablespace ts_info_dat_01;

insert into t17 values('C','33');

commit;

 

---分別使用transparentpassword兩種模式進行匯出

***transparent模式

expdp scott/abcd_1234 directory=hisdmp dumpfile=t17t.dmp tables=t17 logfile=exp_t17t.log reuse_dumpfiles=yes encryption=all encryption_mode=transparent

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t17t.dmp tables=t17 logfile=exp_t17t.log reuse_dumpfiles=yes encryption=all encryption_mode=transparent

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."T17"                               5.414 KB       1 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oradata01/hisdmp/monthly/t17t.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 21:00:06

 

***password模式

expdp scott/abcd_1234 directory=hisdmp dumpfile=t17p.dmp tables=t17 logfile=exp_t17p.log reuse_dumpfiles=yes encryption=all encryption_mode=password encryption_password=tde_1234;

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t17p.dmp tables=t17 logfile=exp_t17p.log reuse_dumpfiles=yes encryption=all encryption_mode=password encryption_password=********

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."T17"                               5.414 KB       1 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oradata01/hisdmp/monthly/t17p.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 21:01:18

 

scp /oradata01/hisdmp/monthly/t17t.dmp oracle@10.10.141.221:/oradata01/hisdmp/monthly/

scp /oradata01/hisdmp/monthly/t17p.dmp oracle@10.10.141.221:/oradata01/hisdmp/monthly/

 

---目標庫wallet處於open狀態,分別匯入transparentpassword模式匯出的dmp

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

---匯入以encryption_mode=transparent方式匯出的t17t.dmp,因源、目標庫的masterkey不一致發生了ORA-28362,進一步導致ORA-39189目標庫無法解密由源庫masterkey加密的dumpfile

impdp scott/abcd_1234 directory=hisdmp dumpfile=t17t.dmp tables=t17 logfile=imp_t17t.log

ORA-39002: invalid operation

ORA-39189: unable to decrypt dump file set

ORA-28362: master key not found

 

---匯入以encryption_mode=password方式匯出的t17p.dmp,匯入成功

impdp scott/abcd_1234 directory=hisdmp dumpfile=t17p.dmp tables=t17 logfile=imp_t17p.log encryption_password=tde_1234

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t17p.dmp tables=t17 logfile=imp_t17p.log encryption_password=********

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."T17"                               5.414 KB       1 rows

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 21:03:14

 

場景9:匯出時源庫encryption wallet處於open狀態,使用ENCRYPTION=ALLdumpfile中對所有列以加密方式儲存,又分別以encryption_mode=transparentpassword兩種模式生成兩個dumpfile;匯入時目標庫wallet處於close狀態,並分別對上述兩種模式下匯出的dumpfile進行匯入

--源庫匯出,wallet處於open狀態

匯出過程同場景8

 

--關閉目標庫的encryption wallet,再次嘗試以上兩種匯入

alter system set encryption wallet close identified by "tde_1234";

 

select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

---嘗試匯入以encryption=transparent方式匯出的t17t.dmp,因目標庫wallet close無法找到解密dmpfile所需的masterkey,匯入失敗

impdp scott/abcd_1234 directory=hisdmp dumpfile=t17t.dmp tables=t17 logfile=imp_t17t.log

ORA-39002: invalid operation

ORA-39189: unable to decrypt dump file set

ORA-28365: wallet is not open

 

---嘗試匯入以encryption=password方式匯出的t17p.dmp,能夠解密出dmpfile,但是因目標庫wallet close,所以無法建立encrypted columns

impdp scott/abcd_1234 directory=hisdmp dumpfile=t17p.dmp tables=t17 logfile=imp_t17t.log encryption_password=tde_1234

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t17p.dmp tables=t17 logfile=imp_t17t.log encryption_password=********

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"SCOTT"."T17" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "SCOTT"."T17" ("C1" VARCHAR2(1 BYTE) ENCRYPT USING 'AES192' 'SHA-1', "C2" VARCHAR2(2 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TS_

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:09:10

 

場景10:匯出時源庫encryption wallet處於close狀態,使用ENCRYPTION=ALLdumpfile中對所有列以加密方式儲存,又分別以encryption_mode=transparentpassword兩種模式生成兩個dumpfile;匯入時目標庫wallet處於open狀態,並分別對上述兩種模式下匯出的dumpfile進行匯入

---源庫匯出,匯出時wallet處於close狀態

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

create table t18 (c1 varchar2(1) encrypt,c2 varchar2(2)) tablespace ts_info_dat_01;

insert into t18 values('C','33');

commit;

 

alter system set encryption wallet close identified by "tde_1234";

select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

---分別使用transparentpassword兩種模式,前者需要masterkey加密dmpfile,後者需要先用masterkey解密encrypted columns後再用password加密,兩者都需要wallet open,但實際wallet處於close狀態,所以這兩種匯出都有問題

 

--transparent模式匯出失敗

expdp scott/abcd_1234 directory=hisdmp dumpfile=t18t.dmp tables=t18 logfile=exp_t18t.log reuse_dumpfiles=yes encryption=all encryption_mode=transparent

ORA-39002: invalid operation

ORA-39188: unable to encrypt dump file set

ORA-28365: wallet is not open

 

--password模式匯出,僅匯出了表結構,因為無法使用masterkey Decrypt加密列

expdp scott/abcd_1234 directory=hisdmp dumpfile=t18p.dmp tables=t18 logfile=exp_t18p.log reuse_dumpfiles=yes encryption=all encryption_mode=password encryption_password=tde_1234;

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t18p.dmp tables=t18 logfile=exp_t18p.log reuse_dumpfiles=yes encryption=all encryption_mode=password encryption_password=********

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-31693: Table data object "SCOTT"."T18" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-28365: wallet is not open

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oradata01/hisdmp/monthly/t18p.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 21:17:11

 

scp /oradata01/hisdmp/monthly/t18p.dmp oracle@10.10.141.221:/oradata01/hisdmp/monthly/

 

---目標庫wallet處於open狀態,匯入encryption_mode=transparent方式匯出的t18p.dmp

因該方式下匯出dmpfile失敗,所以略去

 

---目標庫wallet處於open狀態,匯入encryption_mode=password方式匯出的t18p.dmp

alter system set encryption wallet open identified by "tde_1234";

select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

OPEN

 

impdp scott/abcd_1234 directory=hisdmp dumpfile=t18p.dmp tables=t18 logfile=imp_t18p.log encryption_password=tde_1234

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t18p.dmp tables=t18 logfile=imp_t18p.log encryption_password=********

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 21:22:05

 

***檢查t18表無內容,僅有表結構,相當於expdp時指定了encryption=metadata_only

SQL> select * from scott.t18;

 

no rows selected

 

場景11:匯出時源庫encryption wallet處於close狀態,使用ENCRYPTION=ALLdumpfile中對所有列以加密方式儲存,又分別以encryption_mode=transparentpassword兩種模式生成兩個dumpfile;匯入時目標庫wallet處於close狀態,並分別對上述兩種模式下匯出的dumpfile進行匯入

---源庫匯出,匯出時wallet處於close狀態

匯出步驟同場景10

 

--關閉目標庫的encryption wallet,再次嘗試以上匯入

alter system set encryption wallet close identified by "tde_1234";

 

select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oradata06/wallet

CLOSED

 

---嘗試匯入encryption_mode=transparent方式匯出的t18p.dmp

因該方式下匯出dmpfile失敗,所以略去

 

---嘗試匯入以encryption=password方式匯出的t18p.dmp,因目標庫wallet close,無法建立encrypted columns,匯入失敗

impdp scott/abcd_1234 directory=hisdmp dumpfile=t18p.dmp tables=t18 logfile=imp_t18t.log encryption_password=tde_1234

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=hisdmp dumpfile=t18p.dmp tables=t18 logfile=imp_t18t.log encryption_password=********

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"SCOTT"."T18" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "SCOTT"."T18" ("C1" VARCHAR2(1 BYTE) ENCRYPT USING 'AES192' 'SHA-1', "C2" VARCHAR2(2 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TS_

Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:24:04

 

針對實驗場景的結果歸納如下:

源庫expdp時的encryption wallet狀態

能否正常匯出加密表

目標庫impdp 時的encryption wallet狀態

Expdp引數Encryption_mode取值

Expdp引數Encryption取值

匯入結果

Open

Open

正常

Open

Close

失敗

Close

Open

僅表結構

Close

Close

失敗

Open

Open

password

ENCRYPTED_COLUMNS_ONLY

正常

Open

Close

password

ENCRYPTED_COLUMNS_ONLY

失敗

Close

-

password

ENCRYPTED_COLUMNS_ONLY

-

Open

Open

transparent

ALL

失敗

Open

Open

password

ALL

正常

Open

Close

transparent

ALL

失敗

Open

Close

password

ALL

失敗

Close

-

transparent

ALL

-

Close

open

password

All

僅表結構

Close

Close

password

All

失敗

 

總結:

含有加密列的表進行匯出、匯入時:

1、  源庫上執行匯出操作時encryption wallet只有處於open狀態才能匯出完整的內容,如果是close的情況下一般會把表結構匯出(但encryption= ENCRYPTED_COLUMNS_ONLYencryption_mode=transparent兩種情況除外,這兩種情況連表結構都不會匯出,直接報錯退出)

 

2、目標庫執行匯入操作時,需要先對dumpfile檔案進行解密(如果expdp出來的時候進行了加密),再用自己的masterkey重新對錶進行加密,這兩個步驟中任意一個有問題都會引起匯入失敗

 

3、如果安全上允許建議啟用auto login encryption wallet,資料庫重啟後會自動open

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1354982/,如需轉載,請註明出處,否則將追究法律責任。

相關文章