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.

It is recommended that you perform a switchover during a period of low activity, or reduce or pause write operations on the DB system where possible. Avoid performing a switchover while large or long-running transactions are being applied.

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 transactions that have not reached the commit state are aborted and rolled back. Transactions that have already entered the commit state are allowed to complete. The switchover process waits until all running transactions have completed and MySQL has finished rolling back any aborted transactions.

    Large or long-running transactions and high system load can increase the time required for transactions to complete or roll back. New transactions remain blocked during this time. Therefore, a switchover can take longer than 5 minutes to complete.

  • The DB system endpoint is assigned to the newly promoted primary instance immediately after all running transactions have completed and aborted transactions have finished rolling back.
  • 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. On the DB systems list page, select the DB system that you want to work with. If you need help finding the list page or the DB system, see Listing DB Systems - Using the Console.

    The details page opens and displays information about the DB system.

  2. Select the Details tab.
  3. Select Switchover in the Preferred availability domain or Preferred fault domain field:
  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.
    Note

    Both the current placement and the preferred placement will be set to the selected availability or fault domain. If you select the current placement where the existing primary instance is located, it will only update the preferred placement without incurring any downtime.
  5. Select 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.