A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
Setup physical standby database
After completing physical standby database creation.
standby database creation
Follow below steps to switchover database from primary to standby and vice versa
SETUP:
PRIMARY (Primary Database with
connection string as “TESTDB”)[oracle@NVMBD1BZY150D00 301214]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.3.0 Production on Mon Jan 5 15:33:44 2015
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
Connected
to an idle instance.
SQL> select instance_name,status,database_role from
v$database,v$instance;
----------------
------------ ----------------
TESTDB OPEN PRIMARY
STANDBY (Secondary Database
with connection string as “STANDBY”)
SQL> select instance_name,status,database_role from
v$database,v$instance;
----------------
------------ ----------------
STANDBY OPEN PHYSICAL STANDBY
Archivelog
Destination 1 of PRIMARY:
SQL> sho parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
log_archive_dest_1 string LOCATION=/data1/archive/testdb VALID_
FOR= (ALL_LOGFILES, ALL_ROLES) D
B_UNIQUE_NAME=TESTDB
Archivelog
Destination 2 of PRIMARY:
SQL> sho parameter log_archive_dest_2
------------------------------------
----------- ------------------------------
log_archive_dest_2 string SERVICE=STANDBY VALID_FOR=(ONL
INE_LOGFILES,
PRIMARY_ROLE) DB_
UNIQUE_NAME=STANDBY
Archivelog Destination 1 of
Standby database is “/data1/archive/STANDB” and archivelog destination 2 of
standby database is pointing to the service-name of the primary database.
(Note: destination 2 in standby database is not mandatory. It is required only
if the standby database would be running in as primary database during
switchover or failover. Hence it would be a good practice to set this parameter
to avoid problems during the switchover or failover.)
Archive log
destination 1 of STANDBY:
SQL> sho parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
log_archive_dest_1 string LOCATION=/data1/archive/STANDB
Y VALID_FOR=(ALL_LOGFILES,ALL_
ROLES) DB_UNIQUE_NAME=STANDBY
Archive log
destination 2 of STANDBY:
SQL> sho parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
log_archive_dest_2 string SERVICE=TESTDB VALID_FOR=(ONLI
NE_LOGFILES,PRIMARY_ROLE) DB_U
NIQUE_NAME=TESTDB
Pre-Switchover
Checks:
These checks will have to be
performed before the switchover activity is performed.
Verify whether
Managed Recovery process is running on the standby database
Use the following query to
check if the managed recovery process is running on the standby database.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS
SEQUENCE#
---------
------------ ----------
ARCH CLOSING 157ARCH CLOSING 155
ARCH CONNECTED 0
ARCH CLOSING 156
RFS IDLE 0
RFS IDLE 0
RFS IDLE 158
MRP0 WAIT_FOR_LOG 158
The above fig. shows that the
Managed Recovery Process (MRP0) is running on the standby database. If MRP is
not running, then start the process with real time enabled using the below
query in the standby database.
SQL>alter database recover managed
standby disconnect from session;
Once when the MRP has started
on the standby database, make sure that the archive logs generated at the
primary end are shipped and getting applied to the standby database.
At the primary
side check the maximum archive log sequence that has got generated:
SQL> select thread#,max(sequence#)
from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
----------
--------------
1
163
At the standby
side, check the maximum archive log sequence that has been applied from the
primary database:
SQL> select thread#,max(sequence#)
from v$archived_log group by thread#;
----------
--------------
1 163
In the above case, sequence# 163
is the maximum sequence generated at the primary database and the same has been
applied to the standby database.
Verify primary
and standby tempfiles match
For each temporary tablespace
on the standby, verify that temporary files associated with that tablespace on
the primary database also exist on the standby database. Tempfiles added after
initial standby creation are not propagated to the standby. Run this query on
both the primary and target physical standby databases and verify that they match.
Check whether all the datafiles are online prior to the switchover on both primary and standby databases
SQL>select name from v$datafile where status=’OFFLINE’;
On Standby side:
SQL>
select name from v$datafile where status='OFFLINE';
no rows selected
If there are any offline
datafiles, then bring them online using the below query
Check if there are any jobs
running on the primary database using the below query.
SQL>
select * from dba_jobs_running;
no rows selected
If there are any jobs running
on the primary database and if its execution is not very important, then
terminate the job to continue further.
SQL>
sho parameter job_que
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
job_queue_processes integer 1000
In the above fig. the
job_queue_processes parameter is set to 1000. Set this parameter to the value
0.
SQL>alter
system set job_queue_processes=0 scope=spfile;
System altered
SQL>
Verify that the
primary database can be switched over to the standby
Query the switchover_status
column of the v$database view on the primary database to determine whether the
primary database can be switched over to the standby.
SQL>
select switchover_status from v$database;
--------------------
TO
STANDBY
A value of TO STANDBY or
SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the
switchover command) indicates that the primary database can be switched to the
standby role. If neither of these values is returned, a switchover is not
possible because redo transport is either mis-configured or is not functioning
properly.
Switchover the
primary database to standby
Once when value of
switchover_status returns “TO STANDBY” or “SESSIONS ACTIVE” on the primary
database, then perform the switchover using the below query
SQL>
select switchover_status from v$database;
--------------------
TO
STANDBY
Database
altered.
Now the primary database is switched over to the standby database. The execution of the above command may take some time and the archive logs generated during its execution would be automatically applied to the standby database. Once when the command is executed with the output as “Database altered”, it means that the primary database has been switched over to the standby.
Note: Always perform the switchover of
the primary database to standby database first and then switchover the standby
database to primary. If not, then you would end up landing with two primary
databases
Switchover the
standby database to primary
Query the switchover_status
column from the v$database view at the standby side to determine whether the
standby database can be switched over to the primary database.SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO
PRIMARY
A value of TO PRIMARY or
SESSIONS ACTIVE indicates that the standby database is ready to be switched to
the primary role. If neither of these values is returned, verify that redo
apply is active and that redo transport is configured and working properly.
Continue to query this column until the value returned is either TO PRIMARY or
SESSIONS ACTIVE.
Once when the value of
switchover_status returns “TO PRIMARY” or “SESSIONS ACTIVE” on the standby
database, then perform the switchover using the below query
SQL> select switchover_status from
v$database;
SWITCHOVER_STATUS
--------------------
TO
PRIMARY
SQL> alter database commit to
switchover to primary with session shutdown;
Database altered.
Now the standby database has
been switched over to the primary database.
Open the new primary database (STANDBY)
The new primary database will be in mount state. Open this new primary database using the below query.
SQL> alter database open;
Database altered.
SQL> select
instance_name,status,database_role from v$database,v$instance;
INSTANCE_NAME STATUS DATABASE_ROLE
----------------
------------ ----------------
STANDBY OPEN PRIMARY
Restart the new standby database
Restart the new standby
database (old primary database prim), bring it to the mount stage and start the
managed recovery process.
Shutdown the new standby
database (TESTDB)
SQL>shutdown immediate;
Startup the new standby
database (TESTDB) in mount stageSQL> startup mount;
ORACLE
instance started.
Fixed
Size 2230712 bytes
Variable
Size 335545928 bytes
Database
Buffers 251658240 bytes
Redo
Buffers 11837440 bytes
Database
mounted
Start the managed recovery
process on the the new standby database (TESTDB)
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select
instance_name,status,database_role from v$database,v$instance;
INSTANCE_NAME STATUS
DATABASE_ROLE
----------------
------------ ----------------
TESTDB MOUNTED PHYSICAL STANDBY
Reset the job_queue_processes parameter to its previous value
Set the job queue processes to
its original value on the new standby (TESTDB).
SQL>
sho parameter job_queue_processes
job_queue_processes integer 1000
SQL>alter system set job_queue_processes=1000 scope=spfile;
System altered
Now the roles of the databases have been changed. The primary database (TESTDB) has been changed to standby database and the standby database (STANDBY) has been changed to primary database.
The archive logs that get generated
in the new primary database (STANDBY) get shipped automatically to the new
standby database (TESTDB) and they are applied on it automatically.
Maximum archivelog generated at
the new primary database (STANDBY)
SQL> select thread#,max(sequence#) from v$archived_log where
applied=YES’ group by thread#;
----------
--------------
1 163
Maximum archivelog that has
been shipped and applied to the new standby database (TESTDB)
SQL> select
thread#,max(sequence#) from v$archived_log where applied=YES’ group by thread#;
----------
--------------
1 163
The roles can again be reversed
by following the same above procedures.
I
hope this article helped you. Your suggestions/feedback are most
welcome.
Keep learning... Have a great day!!!
Keep learning... Have a great day!!!
I really appreciate your Blog guy, It's very useful !!!
ReplyDeleteThanks a lot !!!