Restore and Recover a Lost Datafile in Oracle 26ai

A missing datafile does not always require restoring the entire database. When the failure affects an ordinary user datafile and the necessary recovery material remains available, you can isolate that file, restore it, apply recovery, and return it to service. The important decisions are which file actually failed, which container owns it, and whether the required changes can be recovered.

This lesson uses a single-instance primary container database (CDB) in ARCHIVELOG mode. The current control files, server parameter file, and online redo remain usable. The main example assumes a suitable datafile backup and the required recovery history. Later sections explain the alternative when no backup of the file exists and the limits for special files.

1. Identify the Failure and Recovery Material

Start with the alert log, the reported Oracle errors, and storage diagnostics. A file may be inaccessible because of an unavailable mount, permissions, or a storage fault rather than deletion. Correct a reversible access problem before overwriting a surviving file. Record any restore or control-file changes already attempted by another administrator.

The example uses CDB ORC1, PDB APPPDB, tablespace APP_DATA, and absolute datafile number 7. These are illustrative identifiers. File 7 could be a critical system file in your database. Establish the real mapping before executing any state-changing statement.

SQL*Plus, authorized session at CDB$ROOT:

SHOW CON_NAME

SELECT name, database_role, open_mode, log_mode
FROM v$database;

SELECT con_id, file#, name, status
FROM v$datafile
ORDER BY con_id, file#;

SELECT con_id, name, open_mode
FROM v$pdbs
ORDER BY con_id;

SELECT con_id, file#, error, online_status, change#, time
FROM v$recover_file
ORDER BY con_id, file#;

SELECT con_id, file#, name, status, error, recover
FROM v$datafile_header
WHERE recover = 'YES'
   OR error IS NOT NULL
ORDER BY con_id, file#;

Match the file's CON_ID to its container and confirm the tablespace with RMAN's schema report. View visibility depends on privileges and database state. V$RECOVER_FILE does not contain a filename column; use V$DATAFILE for that information. A restored control file, or one recreated after the failure, may lack the information needed for an accurate recovery-status assessment.

V$DATAFILE_HEADER reads headers, not every block. An empty error result therefore does not establish that the file is free of corruption. For isolated corrupt blocks in an otherwise accessible file, RMAN block media recovery may provide a narrower repair when its prerequisites are met. This lesson concentrates on whole-file restoration.

Check the Recovery Inputs

Connect RMAN to CDB$ROOT using an authorized common account with SYSBACKUP or SYSDBA. Connect to the recovery catalog if the installation uses one. Independently verify the target database identity; do not assume that an existing SQL*Plus session determines the RMAN connection.

RMAN, root connection after confirming file 7:

REPORT SCHEMA;
LIST BACKUP OF DATAFILE 7;
RESTORE DATAFILE 7 PREVIEW;

PREVIEW reports the restore selection from repository metadata. It does not read every backup block. LIST BACKUP also does not identify every possible resource, such as an uncataloged image copy. Inspect known copies with LIST COPY and ensure usable backups are recorded and accessible through the configured channels.

When appropriate, check the selected restore inputs before starting the repair:

RESTORE DATAFILE 7 VALIDATE;

This RMAN operation reads the selected backup inputs without writing the restored datafile. It can generate substantial I/O and does not prove that the complete recovery history is available. Check required archived redo, usable online redo, destination space, and backup-device access separately. Encrypted backups and tablespaces require their applicable passwords, keys, and keystore access.

2. Isolate the Affected User Datafile

In the main scenario, APPPDB remains open and file 7 is an ordinary permanent user datafile. Take the verified file offline before restoring it. Objects stored in that file become unavailable, so coordinate the interruption with the affected application even if other PDBs and tablespaces remain operational.

SQL*Plus, common administrative session switching to APPPDB:

ALTER SESSION SET CONTAINER = APPPDB;
SHOW CON_NAME
ALTER DATABASE DATAFILE 7 OFFLINE;

Taking the containing tablespace offline is an alternative when the intended recovery scope includes it. It is not an extra mandatory step after taking one file offline. OFFLINE IMMEDIATE omits the normal checkpoint and requires media recovery before the tablespace can return online; it should not be presented as the default for unrelated maintenance.

If the file is already offline or the affected PDB is closed, adapt the procedure to that state. Do not issue an unconditional shutdown merely because a recovery example contains one. SYSTEM files and active undo do not follow this ordinary-user-file workflow.

3. Restore and Recover with RMAN

For restoration to the original location, use the verified absolute file number in the root-connected RMAN session:

RESTORE DATAFILE 7;
RECOVER DATAFILE 7;

RESTORE retrieves the selected backup of the file. RECOVER advances that restored file using incremental backups and redo as applicable. Complete recovery may need online redo as well as archived logs. ARCHIVELOG mode supports this process but does not guarantee that every required input still exists.

Read the output from both commands. If recovery reports a missing log or another error, resolve it before returning the file to service. A later redo sequence does not automatically replace an earlier missing sequence. An incomplete recovery target is a separate decision with consistency and possible data-loss consequences, not a shortcut for completing this repair.

After successful complete recovery, return to SQL*Plus connected to APPPDB:

ALTER DATABASE DATAFILE 7 ONLINE;

If you isolated a whole tablespace instead, restore its availability at that same scope after the necessary recovery. The demonstrated single-datafile complete recovery uses the current control file and does not require OPEN RESETLOGS. Restoring a backup control file or performing database point-in-time recovery changes the procedure.

Alternative: Restore to a New Location

If the original storage cannot be used, choose relocation instead of the original-location restore. Confirm that the destination has sufficient capacity and appropriate permissions, and that it does not conflict with another file. The example pathname belongs to APPPDB.

RMAN connected to CDB$ROOT, file 7 still offline:

RUN {
  SET NEWNAME FOR DATAFILE 7
    TO '/u02/oradata/ORC1/APPPDB/app_data01.dbf';
  RESTORE DATAFILE 7;
  SWITCH DATAFILE ALL;
  RECOVER DATAFILE 7;
}

SET NEWNAME selects the destination for this operation. SWITCH updates the control-file reference so subsequent operations use the restored file. Here, only file 7 has a new-name assignment; SWITCH DATAFILE ALL applies to those assignments and does not restore every database file. After recovery succeeds, use the same APPPDB online step and verify the new pathname.

4. Alternative: User-Managed SQL*Plus Recovery

SQL*Plus can perform media recovery when you have a valid datafile image copy or user-managed backup. It cannot extract a datafile by treating an RMAN backup piece as an ordinary datafile. RMAN can also restore a file before SQL*Plus applies recovery; the tools need not be treated as mutually exclusive products.

For this alternative, assume the same verified ordinary user file is offline and its original recorded pathname is /u01/oradata/ORC1/APPPDB/app_data01.dbf. Restore the valid backup image to that original filesystem location using the applicable user-managed procedure, with suitable permissions. An arbitrary operating-system copy taken while a file was changing is not automatically a valid online backup.

SQL*Plus connected to APPPDB, after restoring the valid image:

RECOVER DATAFILE '/u01/oradata/ORC1/APPPDB/app_data01.dbf';

Supply the required redo when prompted. SQL*Plus may request archived logs, and completing recovery may require available online redo. Cancelling the operation does not establish complete recovery. Only after successful completion should you issue:

ALTER DATABASE DATAFILE
  '/u01/oradata/ORC1/APPPDB/app_data01.dbf' ONLINE;

If restoring to another pathname, update Oracle's file reference using the applicable rename procedure before recovery. Copying a file elsewhere does not update the control file. For the relocation scenario taught here, the RMAN SET NEWNAME and SWITCH workflow makes that requirement explicit.

5. Recover a Datafile Without Its Own Backup

Suppose a user datafile was added after the last backup and was lost before it was backed up. Recovery may still be possible if its metadata and all required redo since creation survive. Instead of starting from a backed-up datafile, Oracle starts from an empty replacement and reconstructs its contents.

With the file recorded in the control file, RMAN can create an eligible replacement during RESTORE and apply the required history during RECOVER. The Oracle 26ai Backup and Recovery User's Guide describes this behavior in section 16.1.2. Do not conclude that RMAN is unusable solely because there is no backup of this individual file.

The requirements are stricter than they may first appear. Retaining logs since last night's backup is insufficient if the file was created months ago and has never been backed up. Verify the necessary archived redo from creation and any online redo needed for the endpoint. Also confirm that the file's contents were generated with sufficient logging.

User-Managed Reconstruction

Section 37.4 documents ALTER DATABASE CREATE DATAFILE ... AS ... for an eligible lost file without a backup. This creates an empty replacement using recorded file information and changes the file reference. It does not recreate the lost application data until media recovery applies the required changes.

This is a separate recovery branch, not an operation to run on the successfully restored file above. For a confirmed lost, offline, non-SYSTEM user file belonging to APPPDB, with the original pathname recorded and all required redo verified, the SQL*Plus pattern is:

ALTER DATABASE CREATE DATAFILE
  '/u01/oradata/ORC1/APPPDB/app_data01.dbf'
  AS '/u02/oradata/ORC1/APPPDB/app_data01.dbf';

RECOVER DATAFILE '/u02/oradata/ORC1/APPPDB/app_data01.dbf';

Run it in the affected PDB's authorized administrative context, after verifying the replacement destination. Once complete recovery succeeds, bring that replacement online and perform the verification checks below.

SYSTEM datafiles are excluded from this documented reconstruction method. Changes from operations that generated insufficient redo, including applicable NOLOGGING operations, may also be unrecoverable from the logs. ARCHIVELOG mode cannot supply changes that were never fully logged. If these prerequisites fail, choose another supported recovery strategy rather than assuming the empty replacement contains usable data.

6. Distinguish Special File Types

Undo recovery depends on the container and local or shared undo configuration. Avoid attempts to bypass required undo to force the database open. For temporary storage, inspect V$TEMPFILE and existing assignments rather than dropping the whole TEMP tablespace as the first response.

Oracle's whole-CDB restore/open workflow can recreate missing temporary files, but invalid existing headers or destination errors can prevent recreation. Do not assume that every tempfile failure is automatically repaired. The tablespace administration documentation explains availability restrictions and SYSAUX behavior.

7. Account for ASM and Oracle Managed Files

ASM changes storage access and naming, not the need for usable recovery inputs. RMAN still needs repository information, accessible backups, suitable channels, and the necessary keys or credentials. Do not use a filesystem copy command to restore an ASM file.

With Oracle Managed Files, existing file references remain relevant. Setting DB_CREATE_FILE_DEST does not automatically redirect every restore of an existing datafile. Select an appropriate managed destination through the supported RMAN naming procedure when relocating, and update the reference with SWITCH. Verify the resulting location rather than inferring it from a parameter alone.

8. Understand NOARCHIVELOG Limits

The main workflow assumes the required recovery history exists. In NOARCHIVELOG mode, redo may already have been overwritten. Restoring one old read/write datafile beside current files does not produce a usable database when the changes needed to reconcile them are unavailable.

A suitable whole consistent recovery set is the usual fallback when complete media recovery cannot be supported. Specialized cases depend on available redo, incremental backups, file history, and control-file state. Follow the procedure for that exact situation instead of appending a generic RECOVER DATABASE and OPEN RESETLOGS sequence. Enabling archiving afterward cannot reconstruct lost history.

9. Verify Recovery and Restore Protection

After successful recovery and return to service, verify both the physical reference and application access. In SQL*Plus connected to APPPDB, inspect the repaired file and its tablespace:

SELECT file#, name, status
FROM v$datafile
WHERE file# = 7;

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';

Review recovery output and new alert-log errors. Check representative application objects affected by the failure. Where appropriate, use a targeted check in root-connected RMAN:

VALIDATE DATAFILE 7;

This checks the database file, unlike RESTORE DATAFILE 7 VALIDATE, which checks selected restore inputs. Logical block checks can extend validation, but neither operation substitutes for business-level reconciliation. Correct the underlying storage problem, record any relocation, and assess subsequent backup needs. A fresh whole-database backup is not a universal technical requirement after this single-file complete recovery.

Frequently Asked Questions

Must I stop the whole CDB?

Not always. An eligible ordinary user datafile can be isolated while other services remain available. SYSTEM files, active undo, container state, and the actual failure determine the required recovery procedure.

What is the difference between RESTORE and RECOVER?

RESTORE retrieves a datafile backup, or creates an eligible replacement in the documented no-backup case. RECOVER applies the required incremental backups and redo as applicable to advance the file to the recovery endpoint.

How do I identify the correct file number?

Use RMAN REPORT SCHEMA and container-aware V$DATAFILE inspection. Verify the absolute file number, owning PDB, tablespace, and pathname before changing file availability.

Can I recover a datafile without its own backup?

Sometimes. An eligible file can be reconstructed when the necessary metadata and redo since its creation are available. File-type restrictions and insufficiently logged operations can prevent this method from succeeding.

Does single-datafile complete recovery require RESETLOGS?

Not in the demonstrated scenario using the current control file. Recovery with a backup control file and database point-in-time recovery have different opening requirements.

Related Reading

• Recover a Lost Control File: Exercise
• Oracle Trace Files
• RMAN Backup and Recovery Guide

Technical basis: Oracle AI Database Backup and Recovery User's Guide, 26ai, G43741-04, May 2026, chapter 16 and sections 37.4–37.5. The examples illustrate recovery choices; actual identifiers, connection privileges, storage, and available recovery material determine the executable procedure.