database switchover traditional manner

Switchover using traditional manner – Without DG Broker approach (Manual database Switchover)

Preparation before switchover: (Pre-Work)
(1)Check both Primary and standby databases in Sync , there is no archive log gap.

(2)Schedule OEM Blackout for the primary database as well as standby Databases

(3)Inform Application team before switchover

ON THE PRIMARY SERVER Node 1 – Always start from the Primary database

1)Login to PRIMARY and stop the databases that needs to be switched over
ps -ef |grep -i pmon —-> Determine Instance name
. oraenv —-> Set environment
srvctl stop database -d <database_name>>

2) Login to sqlplus as sysdba and start the database instance from Step 1.
sqlplus / as sysdba
startup

3a) Open a command session on the primary. Go to trace directory and monitor alert log
tail -50f alert_<database_name>

3b) Go to the new session from Step 1. Open a sqlplus session on the primary and check the switch over status
sqlplus / as sysdba

select switchover_status from v$database;

command should display ‘TO_STANDBY’ or ‘SESSIONS_ACTIVE’ 

If its difference , switch few archivelogs from primary database

4) Start switch over to standby fom primary database

alter database commit to switchover to physical standby with session shutdown;

shutdown abort;

srvctl config database -d <database_name> —-> check database role and start option it should be “PHYSICAL_STANDBY” and ‘READ ONLY’
If database role is not set correctly follow the below steps..

srvctl modify database -d <database_name> -r PHYSICAL_STANDBY
srvctl modify database -d <database name> -s ‘READ ONLY’
srvctl start database -d <database_name>

select open_mode from v$database – make sure it is “READ ONLY WITH APPLY” –

alter database recover managed standby database using current logfile;

you might get an error if DG Broker is enabled. you can Ignore the error.

srvctl stop service -d <database_name>
srvctl status service -d <database_name> (There should be no services running ).
sqlplus into the database and run the below script and ensure it is open read only mode and both instances are running.

set echo off
set pages 50
col open_mode for a23
col maxseq for 9999999
Col role for a20
col database_role for a10
col instance_name for a10
col user for a10
col host_name for a20
col HOST for a20
col version for a10
col started for a15

alter session set NLS_DATE_FORMAT =’MM/DD/YYYY HH24:MI:SS’;
select tab1.instance,substr(tab1.host_name,1,15) HOST,tab1.version,
to_char(tab1.startup_time,’Mon-dd-yy:hh24:mm’) STARTED,tab1.open_mode,tab1.database_role ROLE,tab1.dbid,tab2.seqn MAXSEQ from
(select a.inst_id,a.host_name, b.open_mode,b.database_role,a.version,a.startup_time,b.dbid,
a.thread# thrd,a.instance_name instance from gv$instance a,gv$database b
where a.inst_id=b.inst_id)
tab1,
(select thread#,max(sequence#) seqn from gv$log_history a
where a.resetlogs_time >= (select max(b.RESETLOGS_TIME) from gv$log_history b) group by thread#)
tab2
where tab1.thrd=tab2.thread#
order by inst_id
/

==============================================================================
ON THE previous STANDBY SERVER NODE 1

5) Set environment
ps -ef grep | pmon —-> Determine Instance name
. oraenv —-> Set environment

6) srvctl stop database -d <database_name>
Login to STANDBY as sysdba on first node only and switch to primary
sqlplus / as sysdba
STARTUP MOUNT;
alter database commit to switchover to primary;
shutdown immediate;
srvctl config database -d <database_name>

—-> check database role and open mode – it should be “PRIMARY” and ‘OPEN’
If database role is not set correctly follow the steps…
srvctl modify database -d <database_name> -r PRIMARY
srvctl modify database -d <database_name> -s ‘OPEN’
srvctl start database -d <database_name>

srvctl status database -d <database_name> —> 2 instances should be running –
srvctl stop service -d <database_name>
srvctl start service -d <database_name>
srvctl status service -d <database_name>
sqlplus into the database and run the script and ensure it is open read write mode and both instances are running.

7) Verify that new standby is synching – Perform step 3a and tail alert log for the new logs arrving on the new Standby.
On new primary:
Alter system switch logfile;

8) Make sure you can connect. Login from remote server and verify database status
. sqlplus /@service_name of the new primary database
==============================================================================
WRAP UP:
1) Run FULL/HBCK backup for the new primary.
2) Remove databases from OEM blackout and verify if database status is GREEN on OEM
3)make sure to start backups to new primary database
4) Verify that log_archive_dest_state_2=ENABLE on new primary and DEFER on new standby
==============================================================================

Script to check Database status-

set echo off
set pages 50
col open_mode for a23
col maxseq for 9999999
Col role for a20
col database_role for a10
col instance_name for a10
col user for a10
col host_name for a20
col HOST for a20
col version for a10
col started for a15

alter session set NLS_DATE_FORMAT =’MM/DD/YYYY HH24:MI:SS’;
select tab1.instance,substr(tab1.host_name,1,15) HOST,tab1.version,
to_char(tab1.startup_time,’Mon-dd-yy:hh24:mm’) STARTED,tab1.open_mode,tab1.database_role ROLE,tab1.dbid,tab2.seqn MAXSEQ from
(select a.inst_id,a.host_name, b.open_mode,b.database_role,a.version,a.startup_time,b.dbid,
a.thread# thrd,a.instance_name instance from gv$instance a,gv$database b
where a.inst_id=b.inst_id)
tab1,
(select thread#,max(sequence#) seqn from gv$log_history a
where a.resetlogs_time >= (select max(b.RESETLOGS_TIME) from gv$log_history b) group by thread#)
tab2
where tab1.thrd=tab2.thread#
order by inst_id
/