Query REDO LOG file name and status

Published by Patrizia Gardis on 6 March 20196 March 2019

In the article “Query REDO LOG file name and status,” we can see the names of the files composing the groups, their sizes, and the status of Oracle redo logs.

Redo log files, essential for data recovery in case of corruptions, are sometimes not sized appropriately for the volume of data that the Oracle database handles daily, resulting in frequent switches between redo logs. This can impact the database, necessitating an increase in size.

If we need to increase the size of the redo logs, we will use the helpful query on the Redo Log page, which selects the following fields:

GROUP# – Group to which the redo log belongs.
MEMBER – Name of the redo log.
STATUS – Status of the redo log.
Size MB – Size of the Oracle redo logs in MB.

To extract this information from the Oracle database, we have related two views:

V$LOGFILE V$LOG

In the WHERE condition, we have related the two views V$LOGFILE and V$LOG through the join lg.group# = lf.group#.

If the article was helpful to you, please like and share it with your colleagues.

Query REDO LOG file name and status:

set lines 200
set pages 99
col member format a60 
col status for a20
col "Size MB" format 9,999,999 
select lf.group#, lf.member ,lg.status, ceil(lg.bytes / 1024 / 1024) "Size MB" 
from v$logfile lf , v$log lg 
where lg.group# = lf.group# order by 1;

;

• Query REDO LOG Switch Count
• Query REDO LOG file name and status

To view my guides on CRS Commands, click here!
At this LINK, instead, you’ll find the SQL queries.

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin