A primary RAC database that is
running normally, the switchover operation must start from the primary site.
Below are the step of switchover operation:
a. Shutdown all primary instances except one.
b. Shutdown all standby instances except one.
c. On the Primary site
i.
SQL> select switchover_status from V$database;
ii.
If switchover_status shows ‘sessions active’ go to step iii. If switchover_status
shows
‘TO_STANDBY’ go to step v.
iii.SQL>
select sid, process, program from v$session where type='user' and sid
<>(select distinct
sid from V$mystat);
iv.
if any rows/process found then kill those process or wait until logout.
v.
SQL> alter database commit to switchover to physical standby with session
shutdown;
vi.
SQL>shutdown immediate;
vii.
SQL>exit;
viii.
Restart the instance and mount it as standby
SQL> startup
nomount;
SQL> alter
database mount standby database;
ix. Open database in read only mode
SQL>
alter database mount standby database;
x. Start recover process
SQL> alter database recover
managed standby database using current logfile disconnect from
session;
d. On the Standby site.
i. SQL>alter database recover
managed standby database cancel;
ii. SQL>alter database recover
managed standby database nodelay disconnect from session
through last
switchover;
Note:
Above (ii) is need only when a time lag
used.
iii. SQL> alter database commit to
switchover to primary with session shutdown;
iv. SQL> alter database open;
e. On the original primary site mount & open the other instances as
standby.
f. On the original DR site start up and open the other instances (as
primary).
g. Verify the new data guard configuration
i. On new Primary
SQL> ALTER SYSTEM ARCHIVE LOG
CURRENT;
ii. On new standby
make
sure the log file is copied over and applied or will be applied (if time lag in
apply is used).
IMPLEMENTATION
Primary server : 172.xx.xxx.60
Standby Server : 172.xx.xxx.45
Step-1: On Primary Server
(172.xx.xxx.60)
SQL> select switchover_status from
v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> select database_role, open_mode
from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
SQL> select sid,process,program from
v$session where type='user';
no rows selected
Step-2: On Standby Server
(172.xx.xxx.45)
SQL> select switchover_status from
v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> select database_role, open_mode
from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
Step-3: On Primary Server
(172.xx.xxx.60)
SQL> alter database commit to
switchover to physical standby with session shutdown;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application
Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing
options
oracle@testbk [/home/oracle]$sqlplus /
as sysdba;
SQL*Plus: Release 11.2.0.4.0 Production
on Thu May 5 16:59:02 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5127602176
bytes
Fixed Size 2214432 bytes
Variable Size 4580181472 bytes
Database Buffers 536870912 bytes
Redo Buffers 8335360 bytes
SQL>
Step-4: On Standby (172.xx.xxx.45)
SQL>
select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
Step-6: On Primary (172.xx.xxx.60)
SQL> alter database mount standby
database;
Database altered.
SQL> alter database recover managed
standby database disconnect;
Database altered.
Step-7: On Standby (172.xx.xxx.45)
SQL> alter database recover managed
standby database cancel;
Database altered.
SQL> select database_role, open_mode
from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY
SQL> alter database recover managed
standby database nodelay disconnect from session through last switchover;
alter database recover managed standby
database nodelay disconnect from session through last switchover
*
ERROR at line 1:
ORA-16177: media recovery is not
required
SQL> alter database commit to
switchover to primary with session shutdown;
Database altered.
SQL> select database_role, open_mode
from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY MOUNTED
SQL>
alter database open;
Database altered.
SQL> select database_role, open_mode
from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
Step-8: On New Primary (172.xx.xxx.45)
SQL>
alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 196
Next log sequence to archive 199
Current log sequence 199
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 196
Next log sequence to archive 200
Current log sequence 200
SQL>
Step-9: On New standby (172.xx.xxx.60)
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 199
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 200
