如何unplug plug enable了tde的pdb
https://blog.dbi-services.com/unplug-an-encrypted-pdb-ora-46680-master-keys-of-the-container-database-must-be-exported/
In the Oracle Database Cloud DBaaS you provision a multitenant database where tablespaces are encrypted. This means that when you unplug/plug the pluggable databases, you also need to export /import the encryption keys. You cannot just copy the wallet because the wallet contains all CDB keys. Usually, you can be guided by the error messages, but this one needs a little explanation and an example.
Here I’ll unplug PDB6 from CDB1 and plug it into CDB2
[oracle@VM122 blogs]$ connect /@CDB1 as sysdba
SQLcl: Release 17.4.0 Production on Fri Jan 19 22:22:44 2018
Copyright © 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
22:22:46 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 PDB6 READ WRITE NO
Here are the master keys:
SQL> select con_id,tag,substr(key_id,1,6)||’…’ “KEY_ID…”,creator,key_use,keystore_type,origin,creator_pdbname,activating_pdbname from v$encryption_keys;
CON_ID TAG KEY_ID… CREATOR KEY_USE KEYSTORE_TYPE ORIGIN CREATOR_PDBNAME ACTIVATING_PDBNAME
1 cdb1 AcyH+Z... SYS TDE IN PDB SOFTWARE KEYSTORE LOCAL CDB$ROOT CDB$ROOT
3 pdb6 Adnhnu... SYS TDE IN PDB SOFTWARE KEYSTORE LOCAL PDB6 PDB6
Export keys and Unplug PDB
Let’s try to unplug PDB6:
22:22:51 SQL> alter pluggable database PDB6 close immediate;
Pluggable database PDB6 altered.
22:23:06 SQL> alter pluggable database PDB6 unplug into ‘/var/tmp/PDB6.xml’;
Error starting at line : 1 in command -
alter pluggable database PDB6 unplug into ‘/var/tmp/PDB6.xml’
Error report -
ORA-46680: master keys of the container database must be exported
This message is not clear. You don’t export the container database (CDB) key. You have to export the PDB ones.
Then, I have to open the PDB, switch to it, and export the key:
SQL> alter session set container=PDB6;
Session altered.
SQL> administer key management set keystore open identified by “k3yCDB1”;
Key MANAGEMENT succeeded.
SQL> administer key management
2 export encryption keys with secret “this is my secret password for the export”
3 to ‘/var/tmp/PDB6.p12’
4 identified by “k3yCDB1”
5 /
Key MANAGEMENT succeeded.
Note that I opened the keystore with a password. If you use an autologin wallet, you have to close it, in the CDB$ROOT, and open it with password.
Now I can unplug the database:
SQL> alter pluggable database PDB6 close immediate;
Pluggable database PDB6 altered.
SQL> alter pluggable database PDB6 unplug into ‘/var/tmp/PDB6.xml’;
Pluggable database PDB6 altered.
Plug PDB and Import keys
I’ll plug it in CDB2:
SQL> connect /@CDB2 as sysdba
Connected.
SQL> create pluggable database PDB6 using ‘/var/tmp/PDB6.xml’ file_name_convert=(’/CDB1/PDB6/’,’/CDB2/PDB6/’);
Pluggable database PDB6 created.
When I open it, I get a warning:
18:05:45 SQL> alter pluggable database PDB6 open;
ORA-24344: success with compilation error
24344. 00000 - “success with compilation error”
*Cause: A sql/plsql compilation error occurred.
*Action: Return OCI_SUCCESS_WITH_INFO along with the error code
Pluggable database PDB6 altered.
The PDB is opened in restricted mode and then I have to import the wallet:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
6 PDB6 READ WRITE YES
SQL> select name,cause,type,status,message,action from pdb_plug_in_violations;
NAME CAUSE TYPE STATUS MESSAGE ACTION
PDB6 Wallet Key Needed ERROR PENDING PDB needs to import keys from source. Import keys from source.
Then I open the destination CDB wallet and import the PDB keys into it:
SQL> alter session set container=PDB6;
Session altered.
SQL> administer key management set keystore open identified by “k3yCDB2”;
Key MANAGEMENT succeeded.
SQL> administer key management
2 import encryption keys with secret “this is my secret password for the export”
3 from ‘/var/tmp/PDB6.p12’
4 identified by “k3yCDB2”
5 with backup
6 /
Key MANAGEMENT succeeded.
Now the PDB can be opened for all sessions
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> alter pluggable database PDB6 close;
Pluggable database PDB6 altered.
SQL> alter pluggable database PDB6 open;
Pluggable database PDB6 altered.
Here is a confirmation that the PDB has the same key as the in the origin CDB:
SQL> select con_id,tag,substr(key_id,1,6)||’…’ “KEY_ID…”,creator,key_use,keystore_type,origin,creator_pdbname,activating_pdbname from v$encryption_keys;
CON_ID TAG KEY_ID… CREATOR KEY_USE KEYSTORE_TYPE ORIGIN CREATOR_PDBNAME ACTIVATING_PDBNAME
1 cdb2 AdTdo9... SYS TDE IN PDB SOFTWARE KEYSTORE LOCAL CDB$ROOT CDB$ROOT
4 pdb1 Adnhnu... SYS TDE IN PDB SOFTWARE KEYSTORE LOCAL PDB6 PDB6
#########################################################
unplug然后plug一个pdb，里面有TDE保护的数据的话，不同的oracle版本的处理方式应该是不一样的，上面的这种需要export 然后import key的方式应该是比较老的版本使用的方式，新的方式可以不用export key，然后再import key
我们先实验一下新的版本下，如何unplug一个数据库，然后plug这个数据库
07:19:44 SQL> create pluggable database tdetest3pdb10010 admin user pdbadmin identified by tdetest3;
Pluggable database created.
Elapsed: 00:00:04.77
07:20:27 SQL> alter session set container=tdetest3pdb10010;
Session altered.
因为这个pdb还没有master encryption key，所以下一步先建立master encryption key
07:25:24 SQL> administer key management set encryption key force keystore identified by "tdetest3" with backup;
keystore altered.
Elapsed: 00:00:00.90
07:25:55 SQL> select status,wrl_type from v$encryption_wallet;
STATUS WRL_TYPE
------------------------------ --------------------
OPEN FILE
Elapsed: 00:00:00.01
检查发现系统表空间都没有encrypt，我们将这些表空间都加密
undo表空间和temp表空间可以不用加密，因为如果存储数据的表空间加密后，那么undo和temp表空间上的数据也就自然加密了，temp表空间要想加密的话，需要新建加密的temp空间，然后删除现有的未加密的表空间
07:27:30 SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
UNDO_2 NO
Elapsed: 00:00:00.01
07:27:51 SQL> alter tablespace system encryption encrypt;
Tablespace altered.
Elapsed: 00:00:07.44
07:28:54 SQL> alter tablespace sysaux encryption encrypt;
Tablespace altered.
Elapsed: 00:00:10.97
07:33:51 SQL> alter tablespace undotbs1 encryption encrypt;
Tablespace altered.
Elapsed: 00:00:05.59
07:34:35 SQL> alter tablespace undo_2 encryption encrypt;
Tablespace altered.
Elapsed: 00:00:03.47
创建一个新的表空间，并且创建一个测试表
07:34:52 SQL> create bigfile tablespace tbs_big1 datafile size 1g;
Tablespace created.
Elapsed: 00:00:00.39
07:37:41 SQL> create user u1 identified by u1;
User created.
Elapsed: 00:00:00.09
07:37:53 SQL> alter user u1 default tablespace tbs_big1;
User altered.
Elapsed: 00:00:00.00
07:38:05 SQL> grant dba to u1;
Grant succeeded.
Elapsed: 00:00:00.04
07:38:11 SQL> create table u1.t_1 (a varchar(20));
Table created.
Elapsed: 00:00:00.50
07:38:25 SQL> insert into u1.t_1(a) values('wang20201224');
1 row created.
Elapsed: 00:00:00.01
07:38:38 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
07:38:40 SQL>
07:38:40 SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
TEMP NO
UNDO_2 YES
TBS_BIG1 YES
6 rows selected.
Elapsed: 00:00:00.00
下面开始unplug和plug 这个pdb
07:39:53 SQL> alter pluggable database tdetest3pdb10010 close immediate instances=all;
Pluggable database altered.
Elapsed: 00:00:01.59
AAA333这个密码是随便写的，在unplug的时候和plug的时候用同一个密码就行
07:43:43 SQL> alter pluggable database tdetest3pdb10010 unplug into '/u01/app/v1/tdetest3pdb10010.xml' encrypt using "AAA333";
Pluggable database altered.
Elapsed: 00:00:09.05
连接到另外一个数据库，然后再这个CDB上，做plug
[oracle@scaqai06adm07 bin]$ ./sqlplus "sys/tdetest2@tdetest2 as sysdba"
07:45:15 SQL> create pluggable database tetest2pdb10010 using '/u01/app/v1/tdetest3pdb10010.xml' copy decrypt using "AAA333";
Pluggable database created.
Elapsed: 00:00:07.56
07:50:07 SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
TEMP NO
UNDO_2 YES
TBS_BIG1 YES
6 rows selected.
Elapsed: 00:00:00.02
07:50:25 SQL> select * from u1.t_1;
A
--------------------
wang20201224
Elapsed: 00:00:00.08
可以发现这是可以plug成功的
用pdb后缀模式也是一样的，也经过测试，可以成功的
06:31:42 SQL> alter pluggable database tdetest3pdb10002 unplug into '/u01/app/v1/tdetest3pdb10002.pdb' encrypt using ccc555;
Pluggable database altered.
Elapsed: 00:00:38.46
06:43:33 SQL> create pluggable database tdetest2pdb10008 using '/u01/app/v1/tdetest3pdb10002.pdb' keystore identified by "WelCome-123#" decrypt using ccc555;
Pluggable database created.
Elapsed: 00:00:07.50
下面依照export import keys的方式做一遍
辅助性的步骤就不贴了，和上次的基本是一样的
unplug成xml这个方式做通了，unplug成pdb这种方式还没有做通
在source cdb端unplug
17:17:42 SQL> administer key management export keys with secret ddd333 to '/u01/app/v1/test.key' force keystore identified by tdetest3;
keystore altered.
Elapsed: 00:00:00.25
17:18:20 SQL> show con_name;
CON_NAME
------------------------------
TDETEST3PDB10099
17:18:44 SQL> alter session set container=cdb$root;
Session altered.
Elapsed: 00:00:00.00
17:19:01 SQL> alter pluggable database tdetest3pdb10099 close immediate instances=all;
Pluggable database altered.
Elapsed: 00:00:01.33
17:20:11 SQL> alter pluggable database tdetest3pdb10099 unplug into '/u01/app/v1/test.xml';
Pluggable database altered.
Elapsed: 00:00:05.23
在target cdb端plug，要先import keys，否则在plug时会报错ora-28374
17:20:42 SQL> create pluggable database tdetest2pdb10099 using '/u01/app/v1/test.xml' copy;
create pluggable database tdetest2pdb10099 using '/u01/app/v1/test.xml' copy
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
Elapsed: 00:00:00.27
17:21:13 SQL> administer key management import keys with secret ddd333 from '/u01/app/v1/test.key' identified by "WelCome-123#" with backup;
keystore altered.
Elapsed: 00:00:00.18
17:22:04 SQL> create pluggable database tdetest2pdb10099 using '/u01/app/v1/test.xml' copy;
Pluggable database created.
Elapsed: 00:00:06.41
export keys
08:10:06 SQL> administer key management export keys with secret "unplugtest" to '/u01/app/v1/tdetest3pdb10088.exp' force keystore identified by "tdetest3";
keystore altered.
Elapsed: 00:00:00.11
08:12:40 SQL> alter pluggable database tdetest3pdb10088 close immediate instances=all;
Pluggable database altered.
Elapsed: 00:00:01.43
将这个pdb unplug
08:15:11 SQL> alter pluggable database tdetest3pdb10088 unplug into '/u01/app/v1/tdetest3pdb10088.pdb';
Pluggable database altered.
Elapsed: 00:00:38.90
在plug时会报错，目前还不确定是不是因为export import key这种方式就不支持pdb这种方式，还是哪里有问题
17:52:10 SQL> create pluggable database tdetest2pdb10100 using '/u01/app/v1/hhhaaa.pdb';
create pluggable database tdetest2pdb10100 using '/u01/app/v1/hhhaaa.pdb'
*
ERROR at line 1:
ORA-19505: failed to identify file "/u01/app/v1/system.1774.1060019065"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
在没有配置TDE 的CDB之间unplug plug pdb是可以成功的，unplug成pdb文件，然后plug进去
18:58:05 SQL> alter pluggable database test88pdb10001 unplug into '/u01/app/v1/test88pdb10001.pdb';
Pluggable database altered.
Elapsed: 00:00:23.85
18:59:02 SQL> create pluggable database test99pdb10001 using '/u01/app/v1/test88pdb10001.pdb';
Pluggable database created.
Elapsed: 00:00:04.93
18:59:33 SQL> alter pluggable database test99pdb10001 open read write instances=all;
Pluggable database altered.
Elapsed: 00:00:09.59
19:00:00 SQL> alter session set container=test99pdb10001;
Session altered.
Elapsed: 00:00:00.00
19:00:11 SQL> select * from u1.t_1;
A
--------------------
ccc1057wang
Elapsed: 00:00:00.03
在CDB level export的key不能再pdb level import，否则会报错ora-46655
但是在pdb level export keys，然后再cdb level import是没有问题的，不会报错
17:34:39 SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
17:35:57 SQL> administer key management export keys with secret jjj999 to '/u01/app/v1/jjj999.key' force keystore identified by tdetest3;
keystore altered.
Elapsed: 00:00:00.14
17:32:18 SQL> show con_name;
CON_NAME
------------------------------
TDETEST2PDB10099
17:36:43 SQL> administer key management import keys with secret jjj999 from '/u01/app/v1/jjj999.key' identified by "WelCome-123#" with backup;
administer key management import keys with secret jjj999 from '/u01/app/v1/jjj999.key' identified by "WelCome-123#" with backup
*
ERROR at line 1:
ORA-46655: no valid keys in the file from which keys are to be imported
Elapsed: 00:00:00.06
17:37:22 SQL> alter session set container=cdb$root;
Session altered.
Elapsed: 00:00:00.00
17:37:39 SQL> administer key management import keys with secret jjj999 from '/u01/app/v1/jjj999.key' identified by "WelCome-123#" with backup;
keystore altered.
Elapsed: 00:00:00.21