DataGaurad Switchover Steps in Oracle 12c using manual method
1. Environment details
Environments
|
Primary server
|
DR server
|
Database
SID
|
PRIM12
|
PRIM12
|
DB
Unique name
|
PRIM12
|
STND12
|
Database
role
|
Primary
|
Standby
(READ-ONLY)
|
Database
version
|
12.2.0.1
(64 bits)
|
12.2.0.1
(64 bits)
|
Operating
system
|
RHEL
6.9
|
RHEL
6.9
|
ORACLE_HOME
|
/u01/app/oracle/product/12.2.0.1/db_1
|
/u01/app/oracle/product/12.2.0.1/db_1
|
2. Database Roles details
2.1. Primary database - PRIM12
SYS@PRIM121 > SELECT NAME,
DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
---------
------------------------------ -------------------- ----------------
PRIM12 PRIM12 READ WRITE PRIMARY
2.2.
Standby
database - STND12
SYS@STND121 > SELECT NAME, DB_UNIQUE_NAME,
OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
---------
------------------------------ -------------------- ----------------
PRIM12 STND12 READ ONLY PHYSICAL STANDBY
3.
Procedures on Pre, During and Post DR database
3.1. Pre DR test
3.1.1.
Checklist on Pre-Switchover
3.1.1.1. Ensure
log_archive_dest_2 is configured on PRIMARY and STANDBY databases
PRIM12
SYS@PRIM121 > show parameter
log_archive_dest_2
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
log_archive_dest_2 string SERVICE=STND12 LGWR ASYNC VALID_FOR=
(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=STND12
STND12
SYS@STND121 > show parameter
log_archive_dest_2
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
log_archive_dest_2 string SERVICE=PRIM12 LGWR ASYNC VALID_FOR=
(ONLINE_LOGFILES, PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM12
3.1.1.2.
Media Recovery Process (MRP) is active on STANDBY and in sync
with PRIMARY database
Run the below scripts on
Standby database – STND12
a.
Script to check MRP process running
SELECT
PROCESS from GV$MANAGED_STANDBY where PROCESS like 'MRP%';
Output:
PROCESS
---------
MRP0
To start the MRP if not running
ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
b.
Script to check the received and applied
archive logs timestamp
SELECT
'Last Applied : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES')
UNION
SELECT
'Last Received : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#) FROM
v$archived_log);
Output:
LOGS TIME
----------------
---------------------------
Last Applied :
24-JUNE-20:05:10:12
Last Received : 24-JUNE-20:05:10:15
c.
Script to check the received and applied
the log sequence
SELECT
al.thrd "Thread", almax "Last Seq Received", lhmax
"Last Seq Applied"
FROM (
SELECT thread# thrd, MAX (sequence#) almax
FROM gv$archived_log
WHERE resetlogs_change# = ( SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) al,
(
SELECT thread# thrd, MAX (sequence#) lhmax
FROM gv$log_history
WHERE first_time = ( SELECT MAX (first_time) FROM v$log_history)
GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;
Output:
Thread
Last Seq Received Last Seq Applied
--------
----------------- ----------------
1 16320 16320
3.1.1.3.
Validate STANDBY REDO logs are available on both PRIMARY and
STANDBY, create if not exists
Script to check the standby
redo log files details on PRIM12 and STND12
set
lines 155 pages 9999
col
thread# for 9999990
col
sequence# for 999999990
col
grp for 990
col
fnm for a50 head "File Name"
col
"Fisrt SCN Number" for 999999999999990
break
on thread
#
skip 1
select
a.thread#
,a.sequence#
,a.group#
grp
,
a.bytes/1024/1024 Size_MB
,a.status
,a.archived
,a.first_change#
"First SCN Number"
,to_char(FIRST_TIME,'DD-Mon-RR
HH24:MI:SS') "First SCN Time"
,to_char(LAST_TIME,'DD-Mon-RR
HH24:MI:SS') "Last SCN Time"
from
v$standby_log a order by 1,2,3,4
/
Output
THREAD# SEQUENCE#
GRP SIZE_MB STATUS ARC First SCN Number First SCN Time Last SCN Time
--------
---------- ---- ---------- ---------- --- ---------------- -------------------
-----------
1 0
9 1000 UNASSIGNED NO
1 0
11 1000 UNASSIGNED NO
1 0
12 1000 UNASSIGNED NO
1 0
13 1000 UNASSIGNED NO
1 16321 10 1000 ACTIVE YES
7.8518E+12 24-June-20 05:10:15
24-June-20 05:41:00
2 0
15 1000 UNASSIGNED NO
2 0
16 1000 UNASSIGNED NO
2 0
17 1000 UNASSIGNED NO
2 0
18 1000 UNASSIGNED YES
2 14417 14 1000 ACTIVE YES
7.8518E+12 24-June-20 05:10:11
24-June-20 05:41:00
3.1.1.4.
Verify PRIMARY and STANDBY TEMPFILES are match
PRIM12
SYS@PRIM121
> select ts#,name,ts#,status from v$tempfile;
TS# NAME
TS# STATUS
----------
----------------------------------------------------------- ---------- -------
3 +PRIM_DATA/PRIM12/TEMPFILE/temp.522.1005297865 3 ONLINE
8 +PRIM_DATA/PRIM12/TEMPFILE/temp.764.1009729329 8 ONLINE
STND12
SYS@STND121
> select ts#,name,ts#,status from v$tempfile;
TS# NAME
TS# STATUS
----------
-------------------------------------------------- ---------- ------
3 +PRIM_DATA/STND12/TEMPFILE/temp.936.1032250645 3 ONLINE
8 +PRIM_DATA/STND12/TEMPFILE/temp.937.1032250645 8 ONLINE
3.1.1.5.
Ensure db_file_name_convert and log_file_name_convert set
correctly on both Databases
PRIM12
SYS@STND121
> show parameter convert
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_file_name_convert string +PRIM_DATA/PRIM12/, +PRIM_DATA/STND12/,
+PRIM_FRA/PRIM12/,
+PRIM_FRA/STND12/
log_file_name_convert string +PRIM_REDOA/PRIM12/onlinelog,
+PRIM_REDOA/STND12/onlinelog,
+PRIM_REDOB/PRIM12/onlinelog,
+PRIM_REDOB/STND12/onlinelog
STND12
SYS@STND121
> show parameter convert
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_file_name_convert string +PRIM_DATA/PRIM12/, +PRIM_DATA/STND12/,
+PRIM_FRA/PRIM12/,
+PRIM_FRA/STND12/
log_file_name_convert string +PRIM_REDOA/PRIM12/onlinelog,
+PRIM_REDOA/STND12/onlinelog,
+PRIM_REDOB/PRIM12/onlinelog,
+PRIM_REDOB/STND12/onlinelog
3.1.1.6.
Validate the readiness of Switchover on PRIM12
a.
Validate if the environments are ready
for the role transition on PRIM12 database
SYS@PRIM121
> alter database switchover to STND12 verify;
Database
altered.
Output from database alert log file:
Primary – PRIM12:
2020-06-15T13:18:37.118480+00:00
alter
database switchover to STND12 verify
2020-06-15T13:18:38.527117+00:00
SWITCHOVER
VERIFY: Send VERIFY request to switchover target STND12
2020-06-15T13:18:39.841277+00:00
SWITCHOVER
VERIFY COMPLETE: READY FOR SWITCHOVER
Completed:
alter database switchover to STND12 verify
Standby- STND12:
2020-06-15T13:18:39.358066+00:00
SWITCHOVER
VERIFY BEGIN
SWITCHOVER
VERIFY COMPLETE
3.1.1.7.
Capture the database connections to validate with post-DR
a.
Script to check all ACTIVE and INACTIVE
sessions on PRIM12 database
set
linesize 300
set
pagesize 300
set
colsep |
SET
UNDERLINE =
COL
USERNAME format a20
COL
OSUSER format a20
COL
SERVICE_NAME format a25
col
machine for a18
--break
on inst_id skip 1
COL
SERVICE_NAME FOR A14
select USERNAME, substr(machine,1,18) machine,
OSUSER, SERVICE_NAME, count(1) from gv$session
where
machine
not in (select HOST_NAME from gv$instance)
and
SERVICE_NAME NOT IN ('SYS$BACKGROUND' ,'SYS$USERS' )
group
by USERNAME, machine, OSUSER, SERVICE_NAME order by USERNAME;
3.2. During DR test
3.2.1. Check for long-running
sessions and Kill Active sessions if any
a.
Script to find the list of long-running sessions
on PRIM12
column
username format 'a18'
column
osuser format 'a10'
column
module format 'a16'
column
program_name format 'a20'
column
program format 'a20'
column
machine format 'a25'
column
action format 'a15'
column
sid format '9999'
column
serial# format '99999'
column
spid format 'a10'
column
START_TIME format 'a20'
column
ACTION format 'a6'
column
status format 'a9'
set
linesize 200
set
pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
v$session
a, v$process b, v$transaction c,
v$sqlarea
s
Where
a.paddr
= b.addr
and
a.saddr = c.ses_addr
and
a.sql_address = s.address (+)
and
to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) --
running for 15 minutes
order
by c.start_time
/
b.
Script to find the list of ACTIVE
sessions on PRIM12
set
linesize 300
set
pagesize 300
set
colsep |
SET
UNDERLINE =
COL
USERNAME format a20
COL
OSUSER format a20
COL
SERVICE_NAME format a20
col
machine for a18
--break
on inst_id skip 1
COL
SERVICE_NAME FOR A14
select USERNAME, substr(machine,1,18) machine,
OSUSER, SERVICE_NAME, count(1) from gv$session
where
machine
not in (select HOST_NAME from gv$instance)
and
SERVICE_NAME NOT IN ('SYS$BACKGROUND' ,'SYS$USERS' ) and status = 'ACTIVE'
group
by USERNAME, machine, OSUSER, SERVICE_NAME order by USERNAME;
c.
Script to kill the ACTIVE Sessions
select
'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' as kill
from gv$session;
d.
Monitor long-running jobs and Clear
Potential Blocking Parameters & Jobs
Capture current job state on the
primary:
SQL>
SELECT * FROM DBA_JOBS_RUNNING;
SQL>
SHOW PARAMETER job_queue_processes
SQL>
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID=’*’;
SQL>
EXECUTE DBMS_SCHEDULER.DISABLE( <job_name> );
SQL> exec
dbms_scheduler.stop_job('AIC.EOD_TG_PURGE_JOB',force=>true);
3.3.
Database
Switchover Steps
3.3.1.
Enable Flashback ON
PRIM12
a.
Validate the Flashback database is
enabled
SYS@PRIM121
> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SYS@PRIM121
> show parameter flashback
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_flashback_retention_target integer 1440
b.
Enable the Flashback database ON
SYS@PRIM121
> alter database flashback ON;
Database
altered.
SYS@PRIM121
> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
c.
Create a restore point
CREATE
RESTORE POINT "June2020_BEFORE_FLIP" GUARANTEE FLASHBACK DATABASE;
select TIME, GUARANTEE_FLASHBACK_DATABASE, NAME from gv$restore_point;
STND12
a.
Validate the Flashback database is
enabled
SYS@STND121
> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SYS@STND121
> show parameter flashback
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_flashback_retention_target integer 1440
b.
Stop MRP process on STND12 database and
enable flashback
ALTER
DATABSE RECOVER MANAGED STANDBY DATABASE CANCEL;
c.
Enable the Flashback database ON
SYS@PRIM121
> alter database flashback ON;
Database
altered.
SYS@PRIM121
> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
d.
Create restore point
CREATE
RESTORE POINT "01MARCH_BEFORE_FLIP" GUARANTEE FLASHBACK DATABASE;
select TIME, GUARANTEE_FLASHBACK_DATABASE, NAME from gv$restore_point;
e.
Start MRP process on STND12 database
ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
3.3.2.
Enable tracing on both PRIM12 and STND12 databases to
diagnose if any issue
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=8191 SID=’*’;
3.3.3.
Perform Switchover on Primary database – PRIM12
a.
Script to perform switchover to standby
database on PRIM12 database
. oraenv
PRIM121
SQL> ALTER DATABASE SWITCHOVER TO STND12;
Database alerted
Note: You don’t need to perform switchover steps on old STANDBY
database, since the above command takes care of the role transition on both PRIMARY
and STANDBY database.
b.
Validate the alert logfiles of PRIMARY
and STANDBY databases if any error
tail -fn 3000 /u01/app/oracle//PRIM12/diag/rdbms/PRIM12/PRIM121/trace/aler*.log
tail -fn 3000 /u01/app/oracle//STND12/diag/rdbms/STND12/STND121/trace/aler*.log
c.
Open the STND12 new primary database and
check the database role and should be PRIAMRY
. oraenv
STND121
SELECT NAME, DB_UNIQUE_NAME,
OPEN_MODE, DATABASE_ROLE FROM V$DATABASE; -- the DB
role should show PRIMARY though database is still in MOUNT state.
srvctl
stop database -d STND12 -f
srvctl
start database -d STND12
srvctl
status database -d STND12
SELECT
NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;- it should open read-write and PRIMARY
3.3.4.
Disable the trace on both PRIM12 and STND12
ALTER
SYSTEM SET LOG_ARCHIVE_TRACE=0;
3.3.5.
Test the DR archive shipping on to new
standby -PRIM12 before we ask Application team to connect
PRIM12
i.
Restart the PRIM12 old primary database
since it will be shutdown automatically and don’t start
the MRP
process on PRIM12 until Application team confirms to start
replicating on Standby database.
srvctl start database -d PRIM12
srvctl status database -d PRIM12
i.
Select the database role on PRIM12
database and should be PHYSICAL STANDBY
SELECT NAME, DB_UNIQUE_NAME,
OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
ii.
Start the MRP process
ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
STND12
ALTER SYSTEM SWITCH LOGFILE
ALTER DATABASE LOG ARCHIVE
CURRENT;
3.3.6.
Validate the database role in OLR
STND12
select
DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
srvctl
config database -db STND12
srvctl
modify database -db STND12 -role PRIMARY
on PRIM12
select
DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
srvctl
config database -db PRIM12
srvctl
modify database -db bwg12 -role PHYSICAL_STANDBY
4.
Create and start the Services on STND12
(new primary)
Add services
srvctl
add service -service PRIM_R -db STND12 -preferred STND121,STND122
srvctl
add service -service PRIM_W -db STND12 -preferred -available STND122
Start services
srvctl
start service -service PRIM_R -db STND12
srvctl
start service -service PRIM_W -db STND12
Services status
srvctl
status service -d STND12
Service
PRIM_R is running on instance(s) STND121,STND122
Service
PRIM_W is running on instance(s) STND121
4.1. Post Switchover Check
4.1.1.
Verify that the STND12 shipping logs to PRIM12 and it is
applying on PRIM12.
On STND12
SQL>ALTER
SYSTEM SWITCH LOGFILE;
SQL>SELECT
DEST_ID,ERROR,STATUS FROM V$ARCHIVE_DEST WHERE DEST_ID=<YOUR REMOTE
LOG_ARCHIVE_DEST_<N>>;
SQL>SELECT
MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
If remote log_Archive_destination is 2 i.e
log_archive_dest_2.
SQL>SELECT
MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES' AND DEST_ID=2;
On PRIM12
SQL>SELECT
THREAD#,SEQUENCE#,PROCESS,STATUS FROM GV$MANAGED_STANDBY;
OR
SELECT
NAME,ROLE,INSTANCE,THREAD#,SEQUENCE#,ACTION FROM GV$DATAGUARD_PROCESS;
SQL>SELECT
MAX(SEQUENCE#),THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
a. Script
to check the received and applied archive logs timestamp on PRIM12
SELECT
'Last Applied : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES')
UNION
SELECT
'Last Received : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#) FROM
v$archived_log);
b. Script
to check the received and applied the log sequence on PRIM12
SELECT
al.thrd "Thread", almax "Last Seq Received", lhmax
"Last Seq Applied"
FROM (
SELECT thread# thrd, MAX (sequence#) almax
FROM gv$archived_log
WHERE resetlogs_change# = ( SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) al,
(
SELECT thread# thrd, MAX (sequence#) lhmax
FROM gv$log_history
WHERE first_time = ( SELECT MAX (first_time) FROM v$log_history)
GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;
5.
Switchback database
5.1.
Database
Roles details
5.2. Primary database – STND12
SYS@STND121 > SELECT NAME, DB_UNIQUE_NAME,
OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
---------
------------------------------ -------------------- ----------------
PRIM12 STND12 READ ONLY PRIMARY
5.3. Standby database - PRIM12
SYS@PRIM121 > SELECT NAME,
DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------
-------------------- ----------------
PRIM12 PRIM12 READ WRITE PHYSICAL STANDBY
6.
Procedures on Pre, During and Post DR database
6.1. Pre DR test
6.1.1.
Checklist on Pre-Switchover
6.1.1.1.
Media Recovery Process (MRP) is active on STANDBY and in sync
with PRIMARY database
d.
Script to check MRP process running PRIM12
SELECT
PROCESS from GV$MANAGED_STANDBY where PROCESS like 'MRP%';
e.
Script to check the received and applied
archive logs timestamp on PRIM12
SELECT
'Last Applied : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES')
UNION
SELECT
'Last Received : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#) FROM
v$archived_log);
f.
Script to check the received and applied
the log sequence on PRIM12
SELECT
al.thrd "Thread", almax "Last Seq Received", lhmax
"Last Seq Applied"
FROM (
SELECT thread# thrd, MAX (sequence#) almax
FROM gv$archived_log
WHERE resetlogs_change# = ( SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) al,
(
SELECT thread# thrd, MAX (sequence#) lhmax
FROM gv$log_history
WHERE first_time = ( SELECT MAX (first_time) FROM v$log_history)
GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;
6.1.2.
Validate the readiness of Switchover on STND12
b.
Validate if the environments are ready
for the role transition on STND12 database
SYS@STND121
> alter database switchover to PRIM12 verify;
Database
altered.
6.1.3.
Capture the database connections to validate with post-DR
b.
Script to check all ACTIVE and INACTIVE
sessions on the database on STND12
set
linesize 300
set
pagesize 300
set
colsep |
SET
UNDERLINE =
COL
USERNAME format a20
COL
OSUSER format a20
COL
SERVICE_NAME format a25
col
machine for a18
--break
on inst_id skip 1
COL
SERVICE_NAME FOR A14
select USERNAME, substr(machine,1,18) machine,
OSUSER, SERVICE_NAME, count(1) from gv$session
where
machine
not in (select HOST_NAME from gv$instance)
and
SERVICE_NAME NOT IN ('SYS$BACKGROUND' ,'SYS$USERS' )
group
by USERNAME, machine, OSUSER, SERVICE_NAME order by USERNAME;
6.2. During DR test
6.2.1.1.
Check for long-running sessions and Kill Active sessions if
any
I.
Script to find the list of long-running
sessions ON STND12
column
username format 'a18'
column
osuser format 'a10'
column
module format 'a16'
column
program_name format 'a20'
column
program format 'a20'
column
machine format 'a25'
column
action format 'a15'
column
sid format '9999'
column
serial# format '99999'
column
spid format 'a10'
column
START_TIME format 'a20'
column
ACTION format 'a6'
column
status format 'a9'
set
linesize 200
set
pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
v$session
a, v$process b, v$transaction c,
v$sqlarea
s
Where
a.paddr
= b.addr
and
a.saddr = c.ses_addr
and
a.sql_address = s.address (+)
and
to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) --
running for 15 minutes
order
by c.start_time
/
II.
Script to find the list of ACTIVE
sessions on STND12
set
linesize 300
set
pagesize 300
set
colsep |
SET
UNDERLINE =
COL
USERNAME format a20
COL
OSUSER format a20
COL
SERVICE_NAME format a20
col
machine for a18
--break
on inst_id skip 1
COL
SERVICE_NAME FOR A14
select USERNAME, substr(machine,1,18) machine,
OSUSER, SERVICE_NAME, count(1) from gv$session
where
machine
not in (select HOST_NAME from gv$instance)
and
SERVICE_NAME NOT IN ('SYS$BACKGROUND' ,'SYS$USERS' ) and status = 'ACTIVE'
group
by USERNAME, machine, OSUSER, SERVICE_NAME order by USERNAME;
III.
Script to kill the ACTIVE Sessions
select
'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' as kill
from gv$session;
IV.
Monitor long running jobs and Clear
Potential Blocking Parameters & Jobs
Capture current job state on the primary:
SQL>
SELECT * FROM DBA_JOBS_RUNNING;
SQL>
SHOW PARAMETER job_queue_processes
SQL>
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID=’*’;
Disable any jobs that may
interfere.
SQL>
EXECUTE DBMS_SCHEDULER.DISABLE( <job_name> );
6.3.
Database
Switchover Steps
6.3.1.
Enable tracing on both PRIM12 and STND12 databases to
diagnose if any issue
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=8191 SID=’*’;
6.3.2.
Perform Switchover on Primary database – STND12
i.
Script to perform switchover to standby
database on STND12 database
. oraenv
STND121
SQL> ALTER DATABASE SWITCHOVER TO PRIM12;
Database alerted
Note: You don’t need to perform switchover steps on old STANDBY
database, since the above command takes care of the role transition on both
PRIMARY and STANDBY database.
ii.
Validate the alert logfiles of PRIMARY
and STANDBY databases if any error
tail -fn 3000 /u01/app/oracle//PRIM12/diag/rdbms/PRIM12/
/PRIM121/trace/aler*.log
tail -fn 3000 /u01/app/oracle//STND12/diag/rdbms/STND12/STND121/trace/aler*.log
iii. Open
the PRIM12 new primary database
. oraenv
PRIM121
SQL> ALTER DATABASE OPEN;
iv. Restart
the STND12 old primary database as a Standby database
. oraenv
STND12
Sqlplus / as sysdba
SQL> STARTUP
SQL> alter database recover managed standby
database disconnect;
v.
Select the database role on STND12
database and it should be PHYSICAL STANDBY
SELECT NAME, DB_UNIQUE_NAME,
OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
vi. Script
to check MRP process running on STND12 database
SELECT
PROCESS from GV$MANAGED_STANDBY where PROCESS like 'MRP%';
vii.
Validate the database role in OLR and
modify the roles accordingly
on STND12
select
DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
srvctl
config database -db STND12
srvctl
modify database -db STND12 -role PHYSICAL_STANDBY
on PRIM12
select
DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
srvctl
config database -db PRIM12
srvctl
modify database -db PRIM12 -role PRIMARY
6.4. Post Switchover Check
6.4.1.
Disable the trace on both PRIM12 and STND12
ALTER
SYSTEM SET LOG_ARCHIVE_TRACE=0;
6.4.2.
Verify that the PWTG12 shipping logs to BNMET12 and it is
applying.
On PRIM12
SQL>ALTER
SYSTEM SWITCH LOGFILE;
SQL>SELECT
DEST_ID,ERROR,STATUS FROM V$ARCHIVE_DEST WHERE DEST_ID=<YOUR REMOTE
LOG_ARCHIVE_DEST_<N>>;
SQL>SELECT
MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
If remote log_Archive_destination is 2 i.e
log_archive_dest_2.
Dest_state should be enabled on both databases
SQL>SELECT
MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES' AND DEST_ID=2;
On STND12
SQL>SELECT
THREAD#,SEQUENCE#,PROCESS,STATUS FROM GV$MANAGED_STANDBY;
OR
SELECT
NAME,ROLE,INSTANCE,THREAD#,SEQUENCE#,ACTION FROM GV$DATAGUARD_PROCESS;
SQL>SELECT
MAX(SEQUENCE#),THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
c. Script
to check the received and applied archive logs timestamp on STND12
SELECT
'Last Applied : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES')
UNION
SELECT
'Last Received : ' Logs,
TO_CHAR (next_time,
'DD-MON-YY:HH24:MI:SS') Time
FROM gv$archived_log
WHERE sequence# = (SELECT MAX (sequence#) FROM
v$archived_log);
d. Script
to check the received and applied the log sequence on STND12
SELECT
al.thrd "Thread", almax "Last Seq Received", lhmax
"Last Seq Applied"
FROM (
SELECT thread# thrd, MAX (sequence#) almax
FROM gv$archived_log
WHERE resetlogs_change# = ( SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) al,
(
SELECT thread# thrd, MAX (sequence#) lhmax
FROM gv$log_history
WHERE first_time = ( SELECT MAX (first_time) FROM v$log_history)
GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;
Comments
Post a Comment