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 '<connection-string-to-source-pdb>';
  

• 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> add CDB19
  Enter your secret/Password:    
  Re-enter your secret/Password: 
  TDE> 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> 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

• Blog post series, Upgrade to Oracle AI Database 26ai
• Hey, Let Me Kill Your Network!