Upgrade to Oracle 23ai using Oracle Best Practices

Upgrade to Oracle 23ai using Oracle Best Practices

Have outlined detailed steps in 5 phases for upgrading Oracle Database from version 19c to 23ai. Oracle Database 23ai introduces significant changes, most notably the mandatory Multitenant architecture, which requires all databases to be either Container Databases (CDBs) or Pluggable Databases (PDBs). The AutoUpgrade utility is highly recommended for this process due to its automation capabilities and enhanced diagnosability

.

Phase1:  Key Considerations for Oracle Database 23ai

1.1 Verify Upgrade Path:

1.2: Oracle 23ai supports multi-tenant architecture only, so we need to convert non-CDB to a PDB. Single-tenant (1 PDB, no extra license) or multitenant (multiple PDBs, extra license if > 3PDBs)

1.3 Generally, no application changes are needed to use a pluggable database.

Phase2. Pre-Upgrade Steps

Thorough preparation is crucial for a successful database upgrade. These steps should be performed on your source 19c database.

2.1. Download and Install Oracle Database 23ai Software

From 23ai onwards, we can download Gold image which is updated with the latest release update. Install the Oracle Database 23ai software into a new Oracle Home. Do not install it into the existing 19c Oracle Home. 

2.2. Download the Latest AutoUpgrade Utility

Always use the latest version of AutoUpgrade, which can be downloaded from My Oracle Support (MOS) Note: 2485457.1 or directly from oracle.com

Verify AutoUpgrade version

$java -jar autoupgrade.jar –version

Auto Upgrade Execution Flow 

Plugin → Upgrade → Convert (non CDB ->PDB)

Note: NON-CDB to PDB conversion is irreversible.

2.3. Perform Pre-Upgrade Checks with AutoUpgrade

Run AutoUpgrade in analyze mode to identify potential issues and generate a pre-upgrade report. This step is critical for both Non-CDB and PDB upgrades.

#step1: ANALYSE - Check readiness (run on source)

java -jar autoupgrade.jar -config <config_file.cfg> -mode analyze

Example Configuration File (config_file.cfg) for Non-CDB to PDB Upgrade:

# oracle23ai_upgrade.cfg

global.autoupg_log_dir=/u02/AutoUpgrade

global.keystore=/u02/keystore

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

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

upg1.sid=NCDB191

upg1.target_cdb=DB23ai1

Review the generated pre-upgrade report (status.log in autoupg_log_dir) carefully. Address any reported issues before proceeding.

2.4. Run Pre-Upgrade Fixups

AutoUpgrade can automatically fix many pre-upgrade issues. Run it in fixups mode:

#step 2: Fixups - Apply automatic fixes

java -jar autoupgrade.jar -config oracle23ai_upgrade.cfg -mode fixups

2.5. Backup Your Database

Perform a full backup of your 19c database. This is a critical step for disaster recovery

. Oracle strongly recommends at least a level 1 backup, or a level 0 backup if time permits

.

Phase 3. Upgrade Execution

This section details the upgrade process using AutoUpgrade.

3.1. Deploy the Upgrade

Execute AutoUpgrade in deploy mode to start the upgrade process. This will handle the shutdown of the 19c database, the upgrade to 23ai, and the Non-CDB to PDB conversion if applicable.

#step3 - Deploy - Execute the full upgrade 

java -jar autoupgrade.jar -config oracle23ai_upgrade.cfg -mode deploy

Monitor the progress through the AutoUpgrade logs. AutoUpgrade provides detailed logging and the ability to resume after a failure

.

3.2. Specifics for Non-CDB to PDB Conversion

During the deploy phase, AutoUpgrade will:

1. Shutdown the 19c Non-CDB.

2. Convert the Non-CDB into a PDB.

3. Plug the newly created PDB into the specified 23ai target CDB.

4. .Open the PDB in the 23ai CDB, initiating the upgrade of the PDB's data dictionary.

3.3. Specifics for PDB Upgrade (within a CDB)

If you are upgrading a 19c PDB within a 19c CDB to a 23ai PDB within a 23ai CDB, AutoUpgrade will manage the unplugging from the source CDB and plugging into the target 23ai CDB, followed by the PDB upgrade.

Alternatively, for PDBs, you can use the Replay Upgrade feature:

1.Perform pre-upgrade tasks and fixups on the 19c PDB.

2.Unplug the 19c PDB from its source CDB.

3.Plug the 19c PDB into the 23ai target CDB using create pluggable database ... nocopy;.

4.Open the PDB in the 23ai CDB: alter pluggable database <PDB_NAME> open;. The CDB will automatically upgrade the PDB's data dictionary

.

5.Note that the PDB will open in restricted mode until datapatch is run

.

Phase 4. Post-Upgrade Steps

After the upgrade completes, several post-upgrade tasks are necessary to ensure the database is fully functional and optimized.

4.1. Backup Database  (immediate post-upgrade)

Backup Database after migration (Level 0 incremental)

Check and configure standby databases

4.2. Run Datapatch

Apply any necessary patches to the upgraded database using datapatch. This is crucial for both CDBs and PDBs.

SQL

$ORACLE_HOME/OPatch/datapatch -pdbs <PDB_NAME> -- For PDBs $ORACLE_HOME/OPatch/datapatch -all -- For CDBs

4.3. Run Post-Upgrade Fixups

Execute AutoUpgrade in postfixups mode to apply any remaining post-upgrade fixes.

SQL

java -jar autoupgrade.jar -preupgrade "dir=/home/oracle/logs/orange-replay/fixups,inclusion_list=<PDB_NAME>" -mode postfixups

4.4. Review and Adjust Initialization Parameters

Review the new 23ai initialization parameters and adjust them as needed for optimal performance and compatibility. Pay close attention to parameters related to the Multitenant architecture.

ALTER SYSTEM SET “_cursor_obsolete_threshold”=1024;

ALTER SYSTEM SET “_sql_plan_directive_mgmt_control”=0;

ALTER SYSTEM SET “_column_tracking_level”=1;

4.5. Recompile Invalid Objects

Recompile any invalid database objects. This can be done using the utlrp.sql script.

SQL

@?/rdbms/admin/utlrp.sql

4.6.Performance Stability

• DO NOT gather new optimizer statistics after upgrade

• DO NOT gather system statistics (defaults are usually fine)

• Use SQL plan management (SPM) for plan standby

• Load SQL Plan baselines from SQL Tuning sets.

• Run SQL performance Analyzer (SPA) to identify regressions.

4.7. Perform Application Testing

Thoroughly test all applications connected to the upgraded database to ensure full compatibility and functionality.

Phase 5. Fallback Options

In case of an issue during or after the upgrade, having a robust fallback plan is essential. AutoUpgrade provides options for restoring a failed attempt

. Other fallback strategies include:

•Restore from RMAN Backup: Restore the database from the pre-upgrade RMAN backup.

•Data Guard with Redo Apply Disabled: If using Data Guard, disable redo apply on the standby database before the upgrade to preserve a 19c copy.

•Refreshable Clone PDBs: Utilize refreshable clone PDBs for testing and potential fallback scenarios

What is the difference between startup Upgrade and Migrate

What is the difference between startup Upgrade and Migrate:



startup migrate:
---------------
Used to upgrade a database till 9i.


Startup Upgrade ?
---------------
From 10G  we are using startup upgrade to upgrade database.


What happens internally when you use startup upgrade/migrate?
------------------------------------------------------------
It will adjust few database (init) parameters (irrespective of what you have defined) automatically to certain values in order to run upgrade scripts smoothely.

in other way..it will issue few alter statements to set certain parameters which are required to complete the upgrade scripts without any issues.

Database Upgrade to 10.2.0.4

Database Upgrade 10G to 10.2.0.4 - DB Upgrade to 10.2.0.4::

Please fine the below steps which will outline 10.2.0.4 DB upgrade action plan.

0)Check the status of all the components before the upgrade:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

SQL>select count(*) from dba_objects where status='INVALID';

1)Download and unzip patch p6810189_10204_Linux-x86-64.zip in /stage directory.

2)Shutdown database and listener

Set the following env variables:

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch:$PATH:

ORACLE_HOME=Oracle_home

ORACLE_SID=sid

export ORACLE_HOME ORACLE_SID

3)Install the 10.2.0.4 patch set

From VNC launch the runinstaller.

cd /stage/Disk1

./runInstaller

On the Welcome screen, click Next.....

4)Upgrading the databse to 10.2.0.4

a)Run the Pre-Upgrade Information Tool

Start the database in the UPGRADE mode:

sqlplus '/as sysdba'

SQL> STARTUP UPGRADE

SQL> SPOOL upgrade_info.log

SQL> @?/rdbms/admin/utlu102i.sql

SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file,Make sure all are fine.

b)Upgrade the Database

SQL> STARTUP UPGRADE

SQL> SPOOL patch.log

SQL> @?/rdbms/admin/catupgrd.sql

SQL> SPOOL OFF

c)Restart the database:& Compile Invalids.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

SQL>@?/rdbms/admin/utlrp.sql

5)Check the status of all the components after the upgrade:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

SQL>select count(*) from dba_objects where status='INVALID';