6 Replies Latest reply: Dec 11, 2012 9:24 AM by LaserSoft RSS

    Primary Standby Configuration Oracle 11.2.0.1.0

    940673
      Hi All,

      I have a primary and physical standby configuration. I am planning a switchover between primary and standby. Both my primary and standby is in sync now and I have done few steps which is mentioned below:-

      In Primary:-

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

      MAX(SEQUENCE#)
      --------------
      146

      In standby:-

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

      MAX(SEQUENCE#)
      --------------
      146

      My Primary database switchover_status is showing "To Standby" but my standby switchover_status is showing "Not Allowed". Please suggest in this circumstance can I go for a switchover. I am waiting for all your inputs.

      Regards,
      Arijt
        • 1. Re: Primary Standby Configuration Oracle 11.2.0.1.0
          861120
          Please check if the parameters in the standby is corret like above:

          log_archive_dest_2='SERVICE=prim LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim'
          log_archive_config='DG_CONFIG=(prim,stdby)'
          fal_client=stdby
          fal_server=prim
          • 2. Re: Primary Standby Configuration Oracle 11.2.0.1.0
            Shivananda Rao
            Hello,

            During switchover, first you need to perform the switchover on primary and then on the standby database.

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



            Regards,
            Shivananda
            • 3. Re: Primary Standby Configuration Oracle 11.2.0.1.0
              Victor Armbrust
              Hello
              That's is the correct situation. Primary Switchover to Stand By and Stand By not allowed.

              Follow some steps to make the switchover:


              --IF YOU DON't HAVE THE BROKER CONFIGURED:

              VALIDATE ON PRIMARY AND STANDBY

              SQL> select name, db_unique_name, open_mode, database_role from v$database;


              VALIDATE THE LGWR APPLY (ON ALL NODES IN CASE OF RAC)

              SQL> SELECT client_process, process, sequence#, status from v$managed_standby;


              VALIDATE THE LOG SEQUENCE (IT DOES NOT NECESSARY MEANS THAT YOUR DG IS OK)

              select thread#, max(sequence#) from v$log
              group by thread#;


              VALIDATE ALERT.LOG

              Check if the last SEQ# is applied fine.


              PREPARE THE STANDBY DATABASE


              SQL>alter database recover managed standby database using current logfile disconnect;

              SQL> select name, db_unique_name, open_mode, database_role from v$database;


              KEEP ONLY ONE INSTANCE RUNNING ON PRIMARY (BEST PRATICE)
              It is not a requirement, however I usually keep one NODE RUNING on PRIMARY and one NODE ON STANDBY (CASE OF RAC)


              MAKE THE SWITCHOVER OF PRIMARY (PRIMARY -> STANDBY)

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


              VALIDATE THE ALERT.LOG (needs to appears the following message on ALERT of PRIMARY)

              ARCH: Noswitch archival of thread 1, sequence 329
              ARCH: End-Of-Redo Branch archival of thread 1 sequence 329
              ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2


              VALIDATE SWITCHOVER STATUS OF STANDBY (SHOULD BE TO PRIMARY)

              SQL> SELECT switchover_status from v$database;

              SWITCHOVER_STATUS
              --------------------
              TO PRIMARY


              VALIDATE SWITCHOVER STATUS OF PRIMARY (SHOULD BE TO RECOVERY NEEDED)

              SQL> SELECT switchover_status from v$database;

              SWITCHOVER_STATUS
              --------------------
              RECOVERY NEEDED


              COMMIT STANDBY TO PRIMARY

              SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;


              OPEN STANDBY AS R&W

              SQL> ALTER DATABASE OPEN;

              Database altered.


              PREPARE PRIMARY TO GET STANDBY ROLE

              SQL> SHUTDOWN IMMEDIATE;
              ORA-01092: ORACLE instance terminated. Disconnection forced
              SQL>
              SQL> conn /as sysdba
              Connected to an idle instance.
              SQL>
              SQL> STARTUP MOUNT;
              ORACLE instance started.

              Total System Global Area 1046224896 bytes
              Fixed Size 1348796 bytes
              Variable Size 444599108 bytes
              Database Buffers 595591168 bytes
              Redo Buffers 4685824 bytes
              Database mounted.
              SQL>
              SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

              Database altered.


              VALIDATE IF PRIMARY IS OK (APPLYING LOG)

              SQL> select process,status,client_process,sequence#
              from v$managed_standby;

              PROCESS STATUS CLIENT_P SEQUENCE#
              --------- ------------ -------- ----------
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              ARCH CONNECTED ARCH 0
              MRP0 APPLYING_LOG N/A 335
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE UNKNOWN 0
              RFS IDLE LGWR 335


              ###THE END###


              --IF YOU HAVE THE BROKER CONFIGURED: (MUCH MORE EASY)

              BE SURE IF ALL CONFIGURATION IS OK FOR PRIMARY
              There's a lot of configuration here, if you need to configure it, I recommend to read the oficial Documentation of DG.

              DGMGRL> SHOW DATABASE db

              Database - ormtp1

              Role: PRIMARY
              Intended State: TRANSPORT-ON
              Instance(s):
              db1
              db2
              db3
              db4

              Database Status:
              SUCCESS

              LOG SWITCH ON PRIMARY(IT IS NOT NECESSARY, ITs just a BEST PRATICE)

              SQL> alter system switch logfile;

              System altered.


              DOUBLE CHECK IN LOG SEQ# IN BOTH DATABASES (PRIMARY / STANDBY)


              SQL> select thread#, max(sequence#) from v$log group by thread#;

              THREAD# MAX(SEQUENCE#)
              ---------- --------------
              1 166
              2 130
              3 36
              4 41


              SQL> select thread#, max(sequence#) from v$log group by thread#;

              THREAD# MAX(SEQUENCE#)
              ---------- --------------
              1 166
              2 130
              3 36
              4 41


              TAIL ON ALERT.LOG (JUST TO MONITORING FOR ERRORS)


              dgmgrl sys/oracle@db

              DGMGRL> SWITCHOVER TO dbd;
              Performing switchover NOW, please wait...
              New primary database "dbd" is opening...
              Operation requires shutdown of instance "db1" on database "db"
              Shutting down instance "db1"...
              ORACLE instance shut down.
              Operation requires startup of instance "db1" on database "db"
              Starting instance "db1"...
              ORACLE instance started.
              Database mounted.
              Switchover succeeded, new primary is "dbd"
              DGMGRL>

              DGMGRL> show configuration

              Configuration - dgcfg

              Protection Mode: MaxPerformance
              Databases:
              dbd - Primary database
              db - Physical standby database

              Fast-Start Failover: DISABLED

              Configuration Status:
              SUCCESS


              VALIDATE ROLE FOR STANDBY

              SQL> select database_role from v$database;

              DATABASE_ROLE
              ----------------
              PRIMARY


              VALIDATE ROLE FOR PRIMARY

              SQL> select database_role from v$database;

              DATABASE_ROLE
              ----------------
              PHYSICAL STANDBY


              VALIDATE STATUS OF LOG APPLYING ON NEW STANDBY (PRIOR PRIMARY)

              SQL> SELECT client_process, process, thread#, sequence#, status from v$managed_standby;

              CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
              -------- --------- ---------- ---------- ------------
              ARCH ARCH 0 0 CONNECTED
              ARCH ARCH 0 0 CONNECTED
              ARCH ARCH 0 0 CONNECTED
              ARCH ARCH 0 0 CONNECTED
              ARCH ARCH 1 175 CLOSING
              ARCH ARCH 2 136 CLOSING
              ARCH ARCH 0 0 CONNECTED
              ARCH ARCH 0 0 CONNECTED
              ARCH ARCH 0 0 CONNECTED
              ARCH ARCH 0 0 CONNECTED
              N/A MRP0 2 137 APPLYING_LOG

              CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
              -------- --------- ---------- ---------- ------------
              ARCH RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE
              UNKNOWN RFS 0 0 IDLE

              CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
              -------- --------- ---------- ---------- ------------
              LGWR RFS 1 176 IDLE
              UNKNOWN RFS 0 0 IDLE
              LGWR RFS 2 137 IDLE



              ##THE END ###

              Just to remember: TO FALLBACK ROLES, you just need to make the inverse procedure.

              As I said above, there is a lot of steps you need to configure DG and BROKER, however the SWITCHOVER procedure is basically as I describe above.


              Hope it helps

              Victor
              • 4. Re: Primary Standby Configuration Oracle 11.2.0.1.0
                940673
                HI Victor,

                Thanks a ton for your help. I have done the switchover gracefully..

                Regards,
                Arijit
                • 5. Re: Primary Standby Configuration Oracle 11.2.0.1.0
                  Victor Armbrust
                  You are welcome my friend

                  Glad to help :D

                  Victor
                  • 6. Re: Primary Standby Configuration Oracle 11.2.0.1.0
                    LaserSoft
                    Excellent Post Victor.