Diagnose and Recover a Missing Datafile in Oracle 26ai

Interpret the Error Before Restoring a File

A failed open operation may report messages resembling these illustrative diagnostics:

ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/u02/oradata/ORCL/APPPDB/app_data01.dbf'

ORA-01157 indicates that Oracle could not identify or lock the datafile. ORA-01110 identifies the file number and path associated with the error. Additional operating-system errors and the DBWR trace can help distinguish a missing file from unavailable storage or an access problem.

These messages do not, by themselves, prove that data blocks are corrupt. Check storage availability, file ownership and permissions, ASM access where applicable, and the alert log. If restoring access makes the original file usable, it may not need replacement from backup. It can still require media recovery, so continue checking its state.

An initial error can identify only part of the incident. Review the file inventory and diagnostics for other affected files rather than repeatedly attempting to open the database and assuming that the first reported file is the only problem.

Check the Instance and Container State

Use SQL*Plus with an authorized administrative connection to the correct instance. For local operating-system authentication, an appropriately authorized account can connect as follows:

sqlplus / as sysdba

Confirm the instance state and current container:

SELECT instance_name, status, database_status
FROM v$instance;

SHOW CON_NAME

If the instance is down, mount the CDB using its normal configured parameter file:

STARTUP MOUNT

If a failed startup already left the database mounted, continue there. Do not issue another startup merely because opening failed. If the CDB is open and one PDB is affected, avoid restarting healthy services without a recovery requirement.

When the CDB is mounted or open, inspect its identity and logging mode from the root:

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

Opening the CDB and opening a PDB are separate operations. For the worked example below, the root is healthy and open, while the affected PDB, APPPDB, is mounted. Establish this state only after confirming that no root-level failure or other outstanding recovery requirement prevents it. Do not force an open over missing essential files.

Map the File to Its Container and Tablespace

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

SELECT d.con_id, d.file#, t.name AS tablespace_name,
       d.name AS datafile_name
FROM v$datafile d
JOIN v$tablespace t
  ON t.con_id = d.con_id
 AND t.ts# = d.ts#
WHERE d.file# = 12;

SELECT con_id, file#, status, error, recover, fuzzy, name
FROM v$datafile_header
WHERE file# = 12;

SELECT file#, online_status, error, change#
FROM v$recover_file;

Decide Whether the Offline-File Approach Is Appropriate

The benefit of taking an eligible application file offline is that the PDB can provide access to unaffected data while repair continues. Objects that depend on the offline file remain unavailable. Check application dependencies before advertising the service as restored.

If the application cannot operate without the missing file, restoring and recovering it before reopening the affected PDB may be simpler. If an unaffected service can operate independently, the following procedure can reduce its interruption. Neither choice changes the need for successful recovery before returning the file online.

Recovery Workflow at a Glance

Prepare the Recovery Work Before Opening Partial Service

Decide which application functions can tolerate the offline file and which must remain stopped. A reporting tablespace may appear independent, but application views, validation queries, or scheduled jobs can still reference its objects. Coordinate that dependency assessment with the application owner before opening the PDB for partial use.

Confirm that the backup location can be reached from the recovery environment. Finding a backup record in RMAN is not the same as being able to read the backup piece. Storage credentials, media-management access, and encryption requirements can affect retrieval. Identify these dependencies before promising a restoration time.

Also preserve the distinction between the two administrative sessions. SQL*Plus controls the state of the file in APPPDB; the separate RMAN connection addresses the CDB root and uses the verified absolute file number. Avoid substituting a tablespace name without checking its container scope, because different PDBs can contain tablespaces with the same name.

Record the starting state and the chosen procedure. If recovery cannot complete because required redo is unavailable, keep the file unavailable and reassess the recovery target and data-loss implications. Do not convert a complete-recovery plan into an incomplete-recovery procedure simply to make the next command succeed.

Step 1: Take the Verified Application Datafile Offline

In SQL*Plus, use an authorized SYSDBA session that can switch to APPPDB. The CDB root is already open for this example. Switch to the owning PDB, confirm the container, and take the verified file offline:

ALTER SESSION SET CONTAINER = APPPDB;
SHOW CON_NAME

ALTER DATABASE DATAFILE 12 OFFLINE;

This is a datafile operation. Do not append IMMEDIATE to this command by copying syntax from ALTER TABLESPACE ... OFFLINE IMMEDIATE. Likewise, a file-online command later in the procedure changes the file state; it does not bring an independently offline tablespace online.

If APPPDB is mounted and all other prerequisites for opening it are satisfied, open the PDB:

ALTER PLUGGABLE DATABASE OPEN;

Omit this open command if APPPDB is already open. If it fails, inspect the new diagnostics and address outstanding requirements before proceeding. Do not infer that every remaining failure can be solved by offlining another file.

At this stage, data dependent on file 12 remains unavailable. Ensure that application routing or workload restrictions reflect that partial availability. The file has not been repaired merely because the PDB opened.

Step 2: Restore and Recover with RMAN

Use a separate RMAN target connection to CDB$ROOT as an authorized common user with SYSBACKUP or SYSDBA. A local connection such as the following requires the corresponding operating-system authorization and the correct instance environment:

rman target /

Confirm the target and file mapping before restoring:

REPORT SCHEMA;

When file 12 is missing or must be replaced, restore and recover that file:

RESTORE DATAFILE 12;
RECOVER DATAFILE 12;

RESTORE retrieves the datafile from a suitable backup. RECOVER brings it forward using the applicable recovery resources, including incremental backups and redo as required. Complete recovery may need available online redo as well as archived redo; the presence of some archived logs alone does not establish that the recovery chain is complete.

If the existing file is usable and only needs redo, omit RESTORE and perform the required recovery. Do not overwrite a usable file reflexively. Review RMAN output and resolve every recovery error before attempting to return the file online.

The example restores to the recorded location after its storage has been repaired. If that location cannot be used, plan a relocation using the appropriate RMAN new-name and switch procedure. Merely copying a file to a different directory does not update the database's recorded location. ASM and Oracle Managed Files also require their supported naming and storage procedures.

An operating-system COPY command cannot extract a datafile from an RMAN backup piece. The legacy user-managed copy procedure is therefore replaced here by RMAN restoration. Keep the backup source and recovery method consistent.

Step 3: Return the Recovered File Online

After RMAN reports successful recovery and outstanding errors have been resolved, return to the SQL*Plus session in APPPDB. Confirm the container again before changing the file state:

SHOW CON_NAME
ALTER DATABASE DATAFILE 12 ONLINE;

Check the affected file header and recovery information in that container:

SELECT file#, status, error, recover, name
FROM v$datafile_header
WHERE file# = 12;

SELECT file#, online_status, error, change#
FROM v$recover_file
WHERE file# = 12;

Review the alert log and confirm that application operations using the recovered data now work. From an authorized root session, check the PDB state as needed:

ALTER SESSION SET CONTAINER = CDB$ROOT;
SELECT name, open_mode, restricted FROM v$pdbs;

Confirm the relevant application service and representative reads and transactions, not just the PDB state. A successfully opened container may still have unrelated application, permission, or service-routing problems.

This example uses a current control file and complete recovery of an application datafile. It does not require OPEN RESETLOGS. Backup-control-file recovery and point-in-time recovery have different procedures and must be evaluated separately.

Investigate Suspected Block Corruption Separately

Once a file is accessible, suspected corruption within it can be investigated using RMAN validation. For example, from the root-connected RMAN session:

VALIDATE CHECK LOGICAL DATAFILE 12;

This checks physical corruption and supported logical block consistency; it does not validate every business rule or relationship in the application. It diagnoses problems rather than repairing them. Inspect the command output and, from SQL*Plus, the recorded block-corruption information:

SELECT file#, block#, blocks, corruption_type
FROM v$database_block_corruption
WHERE file# = 12;

Depending on the findings and prerequisites, the appropriate response may be block media recovery, file restoration and recovery, or another documented repair. Do not treat a successful header read as equivalent to validating all blocks, or treat a missing-file error as proof that block repair is required.

Cases That Need a Different Recovery Plan

In NOARCHIVELOG mode, do not use OFFLINE FOR DROP as a reversible substitute for this procedure. It marks a file for dropping and does not preserve the ordinary ARCHIVELOG recovery path demonstrated here. A consistent whole-database restore is commonly required after media loss in that mode.

If a backup or required redo is unavailable, stop and reassess the recovery options. Certain newly created files can be reconstructed under documented conditions when the complete required redo history exists, but that is a separate procedure. It is not a universal fallback for imported or plugged-in datafiles, required SYSTEM files, or changes that cannot be reconstructed from redo.

Missing tempfiles likewise call for a targeted tempfile procedure. Verify the owning container and temporary tablespace before replacing a tempfile. Do not drop the entire temporary tablespace merely because one file is unavailable.

After recovery, document the root cause, the files involved, the backup and redo used, and the application checks performed. Restore storage redundancy and review the backup schedule, especially if file locations or protection requirements changed. The next lesson explains parallel recovery operations.