Complete Media Recovery in Oracle AI Database 26ai

1. Diagnose the Failure and Identify the Affected Scope

Read the alert log and reported errors. Determine whether files are missing, temporarily inaccessible, or damaged, and identify their container and tablespace. Correct an underlying storage or access problem before assuming a backup must be restored.

Use file metadata, datafile-header checks, and appropriate RMAN diagnostics to establish what needs attention. In RMAN, the following command reports database file information:

REPORT SCHEMA;

Choose the smallest appropriate recovery scope. A lost application datafile does not automatically require restoring every file in the CDB. Conversely, files supporting SYSTEM or active undo cannot be treated like an ordinary application file that can simply remain offline.

2. Confirm the Recovery Inputs

Identify usable backups for the affected files and the redo needed to advance them. Preserve good current control files and online redo logs. A recovery catalog helps locate backups through metadata; it does not contain the backup data itself.

Check backup-media access, available restore space, and required credentials. For encrypted data or backups, ensure the necessary keystores, keys, or backup passwords are available and opened or supplied as required by the encryption method.

For a whole-database restore, this RMAN command previews backup selection:

RESTORE DATABASE PREVIEW;

Preview examines repository information. It does not read the backup contents to establish their integrity. Restore validation provides that additional check without writing restored datafiles:

RESTORE DATABASE VALIDATE;

Use a scope appropriate to the incident and account for the I/O and time required. Successful backup validation still does not replace verifying the complete recovery procedure and application result.

3. Establish the Required Database State

Whole-CDB datafile restoration and recovery require the database to be mounted, not open. If the instance is already stopped and its current control file and startup configuration are usable, STARTUP MOUNT establishes this state directly.

If the database is open, determine whether to close it or isolate eligible affected files for recovery while other data remains available. Files being restored to their active locations must be offline when the database remains open. Use the appropriate mounted procedure for critical-file recovery.

Shutdown behavior depends on the incident. Do not make an abort shutdown a routine recovery step. Similarly, do not restore the control file simply because a datafile was lost; the surviving current control file is valuable recovery information.

4. Restore the Files That Require Restoration

Restoration retrieves a suitable earlier copy of each affected file. It does not by itself bring that file current. RMAN selects backups using its repository and the applicable restore criteria.

For user-managed backups, the DBA restores valid physical file copies using the appropriate storage or operating-system procedure. SQL*Plus RECOVER cannot unpack an RMAN backup set; use RMAN to restore that backup format.

If the original storage location is unavailable, file relocation requires the corresponding metadata update. RMAN supports SET NEWNAME and SWITCH for suitable relocation workflows. Keep relocation decisions explicit rather than silently restoring a file somewhere the database will not use.

5. Apply the Required Recovery Information

RMAN RECOVER advances restored datafiles using the recovery information needed for consistency. It can restore required archived redo from backup and use applicable incremental backups. Surviving online redo may supply the most recent required changes.

Run the complete-recovery example in a session without an earlier point-in-time restriction such as SET UNTIL. Inspect errors and completion messages before continuing. Exhausting the logs in one directory is not the same as completing media recovery.

If a required archived log is missing, search other copies and backups. Applicable incrementals may also help advance datafiles. An unresolved gap in the selected recovery path means the intended complete-recovery endpoint has not been reached.

6. Open Normally or Return Recovered Files Online

After successful complete recovery using the current control file, the whole-database example opens with ALTER DATABASE OPEN. RESETLOGS is not the routine final step for this scenario.

Recovery using a backup control file requires RESETLOGS even when all required redo is applied. Deliberate database point-in-time recovery also has different opening requirements. Neither operation should be substituted silently for the current-control-file procedure.

For partial recovery while the database stays open, return the successfully recovered files or tablespace online as appropriate. Confirm the recovered scope before restoring dependent application access.

7. Verify Service and Resume Backup Protection

Check file status, expected PDB availability, application services, and representative business operations. Review the alert log for new errors. Restore scheduled backup protection and document the files, backups, and recovery information used.

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

SQL*Plus provides three common recovery scopes. The examples below are alternatives. They assume suitable file backups have already been restored and the necessary redo is available.

The tablespace name users and datafile number 7 are illustrative. Verify the actual object, file number, and container before using a command.

A tablespace is identified by its name, while a datafile is identified by its file number or filename. Do not use a tablespace name as though it were a datafile identifier.

These are SQL*Plus commands. RMAN uses its own RECOVER syntax and does not accept the SQL*Plus AUTOMATIC option. The server SQL statement ALTER DATABASE RECOVER is another interface; it is not an Enterprise Manager prefix to add indiscriminately. See the SQL*Plus RECOVER reference.

During user-managed recovery, SQL*Plus can suggest archived-log filenames and apply accessible logs with the expected names. Three related controls reduce repeated filename entry:

• Enter AUTO when an interactive recovery prompt requests a log.
• Specify AUTOMATIC in the SQL*Plus RECOVER command.
• Enable SQL*Plus autorecovery before issuing RECOVER.

The third approach can be expressed as:

SET AUTORECOVERY ON
RECOVER DATABASE

These controls select expected archived-log filenames. They do not retrieve missing backups and do not guarantee that SQL*Plus will locate required online redo automatically. Resolve a missing required log or supply the appropriate full filename when necessary.

If the final required redo remains in an online log, identify the correct surviving member for the requested thread and sequence. Continue until recovery reports successful completion, rather than stopping merely because the last archived file was applied.

CANCEL stops a recovery attempt. It does not prove completion or automatically determine that RESETLOGS is required. Recovery can be resumed after missing information becomes available. Intentionally accepting an earlier endpoint requires a separately planned incomplete-recovery procedure.

Archived logs may have been restored to a temporary directory because their original destination is unavailable. Point the recovery session to that location rather than changing the database's ongoing archiving configuration merely to find existing files.

In SQL*Plus, the FROM clause identifies the source for one recovery command:

RECOVER AUTOMATIC FROM '/restore/archivelogs' DATABASE

Alternatively, set the SQL*Plus log source before starting recovery:

SET LOGSOURCE /restore/archivelogs
RECOVER AUTOMATIC DATABASE

The directory is illustrative and must contain accessible required logs. Use these as separate alternatives; do not combine FROM with an active SET LOGSOURCE setting. An individual fully qualified filename can also be entered at a recovery prompt.

RMAN has a separate control for the destination of restored archived logs. Within an appropriate RUN block, SET ARCHIVELOG DESTINATION directs their staging. RMAN can restore logs as recovery proceeds, so manually staging every log before starting is optional.

Backups on tape or other SBT storage still require functioning media-management access and enough disk space for restored logs. The SQL*Plus SET reference explains LOGSOURCE and AUTORECOVERY.

Dynamic performance views help identify affected files and recorded recovery information. Their contents describe database metadata and state; they do not replace checking that physical files and backups can actually be read.

Use a suitably privileged administrative connection to the CDB root after mounting or opening as appropriate. First inspect recorded recovery requirements:

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

The column is ONLINE_STATUS, not STATUS. Then map file numbers to their names and containers:

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

For recovery-log requirements, inspect:

SELECT *
FROM v$recovery_log;

Results depend on the recovery context and container visibility. V$DATAFILE is preferable to DBA_DATA_FILES for mounted-database work. If combining container-aware views, include container identity in joins where appropriate.

Use V$DATAFILE_HEADER and the alert log to investigate further. Empty recovery-view results do not guarantee that every data block is healthy or that the application is operating correctly.

BACKUP DATABASE PLUS ARCHIVELOG;

Test your understanding of recovery scope, redo application, diagnostic views, and the opening requirements for complete recovery.

Complete DB Recovery Basics - Quiz

The next lesson demonstrates how to perform a closed database recovery.