一 文档说明
Oracle 数据库使用身份验证、授权和审核机制来保护数据库中的数据，但没有能够保护存储在操作系统上的数据文件。为了保护这些数据文件，Oracle数据库提供透明数据加密（TDE）。该文档主要说明 oracle 高级安全组件中的透明数据加密 (TDE) 部分。
二 钱夹的部署
指定钱夹存放位置
在目录 $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 建议将钱夹位置放置在共享文件系统上，以便各个节点共享访问。
创建目录
$cd /u01/app/oracle/product/11.2/network/admin/
$mkdir encryption_wallet
创建主加密键
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
打开和关闭钱夹
第一次设置万能密钥会自动打开钱夹，每次数据库被关闭，钱夹也关闭。在加密或解密之前必须确保钱夹被打开。可以配置自动登录打开（可选）。
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "test";
（注：mount状态下就可以开启钱夹）
关闭钱夹： ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "test";
加密列和表空间具体过程
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.oraENCRYPTION_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_HOMECLOSED 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_HOMEOPEN 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 sizeAdditional information: 1 ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 322 ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute) 拷贝失败，从这也说明了前面 直接指定共享磁盘路径创建钱夹失败的原因，共享磁盘上不支持此类文件。
四 端配置 钱夹
指定钱夹存放位置
备库： 在目录 $ORACLE_HOME/network/admin 下找到文件sqlnet.ora ，添加如下内容：# Oracle Advanced Security Transparent Data EncryptionENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/product/11.2/network/admin/encryption_wallet)))
创建目录
备库：$cd /u01/app/oracle/product/11.2/network/admin/$mkdir encryption_wallet
拷贝primary 端加密钱夹
将生产端钱夹目录下的ewallet.p12 拷贝到standby 端指定的钱夹目录下。对于DG 来说这样的设置还不够，因为钱夹在这个时候还没有被打开，归档仍旧无法正常应用。我们对于备端建议使用无人值守的自动打开的钱包。
创建自动打开的钱夹
备库： 自动打开钱夹（文件名是 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;
说明
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/walletoracle@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.dbfMedia Recovery Log /arch/1_124_936453293.dbfMedia Recovery Log /arch/1_125_936453293.dbfCompleted: alter database recover managed standby database disconnect from sessionMedia Recovery Log /arch/1_126_936453293.dbfMedia Recovery Log /arch/1_127_936453293.dbfMedia Recovery Log /arch/1_128_936453293.dbfMedia Recovery Log /arch/1_129_936453293.dbfMedia Recovery Log /arch/1_130_936453293.dbfMedia Recovery Log /arch/1_131_936453293.dbfMedia Recovery Log /arch/1_132_936453293.dbfMedia Recovery Log /arch/1_133_936453293.dbfMedia Recovery Log /arch/1_134_936453293.dbfMedia Recovery Log /arch/1_135_936453293.dbfMedia Recovery Log /arch/1_136_936453293.dbfMedia 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_localOracle PKI Tool : Version 11.2.0.4.0 - ProductionCopyright (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_SIDOPEN 主库：（手动打开钱夹）SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/wallet/$ORACLE_SIDOPEN 主备切换： 主库：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 nomountORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 432016952 bytesDatabase Buffers 348127232 bytesRedo Buffers 2596864 bytesSQL> 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 immediateORA-01109: database not open Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 432016952 bytesDatabase Buffers 348127232 bytesRedo Buffers 2596864 bytesDatabase 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_SIDOPEN 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_SIDCLOSED （注：原来的主库没有创建自动钱包，所以切成备库以后，需要手动开启）
五 钱夹的备份
正如上述，已经加密过的表列或者表空间，钱夹必须打开才能够查询到里面的数据。如果钱夹丢失，那就意味着加密数据的丢失，所以钱夹的备份是及其重要的。钱夹一开始创建就应该得到有效的备份，放在不同的磁盘上。不要和数据库文件所在磁盘相同，这可以避免数据文件和钱夹同时被盗（当然了，如果同时被盗，想通过数据库查询加密数据，不知道钱夹的密码，也是无法查询到加密数据的）。 除了钱夹需要有效的备份之外，钱夹的密码千万不能忘记。如果忘记了钱夹密码，钱夹就无法打开，加密的数据也就无法查询到，这也就意味着加密数据的丢失。 Oracle 没有提供解决钱夹密码丢失的方法 。 综上，如果使用透明加密来加密数据，要想加密数据不丢失，必须做到两点：1. 钱夹必须存在（备份的重要性）；2. 钱夹的密码不能够忘记。
场景一：透明加密后，存储在磁盘上的数据是密文
这以加密表空间为例：
用户连入数据库进行数据更新或者查询时，所涉及到的对象会自动加密或自动解密；
但加密表空间里的所有数据都是以加密的格式被存储在磁盘上，磁盘或备份介质被盗时，里面的数据也不会被盗取，因为里面是乱码的。举例如下：
表 book_list 未加密，其所在数据文件为 books01.dbf 。
表 no_to_yes 加密过，其所在数据文件为 secure01.dbf 。
授权用户连入数据库查询这两张表：
SQL> select * from book_list; BOOKID BOOKNAME
---------- ---------- 1 note 2 good 3 name 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
TDE 已为用户自动解密，所以查询到的都是正常的数据。
如果使用操作系统命令 strings 直接查看文件中内容，未加密表空间的数据文件中的内容为明文，而加密过表空间对应的数据文件中的内容则为乱码。
[root@ljw jiami]# strings books01.dbf （未加密）
}|{z
WORCL
BOOKS
name,
good,
note
[root@ljw jiami]# strings secure01.dbf （加密过）
MMFJ?8E
iHa!c
jVIa9`o
y8wt0
zcnf
'3{b9
cVe9
+w!N
(9zq9`
[6.H
o1U"
h[%l
\=zm\
,O;Y
Ur9(R
^ UP
Qr*<
AYB=K
=h>:
]? 9
TDV.J2
YL]dV
Z_!
!Q_#
p4|o
(s])s
&q=99
Zqkn
。
（内存中为明文，硬盘中为密文） （注：如果文件很大，则可以使用管道 head –n 行数 指定要显示的行数） 例： [oracle@ljw orcl]$ strings books01.dbf | head -n 3
场景二：exp/imp导出导入测试
结论：导出工具 EXP 无法导出加密过的表，具体测试如下： （钱包打开和钱包关闭时，导出加密过的表进行测试） 钱包关闭时： 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 TEST EXISTING_TABLE DATA AES 192 bits key YES SHA-1 SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS ------------------ file /oracle/app/product/11.2.0/db_1 CLOSED [oracle@ljw backup]$ exp system/oracle tables=test.tde file=tde.dmp log=tde.log Export: Release 11.2.0.4.0 - Production on Sat Jun 24 03:48:27 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion) About to export specified tables via Conventional Path ...Current user changed to TEST EXP-00107: Feature (COLUMN ENCRYPTION) of column DATA in table TEST.TDE is not supported. The table will not be exported.Export terminated successfully with warnings. 钱包打开时： SQL> alter system set encryption wallet open identified by "hzmcdba123"; System altered. [oracle@ljw backup]$ exp system/oracle tables=test.tde file=tde.dmp log=tde.log Export: Release 11.2.0.4.0 - Production on Sat Jun 24 03:58:43 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified tables via Conventional Path ... Current user changed to TEST EXP-00107: Feature (COLUMN ENCRYPTION) of column DATA in table TEST.TDE is not supported. The table will not be exported. Export terminated successfully with warnings.
场景三：expdp/impdp导出导入测试
结论： 1. 导出时，钱包需要打开，否则都会报错。 2. 导出后，导入时，钱包也需要打开，否则也都会报错。 3. 导出后，导入另一个数据库，如果那个数据库没有钱夹，导入失败。如果那个数据库有钱夹，并且钱夹打开（测试中另一库中新建钱夹密码与原库不一样），也会导入成功，具体测试如下：
1. 导出时，钱包需要打开，否则都会报错 钱夹关闭： SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS ------------------ file /oracle/app/product/11.2.0/db_1 CLOSED 导出： [oracle@ljw backup]$ expdp system/oracle directory=dump_file_dir dumpfile=tde.dmp tables=test.tde Export: Release 11.2.0.4.0 - Production on Sat Jun 24 04:15:09 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=dump_file_dir dumpfile=tde.dmp tables=test.tde Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-31693: Table data object "TEST"."TDE" 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 "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /oracle/backup/tde.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Sat Jun 24 04:15:16 2017 elapsed 0 00:00:05
2. 导出后，导入时，钱包也需要打开，否则也都会报错 钱夹打开： SQL> alter system set encryption wallet open identified by " hzmcdba123"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS ------------------ file /oracle/app/product/11.2.0/db_1 OPEN 导出：[oracle@ljw backup]$ expdp system/oracle directory=dump_file_dir dumpfile=tde.dmp tables=test.tde Export: Release 11.2.0.4.0 - Production on Sat Jun 24 04:33:37 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=dump_file_dir dumpfile=tde.dmp tables=test.tdeEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "TEST"."TDE" 5.570 KB 10 rows ORA-39173: Encrypted data has been stored unencrypted in dump file set.Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /oracle/backup/tde.dmpJob "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Sat Jun 24 04:33:44 2017 elapsed 0 00:00:06 导入同一个库： 钱夹关闭时导入：SQL> alter system set encryption wallet close identified by "hzmcdba123"; System altered. [oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp Import: Release 11.2.0.4.0 - Production on Sat Jun 24 04:59:04 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmpProcessing object type TABLE_EXPORT/TABLE/TABLEORA-39083: Object type TABLE:"TEST"."TDE" failed to create with error: ORA-28365: wallet is not openFailing sql is:CREATE TABLE "TEST"."TDE" ("ID" NUMBER(10,0), "DATA" VARCHAR2(50 BYTE) ENCRYPT USING 'AES192' 'SHA-1') SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"Processing object type TABLE_EXPORT/TABLE/TABLE_DATAProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"TEST"."TDE" creation failedJob "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sat Jun 24 04:59:07 2017 elapsed 0 00:00:02 钱夹打开导入：SQL> alter system set encryption wallet open identified by "hzmcdba123"; System altered. [oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp Import: Release 11.2.0.4.0 - Production on Sat Jun 24 05:03:23 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmpProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "TEST"."TDE" 5.570 KB 10 rowsProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Jun 24 05:03:28 2017 elapsed 0 00:00:04 导入成功。 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. 注：钱夹打开，从同一个库中导出后导入成功。 导入：（另一个库）[oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp Import: Release 11.2.0.4.0 - Production on Sat Jun 24 04:54:33 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmpProcessing object type TABLE_EXPORT/TABLE/TABLEORA-39083: Object type TABLE:"TEST"."TDE" failed to create with error: ORA-28365: wallet is not openFailing sql is:CREATE TABLE "TEST"."TDE" ("ID" NUMBER(10,0), "DATA" VARCHAR2(50 BYTE) ENCRYPT USING 'AES192' 'SHA-1') SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"Processing object type TABLE_EXPORT/TABLE/TABLE_DATAProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"TEST"."TDE" creation failedJob "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sat Jun 24 04:54:36 2017 elapsed 0 00:00:02 SQL> select * from test.tde;select * from test.tde *ERROR at line 1:ORA-00942: table or view does not exist 注：导入另一个库失败，因为没有启用透明加密钱夹。 创建钱包，导入SQL> alter system set encryption key identified by "hzmcdba"; System altered. [oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp Import: Release 11.2.0.4.0 - Production on Sat Jun 24 05:41:31 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmpProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "TEST"."TDE" 5.570 KB 10 rowsProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Jun 24 05:41:37 2017 elapsed 0 00:00:04 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. 注：导入另一库，需要有钱包，钱包打开即可。
场景四：rman备份恢复测试
结论：不管钱包是否打开都可以正常备份，恢复时需要打开钱包。 备份：SQL> select table_name from user_tables where tablespace_name='ENCRYPTEDTBS'; TABLE_NAME------------------------------ENCRYPTED_TABLENO_TO_YES 钱夹关闭：SQL> alter system set encryption wallet close identified by "hzmcdba123"; System altered. RMAN> backup datafile 7; Starting backup at 24-JUN-17using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00007 name=/oracle/app/orcl/secure01.dbfchannel ORA_DISK_1: starting piece 1 at 24-JUN-17channel ORA_DISK_1: finished piece 1 at 24-JUN-17piece handle=/oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1 tag=TAG20170624T084727 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 24-JUN-17 Starting Control File and SPFILE Autobackup at 24-JUN-17piece handle=/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-09 comment=NONEFinished Control File and SPFILE Autobackup at 24-JUN-17 钱夹打开：SQL> alter system set encryption wallet open identified by "hzmcdba123"; System altered. RMAN> backup datafile 7; Starting backup at 24-JUN-17using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00007 name=/oracle/app/orcl/secure01.dbfchannel ORA_DISK_1: starting piece 1 at 24-JUN-17channel ORA_DISK_1: finished piece 1 at 24-JUN-17piece handle=/oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1 tag=TAG20170624T084842 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 24-JUN-17 Starting Control File and SPFILE Autobackup at 24-JUN-17piece handle=/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-0a comment=NONEFinished Control File and SPFILE Autobackup at 24-JUN-17 恢复： 钱夹关闭：[oracle@ljw orcl]$ rm secure01.dbf
1. 用之前关闭钱夹备份的备份集经进行恢复：RMAN> restore datafile 7 from tag='TAG20170624T084727'; Starting restore at 24-JUN-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=17 device type=DISK channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00007 to /oracle/app/orcl/secure01.dbfchannel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1channel ORA_DISK_1: piece handle=/oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1 tag=TAG20170624T084727channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 24-JUN-17 RMAN> recover datafile 7 from tag='TAG20170624T084727'; Starting recover at 24-JUN-17using channel ORA_DISK_1 starting media recoverymedia recovery failedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 06/24/2017 08:53:15ORA-00283: recovery session canceled due to errorsRMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed datafile 7ORA-00283: recovery session canceled due to errors ORA-28365: wallet is not open SQL> alter system set encryption wallet open identified by "hzmcdba123"; System altered. RMAN> recover datafile 7 from tag='TAG20170624T084727'; Starting recover at 24-JUN-17using channel ORA_DISK_1 starting media recoverymedia recovery complete, elapsed time: 00:00:00 Finished recover at 24-JUN-17 SQL> alter database open; Database altered. SQL> select * from test.jiami; ID DATA---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 0 SYS 5 SYSTEM 9 OUTLN 10 rows selected.
2. 用打开钱夹备份的备份集经进行恢复：SQL> startupORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 465571384 bytesDatabase Buffers 314572800 bytesRedo Buffers 2596864 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 7 - see DBWR trace fileORA-01110: data file 7: '/oracle/app/orcl/secure01.dbf RMAN> restore datafile 7 from tag='TAG20170624T084842'; Starting restore at 24-JUN-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=17 device type=DISK channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00007 to /oracle/app/orcl/secure01.dbfchannel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1channel ORA_DISK_1: piece handle=/oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1 tag=TAG20170624T084842channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 24-JUN-17 RMAN> recover datafile 7 from tag='TAG20170624T084842'; Starting recover at 24-JUN-17using channel ORA_DISK_1 starting media recoverymedia recovery failedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 06/24/2017 09:00:19ORA-00283: recovery session canceled due to errorsRMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed datafile 7ORA-00283: recovery session canceled due to errors ORA-28365: wallet is not open SQL> alter system set encryption wallet open identified by "hzmcdba123"; System altered. RMAN> recover datafile 7 from tag='TAG20170624T084842'; Starting recover at 24-JUN-17using channel ORA_DISK_1 starting media recoverymedia recovery complete, elapsed time: 00:00:00 Finished recover at 24-JUN-17 SQL> alter database open; Database altered. SQL> select * from test.jiami; ID DATA---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 0 SYS 5 SYSTEM 9 OUTLN 10 rows selected.
场景五：cp拷贝数据库文件能否 打开，数据能否可查测试
结论：数据文件可打开，加密数据可查（当然了，钱夹也拷过去，钱夹密码也知道）。 只拷贝数据文件，钱夹没有拷贝，加密数据不可查（模拟数据文件被盗） 情景一： SQL> drop tablespace dabiao including contents and datafiles; Tablespace dropped. SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on; Tablespace created. SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao; Table created. SQL> begin 2 for i in 1 .. 10 3 loop 4 insert into dabiao values ( i, 'ljw' ); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> alter table dabiao modify(name encrypt); Table altered. SQL> select * from dabiao; ID NAME---------- ---------- 1 ljw 2 ljw 3 ljw 4 ljw 5 ljw 6 ljw 7 ljw 8 ljw 9 ljw 10 ljw 10 rows selected. SQL> alter system set encryption wallet close identified by "hzmcdba"; System altered. SQL> select * from dabiao;select * from dabiao *ERROR at line 1:ORA-28365: wallet is not open 关闭数据：SQL> conn / as sysdba Connected.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down. scp -r /oracle/* oracle@192.168.142.11:/oracle/ 另一台： SQL> startupORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 448794168 bytesDatabase Buffers 331350016 bytesRedo Buffers 2596864 bytesDatabase mounted.Database opened.SQL> conn test/test; Connected.SQL> select * from dabiao;select * from dabiao *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 dabiao; ID NAME---------- ---------- 1 ljw 2 ljw 3 ljw 4 ljw 5 ljw 6 ljw 7 ljw 8 ljw 9 ljw 10 ljw 10 rows selected. 打开查询成功！ 情景二： 源库： [oracle@ljw orcl]$ pwd/oracle/app/orcl[oracle@ljw orcl]$ lsbooks01.dbf control02.ctl redo02.log sysaux01.dbf temp01.dbf test_tde users01.dbfcontrol01.ctl redo01.log redo03.log system01.dbf test.dbf undotbs01.dbf[oracle@ljw orcl]$ scp * oracle@192.168.142.11:/oracle/app/orcl/oracle@192.168.142.11's password:books01.dbf 100% 5128KB 5.0MB/s 00:01 control01.ctl 100% 9840KB 9.6MB/s 00:00 control02.ctl 100% 9840KB 9.6MB/s 00:00 redo01.log 100% 50MB 50.0MB/s 00:01 redo02.log 100% 50MB 50.0MB/s 00:01 redo03.log 100% 50MB 50.0MB/s 00:01 sysaux01.dbf 100% 600MB 30.0MB/s 00:20 system01.dbf 100% 700MB 29.2MB/s 00:24 temp01.dbf 100% 20MB 5.0MB/s 00:04 test.dbf 100% 5128KB 5.0MB/s 00:01 test_tde 100% 5128KB 5.0MB/s 00:00 undotbs01.dbf 100% 1710MB 25.9MB/s 01:06 users01.dbf 100% 5128KB 5.0MB/s 00:00 [oracle@ljw orcl]$ cd $ORACLE_HOME[oracle@ljw db_1]$ cd dbs[oracle@ljw dbs]$ ls25s4jm22_1_1 c-1471212201-20170617-00 c-1471212201-20170627-00 hc_orcl.dat orapworcl27s4k331_1_1 c-1471212201-20170617-01 c-1471212201-20170627-01 hc_test.dat orapwtestarch1_152_944363414.dbf c-1471212201-20170617-02 c-1471212201-20170627-02 init.ora snapcf_orcl.fc-1471212201-20170519-00 c-1471212201-20170617-03 c-1471212201-20170627-03 initTEST.ora spfileorcl.orac-1471212201-20170519-05 c-1471212201-20170617-04 c-1471212201-20170630-00 lkORCL spfiletest.orac-1471212201-20170519-06 c-1471212201-20170617-05 c-1471212201-20170630-01 lkTEST[oracle@ljw dbs]$ scp * oracle@192.168.142.11:/oracle/app/product/11.2.0/db_1/ddbs/ dc_ocm/ deinstall/ demo/ diagnostics/ dv/ [oracle@ljw dbs]$ scp * oracle@192.168.142.11:/oracle/app/product/11.2.0/db_1/dbs/oracle@192.168.142.11's password:25s4jm22_1_1 100% 307MB 34.2MB/s 00:09 27s4k331_1_1 100% 279MB 25.4MB/s 00:11 arch1_152_944363414.dbf 100% 50MB 49.9MB/s 00:01 c-1471212201-20170519-00 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170519-05 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170519-06 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-00 100% 9664KB 3.2MB/s 00:03 c-1471212201-20170617-01 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-02 100% 9664KB 9.4MB/s 00:01 c-1471212201-20170617-03 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-04 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-05 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170627-00 100% 9728KB 9.5MB/s 00:00 c-1471212201-20170627-01 100% 9792KB 9.6MB/s 00:01 c-1471212201-20170627-02 100% 9792KB 9.6MB/s 00:00 c-1471212201-20170627-03 100% 9920KB 9.7MB/s 00:00 c-1471212201-20170630-00 100% 9920KB 9.7MB/s 00:00 c-1471212201-20170630-01 100% 9920KB 9.7MB/s 00:01 hc_orcl.dat 100% 1544 1.5KB/s 00:00 hc_test.dat 100% 1544 1.5KB/s 00:00 init.ora 100% 2851 2.8KB/s 00:00 initTEST.ora 100% 705 0.7KB/s 00:00 lkORCL 100% 24 0.0KB/s 00:00 lkTEST 100% 24 0.0KB/s 00:00 orapworcl 100% 1536 1.5KB/s 00:00 orapwtest 100% 1536 1.5KB/s 00:00 snapcf_orcl.f 100% 9840KB 9.6MB/s 00:00 spfileorcl.ora 100% 2560 2.5KB/s 00:00 spfiletest.ora 100% 2560 2.5KB/s 00:00 SQL> select * from dba_encrypted_columns; OWNER TABLE_NAME------------------------------ ------------------------------COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL------------------------------ ----------------------------- --- ------------TEST TDEDATA AES 192 bits key YES SHA-1 TEST SALTNAME AES 192 bits key YES SHA-1 SQL> conn test/test;Connected.SQL> select * from tde;select * from tde *ERROR at line 1:ORA-28365: wallet is not open 目标库： 相关文件被传过来 SQL> startupORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 448794168 bytesDatabase Buffers 331350016 bytesRedo Buffers 2596864 bytesDatabase mounted.Database opened.SQL> conn test/test;Connected.SQL> select * from tde;select * from tde *ERROR at line 1:ORA-28365: wallet is not open SQL> alter system set encryption wallet open identified by "hzmcdba123";alter system set encryption wallet open identified by "hzmcdba123"*ERROR at line 1:ORA-28367: wallet does not exist 注：如果文件被盗，如果想通过数据库查看数据，已被加密的表是无法查询到数据的，因为有钱夹的保护。
场景六：在对一张未加密的表进行更新操作的同时，对这张表中字段进行加密测试
结论：对表加密失败，提交后，才能加密成功 SQL> create table tijiao (id number,name varchar(10)); Table created. SQL> insert into tijiao values (1,'ljw'); 1 row created. SQL> insert into tijiao values (2,'test'); 1 row created. SQL> insert into tijiao values (3,'dba'); 1 row created. SQL> insert into tijiao values (4,'hzmc'); 1 row created. SQL> insert into tijiao values (5,'hzmcdba'); 1 row created. 另一个窗口：SQL> conn test/test;Connected.SQL> alter table tijiao modify(name encrypt);alter table tijiao modify(name encrypt) *ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter table tijiao modify(name encrypt);alter table tijiao modify(name encrypt) *ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter table tijiao modify(name encrypt); alter table tijiao modify(name encrypt) *ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 提交：SQL> commit; Commit complete. SQL> alter table tijiao modify(name encrypt); Table altered.
场景七：对表空间中现有表进行加密测试 ，测试其随着表的变大，加密表空间大小和加密时间所需长短
结论： 1 . 当数据文件没有打开自动扩展，对表中列进行加密，空间不足时，加密会失败。 2 ．如下表所示（数据文件打开自动扩展）：
|
加密数据量 |
50 万行（ 8M ） |
100 万行（ 16M ） |
300 万行（ 49M ） |
|
加密前数据文件大小 |
100 |
100 |
100 |
|
加密后数据文件大小 |
100 |
119.25 |
345.5 |
|
加密前表空间 FREE 大小 |
91 |
83 |
50 |
|
加密前表大小 |
8 |
16 |
49 |
|
加密后表空间 FREE 大小 |
44 |
6.25 |
16.5 |
|
加密后表大小 |
55 |
112 |
328 |
|
加密所用时间 |
1 分钟 |
1 分 45 秒 |
5 分 35 秒 |
SQL> alter system set encryption wallet open identified by "hzmcdba"; System altered. SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m; Tablespace created. SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao; Table created. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 100SYSAUX 600UNDOTBS1 200BOOKS 5USERS 5TEST 5SYSTEM 700 7 rows selected. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 99SYSAUX 475.5UNDOTBS1 189.5625BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; no rows selected SQL> begin 2 for i in 1 .. 3000000 3 loop 4 insert into dabiao values ( i, 'ljw' ); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 50SYSAUX 475.5UNDOTBS1 2BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO49M 为该表加密 SQL> alter table dabiao modify(name encrypt);alter table dabiao modify(name encrypt)*ERROR at line 1:ORA-01653: unable to extend table TEST.DABIAO by 1024 in tablespace DABIAO SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 3SYSAUX 475.5UNDOTBS1 1.625BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO96M SQL> alter system set encryption wallet close identified by "hzmcdba"; System altered. SQL> select * from dabiao where id > 2999995; ID NAME---------- ---------- 2999996 ljw 2999997 ljw 2999998 ljw 2999999 ljw 3000000 ljw 由于空间不足会导致加密失败。下面测试 50 万行、 100 万行以及 300 万行数据加密前后空间大小及其所用时间。 SQL> alter system set encryption wallet open identified by "hzmcdba"; System altered. SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on; Tablespace created. SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao; Table created. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 100SYSAUX 600UNDOTBS1 505BOOKS 5USERS 5TEST 5SYSTEM 700 7 rows selected. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 99SYSAUX 475.5UNDOTBS1 1.625BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; no rows selected SQL> begin 2 for i in 1 .. 500000 3 loop 4 insert into dabiao values ( i, 'ljw' ); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 91SYSAUX 475.5UNDOTBS1 2BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO 8M 对表进行加密 SQL> alter table dabiao modify(name encrypt); Table altered. （耗时 1 分钟！） SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 44SYSAUX 475.5UNDOTBS1 2BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO 55M 恢复测试环境到初始状态，插入 100 万行数据再来过： SQL> drop tablespace dabiao including contents and datafiles; Tablespace dropped. SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on; Tablespace created. SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao; Table created. SQL> begin 2 for i in 1 .. 1000000 3 loop 4 insert into dabiao values ( i, 'ljw' ); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 83SYSAUX 475.5UNDOTBS1 23BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO 16M 对表进行加密 SQL> alter table dabiao modify(name encrypt); Table altered. （耗时 1 分 45 秒！） SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 119.25SYSAUX 600UNDOTBS1 785BOOKS 5USERS 5TEST 5SYSTEM 700 7 rows selected. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 6.25SYSAUX 475.625BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 6 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO 112M 恢复测试环境到初始状态，插入 300 万行数据再测试： SQL> drop tablespace dabiao including contents and datafiles; Tablespace dropped. SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on; Tablespace created. SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao; Table created. SQL> begin 2 for i in 1 .. 3000000 3 loop 4 insert into dabiao values ( i, 'ljw' ); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 100SYSAUX 600UNDOTBS1 990BOOKS 5USERS 5TEST 5SYSTEM 700 7 rows selected. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 50SYSAUX 475.625UNDOTBS1 2BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 7 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO 49M 进行加密： SQL> alter table dabiao modify(name encrypt); Table altered. （耗时 5 分 35 秒！） SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 345.5SYSAUX 600UNDOTBS1 1710BOOKS 5USERS 5TEST 5SYSTEM 700 7 rows selected. SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024------------------------------ --------------------DABIAO 16.5SYSAUX 475.5625BOOKS 3.875USERS 3.9375TEST 3.9375SYSTEM 423.9375 6 rows selected. SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M' FROM user_segments WHERE segment_name='DABIAO'; TABLENAME--------------------------------------------------------------------------------BYTES/1024/1024||'M'-----------------------------------------DABIAO 328M
场景八：钱夹的备份和恢复测试
结论：钱夹丢失后，恢复即可重新打开钱包。（重新创建一个一模一样的钱包是不可行的，无法查询到之前已经加密过的数据）
1. 备份钱包后，删掉钱包进行测试 数据库没关掉之前，加密的数据仍然是可查的 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 关库重启 SQL> shutdown immediate; SQL> startup; SQL> select * from tde; select * from tde * ERROR at line 1: ORA-28365: wallet is not open SQL> alter system set encryption wallet open identified by "hzmcdba123"; alter system set encryption wallet open identified by "hzmcdba123" * ERROR at line 1: ORA-28367: wallet does not exist 把钱包恢复 [oracle@ljw db_1]$ cp ewallet.p12.bak ewallet.p12 SQL> alter system set encryption wallet open identified by "hzmcdba123"; System altered. 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. 数据可查
2. 重新创建一个一模一样钱夹可行性测试 删除钱夹文件 [oracle@ljw db_1]$ rm ewallet.p12 SQL> alter system set encryption wallet close identified by "hzmcdba123"; // 钱包可关闭 System altered. SQL> alter system set encryption wallet open identified by "hzmcdba123"; alter system set encryption wallet open identified by "hzmcdba123" * ERROR at line 1: ORA-28367: wallet does not exist SQL> alter system set encryption key identified by "hzmcdba123"; alter system set encryption key identified by "hzmcdba123" * ERROR at line 1: ORA-28362: master key not found 重启 SQL> alter system set encryption key identified by "hzmcdba123";alter system set encryption key identified by "hzmcdba123"*ERROR at line 1:ORA-28396: rekey of enc$ dictionary table failed 虽然报错，但新的 wallet 文件还是生成了，上面的 ORA-28362 意指数据库中还存在有使用老的 masterkey 加密的 encryption key ，但这个老的 masterkey 没有包含在当前新建的 wallet 文件里 -rw-r--r-- 1 oracle oinstall 2845 Jun 25 07:11 ewallet.p12 SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER----------------------------------------------------------------------------------------------------STATUS------------------file/oracle/app/product/11.2.0/db_1OPEN SQL> select * from test.tde;select * from test.tde *ERROR at line 1:ORA-28362: master key not found （重新建钱包不可行，只能有之前的备份进行恢复）
场景九：钱夹的重建测试
结论：钱夹可以重建，但是重建后的钱夹不能查询使用旧钱夹中的主密钥加密的数据，即先前加密的数据丢失。 [oracle@ljw orcl]$ mv ewallet.p12 ewallet.p12.bak SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/wallet/$ORACLE_SIDCLOSED SQL> alter system set encryption key identified by "hzmcdba123";alter system set encryption key identified by "hzmcdba123"*ERROR at line 1: （其实就是一条告警信息） ORA-28362: master key not found （新的 wallet 文件会生成。提示信息，上面的 ORA-28362 意指数据库中还存在有使用老的 masterkey 加密的 encryption key ，但这个老的 masterkey 没有包含在当前新建的 wallet 文件里，这意味着用老的 masterkey 加密的 encryption key 无法被解密，之前加密的数据不可访问，即数据丢失。）
（新的钱夹生成）SQL> conn test/test;Connected. SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/wallet/$ORACLE_SID OPEN SQL> select * from tde; （之前加密的表tde ）select * from tde *ERROR at line 1: ORA-28362: master key not found （之前加密的数据丢失） SQL> create table new_tde (id number(10),data varchar2(50) encrypt); Table created. SQL> insert into new_tde select user_id,username from dba_users; 10 rows created. SQL> select * from new_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> alter system set encryption wallet close identified by "hzmcdba123"; System altered. SQL> select * from tde;select * from tde *ERROR at line 1:ORA-28365: wallet is not open SQL> select * from new_tde;select * from new_tde *ERROR at line 1:ORA-28365: wallet is not open
场景十：salt选项测试
结论：创建一个表（加密列），默认 salt ，不能再该加密列上创建索引。如果需要创建索引，必须指定为 no salt 。 SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/product/11.2.0/db_1OPEN SQL> create table salt (id number,name varchar(10) encrypt); Table created. SQL> insert into salt values(1,'dba'); 1 row created. SQL> create index salt_name_index on salt(name);create index salt_name_index on salt(name) *ERROR at line 1:ORA-28338: Column(s) cannot be both indexed and encrypted with salt SQL> alter table salt modify(name encrypt no salt); Table altered. SQL> create index salt_name_index on salt(name); Index created. SQL> alter table salt modify(name encrypt salt); alter table salt modify(name encrypt salt) * ERROR at line 1: ORA-28338: Column(s) cannot be both indexed and encrypted with salt SQL> drop index salt_name_index; Index dropped. SQL> alter table salt modify(name encrypt salt); Table altered.
场景十一：主外键列能否被加密测试
结论：主外键列不能够被加密 SQL> conn test/test;Connected. SQL> create table primarykey(id number,name varchar(10),constraint pkey primary key(name)); Table created. SQL> insert into primarykey values(1,'hzmcdba'); 1 row created. SQL> create table foreignkey(name varchar(10),score number,constraint fkey foreign key(name) references primarykey(name)); Table created. SQL> insert into foreignkey values('hzmcdba','98'); 1 row created. SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/product/11.2.0/db_1 CLOSED SQL> alter system set encryption wallet open identified by "hzmcdba"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/product/11.2.0/db_1 OPEN 为主键加密： SQL> alter table primarykey modify(name encrypt);alter table primarykey modify(name encrypt) *ERROR at line 1:ORA-28335: referenced or referencing FK constraint column cannot be encrypted 为外键加密：SQL> alter table foreignkey modify(name encrypt);alter table foreignkey modify(name encrypt) *ERROR at line 1:ORA-28335: referenced or referencing FK constraint column cannot be encrypted
场景十二：blob字段能否被加密测试
结论： blob 字段不能被加密 SQL> alter table table_blob modify(ph encrypt);alter table table_blob modify(ph encrypt)*ERROR at line 1:ORA-43856: Unsupported LOB type for SECUREFILE LOB operation
场景十三：分区表、分区键能否被加密测试
结论：不能将加密列作为分区键，这样会导致，虽然表能成功创建，但是创建出来的表不是分区表的情况。但是不作为分区键的列，可以成为加密列。 创建加密表空间：
SQL> CREATE TABLESPACE encryptedtbs02
2 DATAFILE '/oracle/app/oradata/orcl/encryptedtbs02.dbf' SIZE 100M
3 ENCRYPTION USING 'AES256'
4 DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE encryptedtbs03
2 DATAFILE '/oracle/app/oradata/orcl/encryptedtbs03.dbf' SIZE 100M
3 ENCRYPTION USING 'AES256'
4 DEFAULT STORAGE(ENCRYPT); 创建加密表：
CREATE TABLE test (
first_name VARCHAR2(128),
empID NUMBER ENCRYPT 'NOMAC' NO SALT ,
salary NUMBER(6)
)
partition by hash(empID)
(
partition part_01 tablespace encryptedtbs02,
partition part_02 tablespace encryptedtbs03
);
Table created. 虽然表是创建成功，但是有一个错误提示： ERROR at line 1: ORA-28346: an encrypted column cannot serve as a partitioning column ora - 28346: 一个加密列不能作为分区列 那么我们去查询表的加密信息及分区信息：
SQL> select table_name,column_name from DBA_ENCRYPTED_COLUMNS;
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE ID
TEST EMPID
SQL> select * from USER_TAB_PARTITIONS;
no rows selected 可以看到该表虽然创建成功，但是并没有分区成功 ，作为对比，我们创建另外一个分区列不是加密列的表：
CREATE TABLE test01 (
first_name VARCHAR2(128),
empID NUMBER ENCRYPT 'NOMAC' NO SALT ,
salary NUMBER(6)
)
partition by hash(first_name)
(
partition part_01 tablespace encryptedtbs02,
partition part_02 tablespace encryptedtbs03
);
Table created. 同样查询信息：
SQL> select table_name,column_name from DBA_ENCRYPTED_COLUMNS;
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE ID
TEST EMPID
TEST01 EMPID
SQL> select table_name,partition_name,tablespace_name from USER_TAB_PARTITIONS where table_name='TEST01';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST01 PART_01 ENCRYPTEDTBS02
TEST01 PART_02 ENCRYPTEDTBS03
场景十四：一个主机上多个库创建钱夹测试
结论：如果一台服务器上有多个数据库，创建一个钱夹即可使用于多个数据库。（钱夹的配置只需在 sqlnet.ora 中指定钱夹存放位置，然后 alter system set encryption wallet open identified by "hzmcdba" 创建钱夹，此时只有记住这个密码，这个钱夹 copy 到哪个数据库中都可以使用）但是不建议这样做， oracle 建议还是一个钱夹对一个库，具体测试看情节二。 情景一： [oracle@ljw ~]$ export ORACLE_SID=test;[oracle@ljw ~]$ echo $ORACLE_SIDtest[oracle@ljw ~]$ sqlplus / as sysdba; SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 30 07:17:39 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> startupORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 465571384 bytesDatabase Buffers 314572800 bytesRedo Buffers 2596864 bytesDatabase mounted.Database opened.SQL> show parameter db_name; NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_name string testSQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/product/11.2.0/db_1 CLOSED SQL> show user;USER is "SYS"SQL> alter system set encryption wallet open identified by "hzmcdba"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/product/11.2.0/db_1 OPEN SQL> create user test identified by "test"; User created. SQL> grant dba to test; Grant succeeded. SQL> conn test/test;Connected.SQL> create table jiami(id number,name varchar(10) encrypt); Table created. SQL> insert into jiami values(1,'hzmcdba'); 1 row created. SQL> select * from jiami; ID NAME---------- ---------- 1 hzmcdba SQL> alter system set encryption wallet close identified by "hzmcdba"; System altered. SQL> select * from jiami;select * from jiami *ERROR at line 1: ORA-28365: wallet is not open 情景二： [oracle@ljw admin]$ vi sqlnet.ora ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/app/wallet/$ORACLE_SID))) test 库： SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@ljw ~]$ sqlplus / as sysdba; SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 30 08:29:37 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/wallet/$ORACLE_SIDCLOSED SQL> alter system set encryption key identified by "hzmcdba123";alter system set encryption key identified by "hzmcdba123"*ERROR at line 1:ORA-28362: master key not found （ 因为之前已经配置，钱包照样生成） SQL> alter system set encryption wallet close identified by "hzmcdba123"; System altered. SQL> alter system set encryption wallet open identified by "hzmcdba123"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/wallet/$ORACLE_SIDOPEN
场景十五：数据库文件和自动打开钱夹文件被拷到另一台数据库上打开测试
结论：相关数据文件被拷贝到其他数据库进行打开，如果自动打开钱夹也被拷过去，能查询到被加密的数据。 [oracle@ljw orcl]$ orapki wallet create -wallet /oracle/app/wallet/orcl/ -auto_login_localOracle PKI Tool : Version 11.2.0.4.0 - ProductionCopyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup;ORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 448794168 bytesDatabase Buffers 331350016 bytesRedo Buffers 2596864 bytesDatabase mounted.Database opened. SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/wallet/$ORACLE_SIDOPEN SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down. [oracle@ljw orcl]$ ls -rtltotal 8-rw-r--r-- 1 oracle oinstall 2845 Jun 24 05:40 ewallet.p12-rw------- 1 oracle oinstall 2923 Jul 4 01:15 cwallet.sso [oracle@ljw orcl]$ scp * oracle@192.168.142.11:/oracle/app/orcl/oracle@192.168.142.11's password:Permission denied, please try again.oracle@192.168.142.11's password:books01.dbf 100% 5128KB 5.0MB/s 00:00 control01.ctl 100% 9840KB 9.6MB/s 00:00 control02.ctl 100% 9840KB 9.6MB/s 00:00 redo01.log 100% 50MB 50.0MB/s 00:01 redo02.log 100% 50MB 50.0MB/s 00:01 redo03.log 100% 50MB 50.0MB/s 00:00 sysaux01.dbf 100% 600MB 26.1MB/s 00:23 system01.dbf 100% 700MB 31.8MB/s 00:22 temp01.dbf 100% 20MB 20.0MB/s 00:00 test.dbf 100% 5128KB 5.0MB/s 00:00 test_tde 100% 5128KB 5.0MB/s 00:01 undotbs01.dbf 100% 1710MB 30.0MB/s 00:57 users01.dbf 100% 5128KB 5.0MB/s 00:00 [oracle@ljw dbs]$ scp * oracle@192.168.142.11:/oracle/app/product/11.2.0/db_1/dbs/oracle@192.168.142.11's password:25s4jm22_1_1 100% 307MB 43.9MB/s 00:07 27s4k331_1_1 100% 279MB 23.3MB/s 00:12 arch1_152_944363414.dbf 100% 50MB 49.9MB/s 00:01 c-1471212201-20170519-00 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170519-05 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170519-06 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-00 100% 9664KB 9.4MB/s 00:01 c-1471212201-20170617-01 100% 9664KB 9.4MB/s 00:01 c-1471212201-20170617-02 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-03 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-04 100% 9664KB 9.4MB/s 00:00 c-1471212201-20170617-05 100% 9664KB 9.4MB/s 00:01 c-1471212201-20170627-00 100% 9728KB 9.5MB/s 00:00 c-1471212201-20170627-01 100% 9792KB 9.6MB/s 00:01 c-1471212201-20170627-02 100% 9792KB 9.6MB/s 00:00 c-1471212201-20170627-03 100% 9920KB 9.7MB/s 00:00 c-1471212201-20170630-00 100% 9920KB 4.8MB/s 00:02 c-1471212201-20170630-01 100% 9920KB 9.7MB/s 00:00 hc_orcl.dat 100% 1544 1.5KB/s 00:00 hc_test.dat 100% 1544 1.5KB/s 00:00 init.ora 100% 2851 2.8KB/s 00:00 initTEST.ora 100% 705 0.7KB/s 00:00 lkORCL 100% 24 0.0KB/s 00:00 lkTEST 100% 24 0.0KB/s 00:00 orapworcl 100% 1536 1.5KB/s 00:00 orapwtest 100% 1536 1.5KB/s 00:00 snapcf_orcl.f 100% 9840KB 9.6MB/s 00:00 spfileorcl.ora 100% 2560 2.5KB/s 00:00 spfiletest.ora 100% 2560 2.5KB/s 00:00 [oracle@ljw orcl]$ scp cwallet.sso oracle@192.168.142.11:/oracle/app/wallet/orcl/oracle@192.168.142.11's password:cwallet.sso 100% 2923 2.9KB/s 00:00 192.168.142.11 ： [oracle@ljw admin]$ vi sqlnet.ora ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/app/wallet/orcl))) SQL> startupORACLE instance started. Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 448794168 bytesDatabase Buffers 331350016 bytesRedo Buffers 2596864 bytesDatabase mounted.Database opened.SQL> select * from v$encryption_wallet; WRL_TYPE--------------------WRL_PARAMETER--------------------------------------------------------------------------------STATUS------------------file/oracle/app/wallet/orclOPEN 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> create table clean (id number(10),data varchar2(50)); Table created. SQL> insert into clean select user_id,username from dba_users; 10 rows created. SQL> create table del_clean (id number(10),data varchar2(50)); Table created. SQL> create or replace trigger tr_del_clean 2 before delete 3 on clean 4 for each row 5 begin 6 insert into del_clean(id,data) values(:old.id,:old.data); 7 end; 8 / Trigger created. SQL> select * from clean; ID DATA---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 0 SYS 5 SYSTEM 9 OUTLN 10 rows selected. SQL> delete clean where id=0; 1 row deleted. SQL> select * from del_clean; ID DATA---------- -------------------------------------------------- 0 SYS SQL> select * from clean; ID DATA---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 5 SYSTEM 9 OUTLN 9 rows selected. 对表clean 进行加密： SQL> alter table clean modify(data encrypt); Table altered. SQL> delete clean where id=5; 1 row deleted. SQL> select * from clean; ID DATA---------- -------------------------------------------------- 34 JSS 35 TEST 14 DIP 21 ORACLE_OCM 31 APPQOSSYS 30 DBSNMP 32 WMSYS 9 OUTLN 8 rows selected. SQL> select * from del_clean; ID DATA---------- -------------------------------------------------- 0 SYS 5 SYSTEM
其它
1. 现有表空间是否能够直接加密？？？
验证结果：现有表空间 不能直接加密，但可以建个加密表空间，然后将该表空间中表move 到加密表空间中。
2. 钱夹密码忘记了怎么办，是否有恢复方法？？？
Oracle 没有提供解决钱夹密码丢失的方法。所以，钱夹密码千万不能忘记，这也是使用TDE 需要承担的风险。
![ORA-600[qesmaGetTblSeg1]错误分析](https://www.herecours.com/d/file/efpub/2026/03-03/20260303154204842178.jpg?x-oss-process=style/bb "ORA-600[qesmaGetTblSeg1]错误分析")
![ORA 600 [ktspgsb-1]ORA 600 [ktecgsc:objdORA-600[ktspgsb3objdchk_kcbgcur_3]故障](https://www.herecours.com/d/file/efpub/2026/03-03/20260303154154842159.jpg?x-oss-process=style/bb "ORA 600 [ktspgsb-1]ORA 600 [ktecgsc:objdORA-600[ktspgsb3objdchk_kcbgcur_3]故障")
