RECOVER TABLE|V4.4.2|OceanBase Database| docs|Distributed Database

RECOVER TABLE

Last Updated：2026-04-02 06:23:57 Updated

Purpose

The ALTER SYSTEM RECOVER TABLE statement is used to recover table data. Specifically, it restores a user-specified table from backup data into an existing tenant. This existing tenant can be the same as the original tenant where the table was located, or it can be 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 destination tenant must be consistent. For example, both must be Oracle-compatible tenants or both must be MySQL-compatible tenants.
Like tenant recovery, table recovery currently only supports restoring tables from lower-version backup data to the same version or a higher version. It also does not support reverse recovery between minor versions within the same major version.
When recovering a table, the specified table name must match the actual table name stored in the system. For example, if a table named test is created in an Oracle-compatible tenant, the system internally stores it as TEST. Therefore, when recovering the table, you must specify the table name as TEST. 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 in the cluster where the destination tenant resides. For detailed instructions on creating the required resource pools for the auxiliary tenant, see Prepare for table 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:
    /* The database name remains unchanged, and only the table name is changed. */
    database_name.old_table_name:new_table_name
    /* The table name remains unchanged, and only the database name is changed. */
    |old_database_name.table_name:new_database_name.table_name
    /* Both the database name and the table name are changed. */
    | old_database_name.old_table_name:new_database_name.new_table_name
    /* All tables in the source database are recovered 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,...`. The names of multiple tables are separated with commas (,). `table_name` must be the same as the actual name of the table stored in the system. For example, if you create a table named `test` in an Oracle-compatible tenant, the system stores the table as `TEST`. Therefore, when you restore the table, you must specify the table name as `TEST`. Otherwise, the system will return 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 it in backticks (`).
dest_tenant_name	The name of the target tenant for table-level restore. In the current version, only user tenants can be specified.
uri	The path for data backup and log archiving. 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 for data backup and log archiving (at least two paths), for example, `'file:///backup/archive, file:///backup/data'`. For more information about data backup methods, see the BACKUP command.
UNTIL	The endpoint of the restore, which specifies that the restore will be performed up to and including this endpoint. When you specify the endpoint as a time or SCN, you must use `=` to connect to the specified value. If you do not specify the `UNTIL` clause, the restore will be performed up to the latest endpoint. `TIME ='timetamp'`: specifies the timestamp of the restore. `SCN = scn`: specifies the SCN (System Change Number) of the restore, including this SCN endpoint.
restore_option	The `pool_list`, `locality`, `primary_zone`, and `concurrency` parameters of the auxiliary tenant, which are separated by `&`: `pool_list` is a required parameter. You must specify the resource pool of the user. Multiple resource pools are separated with commas (,). `locality` is an optional parameter. You can specify the Locality information of the auxiliary tenant replicas. The Locality information must match the Zone information of the `pool_list` of the cluster where the auxiliary tenant is located. If you do not specify this parameter, the system will set 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 specified value must match the `pool_list` and `locality` parameters, that is, the Zone information must match and the `primary_region` must contain at least two Paxos members. If you do not specify this parameter, the system will randomly distribute the leader replicas to the Zones in the `zone_list` of the `locality` parameter. 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 will default to the MAX_CPU value of the auxiliary tenant. Example: `concurrency=50`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	The information about the key backup of the encrypted tenant. This is an optional parameter. You need to specify the key backup information only when the source tenant is configured with transparent encryption. The parameters are described as follows: `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 will attempt 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 the table without changing the database it belongs to; you can also keep the table name and rename it to another database; or you can rename the table and the database it belongs to another database. The source object and the renamed object are connected with a colon (:) in the statement.
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 will restore the table to the same-named table group in the target tenant. If the same-named table group does not exist, the table restore will fail. If the target tenant has 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 with a colon (:) in the statement.
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, mainly used for data encryption. If the source table is bound to a table space, the system will restore the table to the same-named table space in the target tenant. If the same-named table space does not exist, the table restore will fail. If the target tenant has 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 with a colon (:) in the statement.
description	The description of the operation. This is an optional parameter.

Examples

In the test database of the MySQL-compatible mode tenant obt, the data backup path is set to 'file:///data/backup/data', and the log archive 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 the table-level restore feature and rename it to student_recover.

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

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 archive log 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 is 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 restore command is executed successfully, you can view the restore progress in the CDB_OB_RECOVER_TABLE_JOBS view. The STATUS column indicates the restore 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

Perform table-level restore