测试local clone和remote clone基于设置TDE的pdb

在一个配置了TDE 的cdb环境里，测试一下local clone和remote clone
Source CDB :tdetest2
Target CDB:tdetest3

先创建PDB tdetest2pdb10501

03:18:48 SQL> create pluggable database tdetest2pdb10501 admin user pdbadmin identified by tdetest2;

Pluggable database created.

Elapsed: 00:00:03.50

因为默认这个pdb的TDE没有配置master encryttion key，下面的步骤是创建master encryption key
03:20:18 SQL> alter session set container=tdetest2pdb10501;

Session altered.

Elapsed: 00:00:00.01
03:20:28 SQL> select status from v$encryption_wallet;

STATUS
------------------------------
CLOSED

Elapsed: 00:00:00.00
03:21:00 SQL> administer key management set keystore open identified by "WelCome-123#";

keystore altered.

Elapsed: 00:00:00.09
03:21:24 SQL> SELECT status from v$encryption_wallet;

STATUS
------------------------------
OPEN_NO_MASTER_KEY

Elapsed: 00:00:00.01
03:21:35 SQL> administer key management set key identified by "WelCome-123#" with backup;

keystore altered.

Elapsed: 00:00:00.56
03:22:06 SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

Elapsed: 00:00:00.01

local clone很简单，就是创建一个基于tdetest2pdb10501的pdb
03:31:44 SQL> create pluggable database tdetest2pdb10501_clone from tdetest2pdb10501;

Pluggable database created.

Elapsed: 00:00:08.67
新clone的pdb需要open keystore及open pdb
03:33:34 SQL> alter session set container=tdetest2pdb10501_clone;

Session altered.

Elapsed: 00:00:00.00
03:34:19 SQL> administer key management set keystore open identified by "WelCome-123#";

keystore altered.

Elapsed: 00:00:00.02
03:34:37 SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

Elapsed: 00:00:00.01
03:34:45 SQL> alter pluggable database open read write instances=all;

Pluggable database altered.

Elapsed: 00:00:07.40
03:35:12 SQL> select name,open_mode from gv$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
TDETEST2PDB10501_CLONE	       READ WRITE
TDETEST2PDB10501_CLONE	       READ WRITE

Elapsed: 00:00:00.02

新clone的pdb是和原来的pdb共享一个master encryption key的

03:36:36 SQL> alter session set container=cdb$root;

Session altered.

Elapsed: 00:00:00.00
03:36:45 SQL> select key_id,activating_pdbname from v$encryption_keys where activating_pdbname in ('TDETEST2PDB10501','TDETEST2PDB10501_CLONE');

KEY_ID									       ACTIVATING_PDBNAME
------------------------------------------------------------------------------ ------------------------------
ASJeSux/109bvyb1H9ddqKgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA			       TDETEST2PDB10501

Elapsed: 00:00:00.10

我们可以看到新的pdb是在v$encryption_keys查不到的，它和原来的pdb共享一个master encryption key

对新clone的pdb做rekey操作

06:18:48 SQL> connect sys/tdetest2@tdetest2pdb10501_clone as sysdba
Connected.
06:24:18 SQL> administer key management set key identified by "WelCome-123#" with backup;

keystore altered.

Elapsed: 00:00:00.85
06:32:09 SQL> alter session set container=cdb$root;

Session altered.

Elapsed: 00:00:00.00


06:33:28 SQL> select key_id,activating_pdbname from v$encryption_keys where activating_pdbname in ('TDETEST2PDB10501_CLONE','TDETEST2PDB10501');

KEY_ID									       ACTIVATING_PDBNAME
------------------------------------------------------------------------------ ------------------------------
ASJeSux/109bvyb1H9ddqKgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA			       TDETEST2PDB10501
AQqQPTCSWE+Yv2jPkzr2E0gAAAAAAAAAAAAAAAAAAAAAAAAAAAAA			       TDETEST2PDB10501_CLONE

现在就可以查到了，做了rekey操作之后，就可以查到了

06:13:21 SQL> create public database link tdetest2pdb10501_clone_link connect to pdbadmin identified by tdetest2 using 'tdetest2pdb10501_clone';

Database link created.

Elapsed: 00:00:00.05

06:24:34 SQL> create pluggable database tdetest3pdb10501_clone from tdetest2pdb10501_clone@tdetest2pdb10501_clone_link keystore identified by tdetest3;
create pluggable database tdetest3pdb10501_clone from tdetest2pdb10501_clone@tdetest2pdb10501_clone_link keystore identified by tdetest3
*
ERROR at line 1:
ORA-17628: Oracle error 46659 returned by remote Oracle server
ORA-46659: master keys for the given PDB not found


Elapsed: 00:00:00.39

错误ora-46659的原因是因为source pdb clone之后没有做rekey操作，解决这个错误有两种方法，一种就是做source pdb做rekey操作，另外一种就是在这个remote clone 后面加including shared key 子句

加including shared key子句会解决这个错误ora-46659

06:27:34 SQL> create pluggable database tdetest3pdb10501_clone from tdetest2pdb10501_clone@tdetest2pdb10501_clone_link keystore identified by tdetest3 including shared key;

Pluggable database created.

Elapsed: 00:00:11.43


或者对source pdb进行rekey操作后，也可以解决这个问题
在remote clone上指定的keystore密码是target cdb这边的keystore 密码

06:40:15 SQL> create pluggable database tdetest3pdb10501_clone2 from tdetest2pdb10501_clone@tdetest2pdb10501_clone_link keystore identified by tdetest3;

Pluggable database created.

Elapsed: 00:00:11.90

对新创建的pdb做rekey操作
remote clone的pdb就算不做rekey操作，也是可以在v$encryption_keys里面查到，但还是建议做rekey操作

06:44:08 SQL> administer key management set key force keystore identified by tdetest3 with backup;

keystore altered.

Elapsed: 00:00:00.99

06:25:28 SQL> create pluggable database tdetest3pdb10501_clone from tdetest2pdb10501_clone@tdetest2pdb10501_clone_link keystore identified by tdetest3 including shared key;
create pluggable database tdetest3pdb10501_clone from tdetest2pdb10501_clone@tdetest2pdb10501_clone_link keystore identified by tdetest3 including shared key
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file +D001/TDETEST2/B7471E8E84EE142CE053D629850A1E83/DATAFILE/sysaux.1898.1060054345
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server


Elapsed: 00:00:03.90

上面这个错误的原因是因为连接串没有在所有的node上配置，需要将连接串在所有的node上配置