Lessons from my journey with databases

LinkedIn
Youtube
Livelabs
About
, ,

Fast Track your Oracle Database Upgrade from 19c to 23ai in Exadata Database Service on Dedicated Infrastructure using AutoUpgrade

Published by

Amalraj Puthenchira

on

There are multiple ways to Upgrade your Exadata Database Service on Dedicated Infrastructure from 19c to 23ai(or 26ai). One of them is to use the Oracle Cloud Console for Upgrading the database which is easy.

What if ?

  • You need to reduce database upgrade time.
  • You need the flexibility to upgrade only specific PDBs

This blog explains how you can do that.

This article was originally tested for 19c to 23ai. The same AutoUpgrade refreshable-clone approach also applies when the target CDB is Oracle AI Database 26ai

Let’s divide the article into the following sections.

Concept

Step 0 – Prerequisites

Step 1 – Download latest AutoUpgrade file

Step 2 – Create Database link

Step 3 – Create Configuration File

Step 4 – Load TDE encryption password

Step 5 – Analyze and Fixup

Step 6 – Database Upgrade

Step 7 – Post Upgrade

Concept

We will use AutoUpgrade for Upgrading a specific PDB from 19c to 23ai.

As part of the process , AutoUpgrade will clone the PDB from Source CDB to the Target CDB , refresh it multiple times and perform the Upgrade and Post Upgrade steps.

Please note that cloning and refresh happen before the window; outage mainly covers final refresh, upgrade, service cutover, and validation.

Environment :

Source Database : SXA (19.24)

Target Database : TXA (23.5)

PDB to be upgraded : SPDB

Both Source and Target Databases are in same VM cluster.

Step 0 – Prerequisites

Below are the requirements for Source and Target CDBs.

  • You have created the target release CDB, and opened the CDB before starting the unplug-plug upgrade.
  • The set of Oracle Database components configured for the target release CDB include all of the components available on the source CDB.
  • The source and target CDBs have compatible character sets and national character sets
  • External authentication (operating system authentication) is enabled for the source and target CDBs
  • The Oracle APEX installation type on the source CDBs should match the installation type on the target CDB.
  • There should be no existing guaranteed restore point (GRP) on the non-CDB Oracle Database that you want to plug in to the CDB.

Please note that I have removed some of the requirements from documentation since we are dealing with ExaDB-D where some of the requirements are already met.

It is highly recommended to go through the AutoUpgrade documentation and also review the concept of Refreshable PDB before attempting customer database upgrades.

Step 1 – Download latest AutoUpgrade file

AutoUpgrade is part of Oracle Database Binaries since 19.3 , however it is always recommended to download the latest file from MOS.

Copy the latest file to $ORACLE_HOME/rdbms/admin of both Source and Target CDBs.

We can also copy to another location of our choice.

Step 2 – Create Database Link

We need a database link to create a refreshable clone in target CDB from the source PDB.

Follow below steps.

Source Database

Create a user with below privileges on the Source Database root container.

We will use this user while creating database link.

CREATE USER c##clone IDENTIFIED BY some-password CONTAINER=ALL; 
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SELECT_CATALOG_ROLE TO c##clone CONTAINER=ALL;
GRANT READ ON sys.enc$ TO c##clone CONTAINER=ALL;

Target Database

On target CDB , create a database link pointing to source CDB using below SQL.

CREATE DATABASE LINK <source_db_name> CONNECT TO c##clone IDENTIFIED BY <password> USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = <source_db_scan_name>)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SID= <SID>)))';

Please note that it is important to use the above format (with SID) for RAC databases otherwise you will face error similar to below while executing AutoUpgrade modes.

Unable to determine the traces files directory for entry <entry in config file>

This is because service based connection(instead of SID) can redirect the database link to any available database instance and this error will be reported whenever database link tries to connect to the instance which is running on a different node than the node from which you are executing AutoUpgrade.

Refer MOS note : 3044069.1 for more details.

Verify the database link using below SQL , you should be able to get source database name as output.

select name from v$database@<db_link_name>;

If you are interested in using a database link name other than the source database name , you will have to set init parameter global_names to false on the target CDB.

Step 3 – Create configuration file

You can use below AutoUpgrade command to create a sample configuration file.

java -jar <oracle_home_path>/rdbms/admin/autoupgrade.jar -create_sample_file config

I have created a file manually with below contents.

global.autoupg_log_dir=/u02/app/oracle/cfgtoollogs/autoupgrade

global.keystore=/u02/app/oracle/cfgtoollogs/keystore

upg1.source_home=/u02/app/oracle/product/19.0.0.0/dbhome_2

upg1.target_home=/u02/app/oracle/product/23.0.0.0/dbhome_3

upg1.sid=sxa1

upg1.pdbs=SPDB

upg1.target_cdb=txa1

upg1.source_dblink.SPDB=SXA 60

upg1.target_pdb_copy_option.SPDB=file_name_convert=none

upg1.start_time=06/11/2024 06:45:00

Please refer AutoUpgrade Documentation to understand above parameters.

In this case AutoUpgrade is going to Upgrade a PDB named SPDB (in CDB SXA) and as part of Upgrade it will be cloned to TXA database using database link SXA with a refresh frequency of 60 seconds.

Step 4 – Load TDE encryption password

AutoUpgrade will require TDE password for both Source and Target CDBs.

We can load the password to AutoUpgrade wallet store using below commands.

java -jar <oracle_home_path>/rdbms/admin/autoupgrade.jar -config <config_file_name> -load_password

If this is our first database for which we are loading password , AutoUpgrade will create a keystore and it will ask for a keystore password.

Once we have provided a keystore password , we can add TDE password for Source and Target Database using below command.

ADD <SID>

We need to add both Source and Target CDBs , it is better to convert our wallet to auto-login using SAVE command.

Below is sample output.

Step 5 – Analyze and Fixups

Before starting with any Database Upgrade we need to check the readiness of the database.

AutoUpgrade helps us to do this by running it in Analyze mode.

We can use similar command as below to initiate AutoUpgrade in Analyze mode.

java -jar <oracle_home_path>/rdbms/admin/autoupgrade.jar -config <config_file_name> -mode analyze

Below is sample output.

We can perform Analyze in normal business hours since it doesn’t modify anything on the database.

You will receive an output similar to below at the end of job execution.

Let’s check the summary report (status.log) to know the next action.

In my case there were no further manual actions.

Below is sample output.

Next step is to perform any corrections identified as part of the Analyze mode on the database before the upgrade.

AutoUpgrade comes to the help again since it has automated lot of fixups.

If there were any manual steps required that will be highlighted in summary report of Analyze mode.

We can initiate Fixups using below command.

java -jar <oracle_home_path>/rdbms/admin/autoupgrade.jar -config <config_file_name> -mode fixups

Below is sample output.

When you run AutoUpgrade in Fixups mode, AutoUpgrade performs the checks that it also performs in Analyze mode. After completing these checks, AutoUpgrade then performs all automated fixups that are required to fix before you start an upgrade.

AutoUpgrade Fixups mode will make changes to your database, it is recommended to perform this when you when you are very close to the maintenance window.

There were no errors or issues reported for my database in Fixups mode.

Below is output of summary report.

Step 6 – Pluggable Database Upgrade

Okay , we are all set to perform Pluggable Database Upgrade.

We will use AutoUpgrade Deploy mode to initiate the Pluggable database Upgrade.

As mentioned in the beginning AutoUpgrade will start with the PDB clone and will keep refreshing the PDB until the start time specified in the configuration file.

AutoUpgrade will perform final refresh of the PDB at the start time specified and will proceed with Upgrade steps.

Please keep in mind that you should stop all the applications pointing to source PDB before the start time of upgrade so that you don’t miss any changes to Source Pluggable Database.

Let’s initiate the Deploy mode using below command.

java -jar <oracle_home_path>/rdbms/admin/autoupgrade.jar -config <config_file_name> -mode deploy

Below is sample output after initiating the command.

You can see the progress of the Upgrade using various commands.

Below is a sample output.

At the end of the job you can check the summary report to find out the final status.

Below is sample output.

That’s it , we have completed the PDB Upgrade.

Step 7 – Post Upgrade

AutoUpgrade takes care of post upgrade tasks related to PDB upgrade.

However there are some additional tasks that we need to do.

Database Backup

It is highly recommended to perform a full backup of CDB after the upgrade.

PDB service

Any PDB created outside of tooling will be recognised by cloud tooling after some time , however the newly created PDB (in this case created as part of Upgrade) will miss the below default service.

<CDB_NAME>_<PDB_NAME>_paas.oracle.com

This means our PDB won’t be started whenever the cluster or CDB is restarted unless you have created another custom service.

It is always recommended to create an application specific service which will help you to overcome this issue.

Application Connectivity

Whether you need to make changes to application to connect to new PDB depends on your configuration.

If your Source and Target CDBs are in same VM cluster (same as this use case) , your only change will be service_name in your connect string.

If you create a service name for the Upgraded PDB same as the source PDB and disable or remove the service in Source PDB , you can even connect without any application changes to Target PDB which is cool.

Still thinking how this method will reduce downtime ?

We are dealing with PDB Upgrade in this method which means there is no time required for CDB$Root Upgrade which will give some reduction in the overall Database Upgrade time.

Wish you good luck with your Database Upgrades.

Leave a Reply