Once the standby database is configured and works fine then we can switchover to standby database for testing purpose to reduce the primary database downtime .Primary database may need down-time for many reasons like OS upgradation, Hardwares upgradation and for many other issues .
Whenever we switchover the primary database to standby database , there is no loss of data during the switchover. Once the maintainance of the primary database is over , then we can again switchover to standby database.
In this scenario , the Primary database is "NOIDA" and standby database is "RED". Here i will switchover the primary database to standby database i.e, from "noida" to "red". Before switching, we should check some prerequisites .
Step 1 : Verify whether it is possible to perform a switchover
On the current primary database, query the "switchover_status" column of the V$DATABASE fixed view on the primary database to verify it is possible to perform switchover.
SQL>
select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY
The
TO STANDBY value in the "
switchover_status" column indicates that it is possible to switch the primary database to the standby role. If the TO STANDBY value is not displayed, then verify the configuration is functioning correctly . (for example, verify all "
log_archive_dest_n" parameter values are specified correctly). If the value in the
switchover_status column is SESSIONS ACTIVE or FAILED DESTINATION then
click here .
SQL>
select distinct osuser,username from v$session;
Step 3 : Switch the current online redo log file on primary database and verify that it has been appleid
SQL>
alter system switch logfile ;
System altered.
Step 4 : Connect with primary database and initiate the switchover
C:\>
sqlplus sys/xxxx@noida as sysdba
SQL>
alter database commit to switchover to physical standby;
Database altered.
Now, the primary database is converted into standby database.The controlfile is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file,if necessary.
If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :
SQL>alter database commit to switchover to standby ;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances
In order to perform a switchover, run below command on the primary database.
SQL>alter database commit to switchover to physical standby with session shutdown ;
The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log file that was archived.A backup of current control file is created and the current control file is converted into a standby control file.
Step 5 : Shut down and restart the primary instance(RED).
SQL>
shutdown immediate;
SQL>
startup mount ;
Step 6 : Verify the switchover status in the v$database view.
After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "switchover_status" column of the v$database fixed view on the target standby database.
On
old Primary database(noida)
SQL>
select name,open_mode,db_unique_name from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS
------ ----------- -------------------- ----------------------
NOIDA MOUNTED noida TO PRIMARY
On
old standby database (RED)
SQL>
select name,open_mode,db_unique_name,switchover_status from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS
------ ------------ ----------------- ---------------------
NOIDA MOUNTED red TO PRIMARY
Step 8 : Switch the target physical standby database role to the primary role
We can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following sql statement on the physical standby database that we want to change to the primary role:
SQL>
alter database commit to switchover to primary ;
Database altered.
SQL>
shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
startup
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 213911796 bytes
Database Buffers 41943040 bytes
Redo Buffers 6410240 bytes
Database mounted.
Database opened.
Step 9 : Check the new primary database(RED) and switch logfile :
SQL>
select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE
Note : it's a good idea to perform a log switch on the primary .
SQL>
alter system switch logfile;
System altered.
Step 10 : Open new standby database(Noida) in read-write
SQL>
alter database open;
Database altered
SQL>
select name,open_mode ,db_unique_name,switchover_status from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS
------ ------------- ------------------ -----------------------
NOIDA READ ONLY noida RECOVERY NEEDED
SQL>
alter database recover managed standby database disconnect from session;
Database altered.
SQL>
select name,open_mode from v$database;
NAME OPEN_MODE
--------- ---------------------------------
NOIDA READ ONLY WITH APPLY
The
switchover_status column of
v$database can have the following values:
Not Allowed : Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
Session Active : Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
Switchover Pending : This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent : The switchover was in pending mode, but did not complete and went back to the primary database.
To Primary : This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
To Standby : This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
Recovery Needed : This is a standby database that has not received the switchover request.
Enjoy
:-)
