一个pdb,本来模式为united mode的pdb,并且也已经创建了master encryption key,也已经有表和表空间由master encryption key加密了,这种情况下由united mode转化为isolated mode有一个命令为adminster key management isoalte mode可以进行转化。我们尝试手工转化一下,通过export keys和import keys是可以实现的
第一步首先创建一个pdb,然后将之配置为united mode
06:27:21 SQL> create pluggable database tdetest2pdb10895 admin user pdbadmin identified by tdetest2;
Pluggable database created.
Elapsed: 00:00:03.90
06:33:23 SQL> alter pluggable database tdetest2pdb10895 open read write instances=all;
Pluggable database altered.
Elapsed: 00:00:09.97
06:34:31 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
CLOSED UNITED
Elapsed: 00:00:00.01
06:34:49 SQL> administer key management set keystore open identified by "WelCome-123#";
keystore altered.
Elapsed: 00:00:00.09
06:35:31 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN_NO_MASTER_KEY UNITED
Elapsed: 00:00:00.00
06:35:52 SQL> administer key management set key identified by "WelCome-123#" with backup;
keystore altered.
Elapsed: 00:00:00.34
06:36:27 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN UNITED
Elapsed: 00:00:00.01
06:39:09 SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM YES
SYSAUX NO
UNDOTBS1 NO
TEMP NO
UNDO_2 NO
BIG_TBS1 YES
6 rows selected.
Elapsed: 00:00:00.03
到现在为止,测试pdb准备好了,既有表空间加密了,也有表加密了
我们下面开始尝试将这个pdb转化为isolated mode的pdb
第一步
06:39:26 SQL> show parameter tde_configuration;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tde_configuration string
06:40:38 SQL> alter system set tde_configuration='keystore_configuration=file' scope=both;
System altered.
Elapsed: 00:00:00.02
06:40:56 SQL> show parameter tde_configuration;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tde_configuration string keystore_configuration=file
06:41:12 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN ISOLATED
Elapsed: 00:00:00.01
现在模式已经显示为isolated mode了,我们下面就是尝试将encryption key从cdb$root的那个united keystore转移到PDB里的这个isolated mode的keystore
06:41:27 SQL> select guid from v$pdbs;
GUID
--------------------------------
B76AB92AE1E9D252E053D529850A6F77
Elapsed: 00:00:00.00
[oracle@scaqai06adm07 tdetest2]$ ls -al B76AB92AE1E9D252E053D529850A6F77
ls: cannot access B76AB92AE1E9D252E053D529850A6F77: No such file or directory
现在还没有创建isolated mode的keystore
创建isolated mode的keystore
06:43:06 SQL> administer key management create keystore identified by tdetest2pdb10895;
keystore altered.
Elapsed: 00:00:00.11
可以看到这个isolated mode的keystore已经创建了
[oracle@scaqai06adm07 tde]$ pwd
/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde
[oracle@scaqai06adm07 tde]$ ls
ewallet.p12
我尝试着建一个master encryption key,失败了
说密码不对
06:46:34 SQL> administer key management set key force keystore identified by tdetest2pdb10895 with backup;
administer key management set key force keystore identified by tdetest2pdb10895 with backup
*
ERROR at line 1:
ORA-46627: keystore password mismatch
Elapsed: 00:00:00.00
尝试着将united mode 的keystore里的关于这个pdb的那个key move到这个pdb的isolated mode keystore里
06:52:50 SQL> select key_id,activating_pdbname from v$encryption_keys where activating_pdbname='TDETEST2PDB10895';
KEY_ID ACTIVATING_PDBNAME
------------------------------------------------------------------------------ ------------------------------
AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDETEST2PDB10895
Elapsed: 00:00:00.11
这个move keys在这种场景下应该是不适用的
06:55:18 SQL> administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-46630: keystore cannot be created at the specified location
Elapsed: 00:00:00.00
报这个错误的原因是因为我们在前面已经创建了keystore
我们可以尝试删除这个keystore试试
[oracle@scaqai06adm07 tde]$ ls
ewallet.p12
[oracle@scaqai06adm07 tde]$ rm -f ewallet.p12
06:56:48 SQL> administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-28362: master key not found
Elapsed: 00:00:00.01
再次运行报这个错误
这个错误的原因应该是因为这个命令不能再pdb里运行,在pdb里运行肯定找不到master key了
06:58:08 SQL> alter session set container=cdb$root;
Session altered.
Elapsed: 00:00:00.00
06:59:14 SQL> administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-46631: keystore needs to be backed up
Elapsed: 00:00:00.02
06:59:27 SQL> administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' with backup;
administer key management move keys to new keystore '/u01/app/v1/tdetest2/B76AB92AE1E9D252E053D529850A6F77/tde' identified by "tdetest2pdb10895" from keystore identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' with backup
*
ERROR at line 1:
ORA-46688: cannot move a master key
Elapsed: 00:00:00.03
最后报的是不能move a master key,那就是说这种场景不适用于move keys这种方法
我们下面尝试export keys然后再import keys试试
06:59:55 SQL> administer key management export keys with secret unite_convert_isolate to '/u01/app/v1/unite_convert_isolate.key' identified by "WelCome-123#" with identifier in 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
keystore altered.
Elapsed: 00:00:00.02
在cdb里将这个key导出来,然后我们到pdb里尝试将这个key导进去
07:02:55 SQL> alter session set container=tdetest2pdb10895;
Session altered.
Elapsed: 00:00:00.00
因为刚才把那个keystore 文件删除掉了,我们再重新建一遍
07:03:46 SQL> administer key management create keystore identified by "tdetest2pdb10895";
keystore altered.
Elapsed: 00:00:00.16
07:07:30 SQL> administer key management import keys with secret unite_convert_isolate from '/u01/app/v1/unite_convert_isolate.key' identified by "tdetest2pdb10895" with backup;
administer key management import keys with secret unite_convert_isolate from '/u01/app/v1/unite_convert_isolate.key' identified by "tdetest2pdb10895" with backup
*
ERROR at line 1:
ORA-46627: keystore password mismatch
现在还报这种错误的话,应该还是尝试去再向cdb的那个united mode的keystore里import keys,我们尝试将这个pdb重启一下试试
Elapsed: 00:00:00.01
07:08:17 SQL> show con_name;
CON_NAME
------------------------------
TDETEST2PDB10895
重启pdb后还是报密码错误
07:08:25 SQL> alter pluggable database close immediate instances=all;
Pluggable database altered.
Elapsed: 00:00:00.90
07:09:52 SQL> alter pluggable database open read write instances=all;
Pluggable database altered.
Elapsed: 00:00:04.10
07:10:11 SQL> administer key management import keys with secret unite_convert_isolate from '/u01/app/v1/unite_convert_isolate.key' identified by "tdetest2pdb10895" with backup;
administer key management import keys with secret unite_convert_isolate from '/u01/app/v1/unite_convert_isolate.key' identified by "tdetest2pdb10895" with backup
*
ERROR at line 1:
ORA-46627: keystore password mismatch
Elapsed: 00:00:00.03
07:10:24 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN ISOLATED
Elapsed: 00:00:00.02
下面尝试重启CDB
[oracle@scaqai06adm07 bin]$ ./srvctl stop database -d tdetest2
[oracle@scaqai06adm07 bin]$ ./srvctl start database -d tdetest2
[oracle@scaqai06adm07 bin]$ ./sqlplus "sys/tdetest2@tdetest2 as sysdba"
07:14:05 SQL> administer key management set keystore open identified by "WelCome-123#";
keystore altered.
Elapsed: 00:00:00.07
因为没有配置auto login keystore,所以在重启后首先open cdb level的keystore
07:14:30 SQL> alter session set container=tdetest2pdb10895;
Session altered.
Elapsed: 00:00:00.01
07:15:29 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
CLOSED ISOLATED
Elapsed: 00:00:00.02
07:15:46 SQL> administer key management set keystore open identified by "tdetest2pdb10895";
keystore altered.
Elapsed: 00:00:00.06
07:16:07 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN_NO_MASTER_KEY ISOLATED
Elapsed: 00:00:00.00
重启cdb后,这个key可以import进去了
07:17:27 SQL> administer key management import keys with secret unite_convert_isolate from '/u01/app/v1/unite_convert_isolate.key' identified by "tdetest2pdb10895" with backup;
keystore altered.
Elapsed: 00:00:00.16
07:18:21 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN_NO_MASTER_KEY ISOLATED
Elapsed: 00:00:00.01
import key之后,状态还是显式没有master key,我们再尝试运行一下use key这个clause
导入这个key之后也能够查询到
07:20:40 SQL> select key_id,activating_pdbname from v$encryption_keys;
KEY_ID ACTIVATING_PDBNAME
------------------------------------------------------------------------------ ------------------------------
AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDETEST2PDB10895
Elapsed: 00:00:00.02
07:20:54 SQL> administer key management use key 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "tdetest2pdb10895" with backup;
administer key management use key 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "tdetest2pdb10895" with backup
*
ERROR at line 1:
ORA-28388: database is not open in read/write mode
Elapsed: 00:00:00.00
07:22:28 SQL> select name,open_mode from gv$pdbs;
NAME OPEN_MODE
-------------------- ----------
TDETEST2PDB10895 MOUNTED
TDETEST2PDB10895 MOUNTED
Elapsed: 00:00:00.02
07:22:47 SQL> alter pluggable database open read write instances=all;
Pluggable database altered.
Elapsed: 00:00:02.51
07:23:01 SQL> select name,open_mode from gv$pdbs;
NAME OPEN_MODE
-------------------- ----------
TDETEST2PDB10895 READ WRITE
TDETEST2PDB10895 READ WRITE
Elapsed: 00:00:00.03
07:23:17 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN_NO_MASTER_KEY ISOLATED
Elapsed: 00:00:00.01
将pdb起来后,use key clause也能运行成功
07:23:44 SQL> administer key management use key 'AdFwekajDU/Av0DMaYYDqJkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "tdetest2pdb10895" with backup;
keystore altered.
Elapsed: 00:00:00.36
这个isolated mode的keystore的状态显示为正常了
07:24:31 SQL> select status,keystore_mode from v$encryption_wallet;
STATUS KEYSTORE
------------------------------ --------
OPEN ISOLATED
Elapsed: 00:00:00.01
测试表里的数据也能够查询到
07:25:31 SQL> select * from u1.t_1;
A
--------------------
202012272238yudian
Elapsed: 00:00:00.02
07:26:08 SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM YES
SYSAUX NO
UNDOTBS1 NO
TEMP NO
UNDO_2 NO
BIG_TBS1 YES
6 rows selected.
Elapsed: 00:00:00.02
也能够看到表空间是有加密的
再重启一下cdb看看是不是成功了
重启cdb后发现无论是表空间加密还是表加密都没有问题
所以说通过设置tde_configuration以及export keys并import keys的方式是可以将pdb 由united mode方式转化为isolate mode方式的
此方式的效果和administer key management isolate keystore的效果是一样的,只是这种方式需要重启CDB。对于高可用性不好
我在测试过程当中遇到了一个问题,就是pdb里的参数tde_configuration已经修改为keystore_configuration=file但是master encryption key又没有成功的导入进去
这个时候的境况就是pdb也无法open,因为没有master encryption key
,因为pdb没法open,那个tde_configuration这个参数又没办法修改,进入了死结。