| Lesson 7 | Monitoring Recovery Status via Data Dictionary Views |
| Objective | Obtain Recovery Status Information using the Data Dictionary Views |
Monitoring Recovery Status via Data Dictionary Views
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) 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.
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.
- 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).
- 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).
- 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).
- The DBA queries V$RECOVERY_FILE_STATUS view (7) to confirm the recovery status of the datafile (8).
- The DBA performs actual recovery (9) and opens the database after the recovery is complete (10).
Monitoring Recovery Status via Data Dictionary Views
Click the View SlideShow link below. This SlideShow demonstrates how to get recovery status information during a recovery using these two data dictionary views.
SVRMGR> 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'
SVRMGR> 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
SVRMGR> 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.
SVRMGR> SELECT * FROM V$RECOVERY_FILE_STATUS;
FILENUM FILENAME STATUS
------- ------------------------------------ --------------
2 C:\ORANT\DATABASE\USR1ORCL.ORA IN RECOVERY
1 row selected.
SVRMGR> ALTER DATABASE RECOVER
> AUTOMATIC LOGFILE 'C:\ORANT\RDBMS80\ARC480.1';
Statement processed.
SVRMGR> ALTER DATABASE OPEN;
Statement processed.
SVRMGR>
To see the command lines and their results for the whole process, click the View SlideShow link. The next lesson concludes this module.