10 Replies Latest reply: Oct 29, 2013 3:34 AM by Mahir M. Quluzade RSS

    switchover failed,primary status is 'not allowed'

    1051065

      hi,

      i used AS5+11gR2+dg and got a problem after normal switchover.

       

      1.the switchover_status of primary shows 'not allowed'

      2.log seems not SYNC.

      3.dgmgrl shows 'incorrect database role'.

       

      everything goes OK before switchover, how could this happen and how to switchover back?

       

      thanks all.

        • 1. Re: switchover failed,primary status is 'not allowed'
          saurabh

          before doing switchback of the database you need to sync both the primary and standby database.

           

          paste the output of below query.

          1.select name,open_mode,database_role,switchover_status from v$database; from both the databases

          2.select max(sequence#) from v$archive_log where applied ='YES'; from standby

          3. The current archive sequnce on the primary database.


          • 2. Re: switchover failed,primary status is 'not allowed'
            1051065

            Thanks for your reply,

             

            1. 

            PARIMARY

            NAME                                                         OPEN_MODE            DATABASE_R SWITCHOVER_STATUS

            ------------------------------------------------------------ -------------------- ---------- --------------------

            TESTDB                                                       READ WRITE           PRIMARY    NOT ALLOWED

             

            STANDBY

            NAME                                                         OPEN_MODE            DATABASE_R SWITCHOVER_STATUS

            ------------------------------------------------------------ -------------------- ---------- --------------------

            TESTDB                                                       READ ONLY            PHYSICAL S SESSIONS ACTIVE

                                                                                              TANDBY

             

            2.STANDBY

            select max(sequence#) from v$archive_log where applied ='YES'

                                       *

            ERROR at line 1:

            ORA-00942: table or view does not exist

             

            SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

             

             

               THREAD#          A

            ---------- ----------

                     1         16

            3.PRIMARY

            SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

             

             

               THREAD#          A

            ---------- ----------

                     1         37

            • 3. Re: switchover failed,primary status is 'not allowed'
              Mahir M. Quluzade

              Hi,

               

              Before performing switchover you must verify target standby database.

               

              On target standby

               

              1. Apply process is running,

               

              select process from  v$managed_standby where process like 'MR%'

              if no row selected, it means Apply stopped.

              try : alter database recover managed standby database disconnect from sesson;

               

              2. If apply process is running but you have, GAP ?!

               

              select *  from v$archive_gap;

               

              3. Check applied sequences with  primary

               

              select max(sequence#)  from v$archived_log;

              select max(sequence#)  from v$archived_log where applied='YES';

               

              Regards

              Mahir M. Quluzade

              • 4. Re: switchover failed,primary status is 'not allowed'
                Mahir M. Quluzade

                2.STANDBY

                select max(sequence#) from v$archive_log where applied ='YES'

                                           *

                ERROR at line 1:

                ORA-00942: table or view does not exist

                 

                You must select from v$archive_log view

                 

                SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

                 

                 

                   THREAD#          A

                ---------- ----------

                         1         16

                3.PRIMARY

                SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

                 

                 

                   THREAD#          A

                ---------- ----------

                         1         37

                 

                 

                It means your transport stoped.

                 

                Can you paste :

                 

                select dest_id, error from v$archive_dest where error is not null;
                • 5. Re: switchover failed,primary status is 'not allowed'
                  1051065

                  thanks for reply,

                   

                  1.

                  SQL> select process from  v$managed_standby where process like 'MR%';

                   

                   

                  PROCESS

                  ---------

                  MRP0

                   

                  2.

                  SQL> select *  from v$archive_gap;

                   

                  no rows returned here

                  but the first switch i got a RESOLVABLE GAP for primary,and i resolve this.switchover succeed to get over it.

                  3.

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

                   

                   

                  MAX(SEQUENCE#)

                  --------------

                              37

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

                   

                   

                  MAX(SEQUENCE#)

                  --------------

                              16

                  • 6. Re: switchover failed,primary status is 'not allowed'
                    Mahir M. Quluzade

                    Can you paste here:

                     

                    DGMGRL> show configuration;

                    DGMGRL> show database verbose <primary>

                    DGMGRL> show database verbose <standby>

                     

                    Mahir

                    • 7. Re: switchover failed,primary status is 'not allowed'
                      1051065

                      MahirM.Quluzade 撰写:

                       

                      Can you paste here:

                       

                      DGMGRL> show configuration;

                      DGMGRL> show database verbose <primary>

                      DGMGRL> show database verbose <standby>

                       

                      Mahir

                      1.

                      DGMGRL> show configuration

                       

                       

                      Configuration - DRtestdb

                       

                       

                        Protection Mode: MaxPerformance

                        Databases:

                          testdb     - Primary database

                            Error: ORA-16816: incorrect database role

                       

                       

                          sztestdbdg - Physical standby database

                            Error: ORA-16810: multiple errors or warnings detected for the database

                       

                       

                      Fast-Start Failover: DISABLED

                       

                       

                      Configuration Status:

                      ERROR

                       

                      2.

                      DGMGRL> show database verbose 'sztestdbdg';

                       

                       

                      Database - sztestdbdg

                       

                       

                        Role:            PHYSICAL STANDBY

                        Intended State:  APPLY-ON

                        Transport Lag:   (unknown)

                        Apply Lag:       (unknown)

                        Real Time Query: OFF

                        Instance(s):

                          sztestdbdg

                       

                       

                        Database Error(s):

                          ORA-16816: incorrect database role

                          ORA-16700: the standby database has diverged from the primary database

                          ORA-16766: Redo Apply is stopped

                       

                       

                        Properties:

                          DGConnectIdentifier             = 'sztestdbdg'

                          ObserverConnectIdentifier       = ''

                          LogXptMode                      = 'ASYNC'

                          DelayMins                       = '0'

                          Binding                         = 'OPTIONAL'

                          MaxFailure                      = '0'

                          MaxConnections                  = '1'

                          ReopenSecs                      = '300'

                          NetTimeout                      = '30'

                          RedoCompression                 = 'DISABLE'

                          LogShipping                     = 'ON'

                          PreferredApplyInstance          = ''

                          ApplyInstanceTimeout            = '0'

                          ApplyParallel                   = 'AUTO'

                          StandbyFileManagement           = 'AUTO'

                          ArchiveLagTarget                = '0'

                          LogArchiveMaxProcesses          = '4'

                          LogArchiveMinSucceedDest        = '1'

                          DbFileNameConvert               = '/app/oracle/oradata/testdb/, /oradata/sztestdbdg/sztestdbdg/'

                          LogFileNameConvert              = '/app/oracle/oradata/testdb/, /oradata/sztestdbdg/sztestdbdg/'

                          FastStartFailoverTarget         = ''

                          InconsistentProperties          = '(monitor)'

                          InconsistentLogXptProps         = '(monitor)'

                          SendQEntries                    = '(monitor)'

                          LogXptStatus                    = '(monitor)'

                          RecvQEntries                    = '(monitor)'

                          SidName                         = 'sztestdbdg'

                          StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.9.198)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=SZTESTDBDG_DGMGRL)(INSTANCE_NAME=SZTESTDBDG)(SERVER=DEDICATED)))'

                          StandbyArchiveLocation          = '/arch/sztestdbdg'

                          AlternateLocation               = ''

                          LogArchiveTrace                 = '0'

                          LogArchiveFormat                = '%t_%s_%r.dbf'

                          TopWaitEvents                   = '(monitor)'

                       

                       

                      Database Status:

                      ERROR

                       

                      3.

                      DGMGRL> show database verbose 'testdb';

                       

                       

                      Database - testdb

                       

                       

                        Role:            PRIMARY

                        Intended State:  TRANSPORT-ON

                        Instance(s):

                          testdb

                       

                       

                        Database Error(s):

                          ORA-16816: incorrect database role

                       

                       

                        Properties:

                          DGConnectIdentifier             = 'testdb'

                          ObserverConnectIdentifier       = ''

                          LogXptMode                      = 'ASYNC'

                          DelayMins                       = '0'

                          Binding                         = 'optional'

                          MaxFailure                      = '0'

                          MaxConnections                  = '1'

                          ReopenSecs                      = '300'

                          NetTimeout                      = '30'

                          RedoCompression                 = 'DISABLE'

                          LogShipping                     = 'ON'

                          PreferredApplyInstance          = ''

                          ApplyInstanceTimeout            = '0'

                          ApplyParallel                   = 'AUTO'

                          StandbyFileManagement           = 'MANUAL'

                          ArchiveLagTarget                = '0'

                          LogArchiveMaxProcesses          = '4'

                          LogArchiveMinSucceedDest        = '1'

                          DbFileNameConvert               = '/app/oracle/oradata/testdb/, /oradata/sztestdbdg/sztestdbdg'

                          LogFileNameConvert              = '/app/oracle/oradata/testdb/, /oradata/sztestdbdg/sztestdbdg'

                          FastStartFailoverTarget         = ''

                          InconsistentProperties          = '(monitor)'

                          InconsistentLogXptProps         = '(monitor)'

                          SendQEntries                    = '(monitor)'

                          LogXptStatus                    = '(monitor)'

                          RecvQEntries                    = '(monitor)'

                          SidName                         = 'testdb'

                          StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.9.197)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=testdb_DGMGRL)(INSTANCE_NAME=testdb)(SERVER=DEDICATED)))'

                          StandbyArchiveLocation          = '/arch/testdb'

                          AlternateLocation               = ''

                          LogArchiveTrace                 = '0'

                          LogArchiveFormat                = '%t_%s_%r.dbf'

                          TopWaitEvents                   = '(monitor)'

                       

                       

                      Database Status:

                      ERROR

                      • 8. Re: switchover failed,primary status is 'not allowed'
                        Mahir M. Quluzade

                        Which way you used for switchover?

                        Did you used :  DGMGRL> switchover to sztestdbdg; ?

                         

                        I  think, you used SQL*Plus for perform switchover.

                         

                        Do not forget, if you are using broker managed data guard, you must use DGMGRL.

                        Because every thing must be changed  broker managed config files.

                         

                        In your case your database roles changed, but config file is not changed.

                        You are getting this errors.

                         

                        If you are used SQL*Plus, then you must remove Broker configuration safely.

                        After you must create broker configuration again.

                         

                         

                        Regards

                        Mahir M. Quluzade

                        • 9. Re: switchover failed,primary status is 'not allowed'
                          1051065

                          Thanks very much.

                          i acctually use SQL*Plus for switchover.

                          should i disable the current configuration and create a new one?

                          then use the DGMGRL> switchover to sztestdbdg; ?

                          • 10. Re: switchover failed,primary status is 'not allowed'
                            Mahir M. Quluzade

                            You are welcome!

                            1. You must remove data guard configuration safely, please check : Mahir M. Quluzade | Oracle Blog: Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?

                            2. Perform Switchover to testdb (if your testdb must be primary)

                            3. Create DG confguration with  DGMGRL.

                            4. Do not forget, if you are using broker managed data guard, you must use DGMGRL.

                            Because every thing must be changed  broker managed config files.

                             

                            Regards

                            Mahir M. Quluzade