Do I Need To Disable the Scheduler During Upgrade?

I was asked a question the other day:

When upgrading an Oracle Database, do we need to disable the scheduler (DBMS_SCHEDULER)?

The short answer is: No …. or perhaps.

What Happens During Analyze

When you use AutoUpgrade in Analyze mode (java -jar autoupgrade.jar -mode analyze), it will check your database. It is a non-intrusive check, and normal operations can continue, including use of the scheduler.

What Happens During Deploy

When downtime starts, and you are ready to upgrade your database, you start AutoUpgrade in Deploy mode (java -jar autoupgrade.jar -mode deploy).

Analyze And Fixups

First, AutoUpgrade will re-analyze the database, and based on the findings; it will run pre-upgrade fixups. The fixups make changes to the database, like gathering dictionary statistics, emptying recycle bin and other administrative tasks. The scheduler remains active during this period, so if you have any jobs that do administrative things on the database, like gathering statistics, there is a chance that they will collide. But typically not a problem.

Upgrade

Then the actual upgrade of the database can start. This happens while the database is started in upgrade mode (STARTUP UPGRADE)

When the database is started in upgrade mode, many things are disabled automatically. The scheduler being one of them.

Examples of other changes that happen in upgrade mode:

  • System triggers are disabled
  • Certain parameters are changed
  • Resource Manager is disabled

You can check the alert log for more information. Here is a snippet:

2022-05-17T11:56:54.585122+02:00
AQ Processes can not start in restrict mode

Post-Upgrade

After the actual upgrade, the database is restarted in normal mode. The scheduler becomes enabled again.

In this phase, AutoUpgrade is recompiling invalid objects and performing post-upgrade fixups. Changes will be made to the database, like re-gathering dictionary statistics. Similar to the pre-upgrade fixups, depending on the nature of your scheduler jobs, there is a risk of things colliding. That can cause waits or concurrency issues.

Finally, the time zone file is upgraded. This process requires the database to be started in upgrade mode again. Again, the scheduler will be automatically disabled.

What Is The Answer?

From a functional point of view the scheduler is enabled and working during some parts of an upgrade. Only during the most critical parts is it automatically disabled.

So, the answer is: No, you do not need to disable the scheduler during upgrade. The database will automatically disable it when needed.

But the database is restarted multiple times which of course will affect any running scheduler jobs. Depending on the nature of your scheduler jobs, you might decide to disable it completely during the entire database upgrade. For instance, if you have long-running jobs or jobs that are sensitive to being interrupted. On the other hand, if your jobs are short-running, restart easily, or you basically don’t care, then it is perfectly fine to leave it all running during a database upgrade.

Manually Disable The Scheduler

If you decide to disable the scheduler manually, you should temporarily change job_queue_processes:

SQL> alter system set job_queue_processes=0 scope=both;

Don’t forget to set it to the original value after the upgrade.

You can find more information in MOS note How to disable the scheduler using SCHEDULER_DISABLED attribute in 10g (Doc ID 1491941.1).

Upgrade Mode

A few more words about upgrade mode:

When you start Oracle Database in upgrade mode, you can only run queries on fixed views. If you attempt to run other views or PL/SQL, then you receive errors.

When the database is started in upgrade mode, only queries on fixed views execute without errors. This restriction applies until you either run the Parallel Upgrade Utility (catctl.pl) directly, or indirectly by using the dbupgrade script). Before running an upgrade script, using PL/SQL on any other view, or running queries on any other view returns an error.

About Starting Oracle Database in Upgrade Mode, Upgrade Guide 19c

Starts the database in OPEN UPGRADE mode and sets system initialization parameters to specific values required to enable database upgrade scripts to be run. UPGRADE should only be used when a database is first started with a new version of the Oracle Database Server.

When run, upgrade scripts transform an installed version or release of an Oracle database into a later version, for example, to upgrade an Oracle9i database to Oracle Database 10g. Once the upgrade completes, the database should be shut down and restarted normally.

12.46 STARTUP, User’s Guide and Reference 19c

AutoUpgrade and Secure External Password Store Enables Complete Automation

Many commands that involve Transparent Data Encryption (TDE) require inputting the TDE keystore password. Also, when you use AutoUpgrade. on an encrypted Oracle Database you probably need to store the TDE keystore password using the -load_password option.

Manually inputting passwords is unsuitable for an environment with a high degree of automation. In Oracle Database it is solved by Secure External Password Store (SEPS) (as of Oracle Database 12.2). In a previous blog post, I showed how you could use it to your advantage.

This blog post is about how to use AutoUpgrade together with SEPS.

Good News

As of version 22.2 AutoUpgrade fully supports Oracle Database with a Secure External Password Store. If SEPS contains the TDE keystore password, you don’t have to input the password using the -load_password option.

If you are using AutoUpgrade in some sort of automation (like Ansible), you should look into SEPS. AutoUpgrade can use SEPS when the TDE keystore password is needed, and you can upgrade and convert completely unattended.

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 your Oracle Databases DB12 and CDB2 are properly configured with a Secure External Password Store and it contains the TDE keystore password.
  2. Ensure that AutoUpgrade is version 22.2 or higher:
    $ java -jar autoupgrade.jar -version
    
  3. 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
    
  4. Analyze:
    $ java -jar autoupgrade.jar -config DB12.cfg -mode analyze
    
  5. The summary report tells me everything is fine; just go ahead. I don’t need to input the TDE keystore passwords:
    [Stage Name]    PRECHECKS
    [Status]        SUCCESS
    [Start Time]    2022-03-30 10:28:38
    [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 passed and no manual intervention needed
    
  6. Optionally, I can use the -load_password prompt to check the TDE configuration:
    $ java -jar autoupgrade.jar -config DB12.cfg -load_password
    	
    TDE> list
    +----------+---------------+------------------+-----------+------------------+
    |ORACLE_SID|Action Required|      TDE Password|SEPS Status|Active Wallet Type|
    +----------+---------------+------------------+-----------+------------------+
    |      CDB2|               |No password loaded|   Verified|               Any|
    |      DB12|               |No password loaded|    Unknown|        Auto-login|
    +----------+---------------+------------------+-----------+------------------+
    
    Action Required is empty and verifies that I don’t need to input the TDE keystore passwords. AutoUpgrade checked SEPS in CDB2 and found that it works. It is impossible to check SEPS in DB12 because it is on Oracle Database 12.2. The functionality was added in Oracle Database 19c.
  7. Start the upgrade and conversion:
    $ java -jar autoupgrade.jar -config DB12.cfg -mode deploy
    
  8. That’s it!

What Happens

  • You must configure an AutoUpgrade keystore. Even though you are not loading any TDE keystore passwords, it is still required. Some commands require a passphrase (or transport secret) and AutoUpgrade must store them in its keystore.
  • Whenever a database is using SEPS, and a TDE keystore password is required, AutoUpgrade will use the IDENTIFIED BY EXTERNAL STORE clause.

What Else

You can mix and match. If only one database uses SEPS, you can input the other TDE keystore password manually using the -load_password option. AutoUpgrade will check your database configuration and ask only for the needed TDE keystore passwords.

Other Blog Posts in This Series

Upgrading an Encrypted Non-CDB and Converting To PDB

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

Upgrading an Encrypted PDB

An unplug-plug upgrade of an encrypted PDB requires the keystore password of the source and target CDB, and you can do it with AutoUpgrade.

How To

The pluggable database PDB1 is encrypted and is plugged into CDB1, which is Oracle Database 12.2. I want to upgrade the PDB to Oracle Database 19c by plugging it into CDB2.

  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/PDB1
    upg1.source_home=/u01/app/oracle/product/12.2.0.1
    upg1.target_home=/u01/app/oracle/product/19
    upg1.sid=CDB1
    upg1.target_cdb=CDB2
    upg1.pdbs=PDB1
    
  3. Analyze:
    $ java -jar autoupgrade.jar -config PDB1.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 07:58:52
    [Duration]       
    [Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/PDB1/CDB1/100/prechecks
    [Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/PDB1/CDB1/100/prechecks/cdb1_preupgrade.log
    		Check failed for PDB1, 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
    		------------------------------  ------------------------
    		CDB1                            Add TDE password
    		CDB2                            Add TDE password
    
  5. Add the TDE keystore passwords into the AutoUpgrade keystore:
    $ java -jar autoupgrade.jar -config PDB1.cfg -load_password
    
    TDE> add CDB1
    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 PDB:
    $ java -jar autoupgrade.jar -config PDB1.cfg -mode analyze
    
  8. If AutoUpgrade does not report any other problems, start the unplug-plug upgrade. 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 PDB1.cfg -mode deploy
    
  9. That’s it!

What Happens

  • When AutoUpgrade needs to unplug the encrypted PDB into a manifest file, the source CDB will need the TDE keystore password. AutoUpgrade can get it from its keystore. This is the IDENTIFIED BY clause of the ALTER PLUGGABLE DATABASE ... UNPLUG INTO statement.
  • The encryption keys of the PDB go into the manifest file. The database doesn’t want to write the encryption keys in clear text in the manifest file and asks for a passphrase that can encrypt the encryption keys. AutoUpgrade generates a passphrase and stores the passphrase in the AutoUpgrade keystore. This is the ENCRYPT USING clause of the ALTER PLUGGABLE DATABASE ... UNPLUG INTO statement.
  • When the PDB plugs into the target CDB, the target CDB will need the TDE keystore password. This is the IDENTIFIED BY clause of the CREATE PLUGGABLE DATABASE ... USING statement.
  • The database must get the encryption keys of the PDB from the manifest files. The encryption keys are encrypted using a passphrase. The database asks AutoUpgrade about the passphrase which is stored in the AutoUpgrade keystore. This is the DECRYPT USING clause of the CREATE PLUGGABLE DATABASE ... USING statement.

Fallback

AutoUpgrade fallback functionality also works on an encrypted PDB. When it comes to unplug-plug upgrades and fallback capability, it is a requirement that the data files were copied as part of the upgrade process.

In the above example, a fallback using AutoUpgrade would not be possible. Since I did not specify a target_pdb_copy_option the data files were re-used. Other means of falling back to the original state is needed.

Had I specified a target_pdb_copy_option in my config file, a fallback would be possible. In the below example, I am specifying a copy option. file_name_convert=none means that the data files are copied and new OMF names are generated:

upg1.target_pdb_copy_option=file_name_convert=NONE

In this case, I can revert the unplug-plug upgrade and return to the original state using AutoUpgrade. 103 is the job id of the upgrade:

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

Isolated Keystore Mode

In CDBs the default way of storing TDE encryption keys is in a united keystore. The CDB has one keystore and all PDBs store their encryption keys in that one keystore.

With Oracle Database 19.14 a new option became possible: isolated keystore. The CDB still has a keystore that PDBs can use. But you can also configure each individual PDB to use its own keystore.

Currently, AutoUpgrade does not support isolated keystore mode. But we are working on it.

Other Blog Posts in This Series

AutoUpgrade and Transparent Data Encryption (TDE)

It is now easier to upgrade and convert your encrypted Oracle Database. The latest version of AutoUpgrade adds much better support for Oracle Databases that are encrypted with Transparent Data Encryption (TDE).

You must ensure that you are using the latest version of AutoUpgrade. You can download it from My Oracle Support AutoUpgrade Tool (Doc ID 2485457.1). At the time of writing, the latest version of AutoUpgrade is 22.2:

$ java -jar autoupgrade.jar -version
build.version 22.2.220324

AutoUpgrade Keystore

Dealing with TDE, also means dealing with sensitive information. AutoUpgrade must adequately protect the TDE keystore passwords. To do so, AutoUpgrade can have its own keystore to store sensitive information, i.e., TDE keystore passwords. Whenever a TDE keystore password is needed, e.g., during an unplug-plug upgrade of an encrypted PDB, it can get the password from the AutoUpgrade keystore.

You need to tell AutoUpgrade where it can create the keystore. You do so in the config file:

global.keystore=/etc/oracle/keystores/autoupgrade/DB12

When you start to use the AutoUpgrade keystore the following files are created in the directory:

$ pwd
/etc/oracle/keystores/autoupgrade/DB12

$ ll
-rw-------. 1 oracle dba 765 Mar 28 14:56 cwallet.sso
-rw-------. 1 oracle dba 720 Mar 28 14:56 ewallet.p12

It is similar to other keystores that Oracle Database use. ewallet.p12 is the keystore, and cwallet.sso is an auto-login keystore used to open the real keystore. You don’t have to create an auto-login keystore.

You should protect the AutoUpgrade keystore files like you protect any other Oracle Database keystore:

  • Apply restrictive file system permissions.
  • Audit access.
  • Back it up.

Using the Keystore

Create your AutoUpgrade config file and specify global.keystore as described above. Start an interactive prompt that allows you to add the necessary passwords:

$ java -jar autoupgrade.jar -config DB12.cfg -load_password

The first time you use the AutoUpgrade keystore, you must provide a password that protects the AutoUpgrade keystore:

Starting AutoUpgrade Password Loader - Type help for available options
Creating new keystore - Password required
Enter password:       
Enter password again: 
Keystore was successfully created

In the TDE console, the following commands are available:

  • add
  • delete
  • list
  • save
  • help
  • exit

The SID references the databases. If you want to add a TDE password for the database DB12, use the following command:

TDE> add DB12
Enter your secret/Password:    
Re-enter your secret/Password: 
TDE> add CDB2
Enter your secret/Password:    
Re-enter your secret/Password: 

If you want to delete the TDE password for DB12:

TDE> delete DB12

Keystore Password is required prior to operation
Enter wallet password:   

When you save the passwords into the AutoUpgrade keystore, you must decide whether you want to have an auto-login keystore:

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

I recommend using auto-login keystores. If you do not create an AutoUpgrade auto-login keystore, you will be prompted for the AutoUpgrade keystore password when you start AutoUpgrade. If you want to use AutoUpgrade in noconsole mode (-noconsole), then an auto-login keystore is required.

I will show how to upgrade and convert encrypted databases in later blog posts.

Loss of AutoUpgrade Keystore

What happens if your AutoUpgrade keystore is lost? This is fairly simple. You can re-create the keystore and load all passwords into it using the load_password command line option as described above.

Preupgrade Checks

We have added new preupgrade checks to the analyze phase in AutoUpgrade. These checks will help you to provide the needed passwords and ensure your TDE configuration meets certain standards:

  • auto_login_keystore_required
  • keystore_conflict
  • no_keystore_files
  • tde_passwords_required
  • wallet_root
  • tde_in_use
  • oracle_home_keystore

You can read more about these checks in MOS note Database Preupgrade tool check list. (Doc ID 2380601.1).

Further Reading

Other Blog Posts in This Series

Get Started with Autoupgrade

If you never upgraded a database or it has been a while since you did it, I suggest that you get familiar with AutoUpgrade. Other methods of upgrading still exist, but AutoUpgrade is the only recommended method!

How To

AutoUpgrade is a tool that comes in a single file named autoupgrade.jar. You find it in your Oracle Home in $ORACLE_HOME/rdbms/admin. You should always download the latest version of AutoUpgrade from My Oracle Support and put it into your Oracle Home, thus overwriting the existing file.

AutoUpgrade is fully backward compatible, and a newer version of AutoUpgrade can upgrade databases to a previous version. In this example, AutoUpgrade is version 21.3.211115, but notice the information in build.supported_target_versions:

$ java -jar autoupgrade.jar -version

build.hash 081e3f7
build.version 21.3.211115
build.date 2021/11/15 11:57:54
build.max_target_version 21
build.supported_target_versions 12.2,18,19,21
build.type production

Version 21 of AutoUpgrade can upgrade your database to Oracle Database 21c and previous releases.

Now, you are ready to analyze your Oracle Database for upgrade readiness and eventually upgrade it. This short YouTube video explains the process.

Finally, you can watch a short demo of a database upgrade.

Try It

But the best way to learn is to do it yourself. You can use our Hands-On Lab for this purpose. You can find an overview of the lab and the lab instructions on Mike Dietrich’s blog.

You can run the lab in two ways.

VirtualBox image

The Hands-On Lab comes as a self-contained VirtualBox image that you download and run on your own computer. It requires around 100 GB of disk space and a fairly modern computer. Nothing fancy, but it doesn’t run smoothly on arcane hardware. Get started here.

LiveLabs

You can run the entire lab in just a browser using Oracle LiveLabs. You can do it in our Cloud Free Tier so that it will be completely free. Our workshop on Oracle LiveLabs is called Hitchhiker’s Guide for Upgrading to Oracle Database 19c.

Watch this video and learn how to provision a lab in Oracle LiveLabs.

Guided Tour

If you get stuck in the lab or just want to watch and let Mike Dietrich do all the typing, you can watch this recorded session of the complete hands-on lab.

Further Reading

Once done with the lab, you can start on these additional ressources:

Good luck!

P.S. Remember – it is better to fail in our lab than in production…

How To Upgrade An Encrypted Oracle Database and Convert to PDB

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: Overview of upgrading to Oracle Database 19c of database encrypted with TDE and convert to a PDB

AutoUpgrade handles the green things – you handle the yellow thing. My environment:

  1. Non-CDB called FTEX running on Oracle Database 11.2.0.4
  2. FTEX is encrypted with TDE and has an auto-login keystore
  3. CDB called CDB2 running on Oracle Database 19c
  4. 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.

Upgrade with Less Downtime

You can upgrade your Oracle Database with less downtime. With the release of AutoUpgrade 21.2 we added the fast deploy option. It allows you to run the preupgrade fixups while the database is still online. Only the actual upgrade will now require downtime.

The Usual Upgrade

When you upgrade the usual way, i.e., using AutoUpgrade in deploy mode, then you:

  1. Analyze the database – java -jar autoupgrade.jar -mode analyze This is non-intrusive and doesn’t change anything on the database. You get a report, and you can fix any issue that AutoUpgrade can’t fix for you.
  2. Upgrade the database – java -jar autoupgrade.jar -mode deploy The database is analyzed again. Based on the findings of the analysis, a set of preupgrade fixups are executed. Finally, the database upgrade takes place.

Best practice is to use AutoUpgrade in deploy mode

During step #2, the database is not available – you have downtime. Even though the database was analyzed previously, it will be re-analyzed to catch any new issues. This is the safest approach. From the first analysis (in step #1) to the second analysis (in step #2), potentially new issues can occur.

We talked to a few of our customers who are really trying to reduce the downtime of an upgrade. For them – every second counts and has a significant impact on their business. They asked us to make the upgrade faster.

Fast Deploy Mode

One customer suggested moving the final analysis and preupgrade fixup outside of the downtime window. Shortly before the downtime would start, they would like to run an analysis and the preupgrade fixups. Next, they would wait until the downtime window starts and do only the upgrade.

I must stress; there is a risk that a new issue pops up between the fixups and the upgrade – but this one customer is willing to take the risk. In exchange, they will get upgrades with less downtime.

We call this approach fast deploy. The procedure is:

  1. Analyze the database – java -jar autoupgrade.jar -mode analyze
  2. Run the preupgrade fixups – java -jar autoupgrade.jar -mode fixups
  3. Now downtime starts
  4. Upgrade the database – java -jar autoupgrade.jar -mode upgrade

To reduce downtime use the new fast deploy mode - with increased risk

Fast deploy requires AutoUpgrade 21.2 or newer. The target database release can be any of the target releases already supported by AutoUpgrade, meaning 12.2 or higher.

Here are the details of the feature – from the AutoUpgrade change log:

This feature enables DBA’s to run the prechecks and prefixups while the database is still online and then run deploy to complete the upgrade. Once the fixups have been run on the source database, DBA can then skip the prechecks and prefixup stages and proceed directly to the phases that follow for example: Database can be online: java -jar autoupgrade.jar -mode fixups -config yourconfig Upgrade time: java -jar autoupgrade.jar -mode upgrade -config yourconfig The -mode upgrade command proceeds directly to the phases that follow the prechecks and prefixups stages during deploy. The main goal of this feature is to reduce database downtime by running the fixups when your database is online and open for business and then skipping the fixups when your database is offline. See Oracle documentation for further details.

Conclusion

If your application is really sensitive to downtime, you can now upgrade with less downtime using fast deploy. It introduces a slight risk that the preupgrade analyze will not detect an issue that will cause troubles later on. You have to choose whether that risk is acceptable to you.

Our recommendation is to use the old approach – AutoUpgrade in deploy mode. Consider only fast deploy if downtime really hurts.

If you are interested in seeing which other things got put into AutoUpgrade 21.2, you can check the change log.

Future-proof Your Encrypted Database During Upgrade

In Oracle Database 19c, use of sqlnet.ora to define the keystore (or wallet) location has been deprecated. Instead you should use the database parameter WALLET_ROOT. If you upgrade to Oracle Database 19c with AutoUpgrade, it has become a lot easier. Let AutoUpgrade do the work for you.

How To

If you instruct AutoUpgrade to use the new encryption parameters it will not only add the parameters to the SPFile but also copy the keystore file to the location defined. This is what you have to do:

  1. Create a text file which contains the definition of WALLET_ROOT and TDE_CONFIGURATION. I call it /tmp/au-pfile-tde. Optionally, change the location of the keystore to fit your organization.
WALLET_ROOT='/etc/oracle/keystores/$ORACLE_SID'
TDE_CONFIGURATION='KEYSTORE_CONFIGURATION=FILE'
  1. Instruct AutoUpgrade to add those parameter during and after upgrade. Add the following to your AutoUpgrade config file:
upg1.add_during_upgrade_pfile=/tmp/au-pfile-tde
upg1.add_after_upgrade_pfile=/tmp/au-pfile-tde

That’s it! AutoUpgrade will detect that you are changing the keystore location, and it will copy the keystore files to the new location at the appropriate time.

Important: When you use WALLET_ROOT the keystore files should always be stored in a subfolder called tde. This means that the keystore files will end up in /etc/oracle/keystores/$ORACLE_SID/tde. You should not add /tde manually to WALLET_ROOT. The database will do that automatically when it looks up the keystore.

Now What

Since you have moved the keystore files to a new location there are some things that you should take care of:

  • You can remove the sqlnet.ora parameter ENCRYPTION_WALLET_LOCATION. It is not used anymore.
  • The keystore files that were stored in the old location (that defined by ENCRYPTION_WALLET_LOCATION) can be moved manually to a backup location. I would never recommend that you delete keystore files – NEVER! Instead move the old files to a backup location and keep them there.
  • The keystore files are to be considered critical and contain sensitive information, so ensure that the new location has the same security measures as the old one – like:
    • Restricted file permissions
    • Auditing
    • Backup

Background

Traditionally, if you have an encrypted database, you need to define the keystore location in sqlnet.ora using the parameter ENCRYPTION_WALLET_LOCATION. You would set it to something like this:

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/etc/oracle/keystores/$ORACLE_SID)))

For many reasons, sqlnet.ora was not a good location for this parameter, and especially with the introduction of isolated keystore mode, a new method was needed.

In came WALLET_ROOT and TDE_CONFIGURATION database initialization parameters. The former, WALLET_ROOT, defines the location of the keystore. The latter, TDE_CONFIGURATION, defines which kind of keystore is in use. Typically, it is set to FILE – that’s when you use a software keystore (a file in the OS). But it could also be OKV if you are Oracle Key Vault. For a software keystore you would set it to something like:

ALTER SYSTEM SET WALLET_ROOT='/etc/oracle/keystores/$ORACLE_SID' SCOPE=SPFILE;
ALTER SYSTEM SET TDE_CONFIGURATION='KEYSTORE_CONFIGURATION=FILE' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

Now, the database finds the keystore location using the WALLET_ROOT parameter which is much more smooth.

As of Oracle Database 19c, configuration the keystore using sqlnet.ora has been deprecated, and as with any other deprecated functionality, you should move to a fully supported alternative.

Further Reading

Other Blog Posts in This Series

Upgrade Internals – Webinar

Here is a little teaser for our upcoming webinar on Database Upgrade Internals – and so much more on Wednesday 10 February 2021 at 10:00 CET. You can still sign up.

Virtual Classroom Series - Upgrade to Oracle Database 19c

We have a special guest star joining, Frederick Alvarez – one of the masterminds behind AutoUpgrade. In addition, as usual Mike Dietrich will be there, and we have prepared a whole lot of new contents for this webinar. And – if I have to say so myself – it has become quite good and interesting. If you are still not convinced take a look at this!

What Actually Happens During Upgrade

We will go into the upgrade itself, dissect it, and discover what actually happens. Hopefully, it will no longer be a black box to you.

How are the workers processing the upgrade? How are the workers processing the upgrade?

How is a container database upgraded? How is a container database upgraded?

What goes on in the phases of the upgrade? What goes on in the phases of the upgrade?

I Need More Power, Scotty

We will also cover, how you can make your upgrade faster and show what you can achieve.

What is the benefit of gathering stats before the upgrade? What is the benefit of gathering stats before the upgrade?

What Can You Achieve With AutoUpgrade

A deep dive into the checks that are executed by AutoUpgrade. A list of some of the comprehensive checks that are executed by AutoUpgrade A list of some of the comprehensive checks that are executed by AutoUpgrade

One page monitoring One page monitoring

The Old Releases

For those of you on very old releases, we will discuss how you can upgrade to Oracle Database 19c.

What to consider when doing upgrades from very old releases What to consider when doing upgrades from very old releases

See You

Once the webinar is over we will publish a recording and make the slides available for download.

See you on wednesday!