Tag: Upgrade

Upgrade a PDB to Oracle Database 23ai Using Replay Upgrade

When you upgrade a PDB to Oracle Database 23ai, there is a new method for performing the upgrade. It’s called Replay Upgrade.

I would call it a convenience feature. You simply plug in to a higher release CDB and open the PDB. The CDB detects the lower-release PDB and performs the upgrade. You don’t have to invoke AutoUpgrade.

Here’s how to do it.

A Few Words on Replay Upgrade

In Oracle Database 23ai, you can upgrade the data dictionary in two ways:

  • Parallel Upgrade – Has been around for quite a few releases. It’s what you’ve used before and can still use.
  • Replay Upgrade – The new thing that enables you to upgrade the data dictionary by simplying plugging in a lower-release PDB and allowing the CDB to perform the upgrade – without using AutoUpgrade.

I suggest you watch this video about the fundamental differences between the two methods.

Replay Upgrade is not a substitute for the entire upgrade project. Even with Replay Upgrade, you must still run the pre-upgrade and post-upgrade tasks. The version of the PDB must be one that allows for a direct upgrade to Oracle Database 23ai: 19c or 21c.

AutoUpgrade uses Parallel Upgrade. You can force AutoUpgrade to use Replay Upgrade in your config file:

upg1.replay=yes

How To Upgrade Using Replay Upgrade

  1. You must perform the pre-upgrade tasks while the PDB is in the lower-release CDB.
  2. One of such tasks is to analyze the PDB for upgrade readiness:
    java -jar autoupgrade.jar ... -mode analyze
  3. If needed, run the pre-upgrade fixups:
    java -jar autoupgrade.jar ... -mode fixups
  4. Plug in a lower-release PDB into a higher-release CDB. It doesn’t matter whether you plugged in from a manifest file, using refreshable clone PDBs or any other method.
  5. Open the PDB:
    alter pluggable database PDB1 open;
    • When you open the PDB in normal mode, Replay Upgrade starts.
    • The open command doesn’t complete until the upgrade completes. The command is not hanging; it’s simply upgrading in the background.
    • If you open the PDB in upgrade mode, Replay Upgrade does not start.
  6. During the open command, you can see in the alert log that the CDB upgrades the PDB:
    2025-03-31T14:02:37.955470+00:00
ORANGE(6):Starting Upgrade on PDB Open
  7. When the open command completes, the PDB will be upgraded. But it will open in restricted mode until you run Datapatch. From alert.log:
    ORANGE(6) Error Violation: SQL Patch, Cause: '23.5.0.24.07 Release_Update2407102158' is installed in the CDB but no release updates are installed in the PDB, Action: Call datapatch to install in the PDB or the CDB
2025-03-31T14:11:03.803899+00:00
ORANGE(6):Opening pdb with no Resource Manager plan active
Violations: Type: 1, Count: 1
Completed: Pluggable database ORANGE opened read write
Completed:    alter pluggable database orange open
  8. Run Datapatch:
    $ORACLE_HOME/OPatch/datapatch -pdbs PDB1
  9. Restart the PDB to remove restricted mode:
    alter pluggable database PDB1 close immediate;
alter pluggable database PDB1 open;
  10. Run post-upgrade tasks.

Want To Try It?

In our upgrade lab, Hitchhiker’s Guide for Upgrading to Oracle Database 23ai, there is no lab on Replay Upgrade.

You can still perform a Replay Upgrade if you want. I’ve created instructions in the appendix that you can use. The lab takes 15 minutes to complete.

My Database Is A Non-CDB

Replay Upgrade performs an upgrade-on-open. Interestingly, it can also perform a convert-on-open. The latter will run the same commands you’ll find in noncdb_to_pdb.sql, which you normally run to convert a non-CDB to a PDB.

So, you can simply plug a 19c non-CDB into a 23ai CDB. When you open the PDB, the CDB upgrades and converts to a PDB.

My Recommendation

I recommend using AutoUpgrade. It ensures that you run all the tasks and automates them completely, giving you the safest upgrade.

Replay Upgrade does look a lot easier at first glance, but you still need to remember all the pre-upgrade and post-upgrade tasks. When there’s something you must run manually, there’s always the risk that you forget one or two of the tasks.

For me, Replay Upgrade is a convenience feature you can use in a lab or demo environment or if you think it’s easier to incorporate in your automation. But even with automation, you can still use AutoUpgrade with the -noconsole command line option.

But the choice is yours.

Happy upgrading!

Appendix

Replay Upgrade Queries and Commands

  • Here’s how you can tell whether Replay Upgrade (Upgrade on Open and Convert On Open) is enabled:
    select property_name, property_value 
from   database_properties
where  property_name like '%OPEN%';
    You can set the property in the root container and in the PDB.
  • Here’s how to disable Replay Update:
    alter database upgrade sync off;

Hands-On Lab

Here are the instructions for trying a Replay Upgrade in our Hands-On Lab.

The below steps perform a Replay Upgrade of the ORANGE PDB from CDB19 to CDB23.

  1. Start by provisioning a new lab and connecting to it. The lab runs in Oracle LiveLabs and is completely free. No installation is required.
  2. Start the CDB19 database. It’s a container database on Oracle Database 19c:
    . cdb19
env | grep ORA
sqlplus / as sysdba<<EOF
   startup;
EOF
  3. Create an AutoUpgrade config file:
    cd /home/oracle/scripts
cat > orange-replay.cfg <<EOF 
global.autoupg_log_dir=/home/oracle/logs/orange-replay
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/u01/app/oracle/product/23
upg1.sid=CDB19
upg1.target_cdb=CDB23
upg1.pdbs=ORANGE
upg1.target_pdb_copy_option.ORANGE=file_name_convert=none
upg1.timezone_upg=NO
EOF
  4. Run AutoUpgrade in analyze mode:
    cd
java -jar autoupgrade.jar -config scripts/orange-replay.cfg -mode analyze
    • AutoUpgrade analyzes the ORANGE PDB for upgrade readiness.
  5. Check the preupgrade summary report:
    cat /home/oracle/logs/orange-replay/cfgtoollogs/upgrade/auto/status/status.log
    • The report states Check passed and no manual intervention needed.
  6. Run the preupgrade fixups:
    java -jar autoupgrade.jar -config scripts/orange-replay.cfg -mode fixups
    • AutoUpgrade runs pre-upgrade fixups, if any.
  7. Unplug ORANGE from the 19c CDB:
    . cdb19
sqlplus / as sysdba<<EOF
    alter pluggable database ORANGE close;
	alter pluggable database ORANGE unplug into '/home/oracle/orange.xml';
	drop pluggable database ORANGE keep datafiles;
EOF
  8. Plug into the 23ai CDB and open ORANGE:
    . cdb23
env | grep ORA
sqlplus / as sysdba<<EOF
   set timing on
   create pluggable database ORANGE using '/home/oracle/orange.xml' nocopy;
   alter pluggable database orange open;
EOF
    • The open command upgrades the PDB. The command runs for several minutes.
    • In the end, the command completes but prints Warning: PDB altered with errors.
  9. Run Datapatch on the ORANGE PDB:
    $ORACLE_HOME/OPatch/datapatch -pdbs ORANGE
  10. Restart ORANGE:
    sqlplus / as sysdba<<EOF
	alter pluggable database orange close;
	alter pluggable database orange open;
	select open_mode, restricted from v\$pdbs where name='ORANGE';
EOF
    • The PDB now opens normally (READ WRITE) and unrestricted.
  11. Run the post-upgrade fixups:
    java -jar autoupgrade.jar \
   -preupgrade "dir=/home/oracle/logs/orange-replay/fixups,inclusion_list=ORANGE" \
   -mode postfixups
  12. That’s it. ORANGE has now been upgraded:
    sqlplus / as sysdba<<EOF
	select open_mode, restricted from v\$pdbs where name='ORANGE';
	alter session set container=ORANGE;
	select version_full from v\$instance;
EOF

Recreate Database Services After Moving An Oracle Database

Oracle recommends that you connect to the database via custom services. In your connect string, don’t connect:

  • Directly to the SID
  • Or to the database’s default service (the service with the same name as the database).

When you move a database around, in some situations, the database does not retain these services, for instance, when you:

  • Migrate a non-CDB to PDB using refreshable clone PDB
  • Upgrade a PDB using refreshable clone PDB
  • Move a PDB to a different CDB using refreshable clone PDB
  • Migrating a database using Full Transportable Export/Import or transportable tablespaces

The services are important because your application and clients connect to the database through that service. Also, the service might define important properties for things like Application Continuity or set default drain timeout.

Here’s how to recreate such services.

Database Managed Services

A database-managed service is one that you create directly in the database using dbms_service:

begin
   dbms_service.create_service(
      service_name=>'SALESGOLD',
      network_name=>'SALESGOLD');
   dbms_service.start_service('SALESGOLD');   
end;
/

After the migration, you must manually recreate the service in the target database.

dbms_metadata does not support services. So, you must query v$services in the source database to find the service’s defition. Then, construct a call to dbms_service.create_service and dbms_serice.start_service.

Clusterware Managed Services

I recommend defining services in Grid Infrastructure if you are using Oracle RAC or using Oracle Restart to manage your single instance database. Luckily, Grid Infrastructure supports exporting and importing service defitions.

  • You export all the services defined in the source database:

    srvctl config service \
   -db $ORACLE_UNQNAME \
   -exportfile my_services.json \
   -S 2

  • You edit the JSON file.

    1. Remove the default services. Keep only your custom services.
    2. Remove the dbunique_name attribute for all services.
    3. If you are renaming the PDB, you must update the pluggable_database attribute.
    4. Update the res_name attribute so it matches the resource name of the target database. Probably you just need to exchange the db_unique_name part of the resource name. You can find the resource name as grid when you execute crsctl stat resource -t.

  • You can now import the services into the target database:

    srvctl add service \
   -db $ORACLE_UNQNAME \
   -importfile my_services.json

  • Finally, you start the service(s):

    export ORACLE_SERVICE_NAME=SALESGOLD
srvctl start service \
   -db $ORACLE_UNQNAME \
   -service $ORACLE_SERVICE_NAME

Additional Information

  • The export/import features work from Oracle Database 19c, Release Update 19 and beyond.
  • You can also export/import the definition of:
    • Database: srvctl config database -db $ORACLE_UNQNAME -S 2 -exportfile my_db.json.json
    • PDB: srvctl config pdb -db $ORACLE_UNQNAME -S 2 -exportfile my_pdb.json
    • ACFS filesystem: srvctl config filesystem -S 2 -exportfile /tmp/my_filesystem.json
  • At time of writing, this functionality hasn’t made it into the documentation yet. Consider yourself lucky knowing this little hidden gem.

Final Words

Remember to recreate your custom services after a migration. Your application needs the service to connect in a proper way.

Further Reading

How to Upgrade Oracle Database with Username and Password

Normally, you upgrade an Oracle Database with native operating system authentication. The upgrade tooling connects to the database as / as sysdba. However, in some organizations, this is not allowed for security reasons. Every connection must be with username and password.

How can you upgrade an Oracle Database using username and password?

Set-Up

First, I’ll disable connections using native operating system authentication. I do that in sqlnet.ora.

$ env | grep TNS
TNS_ADMIN=/u01/app/oracle/product/19/network/admin
$ cat $TNS_ADMIN/sqlnet.ora
sqlnet.authentication_services=(none)

Let me check that it is disabled:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 6 09:55:05 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Upgrade Without Operating System Authentication

  1. I ensure that my AutoUpgrade is the latest version, or at least version 24.7.241021:
$ java -jar autoupgrade.jar -version
build.version 24.7.241021
build.date 2024/10/21 11:16:20 -0400
build.hash babf5a631
build.hash_date 2024/10/18 18:36:27 -0400
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v24.7, origin/stable_devel, stable_devel)
build.MOS_NOTE 2485457.1
build.MOS_LINK https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1
  1. This is my AutoUpgrade config file. There’s nothing special in it, except for global.keystore which tells AutoUpgrade where to store its keystore. AutoUpgrade uses the keystore to keep your password safe until it is needed.
$ cat UPGR.cfg
global.autoupg_log_dir=/home/oracle/logs/autoupgrade-UPGR
global.keystore=/home/oracle/autoupgrade-keystore
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/u01/app/oracle/product/23
upg1.sid=CDB19
upg1.timezone_upg=NO
  1. Now, I’m starting AutoUpgrade in -load_password mode. This is the first time I use it, so AutoUpgrade prompts for a password that it can use to encrypt the keystore:
$ java -jar autoupgrade.jar -config UPGR.cfg -load_password
Processing config file ...

Starting AutoUpgrade Password Loader - Type help for available options
Creating new AutoUpgrade keystore - Password required
Enter password:
Enter password again:
AutoUpgrade keystore was successfully created
  1. To enter the username and password for my database, I change the group to PWD:’
PWD> group PWD
Group [PWD] is already active
  1. Then, I can enter my username and password for my database (CDB19). AutoUpgrade validates the credentials, so you better make sure they work at this point:
PWD> add CDB19 -user SYS
Enter your secret/Password:
Re-enter your secret/Password:
Database SID: cdb19              User: SYS
  1. I save the keystore and convert it into an auto-login keystore, so I don’t have to enter the keystore password every time I use AutoUpgrade:
PWD> save
Convert the AutoUpgrade keystore to auto-login [YES|NO] ? yes
  1. Exit the keystore.
PWD> exit

AutoUpgrade Password Loader finished - Exiting AutoUpgrade
  1. Finally, I start the upgrade by starting AutoUpgrade in deploy mode:
java -jar autoupgrade.jar -config UPGR.cfg -mode deploy

That’s it!

Now, I can upgrade my Oracle Database without relying on native operating system authentication, using username and password instead.

SQLNET.ORA

Just a few words about sqlnet.ora and how to set up native operating system authentication.

  • Your operating system user (for instance, oracle) must be added to the appropriate groups in your operating system.
  • On UNIX/Linux, you allow native operating system authentication by authentication_services=beq in sqlnet.ora, or you can omit the parameter completely.
  • On Windows, you allow native operating system authentication by authentication_services=nts in sqlnet.ora. If you omit the parameter, then it won’t work.

Happy Upgrading!

Upgrade Encrypted PDB in Cloud to Oracle AI Database 26ai

Here’s a cool way of upgrading your Oracle Database in OCI to Oracle AI Database 26ai.

This post was originally written for Oracle Database 23ai, but it can be used to Oracle AI Database 26ai as well.

I will move my PDB to a new Base Database System using refreshable clone PDBs and AutoUpgrade.

The benefit of using this approach is:

  • Much shorter downtime than an in-place upgrade.
  • A brand-new Base Database System, which means the operating system and Oracle Grid Infrastructure is already on a newer version.

I’m using a Base Database Service as an example, but the target system could also be Exadata Database Service, Exadata Cloud@Customer, or, in fact, any other offering of Oracle Database.

My Environment

I have one PDB that I want to upgrade. It’s called SALES.

Source

  • Base Database System on 19.23.0
  • Name DBS19

Target

  • Base Database System on 23.4.0
  • Name DBS23

How To

Prepare AutoUpgrade

  • Get the latest version of AutoUpgrade:
    wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar
    Copy the new version of AutoUpgrade to your source and target system. You can put it into $ORACLE_HOME/rdbms/admin if you like, but it is not a requirement.
  • I create an AutoUpgrade config file, named sales.cfg, which I store on both servers:
    global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
global.keystore=/u01/app/oracle/cfgtoollogs/keystore
upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/23.0.0.0/dbhome_1
upg1.sid=CDB19
upg1.pdbs=SALES
upg1.target_cdb=CDB23
upg1.source_dblink.SALES=CLONEPDB 600
upg1.target_pdb_copy_option.SALES=file_name_convert=none
upg1.target_version=23
upg1.start_time=12/05/2024 02:00:00
    • I must specify global.keystore to allow AutoUpgrade to create a keystore to work with my encrypted PDB.
    • source_home and target_home list the Oracle Home of the source and target CDB, respectively. It doesn’t matter that the two homes exist on one server only.
    • sid and target_cdb contain the SID of the source and target CDB, respectively. The parameters are case sensitive.
    • pdbs contains the name of the PDB I want to upgrade, sales. If needed, I could specify more PDBs. Don’t include the domain or use the service name.
    • source_dblink has the name of the database link (CLONEPDB) and the rate at which the target CDB brings over redo and rolls forward the copy (600 seconds or 10 minutes).
    • I want to use ASM and Oracle Managed Files, so I set target_pdb_copy_option accordingly.
    • Since my source and target CDB are not on the same host, AutoUpgrade can’t automatically determine the target version. I specify that manually using target_version.
    • start_time specifies when downtime starts. At this point, AutoUpgrade refreshes the PDB for the last time and then moves on with the upgrade.

Prepare Source PDB

  • I connect to the source PDB. I create a user (for a database link) and grant privileges:

    create user dblinkuser identified by ... ;
grant create session, 
      create pluggable database, 
      select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;

  • After the upgrade, I can drop the user.

Prepare Target CDB

  • I connect to the target CDB and create a database link pointing to my source PDB:

    create database link clonepdb
connect to dblinkuser
identified by dblinkuser
using '';

  • Check that the database link works:

    select * from dual@clonepdb;

    If the query fails with ORA-02085, then you can use alter system set global_names=false;.

Analyze and Fix Source PDB

  • First, I analyze the source PDB for upgrade readiness. On the source system:
    java -jar autoupgrade.jar -config sales.cfg -mode analyze
  • The summary report lists the following precheck failures, which I safely ignore:
    • TDE_PASSWORDS_REQUIRED – I will fix that on the target system.
    • TARGET_CDB_AVAILABILITY – the target CDB is remote, and AutoUpgrade can’t analyze it.
  • Then, I execute the preupgrade fixups:
    java -jar autoupgrade.jar -config sales.cfg -mode fixups
    • This changes my source PDB, so I do it as close to my maintenance window as possible.
    • AutoUpgrade issues a warning that the target Oracle home is not present. You can safely disregard this.

Upgrade

  • Since my PDB is encrypted, I must add the source and target CDB keystore password to the AutoUpgrade keystore. I start the load password console on the target host:

    java -jar autoupgrade.jar -config sales.cfg -load_password

  • In the load password console, I add the keystore passwords of the source and target CDB:

    TDE&gt; add CDB19
Enter your secret/Password:    
Re-enter your secret/Password: 
TDE&gt; add CDB23
Enter your secret/Password:    
Re-enter your secret/Password:

  • I save the passwords and convert the AutoUpgrade keystore to an auto-login keystore:

    TDE&gt; save
Convert the keystore to auto-login [YES|NO] ?

  • I start AutoUpgrade in deploy mode:

    java -jar autoupgrade.jar -config sales.cfg -mode deploy
    • AutoUpgrade:
      • Copies the data files over the database link.
      • Rolls the copies of the data files forward with redo from the source.
      • At the time specified by start_time, issues a final refresh and disconnects the PDB from the source. You can change the start time using the proceed command in the AutoUpgrade console.
      • Upgrades the PDB.
    • You can monitor the upgrade by using the command lsj -a 30.

  • I have now upgraded my PDB to Oracle Database 23ai.

The Fine Print

Before Upgrade

  • Ensure that redo is kept in the Fast Recovery Area of the source database until it has been applied on the target. Either postpone your archive backups or change the archive log deletion policy so the archive logs remain on disk.
  • Familiarize yourself with the concept of the AutoUpgrade keystore.
  • The source PDB must be Oracle Database 19c or newer to upgrade directly to Oracle Database 23ai.

During Upgrade

  • Disconnect users from the source database. Right before the upgrade starts, AutoUpgrade executes a final refresh. The last redo from the source database is applied to ensure no data is lost. You must ensure that no users are connected to the source database after this time. Otherwise, that data will be lost.

  • AutoUpgrade starts the final refresh at the start time specified in the config file:

    upg1.start_time=25/09/2023 06:30:00

  • You must be careful about disconnecting users from the source database. Remember, AutoUpgrade connects to the source database over a database link as a regular user (not SYS). This means the listener must be available, and you can’t enable restricted session or similar means.

  • Check this blog post if you want to be in control over when the final refresh happens.

After Upgrade

  • Recreate the services that you use in your connect strings.
  • Update your connection string. The PDB is now on a different Base Database System.
  • Start a new full backup of the target database after the migration.
  • The OCI console will recognize and display the new PDB after a while. You don’t have to do anything … than to wait for the automatic sync job.

Data Guard

When using refreshable clone PDBs you can’t reuse the data files on the standby database. It is similar to STANDBYS=NONE on the CREATE PLUGGABLE DATABASE statement. So, you plug in with deferred recovery on the standby database.

The easiest solution is to configure Data Guard after you have upgraded the database. However, it might not always be feasible. If your target CDB already has Data Guard, then you need to restore the data files to the standby database and enable recovery. Check Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1) for details.

Real Application Clusters (RAC)

AutoUpgrade detects if the target CDB is a RAC database. You don’t have to specify or do anything. AutoUpgrade handles everything for you.

Further Reading

How to Prepare Your Oracle Database for Release 23ai

Here is a list of things to check and carry out while preparing for Oracle Database 23ai.

These tasks are not mandatory, but I recommend them based on my experience. It increases your chance of upgrading with success.

Get ready for Oracle Database 23ai upgrade

Weeks Before

Platform Certification

Ensure that the new release supports your operating system. When available, you can check it in the installation guides. However, you can find the most up-to-date information in the Certifications tab in My Oracle Support.

Use Certifications tab in My Oracle Support to find up-to-date platform certifications

Clients

Check the clients connecting to the database and ensure their client driver version is compatible with Oracle Database 23ai. The client/server interoperability matrix gives you an overview of which clients work with which servers.

Optionally, monitor the database over time to generate a list of clients connecting.

AutoUpgrade

Get the latest version of AutoUpgrade from My Oracle Support. I recommend that you always use the latest version. AutoUpgrade is fully backward compatible, so any newer version can upgrade older releases of Oracle Database.

Upgrade Readiness

Run AutoUpgrade in analyze mode to determine the upgrade readiness of your Oracle Database. Check the summary report for findings with no fixups. Such findings must be dealt with manually.

You can run the analysis even before you install the new Oracle home. Simply specify target_version=23 in your AutoUpgrade config file, and AutoUpgrade knows which checks to run.

Dictionary Check

Check the dictionary on your most important databases. You can run a dictionary check together with an AutoUpgrade analysis. In your config file:

upg1.run_dictionary_health=full

Or, you can run it separately using DBMS_DICTIONARY_CHECK.

Behavior Changes

Review the upgrade guide to find information about deprecated and desupported features, plus any behavior changes.

Grid Infrastructure

If Grid Infrastructure manages your database either in RAC or Oracle Restart configuration, you should upgrade it as well. If you can tolerate an additional maintenance window, I’d recommend upgrading Grid Infrastructure in advance.

Days Before

Statistics

Gather dictionary and fixed objects statistics:

begin
   dbms_stats.gather_fixed_objects_stats;
   dbms_stats.gather_schema_stats('SYS');
   dbms_stats.gather_schema_stats('SYSTEM')
end;

I prefer gathering schema stats rather than dictionary stats. I’ve seen a few edge cases solved by schema stats, and generally, current stats on SYS and SYSTEM are sufficient.

Gather statistics no earlier than seven days before the upgrade. If AutoUpgrade determines your statistics are older than that, it will gather them as part of the upgrade. This extends down time unnecessarily.

If you’re curious, you can find details about the importance of fixed objects statistics in a blog post by Maria Colgan.

Install Oracle Home

Install an Oracle Database 23ai Oracle home. In release 23ai, the Oracle homes that you download are already patched with the latest Release Update.

If you have additional patches to apply, do so now. You should have all patches in place before you start the upgrade.

On the Day of Upgrade

Backup

If time allows, run a level 0 backup. If not, you have to settle with a level 1.

Scheduler

Normally, it is not necessary to disable the scheduler. But in some situations, it can be beneficial.

That’s It

You are now ready to start the journey to Oracle Database 23ai and the many exciting new features.

Happy upgrading!

Oracle Database 23ai Is Here – Time to Sharpen Multitenant Skills

Last week Oracle announced the release of Oracle Database 23ai with many significant enhancements. It is available in Oracle Cloud Infrastructure but according to Release Schedule of Current Database Releases (Doc ID 742060.1) other platforms are following soon.

One important thing about Oracle Database 23ai is that it only supports the multitenant architecture. Once you upgrade beyond Oracle Database 19c, you must also convert your database to a pluggable database.

To give you the best possible starting point for the multitenant migration, our team has prepared two webinars about the multitenant architecture. Actually, we planned on just one. Still, we have so many things to share that we decided to make two webinars. When product managers get a chance to talk, they talk a lot!

Move to Oracle Database 23ai – Everything you need to know about Oracle Multitenant

Part 1

May 16, 14:00 CEST, 2024

  • Multitenant architecure
    • Introduction
    • Consolidation strategies
    • Benefits
  • Creation of container database
    • Recommendations
    • Parameters
  • Migration methods
    • Best practices
    • Data Guard
    • RAC
    • Transparent Data Encryption (TDE)
    • What to remember after migration
    • Rollback and fallback
    • Customer case

Part 2

June 27, 14:00 CEST, 2024

  • Operations
    • Cloning
    • Connecting
    • Running scripts
    • Resource Manager
    • Tips and tricks
    • Recommendations
    • Customer case
  • Patching
    • Entire CDB
    • Individual PDB
    • Recommendations
    • Datapatch
  • Upgrading
    • CDB upgrades
    • PDB upgrades (unplug-plug)
    • Replay upgrade
    • Best practices
    • Downgrade
    • Customer case

Sign Up

You can sign up here. My team (Mike, Rodrigo, Roy and Alex) will be there and answer all your questions. I promise we won’t end the webinar until there are no more questions.

If you miss the webinar, you can watch a recording later on our YouTube channel. Be sure to subscribe so you don’t miss out.

Even if you already use Oracle Database on multitenant architecture, I guarantee there are still new things to learn.

As always: All tech, no marketing!

Show Me Your Upgrade Runbook and Let’s See What You Can Remove

Recently, I talked to a customer about upgrading Oracle Database. I was showing how you can get the most out of AutoUpgrade. The customer showed me their runbook, and together, we found several redundant tasks. AutoUpgrade already handled many of the checks and tasks.

Leave a comment and show me the tasks in your runbook. Let’s see if we can find something to remove.

What Did We Find At the Customer?

Before Upgrade

  • Purging recycle bin The customer actively used the recycle bin and manually purged it before upgrading. However, AutoUpgrade has a check on the recycle bin. From the prechecks log file:
    2024-04-17 10:15:51.775 INFO Running check PURGE_RECYCLEBIN [DB12] - CheckTrigger.call#96 
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM sys.recyclebin$'
    And purges it – if needed – during prefixups. From the prefixups log file:
    2024-04-17 10:15:44.972 INFO Running fixup [PURGE_RECYCLEBIN][DB12][PLSQL][PURGE DBA_RECYCLEBIN
2024-04-17 10:15:44.972 INFO [EB5A67] [PURGE DBA_RECYCLEBIN
2024-04-17 10:15:44.976 INFO [EB5A67] Executing SQL [/* [EB5A67]  */PURGE DBA_RECYCLEBIN
2024-04-17 10:15:45.316 INFO # PURGE_RECYCLEBIN - Check.runFix#292 
2024-04-17 10:15:45.317 INFO Finished fixup [PURGE_RECYCLEBIN][DB12][SUCCESSFUL] - FixUpTrigger.executeFixUp#231

After Upgrade

  • Execute catuppst.sql This script performs:

    … remaining upgrade actions that do not require that the database be open in UPGRADE mode

    AutoUpgrade handles this part for you. Here is an extract from the phase overview during upgrade:

    $ grep -B2 "catuppst"  dbupgrade/phase.log
[phase 102] type is 1 with 1 Files

@catuppst.sql

  • Recompiling objects The customer recompiled objects using utlrp.sql. This is usually not needed. By default, AutoUpgrade performs a recompilation in the postfixups phase unless you override the behavior with run_utlrp. Here is an extract from the autocompile log file found in the postfixups directory:

    13:12:42   6      select count(*) into cnt from sys.dba_objects
13:12:42   7         where status = 'INVALID';
13:12:42   8      IF cnt > 0 THEN
13:12:42   9          :utlprp_name := '/u01/app/oracle/product/23.0.0.0/dbhome_2/rdbms/admin/utlprpom.sql 2';
13:12:42  10          :utldtchk_name := '/u01/app/oracle/product/23.0.0.0/dbhome_2/rdbms/admin/utldtchk.sql';
13:12:42  11      END IF;

  • Datapatch The customer executed Datapatch after a successful upgrade. The upgrade engine already does this as one of the very last phases. Here is an extract from upg_summary.log:

    Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

...
Datapatch                                                        00:00:09

  • Gathering database statistics The customer already knew that AutoUpgrade gathers dictionary statistics and that they needed to gather fixed objects statistics after a while. But, they were gathering database statistics to refresh the statistics on their application schemas. Although this is a good idea in some cases, I would not recommend this. AutoUpgrade does not touch user data during a database upgrade, so if you had good statistics before the upgrade, they are also good after. The only case I can think of is upgrades from Oracle Database 11.2.0.4. Oracle Database introduced new histogram types in Oracle Database 12c, which could significantly benefit some databases.

    By the way, here is a handy trick to gather fixed objects statistics some time after the upgrade.

Conclusion

While working with this customer, we found several redundant items. Getting those items out of the runbook means less overall downtime – and a less complex upgrade. AutoUpgrade makes upgrading Oracle Database much easier.

Please leave a comment if there are tasks that AutoUpgrade should do for you.

Appendix

Further Reading

Are Your Oracle Database Clients Ready for the next Database Upgrade?

Each new Oracle Database release changes the client/server communication protocol. A newer protocol supports new features, strengthens security, and so forth. Before upgrading your Oracle Database, you must ensure your clients can connect afterward. If not, your clients may face:

ORA-28040: No matching authentication procotol error
ORA-03134: Connections to this server version are no longer supported

Oracle lists the supported clients in the client/server interoperability support matrix (Doc ID 207303.1). Take a look – it is probably the most colorful MOS note.

For Oracle Database 23ai, your clients must be version:

  • 23ai
  • 21c
  • 19c

How do you know if your clients are ready for the next release?

A Solution

The view V$SESSION_CONNECT_INFO lists the client driver and version of connected sessions. You can join V$SESSION to get more details.

SELECT PROGRAM, CLIENT_DRIVER, CLIENT_VERSION 
FROM   GV$SESSION S, GV$SESSION_CONNECT_INFO CI 
WHERE  S.SID=CI.SID AND S.SERIAL#=CI.SERIAL#

Here’s is an example of how that data might look like:

PROGRAM CLIENT_DRIVER CLIENT_VERSION
oracle SERVER 19.20.0.0.0
MyApp.exe ODPM.NET : 19.1.0.0.0 19.1.0.0.0
MyApp.exe ODPU.NET : 19.21.0.0.0 19.21.0.0.0
MyApp2.exe (null) 19.21.0.0.0
emagent jdbcthin 12.1.0.2.0
SQL Developer jdbcthin : 21.4.0.0.0 21.4.0.0.0
java jdbcoci : 19.22.0.0.0 19.22.0.0.0
sqlplus SQL*PLUS 19.22.0.0.0
  • If I want to upgrade to Oracle Database 23ai, it looks good except for the emagent with a 12.1.0.2 driver.
  • When client driver is SERVER it is the database itself making connections. In this case, the scheduler was running jobs. You can disregard those entries.
  • The entry with client driver (null) was a thick OCI client used by a C++ program.

The above query gives an overview of the situation right now. But we need to persist the data to check all clients over time. We can solve that with a scheduler job.

If you monitor your database for weeks or a month, you should know which clients connect to your Oracle Database.

A high-five to my friend Frank for helping with some sample connection data.

The Code

Here’s a piece of code that creates a schema, a few objects, and a scheduler job.

The scheduler runs the job every 5 minutes. The job finds new connections, notes the client’s driver, and stores that info in a table.

Run the following code as SYS AS SYSDBA:

--Create a schema
DROP USER ORA_CLIENT_CHECK CASCADE;
CREATE USER ORA_CLIENT_CHECK NO AUTHENTICATION;

--Required privileges
GRANT SELECT ON GV_$SESSION TO ORA_CLIENT_CHECK;
GRANT SELECT ON GV_$SESSION_CONNECT_INFO TO ORA_CLIENT_CHECK;
ALTER USER ORA_CLIENT_CHECK DEFAULT TABLESPACE USERS;
ALTER USER ORA_CLIENT_CHECK QUOTA UNLIMITED ON USERS;

--View contains a current list of clients connected
CREATE VIEW ORA_CLIENT_CHECK.CURRENT_SESSIONS AS (
SELECT DISTINCT S.MACHINE, S.OSUSER, S.PROGRAM, S.MODULE, CLIENT_DRIVER, CLIENT_VERSION FROM GV$SESSION S, GV$SESSION_CONNECT_INFO CI WHERE S.SID=CI.SID AND S.SERIAL#=CI.SERIAL# AND CI.CLIENT_DRIVER != 'SERVER');

--Create a table to hold the result
CREATE TABLE ORA_CLIENT_CHECK.CONNECTED_SESSIONS AS SELECT * FROM ORA_CLIENT_CHECK.CURRENT_SESSIONS WHERE 1=0;

--Create a scheduler job that runs every 5 minutes
BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => '"ORA_CLIENT_CHECK"."GET_NEW_CONNECTIONS"',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN MERGE INTO ORA_CLIENT_CHECK.CONNECTED_SESSIONS OLD USING (SELECT * FROM ORA_CLIENT_CHECK.CURRENT_SESSIONS) NEW ON (OLD.MACHINE=NEW.MACHINE AND OLD.OSUSER=NEW.OSUSER AND OLD.PROGRAM=NEW.PROGRAM AND OLD.MODULE=NEW.MODULE AND OLD.CLIENT_DRIVER=NEW.CLIENT_DRIVER AND OLD.CLIENT_VERSION=NEW.CLIENT_VERSION) WHEN NOT MATCHED THEN INSERT (MACHINE, OSUSER, PROGRAM, MODULE, CLIENT_DRIVER, CLIENT_VERSION) VALUES(NEW.MACHINE, NEW.OSUSER, NEW.PROGRAM, NEW.MODULE, NEW.CLIENT_DRIVER, NEW.CLIENT_VERSION); COMMIT; END;',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
      end_date        => NULL,
      enabled         => TRUE,
      comments        => 'Checks for new connections and store the client version/driver to table.');
END;
/

Query the table to find clients that you must upgrade:

SELECT * 
FROM   ORA_CLIENT_CHECK.CONNECTED_SESSIONS;

The Limitations

  • The code monitors a single database only.

  • If you use Active Data Guard, you must consider the users that connect to the standby only. You can use DML redirect to send the data back to the primary database.

  • If you have a job that connects very seldom or connects for a very short time only, there is a risk that the code won’t capture it.

Other Ideas

Unified Auditing captures much information, but the client driver and version is not part of it. If you can’t sample from V$SESSION_CONNECT_INFO, this could be a viable solution.

You can use the below query to find a unique list of sessions and the program they used to connect. Then, you connect each host and manually ensure that the client driver is up-to-date:

SELECT DISTINCT userhost, program, client_program_name 
FROM   unified_audit_trail;

Of course, this requires you to have configured Unified Auditing.

How to Upgrade Encrypted Oracle Database and Move to New Server

Is Autoupgrade with TDE only possible for in place upgrade (same server)? Are there any ways to do it for out of place (new db home in a different server) with autoupgrade? It seems like the target_home have to be specified.

A reader asked that question on my blog.

The answer is yes; you can upgrade an Oracle Database and move to a new server. We are considering upgrading a non-CDB or an entire CDB using Transparent Data Encryption (TDE) Tablespace Encryption.

Move to New Server and Transparent Data Encryption

When you upgrade your Oracle Database, you often want to move to new hardware. AutoUpgrade fully supports this use case. Mike Dietrich mentions this in his blog post and video.

When you upgrade an encrypted non-CDB or entire CDB, the database must have an auto-login keystore.

There are no further requirements.

The Instructions

I am using the DB12 database from our hands-on lab. You can provision a lab and try it out yourself (for free). See the appendix for instructions on how to encrypt the DB12 database.

Old Server

  1. Always use the latest version of AutoUpgrade.
  2. Create a config file:
    upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.sid=DB12
upg1.target_version=19
    • I don’t specify target_home because it does not exist on the old server. Instead, I specify target_version, so AutoUpgrade knows which checks to execute.
  3. Check the database for upgrade readiness:
    java -jar autoupgrade.jar -config DB12.cfg -mode analyze
  4. Downtime starts.
  5. Run the preupgrade fixups:
    java -jar autoupgrade.jar -config DB12.cfg -mode fixups
  6. Perform a clean shutdown of the database
    shutdown immediate

New Server

There is only one server in the lab environment, so I can’t physically move to a new server. But by moving the instance manually to the new home, I can simulate the same behavior.

  1. Move SPFile and password file to the new Oracle home on the new server. The below instructions work in the hands-on lab only:
    export OLD_ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
export NEW_ORACLE_HOME=/u01/app/oracle/product/19
export ORACLE_SID=DB12
cp $OLD_ORACLE_HOME/dbs/spfile$ORACLE_SID.ora $NEW_ORACLE_HOME/dbs
cp $OLD_ORACLE_HOME/dbs/orapw$ORACLE_SID $NEW_ORACLE_HOME/dbs
  2. Register the instance in /etc/oratab:
    export NEW_ORACLE_HOME=/u01/app/oracle/product/19
export ORACLE_SID=DB12   
cp /etc/oratab /tmp/oratab
sed '/^'"$ORACLE_SID"':/d' /tmp/oratab > /etc/oratab
echo "$ORACLE_SID:$NEW_ORACLE_HOME:N" >> /etc/oratab
cat /etc/oratab
    • Use srvctl as well if you have Oracle Grid Infrastructure.
  3. Move the database files (control files, redo logs, and data and temp files) to the new server.
    • If you need to change any of the paths, see the appendix.
    • Alternatively, unmount the storage from the old server and mount it on the new one.
  4. I want to use the new wallet_root parameter to configure TDE. I copy the keystore files to a new location that matches the naming requirements of wallet_root:
    export OLD_KEYSTORE=$ORACLE_BASE/admin/$ORACLE_SID/wallet
export NEW_KEYSTORE=$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde
mkdir -p $NEW_KEYSTORE
cp $OLD_KEYSTORE/cwallet.sso $NEW_KEYSTORE
cp $OLD_KEYSTORE/ewallet.p12 $NEW_KEYSTORE
    • You should consider moving any backup keystore files as well.
  5. I start a new instance of the database in the new Oracle home and configure TDE using the new parameters:
    export ORACLE_HOME=/u01/app/oracle/product/19
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba<<EOF
   startup nomount
   alter system set wallet_root='$ORACLE_BASE/admin/$ORACLE_SID/wallet' scope=spfile;
   shutdown immediate
   startup nomount
   alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;
EOF
  6. Start the instance in upgrade mode:
    sqlplus / as sysdba<<EOF
   alter database mount;
   alter database open upgrade;
EOF
  7. Create an AutoUpgrade config file:
    upg1.target_home=/u01/app/oracle/product/19
upg1.sid=DB12
  8. Start AutoUpgrade in upgrade mode:
    java -jar autoupgrade.jar -config DB12.cfg -mode upgrade

That’s it! I just upgraded my encrypted Oracle Database and moved it to a new server.

Appendix

Keystore Type

You must have an auto-login database keystore to upgrade it on the new server. Check the keystore type in the source database:

select wrl_type, wallet_type from v$encryption_wallet;
  • AUTOLOGIN – You can copy the auto-login keystore file (cwallet.sso) from the old to the new server.
  • LOCAL_AUTOLOGIN – The keystore file is bound to the old server. You must create a new auto-login keystore on the new server.

To create a new local auto-login keystore:

startup mount
administer key management create local auto_login keystore ...;
shutdown immediate
startup upgrade

Hands-on Lab

If you want to try the procedure in our hands-on lab, you can use these instructions to encrypt the DB12 database.

  1. Add TDE configuration to sqlnet.ora:
echo 'ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet)))' >> $ORACLE_HOME/network/admin/sqlnet.ora
  1. Create keystore directory:
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet
  1. Create the keystore and complete the TDE configuration:
sqlplus / as sysdba <<EOF
   --Restart to re-read sqlnet.ora with keystore setting
   shutdown immediate
   startup
   
   --Configure TDE
   ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet' IDENTIFIED BY "<tde-keystore-pwd>";
   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<tde-keystore-pwd>";
   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<tde-keystore-pwd>" WITH BACKUP;
   ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet' IDENTIFIED BY "<tde-keystore-pwd>";

   --Create data
   create tablespace users2 encryption encrypt;
   grant dba to appuser identified by oracle;  
   create table appuser.t1 tablespace users2 as select * from all_objects;
EOF

Locations

In the instructions, I am using the same paths for the database files. If you need to change the location of the control file or redo logs, then it might be easier to create a PFile on the source and use that instead of the SPFile.

If you need to change the location of data or temp files, it might be easier to re-create the control file. In this case, you need an alter database backup controlfile to '/tmp/ctl.txt' on the source database. Edit the trace file to generate the create controlfile statement.