RECOVER TABLE|V4.3.5| docs|Distributed Database

RECOVER TABLE

Last Updated：2026-02-03 08:48:37 Updated

Purpose

The ALTER SYSTEM RECOVER TABLE statement is used to recover table data. Specifically, it allows you to restore a user-specified table from a backup to an existing tenant. This existing tenant can be the same as the original tenant where the table was located or a different tenant, even in a different cluster.

Limitations and considerations

When recovering a table, only user tables can be recovered. Temporary tables, views, indexes, and other objects cannot be recovered separately.
When recovering a table, foreign keys, triggers, and statistics on the table cannot be recovered.
When recovering a table, the compatibility of the source tenant and the target tenant must be consistent. For example, both must be Oracle-compatible tenants or both must be MySQL-compatible tenants.
Similar to tenant recovery, table recovery currently only supports restoring tables from a lower version backup to the same version or a higher version. Reverse recovery between minor versions within the same major version is not supported.
When recovering a table, the specified table name must match the actual stored table name in the system. For example, if a table named test is created in an Oracle-compatible tenant, but the system internally stores it as TEST, you must specify the table name as TEST during recovery. Otherwise, the system will return an error indicating that the table does not exist.
The ALTER SYSTEM RECOVER TABLE statement requires the use of an auxiliary tenant. Therefore, before executing the statement, ensure that the necessary resource pools have been created for the auxiliary tenant within the cluster of the target tenant. For detailed instructions on creating the required resource pools for the auxiliary tenant, see Preparations for table-level recovery.

Privilege requirements

Only the root user of the sys tenant (root@sys) can execute the ALTER SYSTEM RECOVER TABLE statement.

Syntax

ALTER SYSTEM
    RECOVER TABLE table_name_list
    TO [TENANT [=]] dest_tenant_name
    FROM uri [ UNTIL {TIME ='timestamp'}| {SCN = scn} ]
    WITH 'restore_option' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password']
    [REMAP TABLE remap_table_name_list]
    [REMAP TABLEGROUP remap_tablegroup_list]
    [REMAP TABLESPACE remap_tablespace_list]
    [DESCRIPTION [=] description];

table_name_list:
    database_name.table_name [, database_name.table_name ...]

remap_table_name_list:
    /* Keep the database name unchanged and only change the table name */
    database_name.old_table_name:new_table_name
    /* Keep the table name unchanged and only change the database name */
    |old_database_name.table_name:new_database_name.table_name
    /* Change both the database name and the table name  */
    | old_database_name.old_table_name:new_database_name.new_table_name
    /* Restore all tables from the source database to a new database */
    | old_database_name.`*`:new_database_name.`*`

remap_tablegroup_list:
    old_tablegroup_name:new_tablegroup_name

remap_tablespace_list:
    old_tablespace_name:new_tablespace_name

Parameters

Parameter	Description
table_name_list	The name of the table to be restored. The format is `database_name.table_name1,database_name.table_name2,...`, where the names of multiple tables are separated by commas (,). The value of `table_name` must be consistent with the actual name of the table stored in the system. For example, if a table named `test` is created in an Oracle mode tenant, the system stores the table as `TEST`. Therefore, you must specify `TEST` as the table name when you restore the table. Otherwise, the system returns an error indicating that the table does not exist. To restore all tables in a database, you can use `` to replace `table_name`, that is, `database_name.`. To restore all tables, you can use `` to replace both `database_name` and `table_name`, that is, `.*`. If `database_name` or `table_name` contains special characters, you must enclose the value in backticks (`).
dest_tenant_name	The name of the tenant to which the table is to be restored. In the current version, only user tenants are supported.
uri	The path to the data backup and log archive. If the data backup is initiated by using the `PLUS ARCHIVELOG` option, you only need to specify one path. Otherwise, you need to separately specify the paths to the data backup and log archive (at least two paths), for example, `'file:///backup/archive, file:///backup/data'`. For more information about data backup options, see the BACKUP command.
UNTIL	The endpoint to which the data is to be restored. The data is restored to this endpoint, including this endpoint. When you specify the endpoint as TIME or SCN, you must use `=` to connect the specified value. If you do not specify the `UNTIL` clause, the data is restored to the latest endpoint by default. `TIME ='timetamp'`: the timestamp to which the data is to be restored. `SCN = scn`: the System Change Number (SCN) to which the data is to be restored, including this SCN.
restore_option	The `pool_list`, `locality`, `primary_zone`, and `concurrency` of the auxiliary tenant. The parameters are separated by `&`: `pool_list` is a required parameter. You must specify the resource pool of the user. Multiple resource pools are separated by commas (,). `locality` is an optional parameter. You can specify the Locality information of the auxiliary tenant replica distribution. The value must match the Zone information in the `pool_list` of the cluster where the auxiliary tenant is located. If you do not specify this value, the system sets one F replica for each Zone in the `zone_list` of the `resource_pool`. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone` is an optional parameter. You can specify the preferred location of the leader replica of the auxiliary tenant. The value must match the `pool_list` and `locality`, that is, the Zone information must match and the `primary_region` must contain at least two Paxos members. If you do not specify this value, the system randomly distributes the leader replicas to the Zones in the `zone_list` of `locality`. Example: `primary_zone='z1'`. `concurrency` is an optional parameter. You can specify the concurrency of the restore. If you do not specify this parameter, the concurrency is equal to the MAX_CPU value assigned to the auxiliary tenant by default. Example: `concurrency=50`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	The information about the key backup of the encrypted tenant. This parameter is optional. You need to specify the key backup information only when the source tenant is configured with transparent encryption. The parameter includes the following subparameters: `backup_key_path`: specifies the backup path of the key. `password`: specifies the encryption password set when the key is backed up.
REMAP	The mapping relationship. If you do not specify `REMAP`, the system attempts to restore the table to the same TableSpace->Table Group->Database as the original table.
remap_table_name_list	The new name of the restored table. You can rename only the table name without changing the database to which the table belongs. You can also keep the table name unchanged and rename the table to another database. You can also rename both the table name and the database to which the table belongs. The source object and the renamed object are connected by a colon (:).
remap_tablegroup_list	The new name of the table group to which the table belongs. If the source table is bound to a table group, the system restores the table to the same-named table group in the destination tenant by default. If the same-named table group does not exist in the destination tenant, the table restoration fails. If the destination tenant contains other table groups, you can use this statement to restore the table to other table groups. The source object and the renamed object are connected by a colon (:).
remap_tablespace_list	The new name of the table space to which the table belongs. In OceanBase Database, a table space is a logical unit that is mainly used for data encryption. If the source table is bound to a table space, the system restores the table to the same-named table space in the destination tenant by default. If the same-named table space does not exist in the destination tenant, the table restoration fails. If the destination tenant contains other table spaces, you can use this statement to restore the table to other table spaces. The source object and the renamed object are connected by a colon (:).
description	The description of the operation. This parameter is optional.

Examples

In the test database of the MySQL-compatible tenant obt, the data backup path is set to 'file:///data/backup/data', and the log archiving path is set to 'file:///data/backup/archive'. Log archiving is enabled, and a full data backup has been performed. During the operation, the student table was mistakenly deleted. The following example demonstrates how to restore the test.student table of a tenant to the time point '2023-08-21 19:47:20' using table-level recovery and rename it to student_recover.

Create the resources required for the table-level recovery process in the auxiliary tenant under the sys tenant. In this example, create a resource pool with the 4c4g specification.

Create a resource unit.

obclient [oceanbase]> CREATE RESOURCE UNIT recover_4c5g MAX_CPU 4, MEMORY_SIZE = '5G', MAX_IOPS 1024, MIN_IOPS=1024;

Create a resource pool.

obclient [oceanbase]> CREATE RESOURCE POOL recover_tmp_pool UNIT = 'recover_4c5g', UNIT_NUM = 1, ZONE_LIST = ('z1','z2','z3');

Restore the student table to the test database of the target tenant obt, rename it to student_recover, use the data backup path file:///data/backup/data, the log archive path file:///data/backup/archive, and restore it to the time point '2023-08-21 19:47:20'. The auxiliary tenant uses the Resource Pool recover_tmp_pool and the Primary Zone z1.
```
obclient [oceanbase]> ALTER SYSTEM RECOVER TABLE test.student TO TENANT obt FROM 'file:///data/backup/data,file:///data/backup/archive' UNTIL TIME='2023-08-21 19:47:20' WITH 'pool_list=recover_tmp_pool&primary_zone=z1' REMAP TABLE test.student:student_recover;
```

After the table-level recovery command is executed successfully, you can view the recovery progress in the CDB_OB_RECOVER_TABLE_JOBS view. The STATUS column indicates the recovery stage, and RESTORE_AUX_TENANT indicates that the auxiliary tenant is being restored.

obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_RECOVER_TABLE_JOBS\G

The query result is as follows:

*************************** 1. row ***************************
            TENANT_ID: 1
               JOB_ID: 2
  INITIATOR_TENANT_ID: 1
     INITIATOR_JOB_ID: 2
      START_TIMESTAMP: 2023-08-21 19:54:25
        END_TIMESTAMP: 1970-01-01 08:00:00
               STATUS: RESTORE_AUX_TENANT
      AUX_TENANT_NAME: AUX_RECOVER$1692618864733548
   TARGET_TENANT_NAME: OBT
           IMPORT_ALL: 0
              DB_LIST: NULL
           TABLE_LIST: `TEST`.`STUDENT`
          RESTORE_SCN: 1692618440000000000
  RESTORE_SCN_DISPLAY: 21-AUG-23 07.47.20.000000000 PM
       RESTORE_OPTION: pool_list=RECOVER_TMP_POOL&primary_zone=z1
          BACKUP_DEST: file:///data/backup/data,file:///data/backup/archive
      BACKUP_SET_LIST: file:///data/backup/data/backup_set_1_full
    BACKUP_PIECE_LIST: file:///data/backup/archive/piece_d1002r1p1
        BACKUP_PASSWD: NULL
    EXTERNAL_KMS_INFO: NULL
        REMAP_DB_LIST: NULL
     REMAP_TABLE_LIST: `TEST`.`STUDENT`:`TEST`.`STUDENT_RECOVER`
REMAP_TABLEGROUP_LIST: NULL
REMAP_TABLESPACE_LIST: NULL
               RESULT: SUCCEESS
              COMMENT: NULL
          DESCRIPTION: NULL

References

Execute table-level recovery