Activating the Standby Database: Performing a Failover in Oracle 19c

In this post, we will demonstrate how to activate a physical standby database and convert it into a primary database using SQL*Plus commands. This operation is typically done during failover scenarios.

C:\Windows\system32>sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 10 15:07:02 2025
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> set line 9999 pages 9999
SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PHYSICAL STANDBY MOUNTED              ARCHIVELOG

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#,BLOCKS, STATUS FROM V$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE#     BLOCKS STATUS
--------- -------- ---------- ---------- ------------
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
MRP0      N/A          752466          0 WAIT_FOR_GAP

7 rows selected.

SQL> select max(sequence#) from v$archived_log where applied='yes';

MAX(SEQUENCE#)
--------------
       752465

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL>  alter database recover managed standby database finish ;

Database altered.

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#,BLOCKS, STATUS FROM V$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE#     BLOCKS STATUS
--------- -------- ---------- ---------- ------------
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED

6 rows selected.

MRP0 process should now be gone, indicating recovery has been finished.

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PHYSICAL STANDBY MOUNTED              ARCHIVELOG

SQL>  alter database activate standby database;

Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PRIMARY          MOUNTED              ARCHIVELOG

SQL>
SQL> alter database open ;

Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PRIMARY          READ WRITE           ARCHIVELOG

Once the standby is activated and opened as primary, it cannot be reverted back to a standby. A fresh standby must be recreated if required.
      

Please feel free to ask. thank you 🙂

Toufique Khan