Switchover|V4.2.1| docs|Distributed Database

Switchover

Last Updated：2023-12-25 08:49:41 Updated

When the primary tenant is available, you can switch the roles of the primary tenant and a standby tenant without data loss.

This topic describes how to perform a switchover operation in two deployment modes of the Physical Standby Database solution.

Limitations

Before you switch a standby tenant to the PRIMARY role, all log stream replicas of the standby tenant must be online. Otherwise, you must wait until the offline replicas are permanently offline. You can log on to the standby tenant or the sys tenant of the cluster where the standby tenant resides and query the DBA_OB_LS or CDB_OB_LS view to check whether all log stream replicas are online. For more information about log stream replicas, see About replicas.

Network-based Physical Standby Database solution

In the network-based Physical Standby Database solution, a switchover operation is completed in three steps: Switch the primary tenant to the STANDBY role, switch the standby tenant to the PRIMARY role, and specify a log restore source for the original primary tenant.

Notice

When you perform a switchover operation, you must switch the primary tenant to the STANDBY role before you switch a standby tenant to the PRIMARY role. Otherwise, two primary tenants exist. As a result, the execution of the ALTER SYSTEM SWITCHOVER TO PRIMARY statement fails.

Switch the primary tenant to the STANDBY role.
1. Log on as the administrator to the primary tenant or the sys tenant of the cluster where the primary tenant resides.
2. Execute the SWITCHOVER statement to switch the primary tenant to the STANDBY role.
  - Execute the following statement in the sys tenant of the cluster where the primary tenant resides to switch the primary tenant to the STANDBY role:
```
ALTER SYSTEM SWITCHOVER TO STANDBY TENANT = tenant_name;
```
    For example:
```
ALTER SYSTEM SWITCHOVER TO STANDBY TENANT = mysql;
```
  - Execute the following statement in the primary tenant to switch it to the STANDBY role:
```
ALTER SYSTEM SWITCHOVER TO STANDBY;
```
3. Query the DBA_OB_TENANTS view to check whether the original primary tenant is switched to the STANDBY role.
  - Query the view from the sys tenant of the cluster where the primary tenant resides
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
```
  - Query the view from the primary tenant
    
    MySQL mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
```
    Oracle mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM SYS.DBA_OB_TENANTS;
```
  The query result is as follows:
```
+-------------+-------------+-------------+-------------------+
| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-------------+-------------+-------------+-------------------+
| mysql       | USER        | STANDBY     | NORMAL            |
+-------------+-------------+-------------+-------------------+
1 row in set
```
  In the query result, the value of TENANT_ROLE is STANDBY and that of SWITCHOVER_STATUS is NORMAL, which indicates that the original primary tenant is switched to the STANDBY role.
Switch a standby tenant to the PRIMARY role.
1. Log on as the administrator to the standby tenant or the sys tenant of the cluster where the standby tenant resides.
2. Execute the SWITCHOVER statement to switch the standby tenant to the PRIMARY role.
  - Execute the following statement in the sys tenant of the cluster where the standby tenant resides to switch the standby tenant to the PRIMARY role:
```
ALTER SYSTEM SWITCHOVER TO PRIMARY TENANT = tenant_name;
```
    For example:
```
ALTER SYSTEM SWITCHOVER TO PRIMARY TENANT = standby_tenant;
```
  - Execute the following statement in the standby tenant to switch it to the PRIMARY role:
```
ALTER SYSTEM SWITCHOVER TO PRIMARY;
```
3. Query the DBA_OB_TENANTS view to check whether the original standby tenant is switched to the PRIMARY role.
  - Query the view from the sys tenant of the cluster where the standby tenant resides
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
```
  - Query the view from the standby tenant
    
    MySQL mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
```
    Oracle mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM SYS.DBA_OB_TENANTS;
```
  The query result is as follows:
```
+-----------------+-------------+-------------+-------------------+
| TENANT_NAME     | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-----------------+-------------+-------------+-------------------+
| standby_tenant  | USER        | PRIMARY     | NORMAL            |
+-----------------+-------------+-------------+-------------------+
1 row in set
```
  In the query result, the value of TENANT_ROLE is PRIMARY and that of SWITCHOVER_STATUS is NORMAL, which indicates that the original standby tenant is switched to the PRIMARY role.
Specify a log restore source for the original primary tenant.

You can specify a log restore source before or after the switchover.
1. Log on as the administrator to the original primary tenant or the sys tenant of the cluster where the original primary tenant resides.
2. Specify the log restore source.
  - Execute the following statement in the sys tenant of the cluster where the original primary tenant resides to specify the log restore source for the original primary tenant:
```
ALTER SYSTEM SET LOG_RESTORE_SOURCE ='SERVICE=$ip_list USER=$user_name@$tenant_name PASSWORD=$password' TENANT = tenant_name;
```
  - Execute the following statement in the original primary tenant to specify its log restore source:
```
ALTER SYSTEM SET LOG_RESTORE_SOURCE ='SERVICE=$ip_list USER=$user_name@$tenant_name PASSWORD=$password';
```
  The parameters are described as follows:
  - $ip_list: the IP addresses and RPC port numbers of the OBServer nodes where replicas of the new primary tenant reside.
  - $user_name: the dedicated user created in the new primary tenant for accessing system views.
  - $tenant_name: the name of the new primary tenant to connect to.
  - $password: the password of the dedicated user for accessing system views.
  For example:
```
ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=11.xx.xx.22:17855;11.xx.xx.23:17857;11.xx.xx.24:17859 USER=rep_user2@standby_tenant PASSWORD=******' TENANT = mysql;
```
```
ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=11.xx.xx.22:17855;11.xx.xx.23:17857;11.xx.xx.24:17859 USER=rep_user2@standby_tenant PASSWORD=******';
```
  For more information, see Specify a log restore source.
3. Query the DBA_OB_TENANTS view to check whether the original primary tenant is in the continuous log synchronization state.
  
  Continuous log synchronization is automatically enabled after the switchover. Therefore, you do not need to perform any operation to enable it after you specify the log restore source. You can query the DBA_OB_TENANTS view to confirm the synchronization status of the original primary tenant.
  - Query the view from the sys tenant of the cluster where the original primary tenant resides
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN), RECOVERY_UNTIL_SCN(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
```
  - Query the view from the original primary tenant
    
    MySQL mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN), RECOVERY_UNTIL_SCN(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
```
    Oracle mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN), RECOVERY_UNTIL_SCN(SYNC_SCN) FROM SYS.DBA_OB_TENANTS;
```
  The query result in MySQL mode is as follows:
```
+-------------+-------------+-------------+-------------------+----------------------------+---------------------+
| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS | SCN_TO_TIMESTAMP(SYNC_SCN) | RECOVERY_UNTIL_SCN  |
+-------------+-------------+-------------+-------------------+----------------------------+---------------------+
| mysql       | USER        | STANDBY     | NORMAL            | 2023-04-18 13:46:20.887793 | 4611686018427387903 |
+-------------+-------------+-------------+-------------------+----------------------------+---------------------+
1 row in set
```
  In the query result, the value of the RECOVERY_UNTIL_SCN field is equal to the value of MAX SCN (4611686018427387903), which indicates that the original primary tenant is in the continuous log synchronization state.

Physical Standby Database solution based on log archiving

In the Physical Standby Database solution based on log archiving, a switchover operation is completed in three steps: Enable archivelog for the standby tenant, switch the primary tenant to the STANDBY role, and switch the standby tenant to the PRIMARY role.

Notice

When you perform a switchover operation, you must switch the primary tenant to the STANDBY role first. Otherwise, two primary tenants exist. In the Physical Standby Database solution based on log archiving, the standby tenant does not detect the status of the original primary tenant. Therefore, if you switch the standby tenant to the PRIMARY role first, the system does not return an error when you execute the ALTER SYSTEM SWITCHOVER TO PRIMARY statement.

Enable archivelog for the standby tenant.

In the Physical Standby Database solution based on log archiving, after the switchover succeeds, the original primary tenant must continuously read archived logs from the new primary tenant, namely, the original standby tenant, to synchronize all modifications in the new primary tenant. Therefore, you must enable archivelog for the new primary tenant. To ensure log continuity, you must enable archivelog for the new primary tenant before the switchover.
1. Log on as the administrator to the standby tenant or the sys tenant of the cluster where the standby tenant resides.
2. Specify the archiving destination of the standby tenant.
  
  Notice
  
  You must configure different archiving paths for different tenants.
  
  To do so, specify the following attributes: LOCATION, PIECE_SWITCH_INTERVAL, and BINDING.
  - Execute the following statement in the sys tenant of the cluster where the standby tenant resides to specify the archiving destination of the standby tenant:
```
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=archive_path [PIECE_SWITCH_INTERVAL=piece_switch_interval] [BINDING=archive_mode]' TENANT = tenant_name;
```
  - Execute the following statement in the standby tenant to specify the archiving destination of the current tenant:
```
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=archive_path [PIECE_SWITCH_INTERVAL=piece_switch_interval] [BINDING=archive_mode]';
```
  The attributes are described as follows:
  - The LOCATION attribute specifies the archiving destination of the primary tenant. OceanBase Database supports Network File System (NFS) or Alibaba Cloud Object Storage Service (OSS) as the archiving media of the archiving destination.
  - The BINDING attribute specifies the prioritizing mode for archiving and businesses. The BINDING attribute has two values: Optional and Mandatory. If you do not specify this attribute, the default value Optional takes effect.
    - The Optional mode prioritizes user businesses. In this mode, if log archiving falls behind log generation, logs may be recycled before being archived. This causes interruption.
    - The Mandatory mode prioritizes archiving. In this mode, you may be unable to write more data before existing data is archived.
  - The PIECE_SWITCH_INTERVAL attribute specifies the switching interval for pieces. The value range is [1d,7d]. If you do not specify this attribute, the default value 1d takes effect.
  Assuming that the archiving medium is NFS, execute the following sample statement to set the archiving path of the standby tenant to file:///data/1/standby2_archive2/, and the archiving mode to Optional:
```
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/1/standby2_archive2/ BINDING=optional' TENANT = standby2;
```
```
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/1/standby2_archive2/ BINDING=optional';
```
3. Enable archivelog for the standby tenant.
  - Execute the following sample statement in the sys tenant of the cluster where the standby tenant resides to enable archivelog for the standby tenant:
```
ALTER SYSTEM ARCHIVELOG TENANT = standby2;
```
    Here, standby2 indicates the name of the standby tenant, which must be specified based on your database configurations.
    - Execute the following statement in the standby tenant to enable archivelog for the current tenant:
      ALTER SYSTEM ARCHIVELOG;
  1. Check whether the archiving status of the standby tenant is DOING.
    - Query the view from the sys tenant of the cluster where the standby tenant resides
      SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.CDB_OB_ARCHIVELOG;
    - Query the view from the standby tenant
      
      MySQL mode:
      SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.DBA_OB_ARCHIVELOG;
      Oracle mode:
      SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.DBA_OB_ARCHIVELOG;
      The query result in MySQL mode is as follows:
      +---------+----------+---------+--------+---------------------+----------------------------+---------------------------------------+ | DEST_ID | ROUND_ID | DEST_NO | STATUS | CHECKPOINT_SCN | CHECKPOINT_SCN_DISPLAY | PATH -| +---------+----------+---------+--------+---------------------+----------------------------+---------------------------------------+ | 1001 | 1 | 0 | DOING | 1680265982125159110 | 2023-03-31 20:33:02.125159 | file:///data/1/standby2_archive2 -| +---------+----------+---------+--------+---------------------+----------------------------+---------------------------------------+ 1 row in set
    In the query result, the value of STATUS is DOING, which indicates that the system is archiving logs. CHECKPOINT_SCN and CHECKPOINT_SCN_DISPLAY indicate the archiving progress.
    
    For more information, see View the archiving progress.
4. Switch the primary tenant to the STANDBY role.
  1. Log on to the database as the administrator of the primary tenant or the sys tenant of the cluster where the primary tenant resides.
  2. Execute the SWITCHOVER statement to switch the primary tenant to the STANDBY role.
    - Execute the following statement in the sys tenant of the cluster where the primary tenant resides to switch the primary tenant to the STANDBY role:
      ALTER SYSTEM SWITCHOVER TO STANDBY TENANT = tenant_name;
    - Execute the following statement in the primary tenant to switch it to the STANDBY role:
      ALTER SYSTEM SWITCHOVER TO STANDBY;
  3. Query the DBA_OB_TENANTS view to check whether the original primary tenant is switched to the STANDBY role.
    - Query the view in the sys tenant of the cluster where the primary tenant resides:
      SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='mysql2';
    - Query the view from the primary tenant
      
      MySQL mode:
      SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='mysql2';
      Oracle mode:
      SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM SYS.DBA_OB_TENANTS WHERE TENANT_NAME='mysql2';
    The query result is as follows:
```
+-----------+-------------+-------------+-------------+-------------------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-----------+-------------+-------------+-------------+-------------------+
|      1004 | oracle      | USER        | STANDBY     | NORMAL            |
+-----------+-------------+-------------+-------------+-------------------+
1 row in set
```
    In the query result, the value of TENANT_ROLE is STANDBY and that of SWITCHOVER_STATUS is NORMAL, which indicates that the original primary tenant is switched to the STANDBY role.
  4. Check whether archiving for the original primary tenant is completed by querying the value of the SYNCHRONIZED field in the V$OB_ARCHIVE_DEST_STATUS view.
    
    In the Physical Standby Database solution based on log archiving, the standby tenant must read complete archived logs of the original primary tenant before you switch the standby tenant to the PRIMARY role. Logs are archived in asynchronous mode. Therefore, after you switch the original primary tenant to the STANDBY role, you must verify the integrity of archived logs of the original primary tenant.
    - MySQL mode
      SELECT * FROM oceanbase.V$OB_ARCHIVE_DEST_STATUS WHERE TENANT_ID = 1004;
    - Oracle mode
      SELECT * FROM SYS.V$OB_ARCHIVE_DEST_STATUS WHERE TENANT_ID = 1004;
    The query result in MySQL mode is as follows:
```
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
| TENANT_ID | DEST_ID | PATH                           | STATUS | CHECKPOINT_SCN      | SYNCHRONIZED | COMMENT |
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
|      1004 |    1001 | file:///data/1/sh_archive      | DOING  | 1684638569520797277 | YES          |         |
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
1 row in set
```
    If the value of the SYNCHRONIZED field is YES in the query result, the log archiving of the original primary tenant is completed.
    
    Notice
    
    Before you switch a standby tenant to the PRIMARY role, make sure that the value of the SYNCHRONIZED field is YES. Otherwise, data of the new primary tenant may be incomplete after the switchover.
Switch a standby tenant to the PRIMARY role.
1. Log on to the database as the administrator of the standby tenant or the sys tenant of the cluster where the standby tenant resides.
2. Execute the SWITCHOVER statement to switch the standby tenant to the PRIMARY role.
  
  Notice
  
  Before the SWITCHOVER statement is executed, the system checks whether all logs are synchronized from the restore source to the standby tenant. Therefore, the execution time of the SWITCHOVER statement depends on the progress of synchronization between the standby tenant and the original primary tenant.
  - Execute the following statement in the sys tenant of the cluster where the standby tenant resides to switch the standby tenant to the PRIMARY role:
```
ALTER SYSTEM SWITCHOVER TO PRIMARY TENANT = tenant_name;
```
  - Execute the following statement in the standby tenant to switch it to the PRIMARY role:
```
ALTER SYSTEM SWITCHOVER TO PRIMARY;
```
  1. Query the DBA_OB_TENANTS view to check whether the original standby tenant is switched to the PRIMARY role.
    - Query the view from the sys tenant of the cluster where the original standby tenant resides
      SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='standby2';
    - Query the view from the new primary tenant
      
      MySQL mode:
      SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='standby2'';
      Oracle mode:
      SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM SYS.DBA_OB_TENANTS WHERE TENANT_NAME='standby2'';
    The query result is as follows:
```
+-----------+----------------------------+-------------+-------------+-------------------+
| TENANT_ID | TENANT_NAME                | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-----------+----------------------------+-------------+-------------+-------------------+
|      1006 | standby2                   | USER        | PRIMARY     | NORMAL            |
+-----------+----------------------------+-------------+-------------+-------------------+
1 row in set
```
    In the query result, the value of TENANT_ROLE is PRIMARY, and that of SWITCHOVER_STATUS is NORMAL, which indicates that the original standby tenant is switched to the PRIMARY role.
Specify a restore source for the new standby tenant, namely, the original primary tenant, to receive archived logs from the new primary tenant.

You can specify a log restore source before or after the switchover.
1. Log on to the database as the administrator of the new standby tenant or the sys tenant of the cluster where the new standby tenant resides.
2. Specify a restore source for the new standby tenant.
  - Execute the following statement in the sys tenant of the cluster where the new standby tenant resides to specify the restore source for the new standby tenant:
```
ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path' TENANT = tenant_name;
```
  - Execute the following statement in the new standby tenant to specify its restore source:
```
ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path';
```
  The LOCATION attribute specifies the archiving destination of the new primary tenant.
  
  For example:
```
obclient [SYS]> ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=file:///data/1/standby2/archive2' TENANT = mysql2;

obclient [SYS]> ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=file:///data/1/standby2/archive2';
```
3. Query the DBA_OB_TENANTS view to check whether the original primary tenant is in the continuous log synchronization state.
  
  Continuous log synchronization is automatically enabled after the switchover. Therefore, you do not need to perform any operation to enable it after you specify the log restore source. You can query the DBA_OB_TENANTS view to confirm the synchronization status of the original primary tenant.
  - Query the view from the sys tenant of the cluster where the original primary tenant resides
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN), RECOVERY_UNTIL_SCN(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
```
  - Query the view from the original primary tenant
    
    MySQL mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN), RECOVERY_UNTIL_SCN(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
```
    Oracle mode:
```
SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN), RECOVERY_UNTIL_SCN(SYNC_SCN) FROM SYS.DBA_OB_TENANTS;
```
  The query result in MySQL mode is as follows:
```
+-------------+-------------+-------------+-------------------+----------------------------+---------------------+
| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS | SCN_TO_TIMESTAMP(SYNC_SCN) | RECOVERY_UNTIL_SCN  |
+-------------+-------------+-------------+-------------------+----------------------------+---------------------+
| mysql2      | USER        | STANDBY     | NORMAL            | 2023-05-21 11:41:46.432851 | 4611686018427387903 |
+-------------+-------------+-------------+-------------------+----------------------------+---------------------+
```
  In the query result, the value of the RECOVERY_UNTIL_SCN field is equal to the value of MAX SCN (4611686018427387903), which indicates that the original primary tenant is in the continuous log synchronization state.