10 Replies Latest reply: Jan 25, 2013 3:37 PM by mseberg RSS

    Sync of Primary and Standby

    792422
      Hi,

      SQL> select database_role from v$database;

      DATABASE_ROLE
      ----------------
      PRIMARY

      SQL> select applied,status from v$archived_log where applied='NO' and status='A';

      APP S
      --- -
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A

      APP S
      --- -
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A
      NO A

      18 rows selected.

      SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

      no rows selected

      On my standby

      SQL> select database_role from v$database;

      DATABASE_ROLE
      ----------------
      PHYSICAL STANDBY

      SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

      no rows selected


      IS my primary in sync with standby, if "yes", why am I getting the applied status as "NO" from v$archived_log

      Thanks
        • 1. Re: Sync of Primary and Standby
          Mihael
          SQL> select database_role from v$database;

          DATABASE_ROLE
          ----------------
          PRIMARY

          SQL> select applied,status from v$archived_log where applied='NO' and status='A';

          APP S
          --- -
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A

          APP S
          --- -
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A
          NO A

          18 rows selected.
          you should run this query from standby site
          • 2. Re: Sync of Primary and Standby
            mseberg
            Hello;

            Run this script from the Primary :

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

            Best Regards

            mseberg
            • 3. Re: Sync of Primary and Standby
              792422
              here is the o/p

              SQL> select database_role from v$database;

              DATABASE_ROLE
              ----------------
              PHYSICAL STANDBY


              SQL> select applied,status from v$archived_log where applied='NO' and status='A';

              no rows selected

              Edited by: Gani... on Jan 25, 2013 4:31 AM
              • 4. Re: Sync of Primary and Standby
                Mihael
                primary site not always contain correct info about what logs are applied on standby
                • 5. Re: Sync of Primary and Standby
                  Mihael
                  run the following query from both sites :

                  select * from (select THREAD#, SEQUENCE#,STATUS,APPLIED,NAME from v$archived_log order by recid desc) where rownum < 20 ;
                  • 6. Re: Sync of Primary and Standby
                    mseberg
                    Can you explain this or supply an Oracle or MOS note.

                    My problem with this comment is how could switchover status ever be correct if this were true?


                    Best Regards

                    mseberg
                    • 7. Re: Sync of Primary and Standby
                      Mihael
                      Due to network outage, standby maintenance or something else you temporary disable standby dest on primary. Then you manually register some logs on standby. When you enable standby dest, standby will already have logs and will not try to resolve gap from primary. May be this is true only if fal_client is not configured on primary, so primary will not push logs to standby.
                      • 8. Re: Sync of Primary and Standby
                        mseberg
                        Hello;

                        This is not exactly true :
                         When you enable standby dest, standby will already have logs and will not try to resolve gap from primary.
                        I have set the Primary to DEFER over a weekend and when I ENABLE Data Guard catches up without further action.

                        Also fal_client is not even needed in most Oracle 11 Data Guard setups :

                        http://docs.oracle.com/cd/E11882_01/server.112/e25608/whatsnew.htm

                        It would not occur to me to check sync from the Primary when I'm having a network issue. I would probably just wait to resolve the issue and then check sync.

                        That all said I do believe its a good idea to check sync on both the Primary and the Standby when doing a switchover. It can save you a lot of trouble. I have done a query of switchover status on the Primary which returned "TO STANDBY" when the Standby had an issue which would cause a problem with the switchover.

                        Best Regards

                        mseberg
                        • 9. Re: Sync of Primary and Standby
                          Mihael
                          This is not exactly true :
                          When you enable standby dest, standby will already have logs and will not try to resolve gap from primary.
                          I have set the Primary to DEFER over a weekend and when I ENABLE Data Guard catches up without further action.
                          May be, you didn't notice this : you could manually register some logs on standby. What will happen then ?
                          • 10. Re: Sync of Primary and Standby
                            mseberg
                            OK

                            If you run this :
                            select applied,status from v$archived_log where applied='NO' and status='A';  -- Available 
                            On the Primary side you won't get "Applied".

                            One option is to run something like this on the Primary changing SYSDATE and DEST_ID as needed. :
                            SELECT  
                              NAME AS STANDBY, 
                              SEQUENCE#, 
                              APPLIED, 
                              COMPLETION_TIME 
                            FROM 
                              V$ARCHIVED_LOG 
                            WHERE  
                              DEST_ID = 2 
                            AND 
                              NEXT_TIME > SYSDATE -1;
                              
                            You can also add DEST_ID to the SELECT statement and take it out of the WHERE clause to get a better idea how V$ARCHIVED_LOG works.
                            SELECT  
                              NAME AS STANDBY, 
                              SEQUENCE#, 
                              APPLIED, 
                              COMPLETION_TIME,
                              DEST_ID 
                            FROM 
                              V$ARCHIVED_LOG 
                            AND 
                              NEXT_TIME > SYSDATE -1;
                              
                            V$ARCHIVE_GAP has never helped me with gaps. For some reason I have alway gotton the same results as you, "no rows selected" .

                            This query ( again run from the Primary ) covers a lot of ground because it tells you in a single line
                            the last Sequence number, the last applied, the applied time and size of gap ( so zero is good )
                            SET PAGESIZE 140
                            COL DB_NAME FORMAT A10
                            COL HOSTNAME FORMAT A14
                            COL LOG_ARCHIVED FORMAT 999999
                            COL LOG_APPLIED FORMAT 999999
                            COL LOG_GAP FORMAT 9999
                            COL APPLIED_TIME FORMAT A14
                            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'
                            );
                            Try some of these and see if they help you.

                            Best Regards

                            mseberg