模拟wallet加密文件恢复(tde)

环境:
OS:Centos 7
DB:11.2.0.4

1.备份加密文件
拷贝到另外的目录或是重命名
[oracle@ora11g wallet]$ mv ewallet.p12 bak_ewallet.p12

 

2.这个时候尝试关闭或是打开
SQL> alter system set wallet close identified by "123456";

System altered.

SQL> alter system set wallet open identified by "123456";
alter system set wallet open identified by "123456"
*
ERROR at line 1:
ORA-28367: wallet does not exist

 

3.重建wallet
SQL> alter system set encryption key authenticated by "789000";
alter system set encryption key authenticated by "789000"
*
ERROR at line 1:
ORA-28362: master key not found

这个时候会生成ewallet.p12文件
[oracle@ora11g wallet]$ ls -al
total 8
drwxr-xr-x 2 oracle oinstall 48 Oct 23 07:33 .
drwxr-xr-x. 10 oracle oinstall 139 Oct 23 07:19 ..
-rw-r--r-- 1 oracle oinstall 2845 Oct 23 07:21 bak_ewallet.p12
-rw-r--r-- 1 oracle oinstall 2581 Oct 23 07:33 ewallet.p12

 

4.尝试使用新的wallet
SQL> alter system set wallet close identified by "789000";

System altered.

SQL> alter system set wallet open identified by "789000";

查看旧的wallet创建的表
SQL> select * from hxl.en_test;
select * from hxl.en_test
*
ERROR at line 1:
ORA-28362: master key not found

这个时候是无法访问了的.

 

5.恢复
5.1 关闭新的wallet
SQL> alter system set wallet close identified by "789000";

System altered.

 

5.2 恢复备份的wallet
[oracle@ora11g wallet]$ mv bak_ewallet.p12 ewallet.p12

 

5.3 使用旧的wallet打开
SQL> alter system set wallet open identified by "123456";

System altered.

这样就可以查看旧wallet加密的表数据了
SQL> select * from hxl.en_test;

ID NAME SALARY
---------- -------------------- ----------
1 hwb 50000

 

5.4 查看MASTERKEY是否一致

[oracle@ora11g wallet]$ mkstore -wrl /u01/app/oracle/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:         

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AY9BaFi0qU9Bvys187OmwCwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> select  utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64  FROM (select RAWTOHEX(mkid) mkeyid from x$kcbdbk);

MASTERKEYID_BASE64
--------------------------------------------------------------------------------
AY9BaFi0qU9Bvys187OmwCw=

 

 

6.可以继续创建密码表空间
CREATE TABLESPACE tps_sec01
DATAFILE '/u01/app/oracle/oradata/ora11g/tps_sec01_01.dbf'
SIZE 100M
ENCRYPTION
DEFAULT STORAGE (ENCRYPT);

 

或是创建加密的表
connect hxl/oracle
create table hxl.en_test01 (id int,name varchar(20),salary number(10,2) encrypt using 'AES256');
insert into hxl.en_test01 values(1,'hwb',50000.00);
insert into hxl.en_test01 values(2,'hwb2',30000.00);
commit;