4 Replies Latest reply on Mar 25, 2014 5:37 PM by BPeaslandDBA

    REQUIRED value in open_resetlogs  columm at v$database

    592815

      Friends,

      We restart   physical DB over 3 days that connected to primary DB..

       

      I saw REQUIRED value in open_resetlogs in v$database.

       

      What means is for REQUIRED?

      Right now ,physical standby database  is in active and can be access by  report users.

      Do we need to run alter database open resetlogs;?

      It seems that we need to recover physical standby DB from primary DB

      Thanks

      newdba

        • 1. Re: REQUIRED value in open_resetlogs  columm at v$database
          BPeaslandDBA

          This is expected in a physical standby database. It is that way in mine too:

           

          SQL> select open_resetlogs from v$database;

           

          OPEN_RESETL

          -----------

          REQUIRED

           

          The reason is that your physical standby database is undergoing constant recovery. At this point, any recovery performed is "incomplete" recovery. As such, the only way to open the physical standby is to open with the RESETLOGS option. If you tried to open right now, RESETLOGS is REQUIRED as this column says.

           

          If you perform a switchover operation, then primary writes an End-Of-Redo marker in the redo stream. When the EOR marker is sent to the standby and applied to that standby, Oracle will know that the standby has performed complete recovery and the database can be opened without RESETLOGS. This EOR, which is sent to ensure that all transactions have completed and the database is in a consistent state after being applied to the standby, is what allows one to switch to the standby and then switch back to the original primary.

           

          If you perform a failover operation, no EOR marker is present. The standby is opened with RESETLOGS, and you can't switch back. You would have to recreate the primary as a new standby to what is now the primary (the original standby).

           

           

          HTH,

          Brian

          • 2. Re: REQUIRED value in open_resetlogs  columm at v$database
            592815

            Brian,

            Thanks for your help. But I do not  see data replication. below is our case.The primary and standby DB role does not be changed.

            In  dataguard (primary and physical standby DB), Physical standby host and DB down over 3 days. we startup database and restarted apply server

            alter database recover managed standby database cancel;

            alter database recover managed standby database using current logfile disconnect;

             

            But i did not see data replication processing.

            i take below reviewing in physical standby DB.

            1)

            select *FROM V$ARCHIVE_GAP;

            no any valu return

            2)

            SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS  WHERE name like 'apply lag';

            shoNAME VALUE DATUM_TIME TIME_COMPUTED

            --------- ------------- ------------------- -------------------

            apply lag               03/25/2014 08:54:17

             

             

            3) history lag value

            SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

            no value

            4) SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

             

             

            PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

            --------- ------------ ---------- ---------- ---------- ----------

            ARCH      CLOSING               1      25897     120832       1301

            ARCH      CLOSING               1      25898     100352        710

            ARCH      CONNECTED          0          0          0          0

            ARCH      CLOSING               1      25896     350208       1353

            RFSRFS    IDLE                     0          0          0          0

            MRP0      WAIT_FOR_GAP    1          1          0          0

            RFSRFS    IDLE                     0          0          0          0

             

            7 rows selected.

             

             

            under this situation, what do we need to do?

            Thanks

             

            new dba

            Sorry . i think i need to open new instance.

            • 3. Re: REQUIRED value in open_resetlogs  columm at v$database
              mseberg

              Hello;

               

              I have never trusted V$ARCHIVE_GAP

               

              Try this query:

               

              http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

               

              Best Regards

               

              mseberg

              • 4. Re: REQUIRED value in open_resetlogs  columm at v$database
                BPeaslandDBA

                Like mseberg, I've often seen V$ARCHIVE_GAP return zero rows when I know for certain that there is an archive gap. This can be seen in the Alert Log. When I do have a gap and there are a lot of archived redo logs missing, I will manually transfer the files from the primary to the standby and register the logfile with the standby. This is often faster than letting FAL get the missing log.

                 

                The apply lag is almost 9 hours behind. Assuming you do not have an apply delay, once you get those missing archive logs to the standby, you should see the apply delay going down. This may take awhile to catch up.

                 

                I would start by looking at my archive log destination on the standby. If I see gaps after visual inspection, then I try to manually copy them and register as I stated earlier.

                 

                HTH,

                Brian