This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 12, 2012 11:13 AM by 978280 RSS

Standby SCN greater than SCN on Primary

978280 Newbie
Currently Being Moderated
I have a situation where the SCN on the standby database is greater than the SCN on the Primary database. I referred a few threads earlier which said that this cannot be possible, but this is something which I am facing now.

Primary:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select status,instance_name,database_role,current_scn from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    CURRENT_SCN
------------ ---------------- ---------------- -----------
OPEN         KRA1T            PRIMARY            421851938

SQL> select max(sequence#) From v$archived_log;

MAX(SEQUENCE#)
--------------
         31310
Standby:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select status,instance_name,database_role,current_scn from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    CURRENT_SCN
------------ ---------------- ---------------- -----------
OPEN         KRA1S            PHYSICAL STANDBY   421856797

SQL> select max(sequence#) From v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
         31310
The Standby is in sync with the primary and there is no errors reported in the alert logs. Everything looks perfect, but my concern is why is it that the SCN on the standby is greater than the primary. Any help on this would really be appreciated.
  • 1. Re: Standby SCN greater than SCN on Primary
    mseberg Guru
    Currently Being Moderated
    Hello;

    Are you applying in real time?

    Can you run this query and post the results?


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

    Also can you run just this on both and post results?
    select CURRENT_SCN from v$database;
    Best Regards

    mseberg

    Edited by: mseberg on Dec 11, 2012 1:18 PM
  • 2. Re: Standby SCN greater than SCN on Primary
    978280 Newbie
    Currently Being Moderated
    Hi Mseberg,

    We are using Real Time Apply.

    Here is the outcome of the query:
    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    KRA1T      ORACLE-1                31313       30600 02-DEC/18:15       713
    Primary:
    SQL> select current_scn from v$database;
    
    CURRENT_SCN
    -----------
      421870974
    Standby:
    SQL> select current_scn from v$database;
    
    CURRENT_SCN
    -----------
      421871164
  • 3. Re: Standby SCN greater than SCN on Primary
    mseberg Guru
    Currently Being Moderated
    I'm thinking either you rebuilt your standby or you really have the giant gap the query shows.

    Can you provide additional details? alert log?

    If you never rebuilt your standby you have a huge issue. Data Guard is not working. Gap is nine days - 02-DEC/18:15

    Best Regards

    mseberg
  • 4. Re: Standby SCN greater than SCN on Primary
    978280 Newbie
    Currently Being Moderated
    My Bad. I ran the query on the standby database.

    Here is out come of the query when ran on Primary database:
    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    KRA1T      ORACLE-2                31313       31312 11-DEC/11:10         1
  • 5. Re: Standby SCN greater than SCN on Primary
    mseberg Guru
    Currently Being Moderated
    OK

    Thanks.

    Much better.

    In a standby I believe you need to compare the recovery checkpoint SCN not the current_scn.



    I'm having trouble find a support document

    Later

    All I have is this :

    SCN number difference in primary and standby databases


    My gut feeling is it should not be possible. I checked MOS documents and found nothing. I swear I've have seen this issue before.

    I would probably open a Oracle support question.

    The query I had you run looks OK. The gap of 1 is expected with Real Time Apply.

    If you get additional information consider posting it here.






    Best Regards

    mseberg

    Edited by: mseberg on Dec 11, 2012 1:40 PM
  • 6. Re: Standby SCN greater than SCN on Primary
    978280 Newbie
    Currently Being Moderated
    I came across this Support document CURRENT_SCN from v$database not showing the correct value in physical standby DB [ID 811434.1]

    The doc says that from 11g, the value for CURRENT_SCN shows correctly, but I am on 11.2.0.3 and using active dataguard option and still showing value higher than primary.
  • 7. Re: Standby SCN greater than SCN on Primary
    978280 Newbie
    Currently Being Moderated
    Thanks Mseberg. Initially before posting here, I came across the thread you posted which said that it is not possible but I still cannot come to a conclusion as why is the SCN on standby greater than primary.

    With RTA, I understand that a lag of sequence 1 is acceptable and also the standby is in sync with the primary with no issues being reported by the Users. My only concern is, if in future the standby goes out of sync and I may need to roll forward it, then I might face the SCN issue then regarding what SCN to be considered. (An SCN value on standby which is not generated on Primary :( )
  • 8. Re: Standby SCN greater than SCN on Primary
    mseberg Guru
    Currently Being Moderated
    I checked that out too.

    Run this on the Standby :

    select * from v$recovery_progress;

    The applied_scn column will give a clue why the number is higher.

    I would open a ticket with Oracle support for sure.

    This is going to bug me. SCN is what I depend on in case of failover. Having an issue here is not good.

    Running same version, with same OS with test here.

    Best Regards

    mseberg

    Edited by: mseberg on Dec 11, 2012 1:55 PM
  • 9. Re: Standby SCN greater than SCN on Primary
    978280 Newbie
    Currently Being Moderated
    Here is the outcome of the v$recovery_progress when run on the standby:
    START_TIM TYPE                 ITEM                             UNITS                SOFAR      TOTAL TIMESTAMP COMMENTS
    --------- -------------------- -------------------------------- --------------- ---------- ---------- --------- --------------------
    08-NOV-12 Media Recovery       Log Files                        Files                 3330          0
    08-NOV-12 Media Recovery       Active Apply Rate                KB/sec                1270          0
    08-NOV-12 Media Recovery       Average Apply Rate               KB/sec                  47          0
    08-NOV-12 Media Recovery       Maximum Apply Rate               KB/sec                9982          0
    08-NOV-12 Media Recovery       Redo Applied                     Megabytes           130653          0
    08-NOV-12 Media Recovery       Last Applied Redo                SCN+Time                 0          0 11-DEC-12 SCN: 421897371
    08-NOV-12 Media Recovery       Active Time                      Seconds             250334          0
    08-NOV-12 Media Recovery       Apply Time per Log               Seconds                 74          0
    08-NOV-12 Media Recovery       Checkpoint Time per Log          Seconds                  0          0
    08-NOV-12 Media Recovery       Elapsed Time                     Seconds            2838660          0
    08-NOV-12 Media Recovery       Standby Apply Lag                Seconds                  0          0
    
    11 rows selected.
    I do not see the applied_scn column in this table.
  • 10. Re: Standby SCN greater than SCN on Primary
    mseberg Guru
    Currently Being Moderated
    Odd, but you do get the SCN hmmm.

    Not using Real time it checks out :




    Primary
    SQL> set numformat 99999999999999999999
    SQL> select current_scn from v$database;
    
              CURRENT_SCN
    ---------------------
            1318306300026
    
    SQL> 
    Standby
    SQL> set numformat 99999999999999999999
    SQL> select current_scn from v$database;
    
    
    
              CURRENT_SCN
    ---------------------
            1318306225227
    
    SQL> 
    Second test

    Primary
    SQL> select current_scn from v$database;
    
              CURRENT_SCN
    ---------------------
            1318306303489
    
    SQL> 
    Standby
    SQL> set numformat 99999999999999999999
    SQL> select current_scn from v$database;
    
              CURRENT_SCN
    ---------------------
            1318306225227
    
    SQL> 
    Will try real time and test again.

    Best Regards

    mseberg

    Edited by: mseberg on Dec 11, 2012 2:06 PM
  • 11. Re: Standby SCN greater than SCN on Primary
    mseberg Guru
    Currently Being Moderated
    Not able to recreate.

    Would consider cancel of recovery on the standby and restart followed by a few forced log switches on the primary to see if I could shake out more evidence.

    Best Regards

    mseberg
  • 12. Re: Standby SCN greater than SCN on Primary
    978280 Newbie
    Currently Being Moderated
    I have tried forcefully switching logfile on primary but that too did not help. May be I'll have to raise an SR.
  • 13. Re: Standby SCN greater than SCN on Primary
    mseberg Guru
    Currently Being Moderated
    In 11.2.0.2 ( and higher ) the view V$ARCHIVE_DEST on the primary is suppose to track which scn have been applied on the standby.

    http://docs.oracle.com/cd/E11882_01/server.112/e17110/dynviews_1012.htm#REFRN30007

    My quick query is :
    COLUMN destination FORMAT A35 WRAP 
    column process format a7 
    column archiver format a8 
    column ID format 99 
    
    SELECT 
      DEST_ID "ID",DESTINATION,STATUS,TARGET, ARCHIVER,SCHEDULE,PROCESS,MOUNTID,APPLIED_SCN  
    FROM 
      V$ARCHIVE_DEST; 
    Not sure if this helps at all. Any chance the SCN here is lower?

    Looks like the results are tied to LOG_ARCHIVE_MAX_PROCESSES.

    Best Regards

    mseberg

    Edited by: mseberg on Dec 11, 2012 2:34 PM
  • 14. Re: Standby SCN greater than SCN on Primary
    978280 Newbie
    Currently Being Moderated
    Hi Mseberg,

    Here is the outcome of the query when run on Primary database. The applied_scn seems to be lower than the CURRENT_SCN on primary.

     ID DESTINATION                         STATUS    TARGET  ARCHIVER SCHEDULE PROCESS    MOUNTID APPLIED_SCN
    --- ----------------------------------- --------- ------- -------- -------- ------- ---------- -----------
      1 USE_DB_RECOVERY_FILE_DEST           VALID     PRIMARY ARCH     ACTIVE   ARCH             0           0
      2 KRA1S                               VALID     STANDBY LGWR     ACTIVE   LGWR             0   421940643
      3                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
      4                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
      5                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
      6                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
      7                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
      8                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
      9                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     10                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     11                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
    
     ID DESTINATION                         STATUS    TARGET  ARCHIVER SCHEDULE PROCESS    MOUNTID APPLIED_SCN
    --- ----------------------------------- --------- ------- -------- -------- ------- ---------- -----------
     12                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     13                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     14                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     15                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     16                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     17                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     18                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     19                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     20                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     21                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     22                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
    
     ID DESTINATION                         STATUS    TARGET  ARCHIVER SCHEDULE PROCESS    MOUNTID APPLIED_SCN
    --- ----------------------------------- --------- ------- -------- -------- ------- ---------- -----------
     23                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     24                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     25                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     26                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     27                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     28                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     29                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     30                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
     31                                     INACTIVE  PRIMARY ARCH     INACTIVE ARCH             0           0
    
    31 rows selected.
    
    SQL> select current_scn from v$database;
    
    CURRENT_SCN
    -----------
      421940797
    Now, the very strange thing is that I could see the SCN almost nearing to same on standby and primary. Not sure how it worked out.

    Primary:
    STATUS       INSTANCE_NAME    DATABASE_ROLE    CURRENT_SCN
    ------------ ---------------- ---------------- -----------
    OPEN         KRA1T            PRIMARY            421947020
    Standby:
    STATUS       INSTANCE_NAME    DATABASE_ROLE    CURRENT_SCN
    ------------ ---------------- ---------------- -----------
    OPEN         KRA1S            PHYSICAL STANDBY   421947016
    I really do not understand the logic behind this.
1 2 Previous Next

Legend

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