Description:
- We can alter the database roles of primary and standby according to planned maintenance.
- During a switchover, the original primary database converts to a standby role, and the original standby database converts to the primary role.
- Switchover converts to the standby database manually.
- Without loss of data and resetting logs, called switchover.
Step 1: Check the database role and database name on both databases.
On Primary
SQL> select name, open_mode, and database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
VIYANI READ WRITE PRIMARY
On Standby
SQL> select name, open_mode, and database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
VIYANI MOUNTED PHYSICAL STANDBY
Verify that the switchover process allows or not using the following statement:
SQL> ALTER DATABASE SWITCHOVER TO STANDBY VERIFY;
ALTER DATABASE SWITCHOVER TO STANDBY VERIFY
*
ERROR at line 1:
ORA-16475: succeeded with warnings; check the alert log for more details.
Note: Ignore the error.
Verify the sequence and their applied status.
On Primary
SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
———- ——————– —————– ———
38 06-MAR-2023 23:19:47 06-MAR-2023 23:19:50 YES
39 06-MAR-2023 23:19:50 06-MAR-2023 23:38:46 YES
39 06-MAR-2023 23:19:50 06-MAR-2023 23:38:46 YES
40 06-MAR-2023 23:38:46 06-MAR-2023 23:39:20 YES
40 06-MAR-2023 23:38:46 06-MAR-2023 23:39:20 YES
41 06-MAR-2023 23:39:20 07-MAR-2023 22:50:10 YES
42 07-MAR-2023 22:50:10 08-MAR-2023 17:26:34 YES
42 07-MAR-2023 22:50:10 08-MAR-2023 17:26:34 YES
43 08-MAR-2023 17:26:34 08-MAR-2023 22:23:05 YES
43 08-MAR-2023 17:26:34 08-MAR-2023 22:23:05 YES
44 08-MAR-2023 22:23:05 08-MAR-2023 22:28:33 YES
99 rows were selected.
On Standby
SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
———- ——————– —————– ———
33 06-MAR-2023 23:19:41 06-MAR-2023 23:19:41 YES
34 06-MAR-2023 23:19:41 06-MAR-2023 23:19:44 YES
35 06-MAR-2023 23:19:44 06-MAR-2023 23:19:44 YES
36 06-MAR-2023 23:19:44 06-MAR-2023 23:19:47 YES
37 06-MAR-2023 23:19:47 06-MAR-2023 23:19:47 YES
38 06-MAR-2023 23:19:47 06-MAR-2023 23:19:50 YES
39 06-MAR-2023 23:19:50 06-MAR-2023 23:38:46 YES
40 06-MAR-2023 23:38:46 06-MAR-2023 23:39:20 YES
41 06-MAR-2023 23:39:20 07-MAR-2023 22:50:10 YES
42 07-MAR-2023 22:50:10 08-MAR-2023 17:26:34 YES
43 08-MAR-2023 17:26:34 08-MAR-2023 22:23:05 YES
44 08-MAR-2023 22:23:05 08-MAR-2023 22:28:33 YES
56 rows were selected.
Check the switchover_status in the primary database.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
TO STANDBY
Issue the following query to convert to standby:
SQL> alter database commit to switchover to standby;
Database altered.
Shutdown the database.
SQL> shutdown immediate
ORA-01012: not logged on
Start the database in a nomount state.
SQL> startup nomount
ORACLE instance started.
Total System Global Area: 1543500144 bytes
Fixed Size: 8896880 bytes
Variable Size 1308622848 bytes
Database Buffers 218103808 bytes
Redo Buffers (7876608 bytes)
Mount the database in standby mode.
SQL> alter database mount standby database;
Database altered.
Enable the MRP process as it continues.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Check the database name and database role.
SQL> select name, open_mode, and database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
VIYANI MOUNTED PHYSICAL STANDBY
On Standby
If you check the status, It will show standby mode.
SQL> select name, open_mode, and database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
VIYANI MOUNTED PHYSICAL STANDBY
Issue the query to convert to primary.
SQL> alter database commit to switchover to primary;
Database altered.
Check the database name and database role.
SQL> select name, open_mode, and database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
VIYANI MOUNTED PRIMARY
Open the database.
SQL> alter database open;
Database altered.
Check the database name and database role now.
SQL> select name, open_mode, and database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
VIYANI READ WRITE PRIMARY
- The switch-over activity has been completed successfully; our old primary database has become standby, and our old standby database has become primary.
- Note: To test the above switchover activity, generate multiple archive logs on the primary database and verify that those archive logs are being transferred to the standby database.
On primary
SQL> select thread#, max(sequence#), “Last Primary Sequence Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1; 2 3 4
THREAD# Last Primary Sequence Generated
———- ————————–
1 33
On Standby
SQL> select thread#, max(sequence#), “Last Standby Sequence Applied”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in (‘YES’,’IN-MEMORY’)
group by thread# order by 1; 2 3 4 5
THREAD# Last Standby Seq Applied
———- ————————
1 33
- Once we’ve done the planned activity, we can switch back to the same
- Follow the same procedure as the above method. We can get it back.
Information about the switchover status
Not Allowed:-
- This is mentioned as a primary database, and there are no standby databases.
Session Active:-
- This indicates that there are active SQL sessions attached to the primary
Switchover Pending:-
- This is a standby database, and the primary database switchover request has been received but not processed.
To Primary:-
- This is a standby database with no active sessions that is allowed to switch over to a primary database.
To Standby:
- This is a primary database with no active sessions that is allowed to switch over to a standby database.
Recovery Needed: –
- This is a standby database that has not received the switchover request.
