6 Replies Latest reply: Nov 8, 2012 12:20 AM by user12194321 RSS

    v$archived_log still  showing older sequence after failover in datagaurd

    user12194321
      Hi,

      oracle version : 11.2.0.2

      os : redhat linux 5.8

      We did failover due to failure of production database in datagaurd but if i query v$archived_log still it showing older sequences

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

      MAX(SEQUENCE#)
      --------------
      23541

      where current sequence is

      SQL> select max(sequence#) from v$log;

      MAX(SEQUENCE#)
      --------------
      859

      any inputs please
        • 1. Re: v$archived_log still  showing older sequence after failover in datagaurd
          JohnWatson
          That is correct. The RESETLOGS does not invalidate the previous thread of redo.
          • 2. Re: v$archived_log still  showing older sequence after failover in datagaurd
            user12194321
            Hi Thanks

            one quick question we have created the standby after failback and we are switching back to older configuration

            and now both primary and standby are sync

            is this wrong sequence in v$archived_log view does not make any problem in switchover ?

            Thanks
            • 3. Re: v$archived_log still  showing older sequence after failover in datagaurd
              JohnWatson
              The sequence isn't wrong, it is correct. The controlfile is fully aware of what happened.
              Standard functionality.
              • 4. Re: v$archived_log still  showing older sequence after failover in datagaurd
                Shivananda Rao
                user12194321 wrote:
                Hi Thanks

                one quick question we have created the standby after failback and we are switching back to older configuration

                and now both primary and standby are sync

                is this wrong sequence in v$archived_log view does not make any problem in switchover ?

                Thanks
                It wouldn't cause any problem. The value that you are seeing in max(sequence#) is before the Failover operation. After a failover operation, Oracle sets the sequence# to 0 just as if you opened your database with RESETLOGS.

                After rebuilding the standby, as long as the latest archive generated on primary is applied on the standby database, your standby is in sync.

                http://shivanandarao.wordpress.com/2012/08/28/dataguard-failover/

                If you are still worried and thinking that it is the wrong the sequence thats being shown, then just do a simple basic check:

                Primary:
                archive log list
                Standby:
                sql>select process,status,sequence# from v$managed_standby;
                Example:
                
                Primary:
                SQL> archive log list
                Database log mode Archive Mode
                Automatic archival Enabled
                Archive destination USE_DB_RECOVERY_FILE_DEST
                Oldest online log sequence 4
                Next log sequence to archive 6
                Current log sequence 6
                
                Standby:
                SQL> select process,status,sequence# from v$managed_standby;
                
                PROCESS STATUS SEQUENCE#
                ——— ———— ———-
                ARCH CLOSING 3
                ARCH CONNECTED 0
                ARCH CONNECTED 0
                ARCH CLOSING 5
                RFS IDLE 0
                RFS IDLE 0
                RFS IDLE 6
                RFS IDLE 0
                MRP0 APPLYING_LOG 6
                Here you can check that the current log sequence generated on primary is 6 and on the standby it is the same sequence that is getting applied.
                • 5. Re: v$archived_log still  showing older sequence after failover in datagaurd
                  user12194321
                  Thanks to JohnWatson and shivananda rao
                  • 6. Re: v$archived_log still  showing older sequence after failover in datagaurd
                    user12194321
                    Dear all,

                    i got one more confusion , we did switover and brought the original setup
                    select SEQUENCE#, archived,applied, RESETLOGS_CHANGE#, to_char(COMPLETION_TIME, 'MONTH DD, YYYY, HH24:MI:SS') from  v$archived_log order by 1 desc
                    
                     120 YES YES              1954556047 OCTOBER   31, 2012, 09:06:43
                           119 YES YES              1954556047 OCTOBER   31, 2012, 08:51:41
                           119 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:33
                           119 YES YES              1954556047 OCTOBER   31, 2012, 08:51:41
                           118 YES YES              1954556047 OCTOBER   31, 2012, 08:36:45
                           118 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:40
                           118 YES NO               1954556047 OCTOBER   31, 2012, 08:36:45
                           117 YES YES              1954556047 OCTOBER   31, 2012, 08:22:43
                           117 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:56
                           117 YES NO               1954556047 OCTOBER   31, 2012, 08:22:43
                           116 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:25
                           116 YES NO               1954556047 OCTOBER   31, 2012, 08:07:45
                           116 YES YES              1954556047 OCTOBER   31, 2012, 08:07:45
                           115 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:25
                           115 YES YES              1954556047 OCTOBER   31, 2012, 08:07:03
                           115 YES NO               1954556047 OCTOBER   31, 2012, 08:07:02
                           114 YES NO               1954556047 OCTOBER   31, 2012, 08:01:02
                           114 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:25
                           114 YES YES              1954556047 OCTOBER   31, 2012, 08:07:02
                           113 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:25
                           113 YES YES              1954556047 OCTOBER   31, 2012, 08:07:01
                           113 YES NO               1954556047 OCTOBER   31, 2012, 07:48:34
                           112 YES YES              1954556047 NOVEMBER  01, 2012, 05:52:25
                    
                     SEQUENCE# ARC APPLIED   RESETLOGS_CHANGE# TO_CHAR(COMPLETION_TIME,'MONTHDD,YYYY,HH24:MI:SS')
                    ---------- --- --------- ----------------- -------------------------------------------------------
                           112 YES YES              1954556047 OCTOBER   31, 2012, 08:07:02
                           112 YES NO               1954556047 OCTOBER   31, 2012, 07:47:28
                           111 YES NO               1954556047 OCTOBER   31, 2012, 07:44:46
                           110 YES NO               1954556047 OCTOBER   31, 2012, 07:43:56
                           109 YES NO               1954556047 OCTOBER   31, 2012, 07:35:53
                           108 YES NO               1954556047 OCTOBER   31, 2012, 07:20:54
                           107 YES NO               1954556047 OCTOBER   31, 2012, 07:05:54
                           106 YES NO               1954556047 OCTOBER   31, 2012, 07:01:55
                           106 YES YES              1954556047 OCTOBER   31, 2012, 07:01:55
                           105 YES YES              1954556047 OCTOBER   31, 2012, 06:49:06
                           105 YES NO               1954556047 OCTOBER   31, 2012, 06:49:06
                           104 YES YES              1954556047 OCTOBER   31, 2012, 06:34:04
                           104 YES NO               1954556047 OCTOBER   31, 2012, 06:34:04
                           103 YES NO               1954556047 OCTOBER   31, 2012, 06:19:09
                           103 YES YES              1954556047 OCTOBER   31, 2012, 06:19:09
                           102 YES YES              1954556047 OCTOBER   31, 2012, 06:14:37
                           102 YES NO               1954556047 OCTOBER   31, 2012, 06:14:36
                           101 YES YES              1954556047 OCTOBER   31, 2012, 06:14:34
                           101 YES NO               1954556047 OCTOBER   31, 2012, 06:13:47
                           100 YES YES              1954556047 OCTOBER   31, 2012, 06:14:34
                           100 YES NO               1954556047 OCTOBER   31, 2012, 06:13:47
                            99 YES YES              1954556047 OCTOBER   31, 2012, 06:14:34
                            99 YES NO               1954556047 OCTOBER   31, 2012, 06:13:46
                            98 YES NO               1954556047 OCTOBER   31, 2012, 06:13:44
                            98 YES YES              1954556047 OCTOBER   31, 2012, 06:14:34
                            97 YES YES              1954556047 OCTOBER   31, 2012, 06:14:34
                            97 YES NO               1954556047 OCTOBER   31, 2012, 06:13:41
                            96 YES NO               1954556047 OCTOBER   31, 2012, 06:13:39
                            96 YES YES              1954556047 OCTOBER   31, 2012, 06:14:34
                            95 YES YES              1954556047 OCTOBER   31, 2012, 06:13:23
                            95 YES NO               1954556047 OCTOBER   31, 2012, 06:09:00
                            94 YES YES              1954556047 OCTOBER   31, 2012, 06:13:12
                            94 YES NO               1954556047 OCTOBER   31, 2012, 06:00:05
                            93 YES NO               1954556047 OCTOBER   31, 2012, 05:45:08
                            93 YES YES              1954556047 OCTOBER   31, 2012, 06:12:13
                            92 YES YES              1954556047 OCTOBER   31, 2012, 06:12:14
                            92 YES NO               1954556047 OCTOBER   31, 2012, 05:34:55
                            91 YES NO               1954556047 OCTOBER   31, 2012, 05:19:52
                            91 YES YES              1954556047 OCTOBER   31, 2012, 06:12:11
                            90 YES YES              1954556047 OCTOBER   31, 2012, 06:12:13
                            90 YES NO               1954556047 OCTOBER   31, 2012, 05:16:45
                            89 YES YES              1954556047 OCTOBER   31, 2012, 06:12:55
                            89 YES NO               1954556047 OCTOBER   31, 2012, 05:03:23
                            88 YES NO               1954556047 OCTOBER   31, 2012, 04:48:24
                            88 YES YES              1954556047 OCTOBER   31, 2012, 06:12:11
                            87 YES YES              1954556047 OCTOBER   31, 2012, 06:12:14
                            87 YES NO               1954556047 OCTOBER   31, 2012, 04:36:47
                            86 YES NO               1954556047 OCTOBER   31, 2012, 04:21:48
                            86 YES YES              1954556047 OCTOBER   31, 2012, 06:12:13
                            85 YES NO               1954556047 OCTOBER   31, 2012, 04:06:46
                            85 YES YES              1954556047 OCTOBER   31, 2012, 06:12:41
                            84 YES NO               1954556047 OCTOBER   31, 2012, 03:51:47
                            84 YES YES              1954556047 OCTOBER   31, 2012, 06:12:12
                            83 YES YES              1954556047 OCTOBER   31, 2012, 06:12:26
                            83 YES NO               1954556047 OCTOBER   31, 2012, 03:36:46
                            82 YES NO               1954556047 OCTOBER   31, 2012, 03:21:47
                            82 YES YES              1954556047 OCTOBER   31, 2012, 06:12:27
                            81 YES YES              1954556047 OCTOBER   31, 2012, 06:12:26
                            81 YES NO               1954556047 OCTOBER   31, 2012, 03:06:48
                            80 YES YES              1954556047 OCTOBER   31, 2012, 06:12:26
                            80 YES NO               1954556047 OCTOBER   31, 2012, 03:01:47
                            79 YES NO               1954556047 OCTOBER   31, 2012, 02:46:46
                            79 YES YES              1954556047 OCTOBER   31, 2012, 06:12:13
                            78 YES NO               1954556047 OCTOBER   31, 2012, 02:31:50
                            78 YES YES              1954556047 OCTOBER   31, 2012, 06:12:14
                            77 YES NO               1954556047 OCTOBER   31, 2012, 02:29:02
                            76 YES NO               1954556047 OCTOBER   31, 2012, 02:15:23
                            75 YES NO               1954556047 OCTOBER   31, 2012, 02:00:23
                            74 YES NO               1954556047 OCTOBER   31, 2012, 01:50:57
                            73 YES NO               1954556047 OCTOBER   31, 2012, 01:36:02
                            72 YES NO               1954556047 OCTOBER   31, 2012, 01:27:58
                            71 YES NO               1954556047 OCTOBER   31, 2012, 01:12:56
                            70 YES NO               1954556047 OCTOBER   31, 2012, 00:57:57
                            69 YES NO               1954556047 OCTOBER   31, 2012, 00:43:01
                            68 YES NO               1954556047 OCTOBER   31, 2012, 00:27:56
                            67 YES NO               1954556047 OCTOBER   31, 2012, 00:12:57
                            66 YES NO               1954556047 OCTOBER   30, 2012, 23:57:58
                            65 YES NO               1954556047 OCTOBER   30, 2012, 23:42:56
                            64 YES NO               1954556047 OCTOBER   30, 2012, 23:27:58
                            63 YES NO               1954556047 OCTOBER   30, 2012, 23:12:56
                            62 YES NO               1954556047 OCTOBER   30, 2012, 22:57:57
                    why some sequences generated many times like 118?
                    really appreciated if any one make me to understand this :)

                    Thanks

                    Edited by: user12194321 on Nov 8, 2012 12:19 AM