Recover a Datafile While Oracle AI Database 26ai Stays Open

Conditions for This Recovery Procedure

Assume a single-instance primary CDB in ARCHIVELOG mode, with both the CDB and APPPDB open for read/write use. The current control file survives, a usable backup exists, and the required recovery information remains available. That information may include archived redo, surviving online redo, and applicable incremental backups.

The example concerns a permanent user datafile. SYSTEM, SYSAUX, active undo, and tempfiles are outside its scope. Their recovery requirements need separate assessment; excluding them here does not mean they all use the same alternative procedure.

Confirm backup-media access, sufficient restore space, Oracle's storage permissions, and any required encryption keys or backup passwords. The local connections below assume configured operating-system authentication, suitable administrative privileges, and an environment identifying the correct CDB.

Keeping the database open does not guarantee that every application operation succeeds. Queries, reports, or transactions that need the offline file can fail. Explain the affected business functions and restrict access to them as appropriate while preserving useful access elsewhere.

Recovery Overview

The diagram's statement that only the affected file is unavailable describes the physical recovery scope. Application operations can depend on that file even when they also access healthy files. The commands below reproduce the workflow as selectable text.

Step 1: Connect to the Affected PDB

From the configured database-host environment, start SQL*Plus:

sqlplus / as sysdba

Use an appropriately privileged common administrator to select the affected PDB and confirm the session's container:

ALTER SESSION SET CONTAINER = APPPDB;
SHOW CON_NAME

Container selection matters because multiple PDBs can contain a tablespace named USERS. A familiar tablespace name alone does not identify the correct recovery target. Confirm APPPDB is the application PDB involved in the incident before inspecting or changing its files.

Step 2: Identify the Damaged File

Read the alert log and reported storage errors. Determine whether the file was deleted, became inaccessible, or suffered physical damage. A filesystem or controller problem should be corrected before restoring onto the same storage.

In the SQL*Plus session connected to APPPDB, inspect the identified file and its header:

SELECT d.file#, d.name, d.status, h.recover, h.error
FROM v$datafile d
LEFT JOIN v$datafile_header h
  ON h.file# = d.file#
WHERE d.file# = 12;

The query joins file metadata with header information inside the current PDB. Match the returned filename to the reported failure. The left join preserves the metadata row when header information is unavailable, so missing header results must be investigated rather than interpreted as a healthy file.

To confirm the tablespace mapping in this open PDB, use:

SELECT file_id, file_name, tablespace_name
FROM dba_data_files
WHERE file_id = 12;

DBA_DATA_FILES is useful for mapping file identifiers and tablespaces, but its STATUS value AVAILABLE is not proof that the file is online, physically present, or free of corruption. Use the recovery and header information together with the alert log.

V$RECOVER_FILE can provide additional recovery requirements. Its results may be unreliable with a restored or re-created control file, and empty results do not establish complete block integrity. Likewise, a successful DBVERIFY check does not prove that a file requires no redo application before returning online.

Step 3: Ensure the File Is Offline

If file 12 is not already offline, take it offline from the APPPDB session:

ALTER DATABASE DATAFILE 12 OFFLINE;

Confirm that the operation succeeds before restoration. Oracle can take a damaged file offline after certain write failures, but you must establish the actual state. A failed query against a file does not by itself prove Oracle has taken it offline.

This operation isolates the file while the CDB and PDB remain open. Do not use OFFLINE DROP for this ARCHIVELOG recovery procedure. If the file is already offline, it does not need another offline operation merely to follow the numbered list.

Repair the underlying storage and verify the original destination is usable. If the problem was temporary and the current file remains intact, assess whether restoration is necessary. A file can still require media recovery after storage access returns, even if block checks find no corruption.

Step 4: Restore the Datafile with RMAN

Leave the SQL*Plus session connected to APPPDB. In a new terminal with the configured CDB environment, start RMAN:

rman target /

This example requires the RMAN target connection to CDB$ROOT. The SQL*Plus ALTER SESSION command changed only that SQL*Plus session; it did not change the container of this separate RMAN connection. Verify the intended target before continuing.

Where uncertainty about backup selection warrants a check, RMAN can preview the restore for this file:

RESTORE DATAFILE 12 PREVIEW;

Preview uses repository information to identify the backups RMAN would select. It does not read all backup contents to prove their integrity. If backup readability is the concern, restore validation performs that check without writing the restored datafile:

RESTORE DATAFILE 12 VALIDATE;

These are optional diagnostic checks, not substitutes for RESTORE and RECOVER. Their scope and timing should address the actual incident. Reading a large backup from remote media can consume significant time and I/O, so use the result to resolve a specific uncertainty rather than repeating validation mechanically.

A restore may also depend on a configured channel and functioning media-management software. For example, an accessible backup record does not ensure a tape device or remote backup service is reachable. Resolve those dependencies and ensure enough destination space before interpreting a failure as evidence that the datafile cannot be recovered.

With suitable backup access and the original filename still usable, restore the affected file:

RESTORE DATAFILE 12;

RMAN retrieves a suitable earlier copy of file 12 using its repository and accessible backups. It does not need to replace every datafile in USERS simply because the damaged file belongs to that tablespace. Preserve the good current files, control files, and surviving online redo.

If restoring to another location is necessary, use a planned relocation workflow, such as RMAN SET NEWNAME and SWITCH. The recorded filename must point to the restored file. The simple example here assumes restoration to the original location.

Datafile numbers and paths identify files across the CDB, allowing this root-connected RMAN procedure to recover a PDB file. Tablespace-name recovery requires the appropriate PDB connection because names can repeat. Oracle explains this distinction in its RMAN complete recovery guide.

Step 5: Recover the Restored File

After restoration succeeds, continue in the same RMAN session:

RECOVER DATAFILE 12;

Restoration and recovery perform different work. RESTORE retrieves an earlier physical copy; RECOVER advances that copy using the required recovery information. RMAN can use applicable incremental backups and restore required archived redo from accessible backups. Surviving online redo may supply additional required changes.

Use a complete-recovery session without an earlier SET UNTIL restriction. Review completion messages and errors before returning the file online. Reaching the last archived log in a directory is not equivalent to satisfying the file's recovery requirements.

If a required log is missing, locate another copy or backup, repair media access, or evaluate another usable recovery path. Archived-log metadata does not prove that a physical copy remains available. Match the required thread, sequence, and incarnation when identifying redo.

A permanently unresolved gap requires reassessing the recovery objective and consistency of the affected scope. Do not automatically switch to incomplete recovery or attempt RESETLOGS in response to a missing-log message. The CDB remains open throughout this worked complete-recovery procedure.

Step 6: Bring the File Online and Verify

After RMAN reports successful recovery, return to the SQL*Plus session in APPPDB. Confirm the session container before making the file available:

SHOW CON_NAME
ALTER DATABASE DATAFILE 12 ONLINE;

Rerun the file/header query. Expected evidence includes the file's ONLINE status, no reported header recovery requirement, and no header error. In the diagram these appear as STATUS = ONLINE, RECOVER = NO, and ERROR being NULL. Those expectations require a readable header and valid results; a missing joined row is not equivalent to successful verification.

Review the alert log and test representative application operations that access the recovered data. Check affected services and business functions before declaring the incident resolved. File status and block checks do not independently establish application correctness.

No ALTER DATABASE OPEN statement is needed because the database was never closed. The successful operation restores the file's availability using ONLINE, without RESETLOGS.

When Tablespace Recovery Is More Appropriate

A tablespace-level operation can be appropriate when several of its files are affected or when isolating the entire tablespace is operationally simpler. This changes the availability scope: all files in that tablespace become unavailable. Run tablespace commands in the affected PDB.

TEMPORARY in this syntax describes how a permanent tablespace is taken offline; it does not convert it into a temporary tablespace. Oracle documents these options in ALTER TABLESPACE.

Keep restoration scope separate from offline scope. Taking USERS offline does not automatically require restoring every healthy file, but recovery must satisfy the requirements of the files being returned online. Finish a tablespace-offline workflow with the appropriate ALTER TABLESPACE USERS ONLINE operation, rather than assuming a single datafile ONLINE command restores the entire tablespace's availability.

User-Managed Recovery with SQL*Plus

A valid physical backup copy can also be restored with the appropriate operating-system or storage utility while the file remains offline. This is an alternative to RMAN-managed restoration. SQL*Plus cannot extract a datafile from an RMAN backup set.

After restoring the physical copy and confirming its recorded location and permissions, a suitably privileged SQL*Plus session in APPPDB can apply redo:

RECOVER AUTOMATIC DATAFILE 12

AUTOMATIC selects expected archived-log filenames; it does not retrieve unavailable backups. If required archived logs were staged elsewhere, one option is:

SET LOGSOURCE /restore/archivelogs
RECOVER AUTOMATIC DATAFILE 12

Alternatively, specify the source in the recovery command:

RECOVER AUTOMATIC FROM '/restore/archivelogs' DATAFILE 12

Use SET LOGSOURCE and FROM as separate alternatives, not combined settings. These SQL*Plus controls do not change the destination for newly archived redo. The SQL*Plus RECOVER reference describes the supported syntax.

Resolve required redo, confirm successful media recovery, and then return the recovered file online. Oracle's user-managed open recovery procedure also explains the tablespace-level approach. Do not run this alternative after successful RMAN recovery merely because both tools appear in the lesson.

Example: Availability During a Missing-File Incident

Suppose a backup of file 12 was taken overnight, and the file becomes inaccessible during the afternoon. APPPDB remains open. Some requests continue because their data is elsewhere, while an application report fails when it accesses an object stored in the missing file.

The DBA confirms the container and filename, takes the file offline if necessary, and repairs the destination. Restoring the overnight backup succeeds. The affected report should still remain unavailable: the restored file has not yet received the recovery information needed to return online.

RMAN then applies the required recovery information and completes recovery. Only after the SQL*Plus ONLINE operation succeeds does the DBA test the report and other affected application functions. The useful service improvement comes from leaving unaffected data accessible during restoration and recovery, not from pretending that the offline file causes no application disruption.

Complete the Operational Checks

Keep a brief incident record as recovery proceeds. Record the CDB, PDB, file number, original filename, and confirmed backup source together. That association helps prevent a later shift from the identified PDB file to another USERS tablespace merely because the name looks familiar.

Record the last successful stage separately from the failed stage. A completed RESTORE followed by a missing-log error means recovery remains unfinished; it does not necessarily mean restoration must be repeated. Reassess the current file state and resolve the reported problem before deciding which commands to resume.

Likewise, successful media recovery followed by an application error needs investigation at the relevant layer. Confirm ONLINE succeeded, then examine the specific service or transaction failure. Do not infer that every later error requires replacing the datafile again.

Reconcile application requests that failed or lost their connections around the incident. A retry can duplicate an operation if its commit succeeded before the client received confirmation. Determine transaction outcomes before asking users to repeat work.

Resume scheduled backups and decide whether additional backup protection is appropriate after the repair. Document the recovered file, backup source, destination, recovery result, and verification performed. Remove staged archived logs only under the applicable retention and deletion policy, after confirming required copies remain available.