This discussion is archived
14 Replies Latest reply: May 29, 2013 6:06 AM by shipon_97 RSS

Convert Standby to primary role and then again turn to standby !

shipon_97 Newbie
Currently Being Moderated
Dear Friends ,

I am using oracle11g Active dataguard . My primary database is physycally corrupted , so I make the standby database to primary as well as READ-WRITE mode using the below procedure :

https://blogs.oracle.com/AlejandroVargas/resource/How-to-open-the-standby-when-the-primary-is-lost.pdf

Now my standby turns to the PRIMARY role DB . after few moment , I recover my previous PRIMARY DB which is corrupted physically .

Now My question is , is it possible to make the present PRIMARY DB to standby DB again ? or I need to create standby newly ?

Please help me to give me the procedure .


Waiting for kind reply ... ...
  • 1. Re: Convert Standby to primary role and then again turn to standby !
    MahirM.Quluzade Guru
    Currently Being Moderated
    Hi,
    shipon_97 wrote:
    Dear Friends ,

    I am using oracle11g Active dataguard . My primary database is physycally corrupted , so I make the standby database to primary as well as READ-WRITE mode using the below procedure :

    https://blogs.oracle.com/AlejandroVargas/resource/How-to-open-the-standby-when-the-primary-is-lost.pdf
    Did your priamry database is lost? Why you are used this?

    If yes, then you must create standby database for new primary database. I no you old primary database runing and flashback enabled, then you reinstate
    you primary database to new standby database.
    >
    Now my standby turns to the PRIMARY role DB . after few moment , I recover my previous PRIMARY DB which is corrupted physically .

    Now My question is , is it possible to make the present PRIMARY DB to standby DB again ? or I need to create standby newly ?
    You can create new physical standby database and make SWITCHOVER to new standby database.

    Regards
    Mahir M. Quluzade
  • 2. Re: Convert Standby to primary role and then again turn to standby !
    rarain Explorer
    Currently Being Moderated
    Hi,

    Yes it is possible to make your recovered old primary database as standby database provided flashback database is enabled. Please check and let me know whether Flashback database is enabled or not else you have only option of recreating standby database for new primary database.

    select flashback_on from v$database;

    Thanks
  • 3. Re: Convert Standby to primary role and then again turn to standby !
    Victor Armbrust Oracle ACE
    Currently Being Moderated
    you should use Flashback technology to do that. You can also use REINSTATE from Broker, however you need to make sure your configurations are fine and recover the Primary.
  • 4. Re: Convert Standby to primary role and then again turn to standby !
    shipon_97 Newbie
    Currently Being Moderated
    Thx friend for replies .


    No , my flashback feature is not enabled .

    But If FLASHBACK is open then how can I change my NEW PRIMARY to STANDBY DB .

    Waiting for kind reply ...
  • 5. Re: Convert Standby to primary role and then again turn to standby !
    rarain Explorer
    Currently Being Moderated
    Hi,

    You can reinstate the old primary database after failover using Data Guard Broker or Manually.

    For Manual Reinstate refer this link:- http://docs.oracle.com/cd/E11882_01/server.112/e25608/scenarios.htm#i1050055

    Reinstate using Broker refer this link:- http://docs.oracle.com/cd/E11882_01/server.112/e17023/sofo.htm#DGBKR3406

    Thanks
  • 6. Re: Convert Standby to primary role and then again turn to standby !
    shipon_97 Newbie
    Currently Being Moderated
    Thx rarain ,

    according your first docs link , I change the New PRIMARY server to the STANDBY using below procedure :


    > SQL> SHUTDOWN IMMEDIATE;

    > SQL> STARTUP MOUNT;

    > ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

    > SQL> SHUTDOWN IMMEDIATE;

    > SQL> STARTUP MOUNT;

    Then I check from my DB , it changes to the standby mode :
    SQL> SELECT database_role, open_mode,switchover_status,PROTECTION_MODE FROM v$database;
    DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS PROTECTION_MODE
    ---------------- -------------------- -------------------- --------------------
    PHYSICAL STANDBY MOUNTED RECOVERY NEEDED MAXIMUM PERFORMANCE
    And ,
    select controlfile_type from v$database;

    CONTROL
    -------
    STANDBY
    And after then I start the REAL TIME APPLY using the below query :
    alter database recover managed standby database using current logfile disconnect;
    But problem is , real time apply is not working .When I make several LOG SWITCH in new PRIMARY DB (which was crashed before) , but not working . It shows the below error from alert log of NEW PRIMARY DB :

    ******************************************************************
    LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
    ******************************************************************
    Wed May 29 16:24:18 2013
    Archived Log entry 122 added for thread 1 sequence 70 ID 0xf4e80e7f dest 1:
    LNS: Standby redo logfile selected for thread 1 sequence 71 for destination LOG_ARCHIVE_DEST_2
    Wed May 29 16:24:18 2013
    ARC3: Standby redo logfile selected for thread 1 sequence 70 for destination LOG_ARCHIVE_DEST_2
    Wed May 29 16:24:41 2013
    LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
    LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    Error 3135 for archive log file 2 to 'stan'
    Errors in file /u01/app/oracle/diag/rdbms/prim/prim/trace/prim_nsa2_6916.trc:
    ORA-03135: connection lost contact
    LNS: Failed to archive log 2 thread 1 sequence 71 (3135)
    The above trace file shows :
    *** 2013-05-29 16:24:41.371
    krsb_stream_send: Error 3135 sending stream IOV
    RFS network connection lost at host 'stan' error 3135
    Error 3135 writing standby archive log file at host 'stan'
    *** 2013-05-29 16:24:41.371 4320 krsh.c
    LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
    *** 2013-05-29 16:24:41.371 4320 krsh.c
    LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    *** 2013-05-29 16:24:41.371 4320 krsh.c
    Error 3135 for archive log file 2 to 'stan'
    *** 2013-05-29 16:24:41.371 2932 krsi.c
    krsi_dst_fail: dest:2 err:3135 force:0 blast:1
    ORA-03135: connection lost contact
    Closing Redo Read Context
    Here I see "connection is lost " , but I make test connectivity using tnsping and ping , but dont get any connectivity problem .

    Plese give me a solution .. ...

    Edited by: shipon_97 on May 29, 2013 4:25 PM
  • 7. Re: Convert Standby to primary role and then again turn to standby !
    MahirM.Quluzade Guru
    Currently Being Moderated
    shipon_97 wrote:
    Thx rarain ,

    according your first docs link , I change the New PRIMARY server to the STANDBY using below procedure :

    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP MOUNT;
    ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP MOUNT;
    Then I check from my DB , it changes to the standby mode :
    SQL> SELECT database_role, open_mode,switchover_status,PROTECTION_MODE FROM v$database;
    DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS PROTECTION_MODE
    ---------------- -------------------- -------------------- --------------------
    PHYSICAL STANDBY MOUNTED RECOVERY NEEDED MAXIMUM PERFORMANCE
    And ,
    select controlfile_type from v$database;

    CONTROL
    -------
    STANDBY
    And after then I start the REAL TIME APPLY using the below query :
    alter database recover managed standby database using current logfile disconnect;
    But problem is , real time apply is not working .When I make several LOG SWITCH in new PRIMARY DB (which was crashed before) , but not working . It shows the below error from alert log of NEW PRIMARY DB :


    ******************************************************************
    LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
    ******************************************************************
    Wed May 29 16:24:18 2013
    Archived Log entry 122 added for thread 1 sequence 70 ID 0xf4e80e7f dest 1:
    LNS: Standby redo logfile selected for thread 1 sequence 71 for destination LOG_ARCHIVE_DEST_2
    Wed May 29 16:24:18 2013
    ARC3: Standby redo logfile selected for thread 1 sequence 70 for destination LOG_ARCHIVE_DEST_2
    Wed May 29 16:24:41 2013
    LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
    LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    Error 3135 for archive log file 2 to 'stan'
    Errors in file /u01/app/oracle/diag/rdbms/prim/prim/trace/prim_nsa2_6916.trc:
    ORA-03135: connection lost contact
    LNS: Failed to archive log 2 thread 1 sequence 71 (3135)

    The above trace file shows :

    *** 2013-05-29 16:24:41.371
    krsb_stream_send: Error 3135 sending stream IOV
    RFS network connection lost at host 'stan' error 3135
    Error 3135 writing standby archive log file at host 'stan'
    *** 2013-05-29 16:24:41.371 4320 krsh.c
    LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
    *** 2013-05-29 16:24:41.371 4320 krsh.c
    LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    *** 2013-05-29 16:24:41.371 4320 krsh.c
    Error 3135 for archive log file 2 to 'stan'
    *** 2013-05-29 16:24:41.371 2932 krsi.c
    krsi_dst_fail: dest:2 err:3135 force:0 blast:1
    ORA-03135: connection lost contact
    Closing Redo Read Context


    Plese give me a solution .. ...
    Hi,

    You must check, log_archive_dest_2 parameter is correct or not.
    Can you paste here, you parameter files from both side?

    And Can you paste here below script results?
     
    select max(sequence#)  from  v$archived_log; --  both side
    select max(sequence#)  from  v$archived_log where applied='YES'; --  on standby
    
    select current_scn from  v$database;  -- on both side
    
    select process from v$managed_standby;  -- on both side
    Mahir M. Quluzade
  • 8. Re: Convert Standby to primary role and then again turn to standby !
    rarain Explorer
    Currently Being Moderated
    Hi ,

    Did you read the Doc properly...This document explains you how to Flashing Back a Failed Primary Database into a Physical Standby Database after Failover, that means you suppose to perform these steps on failed primary database which is now recovered after failover, not on the New Primary Database. Your below statement making me confuse whether you are performing these steps on NEW primary or FAILED PRIMARY after Failover.

    {according your first docs link , I change the New PRIMARY server to the STANDBY using below procedure}

    Thanks
  • 9. Re: Convert Standby to primary role and then again turn to standby !
    MahirM.Quluzade Guru
    Currently Being Moderated
    shipon_97 wrote:
    Thx friend for replies .


    No , my flashback feature is not enabled .

    But If FLASHBACK is open then how can I change my NEW PRIMARY to STANDBY DB .

    Waiting for kind reply ...
    I read your this reply now,

    If you not enabled Flashback, then you can not reinstate database as new standby.
    Because you must use
    SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;
    but flashback is not enabled.

    You must create physical standby, again.

    Or you can use below steps.
     
     1. Backup current control file for standby on new primary. 
     2. Backup database on new primary . 
     3. Restore standby  controlfile, mount. 
     4. Restore database.  
     
     or 
      RMAN Duplicate from active database on primary duplicate for standby to new standby server. 
    Regards
    Mahir M. Quluzade

    Edited by: Mahir M. Quluzade on May 29, 2013 3:50 PM
  • 10. Re: Convert Standby to primary role and then again turn to standby !
    shipon_97 Newbie
    Currently Being Moderated
    select max(sequence#) from v$archived_log; -- both side
    (primary)

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

    MAX(SEQUENCE#)
    --------------
    86

    (standby)

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

    MAX(SEQUENCE#)
    --------------
    86
    select max(sequence#) from v$archived_log where applied='YES'; -- on standby


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

    MAX(SEQUENCE#)
    --------------
    83
    select current_scn from v$database; -- on both side

    (primary)

    select current_scn from v$database;SQL>

    CURRENT_SCN
    -----------
    1330662
    (standby)
    select current_scn from v$database;SQL>
    CURRENT_SCN
    -----------
    1311071
    (primary)

    select process from v$managed_standby; -- on both side

    SQL> select process from v$managed_standby;

    PROCESS
    ---------
    ARCH
    ARCH
    ARCH
    ARCH
    LNS

    SQL>

    (standby)
    SQL> select process from v$managed_standby;

    PROCESS
    ---------
    ARCH
    ARCH
    ARCH
    ARCH
    ARCH
    RFS
    RFS
    RFS

    8 rows selected.
  • 11. Re: Convert Standby to primary role and then again turn to standby !
    MahirM.Quluzade Guru
    Currently Being Moderated
    Can you say , after alter system switch logfile, transport service is send archived logs to standby?
    select max(sequence#)  from  v$archived_log;  -- on both side 
    alter system switch logfile; -- on primary 
    
    select max(sequence#)  from  v$archived_log; -- on both side 
    Can you start MRP on standby side?
    alter database recover managed standby database using current logfile disconnect from  session; 
    or
    alter database recover managed standby database disconnect from  session; 
    Mahir

    Edited by: Mahir M. Quluzade on May 29, 2013 5:05 PM
  • 12. Re: Convert Standby to primary role and then again turn to standby !
    shipon_97 Newbie
    Currently Being Moderated
    yes mahir,

    I give the above command but not real time updated .

    And when I make LOG SWITCH in PRIMARY node then I got the error in alert log which I mention before ..
  • 13. Re: Convert Standby to primary role and then again turn to standby !
    MahirM.Quluzade Guru
    Currently Being Moderated
    shipon_97 wrote:
    yes mahir,

    I give the above command but not real time updated .

    And when I make LOG SWITCH in PRIMARY node then I got the error in alert log which I mention before ..
    Can you paste here parameter file both side?
    Did you created Standby redo logs? For Real Time Apply require standby redo logs.


    Mahir
  • 14. Re: Convert Standby to primary role and then again turn to standby !
    shipon_97 Newbie
    Currently Being Moderated
    No I did not create any standby log newly in any of the database (both PRIMARY & STANDBY) . I just give you the present parameter
    and standby parameter list in the below :


    (PRIMARY)

    SQL> show parameter log_archive

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config string dg_config=(prim,stan)


    log_archive_dest_2 string service=stan async valid_for=(
    online_logfiles,primary_role)
    db_unique_name=stan


    (STANDBY)

    SQL> show parameter log_arc

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config string dg_config=(prim,stan)


    log_archive_dest_2 string service=prim ASYNC valid_for=
    (ONLINE_LOGFILE,PRIMARY_ROLE)
    db_unique_name=prim

    STANDBY LOG STATUS :
    ====================
    (PRIMARY)

    SQL> select GROUP#,DBID,STATUS,BYTES from v$standby_log;

    GROUP# DBID STATUS BYTES
    ---------- ---------------------------------------- ---------- ----------
    4 UNASSIGNED UNASSIGNED 104857600
    5 UNASSIGNED UNASSIGNED 104857600
    6 UNASSIGNED UNASSIGNED 104857600

    (STANDBY)

    SQL> select GROUP#,DBID,STATUS,BYTES from v$standby_log;

    GROUP# DBID STATUS BYTES
    ---------- ---------------------------------------- ---------- ----------
    4 4108849023 ACTIVE 104857600
    5 UNASSIGNED UNASSIGNED 104857600
    6 UNASSIGNED UNASSIGNED 104857600

Legend

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