This discussion is archived
6 Replies Latest reply: Dec 11, 2012 7:24 AM by LaserSoft RSS

Primary Standby Configuration Oracle 11.2.0.1.0

940673 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    You are welcome my friend

    Glad to help :D

    Victor
  • 6. Re: Primary Standby Configuration Oracle 11.2.0.1.0
    LaserSoft Journeyer
    Currently Being Moderated
    Excellent Post Victor.

Legend

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