这里讨论的是列加密模式，即具有 TDE encrypted column 的表如何在源库、目标库之间通过 expdp 、 impdp 进行传输。前提是源库和目标库上的 encryption wallet 都必须处于 open 状态，如果源库或者目标库有任何一侧的 wallet 没有 open ，都会引起导入或者导
这里讨论的是列加密模式，即具有 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 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-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 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_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 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_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 oracle@10.10.141.221:/oradata01/hisdmp/monthly/
scp /oradata01/hisdmp/monthly/t17p.dmp oracle@10.10.141.221:/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 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=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