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:

1. 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.

2. Move the application to Oregon

   • Appropriate if Oregon must remain the primary data Region due to data residency, integrations, or operational requirements.

3. 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. 

4. 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 </span>

--host=your-rds-endpoint.rds.amazonaws.com </span>

--port=3306 </span>

--user=test_user </span>

--password </span>

--ssl-mode=REQUIRED </span>

-e "SELECT 1;"

Run this several times:

for i in $(seq 1 10); do

time mysql </span>

--host=your-rds-endpoint.rds.amazonaws.com </span>

--user=test_user </span>

--password='PASSWORD' </span>

--ssl-mode=REQUIRED </span>

-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:

1. Create a temporary small RDS MySQL instance in Ireland.
2. Run the same SELECT 1 and representative application query.
3. 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:

1. Identify top SQL first.
2. Check query plans and indexes.
3. Review connection concurrency.
4. Check batch workloads.
5. 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

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

Phase 2: Short term, one to two weeks

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

Phase 3: Architecture correction

1. Move the DB writer to Ireland, or move the application to Oregon.
2. If migration is not immediately possible, create an Ireland read replica.
3. Route stale-tolerant read traffic to the Ireland replica.
4. Retain the other Region for disaster recovery.
5. Test failover, replica promotion, DNS changes, and recovery procedures.
6. 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.

 

Oracle Database Patches and Enterprise Patching Policy

Below is a practical review of Oracle Database patch types, followed by a step-by-step patching policy suitable for an enterprise database estate. The approach emphasizes security, availability, rollback capability, audit evidence, RAC/Data Guard coordination, and repeatable automation.

Important: Patch instructions differ by Oracle release, operating system, architecture, and deployment model. The patch README and applicable My Oracle Support, or MOS, notes must remain the final authority for every implementation.

---

1. Oracle patching concepts

Oracle patch maintenance falls into two broad categories:

Proactive maintenance

Proactive patching applies Oracle-recommended cumulative updates before a known problem affects the environment. For Oracle Database 19c and later, the principal proactive mechanisms are quarterly Release Updates and, where supported, Monthly Recommended Patches. 

Reactive maintenance

Reactive patching addresses a particular defect or urgent problem, usually through an interim or one-off patch. These patches are produced for a specific bug, database version, platform, and configuration, and may later be included in an RU. 

Recommended policy: Use RUs and supported MRPs as the standard maintenance path. Use one-off patches only when required by Oracle Support, a documented critical defect, or an approved security exception.

---

2. Oracle Database patch types

2.1 Release Update, or RU

An RU is Oracle’s primary cumulative quarterly database patch bundle for currently supported database releases.

It generally includes:

• Security fixes
• Optimizer and database engine fixes
• Reliability and availability fixes
• Data Guard, RAC, ASM, RMAN, and other component fixes
• Fixes from previous RUs
• SQL changes that may require datapatch

Oracle no longer delivers traditional patch sets for current releases. Quarterly RUs are the normal proactive maintenance mechanism. 

Recommended usage

• Make the RU the baseline patch for all production databases.
• Do not remain indefinitely on the base release, such as 19.3.
• Prefer a recent and internally certified RU.
• Avoid allowing different databases in the same service stack to drift across many RU levels.
• Patch Grid Infrastructure and database homes according to the combination and sequence prescribed in the patch README.

Example version interpretation

For a database showing a version such as:

19.28.0.0.0

``

19 represents the major release family and 28 identifies the RU level.

---

2.2 Monthly Recommended Patch, or MRP

An MRP provides Oracle-recommended fixes on top of the current RU. Oracle introduced MRPs as a more frequent proactive maintenance option, initially for Oracle Database 19c on Linux x86-64. Availability must therefore be verified for the exact database release and operating-system platform. 

Recommended usage

Use MRPs when:

• The platform and release support them.
• The organization can test monthly maintenance.
• A recommended fix is needed before the next quarterly RU.
• Security or operational risk justifies a monthly cadence.

Suggested policy

• Critical internet-facing or high-risk systems: Evaluate each applicable MRP.
• Standard production systems: Quarterly RU as the minimum; use an MRP where risk analysis identifies a need.
• Low-criticality systems: Keep aligned with the approved RU baseline.

MRPs should not be treated as a substitute for moving to the next quarterly RU.

---

2.3 Critical Patch Update, or CPU

A CPU is Oracle’s security advisory and security-fix delivery program across Oracle products. CPUs normally occur on the third Tuesday of January, April, July, and October. The advisory identifies affected products, vulnerabilities, severity, affected versions, and links to patch availability documentation.

For Oracle Database, the security fixes announced in a CPU are normally delivered through the applicable database patch bundle, commonly the RU, rather than as a completely separate database maintenance strategy.

Policy implication

When a CPU is released:

1. Security must review the advisory.
2. The DBA team must identify affected versions and components.
3. The team must download the relevant Patch Availability Document from MOS.
4. Risk must be assessed using:
   • CVSS score
   • Remote exploitability
   • Authentication requirement
   • Exposure of the listener or database service
   • Usage of affected components
   • Availability of mitigation
5. Applicable patches must enter expedited testing.

Oracle advises customers to use actively supported releases and apply security patches without delay. 

---

2.4 Security Patch Update, or SPU

SPU is historically associated with the security-only database patch stream and may still appear in MOS references, older releases, and patch-selection documentation. It contains a narrower set of fixes than a full proactive bundle.

Recommended usage

For modern supported databases, an RU should generally be preferred because it provides cumulative security and reliability fixes. Select an SPU only when:

• It is the applicable Oracle-supported delivery method for that release or platform.
• Oracle Support directs the organization to use it.
• An approved exception prevents adoption of the RU.

Do not mix RU, PSU, BP, or SPU streams without checking the README and MOS conflict guidance.

---

2.5 Patch Set Update, or PSU

PSUs are cumulative patch bundles used primarily with older Oracle release families. They include security fixes and selected high-impact fixes.

For 12.2 and later release families, Oracle moved to the RU model. Older databases may still have PSU or Bundle Patch terminology. 

Policy implication

A database that depends on PSUs should be classified as a legacy platform and placed on an upgrade or retirement roadmap.

---

2.6 Bundle Patch, or BP

A Bundle Patch groups fixes for a specific platform or product configuration. The term is commonly encountered with:

• Older Windows database releases
• Engineered systems
• Grid Infrastructure
• Specific components or products

Bundle Patches are normally cumulative within their patch stream.

Policy implication

Never assume that a BP, PSU, SPU, and RU are interchangeable. The DBA must confirm the correct patch stream for the product, release, and platform in MOS.

---

2.7 Interim or one-off patch

A one-off patch fixes a particular Oracle bug. It is usually associated with:

• An Oracle Service Request
• A specific bug number
• A particular RU level
• A particular operating-system platform

One-off patches are reactive and may conflict with an RU, MRP, OJVM patch, or another one-off patch. Oracle therefore requires interim patch conflict analysis before maintenance.

Recommended control

Every one-off patch should have:

• Oracle SR number
• Bug number
• Business justification
• Patch ID
• Applicable RU
• Platform
• Conflict-check result
• Expiry or removal plan
• Confirmation whether the fix is included in a later RU

One-offs should not become permanent undocumented dependencies.

---

2.8 Oracle JavaVM, or OJVM patch

An OJVM patch addresses vulnerabilities and defects in the Java Virtual Machine component installed inside Oracle Database.

Important considerations

• Determine whether OJVM is installed and used.
• Review whether the patch supports rolling or requires non-rolling maintenance.
• Check for Java-dependent applications and invalid objects.
• Run all required SQL patching steps.
• Validate JAVAVM and related components after patching.

Do not assume a database RU automatically resolves every separately delivered OJVM requirement. Always inspect the RU and OJVM README.

---

2.9 Grid Infrastructure Release Update

A Grid Infrastructure RU patches components such as:

• Clusterware
• Oracle Restart
• ASM
• ACFS
• Cluster communication components
• GI-managed listeners and resources

For RAC environments, OPatchAuto can orchestrate prerequisite checks, stopping and starting services, patch application, post-checks, and rollback. Oracle recommends Fleet Patching and Provisioning for larger RAC, Exadata, and Data Guard estates.

Key rule

The Grid home and database homes are separate software inventories. Both need to be assessed and patched where applicable.

---

3. Recommended enterprise patching policy

3.1 Patch cadence

Environment	Target cadence	Suggested completion target
Sandbox / laboratory	As soon as patch is available	3 to 5 business days
Development	Every quarterly RU	Within 7 to 10 days
Test / SIT	Every quarterly RU	Within 14 days
UAT / pre-production	Every quarterly RU	Within 21 days
Critical production	Every quarterly RU	Within 30 days
Standard production	Every quarterly RU	Within 30 to 45 days
Security emergency	Out-of-band	Based on risk, normally 24 hours to 7 days
Supported MRP candidates	Monthly assessment	Risk-based

Oracle’s CPU calendar is quarterly, but Security Alerts can be published outside the normal schedule for particularly critical vulnerabilities or active exploitation. 

---

3.2 Patch currency standard

Use an organizational standard such as:

Production databases must be on the approved current RU or, temporarily, no more than one RU behind. Any database more than one RU behind requires a documented security exception, compensating controls, business-owner approval, and a remediation date.

For highly exposed systems, the organization may adopt a stricter standard:

Internet-facing, regulated, or Tier-0 databases must be moved to the approved current RU within 30 days, or faster when the CPU risk assessment requires it.

---

3.3 Preferred deployment model

Oracle recommends using a new Oracle home and performing out-of-place patching because this simplifies maintenance and reduces the risk associated with modifying the active home.

Out-of-place patching

1. Install or clone a new Oracle home.
2. Apply the approved RU and required one-offs.
3. Validate the new home.
4. Switch the database to the new home.
5. Execute datapatch.
6. Retain the previous home for an approved fallback period.

Why it is better

• Cleaner rollback
• Reduced risk of corrupting the active home
• Repeatable gold-image deployment
• Easier standardization
• Shorter database outage
• Better separation between preparation and cutover

For a large RAC, Exadata, or Data Guard estate, Oracle recommends Fleet Patching and Provisioning. 

---

4. Step-by-step Oracle Database patching procedure

Phase 1: Discovery and scope definition

Step 1: Build the database inventory

Collect:

• Hostname and operating system
• Database name and DB unique name
• Database release and RU
• Oracle home
• Grid home
• RAC or single instance
• CDB and PDB architecture
• Data Guard configuration
• GoldenGate usage
• ASM and ACFS usage
• OJVM installation status
• One-off patches
• Business owner
• Criticality and RTO/RPO
• Maintenance window

Useful discovery commands:

$ORACLE_HOME/OPatch/opatch version

$ORACLE_HOME/OPatch/opatch lsinventory -detail

$ORACLE_HOME/OPatch/opatch lspatches

Database checks:

SELECT banner_full

FROM v$version;

SELECT name, open_mode, database_role

FROM v$database;

SELECT con_id, name, open_mode

FROM v$pdbs

ORDER BY con_id;

SELECT comp_id, comp_name, version_full, status

FROM dba_registry

ORDER BY comp_id;

SELECT patch_id,

patch_type,

action,

status,

action_time,

description

FROM dba_registry_sqlpatch

ORDER BY action_time DESC;

DBA_REGISTRY_SQLPATCH records SQL patch apply and rollback attempts, status, patch type, time, and log location, and is maintained by datapatch.

---

Phase 2: Patch selection

Step 2: Select the target RU

Use MOS as the patch source of truth. Search by:

• Product
• Release
• Platform
• Patch type
• Language, if applicable

Download:

• Database RU
• Grid Infrastructure RU
• OJVM patch, if required
• Latest supported OPatch version
• Required one-off or merge patches
• Patch README
• Known-issues notes

Oracle recommends obtaining patches through MOS and reviewing the exact README for downloading, prerequisites, application, and post-patch instructions.

Step 3: Review known issues

Check:

• RU known issues
• Platform-specific defects
• Data Guard and RAC restrictions
• OJVM restrictions
• Optimizer changes
• RMAN issues
• Data Pump issues
• GoldenGate compatibility
• Application certification
• Required post-install fixes
• Superseded one-offs

If a new RU has a serious known issue for your configuration, choose the preceding approved RU plus the required correction, but document the decision.

---

Phase 3: Conflict and readiness analysis

Step 4: Verify OPatch

$ORACLE_HOME/OPatch/opatch version

$ORACLE_HOME/OPatch/opatch lsinventory

Use the OPatch version specified in the patch README. Oracle recommends using the latest applicable OPatch release. 

Step 5: Run conflict analysis

For a database home:

$ORACLE_HOME/OPatch/opatch prereq </span>

CheckConflictAgainstOHWithDetail </span>

-phBaseDir /stage/patch_directory

For a system patch or GI/RAC patch:

$GRID_HOME/OPatch/opatchauto apply </span>

/stage/patch_directory </span>

-analyze

Resolve conflicts by:

• Removing an obsolete one-off
• Obtaining a replacement one-off for the target RU
• Requesting a merge patch
• Raising an Oracle SR
• Moving to a later RU that already includes the fix

Do not proceed with an unresolved conflict.

Step 6: Verify disk space

Check:

• Oracle home
• Grid home
• Central inventory
• Patch stage
• /tmp
• Database filesystem
• Archive log destination
• FRA
• ASM disk groups

Oracle’s maintenance guidance explicitly requires system dependency and free-space checks before patch application.

---

Phase 4: Backup and recovery preparation

Step 7: Prepare rollback capability

At minimum:

• Current RMAN backup
• Validated restore capability
• Control-file and SPFILE backup
• Oracle home backup or retained old home
• Grid home backup, where applicable
• Central inventory backup
• Listener and network configuration backup
• Password file backup
• Wallet and TDE keystore backup
• OCR and voting-disk health check for RAC
• Data Guard synchronization check
• Recovery runbook

Oracle strongly recommends backing up Oracle home binaries, Grid home binaries, and the central Oracle inventory before applying an RU or interim patch. 

Example RMAN preparation:

BACKUP DATABASE PLUS ARCHIVELOG;

BACKUP CURRENT CONTROLFILE;

BACKUP SPFILE;

RESTORE DATABASE VALIDATE;

A backup is not sufficient unless its restore path has been tested.

---

Phase 5: Rehearsal and approval

Step 8: Patch non-production first

Follow the promotion sequence:

Sandbox -> Development -> SIT -> UAT -> Pre-production -> Production

Test:

• Database startup and shutdown
• Application connectivity
• Critical SQL
• Batch processes
• RMAN backup and restore
• Data Guard transport and apply
• RAC service relocation
• Listener registration
• OEM monitoring
• GoldenGate replication
• Data Pump
• Scheduler jobs
• OJVM applications
• Performance baselines

Step 9: Conduct change review

The change record should contain:

• Patch IDs
• Source and target RU
• Affected systems
• README
• Conflict report
• Test evidence
• Backup evidence
• Implementation plan
• Outage estimate
• Rollback criteria
• Rollback steps
• Business validation plan
• DBA, application, infrastructure, security, and service-owner contacts

---

Phase 6: Production implementation

Step 10: Complete pre-patch health checks

Check for:

• Invalid database components
• Invalid objects
• Failed scheduler jobs
• Tablespace issues
• FRA pressure
• Archive destinations
• Data Guard lag
• RAC resource state
• Blocking transactions
• Backup failures
• Existing alert-log errors

Example queries:

SELECT comp_id, comp_name, version_full, status

FROM dba_registry

WHERE status <> 'VALID';

SELECT owner, object_type, COUNT(*)

FROM dba_objects

WHERE status = 'INVALID'

GROUP BY owner, object_type

ORDER BY owner, object_type;

SELECT dest_id, status, error

FROM v$archive_dest_status

WHERE status <> 'INACTIVE';

SELECT job_name, status, actual_start_date, run_duration

FROM dba_scheduler_job_run_details

WHERE actual_start_date > SYSDATE - 1

ORDER BY actual_start_date DESC;

Record existing faults so they are not incorrectly attributed to the patch.

Step 11: Stop or relocate services

Coordinate:

• Application connections
• Connection pools
• Database services
• GoldenGate
• Monitoring
• Backup jobs
• Batch jobs
• Data Guard Broker
• RAC services

When GoldenGate is used, Oracle’s patch-maintenance guidance says its processes must be shut down before patching the database. 

Step 12: Apply the binary patch

The exact command must come from the patch README.

Typical single-instance in-place pattern:

cd $ORACLE_HOME/OPatch

./opatch apply

Typical GI/RAC pattern:

$GRID_HOME/OPatch/opatchauto apply /stage/patch_directory

OPatch applies and rolls back patches in an Oracle home. OPatchAuto can perform prechecks, stop and start resources, apply patches, conduct post-checks, and perform rollback orchestration. 

Step 13: Run datapatch

After the database and required PDBs are open in the mode prescribed by the README:

$ORACLE_HOME/OPatch/datapatch -verbose

When OPatch is used for database maintenance, datapatch must be run to load applicable SQL changes into the database. 

For multitenant environments:

• Confirm all required PDBs are open.
• Confirm each PDB receives the SQL patch.
• Check for PDBs that were closed or unavailable during datapatch.
• Rerun datapatch if directed after opening missed PDBs.

---

Phase 7: Validation

Step 14: Validate binary inventory

$ORACLE_HOME/OPatch/opatch lsinventory

$ORACLE_HOME/OPatch/opatch lspatches

In RAC, validate every node and every relevant home.

Step 15: Validate SQL patch registry

SELECT patch_id,

patch_type,

action,

status,

action_time,

description

FROM dba_registry_sqlpatch

ORDER BY action_time DESC;

The expected result is normally:

ACTION = APPLY

STATUS = SUCCESS

Any WITH ERRORS status requires log review and remediation. 

Step 16: Validate database health

SELECT comp_id, comp_name, version_full, status

FROM dba_registry

ORDER BY comp_id;

SELECT owner, object_type, COUNT(*)

FROM dba_objects

WHERE status = 'INVALID'

GROUP BY owner, object_type;

SELECT instance_name, status, database_status

FROM gv$instance;

SELECT name, open_mode

FROM v$pdbs

ORDER BY con_id;

Also review:

• Alert log
• Listener log
• Patch logs
• CRS resources
• ASM state
• Data Guard transport and apply lag
• Application smoke tests
• Critical execution plans
• Backup operation
• Monitoring alerts
• Performance compared with baseline

---

5. Data Guard patching best practices

For Data Guard:

1. Confirm zero or acceptable transport and apply lag.
2. Validate broker configuration.
3. Patch the standby side first where supported.
4. Restart and validate standby apply.
5. Perform switchover if the chosen strategy requires it.
6. Patch the former primary.
7. Validate both directions of the configuration.
8. Run datapatch in accordance with the README and the selected Data Guard procedure.
9. Test failover and service behavior where possible.

Oracle prefers out-of-place maintenance using a new Oracle home and recommends Fleet Patching and Provisioning for Data Guard estates. OPatchAuto remains an available alternative for relevant configurations. 

Do not independently activate standby databases merely to run SQL patching unless the documented procedure specifically requires it.

---

6. RAC patching best practices

For RAC:

• Use rolling patching only if the patch is explicitly certified as rolling.
• Run conflict checks against both Grid and database homes.
• Check opatch lsinventory on every node.
• Confirm CRS resources before and after each node.
• Drain or relocate services before stopping an instance.
• Validate SCAN listeners and local listeners.
• Review service failover and connection-pool behavior.
• Verify the patch inventory is consistent across all nodes.
• Run SQL patching once according to the README, not separately and blindly from every node.

Oracle recommends FPP to simplify RAC maintenance, while OPatchAuto remains an orchestration option. Out-of-place patching with a new home is the preferred maintenance model. 

---

7. Rollback policy

Rollback must be defined before entering the maintenance window.

Typical rollback triggers

• Database cannot start
• RAC resource remains unstable
• Data Guard transport or apply cannot be restored
• datapatch fails and cannot be corrected
• Critical application smoke test fails
• Severe performance regression
• Data corruption symptoms
• Maintenance window is exceeded
• Oracle Support recommends rollback

Preferred rollback sequence for out-of-place patching

1. Stop application access.
2. Return the database configuration to the previous Oracle home.
3. Restart the database from the old home.
4. Roll back SQL changes if they were applied and the README requires it.
5. Validate services and application operation.
6. Record all commands and errors.
7. Raise or update the Oracle SR.

Typical in-place binary rollback may use:

$ORACLE_HOME/OPatch/opatch rollback -id PATCH_ID

System patch rollback may use:

$GRID_HOME/OPatch/opatchauto rollback /stage/patch_directory

Exact rollback commands and SQL sequencing must come from the applicable README.

---

8. Governance, evidence, and KPIs

Required patch evidence

Retain:

• Before-and-after opatch lsinventory
• Before-and-after opatch lspatches
• datapatch logs
• DBA_REGISTRY_SQLPATCH results
• Database component status
• Invalid-object comparison
• Backup record
• Conflict-check output
• Change approval
• Test results
• Application-owner validation
• Alert-log review
• Data Guard or RAC validation
• Rollback decision record
• Updated CMDB

Recommended KPIs

• Percentage of databases on approved RU
• Percentage more than one RU behind
• Median days from RU release to production
• Critical-patch SLA compliance
• Patch success rate
• Rollback rate
• datapatch failure rate
• Inventory mismatch count
• Number of undocumented one-offs
• Number of unsupported database releases
• Number of outstanding security exceptions
• Percentage of restore tests completed

---

9. Recommended policy statement

A concise organization-level policy could be:

Oracle Database environments shall be maintained on an actively supported Oracle release and an approved recent Release Update. Quarterly RUs shall be assessed immediately after release, tested through the defined environment sequence, and deployed to critical production systems within 30 days and standard production systems within 45 days. Critical security vulnerabilities, Security Alerts, and actively exploited issues shall follow the emergency patching process. Out-of-place patching shall be the preferred deployment method. Every patch implementation shall include conflict analysis, current recovery capability, non-production testing, documented rollback criteria, binary and SQL patch validation, application-owner sign-off, and retention of audit evidence.

---

10. Practical recommendation for your database estate

As a Database Architect, I recommend structuring the program around five controls:

1. One approved RU baseline per database release and platform
2. Out-of-place gold-image patching as the default
3. Quarterly release train with monthly security review
4. Central register for one-offs, conflicts, and exceptions
5. Automated evidence collection for SOX and operational audits

The most important architectural improvement is to move away from individually patching every Oracle home manually. Maintain certified gold images containing:

• Required RU
• Approved OJVM patch
• Required one-offs
• Correct OPatch release
• Standard configuration
• Antivirus exclusions, if applicable
• Verification manifest and checksum
• Test and approval reference

This reduces configuration drift, patch conflicts, execution errors, and outage duration while producing stronger audit evidence. Oracle’s current maintenance guidance similarly favors out-of-place patching and recommends FPP for large RAC, Exadata, and Data Guard deployments. 

Review summary

This framework separates proactive, reactive, security, and component-specific patches, then ties each category to a controlled lifecycle. The most important best-practice improvements are adopting quarterly RUs as the standard, preferring out-of-place patching, validating both binary and SQL registries, and treating backup verification and rollback as mandatory entry criteria rather than optional DBA activities.