Review the Alert Log and Monitor Oracle 26ai Recovery Progress

Combine Three Sources of Evidence

Each source answers a different question. Correlating them prevents a single message or measurement from being mistaken for the result of the entire recovery.

1. Connect to the Correct Instance

Start a separate SQL*Plus session with the privileges needed to inspect the recovery. For a local connection using an authorized operating-system account, the following command uses operating-system authentication:

sqlplus / as sysdba

This example assumes that the local Oracle environment and authentication are configured correctly. Selecting ORACLE_SID helps identify a local instance, but it does not by itself prove that every connection reaches the intended database. The Oracle home, connection method, and any service selected also matter.

Verify the actual connection in SQL*Plus:

SELECT instance_name, status FROM v$instance;
SHOW CON_NAME

For this whole-CDB example, the monitoring connection should be in CDB$ROOT on the instance performing recovery. The instance name identifies the running instance, while the container name distinguishes the root from a PDB. Check both before interpreting diagnostic information.

In RAC, identify the instance doing the work and its diagnostic home. A view queried on another instance may not show the expected activity. If the recovery procedure restarts the instance, the monitoring session may disconnect and require reconnection. Existing diagnostic files remain available through ADRCI when filesystem access is available, even while SQL queries cannot run.

2. Locate the Alert Log Through ADR

Oracle stores diagnostic data in the Automatic Diagnostic Repository, or ADR. Database instances, listeners, and ASM instances have distinct diagnostic homes. Select the database instance involved in the recovery rather than assuming that any Oracle alert log describes the same operation.

Use the following query to discover the diagnostic locations:

SELECT name, value
FROM   v$diag_info
WHERE  name IN ('Diag Trace', 'Diag Alert');

Diag Trace identifies the directory containing the conventional text alert file, named alert_<SID>.log, along with trace files. Diag Alert identifies the XML alert-log directory, conventionally containing log.xml. Replace the SID placeholder with the actual instance identifier when locating the text file.

Use the returned locations instead of assuming a fixed installation path. The ADR base can depend on the diagnostic configuration and environment. Also, the Default Trace File value available in V$DIAG_INFO refers to the current process's trace file, not the database alert log.

SQL-based discovery requires an appropriate instance state and privileges. If SQL access is temporarily unavailable, use previously recorded locations or ADRCI's list of homes. Check the home identity carefully, especially on hosts running several databases or Oracle components.

3. Follow Recent Messages with ADRCI

ADRCI is the command-line tool for working with ADR diagnostic data. Start it from an operating-system terminal with access to the relevant diagnostic files:

adrci

Inside ADRCI, list the available homes, select the relevant database instance home, and follow recent alert entries:

show homes
set homepath <ADR-home>
show alert -tail 50 -f

Replace <ADR-home> with the actual path returned by SHOW HOMES. It is a placeholder, not a literal command argument. Choose one database home for this example so that the output can be correlated with the instance you verified in SQL*Plus.

-tail 50 displays recent entries, and -f keeps displaying new entries as they arrive. Press Ctrl+C in this ADRCI monitoring terminal to stop the live display. That action ends the display in this terminal; it does not cancel recovery running in a separate RMAN session.

For a recent review that returns to the ADRCI prompt, use:

show alert -tail 100

A tail is only a window into the log. Review enough history to cover the recovery attempt's start and end, including relevant errors before recovery began. Match timestamps carefully when application records, terminal output, and diagnostic messages use different time interpretations.

Before observation begins, record the database and instance, the planned recovery target, and the approximate start time. These details help separate the current attempt from earlier failures or restarts. If the operator changes the recovery plan, record that change as well; otherwise, a later completion message can be judged against a target that is no longer being used.

Searching for recovery or redo keywords can help locate events, but retain the surrounding unfiltered entries. Narrow filters can hide storage errors, referenced trace files, and explanatory lines. Preserve the command transcript and diagnostic context so that another DBA can follow the same evidence.

5. Measure Live Progress

Monitor Media Recovery Measurements

Use V$RECOVERY_PROGRESS to inspect measurements exposed for media recovery. From the separate SQL*Plus session, run:

SELECT start_time, item, sofar, total, units
FROM   v$recovery_progress
WHERE  type = 'MEDIA RECOVERY';

Read ITEM and UNITS together. Redo applied, apply rates, and elapsed time describe different aspects of the operation. Do not add their values or compute a universal recovery percentage by dividing every SOFAR value by TOTAL. Compare repeated samples for the same recovery attempt.

Where populated, the view also describes last-applied redo through its documented TIMESTAMP and COMMENTS fields. Available rows and measurements depend on the recovery mode and instance. Empty output does not prove success. It may reflect the current phase, the instance queried, or the absence of matching live measurements.

The alert log may contain progress summaries, but do not depend on a fixed schedule for those messages. Use the available measurements together with command output to determine what is happening during intervals without new alert entries.

Monitor RMAN Restore Operations

V$SESSION_LONGOPS can show progress for RMAN job steps. This query extends the diagram's example with session identity, timestamps, and a percentage for each reported operation:

SELECT sid,
       serial#,
       opname,
       start_time,
       sofar,
       totalwork,
       units,
       ROUND(100 * sofar / NULLIF(totalwork, 0), 1) AS operation_pct,
       last_update_time
FROM   v$session_longops
WHERE  opname LIKE 'RMAN%'
AND    opname NOT LIKE '%aggregate%'
AND    totalwork > 0
AND    sofar <> totalwork
ORDER BY start_time, sid;

Each matching row describes a job step, not the entire recovery procedure. With several RMAN channels or backup sets, an operation can reach completion while other work remains. The percentage does not include every subsequent redo-application, reopening, and application-validation task.

For example, a channel may finish restoring one backup set and begin another. The first row can disappear from this filtered result, while a new row reports a lower percentage. That transition does not mean the database has moved backward or lost previously restored work.

The filter excludes aggregate rows, completed rows, and rows without a positive total. An empty result means no row currently matches those conditions. It does not establish that RMAN succeeded. Also, unrelated RMAN work can match the filter, so correlate SID, SERIAL#, operation, and start time with the recovery being monitored.

Investigate an Apparent Stall

Repeatedly unchanged measurements warrant investigation, but do not immediately cancel recovery. First establish whether the same operation remains active. Then compare the latest RMAN output, alert entries, and any evidence of waits or storage activity. Tape access, remote storage, and transitions between phases can affect visible progress.

Record the sample times and which measurements changed. A report that identifies the current phase, last observed progress, and accompanying errors is more useful than saying only that recovery appears frozen. Keep the observer session focused on evidence while the recovery operator decides any intervention.

Example: Distinguish a Phase Change from a Problem

Suppose a restore operation reports 80 percent for one backup set. At the next observation, that row is absent and RMAN output indicates that restoration has completed. Media recovery measurements then begin changing. Together, those observations indicate a transition from restoring files to applying recovery information, rather than completion of the entire recovery procedure.

Now suppose the progress query returns no rows, but RMAN reports that it cannot read a required backup piece. The same empty result has a different meaning in this context. The command output identifies a problem requiring investigation; the absence of a progress row does not override it.

In a third case, media recovery completes and the status query still reports the database as mounted. That can be the expected state before the operator performs the planned reopening. The observer should report the completed phase and current state accurately rather than infer either a failed recovery or an already available application.

6. Confirm Recovery and Database Status Separately

When RMAN reports completion, verify that the report belongs to the intended operation and recovery endpoint. Review relevant errors and alert-log events from that attempt. A media recovery completion message means that recovery reached its reported result; it does not itself open the database.

After the recovery operator has performed the planned reopening, check the actual CDB and PDB states from SQL*Plus:

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

Compare the results with the intended operating state. The CDB may be open while an application PDB remains mounted. Some PDBs may intentionally be read-only, and PDB$SEED normally remains read-only. Do not treat every state other than READ WRITE as an error without considering the plan.

For whole-CDB DBPITR, the recovery procedure includes RESETLOGS. This creates a new incarnation and restarts redo sequence numbering; database SCNs and DBID do not reset to 1. RESETLOGS can also follow complete recovery using a backup control file, so its presence alone is not proof that data was lost.

The monitoring session should verify these actions, not issue OPEN RESETLOGS as a status check. If the database remains mounted unexpectedly, compare its state with the operator's last completed step. Opening is a recovery decision with prerequisites, not a command to try merely because the log became quiet.

Finish with Application Verification

A recovery can be technically consistent while returning the application to the wrong business state. Check representative records and confirm that the unwanted changes were excluded. Identify valid later transactions that are absent from the recovered scope, and reconcile external systems before replaying work.

Retain the recovery transcript, relevant alert-log interval, target information, and final status observations. These records explain what was done and support any follow-up investigation. Do not remove archived redo merely because one message said a log was no longer needed for the completed recovery.

A useful final report answers four questions: which recovery operation completed, what endpoint it reached, which CDB and PDB states were verified, and whether application checks passed. Together, these observations provide a stronger conclusion than any single completion message or progress percentage.

The next lesson shows how to perform a time-based recovery.

Incomplete Recovery Concepts - Quiz