OracleRedo & archiving

Online redo log groups and members

Every redo log group with its thread, sequence, archived flag, status, member file and size. More than one CURRENT per thread, or members missing from a group, need looking at.

Tested on 12c, 19c · verified Sep 2026

1-- Show Redo Logs info2CLEAR COLUMNS3SET PAGESIZE 100 TRIMOUT ON TAB OFF4set linesize 3005column REDOLOG_FILE_NAME format a506column SIZE_GB format 990.997SELECT8    a.GROUP#,9    a.THREAD#,10    a.SEQUENCE#,11    a.ARCHIVED,12    a.STATUS,13    b.MEMBER    AS REDOLOG_FILE_NAME,14    (a.BYTES/1024/1024/1024) AS SIZE_GB15FROM v$log a16JOIN v$logfile b ON a.Group#=b.Group#17ORDER BY a.GROUP# ASC;

Save it as ora-redo-logs.sql and run it with SQL> @ora-redo-logs.

Open in denrepoRelated tool: Redo log sizing

More Oracle scripts: Redo & archiving

• Log switches per hour, last 7 daysA day-by-hour grid of log switches. Aim for roughly four an hour at peak; consistently more means the redo logs are too small.
• Archived redo generated per dayCount and size of archived logs for the last 14 days, from the local destination only so standby copies aren't double-counted. Useful for sizing the…
• Redo generated per hourActual redo volume per hour for the last 7 days, from archived log sizes on the local destination, so early log switches and Data Guard copies don't…
• Sessions generating the most redoConnected sessions ranked by redo generated since logon. Finds the job behind a sudden flood of archive logs.