Here is my config:
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 configuration
3. Remove the configuration itself:
DGMGRL> remove configuration;
Removed configuration
4. 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 STANDBY
If 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 STANDBY
7. 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 open
At 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 PRIMARY
8. 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 PRIMARY
According 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.
