Oracle Redo Log Files Report

This script displays detailed information about Oracle redo log files. It joins V$LOGFILE and V$LOG to show log group, member, size, status, archive status, sequence numbers, and change numbers, helping DBAs monitor redo log configuration and recovery readiness.

oraclesqlbackup-restorev1.0.0

0 stars0 downloads18 views0 comments

By OracleDba • Created 2/23/2026

Code

(30 lines)

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/logfiles.sql
-- Author       : Tim Hall
-- Description  : Displays information about redo log files.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @logfiles
-- Last Modified: 21/12/2004
-- -----------------------------------------------------------------------------------

SET LINESIZE 200
COLUMN member FORMAT A50
COLUMN first_change# FORMAT 99999999999999999999
COLUMN next_change# FORMAT 99999999999999999999

SELECT l.thread#,
       lf.group#,
       lf.member,
       TRUNC(l.bytes/1024/1024) AS size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file AS rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
FROM   v$logfile lf
       JOIN v$log l ON l.group# = lf.group#
ORDER BY l.thread#,lf.group#, lf.member;

SET LINESIZE 80

Oracle Redo Log Files Report

Code

General Comments(0)