Oracle Database 19c to 23ai Migration: Complete DBA Guide with DBUA an

Production-tested migration guide from Oracle 19c to 23ai covering pre-migration checks, DBUA upgrade, Data Pump export/import, and post-migration validation.

Oracle Database 19c to 23ai Migration: Complete DBA Guide with DBUA and Data Pump [Framework]

As a senior technology writer at TechNews Venture, I’ve witnessed countless database migrations, each with its unique challenges and triumphs. The journey from Oracle Database 19c, a Long Term Support (LTS) release, to the revolutionary Oracle Database 23ai, often dubbed the "AI Database," represents a significant leap forward. This isn't just an upgrade; it's an embrace of a future where data and artificial intelligence converge at the core of your enterprise applications. For DBAs, understanding this transition with precision and foresight is paramount.

Oracle Database 23ai introduces groundbreaking features like AI Vector Search for RAG (Retrieval Augmented Generation) workloads, JSON Relational Duality Views for flexible data modeling, and JavaScript stored procedures, alongside myriad developer and performance enhancements. While 19c has served as a stable bedrock for many organizations, particularly those running mission-critical applications like Oracle Peoplesoft, the strategic advantages offered by 23ai are compelling enough to warrant a meticulous migration plan. This guide provides a comprehensive framework for DBAs, detailing two primary migration methods: the Database Upgrade Assistant (DBUA) and the Data Pump utility, complete with real-world commands and best practices.

Overview of Oracle Database 23ai and Migration Paths

Oracle Database 23ai (formerly 23c) fundamentally redefines what a database can do by embedding AI capabilities directly within the data layer. Key innovations include:

AI Vector Search: Enables semantic search and RAG workloads by storing and querying vector embeddings within the database, integrated with existing transactional data.
JSON Relational Duality Views: Allows developers to access the same data as JSON documents, relational tables, or objects, offering unprecedented flexibility without data duplication or ETL.
JavaScript Stored Procedures: Execute JavaScript code directly in the database, simplifying application logic and reducing network round trips.
Schema-less Tables: Provides greater flexibility for evolving data models.
Operational Property Graphs: Built-in graph capabilities for complex relationship analysis.

Migrating from 19c to 23ai involves careful planning and execution. The choice between DBUA and Data Pump often depends on factors such as database size, downtime tolerance, complexity of the environment, and whether a new database architecture (e.g., Multitenant) is being adopted. DBUA is generally preferred for in-place or out-of-place upgrades of existing databases, preserving the database ID and structure. Data Pump offers more flexibility for cross-platform migrations, major architectural changes, or when creating a completely fresh database instance.

Prerequisites for a Successful Migration

Before initiating any migration, a thorough pre-check phase is crucial. Skipping these steps can lead to unforeseen complications and extended downtime.

1. Hardware and Operating System Compatibility

Ensure your target server meets the minimum hardware requirements for Oracle Database 23ai. This includes CPU, RAM, and disk space. For Linux, verify the OS version and necessary packages.

Supported OS: Oracle Linux 8+, Red Hat Enterprise Linux 8+, SUSE Linux Enterprise Server 15+, etc.
Memory: Minimum 2 GB RAM (4 GB recommended for production).
Swap Space: 1x RAM if RAM between 2-16 GB, 16 GB if RAM > 16 GB.
Disk Space: At least 10 GB for software, plus sufficient space for database files.


# Example: Check OS version on Linux
cat /etc/os-release

# Example: Check kernel parameters for Oracle
sysctl -a | grep shmmax
sysctl -a | grep shmall
sysctl -a | grep file-max

2. Software Requirements

Java Development Kit (JDK): Oracle Database 23ai requires a certified JDK. The installer typically bundles a compatible JDK, but verify.
Perl: Required for various Oracle utilities.
OS Packages: Ensure all necessary OS packages (e.g., `binutils`, `gcc`, `libaio`, `make`, `glibc`) are installed. Use `yum` or `dnf` for installation.


# Example: Install required packages on Oracle Linux 8
sudo dnf install -y oracle-database-preinstall-23c

3. Source Database Health Check (Oracle 19c)

Clean up and validate your 19c database. This minimizes issues during the upgrade.

Run Pre-Upgrade Information Tool: Located in the 23ai software directory (`$ORACLE_HOME/rdbms/admin/preupgrade.jar` or `preupgrade.sql`). This tool identifies potential issues and provides fixup scripts.
Check for Invalid Objects: Recompile any invalid objects.
Purge Recycle Bin: Empty the recycle bin to free up space and prevent unnecessary object migrations.
Check for Corrupt Blocks: Run RMAN `VALIDATE DATABASE`.
Check for Dictionary Issues: Run `utlprp.sql` (if needed) and `dbms_stats.gather_dictionary_stats`.


-- Connect to 19c database as SYSDBA
sqlplus / as sysdba

-- Check for invalid objects
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status != 'VALID';

-- Recompile invalid objects (if any)
@?/rdbms/admin/utlrp.sql

-- Purge recycle bin
PURGE DBA_RECYCLEBIN;

-- Gather dictionary statistics
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

-- Run pre-upgrade script (from 23ai ORACLE_HOME)
-- First, copy preupgrade.jar/preupgrade.sql from 23ai_HOME/rdbms/admin to 19c_HOME/rdbms/admin
-- Then execute from 19c environment:
@?/rdbms/admin/preupgrade.sql
-- Review the generated output files (e.g., preupgrade_info.txt, preupgrade_fixups.sql)

4. Backup Strategy

A full, restorable backup of your 19c database is non-negotiable. This is your safety net.


-- Connect to RMAN target database
rman target /

-- Take a full backup
BACKUP DATABASE PLUS ARCHIVELOG;

-- Verify backup
RESTORE DATABASE VALIDATE;

5. User Privileges

Ensure the Oracle software owner (e.g., `oracle`) has appropriate permissions on the new Oracle Home directories and sufficient OS privileges.

6. Storage Considerations

Plan for adequate storage for the 23ai software, database files, and any temporary files generated during migration. Consider using ASM for better manageability and performance.

7. Network Connectivity

Verify network connectivity between the application servers (e.g., Peoplesoft application servers) and the new 23ai database server, including firewall rules.

Detailed Migration Steps

Method 1: Database Upgrade Assistant (DBUA)

DBUA is Oracle's recommended tool for upgrading existing databases. It provides a graphical interface and automates many complex tasks.

Step 1: Install Oracle Database 23ai Software

Download the 23ai software from Oracle Support. Install it into a new Oracle Home directory, distinct from your 19c Oracle Home. Do NOT create a database instance during this installation.


# Create Oracle base and product directories
sudo mkdir -p /u01/app/oracle/product/23.0.0/dbhome_1
sudo chown -R oracle:oinstall /u01/app/oracle
sudo chmod -R 775 /u01/app/oracle

# Unzip the software
unzip LINUX.X64_23ai_database.zip -d /u01/app/oracle/product/23.0.0/dbhome_1

# Set environment variables for the new ORACLE_HOME
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Run the installer (as oracle user)
cd /u01/app/oracle/product/23.0.0/dbhome_1
./runInstaller

Follow the graphical prompts:

Select "Set Up Software Only".
Choose "Single Instance Database Installation".
Select "Enterprise Edition".
Specify Oracle Base and Oracle Home.
Proceed with installation.

Step 2: Prepare the 19c Database for Upgrade

Ensure all prerequisites from the previous section are met, especially running the `preupgrade.sql` script from the 23ai Oracle Home and addressing its recommendations.


# Set environment variables for the 19c database
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=YOUR_19C_SID
export PATH=$ORACLE_HOME/bin:$PATH

# Copy preupgrade.sql from 23ai_HOME to 19c_HOME
cp /u01/app/oracle/product/23.0.0/dbhome_1/rdbms/admin/preupgrade.sql \
   /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/

# Execute preupgrade.sql in 19c database
sqlplus / as sysdba
@?/rdbms/admin/preupgrade.sql
EXIT;

# Review the generated reports (e.g., preupgrade_info.txt, preupgrade_fixups.sql)
# Apply any necessary fixes before proceeding.

Shut down the 19c database cleanly.


sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
EXIT;

Step 3: Launch DBUA

Switch to the 23ai Oracle Home environment and launch DBUA.


# Set environment variables for the 23ai Oracle Home
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Launch DBUA
dbua

Follow the DBUA wizard:

Welcome: Click "Next".
Operations: Select "Upgrade Oracle Database". Click "Next".
Select Database: DBUA will discover databases associated with the old Oracle Home. Select your 19c database. Click "Next".
Upgrade Options:
- Oracle Home: The new 23ai Oracle Home should be pre-selected.
- Fast Recovery Area: Configure if not already.
- Listener: Register with an existing or new listener.
- Multitenant: If converting to Multitenant, select this option. This is highly recommended for 23ai.
Click "Next".
Configuration Options:
- Enable Archiving: Recommended.
- Database Vault, Label Security: Enable if required.
- EM Express: Configure port.
Click "Next".
Management Options: Register with Oracle Enterprise Manager if used. Click "Next".
Network Configuration: Configure listener details. Click "Next".
Backup and Recovery:
- Perform a backup: DBUA can initiate an RMAN backup. While useful, it's best to have a separate, verified backup beforehand.
- Configure ASM: If using ASM, configure here.
Click "Next".
Review: Review the summary of choices. Click "Finish" to start the upgrade.

DBUA will perform pre-checks, copy files, upgrade the database, and run post-upgrade scripts. This process can take a significant amount of time depending on database size and system resources.

Step 4: Post-Upgrade Steps

After DBUA completes, perform these essential steps:


# Set environment variables for the new 23ai database
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
export ORACLE_SID=YOUR_23AI_SID
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba

-- Recompile invalid objects (critical for applications like Peoplesoft)
@?/rdbms/admin/utlrp.sql

-- Gather optimizer statistics
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(user => 'SYS', options => 'GATHER AUTO', cascade => TRUE, degree => DBMS_STATS.AUTO_DEGREE, no_invalidate => FALSE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(user => 'SYSTEM', options => 'GATHER AUTO', cascade => TRUE, degree => DBMS_STATS.AUTO_DEGREE, no_invalidate => FALSE);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS => 'GATHER AUTO', GATHER_SYS => TRUE);

-- Check upgrade status
SELECT COMP_ID, COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

-- Check for any remaining invalid objects
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status != 'VALID';

-- Update TNSNAMES.ORA and LISTENER.ORA for new ORACLE_HOME
-- Ensure application connections (e.g., Peoplesoft) point to the new 23ai instance.

Method 2: Data Pump (Export/Import)

Data Pump is ideal for creating a fresh 23ai database, migrating across platforms (e.g., Linux to OCI), or when a complete rebuild is desired. This method generally involves more downtime than DBUA for large databases but offers greater control.

Step 1: Prepare Source Database (19c)

Cleanup: As with DBUA, ensure the 19c database is clean (invalid objects, recycle bin).
Create Directory Object: This is where the export dump file will be stored.


-- Connect to 19c database
sqlplus / as sysdba

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/datapump';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO system; -- Or a dedicated data pump user

Perform Full Database Export: Export the entire database using `expdp`.


# Set 19c environment
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=YOUR_19C_SID
export PATH=$ORACLE_HOME/bin:$PATH

expdp system/password@YOUR_19C_TNS \
      DUMPFILE=yourdb_full_19c.dmp \
      LOGFILE=yourdb_full_19c.log \
      DIRECTORY=DATA_PUMP_DIR \
      FULL=Y \
      COMPRESSION=ALL \
      PARALLEL=4 -- Adjust based on CPU cores

Transfer the `yourdb_full_19c.dmp` file to the target 23ai server.

Step 2: Install Oracle Database 23ai Software and Create New Database

Install the 23ai software into a new Oracle Home as described in DBUA Step 1. Then, create a brand-new 23ai database instance using DBCA (Database Configuration Assistant). This new database will be the target for the import.


# Set 23ai environment
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Launch DBCA
dbca

Follow the DBCA wizard:

Database Operation: Select "Create a database".
Creation Mode: Select "Typical configuration" for simplicity or "Advanced configuration" for more control (e.g., Multitenant, custom storage).
Database Details:
- Database Name (Global Database Name): e.g., `yourdb.example.com`
- SID: e.g., `YOUR_23AI_SID`
- Container Database: Highly recommended to enable as a Container Database (CDB) and create a Pluggable Database (PDB).
Storage: Specify file locations or use ASM.
Initialization Parameters: Adjust SGA, PGA, character set (must match source if not converting).
Review: Review the summary and click "Finish" to create the database.

Step 3: Prepare Target Database (23ai) for Import

Create Directory Object: Point to where you copied the dump file.


-- Connect to new 23ai database (e.g., PDB if using Multitenant)
sqlplus system/password@YOUR_23AI_PDB_TNS

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/datapump'; -- Make sure this path exists and dump file is there
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO system;

Pre-create Users/Tablespaces (Optional but Recommended): If you're importing into a new database, especially if you plan to remap users or tablespaces, pre-creating them can simplify the import. For a full import where schemas should be identical, this might not be strictly necessary but can help avoid issues.

Step 4: Perform Full Database Import


# Set 23ai environment
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
export ORACLE_SID=YOUR_23AI_SID
export PATH=$ORACLE_HOME/bin:$PATH

impdp system/password@YOUR_23AI_PDB_TNS \
      DUMPFILE=yourdb_full_19c.dmp \
      LOGFILE=yourdb_full_23ai.log \
      DIRECTORY=DATA_PUMP_DIR \
      FULL=Y \
      PARALLEL=4 -- Adjust based on CPU cores

If you need to remap tablespaces or schemas (e.g., moving from non-CDB to CDB with different PDB default tablespaces), use the `REMAP_TABLESPACE` or `REMAP_SCHEMA` options.


-- Example with remapping
impdp system/password@YOUR_23AI_PDB_TNS \
      DUMPFILE=yourdb_full_19c.dmp \
      LOGFILE=yourdb_full_23ai.log \
      DIRECTORY=DATA_PUMP_DIR \
      FULL=Y \
      REMAP_TABLESPACE=USERS:NEW_USERS_TS \
      REMAP_SCHEMA=OLD_SCHEMA:NEW_SCHEMA \
      PARALLEL=4

Step 5: Post-Import Steps

Similar to DBUA, perform these steps:


-- Connect to 23ai database
sqlplus system/password@YOUR_23AI_PDB_TNS

-- Recompile invalid objects
@?/rdbms/admin/utlrp.sql

-- Gather optimizer statistics
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(user => 'SYS', options => 'GATHER AUTO', cascade => TRUE, degree => DBMS_STATS.AUTO_DEGREE, no_invalidate => FALSE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(user => 'SYSTEM', options => 'GATHER AUTO', cascade => TRUE, degree => DBMS_STATS.AUTO_DEGREE, no_invalidate => FALSE);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS => 'GATHER AUTO', GATHER_SYS => TRUE);

-- Update TNSNAMES.ORA and LISTENER.ORA

Cloud Migration with Data Pump (OCI Example)

For migrating to Oracle Cloud Infrastructure (OCI), Data Pump is the primary method for moving large datasets into services like Exadata Database Service, VM DB Systems, or even Autonomous Database (via Data Pump to Object Storage and then direct load). Here’s a high-level overview:


# Example: Provision a new VM DB System in OCI using CLI
# Ensure OCI CLI is configured with appropriate tenancy and user credentials.

oci db system create \
    --availability-domain Uocm:PHX-AD-1 \
    --compartment-id ocid1.compartment.oc1..aaaaaa... \
    --cpu-core-count 4 \
    --db-system-options '{"databaseEdition": "ENTERPRISE_EDITION", "licenseModel": "BRING_YOUR_OWN_LICENSE"}' \
    --display-name "23ai-Migration-DB" \
    --hostname "23aimigdb" \
    --shape "VM.Standard.E4.Flex" \
    --subnet-id ocid1.subnet.oc1..aaaaaa... \
    --ssh-public-keys-file ~/.ssh/id_rsa.pub \
    --database-edition "ENTERPRISE_EDITION_EXTREME_PERFORMANCE" \
    --initial-data-storage-size-in-gbs 500 \
    --version "23ai" \
    --db-home-id ocid1.dbhome.oc1..aaaaaa... # Pre-created 23ai DB Home

# Once the DB System is provisioned, connect via SSH.
# Create a new 23ai database within the DB System using `dbca`.
# Transfer the Data Pump dump file to an OCI Object Storage bucket.
# Create a credential and directory in the 23ai database for Object Storage access.

-- In 23ai PDB on OCI
CREATE CREDENTIAL OCI_CREDENTIALS
  USERNAME 'oracleidentitycloudservice/example.user@example.com' -- OCI User or Auth Token
  PASSWORD 'your_auth_token_or_password';

CREATE DIRECTORY OCI_DP_DIR AS 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/your_namespace/b/your_bucket';

-- Perform Data Pump import from Object Storage
impdp system/password@YOUR_23AI_PDB_TNS \
      DUMPFILE=yourdb_full_19c.dmp \
      LOGFILE=yourdb_full_23ai_oci.log \
      DIRECTORY=OCI_DP_DIR \
      FULL=Y

Security Considerations

Upgrading to 23ai provides an opportunity to enhance your database security posture significantly.

Unified Auditing: 23ai leverages Unified Auditing by default. Ensure your audit policies are configured to capture relevant security events. Review `AUDIT_UNIFIED_POLICY` views.
Least Privilege: Review and revoke unnecessary privileges, especially `SYSDBA` and `DBA` roles. Utilize Oracle's Privilege Analysis feature (available with Database Vault or as a separate option) to identify excessive privileges.
Network Encryption (TLS/SSL): Enforce TLS 1.2 or higher for all database connections. Configure `sqlnet.ora` to require encryption.
Patching Strategy: Establish a robust patching schedule for 23ai, applying Release Updates (RUs) and Release Update Revisions (RURs) promptly to address known vulnerabilities. Monitor Oracle's Critical Patch Updates (CPUs) for security advisories. For instance, addressing vulnerabilities like those related to Log4j (CVE-2021-44228) in application layers that interact with the database is crucial, though 23ai itself is not directly affected, its ecosystem might be.
New Security Features in 23ai: Explore features like stronger default password verifiers, SQL Firewall for preventing SQL injection, and enhanced privilege management to further secure your data.
Database Vault: Consider implementing Oracle Database Vault to enforce separation of duties and prevent privileged users from accessing sensitive data.


-- Example: Enable unified auditing for all successful and failed logon attempts
AUDIT POLICY ORA_LOGON_FAILURES;
AUDIT POLICY ORA_ACCOUNT_MGMT;

-- Example: Check current unified audit policies
SELECT policy_name, enabled_option, user_name, entity_name FROM audit_unified_enabled_policies;

-- Configure sqlnet.ora for encryption (server side)
# sqlnet.ora
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

Best Practices

Test Environment First: ALWAYS perform the migration on a non-production environment that mirrors your production setup as closely as possible. This includes data volume and application stack.
Performance Baseline: Capture performance metrics (AWR reports, SQL execution times) from your 19c database before and after the 23ai migration. Compare to ensure performance parity or improvement.
Application Certification: Verify that your applications (especially critical ones like Oracle Peoplesoft) are certified and supported on Oracle Database 23ai. Consult the Oracle Support Certifications matrix (My Oracle Support).
Automate Where Possible: Use scripts for pre-checks, post-upgrade tasks, and even the Data Pump operations to reduce human error and ensure consistency.
Document Everything: Keep a detailed log of all steps, commands, and encountered issues with their resolutions. This is invaluable for future migrations or troubleshooting.
Rollback Plan: Have a clear, tested rollback plan. This typically involves restoring from the pre-migration backup.
Multitenant Architecture: Seriously consider converting to a Multitenant (CDB/PDB) architecture during the migration. 23ai fully embraces this model, offering significant benefits for consolidation, patching, and resource management.
Engage Oracle Support: For complex migrations or mission-critical systems, consider engaging Oracle Support or consulting services.

FAQ

Q1: Why upgrade to 23ai from 19c, given 19c is an LTS release?

While Oracle Database 19c offers extended support, 23ai introduces foundational shifts that are crucial for modern data strategies. The embedded AI Vector Search capabilities allow enterprises to build RAG-based AI applications directly on their operational data without complex ETL processes. JSON Relational Duality Views simplify development for diverse data models, accelerating time-to-market. For organizations looking to leverage the

Oracle Database 19c to 23ai Migration: Complete DBA Guide with DBUA and Data Pump [Framework]