Redo Log File Status (vlog.sql)

What This Script Does

This script provides comprehensive status information for both primary redo logs and standby logs, showing group numbers, sizes, member counts, archive status, and timing information. Essential for monitoring redo log configuration and identifying potential issues.

The Script

rem  vlog.sql
rem
ttitle 'Redo Log File Status'
rem
set linesize 133
set pagesize 100
col site format a10 heading 'Log Type'
col group# format 99 heading 'GROUP'
col thread# format 99 heading 'THREAD'
col sequence# format 9999999 heading 'SEQ'
col mbytes format 99999999 heading 'SIZE|(MB)'
col members format 9 heading 'M'
col archived format a1 heading 'A'
col status format a16 heading 'STATUS'
col first_change# format 999999999999999 heading 'FIRST CHANGE'
col first_time format a17 heading 'FIRST TIME'
rem
break on report
compute sum of kbytes on report
rem
select 'Primary' site
     , thread#
     , group#
     , sequence#
     , bytes/1024/1024 mbytes
     , members
     , decode( archived, 'YES', 'Y', 'NO', 'N', '?' ) archived
     , status
     , first_change#
     , to_char( first_time, 'mm/dd/yy hh24:mi:ss' ) first_time
  from v$log
union all
select 'Standby' site
     , thread#
     , group#
     , sequence#
     , bytes/1024/1024 mbytes
     , 0 members
     , decode( archived, 'YES', 'Y', 'NO', 'N', '?' ) archived
     , status
     , first_change#
     , to_char( first_time, 'mm/dd/yy hh24:mi:ss' ) first_time
from v$standby_log
;

Parameters

The script prompts for:

None - this script requires no parameters

Usage

-- Basic usage
@vlog.sql

Sample Output

                               Redo Log File Status

Log Type   THREAD  GROUP     SEQ      SIZE M A STATUS           FIRST CHANGE      FIRST TIME
---------- ------ ----- ------- --------- - - ---------------- ----------------- -----------------
Primary         1     1     154        50 2 Y INACTIVE         123456789012345   01/05/25 14:20:15
Primary         1     2     155        50 2 Y INACTIVE         123456789012890   01/05/25 14:25:30
Primary         1     3     156        50 2 N CURRENT          123456789013445   01/05/25 14:30:45
Primary         2     4     152        50 2 Y INACTIVE         123456789011234   01/05/25 14:18:22
Primary         2     5     153        50 2 Y INACTIVE         123456789011789   01/05/25 14:23:37
Primary         2     6     154        50 2 N CURRENT          123456789012334   01/05/25 14:28:52

Standby         1    11     154        50 0 Y UNASSIGNED       123456789012345   01/05/25 14:20:15
Standby         1    12     155        50 0 Y UNASSIGNED       123456789012890   01/05/25 14:25:30
Standby         1    13       0        50 0 N UNASSIGNED                     0
Standby         2    14     152        50 0 Y UNASSIGNED       123456789011234   01/05/25 14:18:22
Standby         2    15     153        50 0 Y UNASSIGNED       123456789011789   01/05/25 14:23:37
Standby         2    16       0        50 0 N UNASSIGNED                     0