当前位置:Gxlcms > 数据库问题 > 转://oracle Wallet在expdp/impdp中使用场景

转://oracle Wallet在expdp/impdp中使用场景

时间:2021-07-01 10:21:17 帮助过:8人阅读

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。



使用datapump传输加密表请先确认TDE Encryption wallet状态 2014-10-07 17:30:02

分类: 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 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

转://oracle Wallet在expdp/impdp中使用场景

标签:start   system   error:   导入导出   lsp   test   key   sha   database   

人气教程排行