Oracle Cloud Infrastructure Documentation

Switchover

You can manually select one of the MySQL instances as the primary instance. This is called a switchover.

When you perform a switchover, the preferred placement and current placement change to the newly selected placement of the primary instance. The IP address of the DB system endpoint does not change. If the current placement changes in a switchover, it results in a short period of downtime while the primary instance is redirected to the newly promoted instance, you must reopen all database connections.
Note

No downtime occurs when the current placement does not change in a switchover. This occurs when you switchover to the existing primary instance. You can do this to change the preferred placement to the current placement.
Following is the switchover process of a DB system that has been created or upgraded after February 25, 2025 when there is a change in the current placement:
  • New transactions are blocked from the time the switchover is initiated.
  • Running transactions are allowed to continue to execute. After 5 minutes, any running transactions that have not completed are aborted and rolled back.
  • Immediately after all running transactions have completed or aborted, the DB system endpoint is assigned to the newly promoted primary instance.
  • Existing connections to the previous primary instance are closed. The client application must reopen new connections.
Following is the switchover process of a DB system that has been created before February 25, 2025 and has not been upgraded after February 25, 2025 when there is a change in the current placement:
  • Running transactions are allowed to complete. The switchover process waits until all running transactions finish and commit.
  • New transactions are accepted and can perform reads and writes but, when the MySQL instance is promoted to be the new primary instance, and the connection to the previous primary instance is broken, all uncommitted transactions are rolled back.
  • The DB system endpoint is assigned to the newly promoted primary instance.
  • Existing connections to the previous primary instance are closed. The client application must reopen new connections.
Note

After a switchover, the current binary log file name and position of the new primary may be different from the old primary. As the binary logs of each instance are managed independently, each transaction recorded in the binary logs may be written to a different binary log file and position in different instances.

Switchover Scenarios

You can use switchover in the following scenarios:

  • Application testing: Ensuring your application works correctly with the newly promoted primary instance.
  • Proximity of availability domain: While fault domains are contained in the same data center, availability domains are spread across different data centers. Even though these availability domains are connected with a low-latency network, it may be necessary to test that latency by moving primary instance from availability domain to availability domain to confirm the best location for your primary instance. For example, switching to an instance in the same availability domain as the application which connects to its.

HeatWave Cluster Support

When the current placement changes in a switchover, the HeatWave cluster is detached from the previous primary instance. If the new primary instance is located in the same availability domain (AD) as the previous primary instance, the same HeatWave cluster is reused and it is attached to the new primary instance. If the new primary instance is located in a different AD, the existing HeatWave cluster is deleted. A new HeatWave cluster has to be created in the same AD as the new primary instance and it is attached to the new primary instance. The data in the HeatWave cluster is automatically recovered from the Storage Layer or reloaded from the DB system or Lakehouse Object Storage.

When the current placement changes in a switchover, a long running query offloaded to the HeatWave cluster can get aborted. In this case, you need to reconnect and rerun the query after the HeatWave cluster is ready.

Use one of the following method to perform a switchover:

Using the Console

Use the Console to promote a secondary MySQL instance as the primary instance.

This task requires the following:
  • A running DB system with high availability enabled.
Do the following to switch from the current primary instance to one of the secondary instances:
  1. Open the navigation menu. Under HeatWave MySQL, click DB Systems.
  2. Choose the compartment from the List Scope.
  3. In the list of DB systems, find the DB system you want to switch, and do one of the following:
    • Choose Switchover from the Actions menu on the same line as the DB system.
    • Click the name of the DB system to open the DB System Details page. Select Switchover from the More Actions menu.
  4. In the Switchover dialog box, select the availability or fault domain, depending on your setup, which contains the instance you want to switch to.
  5. Click Switchover.
The status of the DB system changes to Updating, and the selected instance becomes the primary.

Using the CLI

Use the command-line interface to promote a secondary MySQL instance to the primary instance.

This task requires the following:
  • The OCID of a running DB system with high availability enabled.
  • A policy that permits you to update DB system.
  • A properly configured CLI installation. See Command Line Interface.
Do the following to switch from the current primary instance to one of the secondary instances:
  1. Open the command-line interface and list all the availability domains in the current region:
    oci iam availability-domain list
  2. Switchover to the desired MySQL instance:
    • For multi-availability domain: 
      oci mysql db-system update --db-system-id <DBSystemOCID> --availability-domain <SelectedAvailabilityDomain>
    • For single-availability domain:
      oci mysql db-system update --db-system-id <DBSystemOCID> --fault-domain <SelectedFaultDomain>

    Where

    • db-system-id: Specify the OCID of the DB system.
    • availability-domain: Specify the name of the availability domain in which the secondary MySQL instance, which you want to promote to the primary, is present.
    • fault-domain: Specify the name of the fault domain in which the secondary MySQL instance, which you want to promote to the primary, is present.
The status of the DB system changes to Updating, and the instance in the selected availability or fault domain becomes the primary instance and the read/write endpoint.