While we start a restore and recovery process for database or datafiles, next step is to monitor the progress of restore and recovery process. This is particularly important when end user or customer is keen to learn the approximate completion time of recovery when a critical system is down because of database/datafile corruption. In this article, I would explain how we monitor the recovery of database, and approximate time of completion for the recovery process.
I will discuss a real time scenario that I recorded when I was performing a recovery of a datafile that needed hundreds of archived redo logs to be restored form the backup and applying them during recovery.|
RMAN> RECOVER DATAFILE 207; |
After recovery had started, I queried different views to monitor the progress. I will discuss each of them one by one.
Querying these views is worthwhile if you are
recovering datafiles or entire database.
|
SQL> select last_time,checkpoint_change#,last_change# from v$datafile where file#=207; LAST_TIME
CHECKPOINT_CHANGE# LAST_CHANGE# |
Above query shows that my
datafile 207 has last change number 4871323936 when it was available for
writing. Checkpoint has not moved forward because file is no longer available
(OFFLINE).
|
SQL> select distinct first_time,inst_id,thread#,sequence# from gv$archived_log where first_change#<=4871323936 and next_change#>=4871323936 order by thread# FIRST_TIME
INST_ID THREAD# SEQUENCE# |
Above query shows that until what (archived) redo log sequence number this datafile needs to be recovered. We simply tried to find the sequence numbers based on the last change number of the datafile.
|
select recover,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile_header where file#=207; REC CHECKPOINT_CHANGE# CHECKPOINT_TIME |
After the start of recovery, above query output shows
the current recovery progress of the datafile. This shows that datafiles has
been recovered until change number and time returned by this query. Based on
the output, if we query V$ARCHIVED_LOG, we can see until what sequence number
the archived logs have been applied. This will also help us to know how many
more archived logs application is left.
Based on the
checkpoint_change# value returned bu the above query, if we query
V$ARCHIVED_LOG, we can see until what sequence number the archived logs have
been applied. This will also help us to know how many more archived logs
application is left. See below output.
|
select distinct first_time,inst_id,thread#,sequence#
from gv$archived_log where first_change#<=4870743988 and
next_change#>=4870743988 order by thread#; FIRST_TIME
INST_ID THREAD#
SEQUENCE# |
V$RMAN_STATUS
This view can be queried
to check the status of RMAN jobs currently running. You can explore other columns
of the view as well. This view can also be used to monitor backup jobs
progress. This view returned me SID of the session currently doing the
recovery/restore. In case of multiple channels, you may find multiple SIDs
(sessions) doing the recovery.
|
select sid,recid,start_time,end_time,status,operation,mbytes_processed/1024 GB_PROCESSED,input_bytes/1024/1024/1024 INPUT_GB,output_bytes/1024/1024/1024 OUTPUT_GB,object_type from v$rman_status where operation like '%R%' and status='RUNNING' order by 1; SID RECID START_TIME END_TIME STATUS OPERATION GB_PROCESSED INPUT_GB
OUTPUT_GB OBJECT_TYPE |
V$SESSION_LONGOPS
This view is very handy
in monitoring the long running processes. First we can find oud the SID of the
sessions currently connected with RMAN, and then query v$sesison_longops to
find out the restore/recovery process.
|
SQL> select message,sofar,totalwork,time_remaining from v$session_longops where sid=508; MESSAGE
SOFAR TOTALWORK TIME_REMAINING |
V$RMAN_OUTPUT
This view shows us the
output of the rman job that we are currently monitoring. Output is same as you
see on your command line where you have initiated the command. Use SID and RECID
columns returned by V$RMAN_STATUS query to filter the output to see outputs of
the command you have executed.
|
SQL> select output from v$rman_output where
sid=508 and session_recid=4552 order by recid ; archived log thread=1 sequence=43918 select output from v$rman_output where sid=508 and
session_recid=4552 order by recid desc; archived log for thread 1 with sequence 43755 is
already on disk as file
/u03/MTU01MYMP/archivelog/2018_05_03/o1_mf_1_43755__152 starting media recovery channel ORA_DISK_1: SID=491 instance=MTU01MYM1
device type=DISK |
No comments:
Post a Comment