Oracle Dataguard Manual Switchover

Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

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;