Performing Closed Database Recovery in Oracle AI Database 26ai

Establish the Recovery Conditions

The examples assume a single-instance primary CDB in ARCHIVELOG mode, a usable current control file and startup configuration, and sufficient backups and recovery information. Required redo may include both archived logs and changes still present in surviving online logs. ARCHIVELOG mode alone does not guarantee that every required log remains available.

Connect to CDB$ROOT with suitable administrative privileges. The local connection examples assume operating-system authentication is configured and the environment identifies the correct Oracle installation and target instance. If backup media or data is encrypted, make the necessary keys, keystores, or backup passwords available as required.

The affected file in this lesson is a CDB-root SYSTEM datafile. A PDB's SYSTEM or local undo failure requires assessment of that PDB's recovery scope; it does not automatically require this same whole-CDB outage. RAC, standby recovery, and point-in-time recovery also have additional requirements.

Diagnose the Failure Before Restoring

Read the alert log and operating-system errors, identify the failed storage, and determine whether the data is damaged or temporarily inaccessible. A repaired access problem with intact files may require restarting the database and automatic instance recovery, without restoring an older file.

When a privileged SQL*Plus session can access the mounted or open database, inspect recorded recovery requirements:

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

Map the reported file number to its current name and container:

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

The recovery-view column is ONLINE_STATUS; STATUS belongs to the separate V$DATAFILE query. These queries avoid confusing the two views. If you extend the diagnosis with tablespace joins, include container identity because tablespace numbers are local to their containers.

For this example, diagnosis confirms that file number 1 identifies the damaged root SYSTEM datafile, recorded as /u02/oradata/CDB1/system01.dbf. Verify actual metadata before substituting these illustrative values. If the instance is stopped, obtain the diagnostic results after mounting at the appropriate step below.

V$RECOVER_FILE can be unreliable after restoration or re-creation of a control file. Even with the current control file, combine its results with the alert log and appropriate datafile-header or integrity checks. An empty result does not establish that every data block is healthy.

Prepare the Instance and Storage

If the database remains open and can shut down normally, use SQL*Plus to perform an orderly shutdown:

SHUTDOWN IMMEDIATE

The failure may already have stopped the instance. Check its state rather than issuing shutdown commands mechanically. An unresponsive instance may require SHUTDOWN ABORT under the incident procedure, but neither an abort shutdown nor STARTUP FORCE is the default recovery step.

Repair or replace the failed storage, confirm adequate capacity and permissions, and preserve the surviving control files and online redo. Establish that the selected backup belongs to the intended database and supports the required recovery path. Backup metadata, encryption access, and functioning media-management software may all matter.

RMAN can preview restore selection and validate backup readability when those checks are appropriate. A preview uses repository information; restore validation reads backup contents without restoring the datafile. Choose checks that address the incident rather than assuming a listed backup is readable.

Preferred Method: Closed Recovery with RMAN

rman target /

STARTUP MOUNT;

RESTORE DATAFILE 1;

RECOVER DATAFILE 1;

ALTER DATABASE OPEN;

Alternative Method: User-Managed Recovery with SQL*Plus

cp /backup/CDB1/system01.dbf /u02/oradata/CDB1/system01.dbf

sqlplus / as sysdba

STARTUP MOUNT

ALTER DATABASE DATAFILE 1 ONLINE;

RECOVER AUTOMATIC DATAFILE 1

Media recovery complete.

ALTER DATABASE OPEN;

Handle Alternate Log Locations and Missing Redo

If archived logs were restored to a staging directory, SQL*Plus can use that directory as its log source:

SET LOGSOURCE /restore/archivelogs
RECOVER AUTOMATIC DATAFILE 1

An alternative is to specify FROM in the recovery command:

RECOVER AUTOMATIC FROM '/restore/archivelogs' DATAFILE 1

Use these as separate alternatives. The SQL*Plus FROM clause cannot be combined with SET LOGSOURCE. Pointing recovery at existing logs does not change where the database archives newly filled online logs. The SQL*Plus RECOVER reference describes these options.

V$ARCHIVED_LOG records archived-log metadata; V$RECOVERY_LOG can help identify media-recovery log requirements. Neither proves a required file is physically readable. Match the recovery request to the correct thread, sequence, and incarnation, especially when several copies or historical logs exist.

AUTOMATIC does not retrieve a missing backup. Restore the required log through the appropriate tool, or supply a correct full filename when needed. Preserve surviving online redo because the last required changes may not yet exist in archived logs.

If required redo is unavailable, investigate other copies, backups, and usable incremental recovery paths. A permanently unresolved gap means the complete-recovery objective needs reassessment. Choosing an earlier endpoint involves its own restoration scope, consistency requirements, and possible data loss. It is not an automatic response to one missing-file message.

Worked Scenario: The Backup Restores but Recovery Stops

Assume the usable backup of the root SYSTEM datafile was taken overnight. The storage failure occurs the following afternoon. Other current datafiles remain intact, and the surviving online redo logs contain the latest redo. Restoring the overnight file succeeds, but the restored file still needs intervening changes before it can participate in the current database.

Recovery then requests an archived log that is absent from its original destination. The DBA finds that a scheduled job backed up that log before removing its disk copy. The problem is locating and retrieving the required recovery input, rather than immediately selecting an earlier database state.

With RMAN, an accessible log backup known to the repository can be restored as recovery proceeds. With user-managed recovery, the DBA retrieves the log through the appropriate restoration procedure and makes it available to SQL*Plus. In either case, the next decision follows the actual recovery request and the available backup evidence.

After the gap is resolved, recovery continues through the required redo, including surviving online redo when necessary. The good current datafiles do not need to be replaced just because the restored SYSTEM file came from an older backup. Recovery advances the affected file until the database can satisfy its opening requirements.

If the log has no usable copy and no alternative recovery path supplies the missing changes, the original complete-recovery plan remains unfinished. The DBA must evaluate recovery options for a consistent affected scope. Simply canceling recovery and trying RESETLOGS does not make the existing mixture of file states suitable for a chosen earlier endpoint.

Verify the Database and Application

After opening the CDB, inspect its mode and the states of its PDBs:

SELECT open_mode FROM v$database;

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

Open intended application PDBs only when needed. For example, if the application's SALES PDB remains mounted and should be available:

ALTER PLUGGABLE DATABASE sales OPEN;

The PDB name is illustrative. Saved state can restore a PDB's open mode when the CDB restarts, so explicit opening is not always necessary. Preserve intended read-only or closed states. Oracle documents this behavior in ALTER PLUGGABLE DATABASE.

Review the alert log, check affected files, and verify that intended services accept connections. Where warranted, use scoped integrity checks such as RMAN validation. Block-level validation does not establish that application transactions or business records have the expected meaning.

Complete recovery preserves committed changes when their required recovery information is available. Uncommitted work is rolled back. If a user lost the connection around a commit, verify the transaction's outcome before retrying: resubmitting a transaction that actually committed can create duplicate business operations.

Successful opening means the recovered files meet the consistency requirements for opening. It does not require identical header SCNs for every read-only or intentionally offline file, nor does it prove that every application service has resumed.

Protect the Recovered Database

Resume scheduled backups and decide whether additional protection is needed after the incident. Account for replaced storage, relocated files, available recovery points, and the organization's recovery objectives. Confirm that backup jobs can still reach their destinations and use the required keys and credentials.

Remove staged archived logs only after confirming that required retained copies remain available and that deletion follows the applicable policy. Avoid broad wildcard deletion from shared temporary directories. Record the restored file, backup source, recovery outcome, and any service restrictions that remain.

Keep the recovery record specific enough for another DBA to understand the result. Include the target database, container and file identity, original and final filenames, backup identifiers, errors encountered, and the checks used to confirm service. Document any log copies restored to temporary locations so that later cleanup distinguishes recovery staging from retained backup material.

Use the incident to improve the next recovery attempt. A backup that was readable but difficult to locate may indicate missing runbook details. Repeated media-access failures may justify testing that access during scheduled recovery rehearsals. Update the operational procedure to reflect the recovery that actually succeeded.

The essential order is diagnosis and preparation, restoration of the affected file, complete media recovery while mounted, normal opening with the current control file, and verification of database and application service.