This discussion is archived
6 Replies Latest reply: Apr 4, 2013 9:39 PM by user11982706 RSS

Status Column Of v$log on standby database

user11982706 Newbie
Currently Being Moderated
DB Version : 11.2.0.1

OS Version : AIX 7.1

Status column of v$log is showing values as "CLEARING" & "CLEARING_CURRENT". It doesn't change even after adding the standby log files. Here are values from this view. I am not using real time apply feature and have delay of 120 Minutes.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       3397  104857600        512          2 YES CLEARING              22886147 03-APR-13     22887606 03-APR-13
         3          1       3396  104857600        512          2 YES CLEARING              22886111 03-APR-13     22886147 03-APR-13
         2          1       3398  104857600        512          2 YES CLEARING_CURRENT      22887606 03-APR-13     22886111 03-APR-13

SQL> select group#,Thread#, sequence#,USER,archived,status,first_change# from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# USER                           ARC STATUS     FIRST_CHANGE#
---------- ---------- ---------- ------------------------------ --- ---------- -------------
         4          1       3398 SYS                            YES ACTIVE          22887606
         5          1          0 SYS                            NO  UNASSIGNED             0
         6          0          0 SYS                            YES UNASSIGNED             0
         7          0          0 SYS                            YES UNASSIGNED             0
is the status column showing value "CURREN" & "CLEARING_CURRENT" correct, since i have added standby redo logs, i was expecting value to be "INACTIVE" ? reason for expecting this value is because now, standby log files are in use and original log files will be used only in case of switchover or failover. Please comment.

Edited by: user11982706 on Apr 3, 2013 10:31 AM
  • 1. Re: Status Column Of v$log on standby database
    MahirM.Quluzade Guru
    Currently Being Moderated
    Hi,

    Can you check your standby redo log sizes?

    I think your standby redo size is less then primary online redo log size.

    For easy management standby redo log and online redo log size must best same.

    Regards
    Mahir M. Quluzade
    http://www.mahir-quluzade.com
  • 2. Re: Status Column Of v$log on standby database
    user11982706 Newbie
    Currently Being Moderated
    size are same on both primary and standby. See the results from below query:

    Primary
    SQL> select GROUP#,THREAD#,bytes/1024/1024,STATUS from v$log;
    
        GROUP#    THREAD# BYTES/1024/1024 STATUS
    ---------- ---------- --------------- ----------------
             1          1             100 INACTIVE
             2          1             100 INACTIVE
             3          1             100 CURRENT
    Standby
    SQL> select GROUP#,THREAD#,bytes/1024/1024,STATUS from v$log;
    
        GROUP#    THREAD# BYTES/1024/1024 STATUS
    ---------- ---------- --------------- ----------------
             1          1             100 CLEARING
             3          1             100 CLEARING_CURRENT
             2          1             100 CLEARING
    
    
    SQL> select GROUP#,THREAD#,bytes/1024/1024,STATUS from v$standby_log;
    
        GROUP#    THREAD# BYTES/1024/1024 STATUS
    ---------- ---------- --------------- ----------
             4          1             100 ACTIVE
             5          1             100 UNASSIGNED
             6          0             100 UNASSIGNED
             7          0             100 UNASSIGNED
    So, on standby size of online logs as well as standby logs is exactly same as primary and i have one more standby redo then primary as oracle recommends. Please comment....
  • 4. Re: Status Column Of v$log on standby database
    user11982706 Newbie
    Currently Being Moderated
    Thanks for pointing to this link, but this link doesn't has any update on status as "CLEARING" & "CLEARING_CURRENT" for online logs of standby database.....

    My doubt is that why the status is showing status as "CLEARING" & "CLEARING_CURRENT" which according to documentation, indicates that:

    CLEARING - Log is being re-created as an empty log after an ALTER DATABASE
    CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

    http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2029.htm



    Thanks

    Yogesh
    http://orasteps.wordpress.com/
  • 5. Re: Status Column Of v$log on standby database
    user11982706 Newbie
    Currently Being Moderated
    How can i add the points to this thread, if i missed it during initial post, as don't want to start a duplicate thread.... Any suggestions ?
  • 6. Re: Status Column Of v$log on standby database
    user11982706 Newbie
    Currently Being Moderated
    any suggestions on this ?