Procedures
Today's blog is highlighting the
steps to follow before, during and post switch over and switch back.
1. Before Switch Over- Prerequisite steps
==================================================================
To do list:
Before Switch over :- ( in both primary and standby database)
---------------------------------------------------------------
0. TNSPING between primary and DR will be happeb from every node to it's corresponding DG nodes and vice -versa.
1. make ensure that you have correct password of sys user (if not ask Saikat for it) and also check what is the entry do present in password file.
2. Check the database( Db running on how many instances), services, SLR(standby log redo) status.
3. Important- spool out the parameter details from both the system.
4. Check the Sync Status of Primary and Standby.
5. Check on which node the backup details has been enabled( level 0, level 0, archive, check_archvive_log_usage scripts)
Detailed Steps
====================
0. TNSPING between PRIMARY and DG
Connecting to each node 1,2,3,4 --------> tnsping 1,2,3,4 of DG side one by one and vice-versa.
1. Regrading SYS- Checking the password file
Lets query the password file users from the database.
----------------------------------------------------
select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
<<<<As of now, we have just the SYS user in the password file >>>------------------------> check this step while switch over- mandatory-
It will make ensure us yes our password only have sys user entry,,so that if in future we can edit it we can edit it.
2. To check the database status:
------------------------------------
srvctl status database -d database_name
ex. srvctl status database -d jnv
From SQL-
set linesize 200
set trimout on
set space 1
col INSTANCE for a10
col MODE for a11
col ROLE for a10
col HOST for a20
col STATUS for a7
col STARTUP_TIME for a20
select NAME "DB_NAME",INSTANCE_NAME "INSTANCE",OPEN_MODE,LOG_MODE,STATUS,DATABASE_ROLE "ROLE",protection_mode,HOST_NAME "HOST" from gv$database NATURAL JOIN gv$instance;
To check what are services runnign against database.
--------------------------------------------------
srvctl status service -d dbname
Ex. srvctl status service -d jnv
Status of Standby Redo Log file:
---------------------------------
SELECT GROUP#, THREAD#, BYTES/1024 FROM V$LOG;
<< The output should be same in both the system- for every field>>
3. Important- spool out the parameter details from both the system.
--------------------------------------------------------------------
Spool out a text file for every parameter details from both the syste.
Important parameters to look after
1. sga_max_size
2. sga_target
3. pga_aggregate_limit
4. pga_aggregate_target
5. shared_pool_reserved_size
6. sort_area_size
7. db_recovery_file_dest_size
8. result_cache_max_size
Below commands you can use to check the all the parameter details.
===============================================================
set pages 12000 lines 2000
COLUMN name FORMAT A30
COLUMN value FORMAT A70
COLUMN ses_mod FORMAT a10
COLUMN sys_mod FORMAT a10
COLUMN ins_mod FORMAT a10
SELECT p.name,
p.type,
p.value,
p.isses_modifiable as SES_MOD,
p.issys_modifiable as SYS_MOD,
p.isinstance_modifiable as INS_MOD
FROM v$parameter p
ORDER BY p.name
/
4. SYNC STATUS CHECK BETWEEN PRIMARY/STANDBY
##############################################
Status Of MRP Process: (it should be in applying_log status)
-----------------------------------------------------------------
In STANDBY > SQL> select inst_id,process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from gv$managed_standby where process like 'MRP%';
INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
---------- --------- ------------ -------- ---------- ---------- ---------- ---------- ----------
1 MRP0 APPLYING_LOG N/A 1 1112 113379 8388608 0
SYNC status
************
Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby: SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
================================================================
2. During Switch Over Steps- Explained through an example
==================================================================
Caution-
Please
don’t do switch over if the standby database is in “READ ONLY WITH APPLY”.
There
might be chances that whole DG configuration may break and have to build from
scratch as per Oracle.Oracle
recommends to do the switch over while the physical standby is in MOUNTED
state.Please
refer below metalink note on why it is not recommended to switch over the
database during READ ONLY mode.Doc ID 1323789.1
If the physical Stanby database is in READ ONLY mode. Please
switch it to MOUNTED mode
To do so:
P.N- We can start the MRP process either
from DG Broker or from SQLPLUS (not from both at time)
1. Stop the MRP
process from standby.
If it is via DG
Broker :
STANDBY DB>
dgmgrl -> connect sys/password à show database jnvdg-- > check the state
status it should be ON -> then
edit database jnvdg set state=’APPLY-OFF’;
If From SQL command:
P.N- If DGbroker
not functioning properly
ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE CANCEL;
2.
Change the status of
database from READ ONLY to MOUNTED
2.1 srvctl stop database –d
jnvdg
2.2 srvctl start database
–d jnvdg –o mount
Now check the status of the DB:
2.3 srvctl status database
–d jnvdg
3.
Start the MRP process
If it is via dgmgrl
STANDBY DB > dgmgrl -> connect
sys/password à
edit database jnvdg set state=’APPLY-ON’;
Via SQL PLUS-
STANDBY DB > ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from
session;
Once standby Database is MOUNTED /converted to mount state (from
READ ONLY mode), please follow below steps to switchover
Task
|
Steps
|
Expected results
|
Show current state on primary
(isurangi01)
|
1.
Login to primary side
2.
srvctl status database -d jnv
3.
srvctl status service -d jnv
|
Service jnv1_ground is running on
instance(s) jnv1
Service jnv2_ground is running on
instance(s) jnv2
Service jnv3_ground is running on
instance(s) jnv3
Service jnv4_ground is running on
instance(s) jnv4
Service jnv_ground_prd is running on
instance(s) jnv1,jnv2,jnv3,jnv4
Service jnvdg_ground_prd is not
running.
Service pjnv_ground_prd is running on
instance(s) jnv1,jnv2,jnv3,jnv4
|
Show current state on standby
(asurangi01)
|
1.
Login to standby side
2.
srvctl status database -d jnvdg
3.
srvctl status service -d jnvdg
|
|
Perform
Switchover- Primary side
P.N- We have to have the correct password of SYS.
|
1.
Return to primary
2.
cd $ORACLE_HOME/bin
3.
dgmgrl
4.
connect sys@jnv
5.
show database jnv
6.
show database jnvdg
7.
show database verbose jnv
8.
show database verbose jnvdg
9.
switchover to 'jnvdg'
10.
Wait for switchover to complete
11.
show configuration
12.
show database jnv
13.
show database jnvdg [repeat until lag = 0]
|
P.N – switchover to ‘jnvdg’ command will shutdown the database and open
the database in MOUNT mode.
|
Show current state on primary (which became Standby now)
|
1.
Return to primary
2.
srvctl status database -d jnv
3.
srvctl status service -d jnv
|
Service jnv1_ground is not running.
Service jnv2_ground is not running.
Service jnv3_ground is not running.
Service jnv4_ground is not running.
Service jnv_ground_prd is not running.
Service jnvdg_ground_prd is running on
instance(s) jnv1,jnv2,jnv3,jnv4
Service pjnv_ground_prd is not running.
|
Show current state on standby
(Which became primary now)
|
1.
Return to secondary
1.
srvctl status database -d jnvdg
2.
srvctl status service -d jnvdg
|
Service jnv1_ground is running on
instance(s) jnvdg1
Service jnv2_ground is running on
instance(s) jnvdg2
Service jnv3_ground is running on
instance(s) jnvdg3
Service jnv4_ground is running on
instance(s) jnvdg4
Service jnv_ground_prd is running on
instance(s) jnvdg1,jnvdg2,jnvdg3,jnvdg4
Service jnvdg_ground_prd is not
running.
Service pjnv_ground_prd is not running.
|
P.N- If DG Breaker has problem then
Switch over can be done via SQLPLUS as well, but it is always recommended to do
it from DG broker only, specifically in RAC.
Via SQL PLUS-
SWITCH OVER from PRIMARY to STANDBY
PRIMARY DATBASE- JNV> switch from primary to standby
alter database commit to
switchover to physical standby with session shutdown;
·
Check the database status
PRIMARY DATABSE-JNV > srvctl
status database –d jnv
·
If the database is shutdown, then fine , if not,
PRIMARY DATABASE- jnv> srvctl
stop database -d jnv -o immediate
·
Now start the database in mount mode
PRIMARY DATABASE- jnv>
srvctl start database –d jnv –o ‘read
only’ not tested
PRIMAY DATABASE jnv> srvctl
start database –d jnv –o mount
Then go to each node of JNV
database and start the database in read only.
JNV node1- alter database open
read only;
JNV node2- alter databasee open
read only;
JNV node3- alter databasee open
read only;
JNV node4- alter databasee open
read only;
·
Start The MRP process in PRIMARY
alter database recover managed
standby database disconnect from session;
·
IN STANDBY DB: jnvdg
alter database commit to
switchover to primary with session shutdown;
alter database recover managed standby database cancel ;
alter database commit to
switchover to primary;
Shutdown the database and
startup the database;
Srvctl stop database –d jnvdg
Srvctl start database –d jnvdg
Post Switch Over Steps -à Critical
·
Check the status of MRP process and check sync status of
both the database using above command.
1.
Schedule the below backups
1.
Committed out the check_archive_usage.ksh
from jnv and enable it on jnvdg.
2.
Level 0 on jnvdg
3.
Level 1 on jnvdg
4.
Archive Backup on jnvdg
2. Below script help you out if there is any
parameter has been changed after switch over.
p.N- You can specify the
specific parameter you to monitor and rest choose default(don’t give anything)
set pages 12000
set linesize 1550
col time for a15
col parameter_name format a50
col old_value format a30
col new_value format a30
break on instance skip 3
select instance_number instance, snap_id, time,
parameter_name, old_value, new_value from (
select a.snap_id,to_char(end_interval_time,'DD-MON-YY
HH24:MI') TIME, a.instance_number,
parameter_name, value new_value,
lag(parameter_name,1) over (partition by parameter_name,
a.instance_number order by a.snap_id) old_pname,
lag(value,1) over (partition by parameter_name,
a.instance_number order by a.snap_id)
old_value ,
decode(substr(parameter_name,1,2),'__',2,1) calc_flag
from dba_hist_parameter a, dba_Hist_snapshot b , gv$instance
v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like
nvl('¶meter_name',parameter_name)
and a.instance_number like
nvl('&instance_number',v.instance_number)
)
where
new_value != old_value
and calc_flag not in
(decode('&show_calculated','Y',3,2))
order by 1,2
/
Failback
Task
|
Steps
|
Expected results
|
Show current state on standby
(asurangi01)
|
1.
Login to standby side
2.
srvctl status database -d jnvdg
3.
srvctl status service -d jnvdg
|
|
Show current state on primary
(isurangi01)
|
1.
Login to primary side
2.
srvctl status database -d jnv
3.
srvctl status service -d jnv
|
|
Perform failover
|
1.
Return to secondary
2.
cd $ORACLE_HOME/bin
3.
dgmgrl
4.
connect sys@jnvdg
5.
show database jnv
6.
show database jnvdg
7.
show database verbose jnv
8.
show database verbose jnvdg
9.
switchover to 'jnv'
10.
Wait for switchover to complete
11.
show configuration
12.
show database jnv
13.
show database jnvdg [repeat until lag = 0]
|
|
Show current state on primary
|
1.
Return to primary
2.
srvctl status database -d jnv
3.
srvctl status service -d jnv
|
Service jnv1_ground is not running
Service jnv_ground_prd is not running.
Service jnvdg_ground_prd is running on
instance(s) jnv1,jnv2,jnv3,jnv4
Service pjnv_ground_prd is not running.
|
Show current state on standby
|
1.
Return to secondary
1.
srvctl status database -d jnvdg
2.
srvctl status service -d jnvdg
|
Service jnv1_ground is running on
instance(s) jnvdg1
Service jnv2_ground is running on
instance(s) jnvdg2
Service jnv3_ground is running on
instance(s) jnvdg3
Service jnv4_ground is running on
instance(s) jnvdg4
Service jnv_ground_prd is running on
instance(s) jnvdg1,jnvdg2,jnvdg3,jnvdg4
Service jnvdg_ground_prd is running on
instance(s) jnvdg1,jnvdg2,jnvdg3,jnvdg4
Service pjnv_ground_prd is running on
instance(s) jnvdg1,jnvdg2,jnvdg3,jnvdg4
|
P.N- If DG Breaker has problem then
Switch over can be done via SQLPLUS as well, but it is always recommended to do
it from DG broker only, specifically in RAC.
Via SQL PLUS-
SWITCH OVER from PRIMARY to STANDBY
PRIMARY DATBASE- JNVdg>
alter
database commit to switchover to physical standby with session shutdown;
·
Check the database status
PRIMARY DATABSE-JNVdg >
srvctl status database –d jnvdg
·
If the database is shutdown, then fine , if not,
PRIMARY DATABASE- jnvdg>
srvctl stop database -d jnvdg -o immediate
·
Now start the database in mount mode
PRIMARY DATABASE- jnvdg>
srvctl start database –d jnvdg –o ‘read
only’ not tested
PRIMAY DATABASE jnvdg> srvctl
start database –d jnvdg –o mount
Then go to each node of JNVdg
database and start the database in read only.
JNVdg node1- alter database open
read only;
JNVdg node2- alter databasee
open read only;
JNVdg node3- alter databasee
open read only;
JNVdg node4- alter databasee
open read only;
·
Start The MRP process in PRIMARY
alter database recover managed
standby database disconnect from session;
·
IN STANDBY DB:- JNV
alter database commit to
switchover to primary with session shutdown;
alter database recover managed standby database cancel ;
alter database commit to
switchover to primary;
Shutdown the database and
startup the database;
Srvctl stop database –d jnv
Srvctl start database –d jnv
Post Switch Back stepss à Critical
·
Check the status of MRP process and check sync status of
both the database using above command.
1.
Schedule the below backups
5.
Committed out the check_archive_usage.ksh
from jnvdg and enable on jnv.
6.
Level 0 on jnv
7.
Level 1 on jnv
8.
Archive Backup on jnv
General notes
·
Source in profile “. /u01/jnv/admin/profile/oracle.profile”
·
Service names should be present on secondary, if
not, follow the below steps
o
asurangi01.sce.com
/u01/p507/admin/profile=>srvctl status service -d jnvdg
o
asurangi01.sce.com
/u01/p507/admin/profile=>srvctl add service -d jnvdg -s jnv_pci2 -r jnvdg4,jnvdg3,jnvdg2,jnvdg1
o
asurangi01.sce.com
/u01/p507/admin/profile=>srvctl start service -d jnvdg -s jnv_pci2
o
asurangi01.sce.com
/u01/p507/admin/profile=>srvctl status service -d jnvdg
·
·
to check error --do in standby
·
·
select message from v$dataguard_status;
·
·
To switch log file in RAC database
·
alter system archivelog current;
·
·
·
Database
·
--------
·
srvctl start database -d db_name [-o start_options]
[-c connect_str|-q]
·
srvctl start database -d jnv [-o open]
·
srvctl start database -d jnv -o nomount
·
srvctl start database -d jnv -o mount
·
·
srvctl stop database -d jnv -o immediate
·
srvctl stop database -d jnv [-o normal]
·
·
srvctl status database -d jnv
·
·
srvctl config database -d jnv -a
·
·
Instance:
·
---------
·
srvctl add instance –d db_name –i inst_name -n
node_name
·
srvctl add instance -d prod -i prod01 -n linux01
·
·
srvctl remove instance -d prod -i prod01
·
·
srvctl start
instance -d db_name -i inst_names [-o start_options] [-c connect_str|-q]
·
·
srvctl start instance –d db_name –i inst_names [-o
open]
·
·
srvctl start instance –d db_name –i inst_names -o
nomount
·
·
srvctl start instance –d db_name –i l5062 -o mount
·
·
srvctl start instance –d dev -i dev2
·
·
·
srvctl stop instance -d db_name -i inst_names [-o
stop_options] [-c connect_str|-q]
·
·
srvctl stop instance –d db_name –i inst_names [-o
normal]
·
·
srvctl stop instance –d db_name –i inst_names -o
transactional
·
·
srvctl stop instance –d db_name –i inst_names -o
immediate
·
·
srvctl stop instance –d db_name –i inst_names -o
abort
·
·
srvctl stop inst –d vis -i vis
·
·
Adding a Database Service
·
----------------------------------------
·
srvctl status service -d dbname
·
srvctl config service -d dbname
·
srvctl start service -d dbname -s servicename
srvctl stop service -d
dbname -s servicenam
