3 Replies Latest reply on May 26, 2010 8:47 PM by Dr. Paranoid-Oracle

    Data Guard appear to be OK but cannot open standby

    626620
      I configured data Guard physical standby on Red hat with Oracle 11.2. I followed the doc
      http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10g_RACPrimarySingleInstancePhysicalStandby.pdf
      . Things went smoothly and after the duplication completed and created satndby rego log on the satndby I ran
      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
      Then tried to open standby database, but got error
      SQL> alter database open;
      alter database open
      *
      ERROR at line 1:
      ORA-10456: cannot open standby database; media recovery session may be in progress
      I checked the gollowing views for DG status
      SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log
       group by thread# order by 1;
        2
         THREAD# Last Primary Seq Generated
      ---------- --------------------------
               1                       1372
               2                        803
               3                        788
      The same result in primary and standby.
      SQL> select recid,SEQUENCE#,blocks,APPLIED,status,first_chg_time,completed from (
        2  SELECT recid,SEQUENCE#,name log_name,blocks,APPLIED,status,
        3   to_char(first_time,'mmdd-hh24miss') first_chg_time,
        4   to_char(completion_time,'mmdd-hh24miss') completed
        5   FROM V$ARCHIVED_LOG where first_time< sysdate+1 ORDER BY SEQUENCE# desc,1
        6  ) where rownum<16;
      
           RECID  SEQUENCE#     BLOCKS APPLIED   S FIRST_CHG_T COMPLETED
      ---------- ---------- ---------- --------- - ----------- -----------
               2       1372       5264 YES       A 0517-033143 0517-034827
               1       1371       1382 YES       A 0517-032616 0517-034827
               8       1370      19913 YES       A 0517-024906 0517-035620
               7       1369       3264 YES       A 0517-023805 0517-035619
               4        803       1680 NO        A 0517-033144 0517-034828
               6        802        686 YES       A 0517-032617 0517-034924
              10        801       7096 YES       A 0517-024906 0517-035621
               9        800       1080 YES       A 0517-023806 0517-035620
               3        788       1565 NO        A 0517-033144 0517-034828
               5        787        488 YES       A 0517-032616 0517-034921
              12        786       6644 YES       A 0517-024906 0517-035622
              11        785       1486 YES       A 0517-023806 0517-035622
      Note that sequence# 1372 has been applied but 803 and 788 are not
      select facility,severity,message_num msg_num,timestamp,message from v$dataguard_status order by TIMESTAMP desc
      ) where rownum<20;
        2    3
      FACILITY                 SEVERITY         MSG_NUM TIMESTAMP     MESSAGE
      ------------------------ ------------- ---------- ------------- -------------------------------------------------------
      Log Apply Services       Warning               74 0517 03:56:26 Media Recovery Waiting for thread 1 sequence 1373 (in t
                                                                      ransit)
      
      Log Apply Services       Informational         68 0517 03:56:25 Media Recovery Log /u01/flash/flashback_recovery_area/C
                                                                      CHDR/archivelog/2010_05_17/o1_mf_1_1371_5z20ptt7_.arc
      Here sequence 1373 is in transfer. But 1372 is not even exist in primary according to result of query 1. I waited for 20 minutes is still not done. I cannot open database.

      What else can I do to open the database?