一 文档说明
Oracle 数据库使用身份验证、授权和审核机制来保护数据库中的数据，但没有能够保护存储在操作系统上的数据文件。为了保护这些数据文件，Oracle数据库提供透明数据加密（TDE）。该文档主要说明 oracle 高级安全组件中的透明数据加密 (TDE) 部分。
二 钱夹的部署
2.1 指定钱夹存放位置
在目录 $ORACLE_HOME/network/admin 下找到文件 sqlnet.ora ，添加如下内容：
# Oracle Advanced Security Transparent Data Encryption
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY= /u01/app/oracle/product/11.2/network/admin/encryption_wallet)))
注意：如果是 RAC 环境， oracle 建议将钱夹位置放置在共享文件系统上，以便各个节点共享访问。
2.2 创建目录
$cd /u01/app/oracle/product/11.2/network/admin/
$mkdir encryption_wallet
2.3 创建主加密键
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS
---------- ---------------------------- ------------------
file /u01/app/oracle/product/11.2/network/admin/encryption_wallet
CLOSED
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "test";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS
---------- ---------------------------- ------------------
file /u01/app/oracle/product/11.2/network/admin/encryption_wallet
OPEN
2.4 打开和关闭钱夹
第一次设置万能密钥会自动打开钱夹，每次数据库被关闭，钱夹也关闭。在加密或解密之前必须确保钱夹被打开。可以配置自动登录打开（可选）。
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "test";
（注：mount状态下就可以开启钱夹）
关闭钱夹： ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "test";
2.5 加密列和表空间具体过程
a. 加密表中一行测试
(1) 新建表透明加密
SQL> conn test/test;
Connected.
SQL> create table tde (id number(10),data varchar2(50) encrypt);
Table created.
SQL> insert into tde select user_id,username from dba_users;
10 rows created.
SQL> select * from tde;
ID DATA
---------- --------------------------------------------------
34 JSS
35 TEST
0 SYS
5 SYSTEM
31 APPQOSSYS
9 OUTLN
14 DIP
30 DBSNMP
32 WMSYS
21 ORACLE_OCM
10 rows selected.
SQL> conn / as sysdba;
Connected.
SQL> select * from test.tde;
ID DATA
---------- --------------------------------------------------
34 JSS
35 TEST
0 SYS
5 SYSTEM
31 APPQOSSYS
9 OUTLN
14 DIP
30 DBSNMP
32 WMSYS
21 ORACLE_OCM
10 rows selected.
SQL> select * from dba_encrypted_columns;
OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL
------------------------------ ----------------------------- --- ------------
TEST TDE
DATA AES 192 bits key YES SHA-1
打开钱夹才能查询：
SQL> alter system set encryption wallet close identified by "hzmcdba";
System altered.
SQL> select * from test.tde;
select * from test.tde
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet open identified by "hzmcdba";
System altered.
SQL> select * from test.tde;
ID DATA
---------- --------------------------------------------------
34 JSS
35 TEST
0 SYS
5 SYSTEM
31 APPQOSSYS
9 OUTLN
14 DIP
30 DBSNMP
32 WMSYS
21 ORACLE_OCM
10 rows selected.
(2) 已有表透明加密
SQL> conn test/test;
Connected.
SQL> create table existing_table(id number(10),data varchar2(50));
Table created.
SQL> insert into existing_table select user_id,username from dba_users;
10 rows created.
SQL> select * from existing_table;
ID DATA
---------- --------------------------------------------------
34 JSS
35 TEST
0 SYS
5 SYSTEM
31 APPQOSSYS
9 OUTLN
14 DIP
30 DBSNMP
32 WMSYS
21 ORACLE_OCM
10 rows selected.
SQL> alter table existing_table modify(data encrypt);
Table altered.
b. 加密整体表空间
创建加密表空间
SQL> conn test/test;
Connected.
SQL> create tablespace encryptedtbs datafile '/oracle/app/orcl/secure01.dbf' size 5m encryption default storage(encrypt);
Tablespace created.
(1) 已有表移动至加密表空间
（ SQL> create table no_to_yes(id number(10),data varchar2(50));
Table created.
SQL> insert into no_to_yes select user_id,username from dba_users;
10 rows created.
SQL> alter system set encryption wallet close identified by "hzmcdba";
System altered.
SQL> select * from no_to_yes;
ID DATA
---------- --------------------------------------------------
34 JSS
35 TEST
0 SYS
5 SYSTEM
31 APPQOSSYS
9 OUTLN
14 DIP
30 DBSNMP
32 WMSYS
21 ORACLE_OCM
10 rows selected.
）
SQL> alter table no_to_yes move tablespace encryptedtbs;
Table altered.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TDE USERS
EXISTING_TABLE USERS
NO_TO_YES ENCRYPTEDTBS
SQL> alter system set encryption wallet close identified by "hzmcdba";
System altered.
SQL> select * from no_to_yes;
select * from no_to_yes
*
ERROR at line 1:
ORA-28365: wallet is not open
(2) 在加密表空间中新建表测试
SQL> show user;
USER is "TEST"
SQL> create table encrypted_table (id number,name varchar(10)) tablespace encryptedtbs;
Table created.
SQL> insert into encrypted_table values (1,'hzmcdba');
1 row created.
SQL> select * from encrypted_table;
ID NAME
---------- ----------
1 hzmcdba
SQL> alter system set encryption wallet close identified by "hzmcdba";
System altered.
SQL> select * from encrypted_table;
select * from encrypted_table
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet open identified by "hzmcdba";
System altered.
SQL> select * from encrypted_table;
ID NAME
---------- ----------
1 hzmcdba
三 RAC中配置wallet钱夹
wallet 路径
Oracle 11gR2 中 RAC 节点能够共享钱包。 Oracle 建议在共享文件系统上创建钱包，这样允许所有实例访问相同的共享钱包，无需手动复制和同步所有节点上的钱包。
Oracle RAC 中一个实例对钱包进行操作（如打开或关闭钱包），它会为 Oracle RAC 中所有实例打开或关闭。
使用共享文件系统时，需要确保所有 Oracle RAC 实例的 ENCRYPTION_WALLET_LOCATION 或 WALLET_LOCATION 参数指向相同的共享钱包位置。安全管理员还需要通过分配相应的目录权限来确保共享钱包的安全性。
钱包的创建部署测试如下：
测试结果：指定钱夹存放目录在共享磁盘中时，创建钱夹失败！
指定钱夹存放目录在本地时，钱夹可创建成功！
1. 指定钱包存放目录在共享磁盘中
[oracle@rac1 admin]$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY= +data/orcl/wallet)))
[oracle@rac1 admin]$ scp sqlnet.ora oracle@rac2:/oracle/app/product/11.2.0/db_1/network/admin/
sqlnet.ora 100% 94 0.1KB/s 00:00
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
+data/orcl/wallet
CLOSED
SQL> alter system set encryption key identified by "test";
alter system set encryption key identified by "test"
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet
说明：不能够在 +data/orcl/wallet 目录下创建文件 ewallet.p12 （ 往下看第 3 小点 ）
2. 本地钱包配置及测试过程：
1 节点：
[oracle@rac1 admin]$ vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=( DIRECTORY=$ORACLE_HOME)))
[oracle@rac1 admin]$ scp sqlnet.ora oracle@rac2:/oracle/app/product/11.2.0/db_1/network/admin/
sqlnet.ora
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
$ORACLE_HOME
CLOSED
SQL> alter system set encryption key identified by "test";
System altered.
把钱夹拷贝到二节点：
[oracle@rac1 db_1]$ scp ewallet.p12 oracle@rac2:/oracle/app/product/11.2.0/db_1/
ewallet.p12 100% 2845 2.8KB/s 00:00
2 节点查询：
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
$ORACLE_HOME
OPEN
1 节点建个加密表：
SQL> create user test identified by "test";
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> conn test/test;
Connected.
SQL> create table tde (id number(10),data varchar2(50) encrypt);
Table created.
SQL> insert into tde select user_id,username from dba_users;
10 rows created.
SQL> select * from tde;
ID DATA
---------- --------------------------------------------------
0 SYS
5 SYSTEM
34 ORACLE
35 TEST
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM
10 rows selected.
SQL> commit;
Commit complete.
2 节点进行查询：
SQL> conn test/test;
Connected.
SQL> select * from tde;
ID DATA
---------- --------------------------------------------------
0 SYS
5 SYSTEM
34 ORACLE
35 TEST
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM
10 rows selected.
（数据可以查到！）
钱夹部署成功！
1 节点：
SQL> alter system set encryption wallet close identified by "test";
System altered.
SQL> select * from tde;
select * from tde
*
ERROR at line 1:
ORA-28365: wallet is not open
2 节点查询：
SQL> select * from tde;
select * from tde
*
ERROR at line 1:
ORA-28365: wallet is not open
2 节点打开钱包：
SQL> alter system set encryption wallet open identified by "test";
System altered.
SQL> select * from tde;
ID DATA
---------- --------------------------------------------------
0 SYS
5 SYSTEM
34 ORACLE
35 TEST
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM
10 rows selected.
1 节点查询：
SQL> select * from tde;
ID DATA
---------- --------------------------------------------------
0 SYS
5 SYSTEM
34 ORACLE
35 TEST
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM
10 rows selected.
3. 第 1 小点中，我们指定钱夹位置在共享磁盘上，然后创建钱夹，创建钱夹失败，在这直接把钱夹拷贝到共享磁盘上，看能否成功！
把钱包拷贝到共享磁盘上：
ASMCMD> cp /oracle/app/product/11.2.0/db_1/ewallet.p12.bak +data/orcl/wallet/ewallet.p12.bak
ASMCMD-8012: cannot determine file type for file
ORA-15056: additional error message
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 322
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
拷贝失败，从这也说明了前面 直接指定共享磁盘路径创建钱夹失败的原因，共享磁盘上不支持此类文件。
四 DG 端配置 钱夹
4.1 指定钱夹存放位置
备库：
在目录 $ORACLE_HOME/network/admin 下找到文件sqlnet.ora ，添加如下内容：
# Oracle Advanced Security Transparent Data Encryption
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/product/11.2/network/admin/encryption_wallet)))
4.2 创建目录
备库：
$cd /u01/app/oracle/product/11.2/network/admin/
$mkdir encryption_wallet
4.3 拷贝primary 端加密钱夹
将生产端钱夹目录下的ewallet.p12 拷贝到standby 端指定的钱夹目录下。对于DG 来说这样的设置还不够，因为钱夹在这个时候还没有被打开，归档仍旧无法正常应用。我们对于备端建议使用无人值守的自动打开的钱包。
4.4 创建自动打开的钱夹
备库：
自动打开钱夹（文件名是 cwallet.sso ）在数据库启动时会自动打开。
两种方法可创建自动打开的钱夹 ：
-- 命令行工具“orapki ”
$ cd $ORACLE_HOME/network/admin/ encryption_wallet
$ orapki wallet create –wallet $ORACLE_HOME/network/admin/encryption_wallet -auto_login_local
提示输入primary 设置的钱夹密码
钱夹存放目录下会生成一个新文件cwallet.sso
-- 图形化方式显示
以oracle 用户运行owm ，打开钱夹管理工具：
选择“打开钱夹”，找到存放钱夹的路径：
输入正确的密码，打开钱夹：
在菜单栏选中“ 钱夹” ，勾上"Auto Login" 的复选框，表示自动登录打开；
配置完成后保存退出
钱夹存放目录下会生成一个新文件cwallet.sso
此时DG 端打开应用进程即可正常应用日志。
alter database recover managed standby database disconnect from session;
4.5 说明
DG 主库部署 wallet ，备库不部署 wallet ：
查看 alert 日志发现，归档能够正常传输到备库，但是查询应用情况就发现，归档并不能被应用在备库中：
SQL> SELECT SEQUENCE#, APPLIED,FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
88 YES 12-MAY-17 12-MAY-17
89 YES 12-MAY-17 12-MAY-17
90 YES 12-MAY-17 13-MAY-17
91 YES 13-MAY-17 13-MAY-17
92 YES 13-MAY-17 13-MAY-17
93 YES 13-MAY-17 13-MAY-17
94 YES 13-MAY-17 13-MAY-17
95 YES 13-MAY-17 13-MAY-17
96 YES 13-MAY-17 13-MAY-17
97 YES 13-MAY-17 13-MAY-17
98 YES 13-MAY-17 13-MAY-17
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
99 YES 13-MAY-17 13-MAY-17
100 YES 13-MAY-17 13-MAY-17
101 YES 13-MAY-17 13-MAY-17
102 YES 13-MAY-17 13-MAY-17
103 YES 13-MAY-17 13-MAY-17
104 YES 13-MAY-17 13-MAY-17
105 YES 13-MAY-17 13-MAY-17
106 YES 13-MAY-17 13-MAY-17
107 YES 13-MAY-17 13-MAY-17
108 YES 13-MAY-17 13-MAY-17
109 NO 13-MAY-17 13-MAY-17
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
110 NO 13-MAY-17 13-MAY-17
111 NO 13-MAY-17 13-MAY-17
112 NO 13-MAY-17 13-MAY-17
113 NO 13-MAY-17 13-MAY-17
114 NO 13-MAY-17 13-MAY-17
115 NO 13-MAY-17 13-MAY-17
116 NO 13-MAY-17 13-MAY-17
117 NO 13-MAY-17 13-MAY-17
118 NO 13-MAY-17 13-MAY-17
119 NO 13-MAY-17 13-MAY-17
120 NO 13-MAY-17 13-MAY-17
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
121 NO 13-MAY-17 13-MAY-17
122 NO 13-MAY-17 13-MAY-17
alert 日志报错：
Media Recovery Log /arch/1_109_936453293.dbf
Apply redo for TSE master key re-key failed: wallet error 28365
Standby Crash Recovery aborted due to error 28365.
Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3517.trc:
ORA-28365: wallet is not open
Recovery interrupted!
Recovered data files to a consistent state at change 1238216
Completed Standby Crash Recovery.
备库开启wallet （从主库拷贝而来）是否可以正常应用日志
测试如下：
主库SCP 到备库：
[oracle@localhost wallet]$ scp ewallet.p12 192.168.40.71:/oracle/app/admin/orcl/wallet
oracle@192.168.40.71's password:
ewallet.p12 100% 2845 2.8KB/s 00:00
备库开启wallet 钱包：
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY wallet;
System altered.
手动应用mrp 日志：
alter database recover managed standby database disconnect from session;
查看 alert 日志发现成功应用：
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /arch/1_123_936453293.dbf
Media Recovery Log /arch/1_124_936453293.dbf
Media Recovery Log /arch/1_125_936453293.dbf
Completed: alter database recover managed standby database disconnect from session
Media Recovery Log /arch/1_126_936453293.dbf
Media Recovery Log /arch/1_127_936453293.dbf
Media Recovery Log /arch/1_128_936453293.dbf
Media Recovery Log /arch/1_129_936453293.dbf
Media Recovery Log /arch/1_130_936453293.dbf
Media Recovery Log /arch/1_131_936453293.dbf
Media Recovery Log /arch/1_132_936453293.dbf
Media Recovery Log /arch/1_133_936453293.dbf
Media Recovery Log /arch/1_134_936453293.dbf
Media Recovery Log /arch/1_135_936453293.dbf
Media Recovery Log /arch/1_136_936453293.dbf
Media Recovery Log /arch/1_137_936453293.dbf
关闭mrp 进程并将数据库启动到read only 模式后查询数据：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
SQL> select * from test.employee;
NAME ID
-------------------------------------------------------------------------------test 456
test2 789
test3 123
SQL> select table_name,column_name from DBA_ENCRYPTED_COLUMNS;
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE ID
Dg 切换测试：
备库：（自动打开钱夹）
[oracle@ljw orcl]$ orapki wallet create -wallet /oracle/app/wallet/orcl -auto_login_local
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
OPEN
主库：（手动打开钱夹）
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
OPEN
主备切换：
主库：
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
453
备库查询：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
453
主库：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> Alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 432016952 bytes
Database Buffers 348127232 bytes
Redo Buffers 2596864 bytes
SQL> alter database mount standby database;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
备库：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 432016952 bytes
Database Buffers 348127232 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
切日志查询：
主库：
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
455
备库：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
455
主库查询加密表：
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
OPEN
SQL> conn test/test;
Connected.
SQL> select * from tde;
ID DATA
---------- --------------------------------------------------
34 JSS
35 TEST
0 SYS
5 SYSTEM
31 APPQOSSYS
9 OUTLN
14 DIP
30 DBSNMP
32 WMSYS
21 ORACLE_OCM
10 rows selected.
备库（原来为主库）：
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
CLOSED
（注：原来的主库没有创建自动钱包，所以切成备库以后，需要手动开启）
五 钱夹的备份
正如上述，已经加密过的表列或者表空间，钱夹必须打开才能够查询到里面的数据。如果钱夹丢失，那就意味着加密数据的丢失，所以钱夹的备份是及其重要的。钱夹一开始创建就应该得到有效的备份，放在不同的磁盘上。不要和数据库文件所在磁盘相同，这可以避免数据文件和钱夹同时被盗（当然了，如果同时被盗，想通过数据库查询加密数据，不知道钱夹的密码，也是无法查询到加密数据的）。
除了钱夹需要有效的备份之外，钱夹的密码千万不能忘记。如果忘记了钱夹密码，钱夹就无法打开，加密的数据也就无法查询到，这也就意味着加密数据的丢失。 Oracle 没有提供解决钱夹密码丢失的方法 。
综上，如果使用透明加密来加密数据，要想加密数据不丢失，必须做到两点：1. 钱夹必须存在（备份的重要性）；2. 钱夹的密码不能够忘记。