4 Replies Latest reply: Oct 6, 2012 8:26 AM by sanikv RSS

    Standby sync check - Manual syncing

    954573
      Hello,

      I would like to re-confirm what DB views do we need to really check and make sure whether my primary & standby are in sync in a manual standby mode.
      Should it be v$log and v$log_history or v$archived_log.

      My doubt arises with one of my client current setup.Logs are manually shipped,and later manually recovered i.e not manged recovered process.
      Today i did a manual healthcheck and i see there is no rows populated in v$archived_log with APPLIED='YES' on standby.But v$log_history showed me the current log sequence which is the same in primary too.From standby alert log i confirmed the sequence number is same as the value shown in v$log_history.
      Oracle manual says,v$archived_log & v$log_history gets the related info from controlfile.
      So why didnt v$archived_log didnt populate the same data as v$log_history.Sometimes i see a value far less in v$archvied_log when compared to v$log_history.

      Below is my script which does manual recovery .
      The archives are just scp'ed and done a manual recover using sql's.

      This is the script part.

      #---------------------------------------
      recover_auto ()
      #---------------------------------------
      {
      echo "\n`date`... Recovering database $ORACLE_SID." >>$LOGFILE
      sqlplus -s "/ as sysdba" <<EOF >$TMPFILE
      recover standby database ;
      auto
      exit
      EOF

      Here is my output.

      @primary:
      SQL> select THREAD#,max(SEQUENCE#) ,ARCHIVED,APPLIED from V$ARCHIVED_LOG group by THREAD#,ARCHIVED,APPLIED
      2 /

      THREAD# MAX(SEQUENCE#) ARC APP
      ---------- -------------- --- ---
      1 75883 YES NO

      @standby
      SQL> select count(*) from V$ARCHIVED_LOG
      2 /

      COUNT(*)
      ----------
      0
      SQL> select max(SEQUENCE#) from v$log_history
      2 /

      MAX(SEQUENCE#)
      --------------
      75883

      Does the view v$archived_log & v$log_hisotry behave differently after an restart of the DR(manaul shipping & applying methods) ?

      Regards
      Sreeraj
        • 1. Re: Standby sync check - Manual syncing
          mseberg
          Hello;

          Run this from the Primary side :

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

          Best Regards

          mseberg

          h2. Sorry

          I thought you had a Data Guard question. rsync is NOT part of Data Guard.

          Edited by: mseberg on Oct 3, 2012 3:08 PM
          • 2. Re: Standby sync check - Manual syncing
            954573
            Hello,

            As i have already mentioned the archives are manually shipped using rsync command.There is no a remote location defined on primary.We simply pull the archives from primary and put it into a location on remote. Later recovery script is run which will recover using these archives.
            So your query does not provide enough data.
            Even though i do a manual recovery on standby, those details on sequence should be there in the standby controlfile. Am i right.But if you check my previous note, count from v$archved_log shows zero.

            This is were am stuck.

            Thanks,

            sreeraj
            • 3. Re: Standby sync check - Manual syncing
              saurabh
              The view v$archived_log and v$log_history views are same they give you output from the controlfile. The difference is that v$archived_log will give give you output only if you are in automatic recover mode. This view is useful only if the registrar column in v$archived_log is RFS. Which you will get if your standby is in automatic recovery mode.

              In your case you are doing manual recover so that is why it is not giving you any output.
              • 4. Re: Standby sync check - Manual syncing
                sanikv
                you can compare the SCN's , they can be converted to timestamps and then used to check how far is your standby lagging behind.