Oracle26ai Queries for PDB-Level Data Guard

After configuring Data Guard for p_deeds_pd1, use the following SQL*Plus queries to verify the PDB role, open mode, redo apply, and synchronization status.

1 Verify the Current Container and PDB

Run on both source and target CDBs:

			
SHOW CON_NAME;
SELECT name,
       open_mode,
       database_role
FROM   v$database;
SELECT con_id,
       name,
       open_mode,
       recovery_status
FROM   v$pdbs
WHERE  UPPER(name) = 'P_DEEDS_PD1';

		

2 Verify PDB-Level Data Guard Role Using DGMGRL

Use Data Guard Broker for the authoritative PDB-level role and apply-state view:

			
DGMGRL> SHOW PLUGGABLE DATABASE
         p_deeds_pd1 AT aicdb;
DGMGRL> SHOW PLUGGABLE DATABASE
         p_deeds_pd1 AT aicdrdb;

Run on the target CDB:

			
SET LINESIZE 200
SET PAGESIZE 100
COLUMN name FORMAT A20
COLUMN open_mode FORMAT A20
COLUMN recovery_status FORMAT A20
SELECT con_id,
       name,
       open_mode,
       recovery_status
FROM   v$pdbs
WHERE  UPPER(name) = 'P_DEEDS_PD1';

		

This confirms the target PDB’s current open and recovery status. Use the broker output to confirm the PDB’s Data Guard role.

3 Check Redo Apply Processes

Run on the target CDB:

			
COLUMN process FORMAT A12
COLUMN status FORMAT A20
COLUMN client_process FORMAT A15
SELECT process,
       status,
       client_process,
       thread#,
       sequence#
FROM   v$managed_standby
ORDER  BY process;

		

On releases where V$MANAGED_STANDBY is deprecated, use the supported replacement views, such as V$DATAGUARD_PROCESS, for process-level monitoring.

4 Check Data Guard Lag

Run on the target CDB:

			
COLUMN name FORMAT A30
COLUMN value FORMAT A30
COLUMN unit FORMAT A25
COLUMN time_computed FORMAT A30
COLUMN datum_time FORMAT A30
SELECT name,
       value,
       unit,
       time_computed,
       datum_time
FROM   v$dataguard_stats
WHERE  name IN ('transport lag',
                'apply lag',
                'apply finish time');

		

Interpretation:

Transport lag: Delay between redo generation and receipt.
Apply lag: Delay between redo receipt and recovery application.
Apply finish time: Estimated time to apply outstanding redo, when available.

A zero lag is desirable but not guaranteed during active workloads. Check DATUM_TIME and TIME_COMPUTED to ensure the measurements are current.

5 Verify PDB Data Files

Run on the target CDB:

			
COLUMN name FORMAT A20
COLUMN file_name FORMAT A100
SELECT p.name,
       df.file#,
       df.status,
       df.recover,
       df.name AS file_name
FROM   v$datafile df
JOIN   v$pdbs p
       ON p.con_id = df.con_id
WHERE  UPPER(p.name) = 'P_DEEDS_PD1'
ORDER  BY df.file#;

		

This verifies that data files belonging to the protected PDB are visible in the target CDB. File visibility alone does not prove that redo apply is healthy.

6 Final Health Check

Run the following on the target CDB to check the PDB and recent Data Guard errors:

			
SET LINESIZE 220
SET PAGESIZE 100
SELECT name,
       open_mode,
       recovery_status
FROM   v$pdbs
WHERE  UPPER(name) = 'P_DEEDS_PD1';
SELECT timestamp,
       severity,
       error_code,
       message
FROM   v$dataguard_status
WHERE  timestamp > SYSDATE - 1
ORDER  BY timestamp DESC;

		

Important: Execute the SQL queries in the appropriate CDB context. V$DATAGUARD_STATS and redo process views report database-level recovery information; use DGMGRL SHOW PLUGGABLE DATABASE to verify the individual PDB’s Data Guard role and status.

DBADEEDS