Restore and Recover Databases, Tablespaces, Datafiles, PDBs, and Selected Objects

Learning outcomes

A storage incident destroys one datafile, but a DBA immediately runs RECOVER DATAFILE and gets nowhere because there is no file to apply redo to. In another incident, a restored file is put online without recovery and Oracle reports that it needs media recovery. RMAN uses two distinct verbs: RESTORE materializes database files from backup/copies; RECOVER applies redo and/or incrementals to move those files to the required SCN.

1. RESTORE creates/replaces files; RECOVER advances their contents

2. Mandatory safe lab: ask RMAN what it would need

RESTORE DATABASE PREVIEW SUMMARY;

RESTORE PLUGGABLE DATABASE FREEPDB1 PREVIEW SUMMARY;

PREVIEW shows candidate backups and required archived redo from repository knowledge. If RMAN reports RMAN-06023/RMAN-06026 or no usable backups, that is valuable evidence: the current backup set is insufficient for that scope. Do not manufacture a “successful plan” by hiding missing backups.

3. RESTORE ... VALIDATE tests selection/readability without overwriting datafiles

RESTORE PLUGGABLE DATABASE FREEPDB1 VALIDATE;

This reads required backup pieces and validates the restore path but does not perform the destructive restore. It is stronger than LIST BACKUP, yet it still does not prove application startup, redo completeness to a desired point, keystore availability, performance/RTO, or correctness after recovery.

4. Whole-database recovery is the widest blast radius

SHUTDOWN IMMEDIATE;STARTUP MOUNT;RESTORE DATABASE;RECOVER DATABASE;ALTER DATABASE OPEN;

For complete recovery using all required redo, opening normally is possible. For incomplete/point-in-time recovery, the database is normally opened RESETLOGS, creating a new incarnation. Never test this on the only production copy.

5. PDB recovery can leave other PDBs online

-- SQL*Plus/root:ALTER PLUGGABLE DATABASE FREEPDB1 CLOSE IMMEDIATE;-- RMAN/root:RESTORE PLUGGABLE DATABASE FREEPDB1;RECOVER PLUGGABLE DATABASE FREEPDB1;-- SQL*Plus/root:ALTER PLUGGABLE DATABASE FREEPDB1 OPEN;

Other PDBs can remain available in many PDB recovery scenarios. Exact prerequisites depend on local/shared undo, lost files, backup scope, redo, encryption, and whether recovery is complete versus point-in-time.

6. Tablespace/datafile recovery narrows the unit further

When a single non-SYSTEM tablespace or datafile is damaged, you may be able to offline only that unit, restore it, recover it, and bring it online. The business benefit is a smaller outage than whole-database recovery. The cost is more careful object/tablespace dependency analysis.

-- SQL*Plus:ALTER TABLESPACE servicehub_data OFFLINE IMMEDIATE;-- RMAN:RESTORE TABLESPACE servicehub_data;RECOVER TABLESPACE servicehub_data;-- SQL*Plus:ALTER TABLESPACE servicehub_data ONLINE;

Do not use the name unless that tablespace actually exists. In a PDB, connect RMAN appropriately to the PDB for unambiguous tablespace scope, or use supported PDB-qualified recovery syntax from root.

7. Image-copy switching can reduce physical copy-back time

If a valid image copy already exists in a suitable permanent location, RMAN can update it with incrementals and/or switch a datafile to use the copy. SWITCH DATAFILE ... TO COPY changes file-name metadata to make the image copy current; it does not itself apply missing redo.

LIST COPY OF DATAFILE 7;SWITCH DATAFILE 7 TO COPY;RECOVER DATAFILE 7;

Use only a verified copy in a location appropriate for long-term database operation; an image copy sitting in a disposable backup staging directory may be a poor permanent datafile location.

8. Table recovery uses an auxiliary database workflow

Current RMAN can recover selected tables/table partitions to a past point without rewinding the entire PDB. RMAN automatically creates an auxiliary recovery environment, restores/recoveries the required files there, uses Data Pump to export the selected objects, and can import/remap them into the target.

RECOVER TABLE SERVICEHUB_OWNER.WORK_ORDERS  OF PLUGGABLE DATABASE FREEPDB1  UNTIL SCN 123456789  AUXILIARY DESTINATION '/safe/aux/sh15'  REMAP TABLE    'SERVICEHUB_OWNER'.'WORK_ORDERS':'WORK_ORDERS_RECOVERED';

Table recovery requires a local target connection, enough auxiliary disk, backups/redo covering the target, and Data Pump-compatible object conditions. A remapped table lets you compare recovered data before merging it into the live object.

9. Deliberately wrong: restore directly over the production file to “test the backup”

A real RESTORE can overwrite/replace files and force recovery/outage. Testing it against the production target is not validation—it is a recovery event. Use RESTORE ... VALIDATE, a duplicate database, a copied PDB, or an isolated restore host for rehearsal.

10. Recovery verification checklist

• Correct database/PDB/service/container identity.
• Requested files online and V$RECOVER_FILE/alert log clean for the repaired scope.
• Required archived redo applied through intended target SCN/time.
• Application schema objects valid and critical row-count/business checks pass.
• Encrypted tablespaces/backups accessible with the correct keystore/key material.
• New incarnation/RESETLOGS status recorded where applicable.
• Fresh backup taken after a recovery state that invalidates/rebases prior assumptions.

11. Production judgment

Pick the narrowest recovery scope that satisfies correctness and outage requirements, but do not sacrifice dependency consistency for a smaller outage. Use preview/validate in normal operations and execute real restore/recovery only from an approved incident plan or isolated drill. A cataloged copy is not a validated copy; a restored file is not a recovered file.

No pack or COMPATIBLE change is required for preview/validate. Table/PDB recovery has current-version and auxiliary/storage prerequisites. Lesson 4 now verifies the media and block integrity behind these plans and shows why repository status alone is insufficient.

Authoritative references

• Performing Complete Database Recovery — whole/scoped media recovery
• RESTORE Reference — restore, preview and validate semantics
• RECOVER Reference — CDB/PDB/tablespace/datafile/block recovery scopes
• Recovering Tables and Table Partitions — auxiliary/Data Pump table recovery
• RMAN Backup Concepts — image copies, CATALOG and switch-to-copy context