There are 2 role transitions which Oracle Data Guard supports
Switchover:
Switchover  allows the primary database to switch roles with one of its standby databases. There is no
data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role.
Failover:
Failover transitions a standby database to the primary role in response to a primary database failure. If
the primary database was not operating in either maximum protection mode or maximum availability
mode before the failure, some data loss may occur. After a failover, the failed database no longer
participates in the Data Guard configuration. It needs to be reinstated to become an active part of Data
Guard configuration.

Starting manual switchover
Query V$DATABASE to check the role of Primary Database

SELECT Database_role FROM v$Database;
DATABASE_ROLE
----------------
PRIMARY

Query V$DATABASE to check the role of Standby Database

SELECT Database_role FROM v$Database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

Check Switch Over status on Primary Database. It will show the same on Standby Database.

SELECT switchover_status FROM v$Database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Now on Primary execute the following command to convert the Primary database to standby

ALTER DATABASE Commit TO Switchover TO Physical Standby WITH SESSION Shutdown;
DATABASE altered.

Check the status of the Standby database now it will show PHYSICAL STANDBY

SELECT Database_role FROM v$Database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

Check the switchover status of the standby database

SELECT switchover_status FROM v$Database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY

Switchover the physical standby to primary
Alter Database Commit to Switchover to PRIMARY with session Shutdown;
Database altered.
Check the status of the standby database now. It should show as primary

SELECT Database_role FROM v$Database;
DATABASE_ROLE
----------------
PRIMARY

Shutdown and start the switched over standby  database to open state.

shut immediate
startup
SELECT STATUS FROM v$instance;
STATUS
------------
OPEN
SELECT database_role FROM v$database;
DATABASE_ROLE
----------------
PRIMARY

P.N : Now the main primary database will act as a standby. You can shutdown and start the database in mount state and start log shipping. Follow the above steps again to switch back to the main primary database.

SWITCHOVER USING DATAGUARD BROKER:
Check the configuration using dgmgrl

DGMGRL; SHOW CONFIGURATION
Configuration
Name:                dgbroker
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-START Failover : DISABLED
DATABASES:
prim - PRIMARY DATABASE
stan - Physical standby DATABASE
CURRENT STATUS FOR "dgbroker":
SUCCESS
DGMGRL;

Execute switchover command from primary database (PRIM to STAN)

DGMGRL; switchover TO STAN;
Performing switchover NOW, please wait...
Operation requires shutdown OF instance "PRIM" ON DATABASE "prim"
Shutting down instance "PRIM"...
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
Operation requires shutdown OF instance "STAN" ON DATABASE "stan"
Shutting down instance "STAN"...
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
Operation requires startup OF instance "PRIM" ON DATABASE "prim"
Starting instance "PRIM"...
Unable TO CONNECT TO DATABASE
ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor
Failed.
You are no longer connected TO ORACLE
Please CONNECT again.
Unable TO START instance "PRIM"
You must START instance "PRIM" manually
Operation requires startup OF instance "STAN" ON DATABASE "stan"
You must START instance "STAN" manually
Switchover  succeeded, NEW PRIMARY IS "stan"
DGMGRL;

From New Primary (STAN) run the below commands to confirm the successful switchover

startup
SELECT STATUS FROM v$instance;
STATUS
------------
OPEN
SELECT database_role FROM v$database;
DATABASE_ROLE
----------------
PRIMARY
SELECT name FROM v$database;
NAME
---------
PRIM
SELECT instance_name FROM v$instance;
INSTANCE_NAME
----------------
STAN

You can also check the new status using data guard broker

DGMGRL; CONNECT sys/oracle@STAN
Connected.
DGMGRL; SHOW configuration
Configuration
Name:                dgbroker
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-START Failover : DISABLED
DATABASES:
prim - Physical standby DATABASE
stan - PRIMARY DATABASE
CURRENT STATUS FOR "dgbroker":
SUCCESS
DGMGRL;

Re-run switchover command to convert the new primary(STAN) to physical standby

DGMGRL; switchover TO PRIM
Performing switchover NOW, please wait...
Operation requires shutdown OF instance "STAN" ON DATABASE "stan"
Shutting down instance "STAN"...
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
Operation requires shutdown OF instance "PRIM" ON DATABASE "prim"
Shutting down instance "PRIM"...
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
Operation requires startup OF instance "STAN" ON DATABASE "stan"
Starting instance "STAN"...
Unable TO CONNECT TO DATABASE
ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor
Failed.
You are no longer connected TO ORACLE
Please CONNECT again.
Unable TO START instance "STAN"
You must START instance "STAN" manually
Operation requires startup OF instance "PRIM" ON DATABASE "prim"
You must START instance "PRIM" manually
Switchover  succeeded, NEW PRIMARY IS "prim"
DGMGRL;

Check the status of new primary using dgmgrl

[oradev@erpdevdbs01 bdump]$ dgmgrl
DGMGRL FOR Linux: Version 10.2.0.4.0 - Production
Copyright (c) 2000, 2005, Oracle. ALL rights reserved.
Welcome TO DGMGRL, TYPE "help" FOR information.
DGMGRL; CONNECT sys/oracle@PRIM
Connected.
DGMGRL; SHOW configuration
Configuration
Name:                dgbroker
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-START Failover : DISABLED
DATABASES:
prim - PRIMARY DATABASE
stan - Physical standby DATABASE
CURRENT STATUS FOR "dgbroker":
SUCCESS
DGMGRL;

FAILOVER USING DATAGUARD BROKER:
Failover is required when primary site goes down or if there is any failure.Failure results in to a dataloss. Once switched over to standby users need to reneter the data which has been lost.

To failover to standby execute the below command from STANDBY database.
P.N : Without shutting the primary database.

DGMGRL; CONNECT sys/oracle@STAN
Connected.
DGMGRL; switchover TO STAN
Performing switchover NOW, please wait...
Operation requires shutdown OF instance "PRIM" ON DATABASE "prim"
Shutting down instance "PRIM"...
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
Operation requires shutdown OF instance "STAN" ON DATABASE "stan"
Shutting down instance "STAN"...
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
Operation requires startup OF instance "PRIM" ON DATABASE "prim"
Starting instance "PRIM"...
Unable TO CONNECT TO DATABASE
ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor
Failed.
You are no longer connected TO ORACLE
Please CONNECT again.
Unable TO START instance "PRIM"
You must START instance "PRIM" manually
Operation requires startup OF instance "STAN" ON DATABASE "stan"
You must START instance "STAN" manually
Switchover  succeeded, NEW PRIMARY IS "stan"
DGMGRL;

Failover to Standby Database by shutting down the primary database.
Primary Database = STAN
Standby Database = PRIM

DGMGRL; failover to PRIM
Performing failover NOW, please wait...
Failover succeeded, new primary is "prim"
DGMGRL;

Check the status after fail over

DGMGRL; SHOW configuration
Configuration
Name:                dgbroker
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-START Failover : DISABLED
DATABASES:
prim - PRIMARY DATABASE
stan - Physical standby DATABASE (disabled)
CURRENT STATUS FOR "dgbroker":
SUCCESS
DGMGRL;
Tagged with →  
Share →
0 comments
Skip to toolbar