Switch Primary to Standby in Oracle Dataguard

 Switch Primary to Standby in Oracle Dataguard

Check Primary details

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

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

TEST      READ WRITE           PRIMARY

Check Error on primary

select status, gap_status from v$archive_dest_status where dest_id=2;

Check Gap on standby

set linesize 190

col name for 20

col value for a20

select name,value,datum_time from v$dataguard_stats;

Check Switch over status Primary

select switchover_status from v$database;

Stop mrp in standby

alter database recover managed standby database cancel;

Check mrp status

select process,status,SEQUENCE# from v$managed_standby;

Primary to convert standby

alter database commit to switchover to physical standby with session shutdown;

Database altered.

startup primary in mount

startup mount

Database altered.

Check Primary database status

select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

TEST      MOUNTED              PHYSICAL STANDBY

Check standby status

select switchover_status from v$database;

standby to primary

alter database commit to switchover to primary with session shutdown;

In Case of issue with above command:

alter database commit to switchover to primary with session shutdown

*

ERROR at line 1:

ORA-16139: media recovery required

RECOVER MANAGED STANDBY DATABASE FINISH;

alter database commit to switchover to primary with session shutdown;

standby to open database

alter database open;

Check status in new standby

select process,status,SEQUENCE# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED             0

DGRD      ALLOCATED             0

DGRD      ALLOCATED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

6 rows selected.

Start MRP in new standby

alter database recover managed standby database disconnect from session;

Database altered.

check mrp status in new standby 

select process,status,SEQUENCE# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED             0

DGRD      ALLOCATED             0

DGRD      ALLOCATED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

MRP0      WAIT_FOR_LOG         27

7 rows selected.

Check Primary and standby role after transition

select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

TEST      MOUNTED              PHYSICAL STANDBY