How to Get Oracle Redo Log Files, Members, Size, Name and Status - Query

To retrieve redo log information from an Oracle database, you can use the following SQL*Plus script. This script queries the V$LOG view to provide details about the redo log files in the database. Redo logs are a critical component of Oracle databases. They record all changes made to the database, allowing for recovery and transaction consistency. The redo log files are divided into groups, and each group contains one or more members (physical files). The V$LOG view provides information about redo log groups in the database, including their sequence numbers, sizes, members, statuses, and other details. Please note that querying the V$LOG view requires appropriate privileges. Test the script in a controlled environment before running it in a production database.

SET LINESIZE 200
SET PAGESIZE 100
SET VERIFY OFF

COLUMN GROUP# FORMAT 999
COLUMN THREAD# FORMAT 999
COLUMN SEQUENCE# FORMAT 999999
COLUMN BYTES FORMAT 9999999999
COLUMN MEMBERS FORMAT 999
COLUMN STATUS FORMAT A10
COLUMN FIRST_CHANGE# FORMAT 9999999999
COLUMN NEXT_CHANGE# FORMAT 9999999999

SELECT
  GROUP#,
  THREAD#,
  SEQUENCE#,
  BYTES,
  MEMBERS,
  STATUS,
  FIRST_CHANGE#,
  NEXT_CHANGE#
FROM
  V$LOG
ORDER BY
  GROUP#;

advertisements

 

Sample Output

GROUP# THREAD# SEQUENCE#       BYTES MEMBERS STATUS	FIRST_CHANGE#	  NEXT_CHANGE#
------ ------- --------- ----------- ------- ---------- ------------- ----------------
     1	     1	       7   209715200	   1 CURRENT	      2131763          2018703
     2	     1	       5   209715200	   1 INACTIVE	      2018703	       2097286
     3	     1	       6   209715200	   1 INACTIVE	      2097286	       2131763

SQL Query to get member details, size and status

SET LINESIZE 150
SET PAGESIZE 100
SET VERIFY OFF

COLUMN GROUP# FORMAT 999
COLUMN MEMBER FORMAT A50
COLUMN STATUS FORMAT A10
COLUMN SIZE_GB FORMAT 999999.999

SELECT
  l.GROUP#,
  m.MEMBER,
  l.STATUS,
  ROUND(l.BYTES / 1024 / 1024 / 1024, 3) AS SIZE_GB
FROM
  V$LOG l
JOIN
  V$LOGFILE m
ON
  l.GROUP# = m.GROUP#
ORDER BY
  l.GROUP#;

Sample Output

GROUP# MEMBER						  STATUS	 SIZE_GB
------ -------------------------------------------------- ---------- -----------
     1 /u01/app/oracle/oradata/ORCL/redo01.log		  CURRENT	    .195
     2 /u01/app/oracle/oradata/ORCL/redo02.log		  INACTIVE	    .195
     3 /u01/app/oracle/oradata/ORCL/redo03.log		  INACTIVE	    .195