FOA(Fun Oracle Apps) - Learning Never Stops

Oracle Database Performance Tuning Step by Step Beginner to Advanced Guide

noreply@blogger.com (Himanshu) — Wed, 14 Oct 2026 11:21:50 +0530

Oracle Database Performance Tuning Step by Step Beginner to Advanced Guide

Performance tuning is the most critical skill for any Oracle DBA. A slow database affects every user, every transaction, and every business process. This guide walks you through the complete performance tuning process from basic health checks to advanced optimization techniques. Every query and step here is production-tested and ready to use.

This is not about guessing. We follow a structured method: identify the problem, analyze the root cause, fix it, and validate the fix. Whether you manage Oracle 11g, 12c, 19c, or the latest 23ai, these steps apply to all versions.

What is Oracle Database Performance Tuning?

Performance tuning means identifying and removing bottlenecks that slow down your database. The bottleneck could be poorly written SQL, missing indexes, memory pressure, disk I/O waits, locking issues, or configuration problems.

The Oracle database tracks everything it does. Every wait, every SQL execution, every I/O operation gets recorded. Our job is to read those records and fix what is broken.

Prerequisites

Before you start tuning:

You need SELECT access on DBA views (DBA_TABLES, DBA_INDEXES, V$ views).
Diagnostic and Tuning pack license is required for AWR, ADDM, ASH, and SQL Tuning Advisor.
SYSDBA or DBA role for most tuning operations.
A baseline period: you cannot tune if you do not know what normal performance looks like.

Note: If your database does not have the Diagnostic Pack license, use Statspack instead of AWR. The method is the same but the views and reports are different.

Step 1: Establish a Performance Baseline

A baseline is a snapshot of your database during normal operation. You compare current performance against this baseline to see what changed.

1.1 Check AWR Snapshot Configuration

AWR (Automatic Workload Repository) takes snapshots of database performance metrics every hour by default and retains them for 8 days.

-- Check AWR snapshot settings
SELECT snap_interval, retention FROM dba_hist_wr_control;

Expected output:

SNAP_INTERVAL              RETENTION
+00000 01:00:00.0         +00008 00:00:00.0

If AWR is not enabled or the interval is too long, modify it:

-- Modify AWR settings (SYSDBA required)
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 10080,      -- 7 days in minutes
    interval  => 30          -- snapshot every 30 minutes
  );
END;
/

1.2 Take a Manual AWR Snapshot

When you start a tuning exercise, take a manual snapshot so you have a clear before-and-after comparison.

-- Take manual snapshot
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

1.3 Generate an AWR Report

Run the database under normal load for at least 1 hour, then generate the AWR report.

-- For single instance database
@?/rdbms/admin/awrrpt.sql

-- For RAC database
@?/rdbms/admin/awrgrpt.sql

You will be prompted for:

Report type: html or text (choose html)
Begin snapshot ID
End snapshot ID

The report gets saved to your current directory. Open it in a browser.

What to look for in AWR report:

Top 5 Timed Events section: shows where the database spends most of its time
SQL ordered by Elapsed Time: the slowest queries
Load Profile: transactions per second, logical reads, physical reads
Instance Efficiency: buffer cache hit ratio, library cache hit ratio

1.4 Run ADDM (Automatic Database Diagnostic Monitor)

ADDM analyzes AWR data and gives you recommendations automatically.

-- Get the latest snapshot IDs
SELECT MAX(snap_id) - 1 AS begin_snap, MAX(snap_id) AS end_snap 
FROM dba_hist_snapshot 
WHERE instance_number = (SELECT instance_number FROM v$instance);

-- Generate ADDM report
@?/rdbms/admin/addmrpt.sql

Enter the begin and end snapshot IDs from the query above. ADDM will tell you exactly what is slow and what to fix.

Step 2: Analyze Wait Events (The Heart of Tuning)

Oracle wait events tell you why sessions are waiting instead of working. This is the single most important diagnostic data.

2.1 Check Current Wait Events

-- See what the database is waiting for right now
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE event NOT IN ('SQL*Net message from client', 'rdbms ipc message', 
                    'smon timer', 'pmon timer')
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

2.2 Top Wait Events from AWR

-- Top wait events in the last 24 hours
SELECT 
    event_name,
    total_waits,
    time_waited_micro/1000000 time_waited_sec,
    ROUND(time_waited_micro*100/SUM(time_waited_micro) OVER (), 2) pct_time
FROM dba_hist_system_event
WHERE snap_id BETWEEN (SELECT MAX(snap_id)-24 FROM dba_hist_snapshot) 
                  AND (SELECT MAX(snap_id) FROM dba_hist_snapshot)
  AND wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

2.3 Common Wait Events and What They Mean

Wait Event	Root Cause	Fix
db file scattered read	Full table scans, index range scans	Add indexes, rewrite SQL, increase db_file_multiblock_read_count
db file sequential read	Single block reads (index lookups on large tables)	Check index efficiency, partition large tables, faster disks
log file sync	Slow redo log writes, commit frequency	Move redo logs to faster disks, reduce commits, check I/O subsystem
log file parallel write	Slow disk I/O for redo logs	Move redo to SSD or faster storage, check for I/O bottleneck
enq: TX - row lock contention	Multiple sessions updating same rows	Application fix - reduce lock duration, change batch logic
library cache lock/pin	Hard parsing, shared pool contention	Use bind variables, increase shared_pool_size
latch: cache buffers chains	Hot block contention in buffer cache	Reverse key indexes, partition the table, increase freelists
direct path read/write	Parallel query, temp operations	Expected for big queries, check temp tablespace size

2.4 Find Sessions Causing Waits Right Now

-- Active sessions and their wait events
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.event,
    s.seconds_in_wait,
    s.sql_id,
    q.sql_text
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

Step 3: SQL Tuning (Fixing the Queries)

Most performance problems are bad SQL. Fix the SQL and you fix 80% of performance issues.

3.1 Find Top Resource-Consuming SQL

-- Top SQL by elapsed time (from AWR)
SELECT 
    sql_id,
    ROUND(elapsed_time_delta/1000000, 2) elapsed_sec,
    executions_delta executions,
    ROUND(elapsed_time_delta/executions_delta/1000000, 4) avg_sec,
    ROUND(cpu_time_delta/1000000, 2) cpu_sec,
    ROUND(iowait_delta/1000000, 2) io_sec,
    disk_reads_delta,
    buffer_gets_delta
FROM dba_hist_sqlstat
WHERE snap_id = (SELECT MAX(snap_id) FROM dba_hist_snapshot)
  AND executions_delta > 0
ORDER BY elapsed_time_delta DESC
FETCH FIRST 20 ROWS ONLY;

3.2 Get the Full SQL Text

-- Retrieve full SQL text for a SQL_ID
SELECT sql_text 
FROM dba_hist_sqltext 
WHERE sql_id = '&sql_id';

-- Or from current cache
SELECT sql_fulltext 
FROM v$sql 
WHERE sql_id = '&sql_id';

3.3 Check Execution Plan

-- Get execution plan from cursor cache
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

-- Get execution plan from AWR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

What to look for in execution plans:

FULL table scans on large tables (Rows: millions, Access: FULL)
TABLE ACCESS FULL when an index exists
Nested loops joining millions of rows (should be hash join)
High cardinality estimates vs actual rows
Missing indexes (check Predicate Information section)

3.4 Run SQL Tuning Advisor

SQL Tuning Advisor is Oracle's built-in AI for SQL. It suggests indexes, SQL profiles, and rewrites.

-- Create tuning task for a SQL_ID
DECLARE
  l_task_name VARCHAR2(100);
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => '&sql_id',
    scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit  => 300,
    task_name   => 'TUNE_&sql_id',
    description => 'Tuning task for SQL_ID &sql_id'
  );
  DBMS_OUTPUT.PUT_LINE('Task Name: ' || l_task_name);
END;
/

-- Execute the task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_&sql_id');

-- Get recommendations
SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_&sql_id') FROM DUAL;

The report will tell you:

Missing indexes (with exact CREATE INDEX statement)
Whether to accept a SQL Profile for better plan
Statistics that need refresh
SQL rewrites to consider

3.5 Check Table and Index Statistics

Stale or missing statistics cause the optimizer to choose bad plans.

-- Check table statistics age
SELECT 
    table_name,
    num_rows,
    blocks,
    last_analyzed,
    ROUND((SYSDATE - last_analyzed), 2) days_old
FROM dba_tables
WHERE owner = '&owner'
  AND table_name = '&table_name';

-- Check index statistics
SELECT 
    index_name,
    blevel,
    leaf_blocks,
    distinct_keys,
    clustering_factor,
    last_analyzed
FROM dba_indexes
WHERE owner = '&owner'
  AND table_name = '&table_name';

Gather statistics if last_analyzed is more than 7 days old or NULL:

-- Gather table stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => '&owner', tabname => '&table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE);

-- Gather schema stats
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => '&owner', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE);

-- Gather database stats (run during low usage)
EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE);

3.6 Identify Missing Indexes

-- Find columns used in WHERE clause without indexes
SELECT 
    owner,
    table_name,
    column_name,
    num_distinct,
    density
FROM dba_tab_columns
WHERE owner = '&owner'
  AND table_name = '&table_name'
  AND column_name NOT IN (
      SELECT column_name 
      FROM dba_ind_columns 
      WHERE table_owner = '&owner' 
        AND table_name = '&table_name'
  )
ORDER BY num_distinct DESC;

Create indexes on columns with high selectivity (num_distinct close to number of rows).

-- Create index template
CREATE INDEX &owner..idx_&table_name_&column_name 
ON &owner..&table_name(&column_name) 
TABLESPACE &index_tablespace 
PARALLEL 4 NOLOGGING;

-- Rebuild index as logging after creation
ALTER INDEX &owner.idx_&table_name_&column_name LOGGING NOPARALLEL;

3.7 Check for Index Fragmentation

-- Identify fragmented indexes (blevel > 3 or deleted entries > 20%)
SELECT 
    owner,
    index_name,
    blevel,
    leaf_blocks,
    distinct_keys,
    ROUND((leaf_blocks / distinct_keys), 2) blocks_per_key,
    clustering_factor,
    status
FROM dba_indexes
WHERE owner = '&owner'
  AND (blevel > 3 OR status != 'VALID')
ORDER BY blevel DESC;

Rebuild fragmented indexes:

-- Rebuild index online (no downtime)
ALTER INDEX &owner.&index_name REBUILD ONLINE PARALLEL 4;
ALTER INDEX &owner.&index_name NOPARALLEL;

Step 4: Memory Tuning (SGA and PGA)

Memory is the fastest storage. The more you keep in memory, the less you hit disk.

4.1 Check Current Memory Allocation

-- SGA components
SELECT 
    component,
    current_size/1024/1024 current_mb,
    min_size/1024/1024 min_mb,
    max_size/1024/1024 max_mb
FROM v$sga_dynamic_components
ORDER BY current_size DESC;

-- PGA usage
SELECT 
    name,
    value/1024/1024 value_mb
FROM v$pgastat
WHERE name IN ('aggregate PGA target parameter', 
               'aggregate PGA auto target',
               'total PGA inuse',
               'total PGA allocated',
               'maximum PGA allocated');

4.2 Check Buffer Cache Hit Ratio

The buffer cache holds data blocks in memory. A hit ratio below 90% means too much disk I/O.

-- Buffer cache hit ratio (should be > 95%)
SELECT 
    ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) buffer_cache_hit_ratio
FROM 
    v$sysstat cur,
    v$sysstat con,
    v$sysstat phy
WHERE 
    cur.name = 'db block gets'
    AND con.name = 'consistent gets'
    AND phy.name = 'physical reads';

If hit ratio < 90%: Increase db_cache_size or sga_target.

4.3 Check Library Cache Hit Ratio

Library cache stores parsed SQL and PL/SQL. Low hit ratio means hard parsing (CPU expensive).

-- Library cache hit ratio (should be > 95%)
SELECT 
    namespace,
    ROUND(gethitratio * 100, 2) hit_ratio,
    ROUND(pinhitratio * 100, 2) pin_hit_ratio
FROM v$librarycache
ORDER BY namespace;

If hit ratio < 90%: Sessions are not using bind variables. Check for literal SQL.

-- Find SQL with literals (not using bind variables)
SELECT 
    sql_id,
    executions,
    version_count,
    SUBSTR(sql_text, 1, 80) sql_text
FROM v$sqlarea
WHERE executions < 5
  AND version_count = 1
  AND UPPER(sql_text) LIKE '%WHERE%'
  AND UPPER(sql_text) NOT LIKE '%BIND%'
ORDER BY version_count DESC
FETCH FIRST 20 ROWS ONLY;

Force application developers to use bind variables.

4.4 Check Shared Pool Fragmentation

-- Shared pool free memory
SELECT 
    ROUND(SUM(bytes)/1024/1024, 2) free_mb
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name = 'free memory';

If free memory is very low and library cache hit ratio is also low:

-- Flush shared pool (only in test environment, causes hard parsing spike)
ALTER SYSTEM FLUSH SHARED_POOL;

In production, increase shared_pool_size.

4.5 PGA Tuning for Sorting and Hashing

PGA memory is used for sorts, hash joins, and bitmap operations. If PGA is too small, sorts spill to temp tablespace (slow).

-- Check PGA advice
SELECT 
    pga_target_for_estimate/1024/1024 pga_target_mb,
    estd_pga_cache_hit_percentage cache_hit_pct,
    estd_overalloc_count
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

Pick a PGA size where cache hit percentage is above 95% and overallocation count is zero.

-- Set PGA aggregate target (requires restart or use ALTER SYSTEM)
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;

4.6 Automatic Memory Management (AMM)

For Oracle 11g and above, you can let Oracle manage memory automatically.

-- Check current memory management mode
SHOW PARAMETER memory_target;
SHOW PARAMETER memory_max_target;
SHOW PARAMETER sga_target;
SHOW PARAMETER pga_aggregate_target;

If memory_target = 0: You are using manual or ASMM (Automatic Shared Memory Management).

Switch to AMM:

-- Set total memory size
ALTER SYSTEM SET memory_target = 8G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 12G SCOPE=SPFILE;

-- Restart database
SHUTDOWN IMMEDIATE;
STARTUP;

Oracle now manages SGA and PGA automatically.

Note: On Oracle 12.2+ on Linux, AMM is not recommended. Use ASMM instead (set sga_target and pga_aggregate_target, leave memory_target = 0).

Step 5: I/O Tuning (Disk Performance)

Disk I/O is the slowest part of the system. Even a small I/O reduction gives big performance gains.

5.1 Identify Top I/O Consuming Objects

-- Top segments by physical reads
SELECT 
    owner,
    object_name,
    object_type,
    statistic_name,
    value
FROM v$segment_statistics
WHERE statistic_name IN ('physical reads', 'physical writes')
  AND value > 1000
ORDER BY value DESC
FETCH FIRST 20 ROWS ONLY;

5.2 Check Tablespace I/O Distribution

-- I/O per datafile
SELECT 
    df.tablespace_name,
    df.file_name,
    fs.phyrds physical_reads,
    fs.phywrts physical_writes,
    fs.readtim read_time_cs,
    fs.writetim write_time_cs,
    ROUND(fs.readtim / NULLIF(fs.phyrds, 0), 2) avg_read_time_cs
FROM v$filestat fs
JOIN dba_data_files df ON fs.file# = df.file_id
ORDER BY fs.phyrds DESC;

If one datafile has 10x more reads than others: Spread that tablespace across multiple disks or move hot objects to SSD.

5.3 Check Temp Tablespace Usage

Large sorts and hash joins use temp. If temp is full, queries fail.

-- Temp usage by session
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    t.tablespace,
    ROUND(t.blocks * 8192 / 1024 / 1024, 2) temp_mb,
    q.sql_id,
    SUBSTR(q.sql_text, 1, 100) sql_text
FROM v$tempseg_usage t
JOIN v$session s ON t.session_addr = s.saddr
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
ORDER BY t.blocks DESC;

Add more tempfiles if temp is constantly full:

-- Add tempfile
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/PROD/temp02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

5.4 Monitor Redo Log Performance

Redo logs must be fast. Slow redo writes cause log file sync waits.

-- Redo log switch frequency
SELECT 
    TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,
    COUNT(*) switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

If switches happen every 5-10 minutes: Redo logs are too small. Increase redo log size to reduce switch frequency to once per hour.

-- Add new larger redo log group (do this for each group)
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oradata/PROD/redo04a.log', '/u02/oradata/PROD/redo04b.log') SIZE 2G;

-- Switch to new group
ALTER SYSTEM SWITCH LOGFILE;

-- Drop old small group after it is inactive
ALTER DATABASE DROP LOGFILE GROUP 1;

5.5 Check UNDO Retention and Usage

UNDO tablespace holds old versions of rows for read consistency and flashback.

-- UNDO usage
SELECT 
    tablespace_name,
    ROUND(SUM(bytes)/1024/1024, 2) size_mb,
    ROUND(SUM(bytes)/1024/1024 - SUM(DECODE(status, 'EXPIRED', bytes, 0))/1024/1024, 2) used_mb
FROM dba_undo_extents
GROUP BY tablespace_name;

-- UNDO retention
SHOW PARAMETER undo_retention;

If you get "ORA-01555 snapshot too old": Increase undo_retention or add space to UNDO tablespace.

ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;  -- 1 hour retention

Step 6: Locking and Contention Issues

Locks prevent data corruption but also block other sessions. Lock waits kill performance.

6.1 Find Blocking Sessions

-- Blocking session tree
SELECT 
    LPAD(' ', LEVEL * 2) || s.sid || ',' || s.serial# blocking_tree,
    s.username,
    s.program,
    s.status,
    s.event,
    s.seconds_in_wait,
    s.sql_id
FROM v$session s
START WITH s.blocking_session IS NOT NULL
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER SIBLINGS BY s.sid;

-- Who is blocking whom (simple view)
SELECT 
    blocking.sid blocking_sid,
    blocking.serial# blocking_serial,
    blocking.username blocking_user,
    blocked.sid blocked_sid,
    blocked.serial# blocked_serial,
    blocked.username blocked_user,
    blocked.event,
    blocked.seconds_in_wait
FROM v$session blocking
JOIN v$session blocked ON blocking.sid = blocked.blocking_session
WHERE blocked.blocking_session IS NOT NULL;

6.2 Identify Lock Type and Object

-- Locked objects
SELECT 
    s.sid,
    s.serial#,
    s.username,
    o.owner,
    o.object_name,
    o.object_type,
    l.locked_mode,
    DECODE(l.locked_mode, 
        0, 'None',
        1, 'Null',
        2, 'Row Share',
        3, 'Row Exclusive',
        4, 'Share',
        5, 'Share Row Exclusive',
        6, 'Exclusive') lock_mode
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid
ORDER BY s.sid;

6.3 Kill a Blocking Session (Last Resort)

Only kill a session if it is truly stuck and blocking critical work.

-- Kill session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Example
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;

Note: Killing a session rolls back its transaction. This can take time if the transaction is large.

6.4 Reduce Lock Contention at Application Level

Most lock contention is an application design issue:

Use smaller transactions (commit more often in batch jobs)
Use SELECT FOR UPDATE NOWAIT to fail fast instead of waiting
Avoid long-running transactions during peak hours
Partition large tables to reduce row contention
Use application-level queuing instead of database locks

Step 7: Configuration and Initialization Parameters

Some init.ora parameters directly affect performance.

7.1 Review Current Parameters

-- Show all non-default parameters
SELECT 
    name,
    value,
    isdefault,
    ismodified
FROM v$parameter
WHERE isdefault = 'FALSE'
ORDER BY name;

7.2 Key Performance Parameters to Check

-- Critical parameters
SELECT name, value, description 
FROM v$parameter 
WHERE name IN (
    'sga_target',
    'pga_aggregate_target',
    'memory_target',
    'db_cache_size',
    'shared_pool_size',
    'processes',
    'sessions',
    'open_cursors',
    'cursor_sharing',
    'optimizer_mode',
    'optimizer_index_cost_adj',
    'db_file_multiblock_read_count',
    'parallel_max_servers',
    'parallel_degree_policy'
)
ORDER BY name;

Recommended settings for OLTP workloads:

Parameter	Recommended Value	Notes
cursor_sharing	EXACT	Use bind variables in code, not FORCE
optimizer_mode	ALL_ROWS	For OLTP; use FIRST_ROWS_n for reports
optimizer_index_cost_adj	100	Default; reduce if optimizer ignores indexes
db_file_multiblock_read_count	8-16	Higher for DW, lower for OLTP
open_cursors	300-1000	Increase if you get ORA-01000
processes	(connections * 1.2) + 50	Leave headroom
sessions	processes * 1.5	Auto-derived

7.3 Enable Parallel Query for Large Tables (Use Carefully)

Parallel query splits a query across multiple CPU cores.

-- Check parallel execution stats
SELECT 
    name,
    value
FROM v$sysstat
WHERE name LIKE '%parallel%'
ORDER BY name;

Do not enable parallel on OLTP tables. It is for large batch reports and DW loads only.

-- Enable parallel on a table (DW only)
ALTER TABLE &owner..&table_name PARALLEL 4;

-- Disable parallel (OLTP)
ALTER TABLE &owner..&table_name NOPARALLEL;

Step 8: Monitoring and Validation

After tuning, you must validate that performance actually improved.

8.1 Take Another AWR Snapshot

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Wait for the same workload period (1-2 hours), then take another snapshot and generate the AWR report.

8.2 Compare Before and After

Compare these sections in the new AWR report:

Load Profile: Are transactions per second higher? Are physical reads lower?
Top 5 Timed Events: Did the top wait event drop in percentage?
SQL ordered by Elapsed Time: Did the problematic SQL_IDs drop in rank or disappear?
Instance Efficiency Percentages: Did buffer cache hit ratio improve?

8.3 Monitor SQL Execution Time Over Time

-- Track a specific SQL_ID performance trend
SELECT 
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') snap_time,
    h.executions_delta executions,
    ROUND(h.elapsed_time_delta/1000000, 2) elapsed_sec,
    ROUND(h.elapsed_time_delta/h.executions_delta/1000000, 4) avg_sec,
    h.plan_hash_value
FROM dba_hist_sqlstat h
JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id
WHERE h.sql_id = '&sql_id'
  AND h.executions_delta > 0
  AND s.begin_interval_time > SYSDATE - 7
ORDER BY s.begin_interval_time;

If avg_sec decreased after your tuning, you succeeded.

8.4 Set Up Alerts for Future Issues

-- Create alert for tablespace usage > 85%
BEGIN
  DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    warning_operator        => DBMS_SERVER_ALERT.OPERATOR_GE,
    warning_value           => '85',
    critical_operator       => DBMS_SERVER_ALERT.OPERATOR_GE,
    critical_value          => '95',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
    object_name             => 'USERS'
  );
END;
/

Check alerts:

SELECT 
    reason,
    creation_time,
    message_level
FROM dba_outstanding_alerts
ORDER BY creation_time DESC;

Advanced Tuning Techniques

9.1 Partitioning Large Tables

Tables with hundreds of millions of rows should be partitioned. This improves query performance through partition pruning.

-- Identify large tables (> 10 GB)
SELECT 
    owner,
    table_name,
    ROUND(bytes/1024/1024/1024, 2) size_gb,
    num_rows,
    partitioned
FROM dba_tables t
JOIN dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
WHERE t.owner NOT IN ('SYS', 'SYSTEM')
  AND bytes > 10737418240  -- 10 GB
  AND partitioned = 'NO'
ORDER BY bytes DESC;

Partition by a column frequently used in WHERE clauses (date, region, status).

-- Example: Partition by date range
CREATE TABLE orders_partitioned (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION orders_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION orders_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION orders_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

9.2 Result Cache (12c+)

Result cache stores query results in memory. Identical queries return cached results instantly.

-- Enable result cache
SHOW PARAMETER result_cache_max_size;

-- If 0, set it
ALTER SYSTEM SET result_cache_max_size = 1G SCOPE=BOTH;

Use it in queries:

SELECT /*+ RESULT_CACHE */ COUNT(*) FROM large_table WHERE status = 'ACTIVE';

9.3 In-Memory Column Store (12c+)

In-Memory Column Store keeps tables in columnar format in memory for analytic queries.

-- Enable In-Memory
SHOW PARAMETER inmemory_size;

-- Set In-Memory pool size
ALTER SYSTEM SET inmemory_size = 4G SCOPE=SPFILE;
-- Restart required

-- Enable a table for In-Memory
ALTER TABLE &owner..&table_name INMEMORY;

-- Check In-Memory status
SELECT 
    segment_name,
    inmemory_size,
    bytes_not_populated,
    populate_status
FROM v$im_segments;

Only use In-Memory for read-heavy tables in DW and reporting environments.

9.4 Adaptive Query Optimization (12c+)

Oracle 12c introduced adaptive plans that adjust at runtime.

-- Check if adaptive features are enabled
SELECT 
    name,
    value
FROM v$parameter
WHERE name LIKE 'optimizer_adaptive%';

Recommended settings:

ALTER SYSTEM SET optimizer_adaptive_reporting_only = FALSE SCOPE=BOTH;
ALTER SYSTEM SET optimizer_adaptive_features = TRUE SCOPE=BOTH;

9.5 SQL Plan Management (SPM)

SPM locks down good execution plans so Oracle does not change them.

-- Load a plan from cursor cache into a baseline
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id',
    plan_hash_value => &plan_hash_value
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

-- Check SQL Plan Baselines
SELECT 
    sql_handle,
    plan_name,
    enabled,
    accepted,
    fixed,
    origin
FROM dba_sql_plan_baselines;

Troubleshooting Common Performance Problems

ORA-04031: Unable to Allocate Memory in Shared Pool

Root Cause: Shared pool fragmentation or too small.

Fix:

-- Immediate (flush shared pool - causes hard parsing spike)
ALTER SYSTEM FLUSH SHARED_POOL;

-- Permanent
ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

High CPU Usage with No Wait Events

Root Cause: Inefficient SQL causing excessive logical I/O (buffer gets).

Fix: Find CPU-intensive SQL:

SELECT 
    sql_id,
    executions,
    ROUND(cpu_time/1000000, 2) cpu_sec,
    ROUND(cpu_time/executions/1000000, 4) avg_cpu_sec,
    SUBSTR(sql_text, 1, 100) sql_text
FROM v$sql
WHERE cpu_time > 10000000  -- More than 10 seconds total CPU
ORDER BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;

Tune those SQLs.

Database Slow After Statistics Gather

Root Cause: New statistics changed execution plans.

Fix: Restore old statistics:

-- Check stats history
SELECT 
    table_name,
    stats_update_time
FROM dba_tab_stats_history
WHERE owner = '&owner'
  AND table_name = '&table_name'
ORDER BY stats_update_time DESC;

-- Restore old stats
EXEC DBMS_STATS.RESTORE_TABLE_STATS(ownname => '&owner', tabname => '&table_name', as_of_timestamp => TO_TIMESTAMP('2025-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS'));

Log File Sync Wait Spikes

Root Cause: Slow redo log I/O or excessive commits.

Fix:

Move redo logs to faster disks (SSD).
Reduce commit frequency in batch jobs.
Check for I/O bottleneck on redo log disks.

-- Check redo I/O stats
SELECT 
    TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,
    COUNT(*) log_switches,
    ROUND(AVG(bytes)/1024/1024, 2) avg_redo_mb
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Best Practices Checklist

Before you close any tuning exercise, verify:

AWR report shows reduced Top 5 Wait times.
Problem SQL_IDs no longer appear in top 20 by elapsed time.
Buffer cache hit ratio > 95%.
Library cache hit ratio > 95%.
No tablespace > 85% full.
No index with blevel > 4.
All production tables have current statistics (< 7 days old).
Redo log switches happen < once per hour.
No blocking sessions during peak hours.
Application team confirmed improved response times.

Document everything you changed. If performance gets worse later, you need a rollback plan.

Summary

Oracle database performance tuning is not magic. It is a repeatable process: collect data (AWR, ASH, wait events), identify the bottleneck (SQL, I/O, memory, locks), fix it with the right tool (indexes, statistics, rewrites, memory increase), and validate the improvement with another AWR comparison.

Start with wait events. They tell you exactly where the database is slow. Then tune the SQL causing those waits. Refresh statistics and add indexes where needed. Adjust memory if cache hit ratios are low. Move to I/O tuning only if waits point there.

Do not tune blindly. Do not add indexes everywhere. Do not increase memory parameters randomly. Every change must target a specific measured bottleneck.

Use this guide as a checklist every time performance degrades. Follow the steps in order and you will find and fix the problem faster than guessing.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow, share and comment

FAQ

Q1: How often should I run AWR reports?
Run AWR daily during peak hours to establish a baseline. When performance problems occur, run AWR for the problem period and compare it to the baseline.

Q2: Can I use these steps on Oracle Standard Edition?
Most steps yes, but AWR, ADDM, ASH, and SQL Tuning Advisor require Enterprise Edition with Diagnostic and Tuning Pack license. Use Statspack instead for Standard Edition.

Q3: What is the fastest way to identify the performance problem?
Check the Top 5 Timed Events in AWR report first. That tells you the category (SQL, I/O, memory, locks). Then drill down from there.

Q4: Should I increase SGA or PGA first?
Check buffer cache hit ratio and PGA cache hit ratio. Increase whichever has the lower percentage first. Usually buffer cache matters more for OLTP.

Q5: How do I tune without affecting production?
Test all changes in a non-production environment first. For production, use online index rebuild, statistics gather at low-usage hours, and SQL profiles instead of SQL rewrites.

Oracle IDCS Integration with EBS and Active Directory – Complete SSO Setup Guide

noreply@blogger.com (Himanshu) — Wed, 14 Oct 2026 11:11:19 +0530

Oracle IDCS Integration with EBS and Active Directory – Complete SSO Setup Guide

Single Sign-On (SSO) has become a critical requirement for modern Oracle E-Business Suite deployments. Users expect to log in once and access all their enterprise applications seamlessly — without re-entering credentials. Oracle Identity Cloud Service (IDCS) makes this possible by providing a centralized, cloud-based identity management platform that integrates with both Oracle E-Business Suite and enterprise identity providers like Microsoft Active Directory.

In this comprehensive guide, I'll walk you through the complete integration of Oracle Identity Cloud Service (IDCS) with Oracle E-Business Suite 12.2 and Microsoft Active Directory, enabling:

Single Sign-On (SSO) for EBS users using IDCS credentials
Active Directory Federation so users log in with their corporate AD accounts
Centralized user management across cloud and on-premises applications
Multi-Factor Authentication (MFA) and adaptive security policies

This is a production-ready, step-by-step implementation guide with real commands, configuration files, and troubleshooting tips.

What is Oracle Identity Cloud Service (IDCS)?

Oracle Identity Cloud Service (IDCS) is Oracle's cloud-native, Identity-as-a-Service (IDaaS) platform. It provides:

Single Sign-On (SSO) for cloud and on-premises applications
Multi-Factor Authentication (MFA) with OTP, SMS, email, and biometric support
User Lifecycle Management — create, update, deactivate users centrally
Federation with external Identity Providers (IdP) — Active Directory, ADFS, Okta, Azure AD, Google
API-driven access control and OAuth 2.0 / SAML 2.0 support
Adaptive Security — risk-based authentication, IP whitelisting, device trust

IDCS replaces the traditional on-premises SSO stack (Oracle Access Manager + Oracle Internet Directory) with a simpler, cloud-native solution.

Why Integrate IDCS with EBS and Active Directory?

The Traditional EBS SSO Stack is Complex

Historically, implementing SSO for Oracle EBS required:

Oracle Access Manager (OAM) — web SSO engine
Oracle Internet Directory (OID) — LDAP directory
Oracle HTTP Server (OHS) — reverse proxy with WebGate agent
AD Sync Tools — to replicate users from Active Directory to OID

This multi-tier architecture is expensive to deploy, maintain, and upgrade.

IDCS Simplifies Everything

With IDCS:

✅ No OAM or OID required — IDCS replaces both
✅ Lightweight EBS Asserter — a small Java WAR app deployed on WebLogic
✅ Native AD Federation — SAML 2.0 federation with Active Directory Federation Services (ADFS) or AD Bridge for user sync
✅ Cloud-managed — Oracle handles patching, upgrades, and availability
✅ Works with EBS on-premises and in OCI — hybrid cloud ready

Business Benefits

Faster Deployment — 1–2 weeks vs. 2–3 months for OAM/OID
Lower TCO — no infrastructure to maintain
Better Security — MFA, adaptive policies, audit logs in the cloud
Unified User Experience — one login for EBS, cloud apps, and SaaS tools

Architecture Overview — How It All Fits Together

Here's the high-level integration architecture:

┌─────────────────────┐
│  End User Browser   │
└──────────┬──────────┘
           │
           │ 1. Access EBS URL
           ▼
┌──────────────────────────────┐
│   EBS Asserter (WebLogic)    │  ◄── Java WAR app
│   https://ebsasserter.com    │
└──────────┬───────────────────┘
           │
           │ 2. Redirect to IDCS for authentication
           ▼
┌──────────────────────────────┐
│  Oracle Identity Cloud       │
│  Service (IDCS)              │  ◄── Cloud Identity Provider
│  identity.oraclecloud.com    │
└──────────┬───────────────────┘
           │
           │ 3. Federate to AD FS (if AD user)
           ▼
┌──────────────────────────────┐
│  Microsoft Active Directory  │
│  Federation Services (ADFS)  │  ◄── On-Premises IdP
│  or AD Bridge                │
└──────────┬───────────────────┘
           │
           │ 4. Validate user credentials
           ▼
┌──────────────────────────────┐
│  Microsoft Active Directory  │  ◄── Enterprise LDAP
│  (Domain Controller)         │
└──────────────────────────────┘

Flow:

User accesses Oracle EBS URL
EBS redirects to EBS Asserter
EBS Asserter redirects to IDCS for authentication
IDCS offers login options: IDCS native or federated AD (via ADFS or AD Bridge)
User authenticates with Active Directory credentials
ADFS sends SAML assertion to IDCS
IDCS validates assertion and issues OAuth token
EBS Asserter exchanges token for EBS session cookie
User is logged into EBS — SSO complete!

Prerequisites — What You Need Before Starting

1. Oracle Identity Cloud Service (IDCS) Instance

You need an active IDCS tenant. If you don't have one:

OCI Tenancy with IDCS — comes free with Oracle Cloud Free Tier
Standalone IDCS subscription — available via Oracle sales

Check your IDCS URL format:
https://<tenant-name>.identity.oraclecloud.com

Example: https://mycompany.identity.oraclecloud.com

2. Oracle E-Business Suite Environment

Supported EBS Versions:

Oracle EBS Release 11i (11.5.10) — with latest patches
Oracle EBS Release 12.1.3 or later — with latest patches
Oracle EBS Release 12.2.x — recommended

Important: EBS must NOT be integrated with OAM, OID, or any other SSO solution. If it is, remove the integration first.

Verify EBS is accessible via HTTPS (required for IDCS SSO):

https://ebs.example.com/OA_HTML/AppsLogin

3. Separate Oracle WebLogic Server 12c for EBS Asserter

Do NOT use the EBS application server for EBS Asserter. Deploy it on a dedicated WebLogic instance.

Supported Versions:

Oracle WebLogic Server 12.1.3
Oracle WebLogic Server 12.2.x
Oracle WebLogic Server 14.1.1 (14c)

Java Requirements:

Java SE Development Kit 8 (JDK 8) or Java EE 8

Domain Requirement:
⚠️ Critical: The EBS Asserter WebLogic server hostname and EBS application server hostname must be in the same domain for SSO to work.

✅ Correct:

EBS Asserter: https://ebsasserter.example.com
EBS Apps: https://ebs.example.com

❌ Wrong:

EBS Asserter: https://ebsasserter.mydomain.com
EBS Apps: https://ebs.anotherdomain.com

4. Microsoft Active Directory (Optional but Recommended)

If you want users to log in with their corporate AD credentials:

Active Directory Domain Services (Windows Server 2012 R2 or later)
Active Directory Federation Services (ADFS) — for SAML 2.0 federation
OR Oracle IDCS AD Bridge — for direct user sync from AD to IDCS

5. IDCS Administrator Role

You need the Identity Domain Administrator or Security Administrator role in IDCS to:

Register the EBS Asserter application
Configure AD federation
Manage users and policies

6. EBS Application DBA Access

You need SYSADMIN access in EBS to:

Create the EBS Asserter service user
Register the asserter application server
Update user email addresses

7. Time Synchronization

⚠️ Critical: Ensure server clocks are synchronized across:

EBS Asserter WebLogic server
EBS Application server
Active Directory server

Use NTP (Network Time Protocol) to sync all servers. SAML assertions are time-sensitive and will fail if clocks drift more than 5 minutes.

# Check time on Linux
date
timedatectl status

# Enable NTP sync
sudo systemctl enable chronyd
sudo systemctl start chronyd

Planning Checklist — Before You Start

Before touching any system, complete this planning checklist:

1. Network and Firewall

Ensure network connectivity:

Source	Destination	Port	Protocol	Purpose
EBS Asserter WebLogic	EBS DB	1521	TCP	JDBC connection to APPS schema
EBS Asserter WebLogic	IDCS	443	HTTPS	OAuth token exchange
User Browser	EBS Asserter	443	HTTPS	SSO login
User Browser	IDCS	443	HTTPS	Authentication
IDCS	ADFS (if used)	443	HTTPS	SAML federation
AD Bridge (if used)	AD Domain Controller	389/636	LDAP/LDAPS	User sync

2. DNS Resolution

Verify DNS resolution from all servers:

nslookup ebs.example.com
nslookup ebsasserter.example.com
nslookup mycompany.identity.oraclecloud.com
nslookup adfs.example.com

3. SSL Certificates

You need valid SSL certificates for:

EBS Asserter WebLogic Server — signed by a trusted CA (not self-signed)
EBS Application Server — signed by a trusted CA
ADFS Server (if used) — signed by a trusted CA

Self-signed certificates will cause SSO failures.

4. User Identity Mapping

Decide how users will be matched between IDCS and EBS:

Attribute	EBS Column	IDCS Attribute	Recommended
Email	`FND_USERS.EMAIL_ADDRESS`	`email`	✅ Yes (most reliable)
Username	`FND_USERS.USER_NAME`	`username`	⚠️ Only if usernames match exactly

Best Practice: Use email address as the unique identifier. Ensure every EBS user has a valid email in FND_USERS.EMAIL_ADDRESS.

5. Backup

Take backups before starting:

# Backup EBS database
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# Backup EBS configuration files
tar -czf ebs_config_backup_$(date +%Y%m%d).tar.gz $INST_TOP/appl

# Backup WebLogic domain
tar -czf wls_domain_backup_$(date +%Y%m%d).tar.gz $DOMAIN_HOME

6. Downtime Planning

No EBS downtime is required for this integration. The EBS Asserter is deployed externally, and you can test the integration before switching users to SSO.

Estimated implementation time: 6–8 hours

Part 1: Download and Prepare EBS Asserter

Step 1: Download EBS Asserter from IDCS

Sign in to Oracle Identity Cloud Service console:
https://<tenant-name>.identity.oraclecloud.com/ui/v1/adminconsole
Click the Navigation Menu (≡), expand Settings, and click Downloads.
On the Downloads page, locate Identity Cloud Service E-Business Suite Asserter, and click Download.
Save the zip file: idcs-ebs-asserter-<version>.zip
Example: idcs-ebs-asserter-19.1.4-1.2.2.zip

Extract the zip file on your desktop:

unzip idcs-ebs-asserter-19.1.4-1.2.2.zip

Inside the extracted folder, you'll find:
- ebs.war — the EBS Asserter Java application
- idcs-wallet-<version>.jar — wallet creation utility
- fndext.jar — EBS FND library
- README.txt — installation guide

Copy ebs.war, idcs-wallet-<version>.jar, and fndext.jar to a working directory on the EBS Asserter WebLogic server:

mkdir -p /opt/ebssdk
cd /opt/ebssdk

# Copy files from your desktop to the WebLogic server
scp ebs.war oracle@ebsasserter.example.com:/opt/ebssdk/
scp idcs-wallet-*.jar oracle@ebsasserter.example.com:/opt/ebssdk/
scp fndext.jar oracle@ebsasserter.example.com:/opt/ebssdk/

Step 2: Copy fndext.jar to WebLogic Domain

The fndext.jar library is needed for EBS API calls.

# Log in to EBS Asserter WebLogic server
ssh oracle@ebsasserter.example.com

cd /opt/ebssdk

# Copy fndext.jar to WebLogic domain lib folder
cp fndext.jar $DOMAIN_HOME/lib/

# Verify
ls -lh $DOMAIN_HOME/lib/fndext.jar

Note: Replace $DOMAIN_HOME with your WebLogic domain path, e.g., /u01/app/oracle/middleware/user_projects/domains/ebsasserter_domain

Part 2: Configure Oracle E-Business Suite

Step 3: Create EBS Asserter Service User in EBS

The EBS Asserter needs a service account to communicate with the EBS database (APPS schema).

Log in to Oracle E-Business Suite as SYSADMIN:
https://ebs.example.com/OA_HTML/AppsLogin
Navigate to: User Management → Users
Select User Account from the Register dropdown, and click Go.
In the Create User Account page, enter:
Field Value
User Name EBSASSERTER
Password <Strong_Password>
Description EBS Asserter Service Account
Password Expire None
Click Submit.
After the success message appears, click Assign Roles.
In the Assign Roles window, search by Code: UMX|APPS_SCHEMA_CONNECT
Select Apps Schema Connect Role and click Select.
Provide justification: EBS Asserter Service Account, and click Save.
Log out from EBS and log back in as EBSASSERTER to activate the account and set the password.

Field	Value
User Name	`EBSASSERTER`
Password	`<Strong_Password>`
Description	`EBS Asserter Service Account`
Password Expire	None

Note: Store the EBSASSERTER password securely — you'll need it later for the JDBC datasource configuration.

Step 4: Update SYSADMIN Email Address in EBS

IDCS matches users by email address. Ensure the SYSADMIN user has a valid email in EBS.

Log in to EBS as SYSADMIN.
Navigate to: User Management → Users
Search for User Name: SYSADMIN, and click the Update icon.
Update the Email field with a valid email address (e.g., sysadmin@example.com).
Click Apply.

Important: Use the same email address when creating the SYSADMIN user in IDCS in the next section.

Step 5: Register EBS Asserter Application Server with EBS

The EBS Asserter must be registered as a trusted application server in EBS.

Log in to the EBS database server as the oracle user.
Source the EBS environment file:
```
source /u01/install/VISION/EBSapps.env
```
Note: Replace /u01/install/VISION/EBSapps.env with your actual EBS environment file path. Find it with:
```
find /u01 -name "EBSapps.env"
```
Locate the .dbc file for your EBS instance:
```
cd $FND_SECURE
ls -lh *.dbc
```
Example output:
```
VISION_ebs.dbc
```
Note the full path including the filename:
```
echo $FND_SECURE/VISION_ebs.dbc
```
Example: /u01/install/VISION/fs1/inst/apps/VISION_ebs/appl/fnd/12.0.0/secure/VISION_ebs.dbc

Copy fndext.jar to a working directory on the EBS server:

mkdir -p /opt/ebssdk
cd /opt/ebssdk

# Copy fndext.jar from the EBS Asserter package
scp oracle@ebsasserter.example.com:/opt/ebssdk/fndext.jar .

cd /opt/ebssdk

java oracle.apps.fnd.security.AdminDesktop apps/<APPS_PASSWORD> \
  CREATE NODE_NAME=ebsasserter.example.com \
  DBC=/u01/install/VISION/fs1/inst/apps/VISION_ebs/appl/fnd/12.0.0/secure/VISION_ebs.dbc

Replace:

<APPS_PASSWORD> — your EBS APPS schema password
ebsasserter.example.com — your EBS Asserter WebLogic server hostname
DBC path — your actual .dbc file path

Expected Output:

Registering ebsasserter.example.com...
Successfully created node ebsasserter.example.com with APPL_SERVER_ID: 1234

Note the APPL_SERVER_ID — you'll need it for the bridge.properties file.

Verify the registration created a new .dbc file:

cd $FND_SECURE
ls -lh ebsasserter*.dbc

Example output:

ebsasserter.example.com_VISION.dbc

View the APPL_SERVER_ID value inside the new .dbc file:

cat ebsasserter.example.com_VISION.dbc | grep APPL_SERVER_ID

Example output:

APPL_SERVER_ID=1234

Copy the new .dbc file to the EBS Asserter WebLogic server:

scp ebsasserter.example.com_VISION.dbc oracle@ebsasserter.example.com:/opt/ebssdk/

Part 3: Register EBS Asserter in IDCS

Step 6: Create SYSADMIN User in IDCS

Create a user in IDCS that matches the SYSADMIN user in EBS.

Sign in to IDCS console:
https://<tenant-name>.identity.oraclecloud.com/ui/v1/adminconsole
Click Navigation Menu (≡), and select Users.
Click Add.
In the Add User dialog, enter:
Field Value
First Name EBS
Last Name Sysadmin
User Name sysadmin
Email sysadmin@example.com ← same email as EBS
Uncheck ☐ Use the email address as the user name
Click Finish.

Field	Value
First Name	`EBS`
Last Name	`Sysadmin`
User Name	`sysadmin`
Email	`sysadmin@example.com` ← same email as EBS
Uncheck	☐ Use the email address as the user name

Important: The email address must match exactly with the email in FND_USERS.EMAIL_ADDRESS for the SYSADMIN user in EBS.

Step 7: Register EBS Asserter as Confidential Application in IDCS

In the IDCS console, click Navigation Menu (≡), and select Applications.
Click Add.
Select Confidential Application.
In the Details tab, enter:
Field Value
Name EBS Asserter
Description EBS Asserter for SSO
Application URL https://ebsasserter.example.com/ebs
Display in My Apps ☑ Yes
Click Next.
In the Client tab:
- ☑ Configure this application as a client now
- Allowed Grant Types:
  - ☑ Client Credentials
  - ☑ Authorization Code
- Redirect URL:
  https://ebsasserter.example.com/ebs/response
- Logout URL:
  https://ebsasserter.example.com/ebs/logout
- Post Logout Redirect URL:
  https://ebsasserter.example.com/ebs
Scroll down to Grant the client access to Identity Cloud Service Admin APIs.
Click Add.
In the Add App Role dialog, select:
- ☑ Authenticator Client
- ☑ Me
Click Add.
Click Next through the remaining tabs (Resources, Web Tier Policy, Authorization, Trust).
Click Finish.
In the Application Added dialog:
- Copy and save the Client ID
  Example: a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6
- Copy and save the Client Secret
  Example: A1B2-C3D4-E5F6-G7H8-I9J0K1L2M3N4
⚠️ Important: Store these securely — you'll need them to create the wallet.
Click Close.
Click Activate to activate the application.
In the Activate Application dialog, click Activate Application.

Field	Value
Name	`EBS Asserter`
Description	`EBS Asserter for SSO`
Application URL	`https://ebsasserter.example.com/ebs`
Display in My Apps	☑ Yes

Success message: The EBS Asserter application has been activated.

Part 4: Create IDCS Wallet for EBS Asserter

The EBS Asserter stores the IDCS Client ID, Client Secret, and IDCS Base URL in an encrypted wallet for security.

Step 8: Create the Wallet

Log in to the EBS Asserter WebLogic server:
```
ssh oracle@ebsasserter.example.com
```
Navigate to the working directory:
```
cd /opt/ebssdk
```

Run the wallet creation utility:

java -jar idcs-wallet-<version>.jar

Example:

java -jar idcs-wallet-19.1.4.jar

The utility prompts for the following values:

Prompt 1: Enter Wallet Path

Enter Wallet Path: /opt/ebssdk/wallet

Prompt 2: Enter Client ID

Enter Client ID: a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6

Prompt 3: Enter Client Secret

Enter Client Secret: A1B2-C3D4-E5F6-G7H8-I9J0K1L2M3N4

Prompt 4: Enter IDCS base URL

Enter IDCS base URL: https://mycompany.identity.oraclecloud.com

Note: Use your actual IDCS tenant URL.

The wallet file cwallet.sso is created in /opt/ebssdk/wallet/.

Verify:

ls -lh /opt/ebssdk/wallet/cwallet.sso

Expected output:

-rw------- 1 oracle oinstall 2.5K Oct 14 10:30 /opt/ebssdk/wallet/cwallet.sso

Security Note: Protect the wallet file with restrictive permissions (600 or 400).

chmod 400 /opt/ebssdk/wallet/cwallet.sso

Part 5: Configure and Deploy EBS Asserter WAR File

Step 9: Update bridge.properties Configuration File

The bridge.properties file inside ebs.war contains all the EBS Asserter configuration parameters.

ssh oracle@ebsasserter.example.com
cd /opt/ebssdk

Extract the contents of ebs.war:

mkdir ebs-war-extract
cd ebs-war-extract
jar -xvf ../ebs.war

Navigate to the WEB-INF folder:
```
cd WEB-INF
```
Open bridge.properties for editing:
```
vi bridge.properties
```

Uncomment and update the following parameters:

###########################################################
## SSO Bridge for E-Business Suite
###########################################################
app.url=https://ebsasserter.example.com/ebs
app.serverid=1234
ebs.url.homepage=https://ebs.example.com/OA_HTML/OA.jsp?OAFunc=OANEWHOMEPAGE
ebs.ds.name=ebsDS
ebs.user.identifier=email
idcs.iss.url=https://identity.oraclecloud.com
idcs.aud.url=https://mycompany.identity.oraclecloud.com
wallet.path=/opt/ebssdk/wallet/cwallet.sso
whitelist.urls=/OA_HTML/RF.jsp,/OA_HTML/OA.jsp,/OA_HTML/BneApplicationService
ebs.renew.session=true
proxy.mode=false
###########################################################

Parameter Descriptions:

Parameter	Value	Description
`app.url`	`https://ebsasserter.example.com/ebs`	EBS Asserter URL
`app.serverid`	`1234`	APPL_SERVER_ID from Step 5
`ebs.url.homepage`	`https://ebs.example.com/OA_HTML/OA.jsp?OAFunc=OANEWHOMEPAGE`	EBS home page URL
`ebs.ds.name`	`ebsDS`	JDBC DataSource name (you'll create this in WebLogic)
`ebs.user.identifier`	`email`	Match users by email (recommended)
`idcs.iss.url`	`https://identity.oraclecloud.com`	IDCS issuer URL (fixed)
`idcs.aud.url`	`https://mycompany.identity.oraclecloud.com`	Your IDCS tenant URL
`wallet.path`	`/opt/ebssdk/wallet/cwallet.sso`	Full path to wallet
`whitelist.urls`	`/OA_HTML/RF.jsp,/OA_HTML/OA.jsp,...`	Allowed EBS URLs
`ebs.renew.session`	`true`	Auto-renew EBS Forms session

Save and exit.

Rebuild the ebs.war file with the updated bridge.properties:

cd /opt/ebssdk/ebs-war-extract
jar -cvf ../ebs-updated.war *

Verify the updated WAR file was created:
```
ls -lh /opt/ebssdk/ebs-updated.war
```

Step 10: Create JDBC DataSource in WebLogic for EBS Database

The EBS Asserter needs a JDBC connection to the EBS database (APPS schema).

Log in to the WebLogic Server Administration Console:
http://ebsasserter.example.com:7001/console
Credentials: WebLogic admin username/password
In the left Domain Structure panel, expand Services, and click Data Sources.
Click New → Generic Data Source.
Enter the following:
Field Value
Name ebsDS ← same as ebs.ds.name in bridge.properties
JNDI Name jdbc/ebsDS
Database Type Oracle
Click Next.
Select Oracle's Driver (Thin) for Instance connections; Versions:Any and click Next.
Uncheck Supports Global Transactions and click Next.

Field	Value
Name	`ebsDS` ← same as `ebs.ds.name` in bridge.properties
JNDI Name	`jdbc/ebsDS`
Database Type	`Oracle`

Enter the EBS database connection details:

Field	Value
Database Name	`VISION` ← your EBS DB SID
Host Name	`ebs-db.example.com` ← EBS DB server
Port	`1521`
Database User Name	`EBSASSERTER` ← service user from Step 3
Password	`<EBSASSERTER_password>`
Confirm Password	`<EBSASSERTER_password>`

Click Next.
Click Test Configuration.
Expected output:
```
Connection test succeeded.
```
✅ If successful, click Next.
❌ If it fails, verify:
- EBS database is running
- Firewall allows port 1521 from WebLogic to EBS DB
- EBSASSERTER user credentials are correct
- tnsnames.ora is not required for Thin driver
Select the EBSAsserter_server managed server (or your target managed server), and click Finish.
Click Activate Changes.

Step 11: Deploy ebs.war to WebLogic

In the WebLogic console, click Deployments in the left panel.
Click Install.
Click Upload your file(s).
Click Browse and select /opt/ebssdk/ebs-updated.war.
Click Next.
Select Install this deployment as an application, and click Next.
Select the EBSAsserter_server managed server as the target, and click Next.
Click Finish.
Click Activate Changes.
In the Deployments page, select the ebs-updated application.
Click Start → Servicing all requests.
Confirm the State column shows Active.

Step 12: Verify EBS Asserter Deployment

Test that the EBS Asserter is running:

curl -k https://ebsasserter.example.com/ebs

Expected output: You should be redirected to IDCS login page or see an HTML response (not a 404 error).

If you get a 404, check:

# Check WebLogic managed server logs
tail -f $DOMAIN_HOME/servers/EBSAsserter_server/logs/EBSAsserter_server.log

# Look for deployment errors
grep -i "error\|exception" $DOMAIN_HOME/servers/EBSAsserter_server/logs/EBSAsserter_server.log

Part 6: Integrate Microsoft Active Directory with IDCS

Now that EBS and IDCS are configured, let's add Active Directory so users can log in with their corporate AD credentials.

You have two options:

AD Federation via ADFS (SAML 2.0) — users log in through ADFS
AD Bridge — direct user sync from AD to IDCS

Recommended: Use ADFS if you already have it deployed. Use AD Bridge if you want automated user provisioning.

Option A: Federate IDCS with Active Directory Federation Services (ADFS)

This section covers SAML 2.0 federation between IDCS and Microsoft ADFS.

Step 13: Download ADFS Metadata

Access your ADFS Federation Metadata URL:
https://adfs.example.com/FederationMetadata/2007-06/FederationMetadata.xml
Replace adfs.example.com with your actual ADFS server hostname.
Save the file as FederationMetadata.xml.

Step 14: Register ADFS as Identity Provider in IDCS

Sign in to IDCS console.
Click Navigation Menu (≡) → Security → Identity Providers.
Click Add SAML IDP.
Enter:
Field Value
Name Active Directory Federation
Description Corporate AD SAML SSO
Click Next.
Select Import Identity Provider metadata.
Click Upload, select FederationMetadata.xml, and click Open.
Click Next.
Configure the User Mapping:
Field Value
Oracle Identity Cloud Service User Attribute Primary Email Address
Requested NameID Format Email Address
Click Next.
Click Finish.
Do NOT activate yet — we need to complete the ADFS side first.

Field	Value
Name	`Active Directory Federation`
Description	`Corporate AD SAML SSO`

Field	Value
Oracle Identity Cloud Service User Attribute	`Primary Email Address`
Requested NameID Format	`Email Address`

Step 15: Download IDCS Metadata for ADFS

In the IDCS console, click Navigation Menu (≡) → Settings → Default Settings.
Turn ON the Access Signing Certificate option.
Click Save.
Download the IDCS SAML Metadata:
https://<tenant-name>.identity.oraclecloud.com/fed/v1/metadata
Example:
https://mycompany.identity.oraclecloud.com/fed/v1/metadata
Save the file as IDCS_Metadata.xml.
Copy the file to your ADFS server (Windows Server).

Step 16: Register IDCS as Relying Party in ADFS

Log in to the Windows Server where ADFS is installed.
Open Server Manager → Tools → AD FS Management.
In the AD FS console, expand Trust Relationships, right-click Relying Party Trusts, and select Add Relying Party Trust.
Click Start.
Select Import data about the relying party from a file, and browse to IDCS_Metadata.xml.
Click Next.
Enter Display name: Oracle Identity Cloud Service
Click Next.
Select Permit all users to access this relying party.
Click Next → Next → Close.

Step 17: Configure Claim Rules in ADFS

ADFS needs to send the user's email address as the NameID claim.

In the AD FS Management console, right-click Oracle Identity Cloud Service relying party, and select Edit Claim Issuance Policy.
Click Add Rule.
Select Send LDAP Attributes as Claims and click Next.
Enter:
Field Value
Claim rule name Email
Attribute store Active Directory
LDAP Attribute E-Mail-Addresses
Outgoing Claim Type E-Mail Address
Click Finish.
Click Add Rule again.
Select Transform an Incoming Claim and click Next.
Enter:
Field Value
Claim rule name Name ID
Incoming claim type E-Mail Address
Outgoing claim type Name ID
Outgoing name ID format Email
Click Finish → OK.

Field	Value
Claim rule name	`Email`
Attribute store	`Active Directory`
LDAP Attribute	`E-Mail-Addresses`
Outgoing Claim Type	`E-Mail Address`

Field	Value
Claim rule name	`Name ID`
Incoming claim type	`E-Mail Address`
Outgoing claim type	`Name ID`
Outgoing name ID format	`Email`

Step 18: Test and Activate ADFS Identity Provider in IDCS

Return to IDCS console → Security → Identity Providers.
Click the Active Directory Federation IdP.
Click Test Login.
You are redirected to ADFS login page.
Enter your Active Directory credentials (e.g., csaladna@example.com).
If authentication succeeds, you'll see: Connection Successful.
✅ If successful:
- Click Close
- Click Activate
- Click Show on Login Page
❌ If it fails:
- Click Show Assertion Details to see the SAML error
- Verify claim rules are correct in ADFS
- Check ADFS event logs (Server Manager → AD FS → Events)
Add the IdP to the Default Identity Provider Policy:
- Navigate to Security → IDP Policies
- Click Default Identity Provider Policy
- Click the Identity Providers tab
- Click Assign
- Select Active Directory Federation and click OK

Done! ADFS is now federated with IDCS.

Option B: Configure AD Bridge for Direct User Sync

If you don't have ADFS, use the IDCS AD Bridge to sync users directly from Active Directory to IDCS.

Step 19: Download and Install AD Bridge

In the IDCS console, click Navigation Menu (≡) → Settings → Directory Integrations.
Click Add.
Select Microsoft Active Directory.
Enter:
Field Value
Name Corporate AD Bridge
Description User sync from on-premises AD
Click Download to download the AD Bridge installer (IDCSIntegration.zip).
Copy the zip file to a Windows Server that has network access to your Active Directory Domain Controller.
Extract the zip file and run the installer:
```
IDCSIntegration.exe
```
Follow the wizard:
- Accept the license
- Select installation directory (e.g., C:\Oracle\IDCSBridge)
- Enter IDCS Tenant URL: https://mycompany.identity.oraclecloud.com
- Enter IDCS Admin Username and Password
- Click Install
After installation completes, the IDCS Bridge Configuration tool opens.

Field	Value
Name	`Corporate AD Bridge`
Description	`User sync from on-premises AD`

Step 20: Configure AD Bridge Synchronization

In the IDCS Bridge Configuration tool, click Configure.
Enter the Active Directory connection details:
Field Value
AD Server ad.example.com
Port 389 (LDAP) or 636 (LDAPS — recommended)
Base DN DC=example,DC=com
Bind DN CN=IDCSSync,CN=Users,DC=example,DC=com
Password <service_account_password>
Note: Create a dedicated AD service account (IDCSSync) with Read permissions on user objects.
Click Test Connection.
✅ Expected: "Connection successful"
Click Next.
Select the Organizational Units (OUs) to sync:
- ☑ OU=Employees,DC=example,DC=com
- ☑ OU=Contractors,DC=example,DC=com
Click Next.
Configure Attribute Mapping:
AD Attribute IDCS Attribute
sAMAccountName userName
mail email
givenName firstName
sn lastName
Click Next.
Select Synchronization Schedule:
- ☑ Enable scheduled sync
- Frequency: Every 1 hour
Click Finish.
Click Run Sync Now to perform the initial synchronization.
Monitor the sync progress in the Logs tab.

Field	Value
AD Server	`ad.example.com`
Port	`389` (LDAP) or `636` (LDAPS — recommended)
Base DN	`DC=example,DC=com`
Bind DN	`CN=IDCSSync,CN=Users,DC=example,DC=com`
Password	`<service_account_password>`

AD Attribute	IDCS Attribute
`sAMAccountName`	`userName`
`mail`	`email`
`givenName`	`firstName`
`sn`	`lastName`

Verification:

In the IDCS console, click Users.
You should see users from Active Directory synced to IDCS.

Part 7: Test the Complete SSO Flow

Step 21: Assign EBS Asserter Application to Test User

In IDCS console, click Applications.
Click EBS Asserter.
Click the Application Users tab.
Click Assign.
Search for a test user (e.g., sysadmin or an AD-synced user).
Select the user and click OK.

Open a new private/incognito browser window.
Access the EBS Asserter URL:
https://ebsasserter.example.com/ebs
You are redirected to the IDCS login page.
If ADFS federation is enabled, you'll see:
- Sign in with Oracle Identity Cloud Service (IDCS native login)
- Sign in with Active Directory Federation (ADFS login)
Click Sign in with Active Directory Federation.
You are redirected to ADFS login page.
Enter your Active Directory credentials (e.g., csaladna@example.com).
After successful authentication, you are redirected back to IDCS.
IDCS validates the SAML assertion and redirects to EBS Asserter.
EBS Asserter creates an EBS session cookie and redirects to Oracle E-Business Suite.
Oracle EBS Home page opens — you are logged in!

✅ Success! SSO is working end-to-end:

User → EBS Asserter → IDCS → ADFS → Active Directory → IDCS → EBS Asserter → EBS

Step 23: Test Logout

In Oracle EBS, click Logout.
You are redirected to EBS Asserter logout → IDCS logout.
IDCS clears the session and redirects to the IDCS login page.
Try accessing EBS again — you are prompted to log in (session is cleared).

✅ Logout works correctly.

Troubleshooting — Common Errors and Fixes

⚠️ Issue 1: "Connection to IDCS failed" in EBS Asserter

Root Cause: Wallet path is incorrect or wallet file is missing.

Fix:

Check the bridge.properties wallet path:

cd /opt/ebssdk/ebs-war-extract/WEB-INF
grep wallet.path bridge.properties

Verify the wallet file exists:

ls -lh /opt/ebssdk/wallet/cwallet.sso

If missing, recreate the wallet (Step 8).

⚠️ Issue 2: "JDBC DataSource not found" Error

Root Cause: The ebs.ds.name in bridge.properties does not match the JNDI name in WebLogic.

Fix:

Check the DataSource JNDI name in WebLogic console: Services → Data Sources → ebsDS.
Update bridge.properties:
```
ebs.ds.name=ebsDS
```
Rebuild ebs.war and redeploy (Steps 9–11).

Root Cause: Email address mismatch between IDCS and EBS.

Fix:

Check the user's email in IDCS:
- IDCS console → Users → search for the user
- Note the Email field

Check the user's email in EBS:

SQL> SELECT user_name, email_address 
     FROM fnd_users 
     WHERE user_name = 'SYSADMIN';

Ensure they match exactly (case-insensitive).

If they don't match, update EBS:

SQL> UPDATE fnd_users 
     SET email_address = 'sysadmin@example.com' 
     WHERE user_name = 'SYSADMIN';
SQL> COMMIT;

⚠️ Issue 4: ADFS SAML Assertion Fails ("Connection Failed" in IDCS)

Root Cause: SAML claim rules are incorrect in ADFS.

Fix:

In IDCS, click the failed test and click Show Assertion Details.
Look for the <samlp:Status> error message.

Common errors:

Error	Cause	Fix
`urn:oasis:names:tc:SAML:2.0:status:Requester`	Missing NameID claim	Verify Name ID transform claim rule in ADFS
`No email attribute`	Email claim not sent	Verify Email LDAP attribute claim rule in ADFS
`Time mismatch`	Server clocks out of sync	Sync clocks with NTP on ADFS and IDCS

Fix the claim rules in ADFS (Step 17) and retest.

⚠️ Issue 5: "Internal Server Error" in EBS Asserter

Root Cause: Missing or incorrect APPL_SERVER_ID in bridge.properties.

Fix:

Check the .dbc file for the correct APPL_SERVER_ID:

cat /opt/ebssdk/ebsasserter.example.com_VISION.dbc | grep APPL_SERVER_ID

Update bridge.properties:
```
app.serverid=1234
```
Rebuild ebs.war and redeploy.

⚠️ Issue 6: EBS Asserter and EBS Not in Same Domain

Symptom: SSO login succeeds, but EBS shows the login page again (no session cookie).

Root Cause: Cookies cannot be shared across different domains.

Fix:

Ensure both hostnames are in the same domain:

✅ Correct:

EBS Asserter: ebsasserter.example.com
EBS: ebs.example.com

❌ Wrong:

EBS Asserter: ebsasserter.domain1.com
EBS: ebs.domain2.com

If they're in different domains, you must reconfigure DNS or use a reverse proxy.

⚠️ Issue 7: AD Bridge Sync Fails

Root Cause: Insufficient AD permissions for the sync service account.

Fix:

In Active Directory Users and Computers, right-click the Base DN (e.g., DC=example,DC=com).
Select Properties → Security → Advanced.
Click Add and select the IDCSSync service account.
Grant the following permissions:
- ☑ Read all properties
- ☑ Read permissions
- ☑ List contents
Click OK and retry the sync.

Best Practices — Production Deployment

1. Use SSL/TLS Everywhere

All communication must be encrypted:

EBS Asserter WebLogic → HTTPS only
EBS Application → HTTPS only
ADFS → HTTPS only

Use valid SSL certificates signed by a trusted CA (not self-signed).

2. Enable MFA in IDCS

Add an extra layer of security:

In IDCS console, navigate to Security → MFA.
Enable Time-Based One-Time Password (TOTP) or SMS.
Assign MFA enrollment to users.

3. Configure Sign-On Policies

Restrict access based on IP, device, or risk:

Navigate to Security → Sign-On Policies.
Create a policy for EBS Asserter application.
Add rules:
- Require MFA if user is outside corporate network
- Block sign-in from blacklisted countries

4. Monitor IDCS Audit Logs

Track authentication events:

Navigate to Reports → Audit Reports.
Export logs to a SIEM (e.g., Splunk, Oracle Management Cloud).

5. High Availability for EBS Asserter

Deploy multiple EBS Asserter instances behind a load balancer:

Load Balancer (F5, OHS, OCI Load Balancer) → ebsasserter.example.com
EBS Asserter 1 → WebLogic Managed Server 1
EBS Asserter 2 → WebLogic Managed Server 2

Session affinity (sticky sessions) is not required — IDCS handles session state.

6. Backup the Wallet

The wallet contains the IDCS credentials. Back it up securely:

cp /opt/ebssdk/wallet/cwallet.sso /backup/cwallet.sso.$(date +%Y%m%d)
chmod 400 /backup/cwallet.sso.*

7. Test Regularly

Schedule quarterly SSO testing:

Test login with IDCS native users
Test login with AD-federated users
Test logout flow
Test MFA enrollment and authentication
Verify AD Bridge sync is running

8. Keep EBS Asserter Updated

Oracle releases new versions of the EBS Asserter with bug fixes and new features. Check for updates quarterly in IDCS console → Settings → Downloads.

9. Document Your Configuration

Create a runbook that includes:

EBS Asserter server details (hostname, IP, WebLogic version)
IDCS tenant URL and Client ID
ADFS server details
Firewall rules
DNS entries
Service account credentials (stored securely)

Summary

Congratulations! You've successfully integrated Oracle Identity Cloud Service (IDCS) with Oracle E-Business Suite and Active Directory for Single Sign-On. Here's what you accomplished:

Downloaded and prepared the EBS Asserter package
Created the EBS Asserter service account in EBS
Registered the EBS Asserter application server with EBS
Registered EBS Asserter as a confidential application in IDCS
Created an encrypted wallet for IDCS credentials
Configured and deployed the EBS Asserter WAR file to WebLogic
Federated IDCS with Active Directory using ADFS or AD Bridge
Tested the complete SSO flow — EBS → IDCS → AD → EBS
Troubleshot common errors and verified production readiness

Users can now log in to Oracle E-Business Suite using their corporate Active Directory credentials through a simple, cloud-managed SSO solution — no OAM or OID required.

This integration gives you:

Simplified architecture — fewer moving parts
Centralized identity management — one place to manage users
Enhanced security — MFA, adaptive policies, audit logs
Cloud-native scalability — IDCS handles high availability
Hybrid flexibility — works with EBS on-premises or in OCI

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs
If you like this post please follow, share and comment

FAQ

Q1: Can I use IDCS with EBS 11i?
Yes, EBS Asserter supports Oracle E-Business Suite Release 11i (11.5.10) with the latest patches applied. However, Oracle recommends upgrading to EBS 12.2 for the best experience.

Q2: Do I need to remove my existing OAM/OID integration before using IDCS?
Yes. EBS cannot be integrated with both OAM/OID and IDCS simultaneously. You must remove the OAM/OID integration, restart the EBS application tier, and then proceed with IDCS integration.

Q3: Can I use IDCS with EBS running on OCI?
Absolutely. IDCS works seamlessly with EBS deployed on Oracle Cloud Infrastructure (OCI), including EBS Cloud Manager provisioned instances.

Q4: What if my users have different usernames in AD and EBS?
Use email address as the unique identifier (ebs.user.identifier=email in bridge.properties). Ensure every user has a valid, matching email in both AD and EBS (FND_USERS.EMAIL_ADDRESS).

Q5: Can I enforce MFA for only certain users?
Yes. In IDCS, create a Sign-On Policy specific to the EBS Asserter application. Add rules to require MFA for specific groups (e.g., EBS_Admins) or network conditions (e.g., outside corporate IP range).

Q6: How do I upgrade the EBS Asserter to a new version?
Download the latest EBS Asserter from IDCS console → Settings → Downloads. Extract the new ebs.war, update bridge.properties with your existing configuration, rebuild the WAR, and redeploy it to WebLogic. No EBS downtime is required.

Q7: Can I use Google Workspace or Okta instead of Active Directory?
Yes. IDCS supports SAML 2.0 federation with any SAML-compliant IdP, including Google Workspace, Okta, Azure AD, Ping Identity, and others. The integration steps are similar to ADFS (download metadata, register as IdP, configure claim mapping).

Step by Step Upgrade Oracle Database 19c to 23ai Using AutoUpgrade

noreply@blogger.com (Himanshu) — Wed, 14 Oct 2026 10:56:36 +0530

Step by Step Upgrade Oracle Database 19c to 23ai Using AutoUpgrade
Oracle Database 23ai is the latest long-term support release packed with groundbreaking features like AI Vector Search, JSON Relational Duality, True Cache, Schema Privileges, and hundreds of developer productivity enhancements. If you're running Oracle Database 19c (and most of you are), now is the time to plan your upgrade.
Oracle E-Business Suite 12.2 was officially certified with Database 23ai in February 2026, starting with on-premises Exadata systems running Exadata Software 24.1 or later. The direct upgrade path exists only from 19c and 21c to 23ai — and the AutoUpgrade utility is now the only supported method. The old Database Upgrade Assistant (DBUA) has been desupported for 23ai upgrades.
In this post, I'll walk you through the complete upgrade process from Oracle Database 19c to 23ai using AutoUpgrade — covering planning, prerequisites, step-by-step execution, post-upgrade validation, and troubleshooting.
What is Oracle Database 23ai?
Oracle Database 23ai (the "ai" stands for Artificial Intelligence) is the successor to Oracle Database 19c and introduces over 300 new features focused on:
AI Vector Search — native vector data type and similarity search for generative AI and RAG (Retrieval-Augmented Generation) applications
JSON Relational Duality — use the same data as relational tables or JSON documents interchangeably
Schema-level Privileges — grant privileges on all objects in a schema with one command
True Cache — a consistent, read-only cache for your database
Developer Productivity — SQL domain types, direct joins to external tables, boolean data type, annotations, and more
Oracle Database 23ai Release Update 23.5 is the first production-ready version for on-premises deployments.
Why Upgrade from 19c to 23ai?
1) 19c Error Correction Support is ending
Oracle Database 19c entered Extended Support in April 2024. While it remains supported until 2027, you won't get error correction support for much longer if you're still on older RUs.
2) AI and Modern App Development
If you're building modern apps with AI, machine learning, or working with JSON-heavy workloads, 23ai is a must. The native vector search capability alone is game-changing.
3) EBS 12.2 Certification
If you run Oracle E-Business Suite 12.2, Oracle has certified it with 23ai on Exadata (and other platforms are coming). This opens the door to leverage AI features in your ERP environment.
4) Direct Upgrade Path
23ai only allows direct upgrades from 19c and 21c. If you're on 12c or 11g, you must first upgrade to 19c, then to 23ai. Don't delay.
What is AutoUpgrade?
AutoUpgrade is Oracle's recommended and only supported upgrade utility for Oracle Database 23ai. It automates the entire upgrade workflow:
Pre-upgrade analysis — identifies fixable issues and blockers
Automated fixups — applies fixes before upgrade
Upgrade execution — runs catupgrd.sql and all necessary scripts
Post-upgrade tasks — recompiles invalid objects, gathers dictionary stats, runs utlrp
Guaranteed Restore Point (GRP) support — enables flashback if something goes wrong
Multitenant support — upgrades CDBs and PDBs, or migrates non-CDB to PDB
AutoUpgrade runs in four modes: Analyze, Fixup, Deploy, and Upgrade. You can run all modes in sequence or separately.
The Database Upgrade Assistant (DBUA) that was used for 11g, 12c, and 19c upgrades is desupported for 23ai.
Planning Your Upgrade — Checklist
Before you touch any database, complete this planning checklist:
1.1 Verify Current Environment
Check your current Oracle Database version:
SQL> SELECT banner FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Check if it's a non-CDB or CDB:
SQL> SELECT name, cdb, con_id FROM v$database; NAME CDB CON_ID --------- --- ---------- PRODDB NO 0
Important: 23ai requires the multitenant architecture (CDB/PDB). If you have a non-CDB, AutoUpgrade can upgrade and convert it to a PDB in a single operation.
1.2 Review MOS Notes
MOS Note 2485457.1 — Download AutoUpgrade (latest version)
MOS Note 742060.1 — Release Schedule of Current Database Releases
MOS Note 2880697.1 — Database Upgrade Path Reference List (19c to 23ai)
MOS Note 2118136.2 — Oracle Database 23ai Known Issues
1.3 Check Certified Platforms
As of February 2026, Oracle Database 23ai is certified on:
Linux x86-64 (Oracle Linux 7, 8, 9 / RHEL 7, 8, 9)
Oracle Exadata Database Machine (Exadata Software 24.1 or later)
Oracle Cloud Infrastructure (OCI) — Base Database Service, Exadata Database Service
Other platforms (Solaris, AIX, Windows) are pending certification. Check MOS Note 742060.1 for the latest.
1.4 Backup Strategy
Mandatory backups before upgrade:
Full RMAN backup of your 19c database
Export of critical schemas using Data Pump
Backup of configuration files: spfile, tnsnames.ora, listener.ora, password file
Optional: Enable Guaranteed Restore Point (GRP) for instant flashback
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; RMAN> BACKUP SPFILE; RMAN> LIST BACKUP SUMMARY;
1.5 Downtime Estimation
For a 500 GB production database on decent hardware:
Analyze mode: 10–20 minutes
Fixup mode: 5–15 minutes
Upgrade (Deploy mode): 1.5–3 hours (depends on object count, parallelism, and I/O speed)
Post-upgrade tasks: 30–60 minutes
Total downtime: 2.5–4.5 hours for a typical mid-size database.
Use parallelism (target_pdb_copy_option=8 in config) to reduce upgrade time.
1.6 Rollback Plan
AutoUpgrade supports Guaranteed Restore Point (GRP) for instant flashback if the upgrade fails:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE; SQL> ALTER DATABASE OPEN RESETLOGS;
Alternatively, restore from RMAN backup.
Prerequisites — What You Need Before Starting
Step 1: Download Oracle Database 23ai Software
Download Oracle Database 23ai from:
https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
For production, download Oracle Database 23ai Release Update 23.5 or later.
Extract the software:
mkdir -p /u01/app/oracle/product/23.0.0/dbhome_1 cd /u01/app/oracle/product/23.0.0/dbhome_1 unzip -q /stage/LINUX.X64_23ai_db_home.zip
Step 2: Download Latest AutoUpgrade Utility
AutoUpgrade is not part of the 23ai installation. Download it separately from MOS Note 2485457.1 or oracle.com:
cd /home/oracle wget https://updates.oracle.com/Orion/Services/download/p2485457_<platform>_<version>.zip unzip autoupgrade.jar.zip
Verify AutoUpgrade version:
java -jar autoupgrade.jar -version
Always use the latest AutoUpgrade version (as of 2026, version 25.x is current).
Step 3: Install Oracle Database 23ai Software (Software-Only)
Run the installer in software-only mode (do NOT create a database):
cd /u01/app/oracle/product/23.0.0/dbhome_1 ./runInstaller -silent \ -responseFile /u01/app/oracle/product/23.0.0/dbhome_1/install/response/db_install.rsp \ oracle.install.option=INSTALL_DB_SWONLY \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1 \ ORACLE_BASE=/u01/app/oracle \ oracle.install.db.InstallEdition=EE
Run root scripts:
sudo /u01/app/oraInventory/orainstRoot.sh sudo /u01/app/oracle/product/23.0.0/dbhome_1/root.sh
Step 4: Apply Latest 23ai Release Update (Optional but Recommended)
Download the latest 23ai Release Update (RU) from MOS and apply it using OPatch:
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1 cd /stage/35643107 $ORACLE_HOME/OPatch/opatch apply
Verify patch:
$ORACLE_HOME/OPatch/opatch lspatches
Step 5: Set Environment Variables
Set your environment to point to the 19c ORACLE_HOME (source):
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID=PRODDB
Step 1: Create AutoUpgrade Configuration File
AutoUpgrade uses a configuration file that defines source and target homes, database details, and upgrade options.
Create a config file /home/oracle/autoupgrade_config.cfg:
# # AutoUpgrade Configuration File for 19c to 23ai Upgrade # global.autoupg_log_dir=/home/oracle/autoupgrade_logs upg1.dbname=PRODDB upg1.start_time=NOW upg1.source_home=/u01/app/oracle/product/19.0.0/dbhome_1 upg1.target_home=/u01/app/oracle/product/23.0.0/dbhome_1 upg1.sid=PRODDB upg1.log_dir=/home/oracle/autoupgrade_logs upg1.upgrade_node=prodserver upg1.target_version=23.5 upg1.restoration=no upg1.timezone_upg=yes upg1.run_utlrp=yes upg1.target_pdb_copy_option=8
Key Parameters Explained:
Parameter Description
global.autoupg_log_dir Directory for AutoUpgrade logs
upg1.dbname Database name (from v$database)
upg1.source_home Current 19c ORACLE_HOME
upg1.target_home New 23ai ORACLE_HOME
upg1.sid Oracle SID
upg1.target_version Target DB version (23.5 = 23ai RU 23.5)
upg1.restoration Enable GRP for flashback (yes or no)
upg1.timezone_upg Upgrade timezone file (yes recommended)
upg1.run_utlrp Recompile invalid objects post-upgrade
upg1.target_pdb_copy_option Parallelism (use 4–8 for faster upgrade)
For non-CDB to PDB conversion, add:
upg1.target_cdb=CDB23AI upg1.target_pdb_name=PRODPDB upg1.target_pdb_copy_option=8
Step 2: Run AutoUpgrade in Analyze Mode
Analyze mode scans your 19c database and reports upgrade readiness, fixable issues, and blockers.
Run AutoUpgrade:
java -jar autoupgrade.jar -config /home/oracle/autoupgrade_config.cfg -mode analyze
Expected Output:
Autoupgrade tool launched with default options +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be analyzed Type 'help' to list console commands upg> Job 100 for PRODDB completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished successfully [1] Jobs failed [0] Jobs pending [0] Please check the summary report at: /home/oracle/autoupgrade_logs/cfgtoollogs/upgrade/auto/status/status.html /home/oracle/autoupgrade_logs/PRODDB/100/autoupgrade_20261014_user.log upg>
Review the HTML report:
firefox /home/oracle/autoupgrade_logs/cfgtoollogs/upgrade/auto/status/status.html
Common Issues Flagged:
Issue Severity Auto-Fixable?
Deprecated parameters (optimizer_mode=RULE) WARNING Yes
Invalid objects in SYS/SYSTEM WARNING Yes (via utlrp)
Timezone file outdated WARNING Yes (if timezone_upg=yes)
Underscore parameters (_optimizer_...) INFO No (review manually)
SQL Plan Baselines INFO No action needed
Pluggable databases compatibility ERROR No (manual fix required)
If any ERROR appears, you must fix it manually before proceeding.
Step 3: Run AutoUpgrade in Fixup Mode (Optional)
Fixup mode applies automatic fixes for issues flagged in Analyze mode.
java -jar autoupgrade.jar -config /home/oracle/autoupgrade_config.cfg -mode fixups
AutoUpgrade will:
Remove deprecated parameters from spfile
Update timezone file
Set recommended parameters for 23ai
Gather dictionary statistics
Expected Output:
Fixup mode finished successfully for PRODDB Check fixup log: /home/oracle/autoupgrade_logs/PRODDB/101/autoupgrade_fixup.log
Step 4: Enable Guaranteed Restore Point (GRP) — Optional but Recommended
If you want instant rollback capability, enable a Guaranteed Restore Point before upgrade:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE FLASHBACK ON; SQL> ALTER DATABASE OPEN; SQL> CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE; SQL> SELECT name, scn, time, guarantee_flashback_database FROM v$restore_point; NAME SCN TIME GUARANTEE_FLASHBACK_DATABASE ----------------- ------------------ ------------------- ---------------------------- BEFORE_UPGRADE 3894756123 14-OCT-26 10:30:15 YES
Warning: GRP requires sufficient space in the Fast Recovery Area (FRA) to store flashback logs. Monitor space during upgrade.
Step 5: Run AutoUpgrade in Deploy Mode (Full Upgrade)
Deploy mode executes the complete upgrade: fixups, upgrade scripts (catupgrd.sql), timezone upgrade, recompilation, and post-upgrade checks.
Set environment to 19c home:
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 export ORACLE_SID=PRODDB
Run AutoUpgrade:
java -jar autoupgrade.jar -config /home/oracle/autoupgrade_config.cfg -mode deploy
What Happens During Deploy:
Pre-upgrade Fixups — applies all automatic fixes
Shutdown 19c database
Startup database from 23ai home in UPGRADE mode
Run catupgrd.sql — the main upgrade script (this takes the longest)
Timezone upgrade (if enabled)
Run utlrp.sql — recompile invalid objects
Gather dictionary stats
Post-upgrade validation
Restart database in NORMAL mode from 23ai home
Expected Output:
+--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ Autoupgrade tool launched with default options Processing config file ... 1 database(s) will be upgraded Type 'help' to list console commands upg> lsj +----+-------+---------+---------+-------+----------+-------+ | Job|DB_NAME| STAGE |OPERATION| STATUS|START_TIME|UPDATED| +----+-------+---------+---------+-------+----------+-------+ | 102| PRODDB|PREFIXUPS|EXECUTING|RUNNING| 26/10/14 |1m ago | +----+-------+---------+---------+-------+----------+-------+ upg> lsj +----+-------+---------+---------+-------+----------+-------+ | Job|DB_NAME| STAGE |OPERATION| STATUS|START_TIME|UPDATED| +----+-------+---------+---------+-------+----------+-------+ | 102| PRODDB|DBUPGRADE|EXECUTING|RUNNING| 26/10/14 |15m ago| +----+-------+---------+---------+-------+----------+-------+ upg> lsj +----+-------+---------+---------+-------+----------+-------+ | Job|DB_NAME| STAGE |OPERATION| STATUS|START_TIME|UPDATED| +----+-------+---------+---------+-------+----------+-------+ | 102| PRODDB|POSTUPGRADE|EXECUTING|RUNNING| 26/10/14|2m ago| +----+-------+---------+---------+-------+----------+-------+ upg> lsj +----+-------+---------+---------+-------+----------+-------+ | Job|DB_NAME| STAGE |OPERATION| STATUS|START_TIME|UPDATED| +----+-------+---------+---------+-------+----------+-------+ | 102| PRODDB|COMPLETED|STOPPED |SUCCESS| 26/10/14 |1m ago | +----+-------+---------+---------+-------+----------+-------+ Job 102 completed Upgrade Summary Report located at: /home/oracle/autoupgrade_logs/cfgtoollogs/upgrade/auto/status/status.html
Upgrade typically takes 1.5–3 hours depending on database size, object count, and hardware.
Step 6: Post-Upgrade Validation and Verification
Once AutoUpgrade completes successfully, verify the upgrade.
6.1 Check Database Version
SQL> SELECT banner FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
SQL> SELECT version, version_full FROM v$instance; VERSION VERSION_FULL ---------- ------------------------------ 23.0.0.0.0 23.5.0.24.07
6.2 Check Database Status
SQL> SELECT name, open_mode, cdb, version FROM v$database; NAME OPEN_MODE CDB VERSION --------- ---------- --- -------------- PRODDB READ WRITE YES 23.0.0.0.0
6.3 Check for Invalid Objects
SQL> SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type ORDER BY 1, 2;
If any invalid objects exist, recompile them:
SQL> @?/rdbms/admin/utlrp.sql
6.4 Verify Registry Components
SQL> SELECT comp_name, version, status FROM dba_registry ORDER BY comp_name; COMP_NAME VERSION STATUS ---------------------------------------- ---------- ----------- Oracle Database Catalog Views 23.0.0.0.0 VALID Oracle Database Packages and Types 23.0.0.0.0 VALID Oracle JVM 23.0.0.0.0 VALID Oracle Real Application Clusters 23.0.0.0.0 VALID Oracle Text 23.0.0.0.0 VALID Oracle Workspace Manager 23.0.0.0.0 VALID Oracle XML Database 23.0.0.0.0 VALID
All components should show VALID status.
6.5 Check Timezone Version
SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID ----------------------- -------- ------- timezlrg_46.dat 46 0
Oracle 23ai ships with timezone version 46 (or later).
6.6 Verify Upgraded Features
Test new 23ai features to confirm they work:
Test Boolean Data Type:
SQL> CREATE TABLE test_23ai (id NUMBER, is_active BOOLEAN); Table created. SQL> INSERT INTO test_23ai VALUES (1, TRUE); 1 row created. SQL> SELECT * FROM test_23ai; ID IS_ACTIVE ---------- ---------- 1 TRUE
Test Schema Privileges (New in 23ai):
SQL> CREATE USER testuser IDENTIFIED BY Oracle123; SQL> GRANT CREATE SESSION TO testuser; SQL> GRANT SELECT ANY TABLE ON SCHEMA hr TO testuser; -- testuser can now SELECT from all HR schema tables
6.7 Review AutoUpgrade Summary Report
Open the HTML summary report:
firefox /home/oracle/autoupgrade_logs/cfgtoollogs/upgrade/auto/status/status.html
Check:
Upgrade duration
Number of errors/warnings
Final status (SUCCESS)
Step 7: Update Environment and Listener
7.1 Update oratab
Edit /etc/oratab to point to the new 23ai home:
Before:
PRODDB:/u01/app/oracle/product/19.0.0/dbhome_1:N
After:
PRODDB:/u01/app/oracle/product/23.0.0/dbhome_1:N
7.2 Update Listener
Stop the listener running from 19c home:
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 lsnrctl stop
Start the listener from 23ai home:
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH lsnrctl start
Verify listener status:
lsnrctl status
7.3 Update Profile / .bashrc
Update your .bashrc or .bash_profile:
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
Reload:
source ~/.bashrc
Step 8: Gather Database Statistics (Recommended)
After upgrade, gather fresh database statistics for optimal performance:
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 8, cascade => TRUE );
This can take 30–60 minutes for a large database. Run it during a maintenance window.
Step 9: Drop Guaranteed Restore Point (If Enabled)
If you created a GRP before upgrade and the upgrade is successful, drop the restore point to free up space in the FRA:
SQL> SELECT name, scn, guarantee_flashback_database FROM v$restore_point; SQL> DROP RESTORE POINT BEFORE_UPGRADE; Restore point dropped.
Warning: Once you drop the GRP, you cannot flashback. Ensure all testing is complete.
Troubleshooting — Common Errors and Fixes
⚠️ Issue 1: AutoUpgrade Fails with ORA-00904: Invalid Column Name
Root Cause: Deprecated or renamed data dictionary views.
Fix:
Check the AutoUpgrade log:
tail -100 /home/oracle/autoupgrade_logs/PRODDB/102/autoupgrade_err.log
Manually run the failing SQL from 23ai home and fix the issue.
⚠️ Issue 2: ORA-39700: Database Must Be Opened with UPGRADE Option
Root Cause: Database was not started in UPGRADE mode during AutoUpgrade execution.
Fix:
AutoUpgrade handles this automatically. If you see this error, restart the database manually in UPGRADE mode:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP UPGRADE;
Then re-run AutoUpgrade in deploy mode.
⚠️ Issue 3: utlrp.sql Runs Forever (Recompilation Stuck)
Root Cause: High number of invalid objects or insufficient parallelism.
Fix:
Kill the utlrp session and run it manually with higher parallelism:
SQL> @?/rdbms/admin/utlrp.sql
Or run in parallel using PL/SQL:
SQL> EXEC UTL_RECOMP.RECOMP_PARALLEL(8);
⚠️ Issue 4: Timezone Upgrade Fails
Root Cause: Timezone data in SYS.TSTZ$ is inconsistent.
Fix:
Check the AutoUpgrade log for the exact error. You may need to manually upgrade the timezone using DBMS_DST:
SQL> SELECT * FROM v$timezone_file; SQL> EXEC DBMS_DST.BEGIN_UPGRADE(46); SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP UPGRADE; SQL> SET SERVEROUTPUT ON SQL> DECLARE l_failures PLS_INTEGER; BEGIN DBMS_DST.UPGRADE_DATABASE(l_failures); DBMS_OUTPUT.PUT_LINE('Failures: ' || l_failures); END; / SQL> EXEC DBMS_DST.END_UPGRADE; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
Refer to MOS Note 1585343.1 for detailed timezone upgrade steps.
⚠️ Issue 5: AutoUpgrade Hangs at DBUPGRADE Stage
Root Cause: Large database with many objects, slow I/O, or insufficient parallelism.
Fix:
Increase parallelism in the config file:
upg1.target_pdb_copy_option=12
Monitor progress by tailing the catupgrade log:
tail -f /home/oracle/autoupgrade_logs/PRODDB/102/dbupgrade/catupgrd*.log
If it's truly stuck, check for blocking sessions:
SQL> SELECT sid, serial#, username, status, event FROM v$session WHERE username IS NOT NULL;
⚠️ Issue 6: Post-Upgrade Application Fails with ORA-00904
Root Cause: Application uses deprecated features or SQL syntax not supported in 23ai.
Fix:
Identify the failing SQL statement and check Oracle 23ai Deprecations in the official documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/23/upgrade.html
Common deprecated features in 23ai:
LONG columns (migrate to CLOB)
Oracle Streams (use GoldenGate or Data Guard)
SQL*Net V1 protocol
Rewrite or patch the application code.
Best Practices — Upgrade Oracle 19c to 23ai
✅ 1. Always Test in Non-Production First
Upgrade a DEV or UAT database first. Verify application compatibility. Then upgrade PROD.
✅ 2. Use the Latest AutoUpgrade Version
Oracle releases AutoUpgrade updates frequently. Always download the latest from MOS Note 2485457.1 before starting.
✅ 3. Enable Guaranteed Restore Point for PROD Upgrades
GRP gives you instant rollback capability. Worth the FRA space cost.
✅ 4. Monitor Disk Space and FRA
Upgrade generates significant redo and flashback logs. Monitor:
SQL> SELECT * FROM v$recovery_file_dest;
✅ 5. Use Parallelism to Speed Up Upgrade
Set target_pdb_copy_option=8 (or higher) in the AutoUpgrade config file.
✅ 6. Review All AutoUpgrade Logs After Completion
Even if the upgrade succeeds, review the logs for warnings about deprecated features, underscore parameters, or unsupported configurations.
✅ 7. Gather Fresh Statistics After Upgrade
Oracle 23ai has a new optimizer. Gather statistics after upgrade for accurate execution plans.
✅ 8. Update Backup Strategy for 23ai
After upgrade, take a fresh RMAN backup and update your backup scripts to use the 23ai home.
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
✅ 9. Patch to the Latest 23ai RU Immediately
Don't stay on base 23ai. Apply the latest Release Update (RU) right after upgrade for bug fixes and security patches.
✅ 10. Read the 23ai Documentation
Familiarize yourself with new features, deprecated parameters, and new initialization parameters:
https://docs.oracle.com/en/database/oracle/oracle-database/23/upgrade.html
Summary
Upgrading from Oracle Database 19c to 23ai using AutoUpgrade is straightforward if you follow the planning checklist and execute each step carefully. Here's the quick recap:
Plan — Review MOS notes, take backups, estimate downtime
Download — Get Oracle 23ai software, latest AutoUpgrade, and latest RU
Install — Software-only install of 23ai, apply RU
Analyze — Run AutoUpgrade in analyze mode, fix any blockers
Fixup — Run AutoUpgrade in fixup mode (optional)
Upgrade — Run AutoUpgrade in deploy mode
Validate — Check version, invalid objects, registry, and new features
Cleanup — Update oratab, listener, and drop GRP
Optimize — Gather stats, test application, apply latest RU
Oracle Database 23ai brings over 300 new features that will transform how you build modern applications — especially with AI Vector Search, JSON Relational Duality, and True Cache. If you're on 19c, now is the perfect time to upgrade.
Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment
FAQ
Q1: Can I upgrade directly from Oracle 12c to 23ai?
No. Oracle Database 23ai only supports direct upgrades from 19c and 21c. If you're on 12c, you must first upgrade to 19c, then to 23ai.
Q2: Is DBUA supported for Oracle 23ai upgrades?
No. The Database Upgrade Assistant (DBUA) has been desupported for Oracle 23ai upgrades. You must use AutoUpgrade.
Q3: Can I upgrade a non-CDB to 23ai?
Yes, but 23ai requires the multitenant architecture. AutoUpgrade can upgrade your non-CDB and convert it to a PDB in a new CDB in a single operation. Add target_cdb and target_pdb_name to your config file.
Q4: How long does an AutoUpgrade take?
For a typical 500 GB database, expect 2.5–4 hours total. The actual upgrade time depends on the number of objects, I/O speed, and parallelism settings.
Q5: Can I rollback if the AutoUpgrade fails?
Yes, if you enabled a Guaranteed Restore Point (GRP) before upgrade, you can instantly flashback. Otherwise, restore from your RMAN backup. AutoUpgrade does NOT automatically create a GRP — you must do it manually.
Q6: What is the latest Oracle Database 23ai Release Update?
As of October 2026, Release Update 23.9 is the latest. Always apply the latest RU after upgrade. Check MOS Note 742060.1 for the current RU schedule.
Q7: Does Oracle 23ai support my EBS 12.2 environment?
Yes, Oracle E-Business Suite 12.2 is certified with Database 23ai on Exadata Database Machine running Exadata Software 24.1 or later (as of Feb 2026). Other platforms are pending certification. Check the EBS Tech Blog for updates.
Suggested Internal Links (anchor text → URL from existing FOA posts):
"Upgrading an Oracle EBS 12.2.10 Database from 12c to 19c" → https://www.funoracleapps.com/2022/03/upgrading-oracle-ebs-1224-database.html
(Link from the "Direct Upgrade Path" section)
"Step by Step: Provision an Autonomous Database in OCI" → https://www.funoracleapps.com/2025/05/step-by-step-provision-autonomous.html
(Link from the "AI Vector Search" feature discussion)
"Oracle 11g AUD$ Table Maintenance After ORA-01653" → https://www.funoracleapps.com/2022/oracle-11g-aud-table-maintenance.html
(Link from troubleshooting section discussing dictionary space issues)
"How to Find Why CRS/CSSD Rebooted a Server in Oracle RAC" → https://www.funoracleapps.com/2022/how-to-find-why-crscssd-rebooted.html
(Link from the RAC upgrade considerations in prerequisites)
Suggested Images / Diagrams (descriptions + alt text):
AutoUpgrade Workflow Diagram
Description: Flowchart showing the four AutoUpgrade modes (Analyze → Fixup → Deploy → Upgrade) with decision points
Alt text: "Oracle AutoUpgrade workflow diagram showing analyze, fixup, deploy, and upgrade modes"
Before and After Architecture Comparison
Description: Side-by-side comparison of 19c non-CDB vs. 23ai CDB/PDB architecture
Alt text: "Oracle Database 19c non-CDB to 23ai CDB PDB architecture comparison"
AutoUpgrade Console Screenshot
Description: Screenshot of AutoUpgrade running in console mode showing job status (lsj command output)
Alt text: "Oracle AutoUpgrade console showing upgrade job status for database PRODDB"
23ai New Features Infographic
Description: Visual list of top 10 Oracle 23ai features (AI Vector Search, JSON Duality, True Cache, Boolean, Schema Privileges, etc.)
Alt text: "Oracle Database 23ai top new features including AI Vector Search and JSON Relational Duality"

Parameter	Description
`global.autoupg_log_dir`	Directory for AutoUpgrade logs
`upg1.dbname`	Database name (from `v$database`)
`upg1.source_home`	Current 19c ORACLE_HOME
`upg1.target_home`	New 23ai ORACLE_HOME
`upg1.sid`	Oracle SID
`upg1.target_version`	Target DB version (23.5 = 23ai RU 23.5)
`upg1.restoration`	Enable GRP for flashback (`yes` or `no`)
`upg1.timezone_upg`	Upgrade timezone file (`yes` recommended)
`upg1.run_utlrp`	Recompile invalid objects post-upgrade
`upg1.target_pdb_copy_option`	Parallelism (use 4–8 for faster upgrade)

Issue	Severity	Auto-Fixable?
Deprecated parameters (`optimizer_mode=RULE`)	WARNING	Yes
Invalid objects in SYS/SYSTEM	WARNING	Yes (via utlrp)
Timezone file outdated	WARNING	Yes (if `timezone_upg=yes`)
Underscore parameters (`_optimizer_...`)	INFO	No (review manually)
SQL Plan Baselines	INFO	No action needed
Pluggable databases compatibility	ERROR	No (manual fix required)