Redologs information
                                                 Last update (2008-02-29 08:36:35)
                                                                                                                   Date added (2008-02-12 10:42:09)

Summary
In one script all the information about the redologs.
SELECT col1 REDOLOG_INFO FROM
(
SELECT 1 ID, 'Redolog groups : ' || COUNT(*) col1 FROM v$log
UNION
SELECT 2 ID, '-->Group:'||GROUP# || ' Members:'|| members || ' Size :' || BYTES/(1024*1024) || 'MB' ||
DECODE(status,'CURRENT', ' CURRENT', ' ') || ' Archived: ' || ARCHIVED || ' ' col1
FROM v$log
UNION
--Last redolog switch
SELECT 3 ID, 'Last redolog switch before : ' ||
DECODE(TRUNC(SYSDATE - FIRST_TIME), 0, NULL, TRUNC(SYSDATE - FIRST_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-FIRST_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') ||
' (' || TO_CHAR(first_time, 'DD/MM/YYYY HH24:MI:SS') || ')'
col1
FROM v$loghist
WHERE switch_change# = (SELECT MAX(switch_change#) FROM v$loghist)
UNION
--Current log sequence
SELECT 4 ID, 'Current log sequence : ' || SEQUENCE# col1
FROM v$log WHERE GROUP# IN (SELECT GROUP# col1 FROM v$log WHERE status = 'CURRENT')
UNION
--Current log usage
SELECT 5 ID, 'Current log usage :' || SUBSTR(TO_CHAR(100 * cp.cpodr_bno / le.lesiz, '999.00'), 2) || '%' col1
FROM
sys.x$kcccp cp,
sys.x$kccle le
WHERE
le.inst_id = USERENV('Instance') AND
cp.inst_id = USERENV('Instance') AND
le.lesiz <> 0 AND
le.leseq = cp.cpodr_seq
UNION
--Redo log buffer
SELECT 6 ID, 'Redo log buffer : ' || VALUE/(1024*1024) || 'MB' col1
FROM v$parameter WHERE NAME = 'log_buffer'
UNION
--The average log switch interval in seconds
SELECT 7 ID, 'The average log switch interval in sec : ' || ROUND(AVG(b.first_time - a.first_time) * 1440 * 60,2) col1
FROM sys.v_$instance i, sys.v_$log_history a,
( SELECT SEQUENCE#, first_time FROM sys.v_$log WHERE status = 'CURRENT'
UNION ALL
SELECT SEQUENCE#, first_time FROM sys.v_$log_history ) b
WHERE i.startup_time < a.first_time AND a.first_time < b.first_time
AND a.SEQUENCE# + 1 = b.SEQUENCE#
)
ORDER BY ID ASC;
Output explanation
Redolog groups : 3
-->Group:7 Members:2 Size :500MB Archived: YES
-->Group:8 Members:2 Size :500MB Archived: YES
-->Group:9 Members:2 Size :500MB CURRENT Archived: NO
Last redolog switch before : 00:17:03 (12/02/2008 09:52:07)
Current log sequence : 61295
Current log usage : 45.52%
Redo log buffer : 10.00MB
The average log switch interval in sec : 1637.97
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search