Upgrade to Oracle Database 23ai for the latest capabilities, long-term support & support for next-generation applications

Upgrading to Oracle Database 23ai (previously referred to as Oracle 23c) involves several important steps and considerations, particularly since it's a long-term support (LTS) release with significant new features focused on AI integration, JSON relational duality, enhancements to developer productivity, and support for next-generation applications.

Key Considerations Before Upgrading

Check Compatibility:

Ensure your current version is eligible for a direct upgrade (Oracle supports direct upgrade from 19c and later).

Upgrade Paths for Oracle Database 23ai

Flow:

Use Oracle’s Database Pre-Upgrade tool (autoupgrade.jar) to assess readiness.

Understand New Features

⦁ AI Vector Search: Integrates with vector databases for generative AI apps.

⦁ JSON Relational Duality: Combines JSON flexibility with relational integrity.

⦁ SQL Enhancements: New syntax and functions for developers.

⦁ Automatic Storage and Performance Tuning Enhancements.

Environment Preparation

⦁ Check Version Compatibility & Run Pre-Upgrade Checks

⦁ Verify Database Integrity, Check for corruption and Ensure DB_BLOCK_CHECKING, DB_LOST_WRITE_PROTECT are appropriately set

⦁ Check for Deprecated & Desupported Features

⦁ Backup your entire database.

⦁ Check Tablespaces and Undo Settings, Ensure enough free space in SYSTEM, SYSAUX, UNDO, TEMP, verify undo retention

⦁ Clean Up Database, Remove unused components and Clean up invalid objects

⦁ Gather Statistics

⦁ Test the upgrade in a non-production environment first.

⦁ Review hardware and OS requirements—Oracle 23ai might need updated system libraries or OS versions.

⦁ Schedule Downtime

                Plan for appropriate downtime & Notify all users and application teams

High-Level Upgrade Steps

1. Download and Install Oracle 23ai Software

Download from Oracle Downloads.

Install the software in a new Oracle Home (don’t overwrite the old installation).

https://www.oracle.com/database/free/

https://container-registry.oracle.com/ords/f?p=113:4:13231839633032:::4:P4_REPOSITORY,AI_REPOSITORY,AI_REPOSITORY_NAME,P4_REPOSITORY_NAME,P4_EULA_ID,P4_BUSINESS_AREA_ID:1863,1863,Oracle%20Database%20Free,Oracle%20Database%20Free,1,0&cs=36CoLngVeCfTqAcdt8kdPDM-LpQVv8Uo3CBH8ErXg0v77WTlB-7wybwTkPbbebs7VLvuHqMB8Nog36SbD-jwehA

2. Run Pre-Upgrade Checks

$ java -jar autoupgrade.jar –version 

⦁ java -jar autoupgrade.jar -mode analyze -sourcehome <OLD_ORACLE_HOME> -targethome <NEW_ORACLE_HOME> -sid <DB_SID>

⦁ Fix any issues identified by the report.

3. Perform the Upgrade

You can do this using AutoUpgrade, DBUA (GUI-based), or manual methods. AutoUpgrade is the preferred method.

---> java -jar autoupgrade.jar -mode deploy -config upgrade_config.cfg

Where upgrade_config.cfg includes parameters like:

global.autoupg_log_dir=/u01/app/oracle/upgradelogs

upg1.source_home=/u01/app/oracle/product/19c

upg1.target_home=/u01/app/oracle/product/23ai

upg1.sid=ORCL

upg1.log_dir=/u01/app/oracle/upgradelogs/ORCL

upg1.upgrade_node=localhost

4. Post-Upgrade Steps

Run dbupgdiag.sql and utlrp.sql to recompile invalid objects.

• Validate application functionality.
• Update statistics and test performance.
• Begin using new features gradually after stability is confirmed.

Post-upgrade tasks

After upgrading to Oracle 23ai, it's essential to perform a series of post-upgrade tasks to ensure your system is stable, optimized, and ready to take advantage of the new features. Here's a checklist of recommended tasks:

1. Validate the Upgrade

Check upgrade logs:

--> catupgrd0.log, autoupgrade logs, or DBUA logs (depending on method).

--> Run dbupgdiag.sql (included in Oracle-supplied scripts) to analyze the upgrade status.

Confirm the new version:

--> SELECT * FROM v$version;

2. Gather Statistics

Gather dictionary stats:

--> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Gather fixed object stats:

-->EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Gather system and object stats if not already done during upgrade:

--> EXEC DBMS_STATS.GATHER_DATABASE_STATS;

3. Recompile Invalid Objects

--> Run the recompilation script:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Check for invalid objects:

--> SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID';

4. Test Application Compatibility

⦁ Thoroughly test all applications for performance and compatibility.

⦁ Validate custom PL/SQL, Java code, and any dependencies.

5. Backup the Upgraded Database

Take a full RMAN backup immediately after a successful upgrade:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

6. Enable/Review New 23ai Features

Consider enabling features like:

⦁ AI Vector Search (if applicable to your use case).

⦁ JSON Relational Duality Views.

⦁ SQL Enhancements like new syntax or built-in functions.

7. Update Environment Variables and Scripts

Update ORACLE_HOME, PATH, TNS_ADMIN, etc.

Modify any custom scripts, monitoring tools, or backup jobs to point to the new Oracle version paths.

8. Review and Tune Initialization Parameters

Some deprecated/obsolete parameters may need to be removed.

SELECT name, value, isdefault, isdeprecated FROM v$parameter;

9. Remove Obsolete Components (Optional)

Consider removing unused or deprecated components:

SELECT comp_id, comp_name, version, status FROM dba_registry;

10. Enable New Optimizer Features (Optional, After Testing)

Oracle may retain older optimizer behavior for compatibility. Consider explicitly enabling the latest behavior:

ALTER SYSTEM SET optimizer_features_enable='23.0.0' SCOPE=SPFILE;

Source:

https://docs.oracle.com/en/database/oracle/oracle-database/23/fppad/upgrading-oracle-grid-infrastructure-to-a-later-release.html

AutoUpgrade - My Oracle Support Doc ID 2485457.1

Upgrade Paths

From Version Direct Upgrade to 23ai?

19c                  ----->  Yes

18c  ------->  Use intermediate upgrade to 19c first

OCI - Networking draft

Classless Inter-Domain Routing (CIDR):

Classless Inter-Domain Routing (CIDR) allows network routers to route data packets to the respective device based on the indicated subnet. Instead of classifying the IP address based on classes, routers retrieve the network and host address as specified by the CIDR suffix.

Range of IP Addresses (From IP) to (Last IP)

Virtual Cloud Network (VCN):

Reserved IPs:

N/W Address  (192.168.0.0)

Subnet Default Gateway Address - (192.168.0.1)

Broadcast Address - (192.168.0.255)

Finding threshold value of the sequence

 

select * from 

  (

    select

        seq.sequence_owner

      , seq.sequence_name

      , ( select application_name from 

          ( select ord, application_id, application_name from 

            ( select 1 ord, app.application_id, app.application_name

              from apps.fnd_application_vl app

              where app.product_code = substr(seq.sequence_name, 1, instr(seq.sequence_name,'_')-1)

              union

              select 2 ord, app.application_id, app.application_name

              from apps.fnd_oracle_userid fou, apps.fnd_product_installations fpi, apps.fnd_application_vl app

              where fou.oracle_username = seq.sequence_owner 

                and fpi.oracle_id = fou.oracle_id

                and app.application_id = fpi.application_id

            ) order by ord, application_id

          ) where rownum = 1

        ) application_name

      , seq.min_value

      , seq.max_value

      , seq.cache_size

      , seq.last_number

      , round(((seq.last_number-seq.min_value)/(seq.max_value-seq.min_value))*100) "% Range"

    from dba_sequences seq

    where seq.cycle_flag = 'N'

      and seq.max_value > 0

  ) 

where "% Range" >= 10 /* active threshold % */

  and max_value < 2147483648 /* limited range */

  and cache_size > 1000 /* large cache size */

order by cache_size desc;

OCI: Identity and Access Management - Basics

Create Compartment:

Creating Identity Domain

Default Domain:

Note: All Domain Users cant be deleted

Groups

Note: First create the group and assign it to Users

Users:

Who can create users

    a. Tenancy Admin, b. Domain Admin, c. User Manager Admin Role d. Using Policies (allow group domain1/group1 to manage users in tenancy)

a. Create User using Console:

b. Create User using CSV import:

Dynamic Groups

Integrated Applications

Oracle Cloud Services

Jobs

Reports

Security  -->

               

Settings

Notifications

Branding

Managing Group:

a. Collection of Users

b. Simplify Access Management

c. Audit & Compliance

Managing User

Resource manager views for monitoring performance at the PDB level.

 Resource manager views for monitoring performance at the PDB level.

--> V$RSRC_CONS_GROUP_HISTORY displays a history of consumer group statistics for each entry in V$RSRC_PLAN_HISTORY that has a non-NULL plan.

--> V$RSRC_CONSUMER_GROUP displays data related to currently active resource consumer groups.

--> V$RSRC_CONSUMER_GROUP_CPU_MTH displays all resource allocation methods defined for resource consumer groups.

--> V$RSRC_PDB displays data related to currently active resource consumer groups by pluggable database (PDB).

--> V$RSRC_PDB_HISTORY displays a history of consumer group statistics for each entry in V$RSRC_PDB that has a non-NULL plan by pluggable database (PDB).

--> V$RSRC_PLAN displays the names of all currently active resource plans.

--> V$RSRC_PLAN_CPU_MTH displays all available CPU resource allocation methods defined for resource plans.

--> V$RSRC_PLAN_HISTORY displays a history of when a resource plan was enabled, disabled, or modified on the instance. Up to 15 of the most recent entries are shown. Once the database is opened, this view shows at least one row. The row with the most recent START_TIME and with END_TIME equal to NULL gives information about the current resource plan.

--> V$RSRC_SESSION_INFO displays Resource Manager statistics per session.

--> V$RSRCPDBMETRIC displays information about resources consumed and wait times per PDB.

--> V$RSRCMGRMETRIC_HISTORY displays a history (the last one hour) of resource manager metrics, taken from V$RSRCMGRMETRIC. When a resource plan is set, this history is cleared and restarted. This view provides information about resources consumed and wait times per consumer group.

--> V$RSRCPDBMETRIC displays information about resources consumed and wait times per PDB.

--> V$RSRCPDBMETRIC_HISTORY displays a history (the last one hour) of resource manager metrics for a PDB, taken from V$RSRCPDBMETRIC. When a resource plan is set, this history is cleared and restarted. This view provides information about resources consumed and wait times per consumer group.

OCI FastConnect Overview

 

FastConnect Overview:

Oracle Cloud Infrastructure FastConnect provides an easy way to create a dedicated, private connection between your data center and Oracle Cloud Infrastructure. FastConnect provides higher-bandwidth options, and a more reliable and consistent networking experience compared to internet-based connections. This setup offers higher bandwidth, lower latency, and improved reliability compared to typical internet-based connections.

Key Features:

--> Private and Public Peering: Connect to OCI resources using private IP addresses (private peering) or access public OCI services like Object Storage and APIs over a dedicated connection 

--> Bandwidth Options: Supports port speeds ranging from 1 Gbps to 400 Gbps.

--> Cost Efficiency: No charges for inbound or outbound data transfer.

--> Flexible Peering Options:

• Private Peering: Extends your on-premises network into your OCI Virtual Cloud Network (VCN) using private IP addresses.

• Public Peering: Provides access to public OCI services (e.g., Object Storage, OCI Console) over a dedicated connection using public IP addresses.

--> No Data Transfer Charges: Unlike standard internet connections, FastConnect does not incur per-byte data transfer charges, making it cost-effective for large data movements

--> Routing Protocol: Utilizes BGP (Border Gateway Protocol) for dynamic routing.

-->Encryption Support: Options to encrypt public peering connections using IPSec VPN for enhanced security.

How does OCI FastConnect work?

FastConnect provides a dedicated private connection between  OCI environment and  data center or trusted third-party provider.

There are three physical connection types.

• -->Customers who have a presence in an existing FastConnect location (A) can provision redundant cross-connects directly to an Oracle FastConnect edge device. Port speeds of 1 Gb/sec, 10 Gb/sec, 100 Gb/sec, or 400 Gb/sec are available.
• -->Customers who have an existing connection to a FastConnect partner (B) can provision a connection to OCI, typically within hours to days. Partners can also provide connections to other clouds, enabling a multicloud deployment. Port speeds vary depending on the partner’s capabilities.
• -->Customers can independently contract with a third-party provider (C) to establish connectivity to an Oracle FastConnect location.

For resiliency and high availability, best practices recommend establishing multiple physical connections to the same OCI region.

Once a physical connection is established, there are two virtual circuit options. Public peering (D) enables you to access both private resources in a VCN as well as public resources without traversing the internet, similar in concept to using OCI as an ISP. Private peering (E) enables you to extend your on-premises private networks to OCI.

FastConnect supports the Border Gateway Protocol (BGP) to automate route updates between your environment, OCI, and (optionally) FastConnect partners or third-party providers.

North America Oracle FastConnect locations

Use Cases of OCI FastConnect:

Source: https://www.oracle.com/cloud/networking/fastconnect/

OCI IAM draft

 

Oracle Cloud Infrastructure Identity and Access Management (IAM):

Oracle Cloud Infrastructure Identity and Access Management (IAM) provides identity and access management features such as authentication, single sign-on (SSO), and identity lifecycle management for Oracle Cloud as well as Oracle and non-Oracle applications, whether SaaS, cloud-hosted, or on-premises.

I. OCI IAM: Authentication (AuthN)

 

Source: Oracle 

a. User Credentials:

b. API Keys:

c. OAuth 2.0 Tokens

d. Instance Principals

e. Federated Identity

f. Multi Factor Authentication (MFA)

II. OCI IAM: Authorization (AuthZ):

Source: Oracle

a. Policies

b. Groups

c. Dynamic Groups