This discussion is archived
13 Replies Latest reply: Oct 19, 2012 2:48 AM by saurabh RSS

V$ARCHIVED_LOG returns no rows on manual standby

muli033 Newbie
Currently Being Moderated
Hi all,

using Oracle 11gR2 on Solaris 10 I created a standby database. Everything seems to work fine. Changes to data in the primary database appear in the standby database after archived redo logs are applied. When I query V$ARCHIVED_LOG on standby database I always get "no rows selected".

Does this view only fill up when using Data Guard with Oracle Enterprise Edition?

Regards,
Muli
  • 1. Re: V$ARCHIVED_LOG returns no rows on manual standby
    mseberg Guru
    Currently Being Moderated
    Hello;

    Try these queries on your primary :
    clear screen
    set linesize 100
     
    column STANDBY format a20
    column applied format a10
    
    SELECT  
      NAME AS STANDBY, SEQUENCE#, APPLIED, COMPLETION_TIME 
    FROM 
      V$ARCHIVED_LOG 
    WHERE  
      DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;
    
    --
    
    SELECT 
      MAX(SEQUENCE#) 
    FROM 
      V$ARCHIVED_LOG 
    WHERE 
      NEXT_TIME > SYSDATE -1;
    Does this view only fill up when using Data Guard with Oracle Enterprise Edition?
    No, idea since that's all I use.

    Best Regards

    mseberg
  • 2. Re: V$ARCHIVED_LOG returns no rows on manual standby
    muli033 Newbie
    Currently Being Moderated
    Unfortunately, this does not work. Following is the query and the result
    select * from v$archived_log;
    
    no rows selected
    Any ideas?

    Regards,
    Muli
  • 3. Re: V$ARCHIVED_LOG returns no rows on manual standby
    mseberg Guru
    Currently Being Moderated
    Hello;

    If I run your query on a database without Data Guard I get this :
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from v$archived_log;
    
    no rows selected
    
    SQL> 
    That database is not in ARCHIVE mode.

    If I run it on a NON-Data Guard database in archive mode I get results.

    What edition of Oracle are you using?

    Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2) E10700-02

    "Data Guard is a feature of Oracle Database Enterprise Edition"



    Best Regards

    mseberg
  • 4. Re: V$ARCHIVED_LOG returns no rows on manual standby
    muli033 Newbie
    Currently Being Moderated
    Both databases are in archivelog mode. On primary database I also get the expected results:
    SQL> select sequence#, applied from v$archived_log;
    
     SEQUENCE# APPLIED
    ---------- ---------
             4 NO
             5 NO
             6 NO
             7 NO
             8 NO
             9 NO
            10 NO
            11 NO
            12 NO
            13 NO
            14 NO
    
     SEQUENCE# APPLIED
    ---------- ---------
            15 NO
            16 NO
            17 NO
            18 NO
            19 NO
            20 NO
            21 NO
            22 NO
            23 NO
            24 NO
            25 NO
    
     SEQUENCE# APPLIED
    ---------- ---------
            26 NO
            27 NO
            28 NO
    
    25 rows selected.
    On standby database I mount the database with following statement:
    $ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 6 15:16:33 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup nomount;
    ORACLE instance started.
    
    Total System Global Area 1436819456 bytes
    Fixed Size                  2158344 bytes
    Variable Size             855642360 bytes
    Database Buffers          570425344 bytes
    Redo Buffers                8593408 bytes
    SQL> alter database mount standby database;
    
    Database altered.
    After mounting the database, I start recovery until the current available archivelog file
    SQL> recover standby database;
    ORA-00279: change 1033046 generated at 10/06/2011 15:08:15 needed for thread 1
    ORA-00289: suggestion :
    /oracle/admin/teststby/archive/teststby/1_24_763807645.dbf
    ORA-00280: change 1033046 for thread 1 is in sequence #24
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    ORA-00279: change 1033191 generated at 10/06/2011 15:12:27 needed for thread 1
    ORA-00289: suggestion :
    /oracle/admin/teststby/archive/teststby/1_25_763807645.dbf
    ORA-00280: change 1033191 for thread 1 is in sequence #25
    ORA-00278: log file
    '/oracle/admin/teststby/archive/teststby/1_24_763807645.dbf' no longer needed
    for this recovery
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    ORA-00279: change 1033195 generated at 10/06/2011 15:12:30 needed for thread 1
    ORA-00289: suggestion :
    /oracle/admin/teststby/archive/teststby/1_26_763807645.dbf
    ORA-00280: change 1033195 for thread 1 is in sequence #26
    ORA-00278: log file
    '/oracle/admin/teststby/archive/teststby/1_25_763807645.dbf' no longer needed
    for this recovery
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    ORA-00279: change 1033199 generated at 10/06/2011 15:12:33 needed for thread 1
    ORA-00289: suggestion :
    /oracle/admin/teststby/archive/teststby/1_27_763807645.dbf
    ORA-00280: change 1033199 for thread 1 is in sequence #27
    ORA-00278: log file
    '/oracle/admin/teststby/archive/teststby/1_26_763807645.dbf' no longer needed
    for this recovery
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    ORA-00279: change 1033202 generated at 10/06/2011 15:12:34 needed for thread 1
    ORA-00289: suggestion :
    /oracle/admin/teststby/archive/teststby/1_28_763807645.dbf
    ORA-00280: change 1033202 for thread 1 is in sequence #28
    ORA-00278: log file
    '/oracle/admin/teststby/archive/teststby/1_27_763807645.dbf' no longer needed
    for this recovery
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    ORA-00279: change 1033205 generated at 10/06/2011 15:12:35 needed for thread 1
    ORA-00289: suggestion :
    /oracle/admin/teststby/archive/teststby/1_29_763807645.dbf
    ORA-00280: change 1033205 for thread 1 is in sequence #29
    ORA-00278: log file
    '/oracle/admin/teststby/archive/teststby/1_28_763807645.dbf' no longer needed
    for this recovery
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    ORA-00308: cannot open archived log
    '/oracle/admin/teststby/archive/teststby/1_29_763807645.dbf'
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    I now would expect some results when issuing the following query:
    SQL> select count(*) from v$archived_log;
    
      COUNT(*)
    ----------
             0
    But as you can see, there aren't any.

    Current DB-version is 11.2.0.2.0

    OS is SunOS 5.10 on SPARC-System

    Creating a standby database with data guard, broker and features like "real time apply" is certainly a EE-feature. With SE you also can create standby databases, but you'll have to take care of achivelog apply by yourself.

    Regards,
    Muli
  • 5. Re: V$ARCHIVED_LOG returns no rows on manual standby
    mseberg Guru
    Currently Being Moderated
    Confirmed V$ARCHIVED_LOG does not work with a manual standby.

    Looking for alternative :

    Looks like the standby alert log is the best source of information.

    You might be able to build a script that watches it for you :

    Ex.

    http://oraclehack.blogspot.com/search/label/Alert%20log

    Sorry about the detour, I'm not a morning person. Later in the day I would caught the Manual Standby on the first post.

    Best Regards

    mseberg

    Edited by: mseberg on Oct 6, 2011 9:45 AM
  • 6. Re: V$ARCHIVED_LOG returns no rows on manual standby
    muli033 Newbie
    Currently Being Moderated
    Anyway thanks for your help! Guess I've to to some scripting tomorrow :-)

    Regards,
    Muli
  • 7. Re: V$ARCHIVED_LOG returns no rows on manual standby
    CKPT Guru
    Currently Being Moderated
    Can you create standby controlfile from primary and restore on standby. then start MRP/manual recovery.

    even in standard edition also v$archived_log will works.
  • 8. Re: V$ARCHIVED_LOG returns no rows on manual standby
    779345 Explorer
    Currently Being Moderated
    Hi,

    what are you trying to achieve with selecting v$archived_log from the standby server?

    If you wish to get information about the process of applying the archive logs just ask v$controlfile and get the scn from there. You can compare it to the primary and select the missing archive logs in a regurlar manner.
    So you are able to identify the archive logs which are not present on the standby side and transfer and apply only those.

    I checked one of my standard standby sides, i do have entries in v$archived_log.
    select max(next_time), min(next_time), count(*)
    from v$archived_log;
    
    MAX(NEXT MIN(NEXT   COUNT(*)
    -------- -------- ----------
    08.10.11 29.09.11        560
    
    select archiver, instance_role from v$instance;
    
    ARCHIVE INSTANCE_ROLE
    ------- ------------------
    STARTED PRIMARY_INSTANCE
    Maybe this is the reason for the entries in v$archived_log?

    First, my database is not knowing the it is a standby one and second, the standby database is also in archive mode.

    Just a wild guess.

    Joerg
  • 9. Re: V$ARCHIVED_LOG returns no rows on manual standby
    Talip Hakan Ozturk Oracle ACE
    Currently Being Moderated
    you can query log apply status with this script.

    Db name, host name, log archived, log applied, apply time and log gap


    SELECT DB_NAME , HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAP
    FROM
    (
    SELECT NAME DB_NAME FROM V$DATABASE
    ),
    (
    SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM V$INSTANCE
    ),
    (
    SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
    ),
    (
    SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
    ),
    (
    SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
    FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
    );
  • 10. Re: V$ARCHIVED_LOG returns no rows on manual standby
    Talip Hakan Ozturk Oracle ACE
    Currently Being Moderated
    You can also query this select on standby side for getting time.


    select * from v$recovery_log;
  • 11. Re: V$ARCHIVED_LOG returns no rows on manual standby
    user13006016 Newbie
    Currently Being Moderated
    Hi Muli033

    I would be very grateful if you could post your script or information how you did your setup of the manual standby database. Somehow my Standby database in SE is having trouble recognizing the archived logs.

    Regards
  • 12. Re: V$ARCHIVED_LOG returns no rows on manual standby
    733071 Newbie
    Currently Being Moderated
    I am running into the same issue. No rows in v$archivelog in the newly restored standby datbase. Same OS. Any update to this issue?
  • 13. Re: V$ARCHIVED_LOG returns no rows on manual standby
    saurabh Pro
    Currently Being Moderated
    if you want to find the archived applied on the standby by then you can use v$log_history which will give the output from the control file.

    following query might help you.

    seelct max(sequence#) from v$log_history;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points