Tag: Upgrade

Downgrade and Data Guard

Oracle Database is capable of downgrading, and it is a cool fallback mechanism. You can use it even after go-live. Plus, Data Guard plays nicely together with downgrade. You don’t have to rebuild the standby database following a downgrade if you follow the correct procedure.

When To Use Downgrade

You should only consider downgrading after go-live. When users have made changes to your Oracle Database. In that case, you can downgrade to the previous release without any data loss. Before go-live, Flashback Database is our preferred fallback mechanism.

A comparison between the two:

Flashback Database Downgrade
Data loss No data loss
Use before go-live Use after go-live
After flashback, database is identical with before-upgrade state After downgrade, database is compatible with before-upgrade state, but not identical
Requires Enterprise Edition Works in all editions
Preferred method

General Considerations

It is a requirement that you have not changed the COMPATIBLE parameter. As soon as you change COMPATIBLE after upgrade, you can no longer downgrade your Oracle Database. If you have already changed the COMPATIBLE parameter, you must use other fallback methods like Data Pump or RMAN restore.

The old release Oracle Home must still exist on primary and standby hosts. I recommend that you keep them until you are absolutely sure you will not downgrade your Oracle Database.

When you downgrade your database, I recommend leaving your Grid Infrastructure at the new release. Don’t downgrade Grid Infrastructure as well. Ideally, you upgraded Grid Infrastructure to the new release in advance in a previous maintenance window. Thus, you know it can handle the old release of the database. Save yourself the added complexity of also downgrading Grid Infrastructure.

Data Guard broker does not support going back to a previous version. You must disable the broker before downgrade and afterward create a new configuration or restore broker configuration files from the old release.

The following works for Data Guard configuration with a physical standby database. Other considerations and a different procedure apply if you have a logical standby database.

Downgrade

To downgrade an Oracle Database protected by Data Guard the following applies:

  • You can downgrade hours, days, or even months after the upgrade. As long as you haven’t changed the COMPATIBLE parameter.
  • You must mount the standby database and start redo apply in real-time apply mode. Keep it running during the entire process.
  • You must not open the standby database until the end of the process.
  • The downgrade happens on the primary database. A downgrade will make changes to the data dictionary. Those changes are applied on the standby via redo.
  • A downgrade with a standby database generally follows the same procedure as a regular database. However, at the end of each step, you must ensure that the standby database has applied all the redo generated by the primary database during that step. I usually issue a few log switches and check the current sequence (SEQUENCE#) on the primary database. Then I wait for that sequence to be applied on the standby database.

Check our video on YouTube for essential information on downgrade and flip through the slides.

How To Downgrade

AutoUpgrade can’t perform downgrades. You need to do this manually.

In the following, $NEW_ORACLE_HOME refers to the release you were upgrading to (e.g., 19c), and $OLD_ORACLE_HOME refers to the release you upgraded from (e.g., 12.1.0.2).

  1. Follow the pre-downgrade instructions.
  2. Set the environment in your session to the new Oracle Home.
  3. For RAC, set CLUSTER_DATABASE parameter:
    alter system set cluster_database=false scope=spfile sid='*'
  4. Stop the primary database:
    srvctl stop database -d $ORACLE_UNQNAME
  5. Start the primary database (if RAC, just one instance) in downgrade mode:
    startup downgrade
  6. Set the executable flag on downgrade script in the new Oracle Home:
    chmod +x $ORACLE_HOME/bin/dbdowngrade
  7. Start downgrade script on primary database. The environment is still set to the new release Oracle Home.
    $ORACLE_HOME/bin/dbdowngrade
  8. Important: Ensure that the standby database has applied all redo from the downgrade operation. Perform a few log switches and note the sequence number. Ensure the standby database has applied that sequence.
  9. Shut down primary database and standby database (if RAC, all instances).
  10. Downgrade the standby database in Grid Infrastructure. This tells Grid Infrastructure to start the database in the old Oracle Home (in this example, it is 12.1.0.2):
    $ORACLE_HOME/bin/srvctl downgrade database \
  -db $ORACLE_UNQNAME
  -oraclehome $OLD_ORACLE_HOME
  -targetversion 12.1.0.2
  11. Important: Switch the environment in your session to the old Oracle Home.
  12. Start the standby database (if RAC, all instances).
  13. Start redo apply:
    alter database recover managed standby database disconnect from session
  14. Start primary database in upgrade mode (if RAC, only one instance):
    startup upgrade
  15. Finish the downgrade:
    set echo on termout on serveroutput on timing on
spool catrelod.log
@?/rdbms/admin/catrelod.sql
  16. Recompile:
    @?/rdbms/admin/utlrp.sql
  17. Set CLUSTER_DATABASE parameter and shut down:
    alter system set cluster_database=true scope=spfile sid='*'
shutdown immediate
  18. Downgrade the primary database in Grid Infrastructure. Grid Infrastructure will now start the database in the old Oracle Home (in this example it is 12.1.0.2):
    $NEW_ORACLE_HOME/bin/srvctl downgrade database \
  -db $ORACLE_UNQNAME
  -oraclehome $OLD_ORACLE_HOME
  -targetversion 12.1.0.2
  19. Start the primary database:
    $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME
  20. Ensure all components are valid or option off:
    select comp_id, status 
from   dba_registry
where  status not in ('VALID', 'OPTION OFF')
  21. Important: Ensure that the standby database has applied all redo from the downgrade operation. Perform a few log switches and note the sequence number. Ensure the standby database has applied that sequence.
  22. Start Data Guard broker on primary and standby database
    • Either restore broker config files from old release, and start Data Guard broker.
    • Or, start Data Guard broker and recreate the configuration.
  23. : Ensure that your Data Guard configuration works. Use validate database command in Data Guard broker on all databases and ensure they are ready for switchover.
  24. Optionally, test your Data Guard by doing a switchover.
  25. Perform the post-downgrade instructions.

That’s it!

CDB

No specific Data Guard-related changes. The above procedure is written for a non-CDB database. The procedure is slightly different for a CDB; check the documentation or the demo below.

RAC

On the standby database, you can leave all instances running, and you don’t need to change CLUSTER_DATABASE.

Demo

Downgrade of a CDB running RAC and Data Guard environment:

Other Blog Posts in This Series

Upgrade of Time Zone File and Flashback Database

Someone asked me on the blog:

Can you revert an upgrade of the time zone file using Flashback Database?

And the short answer: Yes!

A time zone file update is a two-step process:

  • Time zone patch to Oracle Home
  • Time zone file upgrade of the database

Applying the patch to the Oracle Home is a simple process. The Oracle Home already has a lot of time zone files present (the previous versions), so you can safely add a newer version. It doesn’t hurt anything; you should generally never roll them off again. The time zone patch adds a file with time zone definitions (in $ORACLE_HOME/oracore/zoneinfo).

The second step is to upgrade the time zone file inside the database. This step will make changes to the data dictionary using the information from the time zone file in the Oracle Home. All the changes made in this step are made inside the database. There are situations when you want to revert the second step: Upgrading the time zone file in the database. For instance:

  • If the time zone file upgrade fails.
  • You performed a database upgrade to a new release and upgraded the time zone file afterward. Now you find a critical issue in the new release and decide to roll everything back.

How To Upgrade

Let’s give it a try and see how it works. I start with an Oracle Database running 19c, and I want to upgrade the time zone file and then revert the operation.

  • The database uses the default time zone file version (32 in Oracle Database 19c):

    SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32 	        0

  • I create a user and a table. Also, I insert data related to a time zone change in Yukon, Canada, that comes in with time zone file 35:

    SQL> create user sales no authentication;
SQL> alter user sales quota unlimited on users;
SQL> grant create table to sales;
SQL> create table sales.t1 (
        c1 timestamp with time zone
     );   

SQL> insert into sales.t1 values(TIMESTAMP '2020-03-07 12:00:00.00 America/Whitehorse');
SQL> insert into sales.t1 values(TIMESTAMP '2020-03-08 12:00:00.00 America/Whitehorse');
SQL> insert into sales.t1 values(TIMESTAMP '2020-03-09 12:00:00.00 America/Whitehorse');
SQL> commit;

  • I download the time zone patch from Master Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1). Time zone files are cumulative, so I take the latest one, version 38. I apply it using opatch like any other patch to the Oracle Home. This is the result after applying the patch:

    $ ./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.28
Copyright (c) 2022, Oracle Corporation.  All rights reserved.

...

Patch  34006614     : applied on Sat Jul 30 12:46:16 CEST 2022
Unique Patch ID:  24751709
Patch description:  "RDBMS - DSTV38 UPDATE - TZDATA2022A"
   Created on 27 Apr 2022, 04:16:52 hrs PST8PDT
   Bugs fixed:
     34006614

  • So far – no changes have been made to the database. It still runs with the old time zone file.

  • Before performing the time zone file upgrade, I create a restore point:

    SQL> create restore point before_tz_upg guarantee flashback database;

Restore point created.

  • I start the time zone file upgrade by running the pre-upgrade check:

    SQL> @utltz_upg_check.sql

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

  • It looks good, so go ahead with the actual upgrade. This step requires a maintenance window:

    SQL> @utltz_upg_apply.sql

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv38 .
WARNING: This script will restart the database 2 times
...
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv38 .
INFO: The RDBMS DST update is successfully finished.

  • That’s it. I have upgraded the time zone file in the database.

    SQL> select * from v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_38.dat 	     38 	 0

How To Fall Back

To answer the original question: Can I use Flashback Database to back out of a time zone file upgrade?

  • I restart the database in mount mode:

    SQL> shutdown immediate
...
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
...
Database mounted.

  • I try to use Flashback Database

    SQL> flashback database to restore point before_tz_upg;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

  • It works. The database time zone file is back at version 32:

    SQL> select * from v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 	     32 	 0

  • I can also select my data again:

    SQL> select * from sales.t1;
 
C1
-----------------------------------------------
07-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE
08-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE
09-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE

  • Don’t roll off the time zone patch from the Oracle Home. It makes no difference to the database. Once you have reverted the second step, the database will work like before the upgrade. If you insist on doing so, just ensure that no other database uses the time zone file that came in with the patch.

Conclusion

This proves that you can use Flashback Database to revert an upgrade of the database time zone file. Flashback Database is easy and effective. There is no need to roll off the time zone patch from the Oracle Home. It makes no difference to the database.

Flashback and Data Guard

When you upgrade your Oracle Database, you should also prepare for fallback. Data Guard plays nicely together with Flashback Database. You don’t have to rebuild the standby database following a Flashback Database if you follow the correct procedure.

When To Use Flashback Database

Flashback Database is easy to use, and it is our preferred fallback mechanism. However, Flashback Database also means data loss because the database is rewinded. So, Flashback Database is only useful before you go live on the new release.

A comparison between Flashback Database and downgrade.

Flashback Database Downgrade
Data loss No data loss
Use before go-live Use after go-live
After flashback, database is identical with before-upgrade state After downgrade, database is compatible with before-upgrade state, but not identical
Requires Enterprise Edition Works in all editions
Preferred method

If your Oracle Database is running Standard Edition 2, you are not licensed to use Flashback Database. Instead look at partial offline backup.

General Considerations

It is a requirement that you have not changed the COMPATIBLE parameter. As soon as you change COMPATIBLE after upgrade, you can no longer use Flashback Database. If you have already changed the COMPATIBLE parameter, you must use other fallback methods like Data Pump or RMAN restore.

The old release Oracle Home must still exist on primary and standby hosts. I recommend that you keep them until you are absolutely sure you will not flashback (nor downgrade) your Oracle Database.

When you flashback your database, I recommend that you leave your Grid Infrastructure at the new release. Don’t downgrade Grid Infrastructure as well. Ideally, in a previous maintenance window, you upgraded Grid Infrastructure to the new release in advance. Thus, you know it can handle the old release of the database. Save yourself the added complexity of also downgrading Grid Infrastructure.

Data Guard broker does not support going back to a previous version. You must disable the broker during flashback and afterward create a new configuration or restore broker configuration files from the old release.

The following works for Data Guard configuration with a physical standby database. Other considerations and a different procedure apply if you have a logical standby database.

Flashback

To flashback a database protected by Data Guard the following applies:

  • You must have a restore point on primary and all standby databases.
  • First, create restore points on standby database, then on primary database. The SCN of the restore points on the standby database must be lower than the SCN of the restore point on the primary database.
  • I recommend using guaranteed restore points when upgrading.
  • Don’t rely on restore point propagation. Manually create the restore points on the standby database.
  • The order of the operation is important: First, handle standby databases (order of standby databases is not important), then primary database.

On YouTube, we have a good introduction to using Flashback Database as fallback. You can also flip through the slides.

How To Flashback With AutoUpgrade

The following assumes:

  • You manually created a restore point on the standby database.
  • AutoUpgrade created a restore point on the primary database (default, controlled by restoration).
  • AutoUpgrade was configured to keep the restore point after upgrade (default, controlled by drop_grp_after_upgrade).
  • $NEW_ORACLE_HOME refers to the release you were upgrading to (e.g. 19c) and $OLD_ORACLE_HOME refers to the release you upgraded from (e.g. 12.1.0.2).

The procedure:

  1. The environment in your session is set to the new Oracle Home.
  2. Stop Data Guard broker on all databases:
    alter system set dg_broker_start=false scope=both sid='*'
  3. Stop standby database (all instances, if RAC).
  4. Flashback primary database using AutoUpgrade. nn is the AutoUpgrade job id that executed the upgrade:
    java -jar autoupgrade.jar -config ... -restore -jobs nn
    AutoUpgrade handles everything on primary database, like
    • /etc/oratab
    • Grid Infrastructure downgrade
    • SPFile
    • Etc.
  5. Start standby database in mount mode (only one instance, if RAC).
  6. Flashback the standby database:
    flashback database to restore point ...
  7. Shut down the standby database.
  8. Downgrade the standby database clusterware configuration. Grid Infrastructure will now start the database in the old Oracle Home (in this example it is 12.1.0.2):
    $NEW_ORACLE_HOME/bin/srvctl \
  downgrade database \
  -db $ORACLE_UNQNAME \
  -oraclehome $OLD_ORACLE_HOME \
  -targetversion 12.1.0.2
  9. Start the standby database.
  10. Start Data Guard broker on primary and standby database
    • Either restore broker config files from old release and start Data Guard broker.
    • Or, start Data Guard broker and recreate the configuration.
  11. : Ensure that your Data Guard configuration works. Use validate database command in Data Guard broker on all databases and ensure they are ready for switchover.
  12. Optionally, test your Data Guard by doing a switchover.
  13. Remember to drop the guaranteed restore points on all databases.

That’s it!

Demo

Flashback of a CDB running RAC and Data Guard environment:

Other Blog Posts in This Series

Upgrade and Data Guard

You can upgrade your Oracle Database to a new release and keep the Data Guard setup intact. There is no need to rebuild a physical standby database after the upgrade.

When you upgrade the primary database, many changes go into the data dictionary. These changes are recorded in the redo stream and sent to the standby database. When the standby database applies the redo, it is implicitly upgraded.

Prerequisites

If Grid Infrastructure (GI) manages your Oracle Databases, you must upgrade GI first. Check the Grid Infrastructure Installation and Upgrade Guide for your platform.

You can do it in the same maintenance window as the database upgrade, but I recommend that you perform the GI upgrade in an earlier maintenance window. A newer version of GI can run earlier versions of Oracle Database, so you can safely upgrade GI in advance. Doing so will give you time to adapt to the new GI release.

Also, in advance, you should install the new Oracle Home on both primary and standby hosts. The two Oracle Homes must have the same patches applied, and I recommend that you always apply the latest Release Update and have a look at 555.1 for important one-offs.

How To

When upgrading with Data Guard, there are two approaches:

  1. Standby Offline method
  2. Maximum Availability Architecture (MAA) method

Standby Offline Method

Before the upgrade starts on the primary database, you shut down the standby database. You keep it shut down until the upgrade has completed on the primary database and you have finished your tests. When you are sure you will stay on the new release, the standby database is restarted and synchronized with the primary database. It will take some time before you can go live because the standby database must apply all the redo generated during the upgrade.

If you need to fall back, you can use Flashback Database on the primary database. In addition, no matter what happens to the primary database, you still have the standby database immediately ready in the pre-upgrade state.

My team recommends this method. We prefer to sacrifice a little downtime to achieve even better protection.

MAA Method

The standby database is open and applies redo while the primary database is upgraded. This means that the standby database is closely following the primary database. You can go live very soon after the upgrade completes because there is little or very little apply lag.

The downside is when you must fall back. In that case, you have two databases to bring back in time with Flashback Database. In the very unlikely event that something happens during flashback on both databases, you may need to restore your backup.

The MAA team recommends this method as it guarantees the lowest downtime.

Which One To Choose?

If you have two or more standby databases, you can combine the two methods and get the best of both worlds. Otherwise, rest assured that both methods work fine and are supported.

Standby Offline MAA
Maximum protection Minimum downtime
Upgrade team recommendation MAA recommendation
Redo transport deferred Redo transport enabled
Redo apply stopped Redo apply active
Protected by offline standby and guaranteed restore point Protected by guaranteed restore point
AutoUpgrade default

Of course, AutoUpgrade supports both methods. You can check the other blog post in the series for detailed instructions.

Note, the following implication of using the standby offline method. AutoUpgrade will defer redo log transport to all remote archive destinations. Not only standby databases, but also GoldenGate downstream capture and Real-Time Redo Transport feature of Zero Data Loss Recovery Appliance. Most likely this is not a problem, since the database is in a maintenance window during the upgrade. But remember to enable all of them afterward.

What If

Exadata

If you are running Oracle Database on Exadata, you should read the dedicated procedure created by the Maximum Availability Architecture (MAA) team.

Multiple Standby Databases

Not much changes if you have many standby databases in your Data Guard configuration. The procedure is basically the same, except that you must execute commands on all the standby databases. The order of the standby databases does not matter (unless you have cascaded standby databases – see below).

Data Guard Broker

If you have configured your Data Guard setup using Data Guard broker, then you can leave it running during the upgrade. There used to be some problems with Data Guard broker during upgrade to previous releases, but it works fine when you upgrade to Oracle Database 19c.

However, you must disable Fast-Start Failover before the upgrade. After a successful upgrade, you can enable it again.

Cascaded Standby Databases

If you have cascaded standby databases, the following applies according to the documentation:

If there are cascaded standbys in your configuration, then those cascaded standbys must follow the same rules as any other standby, but should be shut down last, and restarted in the new home first.

You must treat cascaded standby databases like any other standby database. However, the order is now important. Imagine this scenario:

  • Primary database: BOSTON
  • Standby database: CHICAGO
  • Cascaded standby database: NEWYORK

When the procedure tells you to stop standby databases: First CHICAGO, then NEWYORK When the procedure tells you to start standby databases: First NEWYORK, then CHICAGO

Far Sync

A far sync database should be treated like any other standby database. Like cascaded standby databases the order of the shutdown is important to ensure that all redo from primary reaches the standby database connected via the far sync.

Logical Standby

When you have logical standby databases in your Data Guard configuration, things are slightly different. In that case, look in the documentation.

Database Services in OCI

You need to follow the documentation for your particular database service. If you have an Exadata Cloud Service, you might find Exadata Cloud Database 19c Rolling Upgrade With DBMS_ROLLING (Doc ID 2832235.1) interesting.

Other Blog Posts in This Series

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

Upgrading an Encrypted Non-CDB or CDB

Upgrading a non-CDB or an entire CDB is straightforward with AutoUpgrade. There is only one requirement:

  • An auto-login keystore must be present.

Upgrade Non-CDB and CDB

The auto-login keystore enables the database to open the TDE keystore without a DBA manually entering the keystore password. During a database upgrade, the database will restart multiple times. The upgrade process embeds the restarts, and there is no way for a DBA to intervene halfway to enter the TDE keystore password. Hence, it is required to use an auto-login keystore.

You can query the database for the type of the TDE keystore:

SQL> select wallet_type from v$encryption_wallet;

AUTOLOGIN

It must be an AUTOLOGIN keystore or a LOCAL_AUTOLOGIN. I like the local autologin keystore because it adds an additional layer of security.

When a proper keystore is in place, you can start the upgrade.

Keystore Location and WALLET_ROOT

The Oracle Database must know where to find the TDE keystore. It will look in the following locations in the following order:

  • As of Oracle Database 19c, WALLET_ROOT initialization parameter.
  • ENCRYPTION_WALLET_LOCATION sqlnet.ora parameter.
  • $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet
  • $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet

Oracle recommends using the parameter WALLET_ROOT when your database is on Oracle Database 19c. The parameter is introduced in Oracle Database 19c, and all other methods have been deprecated.

It is easier to configure the TDE keystore using WALLET_ROOT than sqlnet.ora. AutoUpgrade can implement the changes needed to switch to the WALLET_ROOT parameter as part of an upgrade . I recommend doing that.

TNS_ADMIN

Often, sqlnet.ora defines the TDE keystore configuration. This means that the TNS_ADMIN location is important.

TNS_ADMIN defaults to $ORACLE_HOME/network/admin. But sometimes, it is relocated either via a profile (like .bashrc) or using srvctl setenv database. AutoUpgrade fully supports both methods.

But it does happen from time to time that there are issues with the TNS_ADMIN location. Recently, I saw it at a customer. The customer used a dedicated sqlnet.ora for each database. The parameter ENCRYPTION_WALLET_LOCATION was unique in each of the sqlnet.ora files. They had issues with their profiles and AutoUpgrade picked up the wrong sqlnet.ora. This caused AutoUpgrade to report issues with the TDE keystore during analyze phase.

Luckily, there is functionality in AutoUpgrade to override the TNS_ADMIN location:

You can put them into the config file. AutoUpgrade will set the TNS_ADMIN environment variable before executing any command. That will effectively override any other TNS_ADMIN setting:

upg1.target_tns_admin_dir=/etc/oracle/keystores/DB12

Usually, I would not recommend using these parameters. In most cases, the correct TNS_ADMIN location is set and all is good. Use only when you encounter issues.

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