Oracle 12c-Step by Step Manual Switchover | Data Guard ~ Oracle DBA Secrets

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> alter database recover managed standby database disconnect from session;
Database altered.

Convert Standby to Primary

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> 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

Oracle DBA Secrets

Thursday, September 12, 2024