perform Manual Switchover and Failover on Physical Standby

1.Perform Manual Switchover on Physical Standby

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

Connect to test database via client and keep querying below

sqlplus sys/sys@test as sysdba

 

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

 

NAME      OPEN_MODE      DB_UNIQUE_NAME         DATABASE_ROLE

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

test    READ WRITE     test                 PRIMARY

Check primary and standby for any gaps

On primary:

===========

select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

 

On standby:

===========

select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;

We will first convert primary to standby and later standby to primary

On primary:

===========

select SWITCHOVER_STATUS from V$DATABASE;

You must see TO STANDBY or SESSIONS ACTIVE

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

 

SQL>startup mount;

At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary.

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

Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary

select SWITCHOVER_STATUS from V$DATABASE;

 

SQL>alter database commit to switchover to primary with session shutdown;

 

SQL> alter database open;

At this stage, the client query would execute successfully!

On new standby – Initially your primary database: Start MRP

SQL>alter database recover managed standby database disconnect;

Revert back: Once again follow the above process from top and re-execute steps in proper databases to revert back to original setup.

2. Performing Manual Failover on Physical Standby

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

Failover is when your primary database is completely lost. When there is a failover, standby is converted into primary but primary is not converted into standby as it is lost. If you do not have Flashback enabled on primary, you must re-create primary from scratch (Using RMAN duplicate method). In this example, we have already enabled flashback on both primary and standby.

 

Our current physical standby server overview

Database SID	Database Role
Test	Primary Database
test_s	Physical Standby

Crash Primary database

Let’s crash primary (test): In order to simulate failure, we will shut down the primary server test. As root user, shutdown the server without shutting down DB.

Execute query on client: At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary

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

3. Perform Failover to Standby

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

On standby:     switch physical standby to primary

===========

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

then finish the database recovery and is no more required

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

at this stage database is ready to be open and converted to primary so if you don't finish it it won't be able to be converted to primary. here how we check it

SQL> select SWITCHOVER_STATUS from V$DATABASE;

You must see TO PRIMARY or SESSIONS ACTIVE. Switch standby to primary

SQL> alter database commit to switchover to primary with session shutdown;

SQL> alter database open;

Check client query: Check the query you executed in step 2 on client, it must get executed