When monitoring redo logs, instead of direct views for statistics, we must look at statistics based on redo log and log writer process statistics. These views are:
- V$STATNAME
- V$SESSION
- V$PROCESS
- V$SESSTAT
- V$LATCH
- V$LATCHNAME
Additional Views
In addition to the previous statistical data, The V$LOGFILE view provides information on Redo Log group structure.
This report provides the DBA with the exact location and status for all redo logs and to what groups they belong. The status of "stale" indicates these logs aren't currently in use. No status means the logs are active.
There are also two additional V$ views that deal with the redo logs:
- VLOG_HISTORY contains information about redo logs that have been written to archives
- V$THREAD contains information on redo log thread assignments
The V$LOG_HISTORY view contains information on the lowest and highest SCN value in the log (transaction number), time of archive and the name of the archive file. This information is valuable in determining what archive log a specific change number resides in which can then be used during recovery operations.
The final view for monitoring redo log status is the V$THREAD view. This view contains information about redo log threads such as the instance they are assigned to in a multi (parallel) instance database, checkpoint times and change numbers.
These views can be joined to give virtually any information you desire about the redo logs, their status and any recovery information needed.
Start the discussion at forums.toadworld.com