HOW TO FIND REDO LOG MEMBERS, REDO LOG FILE SIZE, AND REDO LOG FILE STATUS

Fetching redo log details by querying data dictionary views

DEMO

DEMO: MANAGING THE REDO LOG GROUPS AND MEMBERS

QUERY

You can find the redo log details from data dictionary views as shown below:

SET LINES 200 
SET PAGES 999
COL LGWR FOR A5 
COL THREAD FOR 99999
COL GROUP FOR 9999
COL STATUS FOR A8
COL ARCHIVED FOR A8 
COL LOGFILE_TYPE FOR A12 
COL MEMBER FOR A10
COL FILE_NAME FOR A50
COL MB FOR 9999
COL IS_RECOVERY_DEST_FILE FOR A22

SELECT DECODE(LG.STATUS,'CURRENT', THREAD# || ' -->',NULL) AS LGWR,
       LG.THREAD# "THREAD",
       LG.GROUP# "GROUP",
       LF.TYPE LOGFILE_TYPE,
       LG.STATUS,
       LG.SEQUENCE# AS LOG_SEQUENCE,
       LG.ARCHIVED,
       LG.MEMBERS AS GROUP_MEMBER,
       ROUND(LG.BYTES/1024/1024) MB,
       LF.MEMBER FILE_NAME,
       LF.IS_RECOVERY_DEST_FILE
FROM
       V$LOG LG,
       V$LOGFILE LF
WHERE
       LG.GROUP#=LF.GROUP#
ORDER BY LG.GROUP#;

EXAMPLE

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                    IS_RECOVERY_DEST_FILE
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------- ----------------------
1 -->      1     1 ONLINE       CURRENT            19 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo01.log    NO
           1     2 ONLINE       INACTIVE           17 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo02.log    NO
           1     3 ONLINE       INACTIVE           18 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo03.log    NO