Converting an encrypted non-CDB to a PDB requires the keystore passwords of the non-CDB and the target CDB. You can do it with AutoUpgrade, and you can upgrade in the same operation.

How To

The Oracle Database DB12 is encrypted and on Oracle Database 12.2. I want to upgrade, convert, and plug it into CDB2 on Oracle Database 19c.

1. Ensure that AutoUpgrade is version 22.2 or higher:

   $ java -jar autoupgrade.jar -version
   

2. Create your AutoUpgrade config file and set global.keystore as specified in a previous blog post:

   global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
   global.keystore=/u01/app/oracle/admin/autoupgrade/keystore
   	
   upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/DB12
   upg1.source_home=/u01/app/oracle/product/12.2.0.1
   upg1.target_home=/u01/app/oracle/product/19
   upg1.sid=DB12
   upg1.target_cdb=CDB2
   

3. Analyze:

   $ java -jar autoupgrade.jar -config DB12.cfg -mode analyze
   

4. The summary report warns me that TDE keystore passwords are needed:

   [Stage Name]    PRECHECKS
   [Status]        FAILURE
   [Start Time]    2022-03-29 12:42:32
   [Duration]       
   [Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/DB12/DB12/100/prechecks
   [Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/DB12/DB12/100/prechecks/db12_preupgrade.log
               Check failed for DB12, manual intervention needed for the below checks
               [TDE_PASSWORDS_REQUIRED]
   

   There are more details in the preupgrade log file:

   ==============
   BEFORE UPGRADE
   ==============
   
   	REQUIRED ACTIONS
   	================
   		1.  Perform the specified action ...
   		ORACLE_SID                      Action Required
   		------------------------------  ------------------------
   		CDB2                            Add TDE password
   		DB12                            Add TDE password
   

5. Add the TDE keystore passwords into the AutoUpgrade keystore:

   $ java -jar autoupgrade.jar -config DB12.cfg -load_password
   
   TDE> add DB12
   Enter your secret/Password:    
   Re-enter your secret/Password: 
   TDE> add CDB2
   Enter your secret/Password:    
   Re-enter your secret/Password: 
   

6. Save the passwords into the AutoUpgrade keystore. I choose to create an auto-login keystore:

   TDE> save
   Convert the keystore to auto-login [YES|NO] ? YES
   TDE> exit
   

7. Re-analyze the database:

   $ java -jar autoupgrade.jar -config DB12.cfg -mode analyze
   

8. If AutoUpgrade does not report any other problems, start the upgrade and conversion. Since I chose to create an AutoUpgrade auto-login keystore, I don’t have to provide the password when AutoUpgrade starts:

   $ java -jar autoupgrade.jar -config DB12.cfg -mode deploy
   

9. That’s it!

What Happens

• First, AutoUpgrade upgrades the database to Oracle Database 19c. This is a regular non-CDB database upgrade. It requires an auto-login keystore.
• After the upgrade, AutoUpgrade exports the encryption keys into a file. To avoid writing the encryption keys in clear text in the export file, the database needs a passphrase (transport secret) to encrypt the encryption key. AutoUpgrade generates a passphrase and stores it in the AutoUpgrade keystore. In addition, the database needs the keystore password. This is the WITH SECRET and IDENTIFIED BY clauses of the ADMINISTER KEY MANAGEMENT EXPORT KEYS statement.
• The encryption keys is imported into CDB$ROOT of the target CDB. To load the encryption keys from the export file, the database needs the passphrase and keystore password (of the target CDB). AutoUpgrade gets both password from the AutoUpgrade keystore. This is the WITH SECRET and IDENTIFIED BY clauses of the ADMINISTER KEY MANAGEMENT IMPORT KEYS statement.
• The pluggable database is created from the manifest file using CREATE PLUGGABLE DATABASE statement.
• AutoUpgrade executes the ADMINISTER KEY MANAGEMENT IMPORT KEYS statement again – this time while connected to the PDB itself.
• Finally, AutoUpgrade completes the PDB conversion by running noncdb_to_pdb.sql.

The encryption keys are imported two times – first in CDB$ROOT and then in the PDB itself. AutoUpgrade must import into CDB$ROOT if the PDB has any of the system tablespaces (SYSTEM or SYSAUX) or the undo tablespace encrypted.

Fallback

AutoUpgrade fallback functionality also works for an upgrade and PDB conversion. But there are a few requirements:

• A target_pdb_copy_option must be specified.
• The database must be Enterprise Edition.
• A guaranteed restore point must be created (default behavior).

It is not possible to revert the PDB conversion. To fall back the data files must be copied as part of the PDB conversion. You specify that the data files are copied by using the config file parameter target_pdb_copy_option. As an example, if I want to copy the data files during plug-in and generate OMF names, I use this parameter:

upg1.target_pdb_copy_option=file_name_convert=NONE

AutoUpgrade automatically creates a guaranteed restore point in the beginning of an upgrade. AutoUpgrade will issue a FLASHBACK DATABASE statement to revert the upgrade. The parameter restoration governs the creation of the restore point. The default value is YES, meaning AutoUpgrade creates a guaranteed restore point, and fallback is possible.

If all prerequisites are met, I can revert the entire operation and return the database to the original state (from 19c PDB back into a 12.2 non-CDB). 103 is the job id of the upgrade/PDB conversion:

$ java -jar autoupgrade.jar -config PDB1.cfg -restore -jobs 103

Other Blog Posts in This Series

• AutoUpgrade and Transparent Data Encryption (TDE)
• Upgrading an Encrypted Non-CDB or CDB
• Upgrading an Encrypted Non-CDB and Converting To PDB
• Upgrading an Encrypted PDB
• Future-proof Your Encrypted Database During Upgrade
• AutoUpgrade and Secure External Password Store Enables Complete Automation