Control The Final Refresh When Using Refreshable Clone PDBs in AutoUpgrade

When you migrate or upgrade with refreshable clone PDBs, you sometimes want to decide when the final refresh happens. Perhaps you must finish certain activities in the source database before moving on.

Here’s how to do that in AutoUpgrade.

The Final Refresh Dilemma

In AutoUpgrade, the final refresh happens at the time specified by the config file parameter start_time.

You specify start_time in the config file, but once you start the job, you cannot change it. Remember that you normally start AutoUpgrade a long time before start_time to allow the creation of the new PDB.

In some situations, you want more control. You might want to finish some work on the source database before AutoUpgrade starts the final refresh. Perhaps you need to kick users off or coordinate activities with other teams.

In that case, a fixed start time is not very flexible.

The Solution

Update: Check out the new proceed command.

Imagine my downtime window starting on Saturday, 30 November, 02:00.

At that time, I need to ask the application team to shut down the applications in the database, I need to run certain pre-migration tasks, and finally kill sessions if needed. So, I don’t want AutoUpgrade to start at 02:00 – I want to decide at which point after 02:00 that AutoUpgrade should start.

Here’s my approach

I create a config file and set the start_time parameter to the start of my downtime window.
```
upg1.start_time=30/11/2024 02:00:00
```
I start AutoUpgrade in deploy mode before my downtime starts:
```
java -jar autoupgrade.jar -config ... -mode deploy
```
- I must start well before the downtime window so AutoUpgrade has enough time to copy the database.
- Imagine my tests show it takes around four hours to copy the database. I decide to start on Friday, 29 November, 16:00, so the copy should end around 20:00 – well enough time before my downtime window.

AutoUpgrade now starts the CLONEPDB phase:

+----+-------+--------+---------+-------+----------+-------+---------------------------+
 |Job#|DB_NAME|   STAGE|OPERATION| STATUS|START_TIME|UPDATED|                    MESSAGE|
 +----+-------+--------+---------+-------+----------+-------+---------------------------+
 | 100|   TEAL|CLONEPDB|EXECUTING|RUNNING|  02:00:00| 4s ago|Creating pluggable database|
 +----+-------+--------+---------+-------+----------+-------+---------------------------+

Note the START_TIME value. It is the time when the final refresh happens.

I wait for AutoUpgrade to create the PDB and enter the REFRESHPDB phase:

+----+-------+----------+---------+-------+----------+-------+----------------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|START_TIME|UPDATED|               MESSAGE|
+----+-------+----------+---------+-------+----------+-------+----------------------+
| 100|   TEAL|REFRESHPDB|EXECUTING|RUNNING|  02:00:00| 2s ago|PDB TEAL was refreshed|
+----+-------+----------+---------+-------+----------+-------+----------------------+

Then I stop the job:
```
upg> stop -job 100
```
- If I exit AutoUpgrade after stopping the job, don’t worry. As soon as I restart AutoUpgrade, it will pick up from where it left and continue with the job.
When I stop the job, there is no periodic refresh. I should refresh the PDB in the target CDB manually at regular intervals:
```
SQL> alter pluggable database teal refresh;
```
- If I don’t perform any periodic refresh, the redo will accumulate, and the final refresh will take longer. Keep the final refresh shorter by refreshing more often.
After the start of my downtime window (the start_time parameter), when I’m done on the source database and want to proceed with the final refresh, I resume the job in AutoUpgrade.
```
upg> resume -job 100
```
AutoUpgrade now realizes it is past the defined start_time and immediately moves on with the final refresh and the rest of the job.

Wrapping Up

Ideally, AutoUpgrade should offer better control over the process. We have a task on our backlog to come up with a better solution.

Update: Use the proceed command in AutoUpgrade to control the start time

However, refreshable clone PDBs are still a fantastic method for non-CDB to PDB migrations and for upgrades of individual PDBs.

There are a few quirks to be aware of, and if you are using Data Guard bear in mind that you can only plug in with deferred recovery. Other than that – it’s just to say…

Happy Migrating!

AutoUpgrade New Features: Update Enterprise Manager Configuration

With the latest version, 24.8, AutoUpgrade can interact with Oracle Enterprise Manager:

Blackout the database
Modify target configuration

Details

AutoUpgrade interacts with Enterprise Manager through the command line interface, EM CLI.
In the DRAIN phase, AutoUpgrade creates a blackout of the target. It will also remove the blackout again after the job.
The blackout is named blackout_<SID>.
AutoUpgrade updates the target configuration in Enterprise Manager to reflect the new Oracle home if the job succeeds.
If you restore the job (returning to the pre-upgrade state with Flashback Database), AutoUpgrade restores the original target configuration.

How To

Install and configure EM CLI on the database host.
Persist the credentials on the database host using the autologin feature.

Get the latest version of AutoUpgrade:

wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

Specify the path to EM CLI in the AutoUpgrade config file:
```
upg1.emcli_path=/u01/app/oracle/oem
```
Specify the target name from Enterprise Manager:
```
upg1.em_target_name=ORCL_myhost.domain.int
```

Happy Upgrading

You can use the functionality when you patch and upgrade your Oracle Database.

If you use Oracle Enterprise Manager, AutoUpgrade can handle even more tasks in your runbook.

What are the next tasks that AutoUpgrade should do for you? Leave a comment, and we’ll consider it.

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

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

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

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

To enter the username and password for my database, I change the group to PWD:’

PWD> group PWD
Group [PWD] is already active

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

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

Exit the keystore.

PWD> exit

AutoUpgrade Password Loader finished - Exiting AutoUpgrade

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!

How to Solve DCS-12300:Failed to Clone PDB During Remote Clone (DBT-19407)

A customer reached out to me:

I want upgrade a PDB from Oracle Database 19c to 23ai. It’s in a Base Database Service in OCI. I use the Remote clone feature in the OCI console but it fails with DCS-12300 because IMEDIA component is installed.

The task:

Clone a PDB using the OCI Console Remote clone feature
From a CDB on Oracle Database 19c to another CDB on Oracle Database 23ai
Upgrade the PDB to Oracle Database 23ai

Let’s see what happens when you clone a PDB:

It fails, as explained by the customer.

Let’s dig a little deeper. Connect as root to the target system and check the DCS agent.

$ dbcli list-jobs

ID                                       Description                                                                 Created                             Status
---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------
...
6e1fa60c-8572-4e08-ba30-cafb705c195e     Remote Pluggable Database:SALES from SALES in db:CDB23                      Tuesday, September 24, 2024, 05:04:13 UTC Failure

$ dbcli describe-job -i 6e1fa60c-8572-4e08-ba30-cafb705c195e

Job details
----------------------------------------------------------------
                     ID:  6e1fa60c-8572-4e08-ba30-cafb705c195e
            Description:  Remote Pluggable Database:SALES from SALES in db:CDB23
                 Status:  Failure
                Created:  September 24, 2024 at 5:04:13 AM UTC
               Progress:  35%
                Message:  DCS-12300:Failed to clone PDB SALES from remote PDB SALES. [[FATAL] [DBT-19407] Database option (IMEDIA) is not installed in Local CDB (CDB23).,
 CAUSE: The database options installed on the Remote CDB(CDB19_979_fra.sub02121342350.daniel.oraclevcn.com) m
             Error Code:  DCS-12300
                  Cause:  Error occurred during cloning the remote PDB.
                 Action:  Refer to DCS agent log, DBCA log for more information.

...

What’s Going on?

First, IMEDIA stands for interMedia and is an old name for the Multimedia component. The ID of Multimedia is ORDIM.

Oracle desupported the Multimedia component:

Desupport of Oracle Multimedia Oracle Multimedia is desupported in Oracle Database 19c, and the implementation is removed. … Oracle Multimedia objects and packages remain in the database. However, these objects and packages no longer function, and raise exceptions if there is an attempt made to use them.

In the customer’s and my case, the Multimedia component is installed in the source PDB, but not present in the target CDB. The target CDB is on Oracle Database 23ai where this component is completely removed.

If you plug in a PDB that has more components than the CDB, you get a plug-in violation, and that’s causing the error.

Here’s how you can check whether Multimedia is installed:

select   con_id, status 
from     cdb_registry 
where    comp_id='ORDIM' 
order by 1;

Solution 1: AutoUpgrade

The best solution is to use AutoUpgrade. Here’s a blog post with all the details.

AutoUpgrade detects that multimedia is already present in the preupgrade phase. Here’s an extract from the preupgrade log file:

INFORMATION ONLY
  ================
    7.  Follow the instructions in the Oracle Multimedia README.txt file in <23
      ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 to determine
      if Oracle Multimedia is being used. If Oracle Multimedia is being used,
      refer to MOS note 2347372.1 for suggestions on replacing Oracle
      Multimedia.

      Oracle Multimedia component (ORDIM) is installed.

      Starting in release 19c, Oracle Multimedia is desupported. Object types
      still exist, but methods and procedures will raise an exception. Refer to
      23 Oracle Database Upgrade Guide, the Oracle Multimedia README.txt file
      in <23 ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 for
      more information.

When AutoUpgrade plugs in the PDB with Multimedia, it’ll see the plug-in violation. But AutoUpgrade is smart and knows that Multimedia is special. It knows that during the upgrade, it will execute the Multimedia removal script. So, it disregards the plug-in violation until the situation is resolved.

AutoUpgrade also handles the upgrade, so it’s a done deal. Easy!

Solution 2: Remove Multimedia

You can also manually remove the Multimedia component in the source PDB before cloning.

I grabbed these instructions from Mike Dietrich’s blog. They work for a 19c CDB:

cd $ORACLE_HOME/rdbms/admin
#First, remove ORDIM in all containers, except root
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b imremdo_pdbs -d $ORACLE_HOME/ord/im/admin imremdo.sql
#Recompile
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
#Last, remove ORDIM in root
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b imremdo_cdb -d $ORACLE_HOME/ord/im/admin imremdo.sql
#Recompile
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
#Remove leftover package in all containers
echo "drop package SYS.ORDIMDPCALLOUTS;" > vi dropim.sql
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b dropim -d '''.''' dropim.sql

Without the Multimedia component cloning via the cloud tooling works, but you are still left with a PDB that you attend to.

If you’re not using AutoUpgrade, you will use a new feature called replay upgrade. The CDB will see that the PDB is a lower-version and start an automatic upgrade. However, you still have some manual pre- and post-upgrade tasks to do.

One of the reasons I prefer using AutoUpgrade.

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;.
- After the upgrade, I can drop the database link.

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.

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.

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.

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!

Upgrade to Oracle Database 23ai

This blog post has been replaced with Upgrade to Oracle AI Database 26ai.

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

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 Oracle Database and Replace the Operating System

A reader asked me for advice on upgrading Oracle Database and replacing the underlying operating system.

Currently on Oracle Database 12.1.0.2
Currently on Windows Server 2012
Upgrade to Oracle Database 19c
Move to new servers with Microsoft Windows Server 2022

What’s the recommended approach for transitioning to Oracle 19c on Windows 2022?

Oracle Data Guard

My first option is always Oracle Data Guard. It is often a superior option. You move the entire database, and the only interruption is a Data Guard switchover.

In this case, where the reader needs to replace the operating system, the first thing to check is platform certifications. Always check platform certifications on My Oracle Support. It has the most up-to-date information.

Here is an overview of the platform certification for Oracle Database 12.1.0.2 and 19c.

Oracle Database 12.1.0.2	Oracle Database 19c
Windows Server 2008
Windows Server 2008 R2
Windows Server 2012
Windows Server 2012 R2	Windows Server 2012 R2
	Windows Server 2016
	Windows Server 2019
	Windows Server 2022

Oracle Database 19c does not support the current platform, Windows Server 2012. Thus, the reader can’t set up a standby database on the new servers and transition via a regular switchover.

Windows Server 2012 R2

Let’s imagine the current servers were using Windows Server 2012 R2. Both database releases support this platform. I would recommend this approach:

Upgrade to Oracle Database 19c on current servers.
Set up new servers with Windows Server 2022.
Create standby database on new server.
Transition to new servers with a regular Data Guard switchver.

This approach requires two maintenance windows. Yet, it is still my favorite because it is very simple.

RMAN Backups

You could also use RMAN and incremental backups. You don’t need much downtime – just the time necessary for a final incremental backup and restore. Like with Data Guard, you bring over the entire database.

RMAN can restore backups from a previous version, and you can use that to your advantage.

Provision new servers with just Oracle Database 19c.
Backup on 12.1.0.2.
Restore and recover the database on the new servers with Oracle Database 19c binaries.
After the final incremental backup, open the new database in upgrade mode and perform the upgrade.

We covered this approach in one of our webinars; you can also find details in this blog post.

Move Storage

You can also unmount the storage from the old server, and attach it to the new server.

Run AutoUpgrade in analyze mode to determine upgrade readiness.
Down time starts.
Run AutoUpgrade in fixup mode to fix any issues preventing the upgrade from starting.
Cleanly shut down the source database.
Move the storage to the new server.
Start the database on the new server in upgrade mode.
Start AutoUpgrade in upgrade mode to complete the upgrade.

This is just a high-level overview. For a real move, there are many more intermediate steps.

Be sure to have a proper rollback plan. You are re-using the data files and AutoUprade in upgrade mode does not create a guaranteed restore point.

Data Pump

Data Pump is also a viable option, especially for smaller, less complex databases. It also enables you to restructure your database, for example:

Transform old BasicFile LOBs to SecureFile
Implement partitioning
Exclude data (for archival)
You can import directly into a higher release and even directly into a PDB.

But – the larger the database, the longer downtime (generally speaking).

When you use Data Pump for upgrades, I recommend using a full database export.

Full Transportable Export/Import

You can also use transportable tablespaces for upgrades. You can even migrate directly into a PDB on Oracle Database 19c.

The downside of transportable tablespace is that you must copy the data files to the new system.

But often, you can unmount the storage and mount the storage on the new servers. This avoids the cumbersome process of copying the data files to the new system.

Another approach is to combine transportable tablespaces with incremental backups, if you want to lower the downtime needed. This approach leaves the original database untouched, leaving you with a perfect rollback option.

Oracle GoldenGate

You could also use Oracle GoldenGate. But for most upgrades, it is overkill, partly because of the restrictions and considerations. I see this as a sensible option only if you have very strict downtime or fallback requirements.

Conclusion

What is the best option?

It depends…

This post helps you make the best decision for your organization.

The Final Refresh Dilemma

The Solution

Wrapping Up

Further Reading

Details

How To

Happy Upgrading

Set-Up

Upgrade Without Operating System Authentication

SQLNET.ORA

Happy Upgrading!

What’s Going on?

Solution 1: AutoUpgrade

Solution 2: Remove Multimedia

Further Reading

My Environment

Source

Target

How To

Prepare AutoUpgrade

Prepare Source PDB

Prepare Target CDB

Analyze and Fix Source PDB

Upgrade

The Fine Print

Before Upgrade

During Upgrade

After Upgrade

Data Guard

Real Application Clusters (RAC)

Further Reading

Weeks Before

Platform Certification

Clients

AutoUpgrade

Upgrade Readiness

Dictionary Check

Behavior Changes

Grid Infrastructure

Days Before

Statistics

Install Oracle Home

On the Day of Upgrade

Backup

Scheduler

That’s It

What Did We Find At the Customer?

Before Upgrade

After Upgrade

Conclusion

Appendix

Further Reading

A Solution

The Code

The Limitations

Other Ideas

Oracle Data Guard

Windows Server 2012 R2

RMAN Backups

Move Storage

Data Pump

Full Transportable Export/Import

Oracle GoldenGate

Conclusion