DGMGRL> show configuration;
Configuration - DGConfig1
Protection Mode: MaxPerformance
Members:
kej01 - Primary database
kej01_stby1 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 66 seconds ago)
1. stop the redo apply process:
DGMGRL> edit database kej01_stby1 set state='APPLY-OFF'; Succeeded.2. remove the standby database from the configuration:
DGMGRL> remove database kej01_stby1; Removed database "kej01_stby1" from the configuration3. Remove the configuration itself:
DGMGRL> remove configuration; Removed configuration4. On both the primary and standby server, edit the $TNS_ADMIN/listener.ora by removing these entries:
(SID_DESC =
(GLOBAL_DBNAME = kej01_DGMGRL)
(ORACLE_HOME = /orasw/19c)
(SID_NAME = kej01)
)
(SID_DESC =
(GLOBAL_DBNAME = kej01_DGMGRL.skead.no)
(ORACLE_HOME = /orasw/19c)
(SID_NAME = kej01)
)
Make sure to stop/start the listener afterwards.
5. On previous standby, remove db_unique_name:
SYS@kej01>SQL>alter system set db_unique_name='kej01' scope=spfile;6. On both servers, stop the broker processes:
SYS@kej01>SQL>alter system set dg_broker_start=false scope=both; System altered.6. On the standby, finish database recovery:
SYS@kej01>alter database recover managed standby database finish; Database altered.The database is still mounted as a physical standby:
SYS@kej01>SQL>select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED PHYSICAL STANDBYIf you open it now, it will be in READ ONLY status:
SYS@kej01>SQL>alter database open; Database altered. SYS@kej01>select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY PHYSICAL STANDBY7. Instruct the former standby database that it is now indeed a normal ("primary") database:
SYS@kej01>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered.The alert log will show that we are cutting all strings to the previous role:
2024-05-29T15:55:09.123174+02:00 ALTER DATABASE SWITCHOVER TO PRIMARY (kej01) 2024-05-29T15:55:09.125019+02:00 Maximum wait for role transition is 15 minutes. TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2024-05-29 15:55:09.125651 TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2024-05-29 15:55:09.126990 TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2024-05-29 15:55:09.130097 Standby terminal recovery start SCN: 4695193 RESETLOGS after incomplete recovery UNTIL CHANGE 4695565 time 05/29/2024 15:28:15 ET (PID:1614518): ORL pre-clearing operation disabled by switchover Online log /log/oradata/kej01/redo1.log: Thread 1 Group 1 was previously cleared Online log /log/oradata/kej01/redo2.log: Thread 1 Group 2 was previously cleared Online log /log/oradata/kej01/redo3.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 4695192 2024-05-29T15:55:16.588617+02:00 Setting recovery target incarnation to 3 2024-05-29T15:55:16.662719+02:00 NET (PID:1614518): Database role cleared from PHYSICAL STANDBY [kcvs.c:1133] Switchover: Complete - Database mounted as primary TMI: kcv_commit_to_so_to_primary Switchover from physical END 2024-05-29 15:55:16.667784 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN 2024-05-29T15:55:27.547369+02:00 alter database openAt this point, my standby database is once again mounted, but it has now assumed the PRIMARY role, which is what I want:
SYS@kej01>SQL>select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED PRIMARY8. Open it now, and it will be in read write mode:
SYS@kej01>SQL>alter database open; Database altered. SYS@kej01>SQL>select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE PRIMARYAccording to the oracle documentation, the syntax above is pre-12.1 style, which is still compatible but DBAs are encouraged to use
ALTER DATABASE SWITCHOVER TO target_db_name [FORCE] [VERIFY]; and ALTER DATABASE FAILOVER TO target_db_name;instead.
