Switchover from Primary Database to Physical Standby Database with SQLPLUS Commands

In today’s article, we will talk about Switchover from Primary Database to Physical Standby Database with SQLPLUS Commands.

Before Switchover, there are a series of checks to see if the system is suitable for Switchover. Some of these controls are general and made for our entire Data Guard Environment, while others are specific to Switchover.

PRE-CONTROLS

1.We check if the cluster services are running.

2.We check the status of instances.

3. We check if the Listeners are working properly. For this, we login to all instances from a third party client.

4. We are trying to connect to the new Physical Standby DB and Logical Standby DB from the new Primary DB, which will be after the switchover.

In the same way, we try one by one from Standby-2.

5. We are questioning which Standby Online Redo Logs are used or not, and their status.

Combinations of ARCHIVED and STATUS columns here mean the following.

ARC    STATUS

NO      UNASSIGNED	Standby Redo Log means archived and suitable for reuse.
YES     UNASSIGNED	Standby Redo Log means it has never been used and is ready to use.
NO     ACTIVE	Indicates that writing to the Standby Redo Log has finished and is waiting for the Standby Redo Log to be archived.
YES    ACTIVE	Indicates that the Standby Redo Log is actively used and has not been archived yet.

6. We check the Role, Type and suitability of archive destinations. This process is important for the archives to go to the Standby Database properly after Switchover.

7. We check whether there is an error in the archive destinations.

8. We check for Redo Transport and Redo Apply LAG.

9. We learn whether there are ERROR and GAP in Redo Transport transactions.

10. We check whether the archives created in the primary are processed in all standbys.

11. We check if the Tempfiles are on the Primary and Standby sides.

12. We check the LOG_ARCHIVE_DEST_n parameters so that the archives can be moved correctly after the switchover.

After this check, if the locations where the new Primary Database will send archives are missing after Switchover, it is defined with the ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’…’ command.

13. We check information such as roles, open modes, modes of databases.

SWITCHOVER OPERATION

After the preliminary checks are completed successfully, we proceed to the Switchover process.

1. It will be useful to perform a Log Switch operation in the Primary Database in order to apply the Redo’s that are created in the system in which the Protection Mode is Maximum Performance and the Redo Apply is in Archived Apply mode, to the Standby side in the fastest time.

Even though we use Real-Time Apply, we do Log Switch to get used to it.

2. It is questioned whether the Primary Database is suitable for Switchover.

When the SWITCHOVER_STATUS column is TO STANDBY or SESSIONS ACTIVE, it is suitable for Switchover. Apart from these, we have to repeat the pre-checks.

3. Switch to the Primary Database Standby role.

The reason for using WITH SESSION SHUTDOWN is that SWITCHOVER STATUS is SESSIONS ACTIVE. If SWITCHOVER STATUS TO_STANDBY came, then WITH SESSION SHUTDOWN would not need to be written.

As a result of this process, the logs falling to the instances are as follows.

[Primary-1]—–>ALERT LOG
[Primary-2]—–>ALERT LOG
[Standby-1]—–>ALERT LOG

–

[Standby-2]—–>ALERT LOG
[Logical-1]—–>ALERT LOG
4. The new Physical Standby Database (Old Primary Database) is mounted.
If we pay attention, both of the instances were closed automatically during the process of converting the current Primary to Physical Standby.
5. The suitability of the new Primary Database (Old Physical Standby Database) for Switchover is questioned.
6. Old Physical Standby Database, Primary Database is created.
As a result of this process, the logs falling into the Alert Log are as follows. [Standby-2]—–>ALERT LOG
7. The new Primary Database is set to OPEN mode.
8. We start the Redo-Apply process in the new Physical Standby Database.
[Primary-1]—–>ALERT LOG
9. Switchover process finished. First, we query the modes of the databases.
10. The 2nd instance of the old Primary, which is automatically closed during switchover, opens.
11. After the opened instances, we start the controls.

a. Database roles are checked in all instances.

b.Switchover operation is performed in the new Primary Database and it is seen whether the logs are processed or not.

c. We check that Redo Transport and Apply Processes are working.

d. We check if there is a GAP in the new Physical Standby Database.

e.We check the validity of archive locations.

While DEST_ID of 2 and 3 in Physical-1 before switchover was WRONG VALID_ROLE, now VALID_NOW parameter in Primary-1 has this value. The reason, of course, is the changing of roles.

f.Finally, we make a general check of the databases.