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
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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