Learn DBA : A Life Long Learning Experience

ORA-12514: Diagnose the Service, Not the Listener

noreply@blogger.com (Nikhil Kotak) — Tue, 20 Oct 2026 11:15:00 +0000

A database maintenance window had just ended. The instance was open, the listener process was running, and TNSPING returned successfully. Yet the application connection pool continued to report ORA-12514. The first suggestion on the incident bridge was predictable: restart the listener. That would have changed the system before anyone had established what was actually wrong.

ORA-12514 is not a generic network failure. It tells us something specific: the client reached an Oracle listener, but that listener could not map the requested service name to a registered database service. The gap may be in the client connection string, dynamic registration, PDB state, Clusterware service configuration, or database role after a Data Guard transition. Each cause requires a different response.

This guide treats ORA-12514 as a service-routing problem. We will trace the client descriptor, compare it with the listener registry and database service state, determine when ALTER SYSTEM REGISTER is useful, and examine the multitenant, RAC, and Data Guard cases that simple listener checklists often miss.

What ORA-12514 Actually Proves

The listener receives a client request containing a service name and searches its service registry for a handler capable of accepting the connection. If the requested service is absent, the listener returns ORA-12514.

The error therefore confirms two useful facts:

The client successfully reached an Oracle listener.
That listener did not recognize the requested service.

This distinction immediately narrows the investigation. The following error comparison is useful during an outage:

Error	What It Normally Indicates
ORA-12154	The client could not resolve the supplied connect identifier.
ORA-12541	No listener was reachable at the resolved network address.
ORA-12514	The listener was reached, but the requested service was unknown.
ORA-12505	The requested SID was not known to the listener.
ORA-12516 or ORA-12520	The service was known, but no suitable handler was available.
ORA-01017	The request reached the database, but authentication failed.

Restarting the listener for every ORA-12514 can briefly interrupt new connections while leaving the actual problem untouched. It may also remove useful runtime evidence before the diagnosis is complete.

Start With the Client's Actual Connect Descriptor

Do not begin with listener.ora. Begin with the connection information used by the failing client.

Applications frequently use a different Oracle Home, TNS_ADMIN location, DNS record, SCAN address, JDBC URL, or connection-pool configuration from the DBA workstation. A successful manual connection using another alias does not prove that the application configuration is correct.

Where possible, run the following checks from the affected application host:

which sqlplus
sqlplus -V
echo "$TNS_ADMIN"

tnsping PAY_RW

sqlplus -L app_probe@'//db-vip.example.com:1521/pay_rw.example.com'

tnsping displays the resolved address and the network configuration files used by the client. It confirms listener reachability, but it does not prove that the database is running or that the requested service can accept a login. An actual SQL*Plus connection is still required.

For a JDBC Thin client, a service-based URL normally looks like this:

jdbc:oracle:thin:@//db-vip.example.com:1521/pay_rw.example.com

Common application-side causes include:

A PDB service was renamed during migration, but the old JDBC URL remains deployed.
One connection pool uses a SID-style URL while another uses a service name.
A stale tnsnames.ora file is packaged inside an application container.
DNS directs the application to a listener different from the one checked by the DBA.
A load-balanced descriptor contains one outdated address.
Multiple Oracle Homes read different network configuration directories.

Before changing anything, record the host, port, protocol, and requested service used by the failed connection.

Compare Three Sources of Truth

A reliable ORA-12514 investigation compares three different views:

What the client requests.
What the listener has registered.
What the database or Clusterware is currently offering.

Check the Exact Listener

Run the commands against the listener handling the failed endpoint:

lsnrctl status LISTENER
lsnrctl services LISTENER

lsnrctl status confirms endpoints and provides a service summary. lsnrctl services is usually more useful during diagnosis because it displays registered services, instances, and handlers.

Do not assume that the listener is named LISTENER. Verify the listener name, Oracle Home, host, and port. On RAC systems, distinguish between local listeners and SCAN listeners.

Check the Database and Service State

Connect locally as SYSDBA and compare defined services with services that are active in the instance:

SET LINESIZE 220
SET PAGESIZE 100

SELECT instance_name,
status,
database_status FROM   v$instance;

SELECT name,
db_unique_name,
database_role,
open_mode FROM   v$database;

SELECT name,
network_name,
pdb FROM   dba_services ORDER BY name;

SELECT name,
network_name,
pdb FROM   v$active_services ORDER BY name;

-- Run in a multitenant database SELECT name,
open_mode,
restricted FROM   v$pdbs ORDER BY con_id;

DBA_SERVICES shows service definitions, while V$ACTIVE_SERVICES shows services currently active in the instance. The difference between these views is often where the diagnosis becomes clear.

Observation	Likely Investigation Path
Service missing from `DBA_SERVICES`	Wrong client service name or a missing service definition.
Present in `DBA_SERVICES`, but absent from `V$ACTIVE_SERVICES`	Service stopped, PDB closed, or Clusterware policy not satisfied.
Active in the database, but absent from `lsnrctl services`	LREG registration problem or incorrect listener target.
Registered with another listener only	Wrong host, port, VIP, SCAN address, or listener configuration.
Registered correctly, but the application still fails	The application resolves another descriptor or retains stale pool configuration.
Available on only one RAC node	Service placement, preferred-instance policy, or remote registration issue.

How Dynamic Service Registration Works

Modern Oracle releases use the Listener Registration process, LREG, to publish database services. Older explanations often attribute registration to PMON, but LREG is the relevant background process in current releases.

By default, LREG attempts to register with a local listener on TCP port 1521. LOCAL_LISTENER identifies non-default local endpoints, while REMOTE_LISTENER is commonly used for remote endpoints such as RAC SCAN listeners. Dynamic registration normally does not require a static database entry in listener.ora.

Check the current registration-related parameters:

SHOW PARAMETER local_listener
SHOW PARAMETER remote_listener
SHOW PARAMETER service_names

SELECT name,
value FROM   v$parameter WHERE  name IN ('local_listener',
'remote_listener',
'service_names');

If a single-instance database uses a listener on a non-default port, an explicit local listener address may be required:

ALTER SYSTEM SET local_listener =
  '(ADDRESS=(PROTOCOL=TCP)
            (HOST=db-vip.example.com)
            (PORT=1522))'
  SCOPE=BOTH;

ALTER SYSTEM REGISTER;

Confirm the result afterward with lsnrctl services. Do not copy this parameter change blindly into RAC. Clusterware normally manages local listener and SCAN-related configuration, and manual changes can conflict with the cluster configuration.

What ALTER SYSTEM REGISTER Does Not Do

ALTER SYSTEM REGISTER asks LREG to publish the database's currently active services immediately. It does not:

Create a missing service.
Start a stopped service.
Open a PDB.
Correct the client's service name.
Change a Data Guard service role.
Override a disabled Clusterware resource.
Repair an unreachable listener address.

If the service is absent from V$ACTIVE_SERVICES, repeatedly forcing registration will not solve the underlying problem.

Starting with Oracle Database 19c, customer use of the SERVICE_NAMES initialization parameter is deprecated. Application services should be managed through tools such as SRVCTL, GDSCTL, or DBMS_SERVICE. In high-availability environments, SERVICE_NAMES should not be treated as the service-management mechanism.

SERVICE_NAME, SID, and Database Name Are Different

Many ORA-12514 incidents begin with the assumption that the database name, instance SID, and application service must be identical. They represent different layers of the database architecture.

Name	Purpose
`SERVICE_NAME`	Logical workload endpoint used by clients.
`SID`	Identifier for one Oracle database instance.
`DB_NAME`	Database identity recorded in the control file.
`DB_UNIQUE_NAME`	Unique database identity used in configurations such as Data Guard.
PDB name	Name of a pluggable container, which is not necessarily the preferred application service.

A service can run across several RAC instances, relocate during maintenance, or start only when a Data Guard database has a particular role. A SID cannot provide that abstraction.

A good service name should describe workload intent. For example:

orders_rw.example.com for primary read-write traffic.
orders_ro.example.com for reporting on an Active Data Guard standby.
batch.example.com for scheduled processing with separate placement rules.

Using one generic service for every workload makes draining, placement, failover, load balancing, and maintenance considerably harder.

Multitenant Problems: The CDB Is Open but the PDB Is Not

One of the easiest ways to misdiagnose ORA-12514 is to check only V$DATABASE.OPEN_MODE. The CDB may be open while the target PDB remains mounted. A service associated with that PDB may therefore be unavailable even though the instance appears healthy.

For a routine single-instance environment, check the PDB and its active services:

SHOW PDBS

ALTER PLUGGABLE DATABASE orders_pdb OPEN;

-- Consider this for restart persistence in a suitable non-Data Guard design ALTER PLUGGABLE DATABASE orders_pdb SAVE STATE;

SELECT name,
network_name,
pdb FROM   v$active_services WHERE  pdb = 'ORDERS_PDB';

Saved PDB state is useful in straightforward deployments, but it should not replace role-aware service management in Data Guard. For high-availability applications, use a named application service associated with the correct PDB and database role.

Avoid relying on an automatically created PDB default service for an HA application. A separately managed application service provides proper control over placement, database role, failover behavior, and workload management.

RAC and Data Guard Require Clusterware-Level Checks

In RAC, Oracle Restart, and Data Guard environments, an active instance does not automatically mean that an application service should run there. Clusterware decides service placement based on configuration, management policy, preferred instances, PDB association, and database role.

Use the Grid Infrastructure version of SRVCTL and check both configuration and runtime state:

srvctl status database -db PROD_A -verbose

srvctl config service -db PROD_A 
-service orders_rw -verbose

srvctl status service -db PROD_A 
-service orders_rw -verbose

srvctl status listener -listener LISTENER -verbose
srvctl status scan_listener -verbose

The service configuration should answer the following questions:

Is the service enabled?
Is its management policy automatic?
Which PDB owns the service?
Which RAC instances or server pool can run it?
Is it configured for the PRIMARY role, PHYSICAL_STANDBY role, or both?
Is the service currently running?

For Data Guard, verify the database role and broker configuration as well:

dgmgrl /

DGMGRL> SHOW CONFIGURATION VERBOSE;
DGMGRL> SHOW DATABASE VERBOSE 'PROD_A';
DGMGRL> VALIDATE DATABASE VERBOSE 'PROD_A';

A read-write application service should normally be configured with the PRIMARY role on both Data Guard sites. A reporting service can be configured separately for the PHYSICAL_STANDBY role.

A role-related service correction might look like this:

srvctl modify service -db PROD_B \
  -service orders_rw \
  -role PRIMARY \
  -policy AUTOMATIC \
  -pdb ORDERS_PDB

srvctl start service -db PROD_B -service orders_rw

Service changes can affect placement and existing sessions. Confirm the syntax supported by the installed Grid Infrastructure release and apply the change through the normal production change process.

Performance and Monitoring Considerations

ORA-12514 itself is generally returned quickly and is not usually a database performance problem. The secondary effect can still be serious.

An application pool may retry failed connections without sufficient delay. Hundreds of application processes then create a connection storm as soon as the service returns. Listener logs grow rapidly, monitoring floods the support channel, and the restored database receives a sudden authentication and session-creation spike.

Production monitoring should therefore go beyond checking whether the listener process is running:

Confirm that each required application service is registered.
Run a controlled synthetic login through the application endpoint.
Check service availability after database and listener restarts.
Validate every RAC or SCAN path used by the application.
Test application services after Data Guard switchovers, not only before them.
Alert when a required service remains absent beyond the expected registration interval.
Configure application retries with delay, backoff, and sensible limits.

When an ORA-12514 message contains a CONNECTION_ID, preserve it. The identifier can help correlate the failed request with Oracle listener diagnostics. The lsnrctl status output also reports the active listener log location.

Oracle Versus PostgreSQL Connection Routing

PostgreSQL does not have an Oracle-style listener service registry. High-availability routing is normally provided through a proxy, virtual IP, DNS record, operator, or client connection string.

Oracle	PostgreSQL
The client requests a logical database service.	The client specifies a host, port, and database.
LREG registers services with listeners.	The PostgreSQL server process accepts connections directly.
Services can map to PDBs, RAC instances, and database roles.	External HA components normally route clients to the correct server role.
An unknown service can return ORA-12514.	A missing database normally returns a PostgreSQL `FATAL` error.
`tnsping` checks listener reachability.	`pg_isready` checks PostgreSQL server status.
SQL*Plus validates an actual service login.	`psql` validates an actual database login.

The testing principle is similar on both platforms. pg_isready can report server status without proving that a specific user can connect to a valid database. It should not replace an actual psql connection test.

pg_isready -h pg-vip.example.com -p 5432

psql "host=pg-vip.example.com port=5432 dbname=orders 
user=app_probe connect_timeout=5"

For both platforms, an endpoint health check is weaker than a real application-path login test.

Quick Takeaways

ORA-12514 normally proves that the client reached a listener.
Check the application's resolved service name, host, and port first.
TNSPING success does not prove that the database service is available.
Compare DBA_SERVICES, V$ACTIVE_SERVICES, and lsnrctl services.
ALTER SYSTEM REGISTER publishes active services; it does not start them.
In RAC and Data Guard, use SRVCTL as the service-management source of truth.
Monitor actual application service logins, not only listener availability.

Lessons From the Field: DBA Takeaways

The Wrong Listener Is Checked Surprisingly Often

Multi-home servers, custom ports, SCAN listeners, VIPs, and stale DNS records can make a healthy listener irrelevant to the failed application path. Always reproduce the connection from the same network path used by the application.

Restarts Can Hide Weak Service Design

If every incident is closed after restarting the listener or database, delayed registration, incorrect role policies, and closed PDBs remain in place for the next maintenance window.

Application Retries Need Control

A missing service can become a connection storm when thousands of pool retries arrive after recovery. Retry intervals and backoff settings should be reviewed as part of the database service design.

Default Services Are Poor HA Contracts

Define application services according to workload and database role instead of exposing the raw database or PDB identity directly.

Data Guard Testing Must Include Services

A successful broker switchover does not prove that the application service started on the new primary. The runbook should include service checks and a real application connection test.

Mini Case Study: Service Missing After Switchover

Problem

After a planned Data Guard switchover, the new primary opened successfully and the broker reported a healthy configuration. The SCAN endpoint was reachable, but the application received ORA-12514 for orders_rw.

Investigation

lsnrctl services confirmed that orders_rw was missing. Running ALTER SYSTEM REGISTER made no difference.

DBA_SERVICES showed that the service existed, but it was absent from V$ACTIVE_SERVICES. The decisive check was:

srvctl config service -db PROD_B \
  -service orders_rw -verbose

The service on the second site had been configured with the PHYSICAL_STANDBY role during an earlier build. Clusterware therefore did exactly what its configuration required and did not start the service when that database became primary.

Root Cause

The service definition was inconsistent between the two Data Guard sites. Database switchover had been tested, but application service relocation had not.

Fix

The service was changed to the PRIMARY role, associated with the correct PDB, enabled with an automatic management policy, and started through SRVCTL. The service then appeared in the listener registry and the application connection succeeded.

The permanent remediation was to add service-role validation and an application login test to the switchover runbook.

Frequently Asked Questions

1. Why does TNSPING succeed while SQL*Plus returns ORA-12514?

TNSPING proves that the resolved listener can be reached. It does not establish a database session or confirm that the requested service is registered. Check lsnrctl services and perform an actual SQL*Plus connection.

2. Will Restarting the Listener Fix ORA-12514?

It may appear to help when registration is temporarily delayed, but it is rarely the correct first action. Verify the service name, active database services, PDB state, and registration targets before restarting anything.

3. When Should I Run ALTER SYSTEM REGISTER?

Use it when the required service is active in the database and the listener endpoint is correct, but the listener has not yet received the registration. It is particularly useful after starting or reconfiguring a listener.

4. Why Can ORA-12514 Occur After a PDB Restart?

The CDB may be open while the target PDB remains mounted, or the PDB-associated application service may not have restarted. Check V$PDBS, V$ACTIVE_SERVICES, and the relevant SRVCTL service state.

5. Can ORA-12514 Affect Performance?

The error itself is normally returned quickly. Poorly configured application retries can still cause connection storms, rapid listener-log growth, and a session-creation spike when the service becomes available again.

Conclusion

ORA-12514 becomes easier to diagnose once it is treated as a failed service mapping rather than a vague Oracle Net problem. The client reached a listener. The DBA's task is to identify the service it requested, confirm whether that exact listener knows the service, and determine why the database or Clusterware is not offering it.

The most reliable workflow is consistent: capture the resolved connection descriptor, run lsnrctl services against the correct listener, compare DBA_SERVICES with V$ACTIVE_SERVICES, and verify the instance, PDB, and Data Guard role. ALTER SYSTEM REGISTER is useful when LREG has an active service to publish. It cannot create a service, open a PDB, correct a stale client alias, or override a Clusterware role policy.

Modern Oracle environments need disciplined service management. Use named application services, manage RAC and Oracle Restart services through SRVCTL, associate PDB services explicitly, and configure Data Guard services by role. Do not use the PDB default service as the foundation of a production failover design.

Review one critical application's connection path. Record its host or SCAN address, port, service, PDB, preferred placement, Data Guard role, and monitoring check. Then test it after a restart and during the next planned role transition. A service that works only during normal operations is not yet production-ready.

Official Oracle References

Oracle Error Help: ORA-12514 - Official explanation, causes, and recommended actions for an unknown or unregistered service.
Configuring and Administering Oracle Net Listener - Covers LREG, dynamic service registration, LOCAL_LISTENER, REMOTE_LISTENER, and listener administration.
Oracle Database 19c SRVCTL Reference - Reference for managing RAC, Oracle Restart, PDB, and role-based application services.

Have you encountered ORA-12514 after a PDB restart, RAC service relocation, or Data Guard switchover? Share the symptom and the check that exposed the real cause. Production patterns are often more useful than another listener restart checklist.

Oracle DBA in 2030: What Will the Role Look Like?

noreply@blogger.com (Nikhil Kotak) — Sat, 17 Oct 2026 03:23:00 +0000

A production database alert arrives at 2:00 a.m. The monitoring platform detects increased commit latency, correlates it with storage response time and suggests several corrective actions. That is useful automation. The difficult part begins when someone must decide whether to wait, change the workload, fail over the database or accept the risk of an emergency configuration change.

This is why I do not expect AI to eliminate the Oracle DBA role by 2030. It will, however, remove a significant amount of repetitive administration. The DBA who only performs standard checks manually may struggle. The DBA who understands Oracle internals, automates routine work and makes sound production decisions will remain valuable.

Oracle Automation Is Not New

Oracle databases have provided automated diagnostics and recommendations for years. AWR, ADDM, SQL Tuning Advisor, Automatic Workload Repository baselines and automatic memory management already reduce the amount of manual analysis required. AutoUpgrade, Fleet Patching and Provisioning, Enterprise Manager and cloud-managed database services continue the same direction.

AI extends this capability by processing more operational data, identifying patterns and presenting possible causes more quickly. It may summarise an incident, compare execution plans, identify unusual workload behaviour or generate a first version of a remediation script.

The output still needs validation. Oracle recommendations are based on the information available to the tool. They may not include application release activity, business priorities, licensing restrictions, downstream dependencies or the recovery limitations of a particular environment.

The Work Most Likely to Change

Work Area	Likely Direction	DBA Responsibility
Health checks and routine reporting	Largely automated	Define thresholds, validate exceptions and improve coverage
SQL performance analysis	AI-assisted diagnosis and recommendations	Assess workload-wide impact before implementation
Backup and recovery validation	Automated checks and anomaly detection	Prove recoverability through restore and recovery testing
Patching and provisioning	Policy-driven orchestration	Control compatibility, rollback and operational risk
Outage and disaster recovery decisions	AI-supported but human-led	Own the decision, coordination and business impact

Where Production Experience Still Matters

Consider a Data Guard environment where transport lag suddenly increases. An AI-assisted platform may identify network latency, an archive destination error or heavy redo generation as possible causes. That helps reduce investigation time, but the operational decision is more complicated.

A DBA must determine whether the standby is still suitable for failover, whether the defined RPO is at risk, whether the lag is temporary and whether the application can reconnect correctly after a role transition. The decision may also depend on broker configuration, standby redo logs, protection mode, Fast-Start Failover settings and the availability of application and infrastructure teams.

The same applies to SQL tuning. Creating an index may improve one expensive query while increasing DML latency, storage consumption and maintenance time. Accepting a SQL profile may stabilise a plan but can also hide an underlying statistics or data-distribution problem. AI can suggest an action; the DBA must evaluate its wider effect.

The Oracle DBA Will Become a Platform Engineer

By 2030, managing databases one server at a time will be difficult to justify. Database estates are already moving towards standardised platforms, infrastructure as code, policy-based controls and self-service provisioning.

Oracle expertise will remain the foundation, but it will need to be combined with broader engineering skills:

Oracle RAC, Data Guard, RMAN and multitenant architecture
Exadata, Exadata Cloud@Customer and Oracle Cloud Infrastructure
Shell or Python scripting for operational automation
Ansible and Terraform for repeatable configuration and provisioning
APIs, Git and CI/CD practices for database changes
Security controls, encryption, auditing and access governance
Observability across the database, operating system, storage and application layers

This does not mean every DBA must become an expert in every tool. The practical goal is to stop treating database administration as a collection of manual server activities and start treating it as an engineered service.

Common Mistakes DBAs Should Avoid

Trusting Recommendations Without Testing

An AI-generated script should be reviewed like any other production change. Check its scope, privileges, locking behaviour, failure handling and rollback path. Test it with representative data before running it against a critical database.

Automating a Broken Process

Automation makes a good process faster, but it can also repeat a bad decision consistently. Before automating patching, failover or cleanup, define the prerequisites, safety checks, stopping conditions and evidence required after completion.

Ignoring Database Fundamentals

AI assistance does not remove the need to understand wait events, execution plans, locking, undo, redo, checkpoints, recovery and Oracle memory structures. Without that foundation, it is difficult to recognise when a recommendation is incomplete or unsafe.

Frequently Asked Questions

Will junior Oracle DBA positions disappear?

Entry-level work will change because many routine checks and standard requests can be automated. Junior DBAs will need earlier exposure to scripting, cloud platforms, observability and incident analysis instead of relying only on repetitive operational tasks.

Should Oracle DBAs move completely into cloud roles?

Cloud knowledge is valuable, but many enterprises will continue operating hybrid environments. Experience with on-premises Oracle, Exadata, Exadata Cloud@Customer and OCI can be more useful than focusing on only one deployment model.

Can AI make production database changes automatically?

Technically, automation can execute approved changes. For critical systems, organisations should still use access controls, testing, change approval, audit trails, rollback procedures and human oversight. The level of autonomy should depend on the risk and reversibility of the operation.

Final Thoughts

AI is unlikely to replace the Oracle DBA by 2030, but it will expose inefficient ways of working. Tasks based on predictable inputs and repeatable steps will increasingly move to automation. Architecture decisions, complex recovery, performance trade-offs, security and business-critical incident handling will continue to require experienced engineers.

The safest career strategy is not to compete with automation. Use it to remove repetitive work, then invest the saved time in Oracle internals, platform engineering, cloud architecture and recovery design. The future Oracle DBA will manage fewer individual tasks but carry greater responsibility for the reliability of the entire database service.

MongoDB : What DBAs Must Get Right

noreply@blogger.com (Nikhil Kotak) — Sun, 04 Oct 2026 12:49:00 +0000

MongoDB often looks simple during the first few application releases. Developers can add fields without waiting for a table change, nested-objects map naturally to application code, and a complete business record can be retrieved without several joins.
The real test starts after the collection grows, multiple services begin writing to it, and the workload becomes operationally important. One service stores an identifier as a string while another writes it as a number. Arrays grow without limits. Queries that were fast during development begin examining millions of documents. A secondary falls behind during a batch update, or a shard key concentrates most traffic on one node.

MongoDB is not difficult because it uses documents instead of tables. It becomes difficult when flexible modelling is treated as a replacement for data governance, indexing, recovery planning, and capacity management. This article looks at MongoDB from a production DBA perspective and focuses on the design and operational decisions that usually determine whether the platform remains stable.

Flexible Schema Does Not Mean No Schema

MongoDB stores data as BSON documents inside collections. BSON supports nested documents, arrays, dates, decimal values, binary data, and several numeric types. This allows an application object to be stored close to the form in which the application uses it.

A customer profile, for example, may contain contact details, preferences, addresses, and status information in one document. That can remove several joins and reduce application round trips.

The problem is not flexibility itself. The problem is uncontrolled variation. Without validation, different application versions may gradually introduce conflicting field names, missing attributes, and inconsistent data types.

use customer360

db.createCollection("profiles", {
validator: {
$jsonSchema: {
bsonType: "object",
required: ["customerId", "email", "status", "createdAt"],
properties: {
customerId: {
bsonType: "string"
},
email: {
bsonType: "string"
},
status: {
enum: ["ACTIVE", "SUSPENDED", "CLOSED"]
},
createdAt: {
bsonType: "date"
}
}
}
},
validationLevel: "strict",
validationAction: "error"
})

db.profiles.aggregate([
{
$project: {
customerIdType: { $type: "$customerId" },
emailType: { $type: "$email" }
}
},
{
$group: {
_id: {
customerIdType: "$customerIdType",
emailType: "$emailType"
},
documentCount: { $sum: 1 }
}
}
])

For an existing collection, inspect current documents before introducing strict validation. Enabling validation without understanding legacy data can reject application writes immediately after deployment.

A safer rollout is to identify inconsistent documents, correct the data, introduce validation in a controlled manner, and then enforce the final rules.

Model Around Business Operations

A relational design should not be copied into MongoDB collection by collection. That often creates a system dependent on repeated $lookup operations and multiple application calls.

The opposite approach is equally risky. Embedding every related object into one document can produce oversized documents, heavily updated arrays, and duplicated data that becomes difficult to maintain.

Use embedding when the data belongs together

Embedding works well when the related data is normally read together, updated together, and has a predictable size. Order line items are a common example because the description, quantity, and purchase price belong to the historical order.

Use references when the lifecycle is independent

A product master, audit trail, login history, or notification history usually changes independently and may grow continuously. Referencing such data from a separate collection is generally safer.

Unbounded arrays become operational debt

A customer document containing the last five login locations is manageable. A document containing every login since account creation will become increasingly expensive to read, update, index, replicate, and back up.

MongoDB documents also have a size limit. Even before that limit is reached, a growing document can create write amplification and contention. A practical design is to keep a small summary in the parent document and store the complete history separately.

db.orders.aggregate([
  {
    $project: {
      orderId: 1,
      itemCount: {
        $size: {
          $ifNull: ["$items", []]
        }
      },
      documentSizeBytes: {
        $bsonSize: "$$ROOT"
      }
    }
  },
  {
    $sort: {
      documentSizeBytes: -1
    }
  },
  {
    $limit: 20
  }
])

Indexes Still Control Query Performance

MongoDB does not remove the need for careful access-path design. A missing or poorly ordered index produces familiar symptoms: high CPU, excessive storage reads, slow application calls, cache pressure, and replica lag.

Consider an order service that frequently filters by tenant and status, then sorts by creation time.

db.orders.createIndex(
  {
    tenantId: 1,
    status: 1,
    createdAt: -1
  },
  {
    name: "idx_orders_tenant_status_created"
  }
)

db.orders.explain("executionStats").find({
tenantId: "TENANT-110",
status: "OPEN",
createdAt: {
$gte: ISODate("2026-07-01T00:00:00Z")
}
}).sort({
createdAt: -1
}).limit(100)

db.orders.aggregate([
{ $indexStats: {} },
{ $sort: { accesses: 1 } }
])

An IXSCAN stage does not automatically mean the query is efficient. Review the relationship between nReturned, totalKeysExamined, and totalDocsExamined.

If a query returns 100 documents after examining several million keys, the index is being used, but the access path is still expensive.

Indexing mistakes seen in production

Creating an index for every application filter without measuring write overhead.
Selecting the wrong order for fields in a compound index.
Removing an index because it appears unused during a short monitoring window.
Ignoring different BSON types in the indexed field.
Adding more indexes instead of correcting an unsuitable document model.

Replica Sets: High Availability Needs More Than Three Nodes

A replica set usually contains one primary and multiple secondaries. The primary accepts writes, and secondaries copy changes from the replication oplog. If the primary becomes unavailable, eligible members hold an election and one secondary may be promoted.

This architecture provides redundancy, but durability depends on write concern. A three-member replica set does not prove that every acknowledged write has reached multiple nodes.

db.orders.insertOne(
  {
    orderId: "ORD-2026-10019",
    status: "CREATED",
    createdAt: new Date()
  },
  {
    writeConcern: {
      w: "majority",
      wtimeout: 5000
    }
  }
)

rs.status()
rs.printReplicationInfo()
rs.printSecondaryReplicationInfo()

db.adminCommand({
replSetGetStatus: 1
})

Majority acknowledgment provides stronger durability but may add latency. A write concern timeout also requires careful application handling. It means the requested acknowledgment was not received within the configured time. It does not always prove that the write was never applied.

For this reason, retry logic should be idempotent. Retrying an order creation, payment request, or account update without a business key can create duplicates.

Replica lag is usually a capacity problem

A lagging secondary is often receiving changes correctly but cannot apply them as quickly as the primary generates them. Common causes include slow storage, network problems, large batch updates, index maintenance, insufficient CPU, cache eviction pressure, and competing reporting or backup activity.

Increasing oplog size may provide more recovery time, but it does not fix a secondary whose replay capacity remains lower than the primary write rate.

WiredTiger: Look Beyond Cache Usage

WiredTiger is responsible for storage, compression, cache management, checkpoints, concurrency, and journal-based recovery. The implementation differs from Oracle and PostgreSQL, but the production questions are familiar.

Does the active working set fit in memory?
Are dirty pages being written efficiently?
Is storage latency affecting checkpoints or eviction?
Is a write-heavy workload overwhelming the node?

const status = db.serverStatus()

status.mem
status.wiredTiger.cache
status.wiredTiger.transaction
status.wiredTiger.log

# Operating-system checks

iostat -xz 1 10
vmstat 1 10
pidstat -d -p "$(pidof mongod)" 1 10

grep -E "WTCHKPT|WTEVICT|WTRECOV|Slow query" 
/var/log/mongodb/mongod.log

High cache usage is not automatically a fault. A database cache is expected to remain busy. The warning signs are high cache pressure combined with aggressive eviction, growing dirty bytes, rising disk latency, checkpoint delays, and increasing application response time.

Increasing cacheSizeGB should not be the first response. More WiredTiger cache can reduce memory available to the filesystem cache, the operating system, connections, index builds, and aggregations.

Transactions Should Not Hide a Poor Document Model

MongoDB supports multi-document transactions on replica sets and sharded clusters. They are useful when several independent documents must change as one business operation.

They are not a substitute for proper modelling. If most application requests require updates across several collections, the design may be reproducing a relational system without the natural advantages of a relational database.

Long-running transactions can increase cache and storage pressure, retain older document versions for longer, experience more write conflicts, and become more expensive when multiple shards are involved.

Sharding Starts with the Shard Key

Replica sets provide redundancy, but one primary still has finite CPU, memory, storage, and write capacity. Sharding distributes collection data across multiple shards. Each shard is normally a replica set, applications connect through mongos, and the config server replica set maintains cluster metadata.

The shard key determines how data is distributed and how queries are routed. A poor key can create a hot shard, uneven storage, excessive chunk movement, or scatter-gather queries that contact every shard.

use commerce

db.orders.createIndex({
tenantId: 1,
orderDate: 1
})

sh.enableSharding("commerce")

sh.shardCollection(
"commerce.orders",
{
tenantId: 1,
orderDate: 1
}
)

sh.status()
db.orders.getShardDistribution()

Shard-key warning signs

Low-cardinality fields such as status or a small number of regions.
Monotonically increasing values that concentrate new writes.
A shard key missing from common application queries.
Tenant-based sharding when one tenant generates most of the traffic.
Hashed sharding selected without considering range-query requirements.

The balancer can move chunks between shards, but it cannot correct a workload that continually targets one logical key range. Shard-key selection should use actual query patterns, tenant distribution, write behaviour, and projected growth.

Practical Failure Scenario: Replica Lag During Batch Processing

Problem

A three-member replica set supported an order-processing application. During a nightly batch update, the primary remained responsive, but one secondary gradually fell more than 15 minutes behind. Since every process was running, the environment initially appeared healthy.

Root cause

The batch job issued millions of small updates. Each update modified fields covered by several indexes. The primary also used faster storage than the lagging secondary. Oplog generation increased faster than the secondary could apply the changes.

Fix

Converted individual operations into controlled bulk updates.
Reduced batch size and introduced pauses between batches.
Removed one redundant index after workload validation.
Aligned storage performance across replica-set members.
Increased oplog capacity as temporary protection.
Added alerts for apply-rate divergence and oplog-window reduction.

Increasing the oplog alone would only have delayed the problem. The permanent fix was to reduce write amplification and improve secondary replay capacity.

MongoDB Versus Oracle and PostgreSQL

Workload Requirement	MongoDB	Oracle or PostgreSQL
Variable product attributes	Natural fit when different product categories require different fields and the document structure changes frequently.	Supported through relational modelling, JSON columns, or a hybrid design, although highly variable attributes may require additional schema and query planning.
Nested content records	Strong fit when related data can be stored and retrieved as a single document, reducing the need for joins.	Well suited when nested information must be normalized, independently maintained, or queried across multiple entities. JSON can also be used where appropriate.
Customer profile aggregation	Well suited for entity-centric profiles that combine preferences, addresses, activity summaries, and other related information in one document.	A strong option when profile data is shared across normalized entities or requires complex reporting, joins, and strict relationship management.
Core financial ledger	Supports ACID transactions, but is usually not the first choice for a highly relational ledger requiring extensive constraints, reconciliation, and audit controls.	Usually the safer starting point because of mature transaction handling, strong consistency, relational constraints, locking controls, and established auditing capabilities.
Complex cross-entity reporting	Aggregation pipelines can support advanced analysis, but repeated joins across many collections may indicate that the data model is becoming relational.	Strong fit for complex joins, window functions, analytical SQL, materialized views, and reporting across multiple related entities.
Strong relational integrity	Schema validation is available, but relationships between collections are generally enforced by the application or supporting services.	Strong fit because primary keys, foreign keys, unique constraints, checks, and transactional rules can be enforced directly by the database.
Rapid document evolution	Natural fit when fields are introduced gradually and multiple document versions must coexist during application changes.	Possible through controlled schema migrations or JSON-based designs, but relational changes normally require more formal deployment and compatibility planning.
Heavy distributed document writes	A strong candidate when writes can be distributed through sharding, provided the shard key avoids hotspots and supports the main query patterns.	Can scale through partitioning, replication, distributed extensions, or platform-specific architectures, but horizontal write scaling is not usually as transparent as native document sharding.

MongoDB is a strong option for product catalogues, customer profiles, content platforms, device metadata, and other hierarchical records. Oracle or PostgreSQL is usually the safer starting point for financial ledgers, complex cross-entity reporting, strict foreign-key enforcement, and heavily normalized transactional systems.

Production Lessons for DBAs

Flexible schema moves responsibility to validators, application contracts, and release governance.
Unbounded arrays should be corrected before they become storage and replication problems.
An index can be used and still be inefficient. Always compare examined data with returned data.
Replica lag should be analysed as a difference between write generation and replay capacity.
A successful election does not prove the application reconnected successfully.
Sharding should begin only after a shard key has been tested against real workload patterns.
Replication protects availability. Restore-tested backups protect recoverability.

When MongoDB Is the Better Choice

MongoDB is a strong candidate when the data is naturally represented as a complete document and is usually read or changed as one unit.

Typical examples include:

Product catalogs with category-specific attributes
Customer profiles
Content management platforms
Configuration repositories
Mobile and web application backends
Session and preference data
Device metadata
Case-management records
APIs returning hierarchical objects

It is especially useful when new fields must be introduced frequently and application teams cannot coordinate repeated relational schema migrations.

That flexibility should still be supported by:

Schema ownership
Versioning rules
Validation
Index reviews
Retention policies
Backup and recovery testing

When Oracle or PostgreSQL Is the Safer Choice

MongoDB is not the natural first choice for every modern application.

A relational database is usually safer when the workload depends on:

Strong foreign-key enforcement
Complex joins across many entities
Highly normalized master data
Frequent multi-entity transactions
Financial ledger behavior
Mature SQL reporting
Complex ad hoc analytics
Strict database-side constraints

A payment ledger, general ledger, securities position store, or ERP accounting engine should not be moved to MongoDB only because the development team prefers JSON.

MongoDB can technically support many of these operations. Technical possibility is not the same as operational suitability.

Frequently Asked Questions

Does MongoDB really have no schema?

No. MongoDB has a flexible schema. Documents in the same collection can have different structures by default, but production systems still require an agreed document model. Collection validation can enforce critical rules.

Why is a query slow even when it uses an index?

The index may still examine too many keys or fetch too many documents. Check execution statistics, compound index order, sort behaviour, field data types, and predicate selectivity.

Can applications read from secondaries?

Yes, with an appropriate read preference. Secondary reads may be behind the primary, so read preference and read concern must match the application's consistency requirements.

Should every write use majority write concern?

Not automatically. Majority acknowledgment provides stronger durability but may increase latency and affect write availability during member failures. The choice should follow the business impact of losing, delaying, or retrying a write.

When should a collection be sharded?

Shard when one replica set can no longer meet projected storage or throughput requirements and the workload has a suitable shard key. Do not shard only because the collection is expected to grow.

Conclusion

MongoDB performs well when the workload is genuinely document-oriented and the operating model is designed around that reality. Flexible documents can simplify application development, replica sets provide high availability, and sharding offers a practical route beyond the limits of one server.

Those benefits do not remove the need for database engineering. Reliable MongoDB platforms define ownership for document structure, validate important fields, review execution plans before adding indexes, test elections with real application traffic, and monitor replication as a throughput problem rather than a simple process state.

The operational mindset remains similar to Oracle and PostgreSQL. Capacity planning, workload baselines, recovery testing, index discipline, and failure simulation still matter. The terminology changes to oplog, write concern, WiredTiger cache, checkpoints, and shard keys, but the DBA questions remain familiar: what can be lost, what can become slow, what can fail over, and how quickly can the service recover?

Review the largest collections, inspect slow queries with execution statistics, test application behaviour during elections, validate backup restores, and challenge every proposed shard key before the platform reaches its scaling limit.

PostgreSQL Shared Buffers: A Production DBA Guide

noreply@blogger.com (Nikhil Kotak) — Fri, 25 Sep 2026 12:25:00 +0000

A PostgreSQL server had just returned from planned operating-system maintenance. Recovery completed cleanly, connections were available, and the database log showed nothing unusual. Yet application latency was several times higher than before the restart, read IOPS had increased, and the first recommendation was to increase `shared_buffers`.

That may help, but it is not a diagnosis. The PostgreSQL cache is cold after a restart. A shared-buffer miss may be served by the operating-system page cache or may reach storage. A query can also have an excellent cache-hit ratio and still be slow because it processes millions of cached blocks. On the write side, dirty buffers bring WAL, the background writer, client backends, and the checkpointer into the same performance path.

This article explains what PostgreSQL keeps in shared buffers, how cache hits and misses should be interpreted, and what happens when pages are modified. It also covers practical sizing, monitoring SQL, common production failures, and the operational differences Oracle DBAs should expect when moving from the SGA buffer cache to PostgreSQL.

Why Buffer Sizing Becomes a Production Problem

shared_buffers defines a fixed-size shared memory area allocated when PostgreSQL starts. All databases and normal backends in the same PostgreSQL cluster use the same pool. It normally contains pages from permanent relations, including heap tables, indexes, materialized views, and system catalogs.

Most installations use an 8 KB database block size. A 4 GB setting therefore provides approximately 524,288 block-sized buffer slots. PostgreSQL caches pages, not complete query results. Finding a page in memory does not remove the CPU work required for MVCC visibility checks, filtering, joins, aggregation, row locking, or result transfer.

The setting is also not PostgreSQL's complete memory budget. Sorts and hash tables generally use work_mem. Temporary tables use session-local temp_buffers. WAL waiting to be written uses wal_buffers. The planner setting effective_cache_size allocates no memory at all; it only estimates the cache likely to be available.

What Really Happens During a Read

When an execution plan requests a table or index block, the buffer manager checks whether that block is already in shared buffers. On a cache hit, the backend pins the buffer and works with the cached page. On a miss, PostgreSQL must find a reusable slot and request the page through the I/O path.

A PostgreSQL read is not automatically a physical storage read. Linux may already have the file page in its page cache. PostgreSQL statistics generally cannot distinguish a page supplied by kernel memory from one retrieved from the storage device. That is why a low PostgreSQL hit ratio and low storage IOPS can exist at the same time.

The executor requests a heap or index block.
The buffer manager searches the shared buffer mapping.
On a hit, PostgreSQL uses the cached page.
On a miss, it selects an unused or replaceable buffer.
A dirty victim may need to be written before reuse.
The requested page is loaded and the executor continues processing.

How PostgreSQL Chooses a Victim Buffer

Once unused slots are exhausted, PostgreSQL uses a clock-sweep style replacement algorithm. Buffers have a usage count. Repeatedly used pages receive some protection, while pages with a low usage count become stronger replacement candidates. A pinned page cannot be replaced while a backend is actively using it.

Large scans use limited buffer-access strategies for several operations so that one scan does not automatically occupy the entire pool. Even with that protection, repeated reporting scans can displace useful OLTP pages or saturate storage. The useful signal is not simply a low hit ratio. It is a hot page being evicted and then read again shortly afterward.

Dirty Pages, WAL, and Data-File Writes

An INSERT, UPDATE, or DELETE normally changes a page in shared buffers. Once the in-memory page differs from its current data-file copy, the buffer is dirty. PostgreSQL does not need to write that page to the data file at transaction commit.

For a normal WAL-logged change, PostgreSQL generates the required WAL record, modifies the shared page, and marks it dirty. Commit normally waits for the required WAL to become durable. The data page can be written later because crash recovery can replay WAL if the instance fails before the page reaches disk.

Writer	Role in Production	DBA Signal
Background writer	Writes selected dirty buffers in advance to improve the supply of clean reusable buffers.	Cleaning volume and repeated stops at the configured limit.
Checkpointer	Writes and synchronizes pages required for a checkpoint recovery boundary.	Requested checkpoints, write time, sync time, and storage latency.
Client backend	Can write a dirty victim when it needs a reusable buffer and no clean one is ready.	Writes occurring directly in the application path.

A dirty page is not the same as an uncommitted page. One page can contain row versions created by several transactions. MVCC metadata determines visibility. WAL and the synchronization path protect durability.

Too Small and Too Large Both Create Problems

Condition	Typical Symptoms	What to Validate
`shared_buffers` too small	Repeated reads of hot relations, rising evictions, backend writes, and unstable peak latency.	Whether evicted pages are reused, storage latency, OS cache behavior, and available memory.
`shared_buffers` too large	Reduced OS cache, swap risk, memory pressure during concurrency peaks, and a larger dirty working set.	Full memory exposure, container limits, connection count, query memory, WAL, and checkpoints.

The PostgreSQL documentation suggests 25% of RAM as a reasonable starting point for a dedicated server with at least 1 GB of memory. It also notes that values above roughly 40% are unlikely to work better for most workloads because PostgreSQL relies on the operating-system cache. These are starting points, not universal targets.

Do not size from total database capacity. A 5 TB database may have a 20 GB active working set. A 200 GB reporting database may scan almost everything. The access pattern and reuse interval matter more than allocated size.

A Practical Production Scenario

A pattern I have seen during platform migrations is a production host inheriting a lower-environment configuration. In one representative case, a server with 64 GB of RAM was running with only 512 MB of shared buffers. Query plans were unchanged, but p95 latency rose sharply during the morning peak.

Problem: Hot order and customer indexes were repeatedly read. pg_stat_io showed frequent evictions, storage telemetry confirmed physical reads, and client-backend writes increased during peak concurrency. The server still had safe memory headroom and no swap activity.

Root cause: The PostgreSQL cache was sized for a test workload, while the reusable OLTP working set was several gigabytes. The OS cache absorbed some misses but could not protect the workload when concurrent reporting started.

Fix: We replayed a representative workload with 8 GB and 16 GB settings, reviewed peak memory exposure, and adjusted max_wal_size to spread checkpoint-related writes. The final setting was applied during a planned restart.

Result: Repeated reads and evictions fell, backend writes became uncommon during the peak, and latency stabilized. The lesson was not that 25% is always correct. The improvement came from proving that a reusable working set was being displaced while the host had safe memory headroom.

SQL and Commands for Buffer Analysis

Start with configuration, database-level cache activity, and cluster I/O. Capture differences over a representative interval because cumulative counters can hide a short production incident.

SHOW shared_buffers;
SHOW block_size;

SELECT name, setting, unit, context, pending_restart FROM pg_settings WHERE name IN ( 'shared_buffers', 'effective_cache_size', 'work_mem', 'max_wal_size', 'checkpoint_timeout'
) ORDER BY name;

SELECT datname,
blks_read,
blks_hit,
round(
100.0 * blks_hit /
nullif(blks_hit + blks_read, 0), 2
) AS shared_buffer_hit_pct FROM pg_stat_database WHERE datname IS NOT NULL ORDER BY shared_buffer_hit_pct;

SELECT backend_type, object, context,
reads, hits, evictions, writes, writebacks FROM pg_stat_io WHERE object = 'relation' ORDER BY evictions DESC NULLS LAST;

pg_stat_io is available from PostgreSQL 16. PostgreSQL 17 introduced pg_stat_checkpointer and moved checkpoint-related fields out of pg_stat_bgwriter. Version-aware monitoring matters. Also, pg_stat_bgwriter has never been the view for calculating the cache-hit ratio.

Query-Level and Cache Inspection

EXPLAIN (ANALYZE, BUFFERS, WAL, TIMING OFF)
SELECT order_id, customer_id, order_status
FROM orders
WHERE customer_id = 1001;

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT count(*) AS total_buffers,
count(*) FILTER (WHERE relfilenode IS NOT NULL) AS used_buffers,
count(*) FILTER (WHERE isdirty) AS dirty_buffers,
round(avg(usagecount)::numeric, 2) AS avg_usage_count FROM pg_buffercache;

EXPLAIN (ANALYZE) executes the SQL. Do not run production DML casually to obtain buffer statistics. A plan reporting only shared hits can still be expensive if it touches hundreds of thousands of cached blocks. Logical I/O consumes CPU and can create contention.

Operating-System Correlation and Configuration Review

free -h
vmstat 1 10
iostat -xz 1 10

# Example values for review, not a universal recommendation

shared_buffers = '16GB'
effective_cache_size = '44GB'
max_wal_size = '32GB'
checkpoint_timeout = '15min'
checkpoint_completion_target = 0.9

Check swap, run queue, device utilization, throughput, and read/write latency. Increasing shared buffers requires a restart. A substantial increase should be reviewed with WAL volume, max_wal_size, checkpoint frequency, recovery-time expectations, and disk-space planning.

Oracle Buffer Cache vs PostgreSQL

Oracle DBAs will recognize the basic design. Oracle's database buffer cache is a shared SGA component containing data blocks, while DBWn writes dirty buffers. PostgreSQL shared buffers serve a comparable purpose, but the sizing and write paths are not identical.

Area	PostgreSQL	Oracle
Primary cache	`shared_buffers`, shared across databases in one cluster.	Database buffer cache inside the SGA.
Durability	WAL protects changes before related data pages become durable.	Redo provides the corresponding instance-recovery protection.
Dirty-page writer	Background writer, checkpointer, and sometimes client backends.	DBWn writes dirty buffers and supports checkpoint progress.
Replacement	Clock-sweep behavior using usage counts and buffer pins.	LRU-based mechanisms using touch counts and internal lists.
Sizing model	Fixed at server start and deliberately leaves room for the OS cache.	`DB_CACHE_SIZE` can operate within SGA memory management such as ASMM.

SELECT name, display_value
FROM v$parameter
WHERE name IN (
  'db_cache_size',
  'sga_target',
  'memory_target'
);

SELECT name, block_size, current_size, buffers FROM v$buffer_pool;

SELECT name, physical_reads, db_block_gets, consistent_gets FROM v$buffer_pool_statistics;

In both products, the hit ratio can mislead. An Oracle query performing excessive consistent gets and a PostgreSQL query performing excessive shared hits can both be slow without generating many physical reads. Logical I/O is not free.

Common Failure and Troubleshooting Patterns

High Reads Immediately After Restart

The cache is cold. Check whether read latency and IOPS fall as the workload warms the pool. Do not resize from the first few minutes unless warm-up itself breaks the service objective.

Excellent Hit Ratio but Slow SQL

Review EXPLAIN (ANALYZE, BUFFERS). Look for excessive block access, poor joins, missing predicates, repeated nested-loop reads, and rows removed by filters.

Evictions Rise While the Host Has Free Memory

This can support testing a larger pool, but first confirm that the evicted pages are needed again. A one-time sequential scan may not benefit from a permanent increase.

Backend Writes Increase During Traffic Peaks

Review buffer pressure, background-writer activity, checkpoint timing, and storage latency together. More aggressive background cleaning may reduce backend work but can increase steady write traffic.

Temporary Files Remain High After a Cache Increase

Shared buffers do not store sort and hash spill files. Review work_mem, plan shape, concurrency, and temporary-file logging instead of increasing the wrong memory area.

The Server Starts Swapping

Revisit the full memory budget immediately. Include connections, parallel workers, autovacuum, per-operation memory, extensions, agents, and container limits. A larger cache is not an improvement if active PostgreSQL processes are paged out.

Lessons From the Field

The first common mistake is treating 25% of RAM as a finished design. It is only a starting point. The useful cache size depends on reuse, concurrency, the OS cache, storage, and the memory needed outside the buffer pool.

The second mistake is tuning from cumulative ratios. Capture deltas during the incident window. Separate restart warm-up, reporting, autovacuum, backup activity, and business traffic. One percentage covering all of them is rarely actionable.

The third mistake is increasing memory before reviewing SQL. Cached block access still consumes CPU. A missing index or poor join remains expensive even if every required page is already in memory.

Finally, watch the write side. Backend writes, requested checkpoints, dirty-buffer volume, and storage sync latency can explain application spikes that a cache-hit ratio cannot. The real target is stable latency with predictable memory and I/O behavior at peak load.

Quick Takeaways

Shared buffers cache relation pages and hold the normal working pages modified by DML.
A shared-buffer miss is not proof of physical storage I/O.
Dirty buffers can be written by the background writer, checkpointer, or a client backend.
A high cache-hit ratio does not prove that a query is efficient.
Size for reusable working data and the full memory budget, not total database size.
Review WAL and checkpoint behavior when substantially increasing the pool.
Correlate PostgreSQL statistics with operating-system and storage telemetry.

Frequently Asked Questions

Should shared_buffers always be 25% of RAM?

No. Use it as a starting point for a dedicated server, then validate working-set reuse, OS memory, concurrency, checkpoints, and storage performance. Managed platforms may calculate or restrict the value differently.

Why is the hit ratio low when storage latency is normal?

The operating-system page cache may be satisfying PostgreSQL reads. The workload may also contain expected sequential or bulk reads. Compare PostgreSQL counters with device I/O.

Will increasing shared_buffers eliminate disk reads?

No. Cold starts, one-time scans, changing access patterns, and datasets larger than memory still generate reads. Poor SQL can also touch more blocks than any sensible cache should retain.

Can the setting be changed without a restart?

No. It is a server-start parameter. Check pg_settings.pending_restart to confirm whether a configured change is waiting for restart.

Which view should be used for cache hits?

Use pg_stat_database for database-level hits, pg_statio_* for relations, EXPLAIN (BUFFERS) for a query, and pg_stat_io for cluster I/O by backend type and context.

Conclusion

Shared buffers sit at the center of PostgreSQL's normal table and index I/O path. They reduce repeated reads, hold pages changed by DML, and connect directly to WAL, checkpoints, background writing, and crash recovery. That makes shared_buffers one of the first parameters DBAs inspect, but it should not become the automatic answer to every performance problem.

An undersized pool can repeatedly evict useful pages and expose applications to storage latency. An oversized pool can starve the operating system, increase total memory risk, and complicate write behavior. A cache hit avoids a PostgreSQL file read, but it does not make query processing free. A cache miss may still be served from kernel memory. These details explain why a cache-hit percentage alone is weak tuning evidence.

Start with a sensible allocation, then measure the workload that matters. Capture short-interval changes in reads, hits, evictions, backend writes, checkpoints, and storage latency. Review which relations and SQL statements are responsible. Test changes under realistic concurrency, leave headroom for the OS and per-session memory, and compare the same metrics after restart and cache warm-up.

Review your current setting, prove whether hot pages are being displaced, and confirm the finding outside PostgreSQL before scheduling a restart. Measure, test, change, and monitor. Do not size the buffer cache from folklore.

Have you handled an incident caused by buffer churn, backend writes, or an oversized memory setting? Share the evidence you used and whether resizing the pool actually fixed the problem.

References

Oracle RAC - Oracle DBA Interview Questions with Practical Answers

noreply@blogger.com (Nikhil Kotak) — Mon, 21 Sep 2026 14:24:08 +0000

Oracle DBA interviews, especially for senior roles, usually go beyond definitions. The discussion often moves quickly into troubleshooting, recovery, RAC, Data Guard, patching, GoldenGate, and real production scenarios.

Below are some recent Oracle DBA interview questions reportedly asked in a product-based organization, along with practical answers and the checks I would expect a DBA to discuss during an interview.

1. Explain the Oracle RAC startup sequence.

RAC startup is dependency-driven. Oracle does not simply start the database instances first.

A simplified startup sequence is:

OHASD → CSSD / CRSD / EVMD → ASM → VIP / Listener / SCAN → Database Instances → Services

Clusterware first establishes cluster membership and access to voting resources. ASM must be available before the database can access its files. Database instances and application services are then started according to Clusterware resource dependencies.

crsctl check crs
crsctl stat res -t
srvctl status database -d <db_unique_name>

2. What are the ways to resolve lag in a standby database?

The first step is to determine whether the problem is transport lag or apply lag.

For transport lag, I would check redo shipping, archive destination errors, network latency, archive gaps and standby redo log configuration. For apply lag, I would check managed recovery, CPU and I/O pressure on the standby, recovery waits and whether real-time apply is being used.

Useful views include:

V$DATAGUARD_STATS
V$ARCHIVE_DEST_STATUS
V$ARCHIVED_LOG
V$DATAGUARD_PROCESS

Restarting MRP may recover a stopped recovery process, but it should not be treated as the solution until the actual reason for the lag is understood.

3. Explain the export steps for an audit trail table.

The approach depends on whether the database uses traditional auditing or Unified Auditing.

For Unified Auditing, the required records can be selected from UNIFIED_AUDIT_TRAIL into an archive table and that table can then be exported using Data Pump.

For traditional auditing, SYS.AUD$ may be involved, but audit management should preferably be performed using DBMS_AUDIT_MGMT rather than directly manipulating SYS-owned audit tables.

Before exporting, I would define the required date range and scope so that unnecessary historical audit data is not included.

4. Explain important RAC background processes.

Some important RAC-specific processes are:

LMON – Monitors Global Enqueue Service activity and participates in cluster reconfiguration.
LMD – Handles global enqueue requests and global deadlock detection.
LMS – Handles Cache Fusion block transfers between RAC instances.
LMHB – Monitors RAC lock-management processes and detects hangs.

These processes work with the Global Cache Service and Global Enqueue Service to coordinate resources across RAC instances.

5. If STANDBY_FILE_MANAGEMENT is MANUAL, what happens when a datafile is added on primary?

The standby does not automatically create and manage the corresponding datafile.

During managed recovery, Oracle may register the new file using a name such as UNNAMEDxxxxx, and recovery can stop because the expected standby datafile does not exist.

After identifying the file number and correct destination, the file can be created at the required standby location and managed recovery restarted.

ALTER DATABASE CREATE DATAFILE
'<UNNAMED_file>'
AS '<standby_datafile_path>';

With STANDBY_FILE_MANAGEMENT=AUTO, Oracle can normally handle new files automatically when OMF or the relevant filename conversion configuration is correct.

6. A dump contains 10 schemas. How would you import only one?

Use the SCHEMAS parameter with Data Pump Import.

impdp system/***** \
DIRECTORY=DP_DIR \
DUMPFILE=exp.dmp \
SCHEMAS=HR

If the schema has to be imported under a different name:

REMAP_SCHEMA=HR:HR_TEST

If the requirement is actually to import only one table, then TABLES should be used instead. A table can also be renamed during import using REMAP_TABLE.

7. What causes a split-brain scenario?

Split brain can occur when RAC nodes lose communication with each other while individual nodes remain alive and believe they can continue servicing the database.

Possible causes include private interconnect failure, network partitioning, severe latency, OS hangs, heartbeat failures or voting-disk accessibility problems.

Oracle Clusterware protects the cluster using network and disk heartbeats. A node that cannot maintain valid cluster membership can be evicted to protect database integrity.

8. If the whole RAC cluster goes down, how would you bring it back?

I would first determine why the cluster went down rather than immediately attempting database startup.

The basic validation order would be:

Servers → Storage → Interconnect → Voting/OCR → Clusterware → ASM → Listeners → Database → Services

crsctl check crs
crsctl start crs
crsctl stat res -t

After recovery, I would verify instance status, ASM disk groups, services and both Clusterware and database alert logs to understand the original failure.

9. Explain Cache Fusion.

Cache Fusion allows RAC instances to transfer database blocks directly between their buffer caches through the private interconnect.

If Instance 1 has the required current block and Instance 2 requests it, Oracle can transfer the block through the interconnect instead of forcing Instance 1 to write it to disk and Instance 2 to read it back.

The Global Cache Service manages block ownership and LMS processes participate in the block transfer.

10. Explain Oracle patching steps.

A production patching activity normally follows this sequence:

Read README → Compatibility check → OPatch validation → Inventory check → Prerequisite checks → Backup → Apply patch → Start services → Run datapatch → Validate

opatch lsinventory

datapatch -verbose

After patching, I would verify DBA_REGISTRY_SQLPATCH, database and Clusterware logs, component status and application connectivity.

For RAC and Grid Infrastructure, whether the patch can be applied in rolling mode depends on the specific patch and Oracle's documented instructions.

11. I have a full database backup in two different locations. How would you restore the database?

First I would make sure RMAN can see the required backup pieces. If the backup files have been moved or copied, they may need to be cataloged.

CATALOG START WITH '<backup_location>';

RESTORE DATABASE;
RECOVER DATABASE;

The complete sequence may include restoring the SPFILE and controlfile, mounting the database, cataloging backup pieces, restoring datafiles, recovering the database and finally opening it.

If incomplete recovery is performed or a backup controlfile is involved, opening with RESETLOGS may be required.

12. Normally 100 processes run, but during peak hours it suddenly becomes 300. What would you check?

I would first identify where those additional connections came from instead of immediately increasing the PROCESSES parameter.

I would group sessions by username, machine, program, module, service and session status.

V$SESSION
GV$SESSION
V$RESOURCE_LIMIT

Then I would investigate connection-pool behaviour, application connection leaks, batch jobs, deployments, idle session accumulation, blocking sessions, listener connection storms and CPU or memory pressure.

ASH and AWR can also help establish what changed during the spike.

13. What are the different switchover statuses and what do they mean?

SELECT switchover_status
FROM v$database;

Some important values are:

TO STANDBY – Primary is ready to transition to standby.
SESSIONS ACTIVE – Switchover is possible, but active sessions are present.
TO PRIMARY – Standby is ready to become primary.
RECOVERY NEEDED – Additional redo must be applied before the transition.
NOT ALLOWED – The database is not currently ready for the requested transition.

Other statuses can appear depending on redo gaps or the current stage of the switchover. The important point is to understand why the role transition is or is not currently possible.

14. What happens when active sessions are disconnected during switchover?

When the switchover is performed using the option that allows active sessions to be disconnected, Oracle can proceed with the role transition even when user sessions still exist.

Those sessions are disconnected and any uncommitted transactions may require rollback. Oracle then completes the required role-transition processing and updates the database role.

In production, application connections should still be drained gracefully wherever possible before the switchover.

15. Explain Oracle GoldenGate processes.

In a traditional GoldenGate architecture, the logical flow is:

Source Database → Extract → Local Trail → Data Pump → Remote Trail → Replicat → Target Database

Manager – Manages GoldenGate processes.
Extract – Captures database changes.
Data Pump – Optionally sends trail data to the target environment.
Trail Files – Store captured change records.
Replicat – Applies changes to the target database.

Integrated Extract also uses Oracle database mining infrastructure to capture changes from redo.

16. What are the steps to drop an Oracle database?

Before dropping a database, I would confirm the database identity, verify approvals and make sure any required backups have been retained.

STARTUP FORCE MOUNT RESTRICT;

DROP DATABASE;

For RAC, the database configuration may also need to be removed from Clusterware using the appropriate srvctl command.

Archived logs, RMAN backups, diagnostic files and other external configuration may require separate cleanup depending on the decommissioning procedure.

17. What redo and Data Guard parameters are normally configured?

Common Data Guard parameters include:

DB_UNIQUE_NAME
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
FAL_SERVER
STANDBY_FILE_MANAGEMENT
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT

The filename-conversion parameters are particularly relevant when primary and standby file locations differ and Oracle Managed Files are not being used.

I would also verify FORCE LOGGING, standby redo logs, password-file synchronization and redo transport configuration because Data Guard readiness is not only about initialization parameters.

18. Explain manual Grid Infrastructure patching steps.

The exact sequence should always come from the README supplied with the specific Grid Infrastructure Release Update.

For a rolling manual patch, the general approach is:

Prerequisite checks → Backup configuration → Prepatch → Patch Grid Home → Postpatch → Validate node → Continue with next node

rootcrs.sh -prepatch

# Apply the required patch using the procedure
# documented for the specific RU.

rootcrs.sh -postpatch

crsctl stat res -t
opatch lsinventory

Once a node is healthy, the same procedure can be performed on the remaining nodes. If database homes are also patched, the required SQL patching with datapatch must also be completed.

19. Even after killing a session, why might it still appear?

Executing ALTER SYSTEM KILL SESSION does not necessarily mean that the corresponding operating-system process disappears immediately.

A session can remain in KILLED state while Oracle completes cleanup.

Possible reasons include a large transaction rollback, PMON cleanup, an OS-level process stuck in an I/O call, network waits, distributed transaction cleanup or another uninterruptible operation.

V$SESSION
V$PROCESS
V$TRANSACTION

In RAC, I would also confirm which instance owns the session before attempting further action. Killing the operating-system process should only be considered after identifying exactly what that process is doing.

One Point Worth Remembering During DBA Interviews

For experienced DBA roles, knowing the command is usually only the beginning of the discussion.

The next questions are often:

How would you verify it?
Which view or log would you check?
What if the first solution does not work?
How would you validate the database after the change?

A practical way to structure most troubleshooting answers is:

Symptom → Evidence → Component → Action → Validation

That usually makes the difference between giving a memorized answer and showing how you would actually handle the situation as a DBA.

Have you encountered any of these questions in an Oracle DBA interview? And more importantly, how many of these scenarios have you actually tested in your own lab or environment? 🙂

My Learning and Exam Experience – Oracle Agentic AI Foundations Associate [1Z0-1157-26]

noreply@blogger.com (Nikhil Kotak) — Mon, 21 Sep 2026 08:02:02 +0000

I recently completed the Oracle 1Z0-1157-26 – Agentic AI Foundations Associate exam.

I had been reading and learning about AI agents, MCP, vector search and some of the newer AI capabilities around Oracle for a while, so this certification came at a good time. I decided to go through the complete learning path rather than preparing only from an exam point of view. And I think that is the right way to approach this one.

What I liked about this course

One thing I liked is that the course does not assume that everyone already understands how an AI agent works. It starts with a fairly simple question: what actually makes something an AI agent?

From there, it introduces the role of the LLM, tools and the orchestration loop, and then moves into reasoning patterns such as Chain-of-Thought and ReAct, along with safety and guardrails. That foundation helps because the later topics make much more sense once you understand the basic agent execution flow.

The course then moves through LangChain, Model Context Protocol (MCP), OpenAI Responses API and Agents SDK, OCI Enterprise AI Agents, and finally Agentic AI capabilities around Oracle AI Database. It is not only about learning definitions. Oracle's current course also includes practical exposure to building AI agents and understanding how these pieces fit together. (Refer > Oracle MyLearn)

MCP was one of the interesting parts for me

Before going through this learning path, MCP was something I had seen mentioned more and more whenever AI agents were discussed.

The course gives a good foundation around MCP hosts, clients, servers, tools, resources and prompts, along with JSON-RPC messaging and the different transport options.

Understanding MCP also makes it easier to see why it is becoming relevant. Instead of every AI application building its own custom integration for every external tool or data source, MCP provides a standard way for these components to communicate.

For anyone starting with Agentic AI, I think this is an area worth spending some extra time on rather than just remembering the terminology for the exam.

The Oracle Database part made it more relevant

The later part of the course was particularly interesting because it brings Agentic AI closer to the database.

It covers Oracle AI Vector Search, vector embeddings, similarity search, document chunking, Select AI, Private Agent Factory and the Autonomous AI Database MCP Server.

This is where some of the concepts from the earlier modules start connecting together. You first learn what an agent is, how it calls tools and how MCP works. Then you see how similar ideas can be used with enterprise data sitting inside Oracle AI Database.

For database professionals, I think this part is especially useful because AI is no longer something completely separate from the database world. We are gradually seeing these capabilities getting closer to where enterprise data actually resides.

About the 1Z0-1157-26 exam

The exam format is quite straightforward:

Exam: 1Z0-1157-26 – Agentic AI Foundations Associate
Questions: 40
Duration: 60 minutes
Passing score: 65%

The questions are mostly foundation and concept-oriented, but I would not recommend relying only on memorizing definitions. You should understand things such as what happens during an agent loop, how tools are invoked, the difference between various MCP components, how handoffs work between agents, the purpose of guardrails, and the basic workflow behind embeddings and vector search.

Oracle's learning path also includes skill checks, exam-preparation material and a practice exam before the certification exam. The certification is currently offered free of charge. (Oracle Blogs)

How I would prepare

My suggestion would be to complete the learning material first and then use the practice exam to identify areas where the concepts are still not completely clear.

I would pay particular attention to -- > agent architecture and execution flow, LangChain tools and agents, MCP architecture, OpenAI Agents SDK concepts, OCI Enterprise AI Agents, embeddings and Oracle AI Vector Search.

For several topics, understanding why a particular component exists is more useful than simply remembering what it is called. That approach also makes the learning useful after the certification is finished.

Should you take this course ?

I would encourage anyone who is curious about Agentic AI to go through this course.

You do not necessarily need to be working as an AI or ML engineer to get value from it. If you are working with databases, cloud infrastructure, development, DevOps/SRE or OCI, it gives you a good starting point for understanding where AI agents are heading and how they can interact with existing enterprise systems.

Oracle describes the course as a practical introduction that starts from first principles and then works through LangChain, OpenAI Agents SDK, MCP, OCI Enterprise AI Agents and Oracle AI Database.

That is probably the part I liked most about it: you don't have to begin as an Agentic AI expert. Start with the fundamentals, understand how an agent works, and the rest of the pieces gradually start making sense.

My Recommendation

If Agentic AI is something you have been hearing about but haven't yet found a structured place to start, I would definitely recommend giving this learning path a try.

And I would not approach it only with the goal of clearing 1Z0-1157-26. Take some time to understand the agent loop. Build something small. Understand why tools are needed. Look at how MCP connects systems, and then see how those same ideas are being brought into OCI and Oracle AI Database.

The certification is a nice milestone at the end, but the real value is understanding how these technologies are beginning to fit together.

If you have been planning to start learning Agentic AI, this is a good course to begin with.

References

Oracle Agentic AI Foundations (2026) – Oracle MyLearn
Oracle Agentic AI Foundations course

Oracle University – Agentic AI Foundations Training & Certification
Oracle Agentic AI Foundations: Get skilled for the Agentic AI Era

Cheers..! 🫡🙌😊

Oracle Standby Redo Logs: The Gap Most DBAs Miss

noreply@blogger.com (Nikhil Kotak) — Sun, 20 Sep 2026 05:25:00 +0000

The first warning often appears during a DR drill. Redo transport is healthy, managed recovery is running, and the standby looks ready. Then Data Guard Broker reports insufficient standby redo logs, or apply lag starts rising after an online redo log resize even though the network has no visible problem.

This is why I do not validate standby redo logs only by checking whether rows exist in V$STANDBY_LOG. The count must be correct for every redo thread, the sizes must match the primary redo layout, RFS must actually be using them, and the same preparation should exist on the current primary for the next role transition.

There is another misconception worth clearing up early. Standby redo logs improve how received redo is stored and applied. They do not convert asynchronous transport into a guaranteed zero-data-loss service. Protection mode, transport progress and apply progress are three different parts of the Data Guard path.

The discussion below focuses mainly on physical standby databases and the checks that matter in production: real-time apply, the required SRL count, RAC thread coverage, role readiness, storage pressure and the lag patterns that expose a broken receive path.

What a Standby Redo Log Changes

An online redo log belongs to the database while it is operating in the primary role. A standby redo log, or SRL, is a receiving structure. On a physical standby, the remote file server process, RFS, receives redo from the source database and writes it into the current SRL.

The managed recovery process can read that SRL while it is still being filled. This is real-time apply. Recovery does not need to wait for the current redo sequence to complete and become an archived redo log before applying it to the standby datafiles.

From Oracle Database 12.1 onward, the old USING CURRENT LOGFILE clause is deprecated and is no longer needed to start real-time apply. Starting managed recovery normally enables real-time apply when the standby is in ARCHIVELOG mode and usable SRLs are available.

If RFS cannot obtain a suitable SRL, Oracle can write received redo directly to a standby archived redo log. That does not mean modern ASYNC transport simply waits for the next primary log switch before sending redo. The visible penalty is usually on the apply side: recovery falls back to the completed archived-log path instead of applying the current stream from an SRL.

In monitoring, this can produce a saw-tooth pattern. Transport lag remains low, apply lag grows during the current sequence, and then falls after the sequence is completed and applied. When I see that pattern, I verify real-time apply and SRL usage before blaming network bandwidth or increasing apply parallelism.

Protection Mode Is Not the Same as Apply Mode

SRLs, synchronous transport and zero data loss are often discussed as though they are one feature. They are related, but each controls a different stage.

Production question	Main control
Does the primary wait before acknowledging a commit?	Protection mode and SYNC or ASYNC transport
Must the remote side acknowledge after writing to storage?	`AFFIRM` or `NOAFFIRM`
Can recovery apply the current incoming sequence?	A usable SRL and real-time apply
How much received redo is still unapplied?	Standby CPU, datafile I/O and apply capacity

Maximum Availability and Maximum Protection depend on synchronous transport to an eligible standby and require SRLs. Maximum Performance normally uses ASYNC transport. In ASYNC mode, a primary transaction can commit before its redo reaches durable storage at the standby.

A well-performing ASYNC configuration may show only a small exposure window, but SRLs do not make that window disappear. They protect and expose redo that has reached the destination. They cannot protect redo that was still buffered or in transit when the primary site was lost.

Storage latency also matters differently by mode. With SYNC AFFIRM, the primary commit path includes the remote durable write, so network latency and SRL write latency can affect application response time. With ASYNC transport, slow receiver storage may not delay every foreground commit, but sustained backpressure can increase transport lag and widen the RPO exposure.

The SRL Count Rule: ORL Groups Plus One

Required formula for every source redo thread:
SRL groups = primary online redo log groups + 1

An equal number of online and standby redo log groups is not sufficient. Oracle requires at least one more SRL group than the online redo log count for each source thread. The additional group gives RFS another target while the previous SRL is being archived and made reusable.

A single-instance primary with three online redo groups needs at least four SRL groups.
A two-instance RAC primary with three online groups in each thread needs four SRLs for thread 1 and four for thread 2.
The same thread coverage is required even if the standby RAC cluster runs fewer instances. The receiver must support every redo thread at the source.
If completed SRLs cannot be archived quickly enough during heavy switching, more than the minimum may be required.

Size the SRLs from V$LOG.BYTES. Each SRL must be at least as large as the largest source online redo log, and the cleanest operational layout is to keep the online and standby redo logs the same size. I prefer to use the exact byte value in the DDL instead of manually converting it to an approximate MB or GB value.

If the primary online redo groups have mixed sizes, standardize that layout first. In RAC, assign every SRL to its intended thread and place the files on shared storage. Leaving groups at thread 0 may allow runtime assignment in some configurations, but explicit thread ownership is easier to validate and follows current Oracle guidance.

Why SRLs Should Exist on the Primary Too

A standby redo log is not used while the database is operating in the primary role. That sometimes leads teams to create SRLs only on the current standby. The design works until the first role transition.

After a switchover, the old primary becomes the new redo receiver. If its SRLs were created in advance, it can immediately receive redo through the correct path. If they are missing, the DBA has additional work to perform and the configuration is not fully ready in the reverse direction.

Production best practice: Create the complete SRL layout on both the primary and standby databases before a switchover.

Pre-created SRLs do not make Oracle's internal role-conversion step execute faster. They make the overall switchover cleaner and immediately role-ready because no post-switch SRL creation is required. Data Guard Broker validates SRLs on both sides before a role transition, and SRLs are required on the primary when Fast-Start Failover is enabled.

Build and Inspect the SRL Layout

Keep the discovery and creation statements together in the runbook. Run the online redo queries on the source database and review V$STANDBY_LOG on every database that may receive redo.

-- Primary: online redo count and exact size by thread
SELECT thread#, group#, bytes,
       ROUND(bytes / 1024 / 1024) AS size_mb,
       members, status
FROM v$log
ORDER BY thread#, group#;

SELECT thread#, COUNT(*) AS online_groups,
MIN(bytes) AS min_bytes,
MAX(bytes) AS max_bytes FROM v$log GROUP BY thread# ORDER BY thread#;

-- Run on each role-capable database SELECT thread#, group#, bytes,
ROUND(bytes / 1024 / 1024) AS size_mb,
status, archived, sequence# FROM v$standby_log ORDER BY thread#, group#;

-- Example: four 2 GB SRLs for thread 1 on ASM ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('+DATA') SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('+DATA') SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('+DATA') SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('+DATA') SIZE 2147483648;

-- Physical standby, Oracle 12.1 and later ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Choose group numbers that do not collide with online redo groups or existing SRLs. Repeat the DDL for every source thread. Adding new groups is normally straightforward, but dropping or resizing old groups needs a controlled change. Never drop the group currently receiving redo. Confirm that it is safely reusable before running ALTER DATABASE DROP STANDBY LOGFILE GROUP group_number.

SRL storage must sustain the peak redo rate. A slow or inaccessible disk group can create receiver backpressure even when the network is healthy. If SRLs are multiplexed, place the members in separate failure domains; two members on the same vulnerable file system provide little additional protection.

How to prove That RFS Is Using the SRLs

A clean configuration query is only the first layer. Runtime validation should cover RFS, recovery mode, lag freshness, archive health and broker readiness.

-- Standby: RFS and managed recovery activity
SELECT name, role, action, client_role,
       thread#, sequence#, group#
FROM v$dataguard_process
ORDER BY name, thread#, sequence#;

-- Standby: lag value and metric freshness SELECT name, value, unit, time_computed, datum_time FROM v$dataguard_stats WHERE name IN ('transport lag', 'apply lag', 'apply finish time') ORDER BY name;

-- Primary: remote destination and SRL use SELECT dest_id, status, type, database_mode,
recovery_mode, srl, error FROM v$archive_dest_status WHERE type IN ('PHYSICAL', 'LOGICAL', 'FAR SYNC');

-- Data Guard Broker
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE VERBOSE 'STBYDB';
DGMGRL> VALIDATE DATABASE VERBOSE 'STBYDB';

For new monitoring, use V$DATAGUARD_PROCESS instead of building around V$MANAGED_STANDBY, which has been deprecated since Oracle Database 12.2.

Always read DATUM_TIME with the lag value. A transport lag of zero is not reassuring if DATUM_TIME stopped changing ten minutes ago. The last computed metric is stale because the standby is no longer receiving fresh data.

One ACTIVE SRL for a receiving thread is normal. Use ARCHIVED, SEQUENCE#, process activity and the alert log to understand whether it is currently being written or has completed and is waiting for archival. Do not clear or drop a group simply because its status is ACTIVE.

When RFS Is Not Using an SRL

Compare the primary ORL byte size with the receiver SRL byte size.
Count SRL groups by thread, not only as a database total.
Verify that every source RAC thread is represented.
Check the SRL members, ASM disk groups or file-system paths.
Check the local archive destination and fast recovery area.
Review the standby alert log around the affected sequence.

A full FRA can look like an SRL shortage because completed groups cannot be archived and reused. Adding more SRLs may postpone the symptom, but it does not fix the archive destination.

Once RFS is writing to the expected group, separate transport lag from apply lag. Rising transport lag points toward the send-and-receive path, network capacity, destination errors, authentication or receiver I/O. Low transport lag with rising apply lag points toward MRP, standby CPU, datafile I/O, hot blocks or the redo workload. SRLs remove the wait for a completed log; they do not make an underpowered standby faster.

Oracle SRLs Compared With PostgreSQL WAL Reception

PostgreSQL does not have an object that a DBA creates as a direct equivalent of an Oracle SRL group. The receiver-side stages are still useful to compare.

Oracle Data Guard	PostgreSQL physical replication
Redo transport processes send redo	WAL sender streams WAL
RFS receives redo	WAL receiver receives WAL
Current redo is persisted in an SRL	Received WAL is persisted under `pg_wal`
MRP applies redo	Startup process replays WAL
Archived redo can resolve gaps	WAL archive and `restore_command` can supply missing segments

The commit settings are comparable only at a high level. PostgreSQL synchronous_commit = remote_write waits until the standby operating system has written the WAL, while remote_apply waits until replay. Waiting for replay gives a stronger visibility point but can add significant commit latency. These settings should not be presented as exact equivalents of Oracle protection modes.

-- PostgreSQL primary
SELECT application_name, client_addr, state, sync_state,
       write_lag, flush_lag, replay_lag,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)
       ) AS replay_gap
FROM pg_stat_replication;

-- PostgreSQL standby SELECT status, sender_host, latest_end_lsn,
last_msg_send_time, last_msg_receipt_time FROM pg_stat_wal_receiver;

SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

The time-based replay query is useful, but it is not an exact RPO measurement. It can return NULL before any transaction has been replayed, and an idle database can make a time delay look larger than the actual byte gap. As with Oracle, position, time and metric freshness should be read together.

Mini Case Study: Apply Lag After a Redo Resize

Problem

A busy RAC database had its online redo logs increased from 1 GB to 4 GB to reduce excessive switching during batch processing. Transport lag remained low, but apply lag grew through every sequence and dropped after the next log switch. The first assumption was that MRP could not keep up.

Root cause

Only the primary ORLs had been changed. The standby still had 1 GB SRLs. RFS could not use those smaller groups for the 4 GB incoming sequence and used the standby archived redo path. The alert log and an exact comparison between V$LOG and V$STANDBY_LOG exposed the mismatch.

Fix we made

New 4 GB SRLs were added for every RAC thread, using one more group per thread than the online redo count. The same layout was created on the primary for its future standby role. The old groups were removed only after they were no longer active. RFS resumed writing to the current SRL, and the saw-tooth apply lag disappeared.

The operational lesson was simple: every online redo log change needs a Data Guard log-layout review. Redo sizing is not a primary-only activity.

Lessons From the Field

Most SRL problems are configuration drift, not an Oracle defect. A DBA resizes online redo, adds a RAC thread, rebuilds a standby or moves storage and forgets to repeat the SRL review on every role-capable database.

A small scheduled control is valuable here. Compare ORL and SRL counts per thread, exact byte sizes, member status and database role across the configuration. That check is more useful than discovering the mismatch during the next DR exercise.

The FRA and local archive destination belong in the same monitoring story. A completed SRL must be archived before it can be reused. Destination errors and space pressure can therefore surface as an apparent SRL availability problem.

Finally, test the design in both directions. Broker validation is useful, but a controlled switchover proves the services, transport path, redo reception, apply behavior and ability to switch back. High availability is a tested behavior, not only a correct set of control-file records.

Quick Takeaways

SRLs allow recovery to apply the current incoming sequence without waiting for a completed archived log.
Every source thread requires at least one more SRL group than its primary online redo log groups.
Match the SRL size to the primary redo layout and assign RAC threads explicitly.
SRLs do not turn ASYNC transport into guaranteed zero data loss.
Create the complete SRL layout on both primary and standby before a role transition.
Validate actual RFS use, recovery mode, lag freshness, archive health and broker readiness.
Low transport lag with high apply lag normally points to the standby apply path.

Conclusion

Standby redo logs sit directly between redo transport and recovery. When they are correctly sized, assigned and available, RFS can write the incoming stream to an SRL and MRP can apply it without waiting for a completed archived log. This reduces apply latency and leaves less recovery work during a role transition.

The boundaries are equally important. SRLs do not change ASYNC transport into a zero-data-loss design, repair a slow standby or compensate for a full FRA. Protection mode determines when the primary can acknowledge a commit. Transport health shows what reached the standby. Apply capacity determines how quickly the standby datafiles catch up.

For a single-instance database, use the primary ORL count plus one and verify the exact size. For RAC, apply the same formula separately to every source thread. Create the full layout on every database that may become a redo receiver, including the current primary.

Add this to the next Data Guard review: compare online and standby redo layouts across every member and check both current and future roles. Repeat the review after a redo resize, RAC topology change, standby rebuild or storage migration. Then validate the runtime path. Confirm RFS activity, real-time recovery mode, fresh lag timestamps, archive health and broker readiness before a DR test has to find the gap for you.

Frequently Asked Questions

Are SRLs mandatory on the current primary database?

SRLs are not used while the database remains in the primary role. Oracle recommends keeping them on the primary so it is immediately ready to receive redo after a switchover or failover. Broker-managed role transitions validate them on both sides, and Fast-Start Failover requires them on the primary.

How many SRL groups are required?

Calculate the count separately for each source redo thread. The required baseline is the number of primary online redo log groups in that thread plus one. If thread 1 has three ORL groups, it needs at least four SRL groups.

Do SRLs guarantee zero data loss with ASYNC transport?

No. SRLs persist redo that has reached the standby. ASYNC commits can complete before the redo arrives, so the RPO still depends on transport progress and the exact failure sequence.

Can an SRL simply be larger than the online redo log?

An SRL must not be smaller than the largest source online redo log. For a clean and predictable layout, use matching sizes rather than treating a larger file as the normal standard.

Is an ACTIVE SRL a problem?

Usually not. One active group for a receiving thread is expected. Investigate when completed groups cannot be archived or reused, no group is available for the next sequence, or the alert log reports an SRL error.

What Have You Seen in Production?

Have you seen a Data Guard configuration where transport looked healthy but SRL drift caused delayed apply or a switchover warning? Was it exposed by a redo resize, RAC thread change, FRA issue, broker validation or an actual DR test? Share the check that helped you find it.

Official References

Oracle Database 19c Data Guard - Redo Transport Services
Official guidance for managing standby redo logs, sizing, the per-thread ORL plus one requirement, RFS behavior and archived redo fallback.
Oracle Database 19c Data Guard Broker - Managing Configuration Members
Official guidance for configuring SRLs on primary and standby databases, preparing for switchovers and failovers, and Fast-Start Failover requirements.

ORA-12547 from SRVCTL While SQL*Plus Startup Works – Tracing It Back to a Post-Patching Relink Issue

noreply@blogger.com (Nikhil Kotak) — Sat, 19 Sep 2026 13:09:00 +0000

I recently worked on an Oracle database that could be started normally from SQL*Plus, but failed every time Clusterware tried to start it through srvctl. The error returned by Clusterware was ORA-12547: TNS:lost contact.

The final fix was to relink the database Oracle Home, but I did not reach that conclusion from the error message alone. The useful clues came from comparing SQL*Plus and SRVCTL startup behaviour, noticing what was happening to the Oracle executable during the SRVCTL attempt, and then going back through the last database startup and patching history.

The SRVCTL Startup Failure

The database resource was registered with Clusterware, but the startup failed immediately:

$ srvctl start database -d BPL1VODA

PRCR-1079 : Failed to start resource ora.bpl1voda.db
CRS-5017: The resource action "ora.bpl1voda.db start"
encountered the following error:

ORA-12547: TNS:lost contact

CRS-2674: Start of 'ora.bpl1voda.db' failed

ORA-12547 is not very specific. Oracle describes it as the partner process unexpectedly disappearing, usually during process startup. In a local database startup like this, I would not immediately treat the word TNS as evidence of a listener or network problem. The process being launched by Clusterware may simply be terminating before the startup can complete.

SQL*Plus Startup Worked

Before spending more time inside CRS, I wanted to know whether the database itself had a startup problem. I set the database environment and tried a direct startup as the Oracle software owner.

$ export ORACLE_SID=BPL1VODA
$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/BPL
$ export PATH=$ORACLE_HOME/bin:$PATH

$ sqlplus / as sysdba

SQL> startup;

The database started successfully.

That was an important separation point. If there had been a basic problem with the SPFILE, control files, database files, ASM disk groups or recovery, I would normally expect the manual startup to expose it as well.

Instead, the behaviour was:

SQL*Plus startup worked.
SRVCTL startup consistently failed with ORA-12547.

At that stage it looked as though something was different in the Clusterware-managed startup path. I also verified the Oracle Home registered with the database resource rather than assuming that SRVCTL was using the same environment as my shell.

$ echo $ORACLE_HOME
$ ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/BPL

$ which oracle

$ srvctl config database -d BLL1VODA
$ srvctl status database -d BPL1VODA

The configured database home was the expected one, so I continued looking at what happened when Clusterware actually attempted the startup.

The Oracle Executable Kept Changing Back

This was the first behaviour that made the Oracle Home itself suspicious.

While troubleshooting, I checked $ORACLE_HOME/bin/oracle. Most of the executables in the same database home were owned by oracle:oinstall, but the main Oracle executable was showing this state:

-rwxr-s--x. 1 grid asmadmin 464944488 Sep 11 17:27 oracle

I corrected the ownership and permissions of the executable and verified them before trying another startup

The surprising part was that the correction did not stay in place. The moment I ran:

$ srvctl start database -d BLP1VODA

the executable returned to:

grid:asmadmin
-rwxr-s--x

and the database startup failed again with the same ORA-12547

At first this looked like a straightforward executable permission problem. But repeatedly correcting the file manually was clearly not solving anything. Clusterware was touching the binary again during startup, so there was another reason behind the behaviour.

The s in the group execute position also showed that the set-GID bit was being used. ASM environments have specific handling around the group and set-GID state of the RDBMS oracle executable, including Oracle's setasmgidwrap processing. For that reason, I did not use the permission change alone as proof of the root cause.

What was more relevant was the complete behaviour: SQL*Plus could start the database, SRVCTL could not, and the state of the main executable was being changed again as soon as Clusterware tried to launch the database.

Going Back to the Last Time the Database Was Running

Instead of continuing to retry the same startup, I went back through the timeline. I wanted to know when this database had last been running normally and, more importantly, why it had been stopped.

That brought the recent patching activity into the investigation.

I correlated the instance startup information with the SQL patch history and the Oracle Home inventory:

SQL> SELECT instance_name,
       host_name, SQL > 
       startup_time,
       status
FROM v$instance;


SQL> SELECT patch_id,
action,
status,
action_time,
description FROM dba_registry_sqlpatch ORDER BY action_time DESC;

$ORACLE_HOME/OPatch/opatch lsinventory

The database had been stopped as part of a patching activity. The patch information confirmed that patching had taken place, and when I correlated the timestamps with the database and Clusterware history, I could not find a successful normal SRVCTL-managed startup after that activity.

One detail is worth being precise about. V$INSTANCE does not tell us whether an instance was started through SQL*Plus or SRVCTL, and DBA_REGISTRY_SQLPATCH does not record the startup method. I used these timestamps together with the patch and Clusterware history to reconstruct the sequence.

What I now had was a much more useful timeline:

The database had previously been running normally.
It was stopped for patching.
The database Oracle Home had gone through patching.
There was no confirmed successful SRVCTL-managed startup afterwards.
A direct SQL*Plus startup still worked.
An SRVCTL startup failed with ORA-12547.
Correcting the Oracle executable manually did not persist; the SRVCTL attempt changed it back again.

Why Relinking Became the Next Logical Test

At this point there was still no single log line saying, "the Oracle Home must be relinked." The direction came from the combination of evidence.

The database engine itself could start, the problem appeared only through the Clusterware-managed path, the Oracle executable was being modified during that path, and the last significant change to the Oracle Home had been patching.

Rather than continuing to change file permissions manually, I decided to rebuild the executables in the database Oracle Home.

With the database stopped, I ran:

$ cd $ORACLE_HOME/bin

$ relink all

The relink completed successfully. I then returned to the same operation that had been failing:

$ srvctl start database -d BPL1VODA

$ srvctl status database -d BPL1VODA

This time the database started successfully through SRVCTL.

That result was much more useful than simply seeing SQL*Plus work. The exact Clusterware-managed startup that had been failing before the relink was now working without manually changing the executable.

What I Consider the Most Likely Root Cause

I would not document this incident as "ORA-12547 is fixed by relink." ORA-12547 has several possible causes, and relinking should not be used as a generic response to that error.

For this case, the strongest explanation is that the database Oracle Home had been left in an inconsistent executable or linking state following the patching activity.

The reasoning is based on the sequence:

The Oracle Home had recently been patched.
The problem appeared on the subsequent Clusterware-managed startup.
SQL*Plus could still start the instance.
SRVCTL repeatedly failed with ORA-12547.
The main Oracle executable was being returned to the same unexpected ownership/group state during the failed SRVCTL attempt.
Relinking the database Oracle Home corrected the condition.
The same SRVCTL startup succeeded immediately afterwards.

Unless an older patching or relink log contains a definite linker error, I would still avoid claiming that a previous relink failure was conclusively proven. What the evidence does show is that rebuilding the Oracle executables corrected the post-patching state that was preventing Clusterware from starting the database.

Where to Look for Evidence of a Relink Problem

After recovering the database, it is worth going back through the logs rather than treating the successful startup as the end of the investigation.

Oracle writes relink output below $ORACLE_HOME/install. I would look for the logs created during the original patching window as well as the new successful relink log.

# Relink history
$ ls -ltr $ORACLE_HOME/install/relinkActions*

$ grep -Ein 
'error|failed|fatal|undefined|cannot find|not found|ld:|collect2|make.*error' 
$ORACLE_HOME/install/relinkActions*.log

# OPatch history

$ ls -ltr $ORACLE_HOME/cfgtoollogs/opatch/

$ grep -RniE 'relink|linking|make|error|failed' ORACLE_HOME/cfgtoollogs/opatch/ 2>/dev/null

# Clusterware agent trace

$ grep -iE 'ORA-12547||failed|error' ORACLE_BASE/diag/crs/$(hostname -s)/crs/trace/crsd_oraagent_oracle.trc

An older relinkActions log from the patching window is particularly useful. Messages such as undefined reference, cannot find, linker errors, make: *** Error or permission failures would provide direct evidence that a previous linking operation did not finish cleanly.

The CRS agent trace may not contain an explicit relink error. Clusterware may only know that the process it launched terminated and therefore report ORA-12547. This is why the patching timeline and Oracle Home history can be more useful than searching for one specific error string.

What the SQL*Plus Test Actually Proved

The successful SQL*Plus startup was initially one reason to suspect SRVCTL itself, but it is important not to take that test further than it proves.

It proved that the database instance could be started directly using that Oracle Home. It did not prove that the entire Oracle Home and the Clusterware/ASM-managed startup path were in the expected state.

That difference was exactly where this issue was hiding.

One Check I Would Add After Patching

For a database managed by Oracle Clusterware, seeing an open database after patching is not enough for me to consider the startup validation complete.

I also want to know that the database can be managed through the same path that will be used during the next restart or failover.

$ srvctl status database -d BPL1VODA

$ srvctl stop database -d BPL1VODA

$ srvctl start database -d BPL1VODA

$ srvctl status database -d BPL1VODA

In this case, a manual startup would have given a false sense that everything was fine. The problem only became visible when Clusterware attempted to manage the database through SRVCTL.

The error itself was only one part of the investigation. The useful path was to compare the two startup methods, observe what Clusterware was doing to the executable, trace the database back to the patching activity, and then test the Oracle Home rather than continuing to modify permissions manually.

Oracle References

Oracle Error Help – ORA-12547: TNS:lost contact
Oracle describes ORA-12547 as the partner unexpectedly disappearing, usually during process startup.
Oracle RAC 19c – Administering Database Instances and Cluster Databases
Covers starting and stopping RAC databases and instances through SRVCTL.
Oracle RAC 19c – Server Control Utility Reference
Official SRVCTL command reference for Oracle RAC resources.

Oracle CPU Spike After Statistics Gathering: A Real-World Optimizer Investigation

noreply@blogger.com (Nikhil Kotak) — Mon, 07 Sep 2026 05:58:00 +0000

Every Oracle DBA has experienced that moment when monitoring alerts suddenly explode across the dashboard. Everything was running smoothly, and then without a warning - the CPU utilization jumps to 95%, applications slow down, and users start reporting performance issues.

One of the most frustrating parts of such incidents is when nothing obvious changed. No deployments, no schema modifications, and no new batch jobs. Yet the database is clearly struggling.

In many real-world cases, the root cause is surprisingly subtle: a change in optimizer statistics leading to a different execution plan. Oracle's automatic statistics gathering is powerful, but it is not always perfect and especially when data distribution is skewed or histograms behave unexpectedly.

In this article, we will walk through a real-world Oracle CPU spike investigation. You will see the step-by-step troubleshooting approach using AWR, execution plan history, and histogram analysis. We’ll also cover how the issue was fixed using manual statistics and SQL plan baselines to stabilize performance.

If you ar an Oracle DBA responsible for production environments, this guide will help you recognize the warning signs and quickly resolve similar high CPU incidents before they impact users.

When the Database Suddenly Went High CPU

It started as a normal afternoon when monitoring alerts began appearing.

CPU utilization: 95%+ and Application response times was extremely slow
No deployments, changes or maintenance activities

From an operational perspective, this scenario is common in large enterprise databases. Performance issues often arise not from new changes, but from optimizer behaviour reacting to existing data differently.

At this stage, the priority was clear- that is to identify the SQL statement consuming the most CPU.

Step 1: Identify the Top SQL Consuming CPU

The first step in any Oracle performance investigation is to determine which SQL statement is responsible for the workload.

A quick query against v$sql revealed the top CPU consumers.

SELECT sql_id, executions, elapsed_time, cpu_time, sql_text
FROM v$sql
ORDER BY cpu_time DESC
FETCH FIRST 5 ROWS ONLY;

One query clearly stood out that it was dominating CPU usage.

This approach is widely recommended in Oracle performance tuning because a small number of SQL statements typically account for the majority of database load.

Once the SQL ID was identified, the next step was to determine why the query suddenly became expensive.

Step 2: Investigate Execution Plan Changes

Oracle queries can suddenly become slower if the optimizer decides to use a different execution plan.

To verify this, the historical execution plans were checked using:


SELECT *
FROM dba_hist_sql_plan
WHERE sql_id='sqlid0sqltext1'
ORDER BY timestamp;

The result showed something interesting.

The query's execution plan changed after the nightly statistics collection job. This explained why the query had been performing well previously but suddenly started consuming excessive CPU resources.

Execution plan changes triggered by statistics updates are a well-known cause of performance issues in large Oracle environments.

But why did the optimizer choose a different plan? The answer often lies in histograms and data distribution.

Step 3: Check Histogram Behaviour on Critical Columns

Histograms help Oracle understand data distribution within columns, which is essential for generating efficient execution plans. However, they can also introduce instability when data is skewed.

To investigate further, the histogram for the relevant column was examined.

SQL> SELECT endpoint_value, endpoint_number
FROM dba_tab_histograms
WHERE table_name='EMPLOYEE'
AND column_name='EMPLOYEE_FEED';

The results showed a highly skewed distribution in the EMPLOYEE_FEED column.

When statistics were gathered using the default AUTO_SAMPLE_SIZE, the optimizer misinterpreted the data distribution and selected an inefficient execution plan.

This is not uncommon in production environments with skewed data patterns, such as regional or status-based columns.

Fixing the Issue: Stabilizing the Optimizer

Once the root cause was identified, two corrective actions were taken.

1. Regather Statistics with Controlled Sampling

Instead of relying on automatic sampling, statistics were gathered with a manual sample size.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    'SALES',
    'CUSTOMERS',
    estimate_percent=>50,
    method_opt=>'FOR ALL COLUMNS SIZE AUTO'
);

Using a higher sample percentage provides more accurate cardinality estimates, especially for skewed datasets.

2. Capture the Good Execution Plan Using SQL Plan Baselines

To prevent the optimizer from choosing an unstable plan again, the working plan was locked using SQL Plan Management.

EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id=>'sqlid0sqltext1'
);

SQL Plan Baselines allow Oracle to preserve known good plans, preventing unexpected performance regressions caused by statistics updates.

Results After the Fix

The impact of these changes was immediate.

CPU usage dropped from 95% → 40%
Query response times returned to normal
Application performance stabilized

This confirmed that the issue was entirely caused by optimizer misestimation due to statistics sampling and skewed data.

Unique Insight: Why Auto Statistics Sometimes Fail

Oracle's automatic statistics gathering works well in most environments, but it can struggle in situations involving:

Highly skewed data
Rapidly changing data distributions
Large partitioned tables
Queries sensitive to histogram accuracy

Many DBAs assume automatic statistics are always safe, but in high-scale systems, controlled statistics strategies often produce more predictable performance.

Experienced DBAs frequently implement:

Custom stats gathering schedules
Column-level histogram control
SQL Plan Baselines for critical queries

This combination provides both optimizer intelligence and plan stability.

Quick Takeaways

High CPU spikes in Oracle databases are often caused by execution plan changes.
Always identify the top CPU-consuming SQL statements first.
Statistics updates can trigger unexpected optimizer decisions.
Histograms on skewed columns may cause incorrect cardinality estimates.
Manual statistics sampling can improve optimizer accuracy.
SQL Plan Baselines help prevent performance regressions.
Stable statistics strategies are critical for large production systems.

Conclusion

Unexpected CPU spikes are among the most stressful incidents for any Oracle DBA. What makes them particularly challenging is that they often occur without obvious changes to the system.

As this case demonstrated, the root cause can sometimes be hidden in something as routine as automatic statistics collection. When data distribution is skewed, the optimizer may misinterpret cardinality estimates and choose inefficient execution plans.

The key to resolving such issues quickly is a structured investigation approach. Start by identifying the top SQL statements consuming resources, verify whether the execution plan has changed, and analyze histograms or statistics that might have influenced the optimizer’s decision.

More importantly, prevention is just as critical as troubleshooting. Implementing controlled statistics gathering, monitoring histogram behavior, and protecting critical queries with SQL Plan Baselines can significantly reduce the risk of unexpected performance regressions.

For Oracle DBAs managing high-volume production systems, understanding how the optimizer interprets statistics is a crucial skill. The database optimizer is powerful—but like any intelligent system, it performs best when guided with the right data and safeguards.

If you’ve encountered similar optimizer surprises in your environment, consider reviewing your statistics strategy today. A small adjustment could prevent the next major performance incident.

Frequently Asked Questions (FAQs)

1. Why do Oracle execution plans change after statistics gathering?

Execution plans may change because the optimizer uses updated statistics to estimate cardinality and costs. If the new statistics differ significantly from previous ones, the optimizer may choose a different plan.

2. What is AUTO_SAMPLE_SIZE in Oracle statistics gathering?

AUTO_SAMPLE_SIZE allows Oracle to automatically determine the sampling rate for statistics collection. While convenient, it may sometimes misrepresent skewed data distributions.

3. When should DBAs use SQL Plan Baselines?

SQL Plan Baselines are useful for critical queries where performance stability is essential, especially in systems with frequent statistics updates.

4. How do histograms affect query performance?

Histograms provide detailed information about data distribution. They help the optimizer estimate row counts accurately, but if misinterpreted, they can also lead to inefficient execution plans.

5. How can DBAs prevent optimizer-related performance issues?

Best practices include:

Monitoring execution plan changes
Managing histograms carefully
Using SQL Plan Management
Gathering statistics with controlled sampling

Share Your Thoughts

Have you ever experienced a sudden Oracle CPU spike caused by an optimizer plan change? Would love to hear how you diagnosed and resolved it in your environment.

If you found this article useful, feel free to share it with fellow DBAs or your database engineering team. Real-world troubleshooting stories often help others avoid the same production surprises.

Your feedback and experiences are always welcome!

Split Brain in Oracle RAC: What Every DBA Must Know

noreply@blogger.com (Nikhil Kotak) — Sun, 06 Sep 2026 10:55:00 +0000

If you have worked with Oracle RAC long enough, you know the cluster is only as strong as its communication layer. Most days, RAC behaves beautifully ; instances coordinate, cache fusion works seamlessly, and life is good. But when inter-node communication breaks down, things can get ugly very quickly.

One of the most dangerous scenarios in a RAC environment is a split brain condition. It is not just a theoretical concept., it is something that can bring your cluster to its knees if not handled correctly. I have personally seen environments where a poorly configured interconnect or a flaky switch triggered node evictions during peak hours

In this article, I will break down what split brain really means in practical terms, how Oracle RAC prevents it, and what you as a DBA should monitor proactively. We will also go through essential commands, real-world scenarios, and operational insights that are rarely discussed in documentation.

What Split Brain Actually Means in RAC
At its core, split brain is a communication failure between RAC nodes.
When nodes stop seeing each other over the private interconnect, each node may assume it is the only surviving member of the cluster. Now imagine both nodes continuing to process transactions independently against the same storage—that is where data corruption risk comes in.

In a healthy RAC setup:

The Nodes exchange heartbeat signals over the private interconnect
Clusterware keeps track of node membership & All nodes agree on cluster state

In a split scenario:

Heartbeat is lost & Nodes cannot validate each other
Each node believes the other is down

Oracle does not allow both to continue. It forces a decision.

Why Split Brain Happens in Production
From experience, split brain is rarely caused by a single obvious failure. It is often a combination of infrastructure and configuration issues.
Common triggers I have seen:

Interconnect failure
NIC issues, bonding misconfiguration, or cable problems

Network switch problems
Especially when interconnect traffic shares switches with other workloads

High CPU or OS hang
Node becomes unresponsive and misses heartbeat intervals

Improper interconnect configuration
Wrong interface priority or MTU mismatch

Split brain is not always a "network down" scenario. Even latency spikes or packet drops can trigger it.

How Oracle RAC Protects Your Data
Oracle RAC is designed with a strong principle: Data integrity is more important than availability.

When a split condition is detected, Oracle Clusterware steps in immediately.

1. Node Eviction
Oracle resolves the conflict by evicting one node from the cluster.

The node that loses quorum or heartbeat validation is removed
Its database instance is terminated & Access to shared storage is blocked

This ensures only one side continues processing.

2. Voting Disk Mechanism
Voting disks act as arbiters.

Each node votes to confirm cluster membership
Majority wins & the losing node is evicted

Hence, Always use an odd number of voting disks (3 or 5)

3. Heartbeat Monitoring
Cluster Synchronization Services (CSS):

Tracks node liveness and Uses heartbeat signals over interconnect
Missed heartbeats beyond threshold trigger eviction

4. Fencing
Once evicted, a Node is completely isolated from shared storag and Prevents accidental writes

Essential Commands Every RAC DBA Should Know
During an incident, speed matters. You do not have time to google commands.
Cluster Health Checks :

crsctl check cluster -all
crsctl stat res -t

Node Information olsnodes -n

Voting Disk Status crsctl query css votedisk

CRS Logs (Eviction Analysis) cd $GRID_HOME/log/<node_name>/crsd/

When Node Eviction Becomes a Problem

While node eviction protects data, frequent evictions are a red flag.
You might face some Application disruptions / Session drops / Performance instability

If evictions happen often, it is not a RAC problem-- it is an infrastructure problem.
Focus on the areas such as Interconnect latency / Network packet drops / CPU starvation

Mini Case Study: The Invisible Network Issue
In one production setup, we observed random node evictions during peak batch processing.
Initial suspicion was Database load & High I/O

But the real issue was a misconfigured network switch intermittently dropping jumbo frames.

Symptoms we noticed :

No full network outage and

Just enough packet loss to miss heartbeats

Fix:

Standardized MTU across nodes and switches

Dedicated interconnect VLAN

Lesson learnt : Split brain is often triggered by subtle network inconsistencies, not total failures.

DBA Perspective

Eviction is not failure- it is protection
Many teams panic when they see node eviction. It is actually RAC doing its job.

Interconnect deserves dedicated design
Never treat it as "just another network interface

Monitoring must go beyond database

such as Network latency OS load & NIC errors

CRS logs are gold during incidents
Do not rely only on alert logs

Virtual environments increase risk
Resource contention can mimic node failure

Timeout parameters are not tuning knobs for beginners
Changing misscount or disktimeout without understanding can make things worse

Quick Takeaways

Split brain occurs when RAC nodes lose communication
Oracle resolves it using node eviction
Voting disks ensure majority-based decisions
Interconnect stability is critical
Frequent evictions indicate deeper infrastructure issues
Monitoring CRS logs is essential for diagnosis
Always prioritize data integrity over uptime

Conclusion

Split brain is one of those topics that sounds theoretical until you experience it in production. And when you do, it quickly becomes clear why Oracle designed RAC the way it did.

The cluster does not try to be overly smart -, it simply ensures that only one consistent version of truth exists. That is why node eviction happens so aggressively. It may feel disruptive, but it is preventing something far worse: data corruption.

As a DBA, your responsibility is not just to understand split brain, but to prevent the conditions that lead to it. That means investing time in network design, validating interconnect configurations, and monitoring system health beyond the database layer.
If you are running RAC today, take a moment to review your setup:

Are voting disks properly configured?

Is your interconnect isolated and reliable?

Do you actively monitor cluster health?

Because in RAC, stability is not accidental—it is engineered.

FAQs
1. Does split brain always lead to data corruption?
No. Oracle prevents corruption using node eviction and fencing mechanisms.

2. Can I disable node eviction in RAC?
No. It is a core safety feature and cannot be disabled.

3. What is the ideal number of voting disks?
Always use an odd number -. typically 3 or 5.

4. How do I identify which node was evicted?
Check CRS logs under $GRID_HOME/log/<node>/crsd/ and alert logs.

5. Can high CPU cause split brain?
Yes. If a node cannot send heartbeat signals due to CPU starvation, it may be evicted.

What is your experience with RAC node evictions in production?
Have you ever traced a split brain issue back to something unexpected like network latency or OS load?
Drop your insights in comments below., these are the scenarios every DBA learns from.

PostgreSQL Crash Recovery: An Oracle DBA’s View

noreply@blogger.com (Nikhil Kotak) — Sat, 05 Sep 2026 12:29:00 +0000

The first PostgreSQL crash I handled felt unusually quiet. I was prepared to mount the database, check the required logs and start a recovery procedure. Instead, PostgreSQL detected the unclean shutdown, replayed WAL and opened the database without any DBA command.

That automatic restart can make crash recovery appear simpler than it really is. Recovery time still depends on checkpoint activity, WAL volume, storage performance and the availability of required WAL files. A database that normally starts in seconds can behave very differently after a write-heavy workload or storage incident.

What Happens During PostgreSQL Crash Recovery?

When PostgreSQL starts after an operating system failure, forced termination or power loss, it checks the cluster control information and identifies the latest valid checkpoint. Recovery begins from the REDO location recorded in that checkpoint, which can be earlier than the checkpoint record itself.

PostgreSQL then replays the required Write-Ahead Log records to restore physical consistency. After WAL replay completes, it performs an end-of-recovery checkpoint and starts accepting connections.

The database log normally contains messages similar to:

LOG:  database system was interrupted
LOG:  database system was not properly shut down
LOG:  automatic recovery in progress
LOG:  redo starts at ...
LOG:  redo done at ...
LOG:  database system is ready to accept connections

An invalid record length message near the end of recovery does not always indicate corruption. It can simply mean PostgreSQL reached the end of valid WAL. Always read the messages around it before treating it as a failure.

Oracle Redo and PostgreSQL WAL Are Not Identical

The write-ahead principle is familiar to Oracle DBAs. Oracle writes redo before modified blocks reach the data files, while PostgreSQL writes WAL before dirty pages are flushed. Both use these records to recover from a crash.

The main difference is how uncommitted work is handled. Oracle uses undo to roll back uncommitted transactions. PostgreSQL does not physically reverse every uncommitted tuple during startup. MVCC transaction visibility rules keep rows from uncommitted transactions invisible, and vacuum can reclaim the dead tuple versions later.

Recovery Area	Oracle	PostgreSQL
Recovery records	Online redo	Write-Ahead Log
Uncommitted transactions	Recovered using undo	Hidden through MVCC visibility
Recovery-time target	`FAST_START_MTTR_TARGET`	No direct equivalent
Recovery monitoring	`V$INSTANCE_RECOVERY`	Logs and operating-system metrics

How Checkpoints Affect Recovery Time

Checkpoint configuration influences how much work may be required after a crash, but PostgreSQL does not provide a guaranteed recovery-time target.

checkpoint_timeout limits the time between automatic checkpoints.
max_wal_size is a soft limit that can trigger checkpoints based on WAL volume.
checkpoint_completion_target spreads checkpoint writes across the available interval. It does not control checkpoint frequency directly.

More frequent checkpoints may reduce the amount of WAL replay required, but they can increase normal write I/O. They may also produce more full-page-image WAL because the first change to a page after a checkpoint can require the complete page image to be logged.

This is the same broad performance-versus-recovery trade-off Oracle DBAs associate with FAST_START_MTTR_TARGET, but the controls are not equivalent.

Useful Checks for the DBA

Start with the PostgreSQL and operating-system logs. If the cluster is stopped, pg_controldata can help identify the recorded cluster state and checkpoint positions. It is not a live recovery progress tool.

# Service, recovery and storage checks
systemctl status postgresql
journalctl -u postgresql --since "30 minutes ago"
pg_controldata "$PGDATA"
iostat -xz 1

# Active checkpoint configuration

SELECT name, setting, unit, source FROM pg_settings WHERE name IN (
'checkpoint_timeout',
'checkpoint_completion_target',
'max_wal_size',
'min_wal_size',
'full_page_writes',
'wal_compression'
) ORDER BY name;

# PostgreSQL 17 and later

SELECT * FROM pg_stat_checkpointer;

# WAL generation

SELECT
wal_records,
wal_fpi,
wal_bytes,
wal_buffers_full,
stats_reset FROM pg_stat_wal;

On releases before PostgreSQL 17, checkpoint counters are available in pg_stat_bgwriter. Compare counter changes over a fixed interval instead of interpreting cumulative values in isolation.

A Production Scenario

On one write-heavy system, application latency showed regular spikes and recovery after a server failure took longer than expected. The production configuration had been inherited from a much smaller environment:

checkpoint_timeout = 5min
max_wal_size = 1GB
checkpoint_completion_target = 0.5

Checkpoint statistics showed that WAL volume was repeatedly triggering requested checkpoints before the timeout. Checkpoint writes were also compressed into a relatively short period.

After measuring WAL generation, disk capacity and storage latency, the team increased max_wal_size, extended the checkpoint interval and used a higher checkpoint_completion_target. The exact values were selected from workload measurements rather than copied from a generic recommendation.

Normal latency became more stable, but the work did not end there. Controlled crash testing was used to measure the new recovery time. Larger checkpoint intervals can improve normal performance while increasing the amount of WAL that may need to be replayed, so both sides of the change must be tested.

Common Recovery Problems

Required WAL Is Missing

This often happens because someone deleted files from pg_wal, a separate WAL filesystem was not mounted, or a restored data directory is inconsistent with the available WAL. Never delete WAL files manually to solve a space problem.

Recovery Is Running but Very Slowly

Check storage latency before concluding that PostgreSQL is stuck. WAL replay can become slow on a throttled cloud volume or a storage device reporting repeated I/O errors.

The WAL Filesystem Keeps Growing

Failed archiving, inactive replication slots, disconnected standbys and backup activity can retain WAL. Check these consumers before modifying checkpoint settings.

Unlogged Tables Lose Their Contents

Unlogged tables are not crash-safe. PostgreSQL truncates them automatically after a crash. They should never be used for data that must survive an unexpected restart.

Lessons from the Field

A graceful restart does not test the crash-recovery path.
Recovery time depends on storage throughput as much as checkpoint configuration.
A large pg_wal directory does not automatically indicate a fault.
Do not use pg_resetwal as a routine startup fix. It is a last-resort salvage utility.
Measure checkpoint frequency, WAL generation, archive failures and actual restart duration.
Keep the expected recovery log sequence and escalation steps in the production runbook.

Frequently Asked Questions

Does PostgreSQL require manual crash recovery?

Normally, no. PostgreSQL starts WAL replay automatically after detecting an unclean shutdown.

Can applications connect during primary crash recovery?

Regular connections are normally accepted only after crash recovery has completed. A hot standby accepting read-only connections is a different recovery scenario.

Will reducing checkpoint_timeout always improve recovery?

No. It may reduce recovery work but can increase checkpoint I/O and full-page-image WAL generation during normal operation.

Is there a PostgreSQL equivalent of V$INSTANCE_RECOVERY?

PostgreSQL does not provide a direct view showing a reliable remaining-time estimate for primary crash recovery. DBAs normally follow the logs and operating-system I/O metrics.

When should pg_resetwal be used?

Only after normal restore and WAL recovery options have been exhausted. If it is used to salvage a cluster, the safer approach is to extract the data and rebuild the database rather than return the same cluster directly to production.

Conclusion

PostgreSQL crash recovery is designed to be automatic, but it should never be treated as a black box. WAL restores physical consistency, MVCC keeps uncommitted tuples invisible, and later vacuum activity reclaims dead row versions.

For Oracle DBAs, redo and checkpoint concepts provide a useful starting point. The major differences are the absence of an Oracle-style undo rollback phase and the lack of a direct PostgreSQL equivalent to FAST_START_MTTR_TARGET or V$INSTANCE_RECOVERY.

Review checkpoint statistics, monitor WAL retention, validate storage performance and test an unclean restart in a controlled environment. Until it has been measured under realistic workload conditions, the expected recovery time is only an assumption.

Oracle Indexes: Fix Slow SQL Without Overdoing It

noreply@blogger.com (Nikhil Kotak) — Mon, 31 Aug 2026 09:33:00 +0000

A SQL query running for 8 seconds may not create panic during testing. In production, the same query can become a real incident when it runs thousands of times from an application, report, or batch process.

I have seen queries drop from seconds to milliseconds after adding one correct index. I have also seen OLTP systems become slower because every column was indexed without understanding the workload. Both situations are common.

Indexes are powerful, but they are not free. They reduce unnecessary reads for SELECT queries, but they add cost to INSERT, UPDATE, DELETE, storage, statistics gathering, and maintenance. A good DBA does not create indexes blindly. A good DBA checks the SQL pattern, data volume, selectivity, execution plan, and write workload before touching production.

Why Indexes Matter in Production

When Oracle does not have a useful access path, it may scan the entire table. For a small lookup table, that may be acceptable. For a large transaction table, it can mean high logical reads, CPU pressure, slow application response, and unnecessary I/O.

The real question is not whether an index exists. The real question is whether the index matches how the application searches the data.

A query filtering by customer_id needs a different indexing approach from a report filtering by status, region, and payment_mode. Similarly, a query using UPPER(name) may not benefit from a normal index on name.

B-tree Indexes: The Normal Choice for OLTP

B-tree indexes are the default and most commonly used Oracle index type. In most OLTP systems, this is where DBAs should start.

They work well for columns with many distinct values, such as order IDs, customer IDs, email addresses, account numbers, transaction IDs, and date columns. These indexes are useful for equality searches, range scans, joins, and primary key lookups.

For example, if an application frequently searches orders by customer, a simple B-tree index can reduce unnecessary table reads.

SELECT *
FROM orders
WHERE customer_id = 10045;

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

For queries that regularly filter by multiple columns, a composite index may be better than multiple single-column indexes.

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, order_status);

Column order matters in composite indexes. If the application usually searches by customer_id first, then customer_id should usually be the leading column. Index design should follow real SQL patterns, not assumptions.

Bitmap Indexes: Useful, But Not for Every System

Bitmap indexes are useful for columns with very few distinct values, such as status, gender, active flag, region, or processed flag. They can perform very well in reporting systems and data warehouses where queries combine multiple low-cardinality filters.

CREATE BITMAP INDEX bix_orders_status
ON orders(order_status);

The problem starts when bitmap indexes are created on busy OLTP tables. Concurrent DML can suffer because bitmap locking can affect a range of rows, not just one row. If many sessions are inserting or updating at the same time, this can become a blocking issue.

My practical rule is simple: bitmap indexes belong mostly to read-heavy reporting or warehouse-style workloads. On a hot transaction table, think twice before using them.

Function-Based Indexes: When SQL Changes the Column

One common production issue is that the index exists, but Oracle still does not use it as expected. Many times, the reason is that the SQL applies a function on the column.

For example, an index on name may not help much if the application searches using UPPER(name). Oracle is not searching the raw column anymore. It is searching the result of the function.

SELECT *
FROM employees
WHERE UPPER(name) = 'JOHN';

CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));

Function-based indexes are useful when the application SQL cannot be changed quickly. But do not create them for every function you see. They still add storage and DML overhead.

For date columns, rewriting the predicate is often better than creating another function-based index. Instead of using TRUNC(order_date) in the WHERE clause, use a date range when possible.

WHERE order_date >= DATE '2026-07-05'
AND   order_date <  DATE '2026-07-06'

Invisible Indexes: Safer Testing Before Dropping

Invisible indexes are helpful when you want to test whether an index can be removed. The index remains in the database and continues to be maintained, but the optimizer normally ignores it.

ALTER INDEX idx_orders_status INVISIBLE;

ALTER INDEX idx_orders_status VISIBLE;

This is safer than dropping an index immediately. Some indexes are used only during month-end reports, audit jobs, quarterly reconciliation, or batch processing. A short observation window can give the wrong conclusion.

However, remember that invisible indexes are still maintained during DML. If the goal is to reduce write overhead, the benefit comes only after the index is dropped.

Checking Index Usage Without Guesswork

Before removing an index, monitor its usage and review the workload. Oracle provides index usage monitoring, but the result should be interpreted carefully.

ALTER INDEX idx_orders_status MONITORING USAGE;

SELECT index_name, used, start_monitoring FROM v$object_usage;

ALTER INDEX idx_orders_status NOMONITORING USAGE;

Do not monitor for one quiet day and drop the index. A rarely used index may still protect an important business job. The safer approach is to monitor, review SQL history, make the index invisible, observe a full business cycle, and then decide.

Oracle vs PostgreSQL Indexing View

Oracle and PostgreSQL both use B-tree indexes as the normal default, but the operational handling differs.

Oracle supports B-tree, bitmap, function-based, invisible, domain, reverse key, compressed, and other index options.
PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN, and BRIN indexes.
Oracle bitmap indexes are physical index structures. PostgreSQL may use bitmap scans during execution, but that is not the same as creating an Oracle bitmap index.
PostgreSQL DBAs commonly use CREATE INDEX CONCURRENTLY to reduce blocking during index creation.
Oracle DBAs may use online index creation where available and licensed.

The common lesson is the same in both databases: indexing is workload-driven. The database engine can only help if the index design matches the query pattern.

Common Failure Scenarios

1. Index Exists, But Query Still Scans the Table

This usually happens because of stale statistics, poor selectivity, function usage on the column, datatype conversion, or the optimizer deciding that a full scan is cheaper.

2. Too Many Indexes on a Hot Table

Every insert or update becomes more expensive because Oracle must maintain multiple indexes. This is painful on audit, transaction, session, queue, and event tables.

3. Bitmap Index on OLTP Table

Bitmap indexes can create blocking problems in concurrent DML workloads. They may look attractive because the column has few values, but the workload type matters more.

4. Wrong Composite Index Order

A composite index is not automatically useful. The leading column should match how the SQL filters data. Poor column order can make the index less effective.

5. Index Dropped Too Quickly

An index may not be used during normal daytime workload but may be critical during month-end or batch processing. Dropping it without a proper observation window can create a delayed production issue.

Mini Case Study: Query Slowed Down After Data Growth

A customer order search was running fine for years. Later, the same SQL started taking several seconds during peak time. The application team said the query had not changed, and they were right. The table had changed.

The table grew from a few million rows to hundreds of millions of rows. Existing single-column indexes were no longer enough for the actual search pattern. The SQL usually filtered by customer and order status together.

WHERE customer_id = :b1
AND   order_status = :b2;

CREATE INDEX idx_orders_customer_status ON orders(customer_id, order_status)
ONLINE;

After testing and refreshing statistics, logical reads dropped sharply and the query became stable again. The lesson was simple: data growth exposes weak index design. A query that behaves well at 5 million rows may become expensive at 200 million rows.

DBA Insights From Production

Do not create indexes only because a query is slow. First check the execution plan and row selectivity.
Review existing indexes before adding new ones. Duplicate and overlapping indexes are common in old systems.
Be careful with bitmap indexes on transactional tables.
For date filters, try range predicates before creating function-based indexes.
Use invisible indexes when testing index removal.
Monitor index usage across a real business cycle, not only during normal office hours.
Treat indexes like production code. They need review, testing, monitoring, and cleanup.

FAQs

Why is Oracle not using my index?

Common reasons include stale statistics, poor selectivity, function usage on the column, datatype mismatch, bind variable behavior, or the optimizer estimating that a full table scan is cheaper.

Should every foreign key be indexed?

Not blindly, but many OLTP systems benefit from indexing foreign keys, especially when parent rows are deleted or updated, or when joins frequently use the foreign key column.

When should bitmap indexes be avoided?

Avoid bitmap indexes on tables with frequent concurrent inserts, updates, or deletes. They are usually better for reporting, warehouse, or read-heavy workloads.

Is a composite index better than two single-column indexes?

Sometimes yes. If the SQL always filters by two columns together, one well-designed composite index can be better than two independent indexes. The leading column is important.

How long should index usage be monitored before dropping?

Monitor long enough to include daily, weekly, month-end, batch, reporting, and audit workloads. A short monitoring window can miss rarely used but important indexes.

Conclusion

Indexes can fix slow SQL quickly, but they can also become hidden production debt. A good index reduces unnecessary reads and gives Oracle a better path to the data. A poor index adds storage, write overhead, maintenance effort, and sometimes confusion during troubleshooting.

For most OLTP workloads, B-tree indexes are the practical default. Bitmap indexes should be handled carefully and are usually better suited for read-heavy reporting systems. Function-based indexes are useful when SQL applies expressions to columns, but rewriting the predicate may be cleaner. Invisible indexes are useful when cleaning up doubtful indexes safely.

The best indexing decisions come from real workload evidence. Check the execution plan, data distribution, table growth, existing indexes, and DML volume. Do not add indexes emotionally, and do not drop them casually.

Review your top SQL regularly, clean up duplicate indexes carefully, test changes in a controlled way, and monitor the impact after every release. Index tuning is not a one-time task. It is part of healthy production database maintenance.

The Oracle DBA Role in the AI Era - See how Oracle AI Database 26ai, Autonomous AI Database and MAA are reshaping the DBA role across reliability, security and enterprise AI

noreply@blogger.com (Nikhil Kotak) — Sat, 22 Aug 2026 13:50:00 +0000

Database automation has removed plenty of manual effort, but it has not removed operational accountability. Oracle can now automate provisioning, patching, backup, scaling, and parts of performance management. At the same time, applications are becoming more distributed, security boundaries are expanding, and AI workloads are placing new demands on enterprise data.

This has changed the DBA role rather than eliminated it. The focus is moving from performing every database task manually to selecting the right Oracle capability, defining safe operating boundaries, and proving that the complete service can meet its availability, recovery, security, performance, and cost objectives.

The modern Oracle DBA is no longer responsible only for an instance. The responsibility now extends across the application-to-data path, including cloud services, AI access, identity, networking, observability, and recovery dependencies.

Autonomous Does Not Mean Unmanaged

Oracle Autonomous AI Database demonstrates how far routine database administration has progressed. Provisioning, tuning, patching, monitoring, backup, and scaling can be automated, allowing teams to spend less time maintaining infrastructure and more time improving the service built on top of it.

The important production questions, however, remain with the database and platform teams:

Does the selected deployment model meet the application’s availability requirement?
Are automatic scaling limits based on measured demand or optimistic estimates?
Can the database be restored within the agreed recovery time?
Are wallets, identities, private endpoints, and encryption keys available during recovery?
Will the application reconnect correctly after a role transition?
Are automated backups being periodically restored and validated?

Automation changes how an operation is executed. It does not prove that the result satisfies the business requirement. A successful backup job is not the same as a tested recovery, and an available database endpoint does not guarantee that the application can reach it.

Oracle’s Reliability Stack Still Needs DBA Judgement

Oracle has a mature set of products for mission-critical availability, but each addresses a different failure model.

Oracle Real Application Clusters provides instance-level availability and scale-out within a cluster. Oracle Active Data Guard maintains physical standby databases for disaster recovery, data protection, and eligible read workload offloading. Oracle GoldenGate supports logical replication, migrations, and real-time data integration across Oracle and non-Oracle platforms.

Oracle Zero Data Loss Autonomous Recovery Service adds database-aware backup protection, recovery validation, and an option for real-time redo protection. For demanding consolidation and performance requirements, Oracle Exadata provides an engineered platform in which Oracle Database, compute, storage, and networking are designed to operate together.

These technologies should not be treated as interchangeable checkboxes. RAC does not replace regional disaster recovery. GoldenGate does not automatically replace a physical standby. A standby database does not replace protected backups because corruption, accidental changes, or security incidents may also be replicated.

Oracle Maximum Availability Architecture helps organize these capabilities into validated availability and recovery patterns. The DBA’s contribution is translating business expectations into the appropriate architecture and then rehearsing the failure paths that matter.

Oracle AI Database Brings AI Closer to Enterprise Data

AI introduces another change to the DBA role. Application teams increasingly need semantic search, retrieval-augmented generation, natural-language access, and controlled agent interaction with business data.

Oracle AI Database 26ai supports this through capabilities such as AI Vector Search and Select AI. Vector embeddings can be stored alongside relational, JSON, text, spatial, and graph data instead of forcing every AI use case onto a separate specialist database.

This matters because enterprise searches rarely depend on similarity alone. A support assistant might search product documentation semantically, but the results may also need to be filtered by customer, product version, geography, entitlement, and data-classification rules. Oracle can combine those business filters with vector similarity processing close to the governed source data.

Select AI and Natural-Language Queries

Oracle Select AI can translate natural-language requests into SQL and support retrieval-augmented generation over private data. This can make enterprise information more accessible without requiring every user to understand the underlying schema.

Natural-language access does not remove the need for database controls. Generated SQL still consumes resources, accesses protected objects, and may return sensitive information. Production adoption therefore requires least-privilege access, approved schemas, auditing, workload controls, and testing against ambiguous requests.

The DBA does not need to become a data scientist to support these workloads. However, the role should understand vector indexes, embedding dimensions, similarity metrics, data-refresh behaviour, and the performance effect of combining vector search with conventional SQL predicates.

AI Agents Need Database Guardrails

Oracle’s Select AI Agent framework and Autonomous AI Database MCP Server can provide controlled ways for compatible agents to use defined database tools. This creates useful possibilities for analytics, support applications, and assisted operations.

It also introduces a new class of service account. An agent should receive only the tools and data required for its purpose. Read-only access should be the default where possible, destructive actions should require explicit controls, and every operation should remain attributable through auditing.

Valid SQL is not automatically safe SQL. The same principle applies whether a statement comes from an application, a human administrator, or an AI agent.

One Platform, Several Data Models

One practical strength of Oracle AI Database is its converged design. Relational data, JSON documents, vectors, spatial data, and graph relationships can be managed within the same platform. This can reduce the number of separate systems required for an application and, with that, the number of security models, backup processes, monitoring stacks, and integration pipelines.

JSON-Relational Duality allows applications to access the same underlying data as relational tables or JSON documents. True Cache provides an automatically maintained, transactionally consistent cache for read-intensive applications. SQL Firewall can help identify and block unauthorized SQL activity based on approved SQL patterns.

Using one database for every workload is not automatically the correct design. The decision should still consider scalability, operational isolation, skills, licensing, service availability, and cost. The benefit is that Oracle gives architects the option to consolidate compatible data requirements without immediately introducing another specialist platform.

A Practical Oracle AI Scenario

Consider an internal support assistant that must answer questions using customer records, product documentation, operational procedures, and recent transaction information.

AI Vector Search could locate relevant documents by meaning, while relational predicates restrict the results by business unit, customer entitlement, geography, and data classification. Select AI could provide natural-language interaction, and approved agent tools could retrieve specific operational information.

The database may run on Autonomous AI Database or Exadata, depending on control, deployment, and workload requirements. RAC or the service’s built-in availability architecture can protect against local failures. Active Data Guard or Autonomous Data Guard can provide standby protection, while Recovery Service protects backups. GoldenGate may be introduced when selected data must be replicated to another platform.

Although this is presented as an AI project, its production readiness depends on familiar DBA questions:

Which data is the agent permitted to retrieve?
How are source documents refreshed, retained, and removed?
Are vector data and indexes included in the recovery design?
How will similarity searches affect database resources during peak periods?
Can generated SQL bypass an application-level security rule?
What happens to the complete service during database failover?

The AI feature may be new, but the requirement for controlled access, measurable performance, and tested recovery is not.

A Modern Oracle DBA Responsibility Map

Operational Area	Oracle Capability	DBA Decision or Validation
Routine Operations	Autonomous AI Database	Define scaling limits, maintenance expectations, backup policies, connectivity, and service ownership.
Local Availability	Oracle RAC and Exadata	Validate workload suitability, application continuity, service placement, and cluster failure behaviour.
Disaster Recovery	Active Data Guard or Autonomous Data Guard	Select the protection mode, measure transport and apply lag, test role transitions, and validate application recovery.
Backup Protection	Zero Data Loss Autonomous Recovery Service	Set retention policies, protect recovery credentials, test restores, and confirm the achievable recovery point.
AI Search	AI Vector Search	Choose the vector design, index strategy, similarity metric, data-refresh process, and workload controls.
Natural-Language Access	Select AI	Restrict schema access, audit generated SQL, protect sensitive data, and test ambiguous requests.
Real-Time Integration	Oracle GoldenGate	Define consistency expectations, conflict handling, replication scope, monitoring, and recovery procedures.

The Multicloud DBA Boundary

Oracle Database services are no longer limited to a single cloud strategy. Oracle Database@Azure, Oracle Database@Google Cloud, and Oracle Database@AWS allow organizations to place Oracle database services closer to applications running in their chosen hyperscaler.

The database service may be managed on Oracle infrastructure, but the complete application can still depend on another provider’s compute, networking, identity, monitoring, and storage services. Incident ownership and disaster-recovery runbooks must reflect that split.

A successful database switchover is only technical completion. Operational success means the application has reconnected, dependent services are working, monitoring has followed the new primary, and the business transaction has been validated.

What DBAs Should Build Next

The strongest path forward is not to abandon traditional Oracle knowledge. It is to combine that foundation with a wider operating view.

Keep performance, recovery, transaction knowledge current.
Learn how Autonomous AI Database changes operational ownership.
Understand where RAC, Data Guard, GoldenGate, Recovery Service, and Exadata fit.
Build practical knowledge of vectors, RAG, Select AI, and agent security.
Treat identity, certificates, networks, and key management as database dependencies.
Automate repeatable work like pre-checks, logging, exception handling, and safe recovery paths.
Measure performance and cloud consumption instead of relying only on provisioned capacity.

Good architecture in any environment or estate,- comes from selecting the smallest combination that meets the required performance, availability, security, recovery, and cost objectives., and later evaluating it and exploring & expanding it more

Frequently Asked Questions

Will Autonomous AI Database replace Oracle DBAs?

It reduces routine administration, but architecture, recovery validation, workload design, security, integration, governance, and business service requirements still need qualified ownership., so keep learning and exploring the Oracle Learnings and Certifications.

Is AI Vector Search a separate Oracle database?

No. AI Vector Search is integrated with Oracle AI Database, allowing vector similarity processing to be combined with relational and other supported data searches.

Does an AI agent need special database controls?

Yes. Agent access should follow least-privilege principles, use approved tools, remain auditable, and require stronger controls for any operation that can change production data or configuration.

Are RAC and Data Guard alternatives to each other?

No. RAC primarily addresses availability and scalability within a cluster, while Data Guard maintains standby databases for disaster recovery and data protection. Critical systems commonly use both for different failure scenarios.

Conclusion

Oracle has automated many tasks that once defined daily database administration, but the DBA role has moved toward higher-value decisions. Autonomous AI Database reduces routine effort. RAC, Active Data Guard, GoldenGate, Recovery Service, Exadata, and Oracle MAA support demanding availability and recovery requirements. Oracle AI Database 26ai adds a practical foundation for vector search, natural-language access, and governed AI applications.

The responsibility is therefore broader than keeping an instance online. The modern Oracle DBA must connect database behaviour with application continuity, security, cloud dependencies, AI access, recovery, and cost. The next practical step is to review one existing workload and confirm whether its architecture, automation, and recovery tests prove the business outcome rather than merely showing that individual components are healthy.

Official References

Relevant Oracle Learning and Certifications

DBAs who want to build skills around Oracle AI Database, autonomous operations, vector search, agentic AI, multicloud architecture, and availability can consider the following Oracle University learning paths and certifications.

Priority	Oracle Certification or Learning Path	Relevant Learning
1	Agentic AI Foundations Associate `1Z0-1157-26`	Introduces AI agents, agentic patterns, MCP, LangChain, OCI Enterprise AI, and the use of Oracle AI Database in agent-driven solutions.
2	Oracle AI Vector Search Professional	Covers vector fundamentals, embeddings, similarity searches, vector indexes, retrieval-augmented generation, and Select AI.
3	Oracle Autonomous AI Database Professional `1Z0-931-26`	Focuses on provisioning, scaling, patching, backup, security, performance management, and the operational lifecycle of Autonomous AI Database.
4	Agentic AI for Oracle AI Database Professional `1Z0-1159-26`	Provides advanced learning on agentic workflows, Select AI Agent, MCP integration, database tools, and governed access to enterprise data.
5	Oracle AI Database@Azure Architect Professional `1Z0-1147`	Validates skills in designing, deploying, connecting, and managing Oracle Database services within Microsoft Azure environments.
6	Oracle Database 19c: Data Guard Administration `1Z0-076`	Strengthens practical knowledge of standby databases, redo transport, apply services, protection modes, role transitions, and disaster recovery.

Suggested Learning Sequence

For an Oracle DBA moving toward data, cloud, and AI reliability responsibilities, the following sequence provides a useful progression:

Build the agentic AI foundation.
Learn vector embeddings, similarity search, and RAG through Oracle AI Vector Search.
Develop operational knowledge of Autonomous AI Database.
Move into database-native agentic workflows and MCP integration.
Add Oracle Database@Azure or another relevant multicloud specialization.
Continue strengthening traditional availability skills through Data Guard and Oracle MAA.

Build Hands-On Skills with Oracle AI Database 26ai Free

Certification learning becomes more valuable when it is supported by practical testing. Oracle AI Database 26ai Free provides a suitable environment for experimenting with vector data types, embeddings, similarity queries, JSON-Relational Duality, SQL Firewall, and other 26ai capabilities.

A useful learning exercise is to create a small document-search application, store embeddings with business metadata, perform semantic searches with relational filters, and then evaluate indexing, security, backup, and performance behaviour from a DBA perspective.

Upcoming Oracle Learning

Oracle University has announced an Exadata Database Service professional-level certification planned for October 2026. This should be particularly relevant to DBAs working with Exadata cloud deployments, consolidation, performance, and mission-critical database services.

Oracle's Foundations learning and certification remain available without charge. Professional-level learning paths generally require paid access under Oracle University’s 2026 model. Certification titles are now evergreen, although individual exam codes may continue to contain release identifiers. Review the Oracle University 2026 certification update and the corresponding MyLearn page before scheduling an exam.

Oracle Key Vault: DBA Guide for TDE Keys and scenarios

noreply@blogger.com (Nikhil Kotak) — Sun, 16 Aug 2026 10:26:00 +0000

A database backup can finish successfully and still fail during recovery.

That usually sounds strange until encryption enters the picture. RMAN backup is available, archive logs are available, Data Guard is in sync, but during restore someone asks the real question: where is the TDE wallet or master key?

This is where many production environments become risky. TDE wallets are present on database servers, Java keystores are owned by middleware teams, SSH keys are copied between hosts, certificates are renewed manually, and old wallet backups are kept in folders nobody wants to touch.

Oracle Key Vault helps reduce this spread by centralizing the management of TDE master keys, Oracle wallets, Java keystores, certificates, credential files, SSH keys, and other secrets. For a DBA, the value is not only security. It is also recoverability, auditability, controlled access, and cleaner operations during patching, migration, Data Guard switchover, and disaster recovery.

Why Key Management Becomes a DBA Problem

Encryption may start as a security requirement, but the operational impact often lands with the DBA team.

A few common production situations are very familiar:

A TDE wallet was created on the primary database server but was never validated on the standby.
A database restore completed, but encrypted tablespaces could not be opened.
A RAC node was rebuilt, but the Oracle Key Vault endpoint client was missed.
A certificate expired and the issue appeared during a maintenance window.
Audit asked who accessed or rotated a key, and the answer was scattered across tickets and server logs.

Oracle Key Vault brings these security objects under central control. Instead of each database server becoming its own small vault, OKV becomes the controlled repository where access, lifecycle, backup, and reporting can be managed properly.

The important production point is this: once a database depends on external key management, key availability becomes part of database availability.

How Oracle Key Vault Fits with TDE

Oracle Transparent Data Encryption protects data at rest. Oracle Key Vault does not replace TDE. It manages the keys and security objects used by TDE.

In a typical setup, the database server is enrolled as an Oracle Key Vault endpoint. That endpoint gets access to the required virtual wallet or security objects. The database can then access its TDE master encryption keys through the OKV client configuration.

For modern Oracle databases, the two parameters DBAs should pay attention to are WALLET_ROOT and TDE_CONFIGURATION. Older environments may still have wallet locations configured through sqlnet.ora, especially after upgrades or migrations, so it is worth checking the actual configuration before making assumptions.

SHOW parameter wallet_root
SHOW parameter tde_configuration

SET lines 200 COL wallet_type FOR a20 COL status FOR a20 COL keystore_mode FOR a20 COL wrl_parameter FOR a60

SELECT con_id,
wallet_type,
status,
keystore_mode,
wrl_parameter FROM v$encryption_wallet ORDER BY con_id;

SELECT tablespace_name,
encrypted FROM dba_tablespaces WHERE encrypted = 'YES' ORDER BY tablespace_name;

ALTER SYSTEM SET wallet_root='/u01/app/oracle/admin/PRODDB/wallet' SCOPE=spfile;

SHUTDOWN IMMEDIATE; STARTUP;

ALTER SYSTEM SET tde_configuration='KEYSTORE_CONFIGURATION=OKV' SCOPE=both;

In some environments, you may see values such as OKV|FILE or FILE|OKV. Do not treat these as random alternatives. They are commonly seen during migration or dual-keystore designs. Always verify the Oracle version, current wallet state, and whether the database is using united or isolated keystore mode in a multitenant setup.

The Endpoint Check DBAs Should Not Skip

The database view gives one side of the story. The endpoint check gives another.

Oracle Key Vault uses endpoint software on the database or application server. The command-line utility okvutil is one of the quickest ways to confirm whether the endpoint can talk to OKV and access the assigned security objects.

export OKV_HOME=/u01/app/oracle/okv
export PATH=$OKV_HOME/bin:$PATH

okvutil list

grep -i server $OKV_HOME/conf/okvclient.ora
ls -l $OKV_HOME/conf

nslookup okv-vip.example.com
nc -vz okv-vip.example.com 5696

grep -Ei "ORA-283|ORA-284|TDE|OKV|PKCS|wallet|keystore" 
$ORACLE_BASE/diag/rdbms/*/*/trace/alert*.log

If okvutil list fails, do not immediately recreate wallets. Start with simple checks. Is OKV_HOME correct? Is okvclient.ora present? Was the endpoint enrolled? Can the host reach the OKV server or VIP? Was the endpoint certificate replaced? Did the database move to another Oracle home?

For RAC, validate from every node. A common operational miss is checking only node 1. Everything looks healthy until a service relocates or an instance restarts on node 2.

High Availability: Central Control Needs Strong Design

Centralizing keys improves governance, but it also introduces a dependency that must be designed properly.

A running database may continue during a temporary OKV issue depending on cache behavior and whether the required key is already available. But that does not mean the design is safe. The real test is a database restart, PDB open, standby open read-only, key rotation, or restore to another host.

For critical databases, OKV should be included in the same operational checklist as Data Guard, RAC, backup, and monitoring.

OKV high availability or cluster design
OKV backup and restore testing
Endpoint configuration on all RAC nodes
Standby endpoint access
Firewall and DNS validation from primary and DR sites
Certificate expiry monitoring
Key rotation testing in lower environments

A DR test should not be marked successful only because redo apply worked. If TDE is enabled, encrypted data access after role transition must also be tested.

RAC, Data Guard, GoldenGate and Hybrid Estates

Oracle Key Vault is simple to understand in a single database setup. It becomes more interesting in a real enterprise estate.

In RAC, every node must have a working OKV endpoint configuration. Service relocation, node restart, or instance failover can expose node-specific issues.

In Data Guard, the standby must be able to access the required TDE keys. A standby can be perfect from a redo apply perspective but still fail after promotion if key access was never validated.

In GoldenGate environments, wallets, certificates, database credentials, and encrypted source or target data can all become part of the security chain. OKV helps reduce unmanaged local copies, but endpoint access and operational ownership must be planned properly.

In hybrid or multi-cloud setups, the difficult questions are often outside the database. Can the DR site reach OKV? Does DNS resolve correctly after failover? Are firewall rules open from all database subnets? Who owns certificate renewal? Is OKV monitored by both security and operations teams?

Oracle vs PostgreSQL: Same Goal, Different Model

Oracle has a strong database-native TDE model, and Oracle Key Vault integrates closely with that ecosystem.

PostgreSQL is different. Community PostgreSQL usually follows a layered security model. DBAs commonly combine SSL/TLS, SCRAM authentication, pgcrypto for specific column-level encryption use cases, OS or storage-level encryption, cloud KMS, or external vault solutions. Some PostgreSQL vendors provide their own TDE features, but it is not the same operational model as Oracle TDE with OKV.

SHOW ssl;

SELECT pid,
ssl,
version,
cipher FROM pg_stat_ssl WHERE ssl = true;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT pgp_sym_encrypt('sensitive-value', 'do-not-hardcode-this-key');

The PostgreSQL example above is only to show the capability. In production, encryption keys should not be hardcoded inside SQL, scripts, or application configuration files. Key ownership, rotation, and auditability must be designed properly.

Common Failure Scenarios

Most OKV-related issues are not mysterious. They usually come from one missed endpoint step, one wrong permission, one network rule, or one assumption that was never tested.

Database restart fails because the keystore cannot be opened.
Standby opens after failover but encrypted tablespaces are not accessible.
RAC node 1 works, but node 2 fails after service relocation.
OKV endpoint was re-enrolled, but the database server still has old configuration files.
Firewall or DNS changes break OKV connectivity from the DR site.
Key rotation was performed without validating standby, restore, and application behaviour.

A Case Study about: Data Guard Failover and Missing Key Access

A TDE-enabled Oracle database was protected by Data Guard. Redo transport was healthy, apply lag was under control, and the standby looked ready for DR.

During a failover test, the standby was promoted successfully. The database opened, but application testing failed when encrypted data was accessed.

The root cause was not Data Guard. The standby host did not have proper OKV endpoint access to the required virtual wallet. The team had validated replication but had not validated TDE key access from the standby server.

The fix was to enroll the standby correctly as an OKV endpoint, grant access to the required virtual wallet, validate endpoint connectivity, check wallet status from the database, and test encrypted data access again after role transition.

The lesson is simple: for encrypted databases, Data Guard readiness is not only redo apply. It also includes key availability.

DBA Insights from Production

The most dangerous encryption issue is the one discovered during recovery.

A database can run for months without anyone noticing that a standby endpoint is wrong, a wallet backup is outdated, a RAC node cannot reach OKV, or a restore host does not have the correct access. These issues surface during patching, failover, restore, or migration, exactly when pressure is highest.

My practical recommendations are:

Add OKV checks to database health check scripts.
Validate endpoint connectivity from every RAC node.
Include OKV validation in Data Guard switchover and failover drills.
Test encrypted tablespace access after restore.
Track certificate expiry and endpoint re-enrollment.
Do not rotate keys in production without a tested rollback and communication plan.
Document ownership between DBA, security, Unix, network, and cloud teams.

Oracle Key Vault gives control, but only when the operational process around it is strong.

Quick Takeaways

Oracle Key Vault reduces wallet and key sprawl, but it must be included in availability planning.
WALLET_ROOT and TDE_CONFIGURATION are key parameters for modern Oracle TDE setup.
okvutil list is a simple but powerful endpoint validation command.
RAC nodes, standby servers, and restore hosts must all be checked separately.
Data Guard readiness is incomplete without TDE key access validation.
PostgreSQL security usually needs a layered design rather than one OKV-style model.

FAQs

1. Does Oracle Key Vault replace TDE?

No. TDE encrypts the database data. Oracle Key Vault centrally manages the keys and security objects used by TDE.

2. Will OKV slow down every SQL query?

Normally, no. OKV is more relevant during key access, startup, keystore open, key rotation, migration, and recovery operations. It is not called for every row read.

3. What should I check first during an OKV issue?

Start with v$encryption_wallet from the database and okvutil list from the database server. These two checks quickly separate database-side and endpoint-side issues.

4. Is OKV mandatory for Data Guard?

No, not in every design. But if the primary database uses OKV-managed TDE keys, the standby must also be able to access the required keys.

5. How is PostgreSQL different?

Oracle has native TDE integration with OKV. PostgreSQL commonly uses TLS, pgcrypto, storage encryption, application-level encryption, external vaults, or cloud KMS depending on the requirement.

Conclusion

Oracle Key Vault solves a real production problem: too many keys, wallets, certificates, and secrets spread across too many systems.

For Oracle DBAs, the biggest benefit is not only central storage. It is controlled access, audit support, cleaner key lifecycle operations, and reduced recovery risk. When TDE is used across RAC, Data Guard, GoldenGate, and hybrid environments, this becomes even more important.

At the same time, OKV should not be treated as a black box owned only by the security team. Once Oracle databases depend on it, DBAs need to understand endpoint configuration, TDE parameters, standby access, certificate dependency, and what happens during failover or restore.

The best time to test key access is not during a failed recovery. It is during planned validation.

Review your encrypted databases. Check where the keys live. Confirm who can access them. Validate OKV from all nodes. Test standby and restore scenarios. Add OKV checks to your operational runbooks.

Encryption protects data, but key management protects the recovery path.

Have you ever seen a restore, switchover, or migration delayed because the wallet or encryption key was missing?

Share your experience below. These are the production lessons that are rarely visible in architecture diagrams.

Sometimes It Is Not One Big Problem. It Is Everything Running in the Background.

noreply@blogger.com (Nikhil Kotak) — Sun, 09 Aug 2026 13:15:00 +0000

After more than 12 years of working with production databases, I have learned that performance problems do not always begin with one terrible query.

Sometimes it is several smaller things happening together. One session is using slightly more CPU, another is holding a lock, a background job is running longer than expected, and storage latency has quietly increased. None of them looks disastrous alone, but together they make the whole system struggle.

Stress can build in much the same way.

We usually search for one obvious reason when we feel exhausted: a difficult project, a production incident, financial pressure, or a problem at home.

But sometimes there is no single cause. It is the late-night message we answered, the lunch we skipped, the conversation we kept replaying, the water we forgot to drink, and the sleep we sacrificed because one task was still pending

Each one appears manageable. Together, they become a collection of background processes that nobody remembered to stop.

The Small Things That Keep Us Switched On

Lesson 1: Work Never Completely Ends

Anyone working in production support knows that some situations cannot wait. If a critical database is unavailable, “Let us check tomorrow morning” is unlikely to be accepted as a recovery plan.

But not every email is an incident. Not every Teams message is an escalation. And not every sentence beginning with “quick question” will actually be quick.

When work follows us into every evening, the laptop may be closed, but the mind remains connected. We continue thinking about tomorrow’s change, an unresolved ticket, or the possibility that another message might arrive.

Effectively, the brain remains on call—usually without claiming the allowance.

There needs to be a reasonable difference between being responsible and being permanently available.

Lesson 2: We Postpone the Basics First

During a busy day, lunch is often treated as an optional dependency.

We plan to eat after the next meeting. Then another call starts, a ticket is escalated, and lunch finally happens at 4:30 PM. That is not necessarily intermittent fasting. Sometimes it is simply poor calendar management.

Water, movement, and sleep are handled in much the same way. The body may tolerate this for a while, which can make us believe everything is fine. However, inadequate sleep can affect attention, mood, and emotional regulation, making ordinary problems feel harder the following day.

No productivity application can fully compensate for repeatedly ignoring the basics—not even the one with the attractive dashboard.

Lesson 3: Some Meetings Continue in Our Heads

We have all attended a meeting that officially ended at 3:00 PM but continued in our mind until bedtime.

Perhaps someone spoke unfairly. Maybe an important question remained unanswered. We replay the discussion and prepare several excellent responses—unfortunately, all of them arrive four hours too late.

It is unrealistic to expect every emotion to disappear when a meeting ends. A more practical approach is to write down what happened, decide whether an action is required, and set a time to deal with it.

Otherwise, a 30-minute conversation can occupy mental storage for the rest of the day.

Lesson 4: Notifications Create Invisible Work

One notification does not seem stressful. The problem is the repeated switching

You are reviewing a document when a Teams message appears. Then an email arrives, the phone vibrates, and somebody requests a “small update” in another channel. By evening, you have been active for nine hours but are not entirely sure what you completed.

Research suggests that notifications can interrupt attention even when we do not open them. Turning off unnecessary alerts does not mean becoming unreachable. It means deciding which applications genuinely deserve permission to interrupt you.

If five applications are all marked as urgent, perhaps none of them understands the meaning of urgent.

Lesson 5: Sitting All Day Does Not Help

DBAs can spend several hours in the same chair, particularly during an incident, production change, or troubleshooting call.

At some point, the chair knows more about the shift schedule than the family does.

Movement does not have to mean a full gym session. A short walk after lunch, standing during a call, or stepping away from the screen between meetings can break a long period of sitting.

A ten-minute walk will not resolve chronic stress or fix the execution plan. It can, however, give the mind a useful pause.

Lesson 6: Not Every Energy Dip Is a Blood-Sugar Problem

Tiredness, irritability, and poor concentration are frequently blamed on a “blood-sugar crash.” That explanation is often too simple.

The same symptoms may be related to poor sleep, dehydration, irregular meals, stress, medication, or an underlying health condition. An afternoon slump alone is not enough to conclude that someone has hypoglycaemia.

Regular, balanced meals are a sensible starting point. If fatigue, dizziness, or other unexplained symptoms continue, speak with a qualified medical professional. Google, a smartwatch, and one motivational video do not constitute a clinical team.

Lesson 7 Connection Matters More Than We Admit

When work becomes intense, personal time is often the first item removed from the calendar.

We postpone meeting friends, speak less with family, and remain half-connected to the phone even when we are with people; just in case the world ends while we are having dinner.

Supportive relationships cannot remove difficult circumstances, but they can make those circumstances easier to manage. Sometimes a relaxed conversation with someone we trust is more useful than another hour spent trying to optimise ourselves.

Recovery May Begin with Subtraction

When a database is overloaded, we do not always solve the problem by adding more resources. Sometimes we stop an unnecessary job, reduce repeated calls, correct an inefficient query, or remove work that should never have been running.

The same principle can apply to us.

Recovery may not begin with another tracking application, a complicated morning routine, or ten new habits that eventually become eleven new sources of guilt.

It may begin with something much simpler:

Turn off one unnecessary notification.
Decline one meeting that does not need to happen.
Eat lunch without looking at a screen.
Leave one genuinely non-urgent message until tomorrow.
Allow an empty space in the calendar to remain empty.

The objective is not to eliminate every source of stress. Some pressure is unavoidable, particularly in roles carrying operational responsibility. The problem begins when pressure becomes continuous and recovery is repeatedly postponed.

We spend a great deal of time monitoring the health of our systems. Occasionally, we should check what has been running in our own background for too long.

Not every alert requires a P1 response—but please understand the alert before muting it and going for coffee.

Stay healthy.

Oracle Redo Logs: Sizing Without Guesswork

noreply@blogger.com (Nikhil Kotak) — Sun, 09 Aug 2026 09:44:00 +0000

Redo log problems rarely start with a clean error message. They usually show up as slow commits, archive destinations filling faster than expected, Data Guard apply lag, or batch jobs that suddenly take longer during month-end. By the time someone checks the alert log, the database may already be switching logs too frequently, checkpointing aggressively, or waiting for archiving to catch up.

A common reaction is to increase redo log size and move on. Sometimes that is required, but it is not always the real fix. Redo log sizing controls how frequently Oracle switches logs. It does not reduce the amount of redo generated by bad SQL, unnecessary indexes, row-by-row commits, or poorly designed batch jobs.

This post looks at Oracle redo log sizing from a production DBA angle. We will check log switch frequency, estimate redo generation rate, review MTTR guidance, discuss common failure scenarios, and compare the idea briefly with PostgreSQL WAL pressure.

Why Redo Log Sizing Matters in Production

Every committed change in Oracle must be protected by redo. LGWR writes redo records to the online redo logs, and in ARCHIVELOG mode, those logs are archived after switching. This is normal database behavior, but it becomes painful when the redo infrastructure is not sized for the workload.

When redo logs are too small, Oracle switches logs more often. Frequent log switches can increase checkpoint activity, put pressure on archiver processes, and expose slow storage or archive destinations. On a Data Guard setup, the same problem can also show up as transport or apply lag.

When redo logs are too large without thought, crash recovery behavior may not match business expectations. So the target is not simply “make redo logs huge”. The target is to size them based on workload, recovery requirement, and operational behavior.

The First Check: How Often Are Logs Switching?

Before touching redo log size, check the current switch pattern. I normally start with hourly switch frequency and then compare it against known business activity. A switch every 10 to 15 minutes during normal OLTP workload is usually comfortable. A switch every minute during regular workload needs investigation. During a heavy batch window, faster switching may be acceptable only if checkpointing, archiving, and standby apply are keeping up.

SELECT thread#,
       group#,
       ROUND(bytes/1024/1024) AS size_mb,
       members,
       archived,
       status
FROM v$log
ORDER BY thread#, group#;

SELECT thread#,
TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS switch_hour,
COUNT(*) AS switch_count FROM v$log_history WHERE first_time >= SYSDATE - 7 GROUP BY thread#, TO_CHAR(first_time, 'YYYY-MM-DD HH24') ORDER BY switch_hour, thread#;

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

In RAC, check each thread separately. If thread 1 switches every few minutes but thread 2 is quiet, the issue may be workload imbalance rather than redo sizing alone. Services, application connection pools, or batch jobs may be pinned heavily to one instance.

Size Redo Logs Based on Peak Redo Rate

Average redo generation is useful for capacity planning, but it is not enough for sizing redo logs. Production incidents usually happen during peak load: month-end processing, large data correction jobs, ETL runs, release deployments, or index maintenance.

The below query uses archived logs to estimate redo generation rate. Run it for the busiest window, not during a quiet period.

SELECT thread#,
       sequence#,
       first_time,
       next_time,
       ROUND(blocks * block_size / 1024 / 1024, 2) AS size_mb,
       ROUND((next_time - first_time) * 86400) AS seconds_taken,
       ROUND(
         (blocks * block_size / 1024 / 1024) /
         NULLIF((next_time - first_time) * 86400, 0),
         2
       ) AS mb_per_sec
FROM v$archived_log
WHERE first_time >= TO_DATE('2026-08-08 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND   first_time <  TO_DATE('2026-08-09 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND   dest_id = 1
AND   blocks > 0
ORDER BY first_time, thread#;

A simple sizing formula is:

Redo log size = peak redo MB/sec x desired switch interval in seconds

For example, if peak redo generation is 40 MB/sec and you want roughly 10-minute switches, the estimated log size is:

40 x 600 = 24000 MB

That does not automatically mean you should create 24 GB logs everywhere. It means the workload is heavy enough that you must validate redo size, archive throughput, Data Guard capacity, and recovery expectations together.

Use MTTR Advice, But Keep DBA Judgement

Oracle provides recovery-related advice through V$INSTANCE_RECOVERY. The column OPTIMAL_LOGFILE_SIZE can help you understand whether the current redo log size fits the configured recovery target.

This is useful, but it should not be treated as the only answer. MTTR is mainly about crash recovery time. Runtime behavior also depends on workload, checkpoint activity, I/O, archive speed, and standby apply capacity.

SHOW PARAMETER fast_start_mttr_target
SHOW PARAMETER statistics_level

SELECT target_mttr,
estimated_mttr,
ckpt_block_writes,
optimal_logfile_size FROM v$instance_recovery;

ALTER SYSTEM SET statistics_level = TYPICAL SCOPE=BOTH;

ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=BOTH;

If OPTIMAL_LOGFILE_SIZE is consistently higher than your current redo log size, that is a strong signal to review redo sizing. Still, test the change during a controlled window and confirm that recovery objectives are acceptable.

Redo Pressure Often Comes from Application Design

DBAs often get asked to fix redo growth from the database side, but many redo problems are created by application behavior. Oracle is doing what it is supposed to do: protecting changes. The question is whether the application is asking Oracle to protect too many unnecessary changes.

Too many indexes on write-heavy tables

Every insert, update, or delete may also modify indexes. A table with many indexes can generate much more redo than expected. Before dropping anything, review index usage carefully and consider invisible indexes for controlled testing.

Delete and insert instead of merge

Some batch jobs delete old rows and insert fresh rows because it is easy to code. From a redo and undo perspective, that can be expensive. If most rows already exist, a controlled MERGE or targeted update may generate less pressure.

Updating unchanged columns

ORM-generated SQL sometimes updates every column even when only one value changed. This can increase redo, undo, row locking, buffer activity, and standby apply workload. For critical batch jobs, review the generated SQL instead of assuming the database parameter is wrong.

SELECT owner,
       table_name,
       COUNT(*) AS index_count
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner, table_name
HAVING COUNT(*) > 10
ORDER BY index_count DESC;

ALTER INDEX app_owner.idx_orders_old INVISIBLE;

MERGE INTO customer_stage t USING customer_stage_new s ON (t.customer_id = s.customer_id) WHEN MATCHED THEN UPDATE SET t.status = s.status,
t.updated_at = SYSTIMESTAMP WHERE NVL(t.status, 'X') <> NVL(s.status, 'X') WHEN NOT MATCHED THEN INSERT (customer_id, status, updated_at) VALUES (s.customer_id, s.status, SYSTIMESTAMP);

Commit Frequency Is Not Just a Coding Style

Row-by-row commits are still common in old batch scripts. They look safe because each row is committed quickly, but they can create unnecessary LGWR activity and poor throughput. On the other hand, one huge commit may create restartability and rollback problems. The right commit size depends on business logic, restart design, and workload volume.

For bulk processing, commit in controlled batches where the business process allows it. Do not change commit logic only for performance without confirming functional requirements.

DECLARE
  l_count NUMBER := 0;
BEGIN
  FOR r IN (SELECT id, status FROM source_table) LOOP

```
UPDATE target_table
   SET status = r.status
 WHERE id = r.id;

l_count := l_count + 1;

IF l_count >= 5000 THEN
  COMMIT;
  l_count := 0;
END IF;
```

END LOOP;

COMMIT;
END;
/

Resizing Redo Logs Safely

Oracle does not simply resize an existing redo log group. The usual production approach is to add new larger groups, switch logs until old groups become inactive, and then drop the old groups. In RAC, do this per thread.

Before dropping any group, confirm it is not current and not required for recovery. Also remember Data Guard: standby redo logs should normally be sized consistently with online redo logs.

SELECT thread#,
       group#,
       ROUND(bytes/1024/1024) AS size_mb,
       status,
       archived
FROM v$log
ORDER BY thread#, group#;

ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 4G; ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 4G; ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 4G;

ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM CHECKPOINT;

DROP -- Do not run until the group is inactive and safe ALTER DATABASE DROP LOGFILE GROUP 3;

SELECT group#,
thread#,
ROUND(bytes/1024/1024) AS size_mb,
status FROM v$standby_log ORDER BY thread#, group#;

Common Failure Scenarios

Frequent log switches during normal workload: redo logs may be too small, or redo generation may have increased due to application changes.
Archive destination fills quickly: check redo generation rate, archive backup frequency, FRA size, and retention policy.
Data Guard lag during batch jobs: check primary redo rate, standby redo log size, network throughput, and MRP apply speed.
High log file sync: investigate commit frequency, LGWR latency, and storage performance.
High log file switch completion: check checkpoint pressure, archiving speed, and redo log size.

SELECT event,
       total_waits,
       ROUND(time_waited_micro/1000000, 2) AS time_waited_sec
FROM v$system_event
WHERE event IN (
       'log file sync',
       'log file parallel write',
       'log file switch completion',
       'log file switch (checkpoint incomplete)',
       'log file switch (archiving needed)'
)
ORDER BY time_waited_sec DESC;

SELECT dest_id,
status,
destination,
error FROM v$archive_dest WHERE status <> 'INACTIVE' ORDER BY dest_id;

SELECT name,
value,
unit,
time_computed FROM v$dataguard_stats WHERE name IN ('transport lag', 'apply lag', 'apply finish time');

Oracle Redo and PostgreSQL WAL: Same Pain, Different Knobs

Oracle redo and PostgreSQL WAL both protect database changes for recovery, but DBAs manage them differently. Oracle DBAs think in terms of redo log groups, members, threads, archiving, and standby redo logs. PostgreSQL DBAs deal with WAL segments, checkpoints, archiving, replication slots, and pg_wal growth.

In Oracle, redo log size directly affects switch frequency. In PostgreSQL, WAL segment size is normally fixed at cluster initialization, so day-to-day tuning is usually around checkpoint settings, archiving, replication slots, and workload behavior.

-- PostgreSQL WAL checks

SHOW wal_segment_size;
SHOW checkpoint_timeout;
SHOW max_wal_size;
SHOW min_wal_size;
SHOW archive_mode;
SHOW archive_command;

SELECT wal_records,
wal_fpi,
pg_size_pretty(wal_bytes) AS wal_generated FROM pg_stat_wal;

SELECT slot_name,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;

DBA Lessons from Production

If redo logs are resized on primary, review standby redo logs too.
Frequent log switches are not always a storage issue. Check SQL design and commit behaviour.
Unused indexes on write-heavy tables can silently increase redo volume.
Archive log backup performance matters. Bigger redo logs create bigger archived logs.
For Data Guard, monitor both transport lag and apply lag. They are not the same problem.

Mini Case Study: Data Guard Lag After a Batch Load

A production database started showing 40 to 50 minutes of Data Guard apply lag during a monthly batch. The primary database was still running, so the first assumption was network slowness between primary and standby.

The actual issue was a mix of small redo logs and a batch job that deleted and reinserted large data sets. The target table also had several indexes that were no longer useful for current reporting. During the batch, one RAC thread was switching logs almost every minute.

The fix was not one single change. Redo logs and standby redo logs were resized after checking peak redo rate. The batch logic was changed from delete/insert to a controlled merge for most rows. Two unused indexes were tested as invisible before being dropped. After that, standby lag still appeared during the peak, but it recovered in minutes instead of staying behind for almost an hour.

FAQs

How do I know if redo logs are too small?

Check log switch frequency using V$LOG_HISTORY. If switches are happening every minute during normal workload, review redo size, checkpoint waits, archive speed, and recent workload changes.

Does increasing redo log size reduce redo generation?

No. It reduces log switch frequency. Redo generation depends on DML volume, indexes, commit pattern, SQL design, supplemental logging, and application behavior.

Should all redo log groups be the same size?

Yes, keeping redo log groups the same size per thread makes behavior predictable and troubleshooting easier.

What should I check when Data Guard lag increases?

Check redo generation rate, transport lag, apply lag, archive gaps, standby redo log size, MRP status, network throughput, and standby I/O performance.

Is PostgreSQL WAL tuning the same as Oracle redo tuning?

The recovery concept is similar, but the controls are different. Oracle DBAs manage redo log groups and switch frequency. PostgreSQL DBAs usually manage WAL pressure through checkpoints, archiving, replication slots, and workload design.

Conclusion

Redo log sizing is not a cosmetic database setting. It affects commit performance, checkpoint behaviour, archiving, recovery, and Data Guard stability. If redo logs are too small, the database may spend too much time switching and checkpointing. If they are sized without considering recovery objectives, the design may not match operational expectations.

The best approach is simple: measure first, change later. Start with log switch frequency, then calculate redo generation rate during peak workload. Review V$INSTANCE_RECOVERY, wait events, archive destinations, and Data Guard lag. After that, look at the application side. Index count, batch logic, commit frequency, and unnecessary updates often explain more than redo log size alone.

For Oracle DBAs, redo tuning is a mix of database configuration and workload discipline. For PostgreSQL DBAs, WAL pressure tells a similar story with different knobs. In both cases, the write path exposes the truth. Review it before the next batch window, migration, or month-end run exposes it for you.

Sources : Oracle Docs / AskTom

Oracle 26ai AWR Enhancements for DBAs

noreply@blogger.com (Nikhil Kotak) — Sat, 08 Aug 2026 12:16:00 +0000

AWR used to be simple when most databases were single-instance, single-tenant, and all important workload stayed on the primary database.

That world is mostly gone.

Today, one CDB may host multiple PDBs. Reporting may run on Active Data Guard. Read traffic may be pushed toward True Cache. Replication may be handled through GoldenGate or XStream. When performance breaks, the first question is no longer just “what was the top wait event on the database?”

The better question is:

Which container, which workload, which replica, and which component actually caused the pain?

That is where the Oracle 26ai AWR enhancements become useful. They do not magically tune the database, but they reduce one common DBA problem: missing diagnostic data from the place where the issue happened.

Why AWR Needed to Change

In older environments, AWR was often treated as a primary database diagnostic tool. You generated the report, checked DB Time, top SQL, wait events, IO stats, and moved from there.

That worked reasonably well when the primary handled everything.

But in multitenant and offloaded architectures, this approach creates blind spots. A PDB can suffer from poor SQL while the CDB-level report looks acceptable. A standby can be overloaded by reporting queries while the primary AWR shows nothing unusual. Replication can lag because of apply-side SQL, disk latency, or memory pressure, but the normal AWR report may not clearly separate replication workload from application workload.

Oracle has started closing these gaps by making AWR more aligned with real deployment patterns: PDB-level diagnostics, ADG standby snapshots, True Cache visibility, and replication-focused sections.

Oracle 26ai AWR Enhancements in Multitenant

The most useful change for many DBAs is around PDB-level AWR collection.

Oracle documentation states that AWR_PDB_AUTOFLUSH_ENABLED has a default value of TRUE, which means automatic AWR snapshots are enabled by default for all PDBs in a CDB. Oracle also documents that automatic snapshot operations are enabled by default for a CDB and, in Oracle Database 26ai, for a PDB.

That sounds small, but operationally it matters.

Earlier, a DBA could troubleshoot a PDB performance issue only to realize that PDB-level AWR was never enabled. The incident would then move into guesswork: ASH if available, SQL monitor if still present, application timestamps, OS metrics, and whatever was left in memory.

Now the expectation is different. PDBs are first-class performance units.

Useful checks:

show con_name

select name, value
from v$parameter
where name = 'awr_pdb_autoflush_enabled';

select snap_id, dbid, instance_number, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc
fetch first 10 rows only;

For a specific PDB, connect into the PDB and validate snapshot behavior:

alter session set container = SALES_PDB;

select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc
fetch first 10 rows only;

The production impact is clear: when one PDB is noisy, you do not want to diagnose only from the CDB root. Consolidated environments hide problems. A batch job in one PDB can create IO pressure, CPU starvation, library cache pressure, or temp usage that hurts other tenants.

The trade-off is also real. More snapshots mean more diagnostic data. More diagnostic data means more SYSAUX usage. DBAs should monitor retention and space growth instead of assuming "default-enabled" means "free."

select occupant_name, space_usage_kbytes/1024 mb
from v$sysaux_occupants
where occupant_name like '%AWR%'
order by space_usage_kbytes desc;
fetch first 10 rows only;

Active Data Guard AWR: Finally Closer to Real Standby Troubleshooting

Active Data Guard is often sold as a way to offload read workload. In reality, many standby systems slowly become reporting platforms, ETL sources, dashboard backends, and ad-hoc query zones.

Then someone says: "The query is slow on standby but fast on primary." That is where DBAs need standby-side evidence.

Oracle AI Database 26ai documents AWR for Active Data Guard with automatic snapshots at CDB root and all PDBs, and Oracle states it is enabled by default for Active Data Guard in 26ai. Oracle’s observability blog also describes 26ai as delivering out-of-the-box AWR for ADG standby workloads, reducing older operational overhead around standby diagnostics.

This helps because standby performance problems are not always SQL tuning problems. Common causes include:

redo apply competing with reporting queries
storage latency on standby
stale or different optimizer behavior
read consistency waits
heavy parallel queries
lag-sensitive application reads

Useful standby checks:

select database_role, open_mode
from v$database;

select name, value, unit
from v$dataguard_stats
where name in ('transport lag','apply lag');

select process, status, thread#, sequence#
from v$managed_standby
order by process;

AWR on standby gives DBAs a cleaner way to separate “bad SQL” from “standby workload pressure.” That distinction matters. Tuning the SQL may help, but if redo apply is blocked by IO or CPU saturation, the real fix is capacity, workload isolation, or scheduling.

True Cache Monitoring: Useful, But Do Not Treat Cache as Magic

Oracle True Cache is designed as an in-memory, consistent cache for Oracle Database workloads, helping offload read activity from the primary database. Oracle introduced True Cache with Oracle Database 23ai, and Oracle’s current True Cache guide is published under Oracle AI Database 26ai.

AWR support for True Cache is useful because cache layers are often dangerous when they become invisible. If the application says “database is slow,” the real issue may be cache miss behavior, routing, memory pressure, or fallback to primary.

A DBA should not only ask whether True Cache is configured. The better questions are:

Is the cache actually serving the workload?
Is the application falling back to primary too often?
Is memory sized correctly?
Are read-mostly SQL patterns really suitable for cache offload?

Useful direction for monitoring:

select * from v$true_cache_stat;

The exact columns and usefulness will depend on version and RU level, so DBAs should validate against their installed Oracle documentation and not blindly copy scripts between estates.

The operational warning is simple: cache reduces pressure only when the workload matches the design. If the application sends mixed read-write behavior, uses session-specific logic heavily, or frequently misses cache, True Cache may add another diagnostic layer instead of reducing complexity.

Replication Reporting: Better Visibility for GoldenGate and XStream

Replication problems are rarely explained by one metric.

GoldenGate lag may be caused by extract, pump, network, trail file IO, replicat apply SQL, locking, missing indexes, or target-side resource pressure. Traditional database AWR may show waits, but not always in a way that clearly tells the replication story.

Oracle GoldenGate documentation for 26ai describes AWR report sections for replication, including Replication Top Wait Events, with foreground and background process wait events aggregated by event type and Oracle GoldenGate SQL module.

That is useful because replication workload should not be mixed blindly with application workload. A top SQL statement from Replicat has a different operational meaning from an application SQL_ID. One may require target index tuning. Another may require application tuning. Another may require changing batch size or parallelism.

Typical checks still matter:

select event, total_waits, time_waited
from v$system_event
where event like '%GoldenGate%'
   or event like '%Streams%'
order by time_waited desc;


For database-side replication sessions:

select sid, serial#, program, module, event, wait_class, seconds_in_wait
from v$session
where program like '%replicat%'
   or program like '%extract%'
   or module like '%GoldenGate%'
order by seconds_in_wait desc;

AWR does not replace GoldenGate commands or Microservices UI. It complements them. DBAs still need to check trail files, checkpoints, lag, discard files, and target-side constraints.

New Diagnostic Views DBAs Should Notice

Oracle 26ai also adds or highlights diagnostic views that help DBAs go deeper during troubleshooting.

DBA_HIST_SNAPSHOT_DETAILS displays table details for snapshots in the Workload Repository and is available starting with Oracle AI Database 26ai.

V$SQL_HISTORY displays SQL statements tracked by SQL history monitoring, but it is populated only when SQL_HISTORY_ENABLED is set to TRUE.

Example:

select sql_id, elapsed_time, cpu_time, rows_processed
from v$sql_history
order by elapsed_time desc
fetch first 20 rows only;


For optimizer environment investigations:

select name, value
from dba_hist_optimizer_env_details
where optimizer_env_hash_value = :hash_value
order by name;

These views are not a replacement for ASH, SQL Monitor, trace files, or execution plan history. But they give DBAs more breadcrumbs, especially when the problem has already passed and nobody captured live evidence.

The limitation is retention and configuration. If SQL history is not enabled, or AWR retention is too short, you still lose visibility.

Production Failure Scenarios

Scenario 1: Slow Report on ADG Standby

A reporting query runs in 3 minutes on primary but takes 25 minutes on standby. The application team blames the optimizer.

AWR on standby shows high IO waits and redo apply pressure during the same window. Data Guard stats show apply lag increasing. The SQL is not perfect, but it is not the only issue.

Fix may involve moving the report window, limiting parallelism, tuning standby storage, or separating heavy reporting from redo apply-sensitive periods.

Scenario 2: One PDB Creates CDB-Wide Noise

CPU spikes at CDB level, but the business impact is reported only by one application. PDB-level AWR shows a batch SQL consuming most DB time inside one PDB.

Without PDB snapshots, the DBA may chase system-wide symptoms. With PDB AWR, the noisy tenant becomes visible.

Scenario 3: GoldenGate Lag Misread as Network Lag

Replication lag increases. Network team finds no issue. AWR replication section shows Replicat-related wait events and top SQL on the target.

Root cause: missing index on target table after schema change.

Fix: create the required index, validate Replicat apply rate, and add schema drift checks.

Scenario 4: True Cache Does Not Reduce Primary Load

A new True Cache deployment is expected to reduce primary reads. Primary load remains high.

Cache monitoring shows many reads still going to primary due to routing and workload pattern mismatch. The fix is not “increase cache memory” blindly. The connection routing and SQL eligibility need review.

Mini Case Study: The Standby Was Not Slow, It Was Busy

A finance reporting workload was moved to an ADG standby to protect the primary OLTP system. For the first few weeks, everything looked fine. Then month-end processing started.

Symptoms:

reports timing out
apply lag increasing
storage latency spikes
no major issue visible in primary AWR

Initial assumption was bad SQL. But standby-side diagnostics showed that reporting queries and redo apply were fighting for the same IO bandwidth. A few parallel reports were consuming resources at exactly the wrong time.

Fix:

reduced parallel degree for selected reports
moved month-end report schedule away from peak redo apply
added standby-specific AWR review after batch windows
monitored apply lag and top standby SQL together

Lesson:

A standby is not free capacity. It is a recovery system doing read workload. Treat it casually and it will remind you during the worst possible window.

DBA Insights

The biggest mistake DBAs make with AWR is treating it as a report generator instead of a diagnostic system.

AWR is only useful when snapshots exist at the right level, retention covers the incident window, and the DBA knows which workload they are analyzing.

For Oracle 26ai AWR enhancements, I would watch these areas carefully:

SYSAUX growth after enabling more PDB-level snapshots
snapshot interval and retention standards across CDBs
ADG standby AWR availability before moving reporting workload
Replicat SQL visibility during replication lag
True Cache hit behavior before claiming primary offload success
monitoring dashboards that still show only CDB-level averages

Averages are dangerous in multitenant systems. AWR becoming more PDB-aware is a good thing, but DBAs must update their troubleshooting habits too.

FAQs

Is PDB-level AWR enabled by default in Oracle 26ai?

Yes. Oracle documentation states that AWR_PDB_AUTOFLUSH_ENABLED defaults to TRUE, enabling automatic AWR snapshots for PDBs by default.

Can I use AWR on Active Data Guard standby?

Yes. Oracle 26ai supports automatic AWR snapshots for Active Data Guard at CDB root and PDB levels, and Oracle documents this as enabled by default for ADG in 26ai.

Does AWR replace GoldenGate monitoring?

No. AWR helps correlate database waits, SQL, and resource usage for replication workload. GoldenGate lag, trail files, checkpoints, discard files, and process health still need GoldenGate-side monitoring.

Should I increase AWR retention after these enhancements?

Usually yes, but carefully. More PDB and standby snapshots can increase repository usage. Monitor SYSAUX and define retention based on your incident review window.

Is True Cache always useful for read workload?

No. It helps when workload routing and SQL patterns fit the cache model. If the application frequently falls back to primary or uses unsuitable access patterns, the benefit may be limited.

Conclusion

Oracle 26ai AWR enhancements make AWR more useful for how Oracle databases are actually deployed today: multitenant, replicated, standby-readable, and cache-assisted.

For DBAs, the value is not just automation. The value is evidence.

PDB-level snapshots help avoid CDB-level guesswork. ADG AWR helps troubleshoot where reporting queries really run. True Cache monitoring gives visibility into read offload behavior. Replication sections make GoldenGate and XStream workload easier to separate from normal application SQL.

This is a strong improvement in Oracle’s diagnostic stack. But it still needs DBA discipline: validate snapshots, watch SYSAUX, review retention, and make sure monitoring follows the workload.

AWR is getting smarter. DBAs still need to ask the right questions.

pg_switch_wal() in PostgreSQL: Small Command, Big Recovery Impact

noreply@blogger.com (Nikhil Kotak) — Sat, 25 Jul 2026 11:24:00 +0000

A PostgreSQL database can look completely healthy until the day you try to restore it.

Backups are available. WAL archiving is enabled. The archive directory has files. Monitoring shows green. Then, during PITR, PostgreSQL throws something like:

`could not locate required checkpoint record`

or recovery stops before the target time because the WAL needed for that moment was never archived.

This is where many DBAs learn an uncomfortable lesson: the WAL segment that contains the changes you need may still be sitting inside `pg_wal/` as the active segment. It has not filled up yet, so the archiver has not copied it.

On a busy system, WAL segments rotate quickly. On a quiet database, the same 16 MB segment may remain open for hours or even days.

`pg_switch_wal()` is the function that forces PostgreSQL to close the current WAL segment and move to the next one.
It sounds simple. In recovery work, backup validation, and archive troubleshooting, it is extremely useful.

What `pg_switch_wal()` Actually Does

PostgreSQL writes every change to the Write-Ahead Log before the actual data file change is considered safe. WAL is split into segment files, normally 16 MB each, stored under `pg_wal/`.

A typical WAL file looks like this:
`000000010000000000000019`

That filename contains: Timeline ID Log ID & Segment number
Normally, PostgreSQL closes a WAL segment only when it becomes full.

When you execute:

SELECT pg_switch_wal();

PostgreSQL forces the active WAL segment to finish early and starts writing into a new segment.
Operationally, this means:
`Current WAL segment is closed`
Remaining space is padded
Next WAL segment becomes active
Closed segment becomes ready for archiving
Function returns the LSN where the switch happened

The return value may look like this: 0/2B7A9C0

That is the LSN where the WAL switch happened.

0 = upper 32 bits of the byte position
2B7A9C0 = lower 32 bits in hexadecimal

Converting: 0x2B7A9C0 = 45,590,976 bytes, approximately 43.5 MB into the WAL stream.

With a 16 MB WAL segment size, this position falls inside the second WAL segment of that timeline range. To identify the WAL file name:

SELECT pg_walfile_name('0/2B7A9C0');

That tells you which WAL segment contains that LSN.

Small detail, but useful during recovery: the returned LSN is the switch location. The file containing that LSN is the segment that was active at the time of the switch and should now become available for archiving.

Why DBAs Use It Before Recovery Testing

The most practical use of `pg_switch_wal()` is not academic. It is recovery safety.

Imagine this situation:
`10:00 - Base backup completed10:15 - Application made important changes10:20 - You want PITR to 10:15`

But the database is quiet. The WAL segment containing the 10:15 changes is still open. It has not reached 16 MB. Since it is not complete, the archiver has not copied it yet.

Now your archive location looks fine, but recovery does not have the WAL required to reach 10:15.

Before running PITR tests or relying on a precise recovery target, force a WAL switch:
`SELECT pg_switch_wal();`

Then check whether the segment arrived in the archive destination:

SELECT
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;


If last_archived_wal does not move, do not trust your recovery setup yet.

A lot of backup checks only validate that a base backup exists. That is not enough. PostgreSQL recovery needs both the base backup and the required WAL chain.

Missing one WAL file can make an otherwise good backup useless.

Using It to Validate Archive Command

`pg_switch_wal()` is also one of the fastest ways to test whether archiving is actually working.

First check archive settings:
`SHOW archive_mode;` `SHOW archive_command;`

Then force a WAL switch:
`SELECT pg_switch_wal();`

Now check archiver status:

SELECT
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;

On the OS side, check the archive destination:

ls -ltr /app/postgres/arch/

If the archive command is correct, a new WAL file should appear shortly.

If not, check PostgreSQL logs. Most archive failures are boring but painful:
`Permission denied`
`Destination full`
`Wrong path`
`NFS stale mount`
`archive_command returns non-zero exit code`
`File already exists`
A bad archive command can sit unnoticed for a long time if nobody monitors `failed_count`.

This is a common DBA blind spot. People monitor database availability, replication lag, and CPU. But archiver failures are often discovered only during restore.

That is too late.

The No-Op Behavior That Confuses People

There is one nuance DBAs should remember. `pg_switch_wal()` may do nothing if no WAL has been generated since the last switch.

So if you run this twice:
`SELECT pg_switch_wal();` `SELECT pg_switch_wal();`

the second call may return the same or nearly same LSN. That does not mean PostgreSQL is broken. It means there was nothing new to switch. To force WAL activity, generate some WAL first.

For example:

CREATE TABLE wal_test_tmp(id int);
DROP TABLE wal_test_tmp;

SELECT pg_switch_wal();


Or, depending on the test:

CHECKPOINT; SELECT pg_switch_wal();

Be careful with unnecessary forced switches on busy systems. Every switch creates a new WAL segment for archiving. If someone puts this into a frequent cron job without thinking, archive volume can increase, storage can fill faster, and backup systems may process more WAL files than needed.

Useful command. Bad habit if abused.

Production Failure Scenarios

1. PITR Fails Even Though Backup Exists

Symptoms:


Recovery starts
WAL restore works for some files
Recovery stops before target time
Required checkpoint record not found

Typical cause: The WAL segment containing the target time was never archived.

Fix: SELECT pg_switch_wal();

Then verify:

SELECT last_archived_wal, last_archived_time
FROM pg_stat_archiver;


Operational lesson: never validate PostgreSQL recovery using only the presence of base backups.

2. Archive Destination Fills Up

Symptoms:


WAL files accumulating in pg_wal
archive_command failing
Database disk usage increasing

Check: SELECT failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;

OS check:

df -h

du -sh /home/postgres/arch/

If archiving is broken long enough, pg_wal can grow and eventually threaten database availability.

3. Standby or Backup Lag Caused by WAL Handling

In streaming replication or backup-heavy systems, WAL is the lifeline. If archiving is delayed, replicas or restore jobs may fall behind or fail to fetch required segments.

SELECT
    pg_current_wal_lsn(),
    pg_walfile_name(pg_current_wal_lsn());

For replication:

SELECT
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state
FROM pg_stat_replication;

pg_switch_wal() will not fix a bad replica by itself. But it helps validate whether WAL generation and archiving are behaving as expected.

DBA Insights

Do not treat pg_switch_wal() as a magic recovery command. It does one thing: it closes the current WAL segment and makes it eligible for archiving.

The real DBA work is around validation.

Check whether archiving is enabled:

SHOW archive_mode;

Check whether the command is sane:

SHOW archive_command;

Check whether archiving is progressing:

SELECT archived_count, last_archived_wal, last_archived_time FROM pg_stat_archiver;

Check whether failures are increasing:

SELECT failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;

And always check from the OS side:

ls -ltr /home/postgres/arch/

df -h

Common mistakes I have seen:


Assuming base backup success means PITR is safe
Not monitoring pg_stat_archiver
Forgetting quiet databases may not archive WAL quickly
Running pg_switch_wal too frequently without storage planning
Not testing restore until an actual incident
Ignoring archive_command failures because the database is still online

The most dangerous backup system is the one that has never been restored.

FAQs

Does `pg_switch_wal()` force WAL archiving?

Not directly. It closes the current WAL segment. Once the segment is complete, the archiver can pick it up if `archive_mode` is enabled and `archive_command` is working.

Why did `pg_switch_wal()` return the same LSN twice?

Most likely no WAL was generated between the two calls. Generate a small change, then run it again.

Is it safe to run in production?

Yes, but do not abuse it. Occasional use for backup, PITR validation, or archive testing is normal. Frequent forced switches can increase archive file count and storage pressure.

How do I know the switched WAL was archived?

SELECT last_archived_wal, last_archived_time FROM pg_stat_archiver;
Then confirm at the archive destination: ls -ltr /home/postgres/arch/

Can this fix broken PITR?

It can help if the required WAL is still in the active segment and has not yet been archived. It will not help if the WAL was already lost, deleted, or never generated.

Conclusion

`pg_switch_wal()` is a small PostgreSQL function, but it sits right in the middle of backup confidence, WAL archiving, and PITR reliability.

It is useful before restore testing, after important changes, and while validating archive behavior. But the command itself is only part of the story. The real discipline is checking whether the WAL segment reached the archive location and whether recovery can actually consume it.

A good DBA does not stop at “backup completed.”
A good DBA asks:
`Can I restore it?`
`Can I recover to the required time?`
`Do I have every WAL file needed?`
`Did I test this before the outage?`
That mindset saves production systems.

The Outage Usually Begins Before the Database Fails

noreply@blogger.com (Nikhil Kotak) — Sat, 18 Jul 2026 12:45:23 +0000

A database incident rarely begins when the monitoring system raises an alert. It usually begins weeks earlier - with an unreviewed execution plan, an ignored capacity trend, an oversized privilege, or a recovery procedure that nobody has tested.

By the time the DBA receives the call, the database is often exposing an operational weakness that was already present. The immediate symptom may be exhausted storage, a failed deployment, excessive I/O, or a missing object, but the underlying cause is frequently a gap in engineering discipline.

I have seen expensive Oracle platforms fail because nobody noticed that the Fast Recovery Area was growing rapidly. I have also seen relatively modest PostgreSQL environments remain stable for years because the team consistently reviewed changes, monitored capacity, controlled access, and tested recovery.

The database technology was different, but the real difference was how the platform was operated.

The Incident Often Starts During Deployment

A query completing successfully in development does not mean it is ready for production. Development may contain a few thousand rows, while production contains hundreds of millions. Data distribution, bind values, stale statistics, concurrent workload, and storage latency can all influence the execution plan.

For any significant SQL change, the review should answer practical questions:

Which access path did the optimizer select?
How close were estimated rows to actual rows?
How much logical and physical I/O was required?
Did sorts or hash operations spill to temporary storage?
Is the plan sensitive to particular bind values?
Will the same plan remain acceptable as the table grows?

The following examples capture runtime execution statistics in Oracle and PostgreSQL. Table and column names should be adjusted for the application being reviewed.

-- Oracle: execute the statement and display actual row-source statistics

SELECT /*+ gather_plan_statistics */
order_id,
status,
created_at FROM orders WHERE customer_id = :customer_id AND created_at >= :from_date;

SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
NULL,
NULL,
'ALLSTATS LAST +PEEKED_BINDS'
)
);

-- PostgreSQL: execute the query and report timing, I/O, and settings

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT order_id,
status,
created_at FROM orders WHERE customer_id = 100245 AND created_at >= DATE '2026-07-01';

EXPLAIN ANALYZE executes the statement; it does not merely estimate the plan. It should therefore be used carefully with slow queries and data-changing statements. Testing an UPDATE inside a transaction and issuing ROLLBACK does not protect against every possible side effect, such as external calls or non-transactional sequence changes.

An execution-plan review should not automatically result in another index. Every additional index consumes storage, increases WAL or redo generation, and adds work to inserts, updates, and deletes. Sometimes the correct fix is refreshed statistics, a better predicate, improved partition pruning, or reducing the amount of data requested by the application.

Capacity Alerts Must Predict Failure

Storage exhaustion remains one of the most preventable causes of database downtime. The usual problem is not the absence of monitoring, but monitoring that only reports the current percentage used.

An Oracle Fast Recovery Area at 85% may remain stable for several weeks or fill within an hour during a large batch operation. PostgreSQL pg_wal may grow unexpectedly because archiving is failing, a replication slot is retaining WAL, or a standby is no longer consuming changes.

A useful alert should consider current utilization, growth rate, time to full, and whether space can actually be reclaimed.

-- Oracle: Fast Recovery Area utilization

SELECT name,
ROUND(space_limit / 1024 / 1024 / 1024, 2) AS limit_gb,
ROUND(space_used / 1024 / 1024 / 1024, 2) AS used_gb,
ROUND(space_reclaimable / 1024 / 1024 / 1024, 2) AS reclaimable_gb,
ROUND(space_used * 100 / NULLIF(space_limit, 0), 2) AS used_pct FROM v$recovery_file_dest;

-- Oracle: archive destinations reporting an error or unusual status

SELECT dest_id,
status,
destination,
error FROM v$archive_dest_status WHERE status <> 'INACTIVE' ORDER BY dest_id;

-- PostgreSQL: WAL archiving statistics

SELECT archived_count,
failed_count,
last_archived_wal,
last_archived_time,
last_failed_wal,
last_failed_time FROM pg_stat_archiver;

-- PostgreSQL primary: WAL retained by replication slots

SELECT slot_name,
slot_type,
active,
restart_lsn,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS retained_wal FROM pg_replication_slots WHERE restart_lsn IS NOT NULL ORDER BY pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
) DESC;

These checks are only the starting point. A production control must also define warning and critical thresholds, escalation ownership, and the expected response. An alert that reaches a mailbox nobody actively monitors is not a control.

Production Access Should Limit the Damage

When someone accidentally drops a production table, attention naturally turns to the person who executed the command. The more useful question is why one incorrect command was allowed to become a production outage.

Human production access should normally be:

Assigned through named accounts rather than shared credentials
Restricted to the permissions required for the operational role
Time-bound when elevated access is requested
Audited and associated with an approved activity
Separated from emergency break-glass access
Reviewed and removed when no longer required

DBAs should not be exempt from these controls. Permanent unrestricted access may be convenient, but it increases the impact of a compromised credential, an incorrect connection, or a command executed in the wrong terminal.

Simple safeguards also help. Different connection aliases, shell prompts, database banners, credential locations, and terminal colors can make the production environment visually distinct. They will not replace access control, but they can interrupt the path between a human mistake and a serious incident.

A Rollback Script Is Not a Recovery Plan

Many change records contain a rollback section that says, “Restore the previous version.” That statement does not explain how to recover a partially completed database change.

Before approving a high-risk migration, I expect the implementation team to answer:

What condition will stop the implementation?
Which symptoms require rollback rather than roll-forward?
How will partially completed changes be identified?
What happens to transactions created after deployment begins?
How long will recovery take?
Who has authority to make the recovery decision?

Oracle DDL normally performs implicit commits, so issuing ROLLBACK will not reverse a completed ALTER TABLE or DROP TABLE. PostgreSQL supports transactional behavior for much of its DDL, but a complete deployment may still include non-transactional operations, external systems, application releases, or data transformations that cannot be reversed cleanly.

In some cases, rolling forward is safer than attempting to restore the old state. A renamed column can usually be changed back. Data discarded by a transformation may require a backup, export, shadow table, or purpose-built reconstruction process.

For a major migration, the recovery procedure should be rehearsed with representative data volume. The exercise should measure lock duration, implementation time, recovery time, and the point after which reversal is no longer safe.

Backup Success Does Not Prove Recoverability

A green backup job confirms that a process completed. It does not prove that the database can be restored within the expected recovery time or to the required recovery point.

Oracle RMAN validation and PostgreSQL backup-manifest verification are useful controls:

# Oracle RMAN

RMAN> CROSSCHECK BACKUP;
RMAN> LIST EXPIRED BACKUP;
RMAN> RESTORE DATABASE VALIDATE;

# PostgreSQL base backup with a backup manifest

pg_verifybackup /backup/postgresql/basebackup

RESTORE DATABASE VALIDATE checks whether RMAN can locate and read the backup pieces needed for restoration. It does not replace restoring the database to another host, applying the required archived redo, and opening the recovered database.

Similarly, pg_verifybackup verifies a PostgreSQL base backup against its manifest. A complete exercise must restore the files into an isolated environment, provide the required WAL, start the instance, and validate representative application data.

A useful restore drill records:

The recovery point that was actually achieved
Total restore and recovery time
Missing backup files, WAL, or archived redo
Required credentials, wallets, and encryption keys
Undocumented manual steps discovered during recovery
Application-level validation results

Backups should be monitored every day. Recoverability should be demonstrated periodically.

A Runbook Must Survive a 2 A.M. Incident

A lengthy document stored in a knowledge portal is not automatically a useful runbook. During an incident, the on-call engineer needs a concise and tested procedure.

A practical database runbook should contain:

The condition that triggers the procedure
Required access and prerequisites
Exact commands or actions
Expected output and validation checks
Stop or abort conditions
Recovery or reversal steps
Escalation contacts and ownership
Evidence that must be retained
The date the procedure was last tested

Runbooks also expire. Hostnames change, credentials move, tools are upgraded, and support responsibilities shift between teams. Every incident, change rehearsal, and restore drill should feed corrections back into the procedure.

The most valuable runbook is not the one with the best formatting. It is the one another engineer can follow safely without depending on undocumented knowledge held by a single person.

Discipline Should Not Become Bureaucracy

Operational controls have a cost. Applying the same approval process to a read-only diagnostic query and a destructive production migration wastes time and encourages people to find shortcuts.

The better approach is risk-based control. Low-risk and repeatable activities should be automated or pre-approved where appropriate. High-risk changes require deeper technical review, representative testing, clear abort criteria, and a rehearsed recovery path.

Emergency access should be fast enough to use during a genuine incident, while remaining controlled, temporary, and auditable. A good process reduces uncertainty and helps engineers work safely. It should not create paperwork that adds no protection.

Common Questions

Can monitoring prevent every database outage?

No. Monitoring cannot prevent hardware faults, software defects, or every unexpected workload pattern. It can, however, detect many developing conditions early enough to avoid downtime. Capacity growth, archive failures, replication lag, backup failures, and abnormal query behavior are good examples.

Is adding an index always the safest performance fix?

No. An index may improve one query while increasing storage usage and slowing data modifications. Check cardinality estimates, statistics, predicate design, partition pruning, and the volume of data returned before creating another index.

How frequently should restore testing be performed?

The schedule should reflect business criticality, recovery objectives, and the rate of platform change. Quarterly testing is a reasonable starting point for many important systems, but highly critical databases may require more frequent automated restore validation.

Should every database change have a rollback script?

Every significant change needs a recovery strategy, but that strategy may be rollback, roll-forward, restoration, failover, or a combination of approaches. Forcing every change into a simple rollback template can create false confidence.

Final Thoughts

When a database becomes unavailable, the immediate investigation naturally focuses on the technical symptom: exhausted storage, lock contention, a failed migration, or a deleted object.

The more valuable question is what allowed that condition to reach production without being detected, prevented, or contained.

Reliable database platforms are built through repeated habits: reviewing execution plans, monitoring capacity trends, controlling production access, rehearsing changes, restoring backups, and maintaining procedures that engineers actually use.

Oracle, PostgreSQL, and other database engines provide excellent monitoring, recovery, and diagnostic capabilities. They cannot decide whether a team will apply those capabilities consistently.

The healthiest production environments are usually quiet, predictable, and deliberately boring. That is rarely an accident. It is the result of disciplined engineering.

RESETLOGS and Backups: Debunking a Common Oracle Myth

noreply@blogger.com (Nikhil Kotak) — Mon, 13 Jul 2026 06:33:00 +0000

If you have ever recovered an Oracle database and opened it with RESETLOGS, chances are someone whispered or shouted—the dreaded question: “Are all my old backups now useless?” Lets set the record straight: in modern Oracle releases, your old backups are still very much usable, and panicking is optional.

In this post, I wil walk you through why this myth exists, how Oracle’s handling of RESETLOGS has evolved over the years, and what you should do to ensure your recovery strategy remains solid. We will cover the mechanics of RESETLOGS, explain the differences between pre-10g version and modern behaviour, and share some practical tips I have learned from managing production environments with terabytes of data. By the end, you you will understand why you can sleep a little easier after recovery and make smarter backup decisions.

How RESETLOGS Behaviour Evolved Over Time

Before Oracle 10g: The Incarnation Headache

Back in the days of Oracle 9i and earlier, opening a database with RESETLOGS created a new incarnation. This was a double-edged sword:

Backups and archived redo logs from the previous incarnation could not be reused easily unless you tracked incarnations in an RMAN catalog.
The standard practice was to take a full backup immediately after RESETLOGS to avoid complications in future recoveries.

Think of it as resetting a counter on your database without telling your backups—they became “confused” about which incarnation they belonged to.

Oracle 10g and Later: Simplified Recovery Through RESETLOGS (SRR)

Oracle 10g introduced a game-changer called Simplified Recovery Through RESETLOGS (SRR). RMAN now automatically understands database incarnations. This means:

Pre-RESETLOGS backups, including level 0 and dependent incrementals, remain valid.
Existing restore scripts generally continue to work without modification.
Your backup strategy doesn’t require that immediate panic-full-backup post-RESETLOGS.

From my experience, this feature alone has saved hours during disaster recovery drills in production environments with multiple terabytes of data.

What RESETLOGS Actually Does

Understanding the mechanics helps clear up misconceptions:

Starts a new database incarnation and resets redo log sequence to 1.
Does not invalidate prior backups. They are still valid for recovery, depending on the incarnation.
Archived logs from the parent incarnation are usable only up to the RESETLOGS branch point.
SCN (System Change Number) continues forward; it is not reset.

A practical example: in a recent client recovery, we performed a point-in-time recovery and opened the database with RESETLOGS. Using RMAN, we were able to restore from pre-RESETLOGS backups without a hitch. The key takeaway? Don’t assume RESETLOGS is a “backup killer.”

Best Practices for Backup Verification

Even though RESETLOGS doesn’t invalidate backups, always confirm backup usability post-recovery:

Use LIST BACKUP in RMAN to see what backups are available.
Use VALIDATE to check if backups are actually usable.
Maintain a clear recovery plan documenting which backups correspond to which incarnations.

This approach avoids unnecessary panic backups, keeps your storage clean, and ensures you can recover efficiently when it really counts.

Quick Takeaways

RESETLOGS starts a new incarnation but does not invalidate old backups.
Pre-10g databases required immediate full backups post-RESETLOGS; modern releases do not.
RMAN understands database incarnations from 10g onward, simplifying recovery.
Archived logs from the parent incarnation are usable only up to the RESETLOGS point.
Always validate backups post-recovery using RMAN.
Proper understanding reduces unnecessary panic backups.
Knowledge of incarnations can save hours in large production recoveries.

FAQs About RESETLOGS and Backups

1. Are old backups completely useless after RESETLOGS?
No. RMAN in Oracle 10g and later understands incarnations, so pre-RESETLOGS backups remain valid.

2. Do I need to take a full backup immediately after RESETLOGS?
Not mandatory in 10g or later. However, it’s still a good practice for clarity and future recoveries.

3. What happens to archived logs from the previous incarnation?
They are usable only up to the branch point where RESETLOGS was performed.

4. Does RESETLOGS reset SCNs?
No. SCNs continue forward; only redo log sequences are reset.

5. How can I verify backup usability after RESETLOGS?
Use RMAN commands LIST BACKUP and VALIDATE to confirm backups are intact and usable.

Conclusion

Understanding RESETLOGS is essential for any DBA managing Oracle environments. Misconceptions about invalidated backups can lead to unnecessary panic, extra work, and even wasted storage. Modern Oracle releases, starting from 10g, have simplified recovery through SRR, allowing pre-RESETLOGS backups to remain usable and restore scripts to function without modification.

The key takeaway: always validate your backups, understand database incarnations, and maintain a clear recovery strategy. With these practices, you can confidently perform recoveries, minimize downtime, and ensure your disaster recovery plan is robust. As a DBA, having this clarity not only saves time but also builds trust with stakeholders who rely on the database for mission-critical operations.

And remember, after RESETLOGS, your backups are like your favourite coffee mug - they don’t suddenly disappear, even if the counter is reset.

Found this useful? Share it with your DBA peers and leave a comment about your own RESETLOGS experiences. Did this myth trip you up in production before? Let’s discuss and learn together!

DB Time vs CPU: The Metric Most DBAs Ignore

noreply@blogger.com (Nikhil Kotak) — Sat, 04 Jul 2026 13:18:00 +0000

If you have worked in production environments long enough, you have probably seen this situation before. An alert fires in the middle of your daily shift appears.. as "CPU is at 90%. The database must be overloaded."

Infrastructure teams immediately start discussing scaling CPU, adding cores, or moving the database to a bigger server. But experienced DBAs know something important: High CPU utilization does not automatically mean the database is the bottleneck.

In many real-world incidents, CPU is only a symptom of an underlying issue - inefficient SQL, excessive I/O, application loops, or internal contention inside the database.

This is where DB Time becomes one of the most powerful metrics for diagnosing performance problems. While CPU tells you what the operating system is doing, DB Time reveals where database sessions are actually spending time - on CPU, waiting for I/O, blocked by locks, or stuck waiting on cluster communication.

In this article we will explore how experienced Oracle DBAs interpret CPU vs DB Time, how to diagnose performance incidents properly, and why understanding this relationship can prevent unnecessary infrastructure upgrades.

Understanding DB Time in Oracle

Before analyzing CPU usage, it is important to understand what DB Time really represents.

DB Time is the total time spent by all active database sessions, including:

CPU processing
Waiting for disk I/O
Lock contention
Network waits
RAC interconnect communication
Internal database operations

In simple terms: DB Time = Time spent doing work + Time spent waiting

This metric sits at the center of Oracle performance diagnostics and is heavily used in tools such as:

AWR Reports
ASH (Active Session History)
Oracle Enterprise Manager

If DB Time is high, database sessions are either consuming CPU or waiting on a bottleneck.

If DB Time is low, the database is usually not the primary issue.

Example: Checking DB Time

SELECT *
FROM dba_hist_sys_time_model
WHERE stat_name = 'DB time';

This view helps you understand how much time the database has spent processing workload during a given interval.

How Experienced DBAs Interpret CPU vs DB Time

One of the most effective troubleshooting techniques is correlating CPU usage with DB Time. Looking at either metric alone can easily lead to incorrect conclusions. Let us look at a few patterns commonly seen in production environments.

Scenario 1: High CPU + High DB Time

This situation usually indicates a database workload problem.

When both metrics increase together, the database is actively processing a heavy workload.

Common causes include:

Inefficient SQL queries
Missing indexes
Excessive parsing
Poor execution plans
Statistics issues
Cardinality estimation errors

In many production systems, a single inefficient query executed thousands of times can push CPU usage extremely high.

Typical Investigation Steps

Identify Top SQL by DB Time
Review execution plans
Validate indexing strategy
Check statistics freshness

Example query:


SELECT sql_id, elapsed_time, cpu_time
FROM v$sql
ORDER BY elapsed_time DESC;

Fixing a bad execution plan can often reduce CPU usage dramatically.

Scenario 2: High CPU + Low DB Time

This is where many teams misdiagnose the problem.

If CPU usage is high but DB Time remains relatively low, the database may not be the main consumer of CPU.

Possible causes include:

Application loops repeatedly calling the database
Connection storms
Middleware misconfiguration
JVM issues
Other processes on the server consuming CPU

In these situations, the database becomes a victim of external workload patterns rather than the root cause.

That is why DBAs must always correlate database metrics with operating system level monitoring

Scenario 3: Low CPU + High DB Time

This pattern usually indicates that sessions are waiting rather than actively executing work.

Typical causes include:

I/O Bottlenecks

Common wait events include: db file sequential read & db file scattered read

These usually indicate heavy disk access.

Lock Contention

Examples include: enq: TX - row lock contention & library cache lock

Sessions are blocked waiting for other transactions to complete.

RAC Cluster Waits

In RAC environments, interconnect communication may become the bottleneck.

Examples include: gc cr request & gc buffer busy

In such cases, analyzing wait classes through AWR or ASH becomes essential.

A Real Production Incident:

Let me share a situation that will sound familiar to many DBAs.

Around 2 AM, an alert triggered showing sustained 85 percent CPU usage on a production database server. The immediate response from the infrastructure team was simple: - "We need more CPU."

Before approving any hardware upgrade, we opened the AWR report.

The top wait event it was: db file sequential read

That immediately suggested inefficient access patterns. After digging further, we discovered the root cause. A frequently executed query was missing an index on a lookup table.

Because of this:

Full table scans occurred repeatedly
Logical I/O increased dramatically
CPU consumption rose sharply

After creating the required index:

CPU dropped from 85 percent to about 30 percent
DB Time normalized
Query performance improved significantly

No hardware upgrade was required. The real problem was SQL inefficiency, not CPU capacity.

Production Best Practices for Performance Incidents

In high availability environments such as RAC or Data Guard, performance investigation should follow a structured approach.

Experienced DBAs usually analyze the following.

1. DB Time Trends

Use AWR history to determine when workload changes started.

2. Wait Class Distribution

Identify whether the system is limited by:

CPU
I/O
Concurrency
Cluster communication

3. Top SQL by DB Time

These queries usually contribute to the majority of system load.

4. Load Profile

Important indicators include:

Executions per second
Logical reads per second
Parse calls

5. Execution Plan Changes

Sudden plan changes can cause unexpected performance degradation. Check for:

Statistics drift
Bind variable peeking
Adaptive plan behavior
Cursor invalidation

Quick Takeaways

High CPU does not automatically mean the database is the bottleneck
DB Time is one of the most reliable indicators of database workload
Wait events often reveal the real root cause
Poor execution plans can dramatically increase CPU usage
Hardware scaling should be the last step, not the first
Top SQL by DB Time usually reveals the main performance issue
Always correlate database metrics with operating system metrics

DBA's Production Perspective

Real production systems introduce additional complexity. One common mistake teams make is relying too heavily on CPU graphs from infrastructure monitoring tools.

Those tools rarely provide visibility into database internals. Another challenge is execution plan instability.

A query that ran efficiently for months can suddenly degrade due to:

Statistics refresh
Data skew
Adaptive plan behavior
Cursor invalidation

In RAC environments the situation becomes even more complex. CPU spikes can sometimes be caused by global cache contention, which is not obvious unless you examine cluster wait events.

Avaialble tools every DBA should rely on include:

AWR reports
ASH analysis
V$ACTIVE_SESSION_HISTORY
SQL Monitor

Another operational lesson: the loudest alert is not always the real problem. CPU alerts are often downstream symptoms of deeper database issues.

Mini Case Study: Avoiding an Expensive Hardware Upgrade

A financial trading platform experienced severe slowdowns during peak hours. The initial assumption was that the system needed more CPU.

After deeper analysis we discovered:

A reporting query executed every second
The query scanned a large transactional table
The filtering columns had no supporting index

After adding the required index and optimizing the query:

CPU usage dropped by almost 40 percent
Query execution time improved dramatically
System stability returned

Instead of purchasing new hardware, the solution required simple SQL tuning.

Conclusion

Performance troubleshooting in Oracle databases requires looking beyond surface level metrics. CPU utilization might trigger alerts, but it rarely tells the full story.

DB Time is one of the most valuable indicators of database behaviour, because it reveals how sessions actually spend time inside the database engine.

By correlating DB Time with wait events, load profile metrics, and Top SQL statements, DBAs can quickly identify the real root cause of performance issues.

This approach not only improves incident response but also prevents unnecessary infrastructure spending. Before scaling hardware, always analyze the database workload itself. In many cases the real issue lies in execution plans, indexing strategy, or inefficient SQL statements.

The next time a an alert reports high CPU usage, resist the urge to jump to conclusions. Start where experienced DBAs always start.

FAQs

What is DB Time in Oracle?

DB Time represents the total time database sessions spend either executing on CPU or waiting for resources such as I/O, locks, or network operations.

How is DB Time different from CPU usage?

CPU usage reflects operating system activity, while DB Time reflects database session activity including CPU work and wait events.

Where can I find DB Time metrics?

DB Time can be found in:

AWR Reports
ASH Views
V$SYS_TIME_MODEL
DBA_HIST_SYS_TIME_MODEL

Can high CPU occur without high DB Time?

Yes. High CPU may be caused by external processes, application loops, or middleware issues unrelated to database workload.

Why is Top SQL by DB Time important?

It helps identify SQL statements that consume the most database processing time, which often reveals the root cause of performance issues.

Have you ever seen a situation where high CPU led to the wrong diagnosis?

Share your experience. Production stories are always valuable learning for the DBA community.

How Oracle RAC Handles a Node Failure: Quick Insights About Interview Discussion

noreply@blogger.com (Nikhil Kotak) — Tue, 30 Jun 2026 07:34:00 +0000

In high‑availability database environments, About Interview discussion often centers on how systems react when things go wrong ., especially in mission‑critical deployments like Oracle RAC (Real Application Clusters). One of the most common interview questions DBAs face is: In a 3‑node RAC, if one node goes down, how does instance recovery occur?

Understanding this not only helps you ace interviews but also equips you with real‑world insights into RAC's fault‑tolerance mechanics.

In this article, we will break down the step‑by‑step recovery process when a node fails in a 3‑node Oracle RAC, discuss the components involved, show how workload redistribution happens smoothly, and highlight what every DBA should know — from beginners to seasoned architects. You’ll also get practical tips, real examples, and a deeper look into processes like SMON and PMON that make cluster recovery automatic and robust.

How Node Failure Is Detected in Oracle RAC

Every RAC environment relies on Oracle Clusterware to monitor the health of its nodes. Through interconnect heartbeat signals, Clusterware continuously checks whether each node is alive and responsive.

What Happens When a Node Fails

Heartbeat loss i.e, Clusterware detects missing signals from Node A.
Immediate action i.e., Surviving nodes begin coordinated recovery steps.

Interview Tip: You might be asked, "What is the role of the heartbeat?" - Explain that it is an internal health indicator used for failure detection.

Global Cache and Enqueue Services

Oracle uses the Global Cache Service (GCS) and Global Enqueue Service (GES) to rebalance ownership of cache blocks and locks across the remaining nodes.

Application Continuity

Modern applications benefit from Transparent Application Failover (TAF) and Application Continuity, tools that ensure client sessions automatically reconnect to surviving instances without user intervention.

Example:
Imagine an OLTP application connected mainly to Node 2. Now Node 3 goes down during peak hours. Thanks to TAF and GCS, connections gently shift to Nodes 1 and 2 - with no visible slowdown for the end user.

Instance Recovery: SMON in Action

A critical part of RAC resilience is instance recovery - and this is handled by the surviving instances, specifically the SMON process.

What Does SMON Do?

When Node 3 fails:

Redo logs from shared storage are read.
Rolling forward applies committed transactions that hadn’t yet been written to datafiles.
Rolling back undoes uncommitted transactions to ensure data consistency.

Real‑world example: In one production cluster, a failed node had uncommitted updates that SMON rolled back while committed data was fully preserved - preventing corruption and ensuring consistency.

Cleaning up Resources: PMON process Takes Over

While SMON handles data recovery, PMON (Process Monitor) ensures session resources get cleaned up:

Removes session entries from failed node
Clears out held locks and Closes open cursors

This ensures that no leftover artifacts from the failed node block other operations.

Resource Rebalancing and Global Management

Once recovery completes:

Global Resource Manager (GRM) redistributes workload equitably.
Now, Remaining nodes may adjust cache ownership and background tasks.
The jobs that were running on the failed node get reassigned or restarted as needed.

When the Failed Node Rejoins

Node reintegration is handled automatically:

Clusterware checks that the node is in sync and Cache fusion ensures all blocks are consistent.
The node resumes participation without manual intervention.

This is a key strength of Oracle RAC. It is designed to heal itself.

Quick Takeaways

Heartbeat monitoring detects RAC node failure quickly.
GCS and GES redistribute cache and locks dynamically.
SMON performs instance recovery - rolling forward and backward with redo logs.
PMON cleans session resources and locks.
TAF and Application Continuity help maintain seamless client connections.
Failed nodes rejoin automatically once consistency is validated.
Oracle RAC’s automation makes it resilient — that’s often a focal point About Interview discussion.

Conclusion

Understanding how a 3‑node Oracle RAC handles node failure isn’t just a great interview talking point - it iss essential knowledge for any serious DBA or cloud architect working with high‑availability systems. From Clusterware's heartbeat detection to SMON's intelligent instance recovery and PMON's cleanup duties, each layer plays a role in ensuring that a failure doesn’t become an outage.

What makes RAC particularly powerful is its self‑healing nature: workload redistribution, automatic redo application, and seamless client failover are designed to minimize impact and protect data integrity. Whether you’re prepping for your next interview, designing resilient systems, or troubleshooting production clusters, grasping these recovery mechanics gives you a practical edge.

If you have experienced node failovers in production or have questions about specific scenarios, keep reading n consider joining the conversation with your insights and experiences.

FAQs

What triggers instance recovery in Oracle RAC?
A missing heartbeat from a node triggers Clusterware to initiate recovery on surviving nodes.
How do surviving nodes access redo logs?
In RAC, redo logs are on shared storage, enabling any surviving instance to read and apply them.
What is the key the difference between SMON and PMON during a failure?
SMON handles data recovery, while PMON cleans up sessions and resources from the failed node.
Can applications reconnect automatically after a node failure?
Yes — with TAF or Application Continuity configured, client sessions can reconnect transparently.
Does the failed node require manual steps to rejoin the cluster?
Typically no — Oracle Clusterware synchronizes and reintegrates it automatically if no inconsistencies remain.

Did this help your interview prep or real‑world RAC troubleshooting? Drop a comment below with your experience or questions!
& If you found this useful, please share it on LinkedIn, Twitter, or with your DBA communities and help others master Oracle RAC resilience!

pg_gather: PostgreSQL Snapshot Tool Every DBA Should Use

noreply@blogger.com (Nikhil Kotak) — Mon, 29 Jun 2026 09:07:00 +0000

Assume Its 2 AM. Alerts are firing. CPU is maxed out, connections are piling up, and the application team is already asking for an ETA.

You SSH into the server, open psql, and start your usual routine - check pg_stat_activity, look at locks, scan logs, maybe run a few custom queries you’ve built over the years. Fifteen minutes in, you still don’t have the full picture.

This is exactly where things break down in production - - not because PostgreSQL lacks visibility, but because the data is scattered.

That is where pg_gather changes the game.

Instead of manually stitching together information from multiple system views, logs, and configs, pg_gather captures everything in one go and presents it as a structured report. Its not just a tool its a repeatable diagnostic workflow.

In this article, I will walk through how pg_gather actually fits into real-world DBA operations, when it helps, when it doesnt, and how to use it effectively during incidents - not after they are over.

Why DBAs Struggle During Incidents

In PostgreSQL, all the data you need is already there:

pg_stat_activity shows sessions
pg_locks shows contention
pg_stat_bgwriter hints at checkpoint pressure
Logs tell the story of failures

But here is the problem — they don't tell the story together.

In production, I’ve seen this pattern repeatedly:

One DBA checks locks
Another checks CPU
Someone else scans logs
Nobody correlates it fast enough

By the time you connect the dots, the issue is gone.

What pg_gather Actually Solves

Think of pg_gather as a point-in-time forensic capture. Instead of running 20+ commands manually, you run one:


./pg_gather -d prod_db -o incident_$(date +%s).html

It provides all the necessary information such as,

Full configuration snapshot
Active sessions and wait events
Lock chains (who is blocking whom)
Query statistics (if enabled)
Vacuum and bloat signals
OS-level metrics

The key benefit is not just automation - its consistency.

Every DBA on your team collects the same dataset, every time.

What Gets Captured (And Why It Matters)

Session & Lock Visibility

When a system slows down, it’s usually not CPU - it’s contention. Use the below query.

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle';

pg_gather expands this into - Blocking trees, Wait chains & Duration analysis

Sometimes, in the production system - problem is often not the slow query - its the one holding the lock.

Configuration Snapshot

One underrated cause of performance issues is bad configuration.

pg_gather captures:

shared_buffers
work_mem
autovacuum settings
connection limits

Useful when Comparing configs across environments (dev vs prod mismatch is common).

Vacuum and Bloat Analysis

Autovacuum is one of those things you ignore… until it breaks.

pg_gather highlights:

Dead tuples
Vacuum lag
Table bloat indicators

Real issue I hv seen: A blocked autovacuum due to a long transaction - resulted in → table bloat → sudden performance drop.

Query-Level Insights (Critical)

Without pg_stat_statements, you are basically blind. One should enable it.

# CREATE EXTENSION pg_stat_statements;
# alter system set shared_preload_libraries = 'pg_stat_statements'



Once done, restart the PostgreSQL service and now pg_gather can show:

Top queries by total time
Frequency vs latency
Execution patterns

Note that : It requires restart, but worth it in every serious production system.

Running pg_gather Safely in Production

A common concern: Will this impact my database?

Short answer: Minimal, but not zero.

Best practice:


nice -n 10 ./pg_gather -d db_name > gather.log 2>&1

Why this matters:

Lower CPU priority
No interference with workload
Full audit trail

Reality check:
On very large systems, even read queries can create noticeable load. Avoid running during peak spikes unless necessary.

When to Run pg_gather (Timing Matters)

This is where most teams get it wrong.

Running it after the issue occurred is not useful. Because the data is gone.
If it has run during the issue, that the 'Goldmine'.

Because it Captures most important information such as : Active locks , Running queries, Wait events

It is advisable, to run it multiple times during a long incident to track progression.

PostgreSQL vs Oracle: Diagnostic Approach

As someone who has worked extensively on both - here is short terminology graph.

Installation and Execution

# Install git
sudo apt install git -y

# Clone repo
git clone https://github.com/jobinau/pg_gather.git
cd pg_gather

# Make executable
chmod +x pg_gather

# Run
./pg_gather -d postgres

Quick Takeaways

pg_gather is a snapshot tool, not monitoring
Run it during incidents, not after
Enables faster root cause analysis
Works best with pg_stat_statements enabled
Safe for production when used carefully
Standardizes troubleshooting across teams
Great for audits and baseline comparisons

DBA Takeaways

Most DBAs waste time collecting data, not analyzing it
Lock chains are often the hidden root cause
Autovacuum issues show up only when it’s too late
Teams without standard diagnostics struggle during incidents
Historical snapshots are extremely valuable for recurring issues

Recommendation:
Maintain a shared repository of pg_gather reports - it also becomes your internal knowledge base.

MINI CASE STUDY: Lock Contention Nightmare

Problem: Application slowdown, API latency jumped from 200ms to 6 seconds.

Symptoms : High CPU + Connection spikes + Queries stuck in wait state

pg_gather Findings:

One transaction holding lock for 18 minutes
Downstream queries waiting
Autovacuum blocked

Root Cause: Uncommitted transaction from batch job.

Fix: SELECT pg_terminate_backend(<pid>);

Outcome:

System recovered in minutes
Without pg_gather, took ~40 minutes to diagnose

Conclusion

pg_gather is one of those tools you don't realize you need until you are deep into a production issue. PostgreSQL gives you excellent visibility - but it expects you to assemble the puzzle yourself. That works when you have time. It does not work when systems are burning.

By standardizing how diagnostics are captured, pg_gather removes guesswork and speeds up decision-making. It doesn’t replace monitoring tools, and it doesn’t give you historical trends like enterprise systems — but for point-in-time analysis, it’s incredibly effective.

If you’re managing production PostgreSQL systems and still relying on manual queries during incidents, you’re adding unnecessary delay to your troubleshooting process.

Run it in your environment. Capture baseline reports. Use it during your next incident.

That’s when it proves its real value.

FAQs

1. Does pg_gather impact performance?
Minimal impact, but noticeable on very large systems. Use nice to reduce priority.

2. Can it replace monitoring tools?
No. It’s a snapshot tool, not real-time monitoring.

3. Do I need superuser access?
Recommended. Without it, some data will be missing.

4. Is pg_stat_statements mandatory?
No, but without it you lose query-level insights.

5. Can I run it on managed cloud databases?
Yes, but access restrictions may limit data collection.

Have you ever been stuck diagnosing a PostgreSQL issue where data was scattered everywhere? Do you rely on custom scripts, or something like pg_gather?

Curious to hear how others handle incident diagnostics in production.

Source : https://github.com/jobinau/pg_gather

https://www.percona.com/blog/what-is-in-pg_gather-version-33/

Oracle PDB Point-in-Time Recovery Without Downtime of other PDBs

noreply@blogger.com (Nikhil Kotak) — Sun, 28 Jun 2026 12:48:00 +0000

Most Oracle outages do not begin with hardware failure.

They start with a bad deployment, an accidental delete statement, a broken batch job, or a developer connecting to the wrong pluggable database at 2 AM. In a large multitenant environment, that usually means one application becomes corrupted while dozens of other applications inside the same CDB continue running normally.

Years ago, recovering from that kind of incident often meant painful decisions. Either accept application-level data loss or restore the entire database and impact every tenant sharing the environment. Neither option was ideal for production systems running critical workloads.

Oracle Multitenant changed that recovery model significantly.

With PDB Point-in-Time Recovery (PDB PITR), Oracle can restore and recover only the affected pluggable database while the remaining PDBs continue serving live traffic. Internally Oracle creates a temporary auxiliary environment, performs recovery there, and then plugs the recovered PDB back into the original container database.

On paper the process looks simple. In production, however, archive log gaps, wrong timestamps, FRA exhaustion, and auxiliary storage failures are where things usually become complicated.

This article walks through how single PDB recovery actually works in real environments, what Oracle does internally during recovery, and the operational lessons DBAs usually learn only after handling live incidents.

Why PDB-Level Recovery Matters in Production

The biggest advantage of Multitenant recovery is operational isolation.

In shared Oracle environments, it is common to host many applications inside the same container database. One PDB may support finance workloads, another may handle reporting, while several others serve APIs or internal applications. When corruption affects only one tenant, shutting down the entire CDB becomes unnecessary and operationally expensive.

PDB PITR allows DBAs to isolate the damaged tenant and rewind only that portion of the environment to an earlier timestamp.

This becomes extremely valuable during incidents involving:

accidental schema drops
failed deployments
corrupted data loads
bad ETL jobs
application bugs generating invalid updates
logical corruption inside a single tenant

The key point is that unrelated applications continue operating while recovery happens in parallel.

That is a massive operational improvement compared to traditional database-wide restore procedures.

What Oracle Actually Does During PDB Recovery

A lot of DBAs initially assume Oracle directly restores files into the damaged PDB. That is not how the process works internally.

Oracle first creates a temporary auxiliary instance using the location specified in the recovery command. This auxiliary environment acts as a staging area where Oracle restores the root container metadata and then recovers the target PDB to the requested timestamp.

After recovery completes successfully, Oracle automatically unplugs the recovered PDB from the auxiliary instance and plugs it back into the original production CDB.

The elegant part is that all of this happens while the remaining PDBs stay online.

That architecture is why Oracle requires the damaged PDB to be dropped before recovery begins. Oracle cannot plug recovered metadata into an already existing PDB with conflicting identifiers and file structures.

The recovery workflow usually looks like this:

SQL> ALTER PLUGGABLE DATABASE pdb2 CLOSE IMMEDIATE;

SQL> DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;


This step understandably makes many DBAs nervous the first time they perform it in production. But by the time you reach this stage, the backup validation should already be completed and recovery planning finalized.

Backup Validation Matters More Than Recovery Syntax

In real-world incidents, the RMAN command itself is rarely the difficult part.

The real problems usually involve discovering that required archive logs are missing, backup metadata is stale, or recovery windows do not extend far enough back to avoid corruption.

Before touching the target PDB, validate everything carefully:

RMAN> LIST BACKUP;

RMAN> LIST BACKUP OF PLUGGABLE DATABASE pdb2;

RMAN> LIST BACKUP OF ARCHIVELOG ALL;

If the controlfile repository lost visibility of backup pieces, recatalog them manually:

RMAN> CATALOG START WITH '/u01/backup/FULLBACKUP';



Then crosscheck all backup and archive log metadata:

RMAN> CROSSCHECK BACKUP;

RMAN> CROSSCHECK ARCHIVELOG ALL;

One production issue that appears surprisingly often is aggressive archive log cleanup policies. DBAs sometimes configure retention purely around storage pressure without considering actual recovery objectives.

The result is predictable: recovery starts successfully and then suddenly fails halfway because required logs no longer exist.

Choosing the Correct Recovery Timestamp

Timestamp selection is where experience matters.

Suppose a deployment corrupted application data at 9:42 PM. Recovering to 9:50 PM obviously does not help because corruption already exists in the redo stream. But recovering too far back may discard valid business transactions.

This is why production DBAs rarely rely only on application team estimates.

Useful validation methods include checking deployment timestamps, audit logs, scheduler execution history, and even flashback queries when available.

One lesson many teams learn the hard way is that application owners often provide inaccurate incident timing during stressful outages. Recovery decisions should always be independently verified before execution.

RMAN Command for Single PDB Recovery

Once the recovery timestamp is finalized, the actual RMAN recovery operation becomes relatively straightforward.

RUN {
SET UNTIL TIME "TO_DATE('17-MAY-2026 12:49:09',
'DD-MON-YYYY HH24:MI:SS')";

RECOVER PLUGGABLE DATABASE pdb2
AUXILIARY DESTINATION '/u01/aux';
}

The auxiliary destination deserves special attention.

Oracle uses this location to create temporary recovery files, controlfiles, online logs, and auxiliary instance structures during recovery. Underestimating this filesystem is one of the most common operational mistakes during PDB PITR.

In busy production environments, the auxiliary recovery workload can generate substantial I/O. If the filesystem becomes full midway through recovery, the entire operation may fail and force another restart.

DBAs should also ensure:

filesystem ownership is correct
Oracle has write permissions
sufficient free space exists
backup mount points are accessible from the auxiliary environment

RMAN Backup Strategy for Multitenant Systems

Many organizations still use backup designs inherited from older non-CDB environments. Those strategies do not always scale well for large multitenant systems.

A typical production backup configuration might look like this:

RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
ALLOCATE CHANNEL c4 DEVICE TYPE DISK;

BACKUP AS COMPRESSED BACKUPSET DATABASE
FORMAT '/u01/backup/FULLBACKUP/%d_%T_%s_%p_FULL';

BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL
FORMAT '/u01/backup/FULLBACKUP/%d_%T_%s_%p_ARCH';

BACKUP CURRENT CONTROLFILE
FORMAT '/u01/backup/FULLBACKUP/%d_%T_%s_%p_CTRL';

BACKUP SPFILE
FORMAT '/u01/backup/FULLBACKUP/%d_%T_%s_%p_SPFILE';

RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

But production tuning matters.

Allocating too many RMAN channels can overwhelm shared storage arrays. Compression reduces backup size but increases CPU consumption. In heavily transactional systems, archive log generation can become far larger than expected during peak business windows.

Some environments discover during recovery testing that archive logs consume more storage than full backups themselves.

Common Problems During PDB PITR

Most recovery failures fall into a few predictable categories.

Missing archive logs remain the biggest issue:

RMAN-06054 media recovery requesting unknown archived log

Usually this points to retention policy problems or archive cleanup jobs deleting logs too aggressively.

Another frequent issue is auxiliary destination exhaustion:

ORA-19809: limit exceeded for recovery files

This typically happens because recovery planning considered only backup size and ignored temporary recovery space requirements.

Permission problems are also common in environments using NFS mounts or newly provisioned recovery filesystems:

ORA-27040: file create error

And finally, there is the most frustrating scenario of all: recovery technically succeeds, but the recovered application still contains corruption because the wrong timestamp was selected.

That is not an Oracle problem. That is usually a planning problem.

Oracle vs PostgreSQL Recovery Perspective

Oracle Multitenant recovery is still significantly more granular than PostgreSQL cluster-level PITR.

In PostgreSQL, WAL recovery generally affects the entire cluster because recovery operates at instance level rather than isolated tenant level. If multiple applications share the same PostgreSQL cluster, restoring one database independently becomes operationally harder.

Oracle’s PDB recovery model provides better tenant isolation for large consolidated enterprise platforms.

That said, PostgreSQL environments often achieve isolation differently by deploying separate clusters per application. This increases infrastructure overhead but simplifies recovery boundaries.

Both approaches work. The operational trade-offs are simply different.

Lessons From the Field

The biggest mistake teams make with PDB recovery is assuming successful backups automatically guarantee successful restores.

They do not.

Many organizations never test actual PDB PITR until a real outage happens. That is usually when hidden problems surface:

archive logs missing from retention windows, inaccessible backup mounts, incorrect RMAN catalog synchronization, or insufficient auxiliary storage.

Another issue rarely discussed in documentation is storage impact during recovery. Large PDB restores can create heavy read/write bursts across shared SAN environments. In busy systems this can affect neighboring workloads unexpectedly.

One particularly dangerous assumption is believing recovery can fix any corruption regardless of age. If corrupted data already exists inside the backup baseline, PITR may simply restore the same problem again.

Recovery testing should therefore focus not only on backup integrity but also on realistic business recovery windows and application validation procedures.

Case Study

A production Oracle 19c environment hosted multiple customer-facing applications inside one CDB.

During a release deployment, an automation script connected to the wrong PDB and executed cleanup statements against reporting tables. The application became unusable within minutes.

Fortunately, other applications inside neighboring PDBs were unaffected and continued serving production traffic.

The DBA team isolated the damaged PDB, validated archive log coverage, and initiated RMAN PDB PITR using an auxiliary recovery location on separate storage. Recovery completed successfully, and the PDB was restored to a timestamp just before deployment execution.

Total outage remained limited to the affected reporting platform.

The postmortem later revealed the root cause was not Oracle itself but an incorrectly mapped environment variable inside the deployment pipeline.

Conclusion

PDB Point-in-Time Recovery is one of the most operationally valuable features Oracle introduced with Multitenant architecture.

The ability to recover one tenant without impacting unrelated applications changes how DBAs handle outages in consolidated enterprise environments. Instead of treating the entire database platform as a single recovery boundary, Oracle allows recovery isolation at the application level.

But successful recovery depends far more on operational discipline than on RMAN syntax.

Archive retention policies, backup validation, auxiliary storage planning, timestamp verification, and realistic recovery testing determine whether recovery succeeds smoothly or turns into a prolonged outage.

The actual recovery command often takes only a few minutes to write. The preparation behind it is what truly matters.

DBAs managing multitenant environments should regularly test PDB PITR workflows before production incidents force emergency recovery decisions. Validate archive coverage, monitor FRA growth trends, measure restore timings, and confirm auxiliary recovery locations can handle real workloads.

Because during a live outage, nobody wants to discover recovery gaps for the first time.

Quick Takeaways

PDB PITR allows recovery of a single tenant without shutting down the entire CDB.

Oracle internally creates an auxiliary instance to perform isolated recovery operations.

Archive log retention policies are often the biggest hidden risk during recovery planning.

Recovery timestamp selection is critical because recovering past the corruption point makes PITR useless.

Auxiliary storage sizing and filesystem permissions should always be validated beforehand.

Testing recovery procedures regularly is far more important than simply confirming backups completed successfully.

PostgreSQL vs Oracle: Choosing the Right Database for Your Architecture

noreply@blogger.com (Nikhil Kotak) — Tue, 23 Jun 2026 08:57:00 +0000

Selecting a database today isn’t just about technology - it's a strategic business decision. In production environments, the choice between PostgreSQL and Oracle Database affects scalability, reliability, compliance, and cost for years. As a DBA, architect, or infrastructure engineer, understanding the trade-offs between these systems can save significant headaches during implementation and future growth.

In this article, we will break down the real-world considerations for PostgreSQL and Oracle. You will see when one database might outperform the other, the practical operational challenges, and how different architectures shape your decision. Whether you are supporting a cloud-native startup or managing an enterprise-grade financial system, these insights can guide your choices.

PostgreSQL: Flexibility Meets Cost Efficiency

PostgreSQL has become the go-to choice for organizations that value flexibility, cost savings, and modern development patterns. In startups, SaaS platforms, and microservices-based applications, it shines. Its support for JSON, advanced indexing, and extensibility makes rapid development and schema evolution straightforward.

PostgreSQL reduces database licensing cost and can deliver strong ROI, especially for read-heavy, cloud-native, and horizontally distributed application patterns. However, production TCO still includes managed service cost, support, HA tooling, monitoring, backup storage, and operational ownership. For write-heavy horizontal scaling, PostgreSQL typically requires sharding, extensions, or application-level architecture.

Managed services like AWS RDS, Azure Database for PostgreSQL, or GCP Cloud SQL simplify deployment and monitoring, though enterprise-grade HA/DR design still requires careful planning. The community and ecosystem continue to grow, offering tools for backup automation, monitoring, and performance tuning.

Production Tip: If you are implementing PostgreSQL at scale, plan your replication, failover, and backup strategy carefully. While the platform is feature-rich, high-availability setups are not as plug-and-play as Oracle RAC or Data Guard.

Oracle Database: Enterprise Reliability and Compliance

Oracle Database remains the benchmark for mission-critical systems. Industries like banking, telecom, and ERP rely on Oracle for its proven performance under heavy OLTP workloads. Features like RAC for high availability, Data Guard for disaster recovery, and advanced partitioning allow enterprises to maintain operational stability at scale.

From a DBA standpoint, Oracle provides mature built-in security and auditing capabilities, along with enterprise monitoring and management integrations. However, DBAs should validate which features are included in their edition or cloud service and which require additional options or management packs. The vendor-backed support and SLAs reduce operational risk, which is critical in regulated environments. The trade-off is the licensing and operational cost, which can be significant, and the risk of vendor lock-in.

Production Tip: When managing Oracle environments, proactive monitoring of RAC nodes, Data Guard replication lag, and archive log pressure is essential to prevent performance bottlenecks.

Key Architectural Considerations

When designing database architectures, the decision often comes down to four factors: cost, flexibility, compliance, and operational stability.

Cost Efficiency: PostgreSQL dominates due to zero licensing and open-source tooling.
Compliance & Risk: Oracle offers built-in security and audit capabilities, ideal for highly regulated environments.
Flexibility: PostgreSQL supports rapid development and evolving schemas without significant overhead.
Operational Stability: Oracle simplifies operations with mature built-in HA/DR features.

Real-world architectures often combine both: PostgreSQL powering agile services, with Oracle handling core, regulated systems.

Image Idea: Side-by-side comparison infographic of PostgreSQL and Oracle across cost, flexibility, compliance, and stability metrics.

Keep in mind..

PostgreSQL may require extra operational effort for enterprise-grade HA/DR setups, including logical replication, failover automation, and monitoring tools.
Oracle’s extensive feature set can be underutilized if teams are not trained, increasing cost without commensurate benefit.
Hybrid architectures are increasingly common, where PostgreSQL handles lightweight, cloud-native services, and Oracle supports mission-critical back-office processing.
Monitoring and proactive alerting are critical in both environments. For PostgreSQL, track replication lag, index bloat, and autovacuum performance. For Oracle, watch RAC node health, archive log pressure, and undo tablespace growth.

Mini Case Study: Balancing Agility and Compliance

A fintech startup adopted PostgreSQL to quickly iterate on new features and integrate with cloud-native microservices. The team focused on automation for backups, failover, and monitoring, achieving rapid deployment cycles with minimal cost. Meanwhile, their banking client continued to use Oracle for transaction-heavy operations, ensuring compliance and predictable performance. By running a hybrid model, both agility and reliability were achieved without compromise.

Quick Takeaways

PostgreSQL excels for flexible, cost-sensitive, cloud-native applications.
Oracle shines in mission-critical, high-volume, and regulated environments.
High availability in PostgreSQL requires careful architecture; Oracle provides it out-of-the-box.
Real-world deployments often benefit from hybrid approaches.
Monitoring, proactive alerting, and operational planning are key in both databases.

Conclusion

Choosing between PostgreSQL and Oracle isn’t about finding the “better” database; it’s about understanding which one aligns with your business needs and risk tolerance. PostgreSQL offers cost-effective flexibility and rapid development, while Oracle provides proven stability, compliance, and support for high-volume workloads.

For DBAs and architects, the key is to evaluate your systems’ operational requirements, regulatory constraints, and growth plans. Test configurations, monitor performance closely, and optimize backups and HA/DR strategies. By making informed choices, you can build database architectures that balance cost, agility, and reliability over the long term.

FAQs

1. Can PostgreSQL handle enterprise-scale workloads like Oracle?
Yes, but it requires careful HA/DR setup, performance tuning, and monitoring, especially for high-volume OLTP workloads.

2. Is licensing cost the main reason organizations choose PostgreSQL?
It’s a major factor, but flexibility, JSON support, and modern stack integration also drive adoption.

3. What are common Oracle operational pitfalls?
Over-reliance on built-in features without tuning, poor RAC/Data Guard monitoring, and underestimating license cost.

4. Can PostgreSQL and Oracle coexist in the same architecture?
Absolutely. Many enterprises use PostgreSQL for agile services and Oracle for core regulated systems.

5. How do I decide between PostgreSQL and Oracle for a new project?
Evaluate your regulatory requirements, workload type, expected scale, operational support, and cost constraints.

What's your experience managing PostgreSQL or Oracle in production? Have you tried hybrid architectures? Share your insights or challenges in the comments - your real-world examples help the community.