Thursday, September 10, 2026

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

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

Video Tutorial - Oracle Database about Parsing - Soft and Hard Parsing

What is Soft Parsing in Oracle Database ?

What is Hard Parsing in Oracle Database ?

What is Parsing in Oracle Database ?

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 ?

Tuesday, September 8, 2026

Atharv Pandey - A45 - Value Your Virtues – National Level Contest 2026 Winner #futureleaders

🌟 Please support and encourage young talent!

Proud to share Atharv’s participation in the“Value Your Virtues” National Level Contest 2026.

Every opportunity to speak, perform, and express ideas helps children become more confident and prepared for the future.

🎥 Watch the video:

Speech Video :

🔗 https://www.youtube.com/watch?v=DMswOoRW0D8

Please share your encouragement and best wishes in the comments. 🙏

#ValueYourVirtues #YoungTalent #NationalLevelContest #ProudMoment #PublicSpeaking #ConfidenceBuilding #ChildDevelopment

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

$ORACLE_HOME/rdbms/admin/catcon.pl

-b test

-d /tmp

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

$ORACLE_HOME/rdbms/admin/catcon.pl

-b gather_stats

-d /tmp

gather_stats.sql

The script runs across all open containers.

Run Script Only in Specific PDB

$ORACLE_HOME/perl/bin/perl

$ORACLE_HOME/rdbms/admin/catcon.pl

-c 'PDB1'

-b test

-d /tmp

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

$ORACLE_HOME/rdbms/admin/catcon.pl

-n 4

-l /tmp/logs

-b gatherstats

-d $ORACLE_HOME/rdbms/admin

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

$ORACLE_HOME/rdbms/admin/catcon.pl

-b utlrp

-d $ORACLE_HOME/rdbms/admin

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;

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.

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

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

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

Strategic Priorities

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

Oracle AI Database 26ai features and use

Oracle AI Database 26ai features, It focuses on application development, AI, high availability, security, distributed databases, performance, analytics, and manageability rather than listing every minor initialization parameter or API enhancement.

Release context: Oracle AI Database 26ai is the long-term support release that replaces Oracle Database 23ai. Existing 23ai environments can transition by applying the relevant Release Update, without a conventional database upgrade or application recertification. Features introduced during the 23ai innovation cycle are therefore part of the broader 26ai feature set.

1. AI and Generative AI

1.1 Oracle AI Vector Search

Native VECTOR data type for storing numerical embeddings.
Semantic similarity search based on meaning rather than exact keyword matching.
Exact and approximate nearest-neighbor vector search.
Vector indexes for scalable similarity search.
Ability to combine vector predicates with:
- Relational filters
- Full-text search
- JSON
- Spatial data
- Property graphs
Support for enterprise Retrieval-Augmented Generation, or RAG.
Database-native document loading, transformation, chunking, embedding, retrieval, and LLM integration.
Vector search over structured and unstructured information.
Support for vectors stored in Oracle tables and Apache Iceberg tables.

1.2 Unified Hybrid Vector Search

Oracle can combine different retrieval techniques in one workflow:

Semantic vector search
Keyword and Oracle Text search
Relational SQL predicates
JSON filtering
Spatial filtering
Graph relationships
Business-rule filters

This is particularly useful for enterprise RAG because exact terms such as employee IDs, product codes, locations, and policy names can be combined with semantic similarity.

1.3 Select AI

Select AI provides a natural-language interface to enterprise data:

Converts natural-language questions into SQL.
Generates natural-language explanations from query results.
Supports conversational interaction with database data.
Supports RAG over private enterprise content.
Uses database metadata and annotations to improve generated SQL.
Can integrate with supported external LLM providers.
Helps reduce the need for applications to build separate natural-language-to-SQL layers.

1.4 Select AI Agent

Create and operate AI agents close to governed enterprise data.
Agents can use database objects and procedures as tools.
Agents can invoke external tools through REST interfaces.
Agents can interact with MCP servers.
Supports multi-step agentic workflows involving retrieval, reasoning, and actions.
Database security and auditing can be applied to agent activity.

1.5 Model Context Protocol support

Oracle Database can participate in the MCP ecosystem used by AI applications and agents.
Database capabilities can be exposed as discoverable agent tools.
Managed MCP endpoints can expose Select AI Agent tools.
MCP clients can invoke governed database functions and retrieval workflows.
Integration logic can be kept closer to the data rather than creating a separate custom middleware service.

1.6 Private Agent Factory

No-code or low-code creation of private enterprise AI agents.
Deployable in a customer-controlled environment.
Designed to work against protected enterprise information.
Helps create agents without sending all business data to externally managed systems.
Complements Select AI and database-native agent capabilities.

1.7 Private AI Services Container

Supports deployment of selected AI services in customer-controlled infrastructure.
Helps organizations keep AI processing closer to sensitive database data.
Useful for regulated, disconnected, or tightly controlled environments.
Reduces dependency on sending information to a public AI endpoint.

1.8 Embedding and model interoperability

Integration with leading LLMs.
Support for ONNX embedding models.
Ability to generate embeddings through database-controlled workflows.
Support for open agentic AI frameworks.
Greater flexibility in selecting embedding and language models.

1.9 In-database machine learning

Train and score models without moving data outside the database.
Enhancements to algorithms for improved text and data classification.
Better algorithm performance and flexibility.
Integration of machine-learning output with SQL, analytics, and application workloads.
Reduces data movement and separate ML infrastructure requirements.

2. Application Development

2.1 JSON-Relational Duality Views

Present normalized relational data as application-friendly JSON documents.
Read and update the same underlying data through either SQL or JSON.
Avoid maintaining separate relational and document copies.
Reduce dependence on complex Object-Relational Mapping frameworks.
Updatable JSON documents remain transactionally consistent with relational tables.
Accessible using SQL, REST, document APIs, and MongoDB-compatible interfaces.
Supports optimistic or lock-free concurrency control.
Fine-grained rules can control whether parts of the document are insertable, updateable, or deletable.

2.2 Native JSON capabilities

Native JSON data type and optimized binary JSON storage.
SQL/JSON query and transformation functions.
JSON collection tables.
JSON search indexes.
JSON schema validation capabilities.
JSON document access through SODA and supported document APIs.
Integration of JSON with vector, spatial, graph, and relational queries.

2.3 JavaScript stored procedures

Develop database stored procedures in JavaScript.
Use JavaScript for server-side application logic.
Access database data directly from JavaScript procedures.
Allows JavaScript developers to build data-intensive logic closer to the database.
Complements existing PL/SQL, Java, SQL, and external-language support.

2.4 Operational Property Graphs and SQL/PGQ

Create property graphs over operational relational data.
Query graph relationships using the ISO/IEC SQL Property Graph Queries standard.
Run graph analysis without copying operational data to a separate graph database.
Combine graph results with relational, JSON, vector, and spatial operations.
Useful for fraud detection, dependency analysis, network analysis, recommendations, and customer relationships.

2.5 Lock-Free Reservations

Reserve portions of a numeric resource without locking the complete row.
Designed for heavily updated records such as:
- Account balances
- Inventory quantities
- Seats
- Credit limits
- Quotas
Validation is performed without conventional row-lock serialization.
Final updates are applied at commit.
Improves throughput and reduces blocking for high-concurrency OLTP applications.

2.6 Priority Transactions

Applications can assign transaction priorities.
A low-priority transaction blocking a high-priority transaction can be automatically aborted.
Helps protect business-critical operations.
Reduces the need for DBAs to identify and terminate blockers manually.
Maintains better throughput under contention.

2.7 Data Use Case Domains

Define reusable domain-level business semantics for columns.
Examples include:
- Email
- URL
- Currency
- Password
- Phone number
- Product identifier
Centralizes data validation and semantic information.
Applications and development tools can use domains for code generation and value validation.
Helps maintain consistent definitions across tables and applications.

2.8 Data annotations

Attach business descriptions and semantic context to schemas and data.
Give AI systems a clearer understanding of table and column meaning.
Improve natural-language-to-SQL accuracy.
Improve AI-generated application code.
Reduce ambiguity where technical database names differ from business terminology.

2.9 SQL enhancements

Major developer-facing enhancements include:

Native SQL BOOLEAN data type.
SELECT statements without a FROM clause.
GROUP BY using a column alias or column position.
Direct joins in UPDATE and DELETE.
Unicode 15.0 support.
Improved SQL syntax compatibility for developers migrating from other platforms.
Wide tables supporting as many as 4,096 columns.
Assertions for declarative business rules spanning one or more tables.

2.10 Assertions

Define declarative business rules across one or multiple tables.
Address cases that are difficult to implement with normal check constraints.
Reduce dependency on complex custom triggers.
Enforce rules consistently regardless of which application modifies the data.
Handle concurrency and serialization concerns within the database.
Allow a single central rule to replace duplicate validation logic in multiple applications.

2.11 Transactional Event Queues and Kafka APIs

Kafka-compatible APIs for Oracle Transactional Event Queues, or TxEventQ.
Existing Kafka applications can connect with fewer code changes.
Messaging can participate in Oracle transactions.
Supports event-driven application and microservices architectures.
Reduces the need to operate a separate event platform for certain database-centric workloads.

2.12 Sagas for microservices

Database-supported saga patterns for long-running distributed transactions.
Helps coordinate transactions across multiple services.
Supports compensation when one stage of a business transaction fails.
Avoids holding traditional distributed locks across services.
Appropriate for order, payment, inventory, and shipment workflows.

2.13 Redis-compatible access

Oracle AI Database and True Cache can provide Redis-compatible server functionality.
Redis objects can be backed by Oracle database objects.
Cached data can be synchronized automatically when underlying data changes.
Offers a database-managed alternative for selected Redis-style caching patterns.
Can be used with Oracle Database, Active Data Guard, and True Cache configurations.

2.14 Developer connectivity and APIs

The 26ai development ecosystem includes:

JDBC and Universal Connection Pool
Python python-oracledb
Node.js node-oracledb
Oracle Call Interface
ODBC
.NET providers
Oracle REST Data Services
SODA and document APIs
MongoDB-compatible API
JavaScript, PL/SQL, Java, C, C++, Python, and other supported programming interfaces

3. High Availability and Scalability

3.1 Oracle True Cache

Primarily in-memory, automatically managed cache for Oracle Database data.
Transactionally consistent with the primary database.
Offloads read-intensive workloads.
Applications can connect directly for read-only operations.
JDBC applications can route read-only sections to configured True Cache instances.
Provides fresher and more consistently managed data than many manually maintained application caches.
Designed mainly for read scalability and performance, not as a disaster-recovery replacement for Data Guard.

3.2 Oracle RAC

Oracle Real Application Clusters remains the foundation for:

Active-active database instance availability.
Scale-out of database processing.
Instance failover.
Service-based workload management.
Online maintenance with reduced application disruption.
Fast Application Notification and connection-pool integration.

The 26ai high-availability documentation continues to group RAC enhancements alongside general and Data Guard improvements.

3.3 Oracle Data Guard

Major 26ai Data Guard areas include:

Physical standby protection and disaster recovery.
Data Guard Broker automation.
Fast-Start Failover.
Active Data Guard read offload.
Multitenant and per-PDB protection enhancements.
Improved hybrid cloud support.
Better automation-oriented output and management.
Greater control over role-transition targets.
Application Continuity and rolling-maintenance improvements.

Oracle’s 26ai new-features guide specifically organizes Data Guard, RAC, and general improvements under High Availability.

3.4 Globally Distributed Database with Raft replication

Built-in replication for sharded databases.
Consensus-based commit using the Raft protocol.
Does not require configuring Data Guard or GoldenGate for this replication model.
Declarative replication configuration.
Subsecond failover capabilities.
Improves fault isolation and availability for globally distributed applications.
Helps optimize hardware utilization in sharded environments.

3.5 Directory-based sharding

Dynamically determines the location of records by sharding key.
Maintains key-to-shard mapping in a directory.
Supports large numbers of key mappings.
Allows individual or bulk movement of keys between shards.
Useful for data residency, tenant placement, load balancing, and geographic distribution.
Delivers horizontal scalability with shard-level fault isolation.

3.6 Automatic data movement after sharding-key updates

Automatically moves a row when its updated sharding key maps to another partition or shard.
Reduces application-side data-movement logic.
Supports business changes such as customer relocation, organizational reassignment, or jurisdiction changes.

3.7 Application Continuity and Transaction Guard

Helps applications survive planned and unplanned outages.
Replays eligible database work safely after recoverable failures.
Transaction Guard provides a reliable transaction outcome.
Reduces duplicate transaction risk.
Integrates with RAC, Data Guard, database services, JDBC/UCP, and connection pools.

These capabilities remain central to Oracle’s high-availability stack, with 26ai expanding high-availability and failover integration.

3.8 Online operations

Oracle 26ai continues Oracle’s broader support for online:

Patching and rolling maintenance
Schema changes
Table and index maintenance
Data movement
Reorganization
Application upgrades using Edition-Based Redefinition

Availability depends on deployment type, licensing, and the specific operation.

4. Security

4.1 Oracle Deep Data Security

Database-enforced authorization for users, applications, analytics, and AI agents.
Propagates the original user or agent identity and execution context to the database.
Declarative SQL policies enforce access at:
- Row level
- Column level
- Individual-cell level
Applies authorization regardless of whether data is reached through an application, SQL tool, analytics platform, or AI agent.
Helps mitigate excessive AI-agent privilege and prompt-injection consequences.
Centralizes authorization instead of duplicating it in every application.
Provides an additional defense if application-level authorization is bypassed or incorrectly implemented.

4.2 Oracle SQL Firewall

Built into Oracle AI Database.
Inspects incoming SQL statements.
Learns or defines approved SQL behavior.
Detects, logs, alerts on, or blocks unauthorized SQL.
Helps defend against SQL injection and compromised application credentials.
Enforcement occurs inside the database, regardless of the SQL execution path.

4.3 Schema-level privileges

Grant privileges across objects in a schema without using broad system privileges.
Simplifies privilege administration.
Reduces large sets of individual object grants.
Supports least-privilege access more effectively.
Makes access easier to manage as new objects are introduced into an application schema.

4.4 `DB_DEVELOPER_ROLE`

Predefined role for application developers.
Provides a curated set of privileges required to design, build, and deploy database applications.
Reduces the practice of assigning broad DBA-like privileges to developers.
Supports least-privilege development environments.

4.5 Multi-factor authentication

MFA can be enabled for native Oracle Database users.
Strengthens authentication beyond a password-only model.
Helps protect privileged and sensitive database accounts.

4.6 TLS 1.3 and simplified TLS

Support for TLS 1.3.
Newer cipher suites provide stronger protection for data in transit.
Simplified client/server TLS configuration.
Helps reduce configuration errors while improving transport security.

4.7 FIPS 140-3 preparation

Oracle AI Database 26ai supports preparation for FIPS 140-3 compliance.
Organizations may need to review and replace older encryption algorithms.
Relevant for regulated and government environments.

4.8 Constrained Kerberos delegation

Introduces control over unconstrained versus constrained Kerberos ticket delegation.
Reduces exposure associated with forwarding broad ticket-granting credentials.
Useful for enterprise single sign-on environments.

4.9 OAuth 2.0 and Microsoft Entra ID integration

OAuth 2.0 authentication support through supported database clients.
Integration with OCI IAM and Microsoft Entra ID, formerly Azure AD.
Enables cloud-based single sign-on to Oracle Database services and supported on-premises databases.
Helps application teams avoid embedding permanent database passwords.

4.10 Long passwords

Native database passwords can be up to 1,024 bytes.
Supports stronger password and passphrase policies.
Better accommodates externally generated credentials.

4.11 Read-only users and sessions

A user or session can be restricted to read-only operations irrespective of other granted privileges.
Useful for reporting, troubleshooting, audit access, and production support.
Reduces the possibility of accidental data modification.

4.12 Existing enterprise security capabilities

Oracle 26ai also retains Oracle’s established security stack:

Transparent Data Encryption
Data Redaction
Virtual Private Database
Real Application Security
Label Security
Database Vault
Unified Auditing
Fine-Grained Auditing
Privilege Analysis
Key Vault integration
Native network encryption
Backup encryption
Data Safe integration

Licensing requirements vary by edition, option, cloud service, and deployment platform.

5. Analytics and AI Lakehouse

5.1 Autonomous AI Lakehouse

Combines Oracle AI Database analytics with Apache Iceberg data lakes.
Executes Oracle SQL and AI workloads over Iceberg data.
Supports relational, JSON, graph, spatial, and vector analytics.
Uses Exadata-powered processing.
Offers serverless, pay-per-use scaling.
Available across OCI, AWS, Google Cloud, and Microsoft Azure.
Designed to interoperate with Iceberg ecosystems, including data managed through platforms such as Databricks and Snowflake.

5.2 Apache Iceberg support

Query open-format data-lake tables.
Combine lakehouse data with operational database data.
Store vectors in Iceberg tables.
Create vector indexes over supported Iceberg vector data.
Apply vector similarity and structured business predicates in one workflow.
Reduce forced movement of lakehouse data into proprietary formats.

5.3 Converged analytics

SQL analytics over relational data.
JSON analytics.
Graph analytics.
Spatial analytics.
Text search.
Vector similarity search.
In-database machine learning.
Lakehouse analytics.

The advantage is that these workloads can be combined in a single database platform rather than requiring a separate specialized database for each data model.

6. Performance and Scalability

6.1 Wide tables

Maximum table or view width increased to 4,096 columns.
Useful for:
- Machine-learning feature stores
- IoT data
- De-normalized analytical models
- Large packaged applications
Can simplify designs that previously had to split attributes across multiple tables.

6.2 Hybrid Columnar Compression improvements

Faster compression and decompression.
Improved compression ratios for newly created or rebuilt HCC tables.
Potentially reduced storage and I/O requirements.
Exact benefit depends on the data and compression level.

6.3 Consolidated background services

Consolidates database maintenance and service actions into a more flexible group of background processes.
Reduces dependence on many dedicated background processes.
Improves process and resource management.
Particularly relevant for consolidated environments with many PDBs.

6.4 Concurrency improvements

Lock-Free Reservations reduce row-level contention.
Priority Transactions protect important transactions.
JSON-Relational Duality supports optimistic concurrency.
True Cache offloads read workloads.
RAC and sharding provide scale-out options.
Improved HCC reduces analytical storage and I/O overhead.

7. Database Architecture and Data Management

7.1 Multitenant architecture

Oracle 26ai continues the CDB/PDB architecture for:

Database consolidation
Tenant isolation
Rapid provisioning and cloning
PDB relocation
Centralized patching and administration
Resource governance
Fleet-standardized operations
PDB-level availability and disaster-recovery patterns

7.2 Converged data model

A single Oracle Database can manage:

Relational data
JSON documents
Vectors
Property graphs
Spatial data
XML
Text
Time-series-style data
Blockchain and immutable tables
Large objects and multimedia content

Oracle positions this converged model as a way to eliminate separate data stores and synchronization pipelines for each data type.

7.3 Immutable and blockchain-style data protection

Append-only and tamper-resistant data-management patterns.
Useful for audit trails, financial records, compliance records, and chain-of-custody requirements.
Complements auditing and enterprise security controls.

7.4 Automatic storage and lifecycle functions

Partitioning
Compression
Heat Map
Automatic Data Optimization
Online table and index operations
SecureFiles
Recovery Manager
Flashback technologies

These are established capabilities carried into 26ai rather than completely new 26ai inventions.

8. Manageability, DevOps, and Operations

8.1 Simplified transition from 23ai

Oracle 26ai replaces the 23ai product identity.
Existing 23ai installations transition by applying the October 2025 Release Update or a later relevant RU.
No traditional database upgrade is required for the 23ai-to-26ai transition.
No application recertification is required solely because of the product-name transition. 8.2 Container support and Free edition

Oracle AI Database 26ai Free is available as a container image.
Full and Lite image variants are available.
Lite images reduce image size and improve pull time for simpler development and CI/CD scenarios.
Container settings can enable Archive Log mode and Force Logging.
Useful for local development, testing, training, and automated pipelines.

8.3 Observability and automation

Improved machine-readable administration output.
Integration with Oracle Enterprise Manager and cloud management services.
Data Guard Broker automation.
Fleet Patching and Provisioning.
Automatic performance diagnostics.
Automatic Workload Repository.
SQL Monitor and SQL tuning facilities.
Data Safe integration for security assessment and monitoring.

8.4 Autonomous operations

Depending on the chosen Autonomous Database service:

Automated configuration
Automated backup
Automated patching
Elastic scaling
Automatic tuning
Automatic indexing
Built-in availability management
Serverless consumption models
Autonomous AI Lakehouse capabilities

9. Quick Category Summary

Category	Most important Oracle 26ai capabilities
AI	AI Vector Search, Unified Hybrid Vector Search, Select AI, Select AI Agent, MCP, Private Agent Factory, RAG, ONNX embeddings
Application development	JSON-Relational Duality, JavaScript procedures, SQL/PGQ graphs, annotations, domains, assertions, SQL enhancements
Microservices	Sagas, TxEventQ Kafka APIs, lock-free reservations, REST/JSON APIs, priority transactions
High availability	RAC, Data Guard, Active Data Guard, Application Continuity, Transaction Guard, True Cache
Distributed database	Sharding, directory-based sharding, Raft replication, automatic data movement
Security	Deep Data Security, SQL Firewall, schema privileges, developer role, MFA, TLS 1.3, OAuth2, Entra ID
Analytics	Autonomous AI Lakehouse, Apache Iceberg, graph, spatial, JSON, vector and SQL analytics
Performance	True Cache, HCC enhancements, wide tables, lock-free concurrency, RAC and sharding
Manageability	Multitenant, autonomous operations, containers, fleet tooling, 23ai-to-26ai RU transition

Highest-Priority Features

For architecture evaluation or an Oracle 19c-to-26ai roadmap:

AI Vector Search and enterprise RAG
JSON-Relational Duality Views
Oracle Deep Data Security
Oracle SQL Firewall
True Cache
Data Guard and Application Continuity enhancements
Lock-Free Reservations and Priority Transactions
Schema-level privileges and DB_DEVELOPER_ROLE
TxEventQ Kafka APIs and microservice Sagas
Autonomous AI Lakehouse and Apache Iceberg
Operational Property Graphs with SQL/PGQ
Globally Distributed Database with Raft replication

Important licensing note

Feature availability is not identical across Free, Standard Edition 2, Enterprise Edition, Exadata, Autonomous Database, and cloud database services. Some capabilities are included in the base database, while RAC, Active Data Guard, Partitioning, Advanced Security, Database Vault, and other functions may require a specific edition, option, engineered system, or cloud service entitlement. Always validate the exact deployment against the current Oracle Licensing Information User Manual and your Oracle ordering documents.

Friday, August 21, 2026

If Database and Application running in different region in AWS Cloud ?

If Database and Application on different region ?

1. Highest-priority recommendation

Move the application and primary database into the same AWS Region

For an interactive application, the writer database should normally be in the same Region as the application services.

Preferred architecture

Users

Application / API: Ireland eu-west-1

| Low-latency regional connection

RDS MySQL Primary: Ireland eu-west-1

| Asynchronous cross-Region replication

Read Replica / DR: Oregon us-west-2

Possible approaches:

Move RDS MySQL primary to Ireland
- Best option if the main application and most users are closer to Ireland.
- Keep an Oregon cross-Region read replica for disaster recovery if required.
Move the application to Oregon
- Appropriate if Oregon must remain the primary data Region due to data residency, integrations, or operational requirements.
Create a read replica in Ireland
- Route eligible read-only queries to the local replica.
- Writes still travel to Oregon.
- Replication is asynchronous, so the application must tolerate replication lag and eventual consistency.
- RDS supports cross-Region read replicas for supported RDS for MySQL versions.
If both Regions must actively serve users
- Consider an architecture designed for multi-Region access.
- Separate regional reads from globally coordinated writes.
- Do not assume that a standard RDS MySQL primary can provide low-latency writes from both Regions.

Important: Increasing CPU, memory, IOPS, or buffer pool will not remove the physical cross-Region round-trip delay.

2. Diagnose where the response time is being spent

Before changing parameters, separate the latency into four parts:

Total request time

= application processing

+ connection acquisition

+ network round trips

+ database query execution

Step 1: Measure from the Ireland application host

Run tests from the actual EC2 instance, ECS task, EKS pod, or other compute environment hosting the application.

DNS resolution

dig your-rds-endpoint.rds.amazonaws.com

Check:

DNS lookup duration
Whether the endpoint resolves consistently
Whether the application is caching an obsolete IP address

AWS recommends keeping cached RDS DNS TTL below 30 seconds because the database IP can change after failover.

TCP connection time

time nc -vz your-rds-endpoint.rds.amazonaws.com 3306

TLS and MySQL login time

time mysql

--host=your-rds-endpoint.rds.amazonaws.com

--port=3306

--user=test_user

--password

--ssl-mode=REQUIRED

-e "SELECT 1;"

Run this several times:

for i in $(seq 1 10); do

time mysql

--host=your-rds-endpoint.rds.amazonaws.com

--user=test_user

--password='PASSWORD'

--ssl-mode=REQUIRED

-e "SELECT 1;"

done

Do not expose production passwords in shell history. Use this only with a temporary diagnostic account or a secure credentials mechanism.

Compare against an Ireland test database

For a strong comparison:

Create a temporary small RDS MySQL instance in Ireland.
Run the same SELECT 1 and representative application query.
Compare:
- Connection-establishment time
- Single-query duration
- API response time
- Transaction duration

If the Ireland database is significantly faster, cross-Region latency is the major contributor.

3. Count database round trips per application request

This is often the biggest application-side problem.

Suppose one API request runs 20 sequential SQL operations. Even if each query needs only 5 ms of database execution time, every query also waits for a cross-Region network round trip.

Look for:

N+1 query patterns
Queries executed inside loops
One query per object or per UI row
Separate queries for data that could be joined
Auto-commit after every statement
Repeated lookup queries
Multiple existence checks
ORM lazy loading
Opening a new connection for every query
Chatty stored-procedure calls
Small inserts executed one row at a time

Application improvements

Replace repeated queries with set-based operations

Instead of:

SELECT name FROM customer WHERE id = 101;

SELECT name FROM customer WHERE id = 102;

SELECT name FROM customer WHERE id = 103;

Use:

SELECT id, name

FROM customer

WHERE id IN (101, 102, 103);

Use bulk inserts

Instead of executing one insert at a time:

INSERT INTO audit_log (event_type, created_at)

VALUES

('LOGIN', NOW()),

('SEARCH', NOW()),

('LOGOUT', NOW());

Fetch only required data

Avoid:

SELECT *

FROM orders;

Prefer:

SELECT order_id, customer_id, status, created_at

FROM orders

WHERE customer_id = ?

ORDER BY created_at DESC

LIMIT 50;

Reduce transaction round trips

Group related statements into one correctly scoped transaction:

START TRANSACTION;

UPDATE account

SET balance = balance - ?

WHERE account_id = ?;

INSERT INTO transaction_history

(account_id, amount, transaction_type)

VALUES

(?, ?, 'DEBIT');

COMMIT;

Do not make transactions unnecessarily long, because they can retain locks and old row versions.

4. Enable RDS observability

Database Insights or Performance Insights

Enable the applicable RDS database-performance monitoring capability and investigate:

DB load
Top SQL
Wait events
Top users
Top client hosts
Calls per second
Average latency
Rows examined
Lock waits
I/O waits

Performance Insights can display database load and top SQL, and with MySQL Performance Schema enabled it provides detailed wait-event and per-SQL information.

Prioritize SQL based on:

Total database time = executions × average execution time

Do not optimize only the single slowest query. A query taking 50 ms but running 100,000 times might consume more database capacity than one query taking 10 seconds once per day.

Enable Enhanced Monitoring

Review operating-system-level data, including:

CPU utilization
Load average
Free memory
Swap usage
Disk queue depth
Read and write IOPS
Read and write throughput
Process and thread activity

CloudWatch metrics to review

At minimum, review:

CPUUtilization
FreeableMemory
SwapUsage
DatabaseConnections
ReadLatency
WriteLatency
ReadIOPS
WriteIOPS
ReadThroughput
WriteThroughput
DiskQueueDepth
FreeStorageSpace
NetworkReceiveThroughput
NetworkTransmitThroughput
BurstBalance, if applicable
ReplicaLag, if read replicas are used

AWS recommends monitoring memory, CPU, replica lag, and storage usage, setting CloudWatch notifications, and maintaining sufficient storage and memory headroom.

Enable slow-query logging

In an RDS parameter group, evaluate:

slow_query_log = 1

long_query_time = 1

log_output = FILE

Start with long_query_time around one second, observe the volume, and reduce it later if required. Do not immediately enable extensive logging on a heavily loaded production system without monitoring the overhead and log volume.

Useful additional setting during controlled investigation:

log_queries_not_using_indexes = 1

Use this temporarily and carefully. A query that does not use an index is not automatically bad, especially for small tables, and this setting can generate substantial logging.

5. Analyze and tune the SQL

For each high-impact SQL statement:

EXPLAIN ANALYZE

SELECT ...;

Check for:

Full table scans on large tables
Large differences between estimated and actual rows
Excessive rows examined
Filesort
Temporary tables
Nested-loop amplification
Incorrect join order
Functions applied to indexed columns
Implicit datatype conversion
Leading-wildcard searches
Unnecessary DISTINCT
Large OFFSET values
Missing filters
Sorting without a supporting index

Common non-sargable pattern

Avoid:

WHERE DATE(created_at) = '2026-08-21'

Prefer:

WHERE created_at >= '2026-08-21 00:00:00'

AND created_at < '2026-08-22 00:00:00'

The second form gives MySQL a better opportunity to use an index on created_at.

Avoid deep offset pagination

Potentially expensive:

SELECT order_id, created_at

FROM orders

ORDER BY created_at DESC

LIMIT 50 OFFSET 500000;

Prefer keyset pagination:

SELECT order_id, created_at

FROM orders

WHERE created_at < ?

ORDER BY created_at DESC

LIMIT 50;

6. Review index design

Create indexes based on real query patterns, not simply on individual columns.

Composite index example

For:

SELECT order_id, status, created_at

FROM orders

WHERE customer_id = ?

AND status = ?

ORDER BY created_at DESC

LIMIT 50;

A possible index is:

CREATE INDEX idx_orders_customer_status_created

ON orders (customer_id, status, created_at DESC);

Validate with EXPLAIN ANALYZE before and after the change.

Index review checklist

Index join columns.
Index highly selective filter columns.
Match composite-index order with equality predicates, ranges, and sorting.
Check whether existing indexes are redundant.
Avoid duplicate indexes.
Avoid indexing every column.
Consider write overhead before adding indexes.
Keep primary keys compact when possible.
Check foreign-key columns for appropriate indexes.
Review table and index size.
Refresh optimizer statistics when needed.

For large production tables, plan index creation carefully because it can consume I/O, increase replication lag, or affect application performance.

7. Fix connection management

Cross-Region database connections are particularly expensive to establish because TCP, TLS, and authentication can require multiple network exchanges.

Application connection-pool settings

Review:

Minimum pool size
Maximum pool size
Connection acquisition timeout
Idle timeout
Maximum connection lifetime
Validation query behavior
Connection leak detection
Number of application instances
Number of worker threads
Autoscaling maximum

Calculate the theoretical maximum:

Total possible connections

= number of application instances

× maximum connections per instance

× number of application pools

Keep this below the RDS capacity with sufficient headroom for:

DB administrators
Monitoring agents
Migrations
Background jobs
Failover and recovery
Traffic spikes

Do not increase max_connections blindly. Every connection consumes database resources, and too many active connections can increase scheduling and memory pressure.

Consider RDS Proxy

RDS Proxy can pool and reuse established database connections, reducing the CPU and memory overhead associated with rapid connection creation. It is particularly useful for Lambda, container autoscaling, connection storms, and applications that frequently open and close connections.

Monitor these proxy metrics:

Client connections
Database connections
Connection borrow latency
Maximum allowed database connections
Connection pinning behavior

RDS Proxy exposes controls such as IdleClientTimeout, MaxConnectionsPercent, MaxIdleConnectionsPercent, and ConnectionBorrowTimeout; AWS recommends retaining connection headroom rather than using the database connection limit completely.

RDS Proxy addresses connection overhead and bursts. It does not eliminate the Oregon-to-Ireland latency for SQL execution.

8. Review RDS instance capacity

Check whether the instance class matches the workload.

CPU

If CPU is consistently high:

Identify top SQL first.
Check query plans and indexes.
Review connection concurrency.
Check batch workloads.
Scale to a larger or more appropriate instance class only after identifying the cause.

Memory

Look for:

Low FreeableMemory
Increasing SwapUsage
Buffer-pool misses
Frequent physical reads
Per-connection memory growth
Excessive temporary tables

A larger memory-optimized instance can help if the active data set does not fit into memory, but it will not fix missing indexes or chatty application behavior.

Burstable instances

If using a db.t3 or db.t4g class, review CPU-credit behavior and confirm that sustained CPU demand is appropriate for a burstable class. For steady production workloads, a non-burstable instance may provide more predictable performance.

9. Review storage performance

Check:

Storage type
Provisioned IOPS
Provisioned throughput
Read and write latency
Disk queue depth
Workload peaks
Temporary-table I/O
Available storage
Backup and maintenance windows

AWS recommends increasing I/O capability by moving to an instance class with higher I/O capacity, changing from general-purpose SSD to Provisioned IOPS where appropriate, or provisioning additional IOPS and throughput.

For demanding workloads, gp3 allows storage performance to be provisioned separately within applicable service limits, while Provisioned IOPS storage provides more predictable performance for latency-sensitive workloads.

Schedule these during low-traffic periods:

Automated backups
Snapshots
Large batch processing
Data archival
Index maintenance
Schema changes
ETL jobs

10. MySQL parameter tuning

Use a custom DB parameter group, change one category at a time, and perform load testing before production rollout.

High-value parameters to review

InnoDB buffer pool

innodb_buffer_pool_size

On RDS, the default may already be calculated from instance memory, so do not automatically hard-code an internet-recommended percentage. Confirm:

Database instance memory
Buffer pool size
Working-set size
Buffer-pool hit rate
Freeable memory
Swap utilization

Temporary tables

Review:

tmp_table_size

max_heap_table_size

Also inspect:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

A high number of disk temporary tables can indicate:

Insufficient limits
Large sorts or grouping
TEXT or BLOB data
Poor indexing
Inefficient SQL

Increasing limits raises potential per-connection memory consumption, so calculate the concurrency impact first.

Connection limits

max_connections

thread_cache_size

wait_timeout

interactive_timeout

Tune these together with the application pool, not independently.

Durability-sensitive parameters

innodb_flush_log_at_trx_commit

sync_binlog

Changing these can improve write performance but can also change the amount of committed data at risk during a failure. Do not reduce durability without documented recovery-point approval, business sign-off, and failover testing.

Redo and transaction behavior

Depending on the supported MySQL version, review redo log configuration, checkpoint pressure, transaction size, and write bursts. Make sure long transactions are not retaining undo history or blocking purge activity.

11. Network and security checks

Prefer private connectivity

Use a supported private design such as:

Inter-Region VPC peering for simpler connectivity
Transit Gateway inter-Region peering for a larger hub-and-spoke environment

VPC peering can connect VPCs in different Regions using private IP addresses, and the traffic does not traverse the public internet.

Transit Gateway is useful when centralized and transitive routing is needed across several VPCs, whereas basic VPC peering requires direct connections and does not provide transitive routing.

Check:

VPC route tables
Security groups
Network ACLs
DNS resolution across VPCs
NAT gateway path
Firewall and inspection path
Packet loss
TCP retransmissions
MTU mismatch
Accidental routing through on-premises infrastructure
Cross-Region data-transfer cost

Private connectivity improves security and routing consistency, but it does not remove geographic latency.

12. Application caching

Cache frequently requested, slowly changing data using:

ElastiCache for Redis or Valkey
Application memory cache
CDN for static or cacheable responses
Precomputed summary tables
Materialized application-level aggregates

Good cache candidates:

Product catalog
Configuration
Reference data
User permissions, with careful invalidation
Frequently accessed reports
Dashboard aggregates
Search filters

Define:

Cache key
TTL
Invalidation strategy
Maximum staleness
Cache-miss behavior
Protection against cache stampede

Caching should reduce database calls, not conceal incorrect queries or stale-data risks.

13. Read/write separation

If the workload is read-heavy:

Application in Ireland

+-- Writes and strongly consistent reads --> Oregon primary

+-- Eligible read-only queries ----------> Ireland read replica

Suitable for the local replica:

Reports
Search
Product catalog
Historical data
Dashboards that tolerate slightly stale data

Keep on the writer:

Payment or order confirmation
Read-after-write workflows
Inventory reservation
Account-balance checks
Any workflow that requires immediate consistency

Monitor:

ReplicaLag

Read replica CPU

Read latency

Write throughput on source

Long-running replica queries

Replication errors

14. Recommended implementation plan

Phase 1: Immediate, one to two days

Confirm the exact AWS Regions and network path.
Measure TCP, TLS, login, and SELECT 1 time from Ireland.
Add application tracing for:
- Total request duration
- Connection wait time
- SQL duration
- SQL call count
Enable Database Insights or Performance Insights.
Enable Enhanced Monitoring.
Review CloudWatch CPU, memory, connections, IOPS, latency, and queue depth.
Enable controlled slow-query logging.
Identify the top 10 SQL statements by total database time.
Check connection-pool configuration.
Find N+1 queries and repeated sequential calls.

Phase 2: Short term, one to two weeks

Tune the highest-impact SQL.
Add validated composite indexes.
Batch inserts, updates, and reads.
Reduce ORM-generated round trips.
Correct connection-pool sizing.
Introduce RDS Proxy if connection churn or autoscaling is a problem.
Add application caching.
Right-size the instance and storage.
Tune parameters based on observed wait events.
Run repeatable load tests.

Phase 3: Architecture correction

Move the DB writer to Ireland, or move the application to Oregon.
If migration is not immediately possible, create an Ireland read replica.
Route stale-tolerant read traffic to the Ireland replica.
Retain the other Region for disaster recovery.
Test failover, replica promotion, DNS changes, and recovery procedures.
Track both performance and cross-Region data-transfer cost.

15. Success metrics

Define measurable targets before tuning:

Metric	Suggested objective
API p50 latency	Establish baseline, then reduce
API p95/p99 latency	Main user-experience target
DB connection acquisition	Stable and small
SQL calls per API request	Reduce chatty patterns
Top SQL average latency	Reduce based on query class
Rows examined per row returned	Reduce substantially
CPU utilization	Maintain healthy peak headroom
Freeable memory	Avoid sustained memory pressure
Swap usage	Ideally zero or consistently minimal
Database connections	Below safe capacity
Read/write latency	Stable without peak spikes
Disk queue depth	Appropriate for provisioned storage
Replica lag	Within application staleness limit
Error/timeout rate	Near zero under expected peak load

Bottom line

Work in this order:

1. Put application and writer DB in the same Region

2. Measure network and connection time

3. Reduce SQL round trips

4. Tune top SQL and indexes

5. Correct connection pooling

6. Add caching and local read capacity

7. Right-size RDS compute and storage

8. Tune MySQL parameters using observed evidence

For your setup, moving the primary database to Ireland, or moving the application to Oregon, will likely deliver a larger improvement than parameter tuning alone. Until that is possible, reduce sequential database calls, reuse connections, and place a read replica in Ireland for stale-tolerant reads.