This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 10, 2012 11:30 PM by Fayn_dba RSS

manual switch over for maintenance

Fayn_dba Newbie
Currently Being Moderated
Hi experts:
Following are the steps for Manual Switch over from primary to standby
***************************
connect /@PRIM as sysdba

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

connect /@STAN as sysdba

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ON previous primary
SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;



New Primary (previous stand by )
ALTER DATABASE OPEN;
******************************************************

I have primary & standby with protection mode maximize availability
How can I vice versa the above process lets say my primary is down for maintenance & i want this as my primary again ?
one more thing is that reports are generated from standby right now so it mean active dg is configured.

Regards
  • 1. Re: manual switch over for maintenance
    saurabh Pro
    Currently Being Moderated
    you need to repeat the same process again.
  • 2. Re: manual switch over for maintenance
    P.Forstmann Guru
    Currently Being Moderated
    Fayn_dba wrote:

    How can I vice versa the above process lets say my primary is down for maintenance & i want this as my primary again ?
    You only need to run the same switchover steps with new standy and new primary database.
  • 3. Re: manual switch over for maintenance
    abdul: Newbie
    Currently Being Moderated
    hi,
    hope this help you

    http://www.visi.com/~mseberg/Data_Guard_switchover.html
  • 4. Re: manual switch over for maintenance
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    Please refer this http://shivanandarao.wordpress.com/2012/04/23/switchover-of-physical-standby-database/

    The same steps need to be followed in the same sequence
  • 5. Re: manual switch over for maintenance
    mseberg Guru
    Currently Being Moderated
    Hello;

    Try to remember if you databases are in sync its kind of like having one database in two different roles, Primary role and Standby role.

    Also if you want to do maintenance then don't issue this command after switchover, wait until the maintenance is complete and then issue it and Data Guard will catch up :
    alter system set log_archive_dest_state_2=enable;
    Once you have done a switchover its a good idea to do a log switch and make sure the new primary and standby are working correctly. Once you have done that the step to switch back are as follows : ( Do not use if you have Data Guard Broker setup )

    h3. Verify that it is possible to perform a switchover operation.
    SQL> select switchover_status from v$database;
    
    
    Note:All v$database switchover_status values are shown at the bottom of this page.
    
    If SWITCHOVER_STATUS returns SESSIONS ACTIVE then you should either
    disconnect all sessions manually or when performing step 2 you should append
    the with session shutdown clause. For example:
    
    SQL> alter database commit to switchover to standby with session shutdown;
    h3. Convert the primary database to the new standby
    SQL> alter database commit to switchover to standby with session shutdown;
    
    Note: This might take a few minutes.
    h3. Shutdown the former primary and mount as a standby database
    SQL> shutdown immediate
    
    Note: An ORA-01507: database not mounted is normal
    
    SQL> startup nomount
    SQL> alter database mount standby database;
    h3. Defer the remote archive destination on the old primary
    SQL> alter system set log_archive_dest_state_2=defer;
    h3. Verify that the physical standby can be converted to the new primary
    QL> select switchover_status from v$database;
    
    SWITCHOVER_STATUS
    --------------------
    TO PRIMARY
    
    Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 6.
    h3. Convert the physical standby to the new primary ( step 6 )
    SQL> alter database commit to switchover to primary;
    h3. Shutdown and startup the new primary

    SQL> shutdown immediate
    ORA-01109: database not open
    
    SQL> startup
    h3. Enable remote archiving on the new primary to the new standby
    SQL> alter system set log_archive_dest_state_2=enable;
    h3. Start managed recover on the new standby database
    SQL> recover managed standby database disconnect;
    Media recovery complete.
    SQL>
    
    
    Note: It’s a good idea to perform a log switch on the new primary.
    
    
    SQL>alter system switch logfile;
    Best Regards

    mseberg
  • 6. Re: manual switch over for maintenance
    Fayn_dba Newbie
    Currently Being Moderated
    Thanks ! for your detailed answer.


    Actually i my environment active dg is configured where!, from standby report are generated on almost every 5 min !
    The standby is in open mode.

    how i will bring my previous primary as standby in active or open mode for reporting purpose

    Hope I am able to explain
  • 7. Re: manual switch over for maintenance
    Shivananda Rao Guru
    Currently Being Moderated
    Once you do the switchover, you can place your standby database is open mode for reporting purpose.

    Open the standby database, and start the recovery.

    If mounted and recovery is in progress, then cancel it using below query.
    sql>alter database recover managed standby database cancel;
    Now open the standby database which would get opened in read-only mode.
    sql>alter database open;
    Start the Recovery:
    sql>alter database recover managed standby database disconnect from session;
    or
    sql>alter database recover managed standby database disconnect from session using current logfile;
    You can refer this http://shivanandarao.wordpress.com/2012/03/19/how-to-setup-active-dataguard-in-oracle-11g/
  • 8. Re: manual switch over for maintenance
    Fayn_dba Newbie
    Currently Being Moderated
    Thanks Boss!
    But here is another situation

    I want to make my physical standby as primary database

    lets suppose my primary is crashed & I am unable to run the following command

    alter database commit to switchover to physical standby with session shutdown;

    than how can I make my standby as primary

    i have also issued

    alter database recover managed standby database cancel; at standby

    alter database open;

    it open db but show physical standby
  • 9. Re: manual switch over for maintenance
    mseberg Guru
    Currently Being Moderated
    Hello;

    That would be a failover

    Please review my test notes here :

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

    Best Regards

    mseberg
  • 10. Re: manual switch over for maintenance
    Shivananda Rao Guru
    Currently Being Moderated
    When your primary database is crashed and you want to convert your standby database to primary database, then that would be called in as FAILOVER.

    You can refer this for the detailed steps on how to perform failover http://shivanandarao.wordpress.com/2012/08/28/dataguard-failover/

    Make sure that you would be testing out a switchover operation or a failover as they both are different. Switchover is a two way process where primary would behave as standby and standby as primary.

    Failover is a one way process where say your primary is crashed, you can convert your standby to primary database and you'll have to build a new standby for it if flashback is not enabled. You can refer the link mentioned above.
  • 11. ORA-00905 in failover
    Fayn_dba Newbie
    Currently Being Moderated
    Hi expert:


    I am getting error ORA-00905 when I issue the following command in failover

    ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

    I am using 10gR2

    is there any error in this command

    Regards & Thanks in Advance
  • 12. Re: ORA-00905 in failover
    abdul: Newbie
    Currently Being Moderated
    hello,
    you can refer this link:
    http://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ps.htm

    if you use dgmgrl then it should be differ comman.
  • 13. snapshot database
    Fayn_dba Newbie
    Currently Being Moderated
    Hi experts!
    11gR1 database is running. Active dataguard is configured in my environment.

    Can I shutdown my primary database & convert my standby database as snapshot database & then revert back snapshot to physical standby ?

    will it work instead of switchover ?

    Regards
    &
    Thanks

    & Also provide steps for active dg in 11gr1 as i want to do it for my practice.

    Edited by: Fayn_dba on Oct 10, 2012 2:47 AM
  • 14. Re: snapshot database
    abdul: Newbie
    Currently Being Moderated
    hello Fayn,

    you have marked this post as answered so now what you have to do is ask your remaining doubts on new post.
1 2 Previous Next

Legend

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