Oracle Database Deep Health Check Checklist

Recommended assessment levels

Level	Purpose	Typical scope
L1	Operational health	Availability, space, backup, alert log
L2	DBA health	Objects, jobs, sessions, memory, RMAN, Data Guard
L3	Performance health	AWR, SQL, waits, I/O, redo, RAC, concurrency
L4	Architecture/risk	HA/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.

Area	Suggested status
Availability	Green / Amber / Red
Backup & Recovery	Green / Amber / Red
Data Guard / HA	Green / Amber / Red
Storage	Green / Amber / Red
Performance	Green / Amber / Red
CPU	Green / Amber / Red
Memory	Green / Amber / Red
SQL	Green / Amber / Red
Statistics	Green / Amber / Red
Objects	Green / Amber / Red
Security	Green / Amber / Red
Patch/Version	Green / Amber / Red
Capacity	Green / Amber / Red
Monitoring	Green / 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

Video Tutorial - How to install Oracle RAC Grid ?

 Install  Oracle RAC Grid

Video Tutorial - How to create an AMI (Image) from an Amazon EC2 Instance in AWS Cloud ?

Create an AMI (Image) from an Amazon EC2 Instance in AWS Cloud

 

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) ?

 

Video Tutorial - Enterprise High-Availability Metrics and SLA Standards "Nines" Explanation

High-Availability Metrics and SLA Standards "Nines"

 

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 ?

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

Option	Description
-b	Base log file name
-d	Script location
-c	Include containers
-C	Exclude containers
-n	Parallel execution
-l	Log directory
-u	Username
-p	Password

---

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

Utility	Purpose
catcon.pl	Execute scripts across CDB/PDBs
catctl.pl	Database upgrade orchestration tool
dbupgrade	Wrapper around catctl.pl
datapatch	Applies 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.