Oracle Dataguard Manual Switchover
1): On primary (server-A):
-- make sure no (active) user sessions connected to the primary db before do the switchover.
set linesize 180 trimspool on
column MACHINE FORMAT A50
select database_role, status, machine, count(*) from v$session, v$database
where type='USER'
group by DATABASE_ROLE, status, machine
order by DATABASE_ROLE, status, machine;
select DATABASE_ROLE, switchover_status from v$database;
-- Note: [ Sometimes it takes a while to get the "TO STANDBY" . It will show as
-- "SESSIONS ACTIVE" . Dont Panic just wait for sometime and execute the query again. ]
SELECT THREAD#, SEQUENCE# FROM V$THREAD;
SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;
select status, gap_status from v$archive_dest_status where dest_id = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
2): On primary (server-A)
select name ,open_mode, database_role, switchover_status from v$database;
select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
3):On primary (server-A)
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
---dangerous, kill active session --- alter database commit to switchover to physical standby with session shutdown;
4): On primary (server-A)
shutdown immediate;
top prev next
1): On standby: (server-B)
1a): How much gap we still have ?
SELECT THREAD#, SEQUENCE# FROM V$THREAD;
SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;
-- 12c trace file location:
/{diag_dest}/diag/rdbms/db_unique_name/sid/trace/
1b):
SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
SELECT * FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
SELECT * FROM V$MANAGED_STANDBY;
1c):
select name ,open_mode, database_role, switchover_status from v$database;
select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
-- if says "NOT ALLOWED" then :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- or real time applied log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Physical Standby Switchover_status Showing Not Allowed. (Doc ID 1392763.1)
In that step, Query returned "NOT ALLOWED" --- So We started to investigate why SWITCHOVER_STATUS shows NOT ALLOWED.
In Oracle documentation explain SWITCHOVER_STATUS column of v$database can have the following values:
NOT ALLOWED --- Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SESSIONS ACTIVE --- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
SWITCHOVER PENDING --- This is a standby database and the primary database switchover request has been received but not processed.
SWITCHOVER LATENT --- The switchover was in pending mode, but did not complete and went back to the primary database.
TO PRIMARY --- This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
TO STANDBY --- This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
RECOVERY NEEDED --- This is a standby database that has not received the switchover request.
2): On standby: (server-B)
alter database commit to switchover to primary;
3): On standby: (server-B)
alter database open;
or
shutdown immediate;
startup;
top prev next
on new standby (original primary) (server-A)
5): On Old primary
startup nomount
alter database mount standby database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;