Oracle 12c-Step by Step Manual Switchover | Data Guard

Performing Switchover Activity

Convert Primary to Standby

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE  OPEN_MODE   LOG_MODE
----------- ------------------------- -------------------- --------------------
ORADB  PRIMARY                 READ WRITE   ARCHIVELOG

SQL> col destination for a70
SQL>select b.destination,a.dest_id,b.ERROR,max(a.FIRST_TIME),max(a.sequence#) from gv$archived_log a, gv$archive_dest_status b where (a.dest_id='1' or a.applied ='YES')    and a.dest_id=b.dest_id group by  b.destination,a.dest_id,b.ERROR order by a.dest_id;
DESTINATION                                        DEST_ID ERROR    MAX(A.FIR MAX(A.SEQUENCE#)
------------------------------------------------- ------------ ------------ ---------------  ----------------
/u01/app/oracle/product/12.1.0/db_1/dbs           1                      04-AUG-24   68
oradbdr                                                                2                      04-AUG-24   68

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Alter LogFile  

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

Sun Aug 04 19:45:20 2024

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 8103] (oradb)

Sun Aug 04 19:45:20 2024

Waiting for target standby to receive all redo

Sun Aug 04 19:45:20 2024

Waiting for all non-current ORLs to be archived...

Sun Aug 04 19:45:20 2024

All non-current ORLs have been archived.

Sun Aug 04 19:45:20 2024

Waiting for all FAL entries to be archived...

Sun Aug 04 19:45:20 2024

All FAL entries have been archived.

Sun Aug 04 19:45:20 2024

Waiting for potential Physical Standby switchover target to become synchronized...

Sun Aug 04 19:45:21 2024

Active, synchronized Physical Standby switchover target has been identified

Preventing updates and queries at the Primary

Generating and shipping final logs to target standby

Switchover End-Of-Redo Log thread 1 sequence 69 has been fixed

Switchover: Primary highest seen SCN set to 0x0.0x26a747

ARCH: Noswitch archival of thread 1, sequence 69

ARCH: End-Of-Redo Branch archival of thread 1 sequence 69

ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

ARCH: Archiving is disabled due to current logfile archival

Primary will check for some target standby to have received all redo

Waiting for target standby to apply all redo

Final check for a synchronized target standby. Check will be made once.

LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target

Active, synchronized target has been identified

Target has also received all redo

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_8103.trc

Converting the primary database to a new standby database

Clearing standby activation ID 2826499478 (0xa878f596)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

Archivelog for thread 1 sequence 69 required for standby recovery

Switchover: Primary controlfile converted to standby controlfile succesfully.

Switchover: Complete - Database shutdown required

USER (ospid: 8103): terminating the instance

Sun Aug 04 19:45:24 2024

Instance terminated by USER, pid = 8103

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

Shutting down instance (abort)

License high water mark = 5

Sun Aug 04 19:45:24 2024

Instance shutdown complete

Sun Aug 04 19:45:43 2024

On New Standby

[oracle@srv1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 4 19:45:37 2024
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> def
DEFINE _DATE           = "04-AUG-2024 19:45:40" (CHAR)
>DEFINE _CONNECT_IDENTIFIER = "oradb" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "" (CHAR)
DEFINE _O_RELEASE      = "" (CHAR)
SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1761607680 bytes
Fixed Size                    2925360 bytes
Variable Size                587205840 bytes
Database Buffers         1157627904 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE        OPEN_MODE   LOG_MODE
----------- ------------------------------ -------------------- --------------------
ORADB  PHYSICAL STANDBY  READ ONLY     ARCHIVELOG

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE        OPEN_MODE                          LOG_MODE
----------- ------------------------------ ------------------------------------  --------------------
ORADB  PHYSICAL STANDBY  READ ONLY WITH APPLY    ARCHIVELOG

Convert Standby to Primary

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE        OPEN_MODE                          LOG_MODE
----------- ------------------------------ ------------------------------------  --------------------
ORADB  PHYSICAL STANDBY  READ ONLY WITH APPLY    ARCHIVELOG

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS  CLIENT_PROCESS  SEQUENCE# STATUS
-------------- -------------------------  ----------------- ---------------------
ARCH         ARCH                        0                      CONNECTED
ARCH         ARCH                        0                      CONNECTED
ARCH         ARCH                        0                      CONNECTED
ARCH         ARCH                        0                      CONNECTED
RFS             LGWR                       69                    IDLE
RFS             ARCH                        0                      IDLE
MRP0         N/A                             69                    WAIT_FOR_LOG
7 rows selected.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME     DATABASE_ROLE OPEN_MODE    LOG_MODE
------------ ------------------------  -------------------- --------------------
ORADB   PRIMARY                MOUNTED        ARCHIVELOG

SQL> alter database open;
Database altered.

SQL> select INST_ID,name,open_mode,database_role,log_mode,controlfile_type from gv$database;
 INST_ID NAME    OPEN_MODE   DATABASE_ROLE  LOG_MODE     CONTROL
------------ ----------- -------------------- ------------------------- -------------------- -----------------

1               ORADB  READ WRITE   PRIMARY                 ARCHIVELOG  CURRENT
   

Note :- A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-enable either database.

Please feel free to ask. thank you 🙂

Toufique Khan