How full is the current redo log file?

Here is a query that can tell us how full the current redo log file is. This is useful  when we  need to predict when the next log file will be archived out.

[oracle@primaryDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 14 10:57:58 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT le.leseq                      "Current log sequence No",
          100*cp.cpodr_bno/le.lesiz         "Percent Full",
           cp.cpodr_bno                            "Current Block No",
           le.lesiz                                       "Size of Log in Blocks"
           FROM   x$kcccp  cp,    x$kccle  le
           WHERE    le.leseq =CP.cpodr_seq
           AND  bitand(le.leflg,24) = 8 ;  2    3    4    5    6    7

Current log sequence No   Percent Full      Current Block       No Size of Log in Blocks
-----------------------           --------------------    ----------------           ---------------------
                     24                   34.5537109            35383                102400


Note :  Very useful query especially when archive_lag_target is set to 0 on the source database

Comments

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

Register Archive log file manually in standby Database

How to check UNDO tablespace usage and who is using more undo