12 Replies Latest reply: Dec 6, 2012 1:21 PM by timscn RSS

    After DR Failover, Primary will not become Standby

    BostonDBA
      To test our instance, we killed PMON on the Primary and enabled the Standby to be the Primary. Now the new Primary (after bringing it back up) will not switch over to be the Standby (it's very stubborn that way).

      Here's the error messages:
      19:09:21 nolog> startup mount
      ORACLE instance started.
      
      Total System Global Area  676433920 bytes
      Fixed Size                  2162760 bytes
      Variable Size             239079352 bytes
      Database Buffers          427819008 bytes
      Redo Buffers                7372800 bytes
      Database mounted.
      19:09:34 nolog> alter database commit to switchover to standby with session shutdown;
      alter database commit to switchover to standby with session shutdown
      *
      ERROR at line 1:
      ORA-01109: database not open
      
      
      Elapsed: 00:00:00.00
      19:09:50 nolog> alter database open;
      
      Database altered.
      
      Elapsed: 00:00:07.61
      19:10:05 nolog> alter database commit to switchover to standby with session shutdown;
      alter database commit to switchover to standby with session shutdown
      *
      ERROR at line 1:
      ORA-16416: Switchover target is not synchronized with the primary
      
      
      Elapsed: 00:00:11.09
      19:10:19 nolog>
      Alert Log shows:
      Wed Dec 05 19:09:27 2012
      ALTER DATABASE   MOUNT
      NOTE:Loaded library: System
      SUCCESS: diskgroup DG_01 was mounted
      SUCCESS: diskgroup FRA_DG_01 was mounted
      Setting recovery target incarnation to 2
      Successful mount of redo thread 1, with mount id 3967177063
      Database mounted in Exclusive Mode
      Lost write protection disabled
      Completed: ALTER DATABASE   MOUNT
      Wed Dec 05 19:09:48 2012
      Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
      RFS[1]: Assigned to RFS process 15293
      RFS[1]: Database mount ID mismatch [0xec75ffab:0xec765167] (3967156139:3967177063)
      RFS[1]: Not using real application clusters
      Errors in file /data/oracle/app/oracle/diag/rdbms/ecpmtsb/ECPMTSB/trace/ECPMTSB_rfs_15293.trc:
      ORA-16009: remote archive log destination must be a STANDBY database
      Wed Dec 05 19:09:50 2012
      alter database commit to switchover to standby with session shutdown
      ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (ECPMTSB)
      ORA-1109 signalled during: alter database commit to switchover to standby with session shutdown...
      alter database open
      Wed Dec 05 19:09:58 2012
      LGWR: STARTING ARCH PROCESSES
      Wed Dec 05 19:09:58 2012
      ARC0 started with pid=24, OS id=15622
      Wed Dec 05 19:09:58 2012
      ARC1 started with pid=25, OS id=15624
      Wed Dec 05 19:09:58 2012
      ARC2 started with pid=26, OS id=15626
      Wed Dec 05 19:09:58 2012
      ARC3 started with pid=27, OS id=15628
      Wed Dec 05 19:09:58 2012
      ARC4 started with pid=28, OS id=15630
      Wed Dec 05 19:09:58 2012
      ARC5 started with pid=29, OS id=15632
      ARC0: Archival started
      Wed Dec 05 19:09:58 2012
      ARC6 started with pid=30, OS id=15634
      ARC1: Archival started
      ARC2: Archival started
      ARC3: Archival started
      ARC4: Archival started
      ARC5: Archival started
      ARC6: Archival started
      LGWR: STARTING ARCH PROCESSES COMPLETE
      Thread 1 opened at log sequence 1339
        Current log# 2 seq# 1339 mem# 0: +FRA_DG_01/ecpmtsb/onlinelog/group_2.401.801169497
      Successful open of redo thread 1
      MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
      Wed Dec 05 19:09:58 2012
      SMON: enabling cache recovery
      ARC2: STARTING ARCH PROCESSES
      ARC0: Becoming the 'no FAL' ARCH
      ARC0: Becoming the 'no SRL' ARCH
      ARC6: Becoming the heartbeat ARCH
      ARC7: Archival started
      Wed Dec 05 19:09:58 2012
      ARC7 started with pid=31, OS id=15638
      ARC2: STARTING ARCH PROCESSES COMPLETE
      Successfully onlined Undo Tablespace 2.
      Verifying file header compatibility for 11g tablespace encryption..
      Verifying 11g file header compatibility for tablespace encryption completed
      SMON: enabling tx recovery
      Database Characterset is WE8MSWIN1252
      Opening with internal Resource Manager plan
      Starting background process FBDA
      Wed Dec 05 19:09:59 2012
      FBDA started with pid=32, OS id=15645
      replication_dependency_tracking turned off (no async multimaster replication found)
      Starting background process QMNC
      Wed Dec 05 19:09:59 2012
      QMNC started with pid=33, OS id=15651
      Wed Dec 05 19:10:02 2012
      db_recovery_file_dest_size of 51200 MB is 1.96% used. This is a
      user-specified limit on the amount of space that will be used by this
      database for recovery-related files, and does not reflect the amount of
      space available in the underlying filesystem or ASM diskgroup.
      Wed Dec 05 19:10:05 2012
      Completed: alter database open
      alter database commit to switchover to standby with session shutdown
      ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (ECPMTSB)
      Wed Dec 05 19:10:08 2012
      Thread 1 advanced to log sequence 1340 (LGWR switch)
      Wed Dec 05 19:10:08 2012
      Shutting down archive processes
        Current log# 1 seq# 1340 mem# 0: +FRA_DG_01/ecpmtsb/onlinelog/group_1.402.801169497
      Wed Dec 05 19:10:08 2012
      ARCH shutting down
      ARC7: Archival stopped
      Waiting for all non-current ORLs to be archived...
      Waiting for the ORL for thread 1 sequence 1339 to be archived...
      Archived Log entry 37 added for thread 1 sequence 1339 ID 0xec73e4a2 dest 1:
      Wed Dec 05 19:10:18 2012
      ORL for thread 1 sequence 1339 has been archived...
      All non-current ORLs have been archived.
      Waiting for all FAL entries to be archived...
      All FAL entries have been archived.
      Waiting for potential switchover target to become synchronized...
      No viable Physical Standby switchover targets available
      ORA-16416 signalled during: alter database commit to switchover to standby with session shutdown...
      The Parameter for DEST :
      19:10:19 nolog> show parameter dest
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      audit_file_dest                      string      /data/oracle/app/oracle/admin/
                                                       ECPMTSB/adump
      background_dump_dest                 string      /data/oracle/app/oracle/diag/r
                                                       dbms/ecpmtsb/ECPMTSB/trace
      core_dump_dest                       string      /data/oracle/app/oracle/admin/
                                                       ECPMTSB/cdump
      db_create_file_dest                  string
      db_create_online_log_dest_1          string
      db_create_online_log_dest_2          string
      db_create_online_log_dest_3          string
      db_create_online_log_dest_4          string
      db_create_online_log_dest_5          string
      db_recovery_file_dest                string      +FRA_DG_01
      db_recovery_file_dest_size           big integer 50G
      diagnostic_dest                      string      /data/oracle/app/oracle
      log_archive_dest                     string
      log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                       DEST VALID_FOR=(ALL_LOGFILES,A
                                                       LL_ROLES) DB_UNIQUE_NAME=ECPMT
                                                       SB
      log_archive_dest_10                  string
      log_archive_dest_2                   string      SERVICE=ECPMT VALID_FOR=(ONLIN
                                                       E_LOGFILES,PRIMARY_ROLE) DB_UN
                                                       IQUE_NAME=ECPMT
      log_archive_dest_3                   string      LOCATION=+FRA_DG_01/ecpmtsb/st
                                                       andbylog  VALID_FOR=(STANDBY_L
                                                       OGFILES,STANDBY_ROLE) DB_UNIQU
                                                       E_NAME=ECPMTSB
      log_archive_dest_4                   string
      log_archive_dest_5                   string
      log_archive_dest_6                   string
      log_archive_dest_7                   string
      log_archive_dest_8                   string
      log_archive_dest_9                   string
      log_archive_dest_state_1             string      enable
      log_archive_dest_state_10            string      enable
      log_archive_dest_state_2             string      DEFER
      log_archive_dest_state_3             string      ENABLE
      log_archive_dest_state_4             string      enable
      log_archive_dest_state_5             string      enable
      log_archive_dest_state_6             string      enable
      log_archive_dest_state_7             string      enable
      log_archive_dest_state_8             string      enable
      log_archive_dest_state_9             string      enable
      log_archive_duplex_dest              string
      log_archive_min_succeed_dest         integer     2
      standby_archive_dest                 string      ?/dbs/arch
      user_dump_dest                       string      /data/oracle/app/oracle/diag/r
                                                       dbms/ecpmtsb/ECPMTSB/trace
      Anyone that can help would be much appreciated. Thank you!

      Edited by: BostonDBA on Dec 5, 2012 2:14 PM
        • 1. Re: After DR Failover, Primary will not become Standby
          mseberg
          Hello;

          If you did a failover I would expect :

          These statements run on the old standby
          SQL> alter database recover managed standby database finish;
          
          SQL> alter database commit to switchover to primary with session shutdown;
          
          SQL> alter database open;
          To bring back the Old Primary I would expect :

          On the new primary : (record the results)
          SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FAILOVER_SCN FROM V$DATABASE;
          Then the old primary needs to be flashed back to that SCN and converted to a standby.


          Did you do this?

          Best Regards

          mseberg
          • 2. Re: After DR Failover, Primary will not become Standby
            BostonDBA
            You're correct, I did run those statements on the old standby to make it the new Primary.

            On the old Primary, when I run that statement I get:
            SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FAILOVER_SCN FROM V$DATABASE;
            
            
            
            FAILOVER_SCN
            ----------------------------------------
            86270028478
            So it turns out Flashback is not turned on, is there any other way to achieve forcing the old Primary to become a Standby?

            Edited by: BostonDBA on Dec 5, 2012 2:45 PM
            • 3. Re: After DR Failover, Primary will not become Standby
              Shivananda Rao
              Hello,

              If you have performed the failover and FLASHBACK was not enabled, then you'll have to create a new standby database from scratch for the new Primary database.

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



              Regards,
              Shivananda
              • 4. Re: After DR Failover, Primary will not become Standby
                mseberg
                Yes.

                You can use RMAN to bring the old Primary back to a point where it can become a standby. Once its a standby and working and in SYNC with the new primary.

                For an overview of the flashback method ( good for concept ) I have this :

                http://www.visi.com/~mseberg/Data_Guard_Failover_Test_using_SQL.pdf

                Will check for RMAN document, but I don't think I have one.

                Update

                I don't have a document or have I tested. In theory this would work.
                RMAN> run {
                2> set until scn 86270028478;
                3> restore database;
                4> recover database;
                5> }
                Would consider checking my friends site here :

                http://www.oracle-ckpt.com/


                I trust all his documents and he probably has it.

                h2. Update

                OK, There's a MOS document on this :

                Reinstating a Physical Standby Using Backups Instead of Flashback [ID 416310.1]

                So the statement you have to recreate is bogus...

                Plus


                Using RMAN Effectively In A Dataguard Environment. [ID 848716.1]






                Best Regards

                mseberg

                Edited by: mseberg on Dec 5, 2012 2:16 PM
                • 5. Re: After DR Failover, Primary will not become Standby
                  Shivananda Rao
                  Reinstating a Physical Standby Using Backups Instead of Flashback [ID 416310.1]
                  
                  So the statement you have to recreate is bogus...
                  Thank you MSeberg. I would add it to my knowledge base. :)


                  Regards,
                  Shivananda
                  • 6. Re: After DR Failover, Primary will not become Standby
                    mseberg
                    Very good!

                    BostonDBA      

                    While flash is not required for Data Guard I would not run it without it.

                    Edited by: mseberg on Dec 5, 2012 6:35 PM
                    • 7. Re: After DR Failover, Primary will not become Standby
                      978280
                      So the statement you have to recreate is bogus...
                      But Oracle documentation says that there are 3 ways of getting back your old primary database as a new standby database.

                      1. Using FlashBack method
                      2. Re-create the old primary database using the backup of new primary database.

                      Note from the oracle documentation:
                      To reuse the old primary database in the new configuration, you must re-create it as a standby database using a backup copy of the new primary database.
                      3. REINSTATE DATABASE which you mentioned earlier. But the documentation mentioned below says that this would be done using DGMGRL. What if DGMGRL is not configured ?

                      Oracle documentation link http://docs.oracle.com/cd/B19306_01/server.102/b14239/role_management.htm#i1026398 (STEP: 8)

                      So, is re-creating the standby database really bogus or can be considered as an approach if case 1 failed.
                      • 8. Re: After DR Failover, Primary will not become Standby
                        CKPT
                        Boston DBA,

                        When you performed failover on standby database, Everytime the new incarnation will be created and caused incompatible incarnation with primary and standby databases.
                        You can check, SQL> select resetlogs_change#,prior_resetlogs_change# from v$database;

                        If you are willing to use your Old primary as Primary, then if you have created restore point on standby then you have option of flashback your standby database, Then you no need to build new standby. Am not aware of your requirement and when you are not using flashback, You do not have any option now except to create a standby from your new Primary database.

                        So consider implementing flashback technology. You cannot use flashback in your life, at least we can use in database ;)
                        Note:- If you are managing with Broker, Reinstating databases is very much simpler. Again you must have Flashback enabled :)
                        • 9. Re: After DR Failover, Primary will not become Standby
                          timscn
                          hi
                          i think During switchover, primary will check all sites included in log_archive_config to ensure they are in sync before switchover can proceed.
                          check your
                          log_archive_config
                          and set correct for your config
                          and
                          alter system set log_archive_dest_2='';
                          *ORA-16416 Switchover Target Is Not Synchronized With The Primary [ID 753766.1]*+

                          sorry i can mistake about your problem((
                          • 10. Re: After DR Failover, Primary will not become Standby
                            mseberg
                            Hello;

                            Not sure you are comparing apples and apples.

                            The document you refer to is Oracle 10. OP is using Oracle 11.

                            On MOS document you make an excellent point since Data Broker is not being used here. However RMAN is still an option without Data Broker.

                            The correct MOS is :

                            Reinstating a Physical Standby Using Backups Instead of Flashback [ID 416310.1]

                            If you read it it say Oracle 10 in one spot and Oracle 10 or higher in another.

                            Thanks for the correction! Any chance you know Craig in Canada?

                            Best Regards

                            mseberg
                            • 11. Re: After DR Failover, Primary will not become Standby
                              978280
                              Thank you for letting me know. I was of the opinion that Reinstate can be done only using DGBROKER.
                              The problem is that I do not have access to MOS and so could not look into what it said.
                              Thanks for the correction! Any chance you know Craig in Canada?
                              Not sure who Craig is. But if OP (for using your thread) and you do not mind, then can I know who Craig is why is it so ?
                              • 12. Re: After DR Failover, Primary will not become Standby
                                timscn
                                hi;)
                                you're right ... but I have same problem on 10g as in mos 753766.1 .. I thought that the same situation as the BostonDBA,