Syntax to get REDO LOG group details.

 

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#;