Manual Switchover - Physical Standby

      ·        GAP - There should not be any gap between primary & standby
·        Convert - Primary to Standby [All clients SELECT queries will hold for 5 minutes]
·        Convert - Standby to Primary [All clients SELECT queries will get executed]
·        Start MRP process on New standby
 
[oracle@primary ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 10 03:51:55 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select name, open_mode, db_unique_name, database_role from v$database;
 
NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
MAXCDB    READ WRITE           maxcdb                         PRIMARY
 
SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
 
STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP
 
[oracle@standby ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 10 04:20:44 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> set line 999
SQL> select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;
 
NAME                                 VALUE                                           DATUM_TIME
-------------------------------- ---------------------------------------- ------------------------------
transport lag                    +00 00:00:00                                  08/10/2024 04:21:12
apply lag                           +00 00:00:00                                  08/10/2024 04:21:12
apply finish time             +00 00:00:00.000
estimated startup time           8
 
[oracle@primary ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 10 04:24:30 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
v  Convert primary to standby
 
SQL> select SWITCHOVER_STATUS from V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO STANDBY
 
SQL> alter database commit to switchover to physical standby with session shutdown;
 
Database altered.
 
SQL> startup mount;
 
ORACLE instance started.
Total System Global Area 1828714320 bytes
Fixed Size                  9135952 bytes
Variable Size             436207616 bytes
Database Buffers         1375731712 bytes
Redo Buffers                7639040 bytes
Database mounted.
 
[oracle@standby ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 10 04:29:18 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

v  Convert standby to primary
 
SQL> select SWITCHOVER_STATUS from V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY
 
SQL> alter database commit to switchover to primary with session shutdown;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> select name, open_mode, db_unique_name, database_role from v$database;
 
NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
MAXCDB    READ WRITE           stand                          PRIMARY
 
[oracle@primary ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 10 04:34:17 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> shu immediate
 
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
 
ORACLE instance started.
Total System Global Area 1828714320 bytes
Fixed Size                  9135952 bytes
Variable Size             436207616 bytes
Database Buffers         1375731712 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.

      v  Start MRP

 
SQL> alter database recover managed standby database disconnect;
 
Database altered.
SQL>  select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
 
NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
MAXCDB    READ ONLY WITH APPLY PHYSICAL STANDBY maxcdb
 
v  Standby [New Primary]
 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     16
Next log sequence to archive   18
Current log sequence           18
 
v  Primary [New Standby]
 
SQL> select PROCESS,STATUS,SEQUENCE# from v$managed_standby;
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              13
DGRD      ALLOCATED             0
DGRD      ALLOCATED             0
ARCH      CLOSING              17
ARCH      CLOSING              16
RFS       IDLE                 18
ARCH      CLOSING              12
RFS       IDLE                  0
RFS       IDLE                  0
MRP0      APPLYING_LOG         18
 
10 rows selected.