1 2 Previous Next 19 Replies Latest reply: Dec 12, 2012 1:13 PM by 978280 Go to original post RSS
      • 15. Re: Standby SCN greater than SCN on Primary
        mseberg
        Hello again;

        Sorry for the delay. I don't understand either. Feels like a bug but I hate to guess.

        Would consider an SR and would monitor close for a few days.

        I have asked CKPT to review and he may have an additional insight.

        Very interesting.

        Best Regards

        mseberg
        • 16. Re: Standby SCN greater than SCN on Primary
          CKPT
          Hello,

          This situation is really interesting. Usually the primary SCN will be ahead than standby database. and CURRENT_SCN from standby refers to the SCN of redo application.
          Moreover i would like to see the CHECKPOINT_CHANGE# of datafiles from primary and standby, Does this output also vary?

          and try to stop the MRP and then see the CURRENT_SCN from v$database of both primary and standby.
          As Mseberg said if this is the case, Better to check with Oracle Support by submitting SR.

          Thank you.
          • 17. Re: Standby SCN greater than SCN on Primary
            978280
            Hi CKPT,

            The SCN on the standby seems to be less than the Primary. As said yesterday, I am not sure how it turned to be low. Here is the outcome of what you asked.

            Prirmary:
            SQL> select current_scn from v$database;
            
            CURRENT_SCN
            -----------
              422793745
            
            SQL> select file#,checkpoint_change# from v$datafile;
            
                 FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                     1          422776760
                     2          422776760
                     3          422776760
                     4          422776760
                     5          422776760
                     6          422776760
                     7          422776760
                     8          422776760
                     9          422776760
                    10          422776760
                    11          422776760
            
                 FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                    12          422776760
                    13          422776760
                    14          422776760
                    15          422776760
                    16          422776760
                    17          422776760
            Standby:
            SQL> select current_scn from v$database;
            
            CURRENT_SCN
            -----------
              422793741
            
            SQL> select file#,checkpoint_change# from v$datafile;
            
                 FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                     1          422776760
                     2          422776760
                     3          422776760
                     4          422776760
                     5          422776760
                     6          422776760
                     7          422776760
                     8          422776760
                     9          422776760
                    10          422776760
                    11          422776760
            
                 FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                    12          422776760
                    13          422776760
                    14          422776760
                    15          422776760
                    16          422776760
                    17          422776760
            I have another set of Primary and standby database with same 11.2.0.3 version and even here I am facing the same issue. Here is the details:

            Primary:
            SQL> select status,instance_name,database_role from v$database,v$instance;
            
            STATUS       INSTANCE_NAME    DATABASE_ROLE
            ------------ ---------------- ----------------
            OPEN         MRAT            PRIMARY
            
            SQL>
            SQL> col current_scn for 999999999999999999
            SQL> select current_scn from v$database;
            
                    CURRENT_SCN
            -------------------
                    705523588
            Standby:
            SQL> select status,instance_name,database_role from v$database,v$instance;
            
            STATUS       INSTANCE_NAME    DATABASE_ROLE
            ------------ ---------------- ----------------
            OPEN         MRAS            PHYSICAL STANDBY
            
            SQL> col current_scn for 999999999999999999
            SQL> select current_scn from v$database;
            
                    CURRENT_SCN
            -------------------
                    705523669
            Here the SCN on standby seems to be higher than primary. This also uses the Real Time apply and the standby is in sync with the primary. Below is the checkpoint_change# for the 2nd set of database and the chekcpoint_change# shows less on the standby than primary.

            Primary:
             FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                     1        705517464
                     2        705517464
                     3        705517464
                     4        705517464
                     5        705517464
                     6        705517464
                    11        705517464
                    13        705517464
                    14        705517464
                    15        705517464
                    16        705517464
            
                 FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                    17        705517464
                    18        705517464
                    19        705517464
                    20        705517464
                    21        705517464
                    22        705517464
                    23        705517464
                    24        705517464
                    25        705517464
                    26        705517464
                    27        705517464
            Standby:
            FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                     1        705503643
                     2        705503643
                     3        705503643
                     4        705503643
                     5        705503643
                     6        705503643
                    11        705503643
                    13        705503643
                    14        705503643
                    15        705503643
                    16        705503643
            
                 FILE# CHECKPOINT_CHANGE#
            ---------- ------------------
                    17        705503643
                    18        705503643
                    19        705503643
                    20        705503643
                    21        705503643
                    22        705503643
                    23        705503643
                    24        705503643
                    25        705503643
                    26        705503643
                    27        705503643
            • 18. Re: Standby SCN greater than SCN on Primary
              CKPT
              I have one last question. CURRENT_SCN is like clock which is never stops until instance is closed. Which is never going to stop even though there is no activity on database.

              Just a stone, what is the time you taking to query between primary and standby? Probably you querying first on Primary and after some time like 20-30 seconds you querying on standby?
              Can you try with couple of tests by querying immediately in standby and then primary and vice versa?

              and if still the issue is same, then i suggest to submit SR to oracle support.
              Thanks.
              • 19. Re: Standby SCN greater than SCN on Primary
                978280
                Thanks CKPT. I have opened two sessions, one in primary and the other for a standby. I am running the CURRENT_SCN on both the databases at the same time.
                I guess the difference in time the query is run may be 1 second.

                I'll certainly check for a few more duration and if its the same, then I'll raise an SR.
                1 2 Previous Next