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.
No comments:
Post a Comment