oracle TDE使用

环境：

OS:Centos 6

DB:11.2.0.4 3节点组成的rac环境

1.先要创建一个"wallet钱包",这个钱包里面保存着密钥,Oracle就是通过这个密钥对列进行加密和解密的.
在其中一个节点上操作,我这里是在节点1上操作
su - oracle
[oracle@rac01 ~]$ mkdir $ORACLE_BASE/wallet
[oracle@rac01 ~]$ cd $ORACLE_BASE/wallet
[oracle@rac01 wallet]$ pwd
/u01/oracle/app/wallet

2.生成wallet钱包之前先要设定wallet钱包的保存位置
这里是在oracle账号下的sqlnet.ora,而不是grid用户下的sqlnet.ora
设置wallet钱包位置的文件$ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=(
SOURCE=(
(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/u01/oracle/app/wallet))
)
)

3.数据库里创建wallet并且设置访问密码 
SQL> connect / as sysdba
alter system set encryption key authenticated by "123456";

4.创建带有加密列的表，插入相关数据
connect hxl/oracle
create table hxl.en_test (id int,name varchar(20),salary number(10,2) encrypt using 'AES256');
insert into hxl.en_test values(1,'hwb',50000.00);
insert into hxl.en_test values(2,'hwb2',30000.00);
commit;

5.设置了密码后可以手动打开或者关闭wallet
打开wallet
alter system set encryption wallet open authenticated by "123456";

关闭wallet
alter system set wallet close identified by "123456";

6.关闭wallet查看加密列
如果有查询权限,不加密的列可以查询,加密的无法查询,必须打开wallet才可以
SQL> alter system set wallet close identified by "123456";
SQL> select wrl_type,wrl_parameter,status from gv$encryption_wallet;
SQL> desc hxl.en_test;
SQL> select id,name from hxl.en_test;
SQL> select SALARY from hxl.en_test;

7.打开wallet,可以正常访问加密列
SQL> alter system set encryption wallet open authenticated by "123456";
SQL> select SALARY from hxl.en_test;

8.数据字典 查看那些列被加密
select * from dba_encrypted_columns;

#################################RAC环境#############################
9.rac环境尝试在另外的节点上查询该表
SQL> select * from hxl.en_test;
select * from hxl.en_test
*
ERROR at line 1:
ORA-28365: wallet is not open

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

另外的节点也需要配置钱包

节点2:
su - oracle
[oracle@rac01 ~]$ mkdir $ORACLE_BASE/wallet
[oracle@rac01 ~]$ cd $ORACLE_BASE/wallet

将节点1上的钱包文件拷贝到节点2相应的目录
su - oracle
scp /u01/oracle/app/wallet/ewallet.p12 [email protected]:/u01/oracle/app/wallet/

同样生成wallet钱包之前先要设定wallet钱包的保存位置
设置wallet钱包位置的文件$ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=(
SOURCE=(
(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/u01/oracle/app/wallet))
)
)

重新登录查询
SQL>connect / as sysdba
SQL>alter system set encryption wallet open authenticated by "123456";
SQL> select * from hxl.en_test;

ID NAME SALARY
---------- -------------------- ----------
1 hwb 50000
2 hwb2 30000

节点3的做法与节点2一样

###############expdp/impdp验证###########################

expdp导出测试

钱包关闭的情况下(非加密的表会正常导出)
SQL> alter system set wallet close identified by "123456";

expdp system/oracle tables=hxl.en_test dumpfile=en_test_wallet.dmp directory=DATA_PUMP_DIR
会报如下的错误:
ORA-31693: Table data object "HXL"."EN_TEST" 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.

钱包打开的情况
SQL> alter system set wallet open identified by "123456";

expdp system/oracle tables=hxl.en_test dumpfile=en_test_wallet_open.dmp directory=DATA_PUMP_DIR
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HXL"."EN_TEST" 5.882 KB 2 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************

这个时候可以导出成功

导入测试
我们这里导入使用成功导出的文件进行导入
钱包关闭的情况下
SQL> alter system set wallet close identified by "123456";
SQL> drop table hxl.en_test;

impdp system/oracle directory=data_pump_dir dumpfile=en_test_wallet_open.dmp tables=hxl.en_test
导入提示:
ORA-39083: Object type TABLE:"HXL"."EN_TEST" failed to create with error:
ORA-28365: wallet is not open

钱包打开的情况下
SQL> alter system set wallet open identified by "123456";

impdp system/oracle directory=data_pump_dir dumpfile=en_test_wallet_open.dmp tables=hxl.en_test
这个还是导入成功:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HXL"."EN_TEST" 5.882 KB 2 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 18 15:48:42 2023 elapsed 0 00:00:01

###############rman验证###########################
钱包关闭的情况
SQL> alter system set wallet close identified by "123456";

run
{
allocate channel ch1 device type disk;
backup as compressed backupset full filesperset 5 database format '+REDO';
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '+REDO';
backup current controlfile format '+REDO';
backup spfile format '+REDO';
release channel ch1;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
}

这种方式会自动生成如下目录的备份集,即所谓的omf管理
piece handle=+REDO/slnngk/backupset/2023_10_18/nnndf0_tag20231018t160353_0.358.1150560233 tag=TAG20231018T160353 comment=NONE

这种方式的备份可读性不强,我们可以自己创建一个备份目录,文件名按照特定格式生成,脚本如下
run
{
allocate channel ch1 device type disk;
backup as compressed backupset full filesperset 5 database format '+REDO/rman_backup/db_fullbackup_%d_%s_%p_%T';
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '+REDO/rman_backup/arch_%d_%s_%p_%T';
backup current controlfile format '+REDO/rman_backup/ctl_%d_%s_%p_%T';
backup spfile format '+REDO/rman_backup/spfile_%d_%s_%p_%T';
release channel ch1;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
}

前提需要创建目录+REDO/rman_backup

[grid@rac02 ~]$ asmcmd
ASMCMD> cd redo
ASMCMD> mkdir rman_backup
ASMCMD> cd rman_backup
ASMCMD> pwd
+redo/rman_backup

asm查看备份文件,我们自己命名的文件其实是一个连接符,指向omf具体的文件
ASMCMD> ls -al
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'

Type Redund Striped Time Sys Name
N arch_slnngk_26_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/annnf0_TAG20231018T161144_0.491.1150560705
N ctl_slnngk_27_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/ncnnf0_TAG20231018T161146_0.363.1150560707
N db_fullbackup_slnngk_23_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/nnndf0_TAG20231018T161039_0.436.1150560639
N db_fullbackup_slnngk_24_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/nnndf0_TAG20231018T161039_0.392.1150560665
N spfile_slnngk_28_1_20231018 => +REDO/SLNNGK/BACKUPSET/2023_10_18/nnsnf0_TAG20231018T161148_0.494.1150560709
ASMCMD>

说明:使用wallet对rman不会有影响

#####################开启自动打开钱包##########################
在命令行模式先打开钱包
su - oracle
SQL>connect / as sysdba
SQL> alter system set wallet open identified by "123456";

然后使用oracle账号登录图形界面,输入owm

选择auto login后点击保存。