Oracle透明資料加密
文件說明
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
路徑
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)
複製失敗，從這也說明了前面 直接指定共享磁碟路徑建立錢夾失敗的原因，共享磁碟上不支援此類檔案。
端配置 錢夾
指定錢夾存放位置
備庫：
在目錄 $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)))
建立目錄
備庫：
$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/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. 錢夾的密碼不能夠忘記。
這以加密表空間為例：
使用者連入資料庫進行資料更新或者查詢時，所涉及到的物件會自動加密或自動解密；
但加密表空間裡的所有資料都是以加密的格式被儲存在磁碟上，磁碟或備份介質被盜時，裡面的資料也不會被盜取，因為裡面是亂碼的。舉例如下：
表 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 無法匯出加密過的表，具體測試如下：
（錢包開啟和錢包關閉時，匯出加密過的表進行測試）
錢包關閉時：
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 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.
錢包開啟時：
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.
結論： 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 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
. . 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.dmp
Job "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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"TEST"."TDE" failed to create with error:
ORA-28365: wallet is not open
Failing 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_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"TEST"."TDE" creation failed
Job "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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TDE" 5.570 KB 10 rows
Processing 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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"TEST"."TDE" failed to create with error:
ORA-28365: wallet is not open
Failing 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_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"TEST"."TDE" creation failed
Job "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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_file_dir dumpfile=tde.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TDE" 5.570 KB 10 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "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.
注：匯入另一庫，需要有錢包，錢包開啟即可。
結論：不管錢包是否開啟都可以正常備份，恢復時需要開啟錢包。
備份：
SQL> select table_name from user_tables where tablespace_name='ENCRYPTEDTBS';
TABLE_NAME
------------------------------
ENCRYPTED_TABLE
NO_TO_YES
錢夾關閉：
SQL> alter system set encryption wallet close identified by "hzmcdba123";
System altered.
RMAN> backup datafile 7;
Starting backup at 24-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/app/orcl/secure01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1 tag=TAG20170624T084727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-17
Starting Control File and SPFILE Autobackup at 24-JUN-17
piece handle=/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-09 comment=NONE
Finished 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-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/app/orcl/secure01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1 tag=TAG20170624T084842 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-17
Starting Control File and SPFILE Autobackup at 24-JUN-17
piece handle=/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-0a comment=NONE
Finished 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-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oracle/app/orcl/secure01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1
channel ORA_DISK_1: piece handle=/oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1 tag=TAG20170624T084727
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-JUN-17
RMAN> recover datafile 7 from tag='TAG20170624T084727';
Starting recover at 24-JUN-17
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/24/2017 08:53:15
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
datafile 7
ORA-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-17
using channel ORA_DISK_1
starting media recovery
media 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> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 465571384 bytes
Database Buffers 314572800 bytes
Redo Buffers 2596864 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oracle/app/orcl/secure01.dbf
RMAN> restore datafile 7 from tag='TAG20170624T084842';
Starting restore at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oracle/app/orcl/secure01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1
channel ORA_DISK_1: piece handle=/oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1 tag=TAG20170624T084842
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-JUN-17
RMAN> recover datafile 7 from tag='TAG20170624T084842';
Starting recover at 24-JUN-17
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/24/2017 09:00:19
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
datafile 7
ORA-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-17
using channel ORA_DISK_1
starting media recovery
media 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.
開啟，資料能否可查測試
結論：資料檔案可開啟，加密資料可查（當然了，錢夾也拷過去，錢夾密碼也知道）。
只複製資料檔案，錢夾沒有複製，加密資料不可查（模擬資料檔案被盜）
情景一：
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> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database 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]$ ls
books01.dbf control02.ctl redo02.log sysaux01.dbf temp01.dbf test_tde users01.dbf
control01.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]$ ls
25s4jm22_1_1 c-1471212201-20170617-00 c-1471212201-20170627-00 hc_orcl.dat orapworcl
27s4k331_1_1 c-1471212201-20170617-01 c-1471212201-20170627-01 hc_test.dat orapwtest
arch1_152_944363414.dbf c-1471212201-20170617-02 c-1471212201-20170627-02 init.ora snapcf_orcl.f
c-1471212201-20170519-00 c-1471212201-20170617-03 c-1471212201-20170627-03 initTEST.ora spfileorcl.ora
c-1471212201-20170519-05 c-1471212201-20170617-04 c-1471212201-20170630-00 lkORCL spfiletest.ora
c-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/d
dbs/ 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 TDE
DATA AES 192 bits key YES SHA-1
TEST SALT
NAME 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> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database 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 100
SYSAUX 600
UNDOTBS1 200
BOOKS 5
USERS 5
TEST 5
SYSTEM 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 99
SYSAUX 475.5
UNDOTBS1 189.5625
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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 50
SYSAUX 475.5
UNDOTBS1 2
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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);
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 3
SYSAUX 475.5
UNDOTBS1 1.625
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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
96M
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 100
SYSAUX 600
UNDOTBS1 505
BOOKS 5
USERS 5
TEST 5
SYSTEM 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 99
SYSAUX 475.5
UNDOTBS1 1.625
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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 91
SYSAUX 475.5
UNDOTBS1 2
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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 44
SYSAUX 475.5
UNDOTBS1 2
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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 83
SYSAUX 475.5
UNDOTBS1 23
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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.25
SYSAUX 600
UNDOTBS1 785
BOOKS 5
USERS 5
TEST 5
SYSTEM 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.25
SYSAUX 475.625
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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 100
SYSAUX 600
UNDOTBS1 990
BOOKS 5
USERS 5
TEST 5
SYSTEM 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 50
SYSAUX 475.625
UNDOTBS1 2
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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.5
SYSAUX 600
UNDOTBS1 1710
BOOKS 5
USERS 5
TEST 5
SYSTEM 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.5
SYSAUX 475.5625
BOOKS 3.875
USERS 3.9375
TEST 3.9375
SYSTEM 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_1
OPEN
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_SID
CLOSED
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 ，不能再該加密列上建立索引。如果需要建立索引，必須指定為 no salt 。
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/product/11.2.0/db_1
OPEN
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 欄位不能被加密
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_SID
test
[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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 465571384 bytes
Database Buffers 314572800 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string test
SQL> 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> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With 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 Production
With 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_SID
CLOSED
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_SID
OPEN
場景十五：資料庫檔案和自動開啟錢夾檔案被拷到另一臺資料庫上開啟測試
結論：相關資料檔案被複製到其他資料庫進行開啟，如果自動開啟錢夾也被拷過去，能查詢到被加密的資料。
[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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/$ORACLE_SID
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@ljw orcl]$ ls -rtl
total 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> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 448794168 bytes
Database Buffers 331350016 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/oracle/app/wallet/orcl
OPEN
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 需要承擔的風險。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23732248/viewspace-2777104/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle TDE（透明資料加密）Oracle加密
- 透明資料加密加密
- 2.9.2 透明資料加密加密
- oracle 透明加密Oracle加密
- openGauss-透明資料加密加密
- [zt]Oracle 10G：透明資料加密技術(TDE)Oracle 10g加密
- Oracle 10G 新特性--透明資料加密技術(TDE)Oracle 10g加密
- 資料加密 第六篇：透明檔案加密加密
- ORACLE透明加密安裝操作Oracle加密
- openGauss 設定透明資料加密_TDE加密
- ORACLE透明加密場景模擬Oracle加密
- Oracle Transparent Data Encryption 透明加密（一）Oracle加密
- Oracle Transparent Data Encryption 透明加密（二）Oracle加密
- oracle 11g高階安全元件測試透明資料加密（表空間加密）記錄Oracle元件加密
- oracle資料加密Oracle加密
- 拿走磁碟也甭想讀資料——透明加密保安全加密
- ORACLE資料加密(轉)Oracle加密
- Sqlserver關於TDE透明資料加密的使用總結SQLServer加密
- Oracle Data Redaction資料加密Oracle加密
- oracle 資料庫加加密。Oracle資料庫加密
- 大資料叢集被竊取資料怎麼辦？透明加密可以一試大資料加密
- SQLServer·最佳實踐·透明資料加密TDE在SQLServer的應用SQLServer加密
- Oracle透明閘道器訪問SQLServer資料庫OracleSQLServer資料庫
- Oracle透明閘道器訪問MySQL資料庫OracleMySql資料庫
- 【TUNE_ORACLE】Oracle資料庫與HugePages（四）如何禁用透明大頁Oracle資料庫
- 達夢加密之外部加密函式的半透明列加密加密函式
- Hadoop KMS配置(HDFS透明加密)Hadoop加密
- TDE 透明表空間加密 Wallet加密
- [重慶思莊每日技術分享]-oracle 12c透明加密Oracle加密
- Oracle對資料進行加密&解密處理Oracle加密解密
- 使用ssh tunnels加密連線oracle資料庫加密Oracle資料庫
- 資料加密加密
- oracle透明閘道器之異構資料庫的訪問Oracle資料庫
- OceanBase 安全審計之透明加密加密
- 【BASIS】業務資料透明表故障
- 【磐維資料庫】Oracle（透明閘道器）訪問磐維資料庫（PanWeiDB）資料庫Oracle
- 谷歌：2015年透明度報告 75%的資料已加密保護谷歌加密
- 文件透明加密軟體哪家好？window透明加密軟體產品如何對電子檔案加密？陝西風奧科技加密