Monday, 8 May 2017

Oracle: Activate Standby Database (Failover/Primary lost situation)

Generally a in a disaster situation when primary is lost and standby needs to take over PRIMARY role.
(alternate Procedure at bottom)
This is a test run for this blog purpose only:

select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S
--------- -------------------- ---------------- -------
ALOK      MOUNTED              PHYSICAL STANDBY NONE

SQL> /

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S
--------- -------------------- ---------------- -------
ALOK      MOUNTED              PHYSICAL STANDBY NONE

SQL>  select inst_id,process, status, sequence#, block#,thread# from gv$managed_standby  order by 1,2,3;

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 MRP0      WAIT_FOR_LOG         24          0          1
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                 24         39          1

9 rows selected.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

SQL> select inst_id,process, status, sequence#, block#,thread# from gv$managed_standby  order by 1,2,3;

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0

SQL> select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S
--------- -------------------- ---------------- -------
ALOK      MOUNTED              PRIMARY          NONE

SQL> alter database open;   <=DB is ready for use

Database altered.

SQL> select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S
--------- -------------------- ---------------- -------
ALOK      READ WRITE           PRIMARY          NONE
----------------------Alternate Procedure
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shut immediate;
alter database open;-----opens in read only mode.(If not open or complains on more recovery, stop and recover more)
shut immediate;
startup mount
alter database activate standby database;
shutdown immediate


No comments:

Post a Comment