Backup Options   «Prev  Next»

Lesson 7Monitoring Recovery Status via Data Dictionary Views
ObjectiveObtain Recovery Status Information using the Data Dictionary Views

Monitoring Recovery Status via Data Dictionary Views

As an Oracle DBA, it might be possible to obtain "recovery status information" of a database in Oracle 12c by querying certain data dictionary views. Oracle's data dictionary views are a rich source of information about the database, encompassing a wide range of details from schema metadata to in-depth operational metrics, including aspects related to backup and recovery operations.
To access recovery-related information, one might consider exploring views such as:
  • `V$RECOVERY_FILE_STATUS`: This view could provide insights into the recovery status of each file in the database, offering details about whether files need recovery, are being backed up, or have other statuses.
  • `V$RECOVER_FILE`: This view might be used to identify files that require media recovery, potentially aiding in assessing the recovery needs of the database.
  • `V$BACKUP`: By querying this view, one may obtain information about backup files, including their status, which could indirectly inform recovery status by indicating the availability of backups for recovery processes.
  • `V$ARCHIVED_LOG`: This view might be useful for understanding the archiving status of redo logs, which are crucial for point-in-time recovery operations, thereby providing indirect insights into the recoverability of the database.

When querying these views, it's important to consider the specific recovery scenario and the type of information required to assess the recovery status effectively. For instance, if you are interested in the recoverability of specific data files or tablespaces, focusing on views that provide file-level details would be more pertinent. Additionally, it's advisable to consult the Oracle 12c documentation for the most accurate and detailed descriptions of the available data dictionary views and their intended use cases. This approach can ensure that the queries crafted to extract recovery status information are both accurate and aligned with best practices recommended by Oracle.
There are two data dictionary views that provide information about recovery status: V$RECOVERY_STATUS and V$RECOVERY_FILE_STATUS. You can query the two views as soon as the 
ALTER DATABASE RECOVERY
command is issued. The V$RECOVERY_STATUS view shows you all files that require recovery. The V$RECOVERY_FILE_STATUS view gives you the recovery status of each file. During recovery, the information for these views exists only in the PGA(Program Global Area)[1] of the server process. The PGA is a temporary area of memory used by the server process that is connected to the user. This area disappears when the connection ends. The above views are therefore only available to the user who issued the recovery command. In addition, the views become inaccessible after the recovery process is terminated. The following series of images demonstrates how to obtain "recovery status information" during a recovery using these two data dictionary views.

Monitoring 'Recovery Status' via the 'Data Dictionary Views'
1) Suppose a media failure brings down the database
1) Suppose a media failure brings down the database. After the media failure is corrected, the DBA attempts to open the database (1). But the database fails to open because one of the datafiles is damaged and needs recovery (2).

2) After the DBA restores the damaged datafile
2) After the DBA restores the damaged datafile (this step is omitted here), the DBA issues the   ALTER DATABASE RECOVER command to start the recovery process (3). The messages tell which archived redo log is needed for the recovery (4).

3) Now the DBA checks the recovery information in V$RECOVERY_STATUS
3) Now the DBA checks the recovery information in  V$RECOVERY_STATUS view (5). The result shows the required archived log number required for the recovery and the starting time (6).

4) The DBA queries V$RECOVERY_FILE_STATUS
4) The DBA queries   V$RECOVERY_FILE_STATUS view (7) to confirm the recovery status of the datafile (8).

5) The DBA performs actual recovery (9) and opens the database after the recovery is complete (10).
5) The DBA performs actual recovery (9) and opens the database after the recovery is complete (10).


  1. Suppose a media failure brings down the database. After the media failure is corrected, the DBA attempts to open the database (1). But the database fails to open because one of the datafiles is damaged and needs recovery (2).
  2. After the DBA restores the damaged datafile (this step is omitted here), the DBA issues the   ALTER DATABASE RECOVER command to start the recovery process (3).
  3. Now the DBA checks the recovery information in  V$RECOVERY_STATUS view (5). The result shows the required archived log number required for the recovery and the starting time (6).
  4. The DBA queries   V$RECOVERY_FILE_STATUS view (7) to confirm the recovery status of the datafile (8).
  5. The DBA performs actual recovery (9) and opens the database after the recovery is complete (10). 

SQL> STARTUP OPEN PFILE='C:\ORANT\DATABASE\INITORCL.ORA'
ORACLE instance started.
Total System Global Area                         11710464 bytes
Fixed Size                                          49152 bytes
Variable Size                                    11177984 bytes
Database Buffers                                   409600 bytes
Redo Buffers                                        73728 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'C:\ORANT\DATABASE\USR1ORCL.ORA'
SQL> ALTER DATABASE RECOVER DATAFILE 2;
ALTER DATABASE RECOVER DATAFILE 2
*
ORA-00279: change 6262125 generated at 02/13/00 20:21:24 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC480.1
ORA-00280: change 6262125 for thread 1 is in sequence #480
SQL> SELECT * FROM V$RECOVERY_STATUS;
RECOVERY THREAD SEQUENCE_N SCN_NEEDED TIME_NEED   REVIOUS_LOG_NAME     PREVIOUS_LOG_ REASON
-------- ------ ---------- ---------- ------------------
-----------------    ------------------------------------
13-FEB-00     1        480 0           13-FEB-00 NONE
NONE                 NEED LOG
1 row selected.
SQL> SELECT * FROM V$RECOVERY_FILE_STATUS;
FILENUM    FILENAME                             STATUS

-------    ------------------------------------ -------------- 
      2    C:\ORANT\DATABASE\USR1ORCL.ORA       IN RECOVERY
1 row selected.
SQL> ALTER DATABASE RECOVER 
      > AUTOMATIC LOGFILE 'C:\ORANT\RDBMS80\ARC480.1';
Statement processed.
SQL> ALTER DATABASE OPEN;
Statement processed.
SQL>

To see the command lines and their results for the whole process, click the View SlideShow link. The next lesson concludes this module.
[1]PGA: The Program/Process Global Area (PGA) is a memory region that contains data and controls information for a single server process or a single background process. The information contained in the PGA could be sort data, session information, cursor state, or stack space. The PGA is allocated when a process is created and de-allocated when the process is terminated.

SEMrush Software