New functionality superseeds the functionality mentioned in this blog post. Please use the method described in Upgrading an Encrypted Non-CDB and Converting To PDB.
Using AutoUpgrade, you can upgrade your encrypted Oracle Database and convert to a pluggable database. The process is not entirely automated, so you must handle the TDE encryption key manually.
A new parameter called skip_tde_key_import is introduced. Here is what the documentation says:
(Optional) The default is NO. You can use this option for non-CDB-to-PDB and unplug/plug operations. When set to YES, the upgrade is run, but import of the source database KeyStore into the target database is skipped, without raising an error. AutoUpgrade will leave the PDB open in upgrade mode, so that you can import the keys manually yourself. After you import the keys, you must then restart the database in normal mode.
In other words, AutoUpgrade does everything except import of the TDE encryption key.
How To
This is what happens:
AutoUpgrade handles the green things – you handle the yellow thing. My environment:
- Non-CDB called FTEX running on Oracle Database 11.2.0.4
- FTEX is encrypted with TDE and has an auto-login keystore
- CDB called CDB2 running on Oracle Database 19c
- CDB2 is prepared for TDE and has a keystore defined
First, I ensure that AutoUpgrade is version 21.2 or newer:
java -jar autoupgrade.jar -version
I create a config file for AutoUpgrade. Notice, that I have specified that the database is plugged into CDB2 using the parameter target_cdb. Also, notice that I have specified the new parameter skip_tde_key_import and set it to YES:
upg1.source_home=/u01/app/oracle/product/11.2.0.4
upg1.target_home=/u01/app/oracle/product/19
upg1.sid=FTEX
upg1.target_cdb=CDB2
upg1.skip_tde_key_import=YES
Then, I analyze my database (FTEX) for any issues:
java -jar autoupgrade.jar -config FTEX.cfg -mode analyze
No issues are detected, so I proceed with the upgrade:
java -jar autoupgrade.jar -config FTEX.cfg -mode deploy
After the upgrade and conversion to PDB, I log on to CDB2. I want to check the outcome. Has FTEX been upgraded and converted?
SQL> select name, open_mode, restricted from v$pdbs
NAME OPEN_MODE RESTRICTED
PDB$SEED READ ONLY NO
FTEX READ WRITE YES
Open – but restricted. I check for plug-in violations:
SQL> select cause, type, message, status, action
from pdb_plug_in_violations
where name = 'FTEX' AND status not in ('RESOLVED')
CAUSE TYPE MESSAGE STATUS ACTION
Wallet Key Needed ERROR PDB needs to import keys from source. PENDING Import keys from source.
OK. This is expected. AutoUpgrade does not handle the TDE encryption key. See the yellow part of the arrow above. You have to do that.
I start by merging the two keystores – the keystore of the old non-CDB database (FTEX) and the keystore of the CDB (CDB2). I merge the two keystores into the one of CDB2:
CDB$ROOT SQL> administer key management
merge keystore '/u01/app/oracle/admin/FTEX/wallet' identified by "oracle_4U"
into existing keystore '/u01/app/oracle/admin/CDB2/wallet/tde' identified by "oracle_4U"
with backup using 'merge_keystore';
Notice that I have specified the folders of keystores and not the actually file names. I have also specified the keystore password for both the keystores. And, finally, I tell the database to create a backup of the keystore before the merge (as a precaution – and I recommend always doing that).
Now, I switch to the FTEX PDB. I need to configure FTEX to use the TDE encryption key that is merged from the old keystore. Each key in the keystore has a key ID and I tell the PDB to use the right one. I explain later on how to determine the key ID:
CDB$ROOT SQL> alter session set container=FTEX;
FTEX SQL> administer key management
use key 'Ad8l2SYuf0/dv+6Au9tRmAIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
force keystore identified by "oracle_4U"
with backup using 'use_key_ftex';
Now, the PDB knows the TDE encryption key and you can actually start to query data from an encrypted tablespace. However, there is still a plug-in violation saying the TDE encryption key has to be imported. The PDB insists that the encryption key is imported – rather than merged into the keystore. So I will export the key from the PDB and import it again:
FTEX SQL> administer key management
export keys with secret "secret-passphrase"
to '/etc/oracle/exported-keys-ftex' force keystore identified by "oracle_4U";
FTEX SQL> administer key management
import keys with secret "secret-passphrase"
from '/etc/oracle/exported-keys-ftex' force keystore identified by "oracle_4U"
with backup using 'import_key_ftex';
FTEX SQL> host rm /etc/oracle/exported-keys-ftex
The encryption key is saved in an encrypted file in the OS. The file is encrypted using a passphrase (secret-passphrase), and you should pick a better one than I did. Finally – and important – I remove the file from the OS when I am done. I don’t want my encryption keys lying around in files all over the OS.
I restart the FTEX PDB:
FTEX SQL> alter pluggable database FTEX close immediate;
FTEX SQL> alter pluggable database FTEX open;
And the PDB is now open in READ WRITE mode and unrestricted
SQL> select name, open_mode, restricted from v$pdbs
NAME OPEN_MODE RESTRICTED
PDB$SEED READ ONLY NO
FTEX READ WRITE NO
I check for plug-in violations:
SQL> select cause, type, message, status, action
from pdb_plug_in_violations
where name = 'FTEX' AND status not in ('RESOLVED')
No rows selected.
Job done! Encrypted database upgraded and converted to a PDB.
Determine Key ID
Use the orapki utility to determine the key ID of the TDE encryption key. Specify the location of the old non-CDB keystore:
orapki wallet display -wallet /u01/app/oracle/admin/FTEX/wallet
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.Ad8l2SYuf0/dv+6Au9tRmAIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BUCsXzQga4wfJ8gNSuptAPMCAwAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
The TDE encryption key is starting with ORACLE.SECURITY.DB.ENCRYPTION and doesn’t end on MASTERKEY. In the above example the key ID is Ad8l2SYuf0/dv+6Au9tRmAIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.
If your non-CDB is Oracle Database 12c or later you can also get the key ID using a view:
SQL> select key_id from v$encryption_keys where key_use='TDE';
Why Do I Need to Export and Import?
Above you saw that I had to export and import the encryption keys, even after merging the keystores. You might ask: Why is that even needed? Why is the ADMINISTER KEY MANAGEMENT MERGE KEYSTORE command not enough?
That’s a very good question. I have reached out to our security people to get their take on it. It sounds like a bug to me.
The Future
The above solution makes it easier to handle encrypted databases. But we are not satisfied yet. A future version of AutoUpgrade will have even better support for encrypted databases, and everything will be automated.
The biggest issue for us is to find a safe way to handle the TDE keystore password. We need to have a safe way of getting the password from you and storing it in memory until we actually need it.
We are just about to start testing the very first version of AutoUpgrade with proper TDE support. If you are interested in becoming a beta tester, reach out to me to work something out.
Conclusion
Converting encrypted databases to PDB is now possible in AutoUpgrade. AutoUpgrade mainly handles the process, but you have to manage the TDE encryption keys yourself.
New functionality superseeds the functionality mentioned in this blog post. Please use the method described in Upgrading an Encrypted Non-CDB and Converting To PDB.
Databases Are Fun 