Single Datafile Recovery in a Multitenant CDB
A 19c CDB named CDB1 hosts PDBs SALES and HR. The database is in ARCHIVELOG mode, uses a 1 TB FRA, and has a recovery catalog. On Wednesday at 10:00, users report ORA-01578 for a block in datafile 17, which belongs to the SALES PDB's APP_DATA tablespace. The CDB is open and SALES is open read/write. RMAN has a Sunday level 0 database backup, daily level 1 backups, and archived log backups every two hours. An automated cleanup script deleted several archived logs from the FRA after they were backed up; the backup catalog still lists them. The storage team confirms the datafile's disk is healthy. The application owner wants to avoid taking the whole CDB down and asks whether they can recover only SALES to current time. A junior DBA ran RESTORE DATAFILE 17 and received RMAN-06023.
1. Was the junior DBA's approach correct? Diagnose RMAN-06023 and list the minimal recovery steps for the single datafile while keeping the CDB open.
Read the worked answer
RECOVER DATAFILE applies redo; it does not restore a damaged or missing file. The immediate correction is to run RESTORE DATAFILE 17 and then RECOVER DATAFILE 17 from the root CDB connection. RMAN-06023 means RMAN found no usable backup or copy for that datafile. Check LIST BACKUP OF DATAFILE 17;, LIST COPY OF DATAFILE 17;, and CROSSCHECK BACKUP;. The Sunday level 0 should contain it if the file existed then; if the datafile was added after the level 0, the backup history explains the error. Confirm you are connected to CDB$ROOT, not the PDB, and that the recovery catalog recorded the backup. If the datafile is offline, bring it offline cleanly in SALES with ALTER DATABASE DATAFILE 17 OFFLINE;, restore and recover it, then ALTER DATABASE DATAFILE 17 ONLINE;. The CDB and other PDBs can stay open because only this datafile is affected.
2. The required archived log for recovery is no longer in the FRA but is in an RMAN backup. What commands make it available, and when would you be forced into PDB point-in-time recovery?
Read the worked answer
First make the archived log available again: RESTORE ARCHIVELOG FROM SEQUENCE 1200 UNTIL SEQUENCE 1210; using the actual sequence range, if it is cataloged. If RMAN does not know the copy, CATALOG START WITH '/backup/arch/'; then CROSSCHECK ARCHIVELOG ALL; and DELETE EXPIRED ARCHIVELOG ALL;. Then rerun RECOVER DATAFILE 17;. If the log was never backed up and is truly gone, current-time recovery is impossible. The least invasive option is PDB point-in-time recovery: close SALES, RESTORE PLUGGABLE DATABASE sales UNTIL SCN <scn_before_loss>;, RECOVER PLUGGABLE DATABASE sales UNTIL SCN <scn_before_loss>;, then open SALES read/write. This requires local undo for the PDB and loses all SALES changes after that SCN; HR is unaffected. Verify local undo before choosing this path, because without it PDB PITR is not available in the same way.
3. How do you confirm the PDB is consistent and the block corruption is gone before reopening to users?
Read the worked answer
After recovery, run RMAN> VALIDATE DATAFILE 17; and BACKUP VALIDATE CHECK LOGICAL DATAFILE 17; to scan for physical and logical corruption. Query V$DATABASE_BLOCK_CORRUPTION; it should show no rows for file 17. Check V$DATAFILE for ONLINE status and checkpoint consistency, and V$RECOVER_FILE, which should be empty. Inspect the alert log for ORA-01578 or recovery errors. Then open SALES if it was closed and run a targeted application check against the APP_DATA tablespace. If PDB PITR was used, also verify that the PDB is open read/write and that no datafile remains in offline or backup mode. A clean VALIDATE is necessary but not sufficient: compare row counts or application checksums for the affected table before releasing the PDB.