Showing posts with label ORACLE DATABASE. Show all posts
Showing posts with label ORACLE DATABASE. Show all posts

Monday, November 2, 2026

Oracle Database Deep Health Check Checklist


Oracle Database Deep Health Check Checklist

Recommended assessment levels

LevelPurposeTypical scope
L1Operational healthAvailability, space, backup, alert log
L2DBA healthObjects, jobs, sessions, memory, RMAN, Data Guard
L3Performance healthAWR, SQL, waits, I/O, redo, RAC, concurrency
L4Architecture/riskHA/DR, capacity, security, recoverability, OS/ASM/design

A proper architecture assessment should reach L4.


1. Database / Instance Health

1.1 Database state

Check:

  • Instance status
  • Database open mode
  • Database role
  • Restricted mode
  • ARCHIVELOG mode
  • FORCE LOGGING
  • Flashback status
  • Startup time
  • Database version/RU
  • CDB status

SELECT
    i.instance_name,
    i.host_name,
    i.version,
    i.status,
    i.database_status,
    i.startup_time,
    d.name,
    d.open_mode,
    d.database_role,
    d.log_mode,
    d.force_logging,
    d.flashback_on
FROM v$instance i
CROSS JOIN v$database d;

Validate

Healthy

  • Instance OPEN
  • Primary = READ WRITE
  • Physical standby normally MOUNTED or READ ONLY WITH APPLY depending on design
  • No unexpected restart

Investigate

  • Unexpected startup time
  • Restricted login
  • Instance recovery
  • ARCHIVELOG disabled on a database requiring PITR/DR
  • FORCE LOGGING disabled where Data Guard/design requires it

2. Oracle Version and Patch Health

Check:

SELECT banner_full
FROM v$version;
SELECT
    action_time,
    patch_id,
    patch_type,
    action,
    status,
    description
FROM dba_registry_sqlpatch
ORDER BY action_time DESC;

Validate:

  • Database RU level
  • GI RU level
  • OPatch inventory
  • SQL patch inventory
  • Failed datapatch components
  • GI/DB home consistency
  • Out-of-place patching strategy
  • One-off conflicts
  • Unsupported database version

Also compare:

$ORACLE_HOME/OPatch/opatch lsinventory

against:

SELECT * FROM dba_registry_sqlpatch;

A mismatch deserves investigation.


3. CDB/PDB Health

Especially important for your multitenant environments.

SELECT
    con_id,
    name,
    open_mode,
    restricted,
    open_time
FROM v$pdbs
ORDER BY con_id;

Check:

  • PDB open state
  • SAVE STATE
  • Restricted PDBs
  • PDB startup failures
  • PDB service configuration
  • Resource plans
  • PDB space utilization
  • Local undo configuration
  • PDB violations

SELECT *
FROM pdb_plug_in_violations
WHERE status <> 'RESOLVED'
ORDER BY time DESC;

Red flag: unresolved ERROR-level compatibility/plugin violations.


4. Tablespace Health

A health check should consider both percentage utilization and remaining GB.

Example:

SELECT
    tablespace_name,
    ROUND(used_space * 8192/1024/1024/1024,2) used_gb,
    ROUND(tablespace_size * 8192/1024/1024/1024,2) total_gb,
    ROUND(used_percent,2) used_pct
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

A commonly used operational starting point is:

  • <80% normal
  • 80–90% investigate/growth watch
  • >90% action

However, do not judge a very large tablespace by percentage alone. A multi-terabyte tablespace at 90% could still have substantial free space.

Check additionally:

  • Autoextend
  • MAXSIZE
  • Remaining filesystem/ASM space
  • SYSTEM
  • SYSAUX
  • UNDO
  • TEMP
  • Bigfile tablespaces
  • Read-only tablespaces
  • Offline datafiles

5. Datafile Health

SELECT
    tablespace_name,
    file_name,
    bytes/1024/1024/1024 GB,
    autoextensible,
    maxbytes/1024/1024/1024 max_gb,
    status
FROM dba_data_files
ORDER BY tablespace_name, file_name;

Check:

  • Offline files
  • Recover status
  • Autoextend
  • Maximum file size
  • Storage headroom underneath autoextend
  • Uneven datafile growth
  • Excessive datafile count

Also:

SELECT * FROM v$recover_file;

Ideally no unexpected rows.


6. ASM Health

For ASM environments check:

SELECT
    name,
    state,
    type,
    total_mb,
    free_mb,
    usable_file_mb,
    ROUND(100 * (1 - usable_file_mb/NULLIF(total_mb,0)),2) used_pct
FROM v$asm_diskgroup;

Check:

  • Diskgroup mounted
  • Disk failures
  • Rebalance operation
  • Usable space
  • ASM redundancy
  • Failure-group distribution
  • OCR/Voting disk placement
  • FRA/data separation where architecture requires it

SELECT * FROM v$asm_operation;

Unexpected long-running rebalance activity needs review.


7. TEMP Health

Check current TEMP configuration:

SELECT
    tablespace_name,
    file_name,
    bytes/1024/1024/1024 gb,
    autoextensible,
    maxbytes/1024/1024/1024 max_gb
FROM dba_temp_files;

Investigate:

  • TEMP exhaustion
  • Large TEMP consumers
  • SQL spilling repeatedly to TEMP
  • Sort/hash pressure
  • Parallel queries consuming TEMP
  • ETL/reporting peaks

TEMP problems should also be correlated with PGA sizing rather than solved only by adding TEMP.


8. UNDO Health

SELECT
    begin_time,
    end_time,
    undoblks,
    txncount,
    maxquerylen,
    nospaceerrcnt,
    ssolderrcnt
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 24 ROWS ONLY;

Look especially at:

  • NOSPACEERRCNT
  • SSOLDERRCNT
  • MAXQUERYLEN
  • Undo retention
  • Long-running transactions
  • Flashback requirements

Repeated ORA-01555 should trigger workload/undo-retention analysis, not blindly increasing UNDO.


9. Redo Log Architecture

Check:

SELECT
    group#,
    thread#,
    sequence#,
    bytes/1024/1024 size_mb,
    members,
    status,
    archived
FROM v$log
ORDER BY thread#, group#;

and:

SELECT
    group#,
    member,
    type,
    status
FROM v$logfile
ORDER BY group#;

Validate:

  • Correct multiplexing
  • Group sizes
  • Group counts
  • RAC thread configuration
  • Member failures
  • Log switch frequency

Ideally review:

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

Frequent switches can indicate undersized redo logs or unusually high redo generation.


10. FRA Health

SELECT
    name,
    space_limit/1024/1024/1024 limit_gb,
    space_used/1024/1024/1024 used_gb,
    space_reclaimable/1024/1024/1024 reclaimable_gb,
    number_of_files
FROM v$recovery_file_dest;

Also:

SELECT *
FROM v$flash_recovery_area_usage;

Check:

  • Archive logs
  • Backups
  • Flashback logs
  • Guaranteed restore points
  • Reclaimable space
  • FRA growth rate

A stuck archive process combined with FRA exhaustion can ultimately affect database availability. 


11. Alert Log / ADR Health

This is one of the highest-value checks.

Search for:

ORA-
TNS-
Fatal
Incident
Errors in file
Corrupt
Archiver
ORA-00600
ORA-07445
ORA-04031
ORA-01555
ORA-01652
ORA-01653
ORA-19809
ORA-00257

SQL:

SELECT
    originating_timestamp,
    message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY originating_timestamp DESC;

Do not treat every ORA message equally. Classify findings into:

Critical / Major / Warning / Informational


12. Invalid Objects

SELECT
    owner,
    object_type,
    COUNT(*) invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;

Compare to baseline.

Investigate:

  • Newly invalid objects
  • Application packages
  • SYS/SYSTEM invalid objects
  • Materialized views
  • Synonyms/dependencies

Avoid automatically assuming every invalid object is an outage risk.


13. Unusable Indexes

SELECT owner, index_name, table_name, status
FROM dba_indexes
WHERE status <> 'VALID';
SELECT index_owner, index_name, partition_name, status
FROM dba_ind_partitions
WHERE status <> 'USABLE';

Particularly important after:

  • Partition maintenance
  • ETL
  • Large data loads
  • Direct-path operations
  • Maintenance activities

14. Statistics Health

Given your recent focus on stale statistics, I would make this an explicit health-check category.

SELECT
    owner,
    table_name,
    num_rows,
    last_analyzed,
    stale_stats
FROM dba_tab_statistics
WHERE owner NOT IN ('SYS','SYSTEM')
AND (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER BY owner, table_name;

Check:

  • Stale table statistics
  • Stale partition statistics
  • Missing statistics
  • Global vs partition stats
  • Incremental statistics
  • Histogram usage
  • Modification rate
  • Auto stats job success
  • Stats gathering duration
  • Concurrent statistics jobs


15. Scheduler Health

SELECT
    owner,
    job_name,
    enabled,
    state,
    failure_count,
    last_start_date,
    last_run_duration,
    next_run_date
FROM dba_scheduler_jobs
WHERE enabled = 'TRUE'
ORDER BY owner, job_name;

Failures:

SELECT
    owner,
    job_name,
    status,
    actual_start_date,
    run_duration,
    error#,
    additional_info
FROM dba_scheduler_job_run_details
WHERE status <> 'SUCCEEDED'
ORDER BY actual_start_date DESC;

Look for:

  • FAILURES
  • BROKEN jobs
  • Runtime increase
  • Jobs not executing
  • Overlapping jobs
  • Stats/backup/batch contention

16. Blocking / Locking Health

SELECT
    sid,
    serial#,
    username,
    blocking_session,
    sql_id,
    event,
    seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

Empty is good at that instant.

Then investigate:

  • TX row lock contention
  • TM contention
  • DDL locks
  • Long transactions
  • Hot rows
  • Application commit behavior

One long transaction can block application activity without necessarily generating an obvious database error. 


17. Long-Running SQL

SELECT
    sid,
    serial#,
    username,
    sql_id,
    event,
    wait_class,
    last_call_et
FROM v$session
WHERE status = 'ACTIVE'
AND username IS NOT NULL
ORDER BY last_call_et DESC;

Compare against the normal workload baseline rather than adopting an arbitrary “30-minute SQL = bad” rule.


18. Wait Event Analysis

Start with:

SELECT
    wait_class,
    time_waited,
    total_waits
FROM v$system_wait_class
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC;

Then assess important waits such as:

  • db file sequential read
  • db file scattered read
  • direct path read
  • direct path write temp
  • log file sync
  • log file parallel write
  • enq: TX - row lock contention
  • buffer busy waits
  • read by other session
  • RAC gc waits

Do not tune purely from cumulative v$system_event. For serious diagnosis use an AWR interval representing the affected period.


19. AWR Deep Health Assessment

This is where I would move from DBA health check to Architect-level performance review.

Review:

Workload

  • DB Time
  • DB CPU
  • Executions
  • Transactions/s
  • Logical reads
  • Physical reads
  • Redo/sec
  • Parses
  • Hard parses

Load profile

Compare:

Per second
Per transaction
Per execution

against historical normal periods.

Top foreground events

Identify whether time is dominated by:

  • CPU
  • User I/O
  • Commit
  • Concurrency
  • Cluster
  • Configuration
  • Network

SQL assessment

Review top SQL by:

  • DB Time
  • CPU
  • Elapsed time
  • Buffer gets
  • Physical reads
  • Executions
  • Parse calls
  • Version count

The key question is not merely “what is the highest SQL?” but:

Why did its contribution change from the normal baseline?


20. Execution Plan Stability

Check important SQL for:

  • Multiple Plan Hash Values
  • Sudden plan change
  • Cardinality errors
  • New full scans
  • Join-method changes
  • Statistics-triggered plan changes
  • Bind sensitivity
  • Adaptive cursor behavior
  • SQL Profile
  • SQL Plan Baseline

Architecture health should include performance stability, not only current performance.


21. Shared Pool / Cursor Health

Investigate:

  • Hard parse percentage
  • Parse-to-execute ratio
  • Cursor invalidations
  • Reloads
  • Version count
  • ORA-04031
  • Excessive child cursors

Useful:

SELECT sql_id, version_count
FROM v$sqlarea
WHERE version_count > 20
ORDER BY version_count DESC;



22. SGA Health

SELECT component,
       current_size/1024/1024 MB
FROM v$sga_dynamic_components
ORDER BY current_size DESC;

Review:

  • Buffer cache
  • Shared pool
  • Large pool
  • Java pool
  • Streams pool
  • ASMM/AMM configuration

Also compare SGA allocation against:

  • server RAM
  • HugePages
  • NUMA architecture
  • other instances on host


23. PGA Health

SELECT
    name,
    ROUND(value/1024/1024,2) MB
FROM v$pgastat;

Review:

  • PGA allocated
  • Maximum allocated
  • Over-allocation
  • Workarea executions
  • TEMP spill behavior
  • pga_aggregate_target
  • pga_aggregate_limit

High TEMP activity + poor optimal workarea execution can point toward PGA/workarea pressure.


24. CPU Health

At database level review:

  • DB CPU
  • CPU wait
  • Run queue
  • Host CPU
  • Oracle process CPU
  • Top SQL CPU
  • Parallel workload

At OS/AIX level additionally assess:

CPU user
CPU sys
CPU idle
Run queue
Physical CPU
Entitlement
%entc
physc
Virtual CPU
Capped/uncapped
Shared processor pool
SMT

This is especially important for the IBM Power/LPAR environments you've been reviewing.

A database CPU problem may actually be:

Oracle workload → LPAR entitlement constraint → processor pool contention

rather than a database parameter problem.


25. Linux/AIX Memory Health

Check:

Physical RAM
Free/available
Swap
Paging
SGA
PGA
HugePages
NUMA
Filesystem cache
OS processes

For Linux:

free -g
vmstat 1 10
numactl --hardware
grep -i huge /proc/meminfo

For AIX:

svmon -G
vmstat 1 10
lparstat -i
lparstat 1 10

Critical question:

Is Oracle being paged/swapped?

That requires immediate investigation.


26. Storage / I/O Health

Review at least:

  • Read latency
  • Write latency
  • IOPS
  • Throughput
  • Queue depth
  • ASM disk latency
  • Datafile latency
  • Redo latency
  • TEMP latency



27. Commit / Redo Performance

Given the commit-behavior work you've been doing, I would give this its own section.

Correlate:

log file sync
log file parallel write
user commits
redo size
redo writes
log switches
storage latency
commit frequency

Interpretation pattern:

High log file sync + low redo-write latency

→ investigate application commit frequency/concurrency.

High log file sync + high log file parallel write

→ investigate redo I/O path.

Never diagnose commit performance using only one wait event.


28. RAC Health

For RAC check:

  • All instances online
  • Services correctly distributed
  • Interconnect health
  • Global Cache waits
  • Instance affinity
  • Service failover
  • VIP/SCAN
  • Clusterware resources
  • Node eviction history

crsctl stat res -t
srvctl status database -d <db_unique_name>
srvctl status service -d <db_unique_name>
olsnodes -n -s

Database:

SELECT inst_id, instance_name, status FROM gv$instance;


29. Data Guard Health

Essential checks:

SELECT
    database_role,
    open_mode,
    protection_mode,
    protection_level,
    switchover_status
FROM v$database;

Check:

  • Transport lag
  • Apply lag
  • MRP
  • Archive gaps
  • Broker configuration
  • Standby redo logs
  • Protection mode
  • Flashback
  • Apply rate
  • DR connectivity


30. RMAN Backup Health

Do not stop at:

Backup completed successfully.

Check:

SELECT
    start_time,
    end_time,
    input_type,
    status,
    output_bytes/1024/1024/1024 output_gb
FROM v$rman_backup_job_details
ORDER BY start_time DESC;

Assess:

  • Level 0
  • Level 1
  • Archive backup
  • Controlfile backup
  • SPFILE backup
  • Backup duration
  • Failed jobs
  • Backup growth
  • Retention policy
  • Crosscheck
  • Obsolete backups



31. Corruption Detection

Check:

SELECT * FROM v$database_block_corruption;

Also include appropriate RMAN validation processes in the recovery assurance program.

Any confirmed corruption should be Critical until scope and recoverability are understood.


32. Security Health

At minimum:

SELECT username, account_status, last_login
FROM dba_users
ORDER BY username;

Check:

  • Open default accounts
  • Default passwords
  • DBA grants
  • ANY privileges
  • SYSDBA access
  • Password profiles
  • Dormant users
  • Failed logins
  • Unified auditing
  • Audit retention
  • TDE
  • Wallet/keystore
  • Listener exposure



33. Listener / Network Health

Check:

lsnrctl status
lsnrctl services

Review:

  • Service registration
  • Handler count
  • Listener errors
  • TNS timeouts
  • Connection storms
  • DNS dependency
  • SCAN/VIP for RAC
  • SQL*Net encryption policy
  • Firewall idle timeout
  • Connection-pool behavior

Network problems frequently appear to applications as “database slowness”.


34. Database Links

SELECT owner, db_link, username, host
FROM dba_db_links;

Review:

  • Invalid links
  • Obsolete target systems
  • Security exposure
  • Password handling
  • Production → lower environment links
  • Remote dependency risk

35. Materialized Views

SELECT
    owner,
    mview_name,
    last_refresh_type,
    last_refresh_date,
    staleness
FROM dba_mviews;

Check:

  • Failed refreshes
  • Stale MVs
  • Increasing refresh duration
  • Broken chains/dependencies

36. Capacity / Growth Analysis

A mature health check should answer:

When will we run out of capacity?

Trend:

  • Database size
  • Tablespace growth
  • ASM consumption
  • FRA growth
  • Archive generation
  • Backup size
  • TEMP peak
  • Session growth
  • CPU
  • memory
  • IOPS

Forecast separately for:

30 days
90 days
6 months
12 months

Do not simply extrapolate one abnormal day. Use historical workload trends.


37. Configuration / Parameter Review

Capture:

SELECT name, value, isdefault, ismodified
FROM v$parameter
ORDER BY name;

Review non-default parameters.

Pay special attention to:

sga_target
sga_max_size
pga_aggregate_target
pga_aggregate_limit
processes
sessions
open_cursors
undo_retention
db_files
parallel_max_servers
optimizer_features_enable
optimizer_mode
cursor_sharing
filesystemio_options
disk_asynch_io
db_writer_processes

Do not recommend parameter changes simply because a parameter is non-default.

Every change should have:

Finding → Evidence → Impact → Recommendation → Risk → Rollback


38. Dictionary / Component Health

SELECT comp_id,
       comp_name,
       version,
       status
FROM dba_registry
ORDER BY comp_id;

Anything unexpectedly INVALID requires investigation.


39. Architectural Resilience Review

This separates an operational health check from an architecture assessment.

Check:

Single points of failure

  • Single DB node?
  • Single ASM failure group?
  • Single storage path?
  • Single listener path?
  • Single backup destination?
  • Single geographical location?
  • DR connectivity dependency?

Recovery design

Document:

RPO
RTO
Backup retention
Archive retention
PITR capability
DR topology
Restore test date/result
Failover test
Switchover test

A backup strategy without demonstrated recovery is incomplete.


40. OEM / Monitoring Health

Validate:

  • Database target status
  • Host target
  • ASM
  • Cluster
  • Listener
  • Data Guard
  • Tablespace threshold
  • FRA alert
  • CPU
  • filesystem
  • blocking session
  • backup
  • archive gap
  • scheduler failure alerts

Also review:

  • stale incidents
  • alert storms
  • permanently suppressed alerts
  • incorrect thresholds
  • monitoring blackout left active

Recommended Final Health Score

Recommend scoring by domain instead of producing one misleading overall percentage.

AreaSuggested status
AvailabilityGreen / Amber / Red
Backup & RecoveryGreen / Amber / Red
Data Guard / HAGreen / Amber / Red
StorageGreen / Amber / Red
PerformanceGreen / Amber / Red
CPUGreen / Amber / Red
MemoryGreen / Amber / Red
SQLGreen / Amber / Red
StatisticsGreen / Amber / Red
ObjectsGreen / Amber / Red
SecurityGreen / Amber / Red
Patch/VersionGreen / Amber / Red
CapacityGreen / Amber / Red
MonitoringGreen / Amber / Red

Then assign each finding:

🔴 Critical

Possible outage, corruption, data-loss or recovery failure.

🟠 High

Material production risk requiring remediation.

🟡 Medium

Health degradation or developing capacity/performance issue.

🔵 Low

Optimization or best-practice improvement.

🟢 Healthy

No material issue observed.


Architect-Level Structure


1. Executive Summary

Overall DB health and top risks.

2. Critical Findings

Anything that threatens:

  • Availability
  • Data integrity
  • Recoverability
  • Security
  • Performance

3. Current Architecture

Application
    ↓
Listener / SCAN
    ↓
Oracle RAC / Single Instance
    ↓
CDB
 ├── PDB1
 ├── PDB2
 └── PDB3
    ↓
ASM
    ↓
Storage

4. Performance Findings

Each one reported as:

Observation → Evidence → Root-cause hypothesis → Impact → Recommendation

5. Capacity Findings

Current:

DB → Tablespace → ASM → Filesystem → Storage

plus growth projection.

6. HA/DR & Backup

Primary → Data Guard → RMAN → Restore validation

7. Security / Compliance

Accounts, privileges, auditing, TDE, listener/network.

8. Remediation Plan

Classify actions as:

Immediate → 30 days → 90 days → Strategic




About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Wednesday, September 9, 2026

Video Tutorial - How to create or drop the users in Oracle Database ?



How to create user in Oracle Database ?




How to drop user in Oracle Database ?





How to set custom name on CMD prompt in Linux OS (Operating System) ?







 

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Video Tutorial - Oracle Database Start / Stop Administration

 

How to Start the Oracle Database ?




How to Stop / Shutdown the Oracle Database ?



Explain in detail how oracle database start ?




Explain in detail how oracle database stop / shutdown ?





About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Wednesday, August 26, 2026

what is catcon.pl (Catalog Container Script) and why very useful for Oracle Multitenant Database ?

 catcon.pl (Catalog Container Script) is an Oracle utility introduced with the Multitenant Architecture (CDB/PDB) to execute SQL scripts across one or more containers (CDB root, PDBs, or all PDBs).

It is heavily used during:

  • Database upgrades
  • Patching (RU, RUR, OJVM)
  • Running Oracle supplied scripts
  • Component installation
  • Post-upgrade tasks
  • Custom DBA scripts across all PDBs

Why Oracle Created catcon.pl

Before Multitenant, if you had:

ORCL

you ran a script once:

@script.sql

With Multitenant:

CDB1
├── PDB1
├── PDB2
├── PDB3
└── PDB4

The script may need to run in:

  • CDB$ROOT
  • PDB1
  • PDB2
  • PDB3
  • PDB4

Instead of connecting manually to each container, Oracle uses catcon.pl to automate the execution.


Location

Usually located under:

$ORACLE_HOME/rdbms/admin

Check:

find $ORACLE_HOME -name catcon.pl

Common path:

$ORACLE_HOME/rdbms/admin/catcon.pl


Basic Syntax

perl catcon.pl [options] script.sql

Example:

$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-b test </span>
-d /tmp </span>
test.sql

Where:

  • -b = base name for logs
  • -d = script directory
  • test.sql = SQL script to execute

Execute Script on All PDBs

Example:

$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-b gather_stats </span>
-d /tmp </span>
gather_stats.sql

The script runs across all open containers.


Run Script Only in Specific PDB

$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-c 'PDB1' </span>
-b test </span>
-d /tmp </span>
script.sql

Here:

PDB1 only

is processed.


Run in Multiple PDBs

-c 'PDB1 PDB2 PDB3'

Example:

catcon.pl -c 'PDB1 PDB2 PDB3' script.sql


Exclude Specific PDBs

-C 'PDB$SEED'

Example:

catcon.pl -C 'PDB$SEED' script.sql
``


Common Options

OptionDescription
-bBase log file name
-dScript location
-cInclude containers
-CExclude containers
-nParallel execution
-lLog directory
-uUsername
-pPassword

Parallel Execution

Suppose you have 20 PDBs.

Without parallelism:

catcon.pl script.sql

Runs one at a time.

Use:

catcon.pl -n 8 script.sql

to execute in parallel across 8 PDBs.


Example: Gather Dictionary Statistics

Oracle commonly uses:

$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-n 4 </span>
-l /tmp/logs </span>
-b gatherstats </span>
-d $ORACLE_HOME/rdbms/admin </span>
gather_stats.sql


During Database Upgrade

After upgrading Oracle software, Oracle internally runs scripts such as:

catupgrd.sql
utlrp.sql

using catcon.

Example:

catctl.pl

internally calls:

catcon.pl

to process all PDBs.

Relationship:

catctl.pl
|
+-- catcon.pl
|
+-- Executes scripts in all containers


Example: Recompile Invalid Objects in All PDBs

Instead of:

ALTER SESSION SET CONTAINER=PDB1;
@utlrp.sql

ALTER SESSION SET CONTAINER=PDB2;
@utlrp.sql

Use:

$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-b utlrp </span>
-d $ORACLE_HOME/rdbms/admin </span>
utlrp.sql
``

Oracle recompiles invalid objects in all PDBs automatically.


Log Files

Suppose:

-b test
-l /tmp/logs

Oracle generates:

test0.log
test1.log
test2.log
test3.log
``

along with spool files for each container.

Very useful during:

  • Upgrades
  • PSU/RU patching
  • Component installation

How catcon Knows the Current Container

Within the SQL script, you can identify the current container:

SELECT SYS_CONTEXT('USERENV','CON_NAME')
FROM dual;
``

When executed through catcon, each container processes the script independently.


Typical Oracle Scripts Executed with catcon

utlrp.sql
catalog.sql
catproc.sql
utluiobj.sql
dbmsupgnv.sql
gather_stats.sql


Best Practices for DBAs

Execute on all open PDBs

catcon.pl -n 8 script.sql

Keep separate log directory

-l /u01/logs

Validate PDB status before execution

SHOW PDBS;

Review logs after completion

grep -i "ORA-" *.log

Exclude PDB$SEED unless Oracle documentation requires it

-C 'PDB$SEED'


catcon.pl vs catctl.pl

UtilityPurpose
catcon.plExecute scripts across CDB/PDBs
catctl.plDatabase upgrade orchestration tool
dbupgradeWrapper around catctl.pl
datapatchApplies SQL patch changes using catcon internally

In One Line

catcon.pl is Oracle's Multitenant utility that executes SQL scripts simultaneously across one or more PDBs/CDB containers, making patching, upgrades, and administrative operations manageable in environments with many PDBs.

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Saturday, August 22, 2026

10000 foot level overview - High-Level Oracle 26ai Database overview and enhancement


Oracle AI Database 26ai

AI-Native, Secure, Highly Available and Developer-Friendly Database

Executive Summary

Oracle AI Database 26ai is Oracle’s long-term support, AI-native database release. It integrates AI, application development, security, high availability, analytics, and distributed data management into a single converged platform.

Key Value Proposition

  • Build enterprise AI applications directly on business data
  • Develop modern applications using SQL, JSON, Graph, Vector and JavaScript
  • Protect data consistently across users, applications and AI agents
  • Deliver mission-critical availability and global scalability
  • Reduce data movement and platform complexity

1. AI and Generative AI

Major Capabilities

  • AI Vector Search
    Performs semantic searches based on meaning rather than exact keywords.

  • Unified Hybrid Vector Search
    Combines vector, relational, text, JSON, graph and spatial searches in a single query.

  • Enterprise RAG
    Uses private business data to improve the accuracy and relevance of LLM-generated answers.

  • Select AI
    Enables users to query enterprise data using natural language.

  • Select AI Agent
    Supports governed AI agents that can retrieve information, execute database tools and perform business actions.

  • Model Context Protocol integration
    Allows AI assistants and agent frameworks to securely discover and use database tools.

  • Private Agent Factory
    Provides low-code and no-code capabilities for building private enterprise AI agents.

  • In-Database Machine Learning
    Allows organizations to train and score ML models without moving sensitive data outside the database.

Business Value

Bring AI to the data instead of moving enterprise data to separate AI platforms.


2. Modern Application Development

Major Capabilities

  • JSON-Relational Duality Views
    Applications can access the same data as JSON documents or relational tables without creating duplicate copies.

  • JavaScript Stored Procedures
    Developers can implement server-side application logic using JavaScript.

  • Operational Property Graphs
    Graph analysis can be performed directly on operational relational data using SQL.

  • Lock-Free Reservations
    Improves concurrency for highly contested data such as account balances, inventory and seat reservations.

  • Priority Transactions
    Protects critical business transactions by automatically resolving lower-priority blocking transactions.

  • Data Use Case Domains
    Centralizes reusable business definitions such as email, currency, URL and product identifiers.

  • Data Annotations
    Adds business meaning to database objects, helping AI systems better understand enterprise data.

  • Assertions
    Enforces complex business rules across multiple tables using declarative database constraints.

  • Enhanced SQL
    Includes Boolean data types, simplified queries, direct joins for updates and deletes, and tables with up to 4,096 columns.

Business Value

Accelerates application development while reducing middleware, ORM complexity and duplicate data stores.


3. Microservices and Event-Driven Applications

Major Capabilities

  • Transactional Event Queues
  • Kafka-compatible APIs
  • Database-supported Saga transactions
  • REST and JSON APIs
  • MongoDB-compatible access
  • Redis-compatible caching
  • Transaction-aware messaging
  • Lock-free concurrency controls

Business Value

Enables reliable microservices and event-driven applications while maintaining transactional consistency.


4. High Availability and Scalability

Major Capabilities

  • Oracle Real Application Clusters
    Provides active-active instance availability and horizontal database scaling.

  • Oracle Data Guard
    Delivers disaster recovery and standby database protection.

  • Active Data Guard
    Offloads read-only workloads, reporting and backups to standby databases.

  • Application Continuity
    Replays eligible application requests following recoverable failures.

  • Transaction Guard
    Determines the reliable outcome of transactions after interruptions.

  • True Cache
    Provides an automatically managed, consistent in-memory cache for read-intensive applications.

  • Online Maintenance
    Supports rolling patching, application upgrades and selected schema changes with minimal disruption.

Business Value

Maintains application availability during failures, maintenance and infrastructure changes.


5. Globally Distributed Database

Major Capabilities

  • Database Sharding
    Distributes data across multiple databases for horizontal scalability and fault isolation.

  • Directory-Based Sharding
    Provides flexible control over where tenant or customer data is stored.

  • Raft Replication
    Delivers built-in consensus-based replication and rapid failover for sharded environments.

  • Automatic Data Movement
    Automatically relocates data when a sharding key changes.

  • Geographic Data Distribution
    Helps address data residency, latency and regional availability requirements.

Business Value

Supports globally distributed applications with scale, local performance and regional fault isolation.


6. Security and Data Protection

Major Capabilities

  • Deep Data Security
    Enforces authorization at the row, column or cell level for users, applications and AI agents.

  • SQL Firewall
    Detects and blocks unauthorized SQL statements and SQL injection attacks.

  • Schema-Level Privileges
    Simplifies access management without granting broad system privileges.

  • Developer Role
    Provides developers with a predefined least-privilege role.

  • Multi-Factor Authentication

  • TLS 1.3

  • OAuth 2.0

  • Microsoft Entra ID integration

  • Constrained Kerberos delegation

  • Read-Only Users and Sessions

  • Longer Password Support

Existing Enterprise Controls

  • Transparent Data Encryption
  • Database Vault
  • Data Redaction
  • Virtual Private Database
  • Unified Auditing
  • Fine-Grained Auditing
  • Privilege Analysis
  • Oracle Key Vault integration

Business Value

Protects enterprise data at its source, regardless of whether it is accessed by a user, application, analytics tool or AI agent.


7. Analytics and Lakehouse

Major Capabilities

  • Autonomous AI Lakehouse
  • Apache Iceberg support
  • Vector search over lakehouse data
  • SQL analytics
  • Graph analytics
  • Spatial analytics
  • JSON analytics
  • Text search
  • In-database machine learning

Business Value

Combines operational data, analytics, AI and open lakehouse data without creating multiple isolated platforms.


8. Performance and Optimization

Major Capabilities

  • True Cache for read scalability
  • Lock-Free Reservations for high-concurrency workloads
  • Improved Hybrid Columnar Compression
  • Wide tables with up to 4,096 columns
  • Consolidated background processes
  • RAC-based scale-out
  • Sharding-based horizontal scaling
  • Exadata optimization

Business Value

Improves transaction throughput, query performance, storage efficiency and application response time.


9. Manageability and DevOps

Major Capabilities

  • Multitenant CDB and PDB architecture
  • Automated provisioning and cloning
  • Fleet patching and standardized maintenance
  • Container images for development and CI/CD
  • Automated backup and recovery
  • Automatic performance diagnostics
  • Autonomous tuning, indexing and scaling
  • Simplified transition from Oracle Database 23ai
  • Enterprise monitoring and observability

Business Value

Reduces operational effort and enables consistent database management across on-premises, cloud and multicloud environments.


Key Features at a Glance

CategoryKey Features
AIVector Search, Hybrid Search, RAG, Select AI, AI Agents and MCP
ApplicationsJSON Duality, JavaScript, Graph, Domains, Assertions and enhanced SQL
MicroservicesKafka APIs, TxEventQ, Sagas, REST, Redis and MongoDB-compatible APIs
High AvailabilityRAC, Data Guard, Active Data Guard, Application Continuity and True Cache
Distributed DatabaseSharding, Raft Replication and geographic data distribution
SecurityDeep Data Security, SQL Firewall, MFA, TLS 1.3 and Entra ID
AnalyticsAutonomous AI Lakehouse, Iceberg, Graph, Spatial, JSON and ML
PerformanceTrue Cache, HCC, lock-free transactions, RAC and Exadata
OperationsMultitenant, automation, containers, fleet management and autonomous operations

Top Features for Enterprise Adoption

Immediate Priorities

  1. SQL Firewall and schema-level privileges
  2. Deep Data Security for applications and AI agents
  3. Application Continuity and Data Guard improvements
  4. JSON-Relational Duality for modern applications
  5. AI Vector Search for enterprise RAG

Strategic Priorities

  1. True Cache for read-intensive workloads
  2. Select AI and Select AI Agent
  3. Transactional Event Queues and Kafka APIs
  4. Autonomous AI Lakehouse and Apache Iceberg
  5. Sharding with Raft replication

Recommended Closing Slide

Why Oracle AI Database 26ai?

One Database for Modern Enterprise Workloads

  • AI-native: Enterprise RAG, vector search and AI agents
  • Developer-friendly: SQL, JSON, JavaScript, Graph and REST
  • Mission-critical: RAC, Data Guard and Application Continuity
  • Secure by design: Security enforced directly where the data resides
  • Globally scalable: Sharding and distributed replication
  • Converged: Operational, analytical, AI and lakehouse workloads on one platform

Oracle AI Database 26ai brings AI to trusted enterprise data while preserving security, consistency, scalability and availability.


About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

PostgreSQL Process Architecture Diagram and Details

PostgreSQL Process Architecture Diagram                            +------------------+                            |    POSTMASTER    |     ...