Oracle Key Vault: DBA Guide for TDE Keys and scenarios

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.

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

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.

Oracle 26ai AWR Enhancements for DBAs

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

pg_switch_wal() in PostgreSQL: Small Command, Big Recovery Impact

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 completed
10:15 - Application made important changes
10: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.