Dataguard : Switchover from Pluggable primary to standby databse 12c

• Points to be noted during switchover

1. Verify the primary database instance is open and the standby database instance is mounted

2. Verify there are no active users connected to the databases  SELECT username, status, logon_time   FROM v$session  WHERE status = 'ACTIVE'

3.Make sure the last redo data transmitted from the Primary database was applied on the standby database

Issue the following commands on Primary database and Standby database to find out redo's applied or not. SQL>select sequence#, applied from v$archvied_log; SQL>select sequence#, applied from v$archvied_log; (check on physical Standby) In order to apply redo data to the standby database as soon as it is received, use Real-time apply Step -1 > Command to execute on primary for Switchover to Standby  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Step-2> After step 1 finishes, Switch the original physical standby db orcldb to primary role; SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Step-3> shut down and restart the former primary instance orcl SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP MOUNT; Step-4 > After step 3 completes Note- If you are using oracle 10 g release 1 than you need to shutdown and restart the new primary  database orcldr SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP; OR----- If you are using oracle 10 g release 2 than you need to open only the new primary  database orcldr SQL>ALTER DATABASE OPEN;  Step-4 > To Start the Recovery of new Standby ORCL SQL>alter database recover managed standby database disconnect from session; Step-5 > On the new primary database orcldr, perform a SWITCH LOGFILE to start sending redo  data to the standby database ORCL SQL>ALTER SYSTEM SWITCH LOGFILE; ------------------------------------------------------------------------------------------------------------- Switchover from Pluggable primary to standby database  example  Primary Instance  : rac1,rac2 Physical standby Instance : racdr In this example we have 2 node RAC with non rac DR physical standby database Check archive log sync between primary and standby database script used standby.sql and d.sql during switchover    select sequence#,applied from v$archived_log order by sequence# asc; SQL> !cat standby.sql select name,open_mode,database_role from v$database; select process,status,sequence# from v$managed_standby; SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#; SQL> !cat d.sql set lines 200 select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance; SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   rac1    rac   READ WRITE        PRIMARY NO    0 SQL> @standby                                                                       NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   READ WRITE        PRIMARY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CLOSING        57 ARCH   CLOSING        58 ARCH   CLOSING        56 ARCH   OPENING        47 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 LNS   CONNECTED 0 DGRD   ALLOCATED 0 LNS   WRITING        59 DGRD   ALLOCATED 0 10 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        58      58   0  1        58      58   0  2        52      52   0  2        52      52   0 Switchover command for converting primary database to standby database  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Database altered. Shutdown database and start old primary in mount stage ( In case of RAC we can start srvctl start database -d rac -o mount ) , Here when type above command both instance 1 and 2 were shutdown.)  SQL> SHUTDOWN IMMEDIATE; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@srv1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 16 15:38:28 2021 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size     8795664 bytes Variable Size   805308912 bytes Database Buffers  1610612736 bytes Redo Buffers     7979008 bytes Database mounted. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   rac1    rac   MOUNTED        PHYSICAL STANDBY NO    0 Now apply switchover command for converting  standby database to primary database SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CLOSING        47 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        58 ARCH   CLOSING        52 ARCH   CLOSING        57 RFS   IDLE 0 RFS   IDLE        59 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE        53 PROCESS   STATUS SEQUENCE# --------- ------------ ---------- RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 MRP0   APPLYING_LOG        53 15 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1        58      58 0 2        52      52 0 SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   racdr    racdr   MOUNTED        PRIMARY YES    3788307 Shutdown new primary database and start database in normal state SQL> SHUTDOWN IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size     8795664 bytes Variable Size   738200048 bytes Database Buffers 1677721600 bytes Redo Buffers     7979008 bytes Database mounted. Database opened. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   racdr    racdr   READ WRITE        PRIMARY YES    3788307 Forcefully switching  log file for testing purpose so we can confirm MRP0 process running fine on new DR  SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   READ WRITE        PRIMARY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- DGRD   ALLOCATED 0 ARCH   CLOSING        64 DGRD   ALLOCATED 0 ARCH   CLOSING        61 ARCH   CLOSING        62 ARCH   CLOSING        63 LNS   WRITING        65 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 9 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1        64      64 0 1        64      64 0 2        54      54 0 2        54      54 0 Check new standby database and appy mediaa recovery  SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CONNECTED 0 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        61 ARCH   CONNECTED 0 ARCH   CONNECTED 0 RFS   IDLE        62 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 10 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        61      58   3  2        54      52   2 Apply Media recovery process on new standby database. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CONNECTED 0 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        64 ARCH   CLOSING        62 ARCH   CLOSING        63 RFS   IDLE        65 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 MRP0   APPLYING_LOG        65 11 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        64      64   0  2        54      54   0 New Primary PDB database SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 RACPDB   MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 RACPDB   READ WRITE NO SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE; Pluggable database altered. SQL> !hostname srv4.example.com SQL>  New Physical Standby database  SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   MOUNTED 3 RACPDB   MOUNTED SQL> !hostname srv1.example.com SQL>