This discussion is archived
12 Replies Latest reply: Dec 6, 2012 11:21 AM by timscn RSS

After DR Failover, Primary will not become Standby

975078 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    975078 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points