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