尝试在TDE环境下手工将本来为united mode的pdb转化为isolated mode的pdb

一个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这个参数又没办法修改,进入了死结。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值