14 Replies Latest reply: May 29, 2013 8:06 AM by shipon_97 RSS

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

    shipon_97
      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 !
          Mahir M. Quluzade
          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
            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
              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
                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
                  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
                    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 !
                      Mahir M. Quluzade
                      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
                        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 !
                          Mahir M. Quluzade
                          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
                            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 !
                              Mahir M. Quluzade
                              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
                                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 !
                                  Mahir M. Quluzade
                                  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
                                    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