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.

Why SAVE STATE is Required in Oracle PDB ?

In Oracle Multitenant architecture, SAVE STATE is a feature that allows a Pluggable Database (PDB) to remember its open mode across a CDB restart.

Without SAVE STATE, when the Container Database (CDB) is restarted, all PDBs (except PDB$SEED) typically remain in MOUNTED state and must be opened manually.

The PDB SAVE STATE feature was introduced in Oracle Database 12c Release 1 Patch Set 1 (12.1.0.2). Prior to 12.1.0.2, PDBs did not automatically return to their previous open state after a CDB restart, and DBAs commonly used startup triggers to open PDBs automatically.

For Oracle 19c/21c/23ai/26ai, this feature remains widely used and is considered the standard method for automatic PDB startup.

Why SAVE STATE is Required

Suppose you have:

SQL> ALTER PLUGGABLE DATABASE PDBPROD OPEN;

PDBPROD is open and users can connect.

After a database restart:

SHUTDOWN IMMEDIATE;

STARTUP;

You may find:

SHOW PDBS;

Output:

PDB Name Open Mode

----------- ----------

PDB$SEED READ ONLY

PDBPROD MOUNTED

Applications cannot connect until you manually open the PDB.

To avoid this, Oracle provides SAVE STATE.

---

How to Save PDB State

Open the PDB first:

ALTER PLUGGABLE DATABASE PDBPROD OPEN;

Save its current state:

ALTER PLUGGABLE DATABASE PDBPROD SAVE STATE;

Oracle stores this information internally.

---

Verify Saved State

Query:

SELECT con_name,

state

FROM dba_pdb_saved_states;

Example:

CON_NAME STATE

--------- -----

PDBPROD OPEN

---

After CDB Restart

SHUTDOWN IMMEDIATE;

STARTUP;

Check:

SHOW PDBS;

Output:

PDB Name Open Mode

----------- ----------

PDB$SEED READ ONLY

PDBPROD READ WRITE

The PDB automatically opens because Oracle remembered the saved state.

---

Save State for All PDBs

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

Very useful after patching or maintenance.

---

Discard Saved State

If you don't want Oracle to auto-open a PDB:

ALTER PLUGGABLE DATABASE PDBPROD DISCARD STATE;

Verify:

SELECT * FROM DBA_PDB_SAVED_STATES;

The entry will be removed.

---

Save Different Open Modes

Read Write

ALTER PLUGGABLE DATABASE PDBPROD OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDBPROD SAVE STATE;

Read Only

ALTER PLUGGABLE DATABASE REPORT_PDB OPEN READ ONLY;

ALTER PLUGGABLE DATABASE REPORT_PDB SAVE STATE;

``

After restart, Oracle restores the same mode.

---

Check Current and Saved State

Current state:

SHOW PDBS;

or

SELECT name, open_mode

FROM v$pdbs;

Saved state:

SELECT con_id,

con_name,

instance_name,

state

FROM dba_pdb_saved_states;

---

RAC Environment

In Oracle RAC, SAVE STATE is instance-specific.

Example:

ALTER PLUGGABLE DATABASE PDBPROD OPEN INSTANCES=ALL;

ALTER PLUGGABLE DATABASE PDBPROD SAVE STATE INSTANCES=ALL;

Check:

SELECT con_name,

instance_name,

state

FROM dba_pdb_saved_states;

You will see an entry for each RAC instance.

---

Best Practice

After:

• Creating a new PDB
• Cloning a PDB
• Refreshable PDB setup
• Database patching
• Migration to a new server

Always execute:

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

and verify:

SELECT con_name, state

FROM dba_pdb_saved_states;

This ensures all required PDBs automatically open after any database restart and avoids application outages caused by PDBs remaining mounted.

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

Category	Key Features
AI	Vector Search, Hybrid Search, RAG, Select AI, AI Agents and MCP
Applications	JSON Duality, JavaScript, Graph, Domains, Assertions and enhanced SQL
Microservices	Kafka APIs, TxEventQ, Sagas, REST, Redis and MongoDB-compatible APIs
High Availability	RAC, Data Guard, Active Data Guard, Application Continuity and True Cache
Distributed Database	Sharding, Raft Replication and geographic data distribution
Security	Deep Data Security, SQL Firewall, MFA, TLS 1.3 and Entra ID
Analytics	Autonomous AI Lakehouse, Iceberg, Graph, Spatial, JSON and ML
Performance	True Cache, HCC, lock-free transactions, RAC and Exadata
Operations	Multitenant, 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

6. True Cache for read-intensive workloads
7. Select AI and Select AI Agent
8. Transactional Event Queues and Kafka APIs
9. Autonomous AI Lakehouse and Apache Iceberg
10. 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.