Migrating from Oracle Database 11.2.0.2 and Beyond to Oracle AI Database 26ai

Upgrading your Oracle database from version 11.2.0.2 to the latest Oracle AI Database 26ai release gives you access to the cutting-edge functionality that Oracle is delivering to this data platform.

This guide will walk you through setting up a new Oracle Enterprise Linux (OEL) 8.7 environment to support 11g, 19c, and 26ai database software. I’ll also highlight some noteworthy features and benefits of 26ai.

Why Upgrade to Oracle AI Database 26ai?

Enhanced Performance: Optimized for faster speeds, reduced latency, and improved response times.

Advanced AI Integration: Features built-in machine learning and Oracle AI Vector Search for in-database artificial intelligence and predictive analytics.

Real-Time Analytics: Instantly processes data to deliver immediate insights and actionable results.

Stronger Security: Includes advanced encryption, multi-factor authentication (MFA), real-time threat detection, and secure AI execution.

Broader Compatibility: Supports a wider range of platforms and integrates seamlessly with modern tools, including enhanced Java support.

Shrink Tablespace: Efficiently reclaims unused space to reduce storage costs.

Automatic Transaction Rollback: Prevents long-running jobs from blocking critical transactions by automatically rolling back problematic transactions.

Fast Ingest Upgrades: Improves performance for partitioned, compressed, and in-memory data loads.

Real-Time SQL Plan Management: Automatically detects and corrects performance regressions in SQL plans.

SQL Analysis Reports: Provides actionable recommendations for developers to optimize SQL code.

True Cache: Offers a self-managed in-memory cache for enhanced performance and scalability.

How to Upgrade to Oracle AI Database 26ai

One way to upgrade directly from any 10g, 11g, or 12c Oracle database is using datapump (exp/expdp) to create a logical export of the source data and import it directly into a 26ai database.

This process has significant disadvantages with particularly large or busy databases, so here we will be using a RMAN backup and upgrading to 26ai by using the traditional 11g upgrade script and the autoupgrade utility for the 19c and 26ai processes.

Upgrading to Oracle 26ai requires a 19c/21c database source and upgrading to 19c requires a version such as 11.2.0.4, 12.1.0.2, 12.2.0.1, or 18.

Overview of the Oracle Database 11.2.0.2 to 26ai Migration Process

• Setup a new Linux server.
• Install 11.2.0.4.
• Restore your database from an RMAN backup.
• Upgrade your database to 11.2.0.4 from 11.2.0.2.
• Install and patch 19c.
• Upgrade from 11g to 19c using autoupgrade.
• Install Oracle AI Database 26ai.
• Create a 26ai Container database (CDB).
• Migrate and upgrade 19c into that CDB as a PDB database.

Migration Assumptions

• You have an RMAN backup of your 11.2.0.2 database that includes the control file and spfile.
• We will be setting up a new server, using Oracle Enterprise Linux 8.7.
• We will be using Oracle’s Virtual Box to host the new server image, but you can extrapolate this to a compute instance on VMWare, AWS, OCI, or Azure.
• For more details in depth please review the 11g/19c/26ai documentation and install guides.

OEL 8.7 Server Setup and configuration

As part of the upgrade process, and to perform this demo, we will set up a new Oracle Enterprise Linux (OEL) 8.7 server using Oracle’s Virtual Box.

Download Installation Media

Download the OEL 8.7 ISO image from Oracle’s software delivery cloud.

You can also download the 26ai, 19c, and 11.2.0.4 database install files from this location.

Setup Virtual Box Instance

• Create a new Server, name it something (ol8 for me), set the Type is: Linux, Version: Oracle Linux 64 bit.
• Select the ISO you just downloaded to boot with.
• 10 GB Memory, 4 vCPU
• 300 GB Virtual hard disk, not pre-allocated
• I use a bridged network connection so I can connect to it with putty.

Install OEL 8.9

• Begin Installation: Select “Install Oracle Linux 8.7” and proceed with the guided setup.
• Partition Disks: To keep it simple, I just save 10 GB of disk for swap and put the rest under /
• Select Software Packages: Choose the Server only install option, or “Server with GUI” if you prefer.
• Configure Network: Enable the virtual network card.

Post-Installation Setup

Update the System: Once the installation is complete, update the system using “yum update”

Install Required Dependencies (this is a shortcut for many of the requirements for all 3 versions):

yum update
yum -y install oracle-database-preinstall-19c.x86_64 perl.x86_64

mkdir -p /u01 /oracle
chown oracle:oinstall /u0* /oracle

11.2.0.4 Installation

11g will require these additional packages:

yum install -y gcc.x86_64 glibc glibc-devel

curl -o libaio-0.3.109-13.el7.i686.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/libaio-0.3.109-13.el7.i686.rpm
curl -o libaio-0.3.109-13.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/libaio-0.3.109-13.el7.x86_64.rpm
curl -o libaio-devel-0.3.109-13.el7.i686.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/libaio-devel-0.3.109-13.el7.i686.rpm
curl -o libaio-devel-0.3.109-13.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/libaio-devel-0.3.109-13.el7.x86_64.rpm

yum -y localinstall libaio-0.3.109-13.el7.i686.rpm
yum -y localinstall libaio-0.3.109-13.el7.x86_64.rpm
yum -y localinstall libaio-devel-0.3.109-13.el7.x86_64.rpm
yum -y localinstall libaio-devel-0.3.109-13.el7.i686.rpm

Now we install 11g. As the Oracle id, download installation software to /u01/inst, and unzip there.

cd /u01/inst/database
mkdir -p /u01/app/oracle/product/11

./runInstaller -silent -ignoreSysPrereqs -ignorePrereq -waitforcompletion -showProgress \
FROM_LOCATION=/u01/inst/database/stage/products.xml \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
ORACLE_HOME=/u01/app/oracle/product/11 \
ORACLE_HOME_NAME="OraDb11g_Home1" \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=oinstall \
oracle.install.db.OPER_GROUP=oinstall \
DECLINE_SECURITY_UPDATES=true

Run the required root scripts mentioned at the end of the install.

Restore the Database Directly to 11.2.0.4

Add an entry to your /etc/oratab for your database name.

echo "bob:/u01/app/oracle/product/11:N" >> /etc/oratab
. oraenv

Copy your backup files to a suitable location (I used /u02/backup).

Make some required directories (you may need to restore spfile first, to adjust locations or create the right places.)

mkdir -p /oracle/admin/bob/adump/bob /oracle/oradata /oracle/oradata/BOB/controlfile /oracle/fra

Restore from your RMAN backup.

rman target /

startup nomount;

restore spfile from '/u02/backup/bob_COLD_102vit1e_1_1_20240710_1628.bus';

shutdown immediate;

startup nomount;

restore controlfile from '/u02/backup/bob_CTRL_112vit27_1_1_20240710_1628.bus';
alter database mount;

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;

restore database;
recover database;  --- Here is where you could potentially do multiple rounds of recovery, if you're restoring from a large database backup.

Upgrade Database to 11.2.0.4

Now we open in upgrade mode and upgrade the system to 11.2.0.4.

sqlplus / as sysdba
alter database open resetlogs upgrade;

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba
spool /tmp/upgrade.log
set echo on
@catupgrd.sql

Then start the instance for the next step.

startup mount

ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                 2255832 bytes
Variable Size             335545384 bytes
Database Buffers          281018368 bytes
Redo Buffers                7507968 bytes

Database mounted.

alter database archivelog;

Database opened.

Install 19c

Create a path for 19c, unzip the database, and install.

mkdir /u01/app/oracle/product/19
cd /u01/inst
unzip -q 19c_db_V982063-01.zip -d /u01/app/oracle/product/19

export ORACLE_HOME=/u01/app/oracle/product/19
export INST_DIR=/u01/inst
export ORACLE_BASE=/u01/app/oracle
export ORACLE_GROUP=oinstall
export EDITION=EE
export CV_ASSUME_DISTID=OEL8.5

cd $ORACLE_HOME
./runInstaller -waitforcompletion -silent \
  oracle.install.option=INSTALL_DB_SWONLY \
  ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
  UNIX_GROUP_NAME=${ORACLE_GROUP} \
  SELECTED_LANGUAGES=en \
  ORACLE_HOME=${ORACLE_HOME} \
  ORACLE_BASE=${ORACLE_BASE} \
  oracle.install.db.InstallEdition=${EDITION} \
  oracle.install.db.OSDBA_GROUP=${ORACLE_GROUP} \
  oracle.install.db.OSBACKUPDBA_GROUP=${ORACLE_GROUP} \
  oracle.install.db.OSDGDBA_GROUP=${ORACLE_GROUP} \
  oracle.install.db.OSKMDBA_GROUP=${ORACLE_GROUP} \
  oracle.install.db.OSRACDBA_GROUP=${ORACLE_GROUP} \
  SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
  DECLINE_SECURITY_UPDATES=true

bob:> sudo /u01/app/oracle/product/19/root.sh

Check /u01/app/oracle/product/19/install/root_ol8_2024-07-11_16-20-02-692938618.log for the output of the root script.

Patch Oracle 19c Database to 19.23

Download the required patches and patch the database. This also gives you a new version of the autoupgrade.jar file.

You can use wget to download the patch files.

cd /u01/inst

wget --http-user="[email protected]" --http-password="password" --output-document="19.24-db-p36582781_190000_Linux-x86-64.zip" https://updates.oracle.com/Orion/Services/download/p36582781_190000_Linux-x86-64.zip?aru=25751445&patch_file=p36582781_190000_Linux-x86-64.zip

wget --http-user="[email protected]" --http-password="password" --output-document="p6880880_190000_Linux-x86-64.zip" "https://updates.oracle.com/Orion/Download/process_form/p6880880_190000_Linux-x86-64.zip?file_id=112014090&aru=25200703&[email protected]&[email protected]&patch_password=&patch_file=p6880880_190000_Linux-x86-64.zip"

Install the patches. Set the 19c environment for the patch and unzip the patches.

echo 19:/u01/app/oracle/product/19:N >> /etc/oratab
. oraenv   # enter 19

unzip -q 19.23-db-p36233263_190000_Linux-x86-64.zip
unzip -q 19.23-jvm-p36199232_190000_Linux-x86-64.zip
unzip -oq p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME

$ORACLE_HOME/OPatch/opatch lspatches

cd 36233263
$ORACLE_HOME/OPatch/opatch apply
cd ../36199232

$ORACLE_HOME/OPatch/opatch lspatches

Upgrade 11.2.0.4 to 19c

We use the autoupgrade utility to upgrade to 19c.

Make some directories to hold the upgrade files:

mkdir -p ~/upgrade ~/upgrade/global ~/upgrade/log
cd ~/upgrade

Create a sample config file and update it with these values.

export NEW_ORACLE_HOME=/u01/app/oracle/product/19
$NEW_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config noncdbtopdb

mv sample_config.cfg config.cfg

I suggest using the following parameters:

global.autoupg_log_dir=/home/oracle/upgrade/global
upg1.log_dir=/home/oracle/upgrade/log
upg1.sid=bob
upg1.source_home=/u01/app/oracle/product/11
upg1.target_home=/u01/app/oracle/product/19
upg1.run_utlrp=yes
upg1.timezone_upg=yes

Run the autoupgrade using analyze mode to see if any manual changes are needed.

$NEW_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.cfg -mode ANALYZE

# Check log to confirm if it passed checks.
cat /home/oracle/upgrade/global/cfgtoollogs/upgrade/auto/status/status.log

# Deploy to upgrade.
$NEW_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.cfg -mode DEPLOY

# Monitor
lsj
tasks
status -a 7

Install Oracle AI Database 26ai

Now we install 26ai. This is currently the Free version available here. Or we can use wget again as root.

wget --no-check-certificate -c --header "Cookie: oraclelicense=accept-securebackup-cookie" https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23ai-1.0-1.el8.x86_64.rpm

Run the pre-install and the rpm install as root.

dnf install -y oracle-ai-database-preinstall-26ai
dnf -y localinstall oracle-ai-database-free-26ai-23.26.0-1.el8.x86_64.rpm

To keep things simple, we setup the 26ai Free database using default parameters. This will automatically create a CDB we can use for the 19c->26ai upgrade.

export DB_PASSWORD=DBA_Password8
(echo "${DB_PASSWORD}"; echo "${DB_PASSWORD}";) | /etc/init.d/oracle-free-26ai configure

Upgrade 19c to 26ai pdb

Drop a restore point created in the prior upgrade. Set compatibility to 19c and restart the instance.

drop restore point AUTOUPGRADE_9212_BOB112040;
alter system set compatible='19.0.0' scope=spfile;

shutdown immediate
startup

Re-run the prior steps for autoupgrade, this time using non cdb to pdb option.

export NEW_ORACLE_HOME=/opt/oracle/product/26ai/dbhomeFree

cd ~/upgrade
rm -rf global log

$NEW_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config noncdbtopdb

global.autoupg_log_dir=/home/oracle/upgrade/global
upg1.log_dir=/home/oracle/upgrade/log
upg1.sid=bob
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/opt/oracle/product/26ai/dbhomeFree
upg1.run_utlrp=yes
upg1.timezone_upg=yes
upg1.target_pdb_name=bob
upg1.target_cdb=FREE

Use the same analyze and deploy steps as before.

$NEW_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.cfg -mode ANALYZE
$NEW_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.cfg -mode DEPLOY

Note: You can not quite get to open mode with the current release of the free version of the on-premise Oracle AI Database 26ai. You will see this error during the final open process:

Upgrading to 26ai and setting up a new OEL 8.9 server is a strategic move that can significantly enhance your system’s performance, security, and functionality. By following the outlined steps and taking advantage of the new features, you can ensure a smooth transition and position your organization to leverage the full potential of this powerful software.

Explore more Oracle insights at Datavail.com.