oracle Wallet的使用（即內部加密技術TDE（Transparent Data Encryption ））
1. TDE是Oracle10gR2中推出的一個新功能，使用時要保證Oracle版本是在10gR2或者以上
--查看oracle版本：
select * from v$version;
2、創建一個新目錄，並指定為Wallet目錄
D:\oracle\product\10.2.0\admin\ora10\ora_wallet
3. 設置wallet目錄，在參數文件sqlnet.ora中（window+f,在你安裝盤區查找sqlnet.ora），按照下面的格式加入信息：
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=D:\oracle\product\10.2.0\admin\ora10\ora_wallet)))
4. 創建master key文件，指定wallet密碼，使用SYS用戶登入系統，建立加密文件
SQL> alter system set encryption key identified by "wallet";
System altered
-- 密碼"wallet"不加引號時，後面使用時也不需要用引號
此時在設置的目錄下，多出一個Personal Information Exchange類型的文件，相當於我們生成的master key文件。D:\oracle\product\10.2.0\admin\ora10\ora_wallet\ewallet.p12
5、啟動、關閉Wallet
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet";
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet"
ORA-28354: wallet 已經打開
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; --關閉
System altered
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet"; --打開
System altered
到此，已經成功配置了Wallet，創建了master key。
分類： Oracle
這裡討論的是列加密模式，即具有TDE encrypted column的表如何在源庫、目標庫之間通過expdp、impdp進行傳輸。前提是源庫和目標庫上的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 [email protected]:/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-28365因wallet 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 [email protected]:/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_ONLY在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 t15 (c1 varchar2(1) encrypt,c2 varchar2(2)) tablespace ts_info_dat_01;
insert into t15 values('C','33');
commit;
---注意這裡只能使用password模式，不能使用transparent和dual模式
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 [email protected]:/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_ONLY在dumpfile中對加密列以加密方式存儲；導入時目標庫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-28365因wallet 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_ONLY在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 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模式，不能使用transparent和dual模式，之所以報錯是因為使用password對encrypted column在導出時進行加密之前必須先用masterkey對encrypted列進行解密，對解密的結果再進行加密，而這時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=ALL在dumpfile中對所有列以加密方式存儲，又分別以encryption_mode=transparent和password兩種模式生成兩個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;
---分別使用transparent和password兩種模式進行導出
***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 [email protected]:/oradata01/hisdmp/monthly/
scp /oradata01/hisdmp/monthly/t17p.dmp [email protected]:/oradata01/hisdmp/monthly/
---目標庫wallet處於open狀態，分別導入transparent、password模式導出的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=ALL在dumpfile中對所有列以加密方式存儲，又分別以encryption_mode=transparent和password兩種模式生成兩個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=ALL在dumpfile中對所有列以加密方式存儲，又分別以encryption_mode=transparent和password兩種模式生成兩個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
---分別使用transparent和password兩種模式，前者需要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 [email protected]:/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=ALL在dumpfile中對所有列以加密方式存儲，又分別以encryption_mode=transparent和password兩種模式生成兩個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_ONLY和encryption_mode=transparent兩種情況除外，這兩種情況連表結構都不會導出，直接報錯退出）
2、目標庫執行導入操作時，需要先對dumpfile文件進行解密(如果expdp出來的時候進行了加密)，再用自己的masterkey重新對表進行加密，這兩個步驟中任意一個有問題都會引起導入失敗
3、如果安全上允許建議啟用auto login encryption wallet，數據庫重啟後會自動open
