Upgrading Oracle Database XE to Oracle AI Database FREE

While there is no XE release in Oracle AI Database, you can upgrade Oracle Database XE to Oracle AI Database using an unplug-plug upgrade.

Oracle Database Express Edition (XE) has been replaced by Oracle AI Free. There is no direct upgrade option from Oracle Database XE to Oracle AI Database Standard Edition (SE) or Enterprise Edition (EE). You must use Oracle Data Pump to export data files to Oracle AI Database. For more details, refer to the installation guide for your platform.

You can perform an unplug-plug upgrade from Oracle Database XE to Oracle AI Database Free.

Parent topic: Using Other Methods for Oracle Database Upgrades

Upgrading Oracle Database XE 21c PDB to Oracle AI Database Free 26ai

You use an unplug-plug upgrade to upgrade Oracle Database XE to Oracle AI Database Free.

Upgrades from Oracle Database XE to Oracle AI Database have the following restrictions:

  • Database Upgrade Assistant (DBUA) cannot be used to perform the upgrade.
  • Database Configuration Assistant (DBCA) cannot be used to perform a plug-in upgrade of Oracle Database XE to Oracle AI Database.
  • Unplug/plug upgrades from Enterprise Edition or Standard Edition into Oracle AI Database 26ai Free is not supported.
  • Unplug/plug upgrades from Oracle Database XE or Oracle AI Database Free into Oracle AI Database Enterprise Edition or Standard Edition is not supported.

The upgrade consists of the following high-level steps:

  1. Unplug the PDB from Oracle Database XE 21c.
  2. Install the latest release of Oracle AI Database 26ai, and plug in Oracle Database XE 21c PDB to the Oracle AI Database CDB.
  3. Confirm that there are no invalid objects remaining in the upgraded PDB.

Unplug the PDB from Oracle Database XE 21c

  1. From Oracle Database XE 21c, connect to the container database as SYSDBA.

    SQL> CONNECT / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

    The session is altered.

  2. Close the PDB that you want to upgrade.

    For example, use the following command to close the PDB named XEPDB1. 

    SQL> ALTER PLUGGABLE DATABASE XEPDB1 CLOSE;

    The pluggable database is closed.

  3. Unplug the Oracle Database XE 21c PDB by running the following SQL command: 

    SQL> ALTER PLUGGABLE DATABASE XEPDB1 UNPLUG INTO '/home/oracle/XEPDB1.xml';

    In this example, the PDB name is XEPDB1, and the location of the PDB XML file is /home/oracle/XEPDB1.xml.

    The following response appears when the command is completed: Pluggable database altered.

  4. Drop the closed PDB, and keep the data files.

    SQL> DROP PLUGGABLE DATABASE XEPDB1 KEEP DATAFILES;
  5. Deinstall Oracle Database XE 21c by running the following command: /etc/init.d/oracle-xe-21c delete.

Plugging in Oracle Database XE 21c PDB to Oracle AI Database 26ai CDB

  1. Install the latest version of Oracle AI Database Free 26ai.

    For more information, see:

    Installing Oracle Database Free

  2. Connect to the Oracle AI Database CDB as SYSDBA. Unplug the PDB from the XE 21c CDB, and then plug it into the Oracle AI Database CDB by running the following command, where XEPDB2 is the PDB that you are upgrading: 

    SQL> CONNECT / AS SYSDBA
SQL> CREATE PLUGGABLE DATABASE XEPDB2 USING '/home/oracle/XEPDB1.xml' NOCOPY TEMPFILE REUSE;

    A new pluggable database, XEPDB2 is created.

  3. Open the PDB in UPGRADE mode. 

    SQL> ALTER PLUGGABLE DATABASE XEPDB2 OPEN UPGRADE;

  4. Upgrade the PDB using the Parallel Upgrade Utility command. 

    $ORACLE_HOME/perl/bin/perl  $ORACLE_HOME/rdbms/admin/catctl.pl -u sys -d $ORACLE_HOME/rdbms/admin -c XEPDB2 -l $ORACLE_BASE -t catupgrd.sql

    You can view the upgrade summary report at /opt/oracle/upg_summary.log. The PDB is in the MOUNTED state after the upgrade.

  5. Open the PDB to run post-upgrade fixups.

    SQL> ALTER PLUGGABLE DATABASE XEPDB2 OPEN;

  6. Use the catcon.pl utility to run utlrp.sql from the $ORACLE_HOME/rdbms/admin directory. 

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -n 1 -l /home/oracle/utlrp -e -b utlrp -c 'XEPDB2' utlrp.sql

    The script recompiles INVALID objects in the database.

Confirm that there are no invalid objects remaining in the upgraded PDB

Confirm that there are no invalid objects in the database. For example: 

SQL> alter session set container=XEPDB2;
Session altered.

SQL> show con_name;
CON_NAME
------------------------------
XEPDB2
SQL> select count(*) , ' Invalid Objects' from all_objects where status = 'INVALID';
  COUNT(*) 'INVALIDOBJECTS'
---------- ----------------
         0  Invalid Objects

Note:

To upgrade APEX, you also have to complete an APEX installation, as described in Oracle APEX Installation Guide.

Related Topics

Parent topic: Upgrading Oracle Database XE to Oracle AI Database FREE

Exporting and Importing Data between Different Versions of Oracle AI Database 26ai Free

Learn how to export and import data between different versions of Oracle AI Database 26ai Free.

Note:

Oracle Database 23.x Free can refer to 23.2 or later releases.

Exporting Data

To export data from Oracle Database 23.x Free:

  1. As the root user, create a /opt/dump directory on the local file system for the DUMP_DIR directory object. 

    mkdir /opt/dump
chown -R oracle:oinstall /opt/dump
chmod -R 760 /opt/dump

  2. Perform the following steps for each pluggable database (PDB). The steps in this section are for the PDB freepdb1.

    1. Set the ORACLE_HOME and ORACLE_SID environment variables.

      export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree

    2. Connect to the 23.x Free database as user SYS using the SYSDBA privilege as the oracle user, and switch the container to freepdb1.

    3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user. 

      /opt/oracle/product/23ai/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    4. Export data from your 23.x Free PDB freepdb1 to the dump folder. For example:

      /opt/oracle/product/23ai/dbhomeFree/bin/expdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb23ai_freepdb1.dmp logfile=expdb23ai_freepdb1.log

      Note:

      • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
      • The default PDB name created is freepdb1 in 26ai.

  3. Deinstall Oracle Database 23.x Free if you plan to install 26ai Free on the same system.

  4. Install Oracle AI Database 26ai Free.

Importing Data

To import data to your Oracle AI Database 26ai Free, perform the following steps for each PDB. The steps in this section are for importing data from 23.x Free PDB freepdb1 and overwriting to the same PDB freepdb1 in 26ai Free.

  1. Set the ORACLE_HOME and ORACLE_SID environment variables.

    export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/26ai/dbhomeFree

  2. Connect to the 26ai Free database as user SYS using the SYSDBA privilege as the oracle user and switch the container to freepdb1.

  3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user. 

    /opt/oracle/product/26ai/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

  4. Import data to the 26ai Free PDB freepdb1 from the dump folder created during the export operation. For example:

    /opt/oracle/product/26ai/dbhomeFree/bin/impdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb23ai_freepdb1.dmp logfile=impdb26ai_freepdb1.log

    Note:

    • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
    • The default PDB name created is freepdb1 in 26ai.

Ignore the following errors:

  • ORA-39342: Data Pump did not import dependent objects for NETWORK_ACL due to the previous error
  • ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
  • ORA-31684: Object type TABLESPACE:"TEMP" already exists
  • ORA-31684: Object type TABLESPACE:"USERS" already exists
  • ORA-31684: Object type USER:"PDBADMIN" already exists
  • ORA-31684: Object type DIRECTORY:"DUMP_DIR" already exists

Parent topic: Upgrading Oracle Database XE to Oracle AI Database FREE