RECOVER TABLE|V4.2.5| docs|Distributed Database

RECOVER TABLE

Last Updated：2026-02-11 07:43:39 Updated

Purpose

The ALTER SYSTEM RECOVER TABLE statement is used to recover table data. Specifically, it recovers a user-specified table from backup data into an existing tenant. This existing tenant can be the same as the original tenant of the table or a different one, 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 and destination tenants 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 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 stored table name 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.
During the execution of the ALTER SYSTEM RECOVER TABLE statement, an auxiliary tenant is required. 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 operations on creating resource pools for the auxiliary tenant, see Prepare 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:
    /* 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 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 the 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 value is in the `database_name.table_name1,database_name.table_name2,...` format. Multiple tables are separated by commas (,). The value of `table_name` must be consistent with the actual table name stored in the system. For example, if you create a table named `test` in an Oracle tenant, the system actually stores the table as `TEST`. Therefore, you must specify `TEST` as the table name for restoration. Otherwise, an error will be returned, 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 in all databases, 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 destination tenant for table-level restore. This version supports only user tenants.
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 is the point up to which the restore is performed, including this point. When you specify the restore endpoint as TIME or SCN, you must use `=` to connect to the specified value. If you do not specify the `UNTIL` clause, the restore is performed up to the latest point by default. `TIME ='timestamp'`: the timestamp of the restore. `SCN = scn`: the SCN (System Change Number) of the restore, including this SCN point.
restore_option	The `pool_list`, `locality`, `primary_zone`, and `concurrency` of the standby tenant. Different parameters are separated by `&`: `pool_list` is a required parameter. You must specify the resource pools for the standby tenant. Multiple resource pools are separated by commas (,). `locality` is an optional parameter. You can specify the Locality information of the standby tenant replicas. The value must match the Zone information in the `pool_list` of the cluster where the standby tenant resides. If you do not specify this parameter, the system sets one F replica for each Zone in the `zone_list` of the `resource_pool` by default. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone` is an optional parameter. You can specify the preferred location for the leader replica of the standby 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 parameter, 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 of the standby tenant by default. Example: `concurrency=50`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	The key backup information of the encrypted tenant. This parameter is optional. You must specify the key backup information only when the source tenant is transparently encrypted. The parameter includes the following subparameters: `backup_key_path`: the backup path of the key. `password`: 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 the table without changing the database to which the table belongs, or keep the table name and rename the table to another database, or rename the table and the database to which the table belongs to another database. The source object and the renamed object are separated 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 will restore 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 restore will fail. If the destination tenant contains other table groups, you can restore the table to other table groups by using this statement. The source object and the renamed object are separated by a colon (:).
remap_tablespace_list	The new name of the tablespace to which the table belongs. In OceanBase Database, a tablespace is a logical unit, mainly used for data encryption. If the source table is bound to a tablespace, the system will restore the table to the same-named tablespace in the destination tenant by default. If the same-named tablespace does not exist in the destination tenant, the table restore will fail. If the destination tenant contains other tablespaces, you can restore the table to other tablespaces by using this statement. The source object and the renamed object are separated by a colon (:).
description	The description of the operation. This parameter is optional.

Examples

In the MySQL mode tenant obt, the database test has been set to use the data backup path 'file:///data/backup/data' and the log archive path 'file:///data/backup/archive'. The log archiving feature 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 to the time point '2023-08-21 19:47:20' using table-level recovery and rename it to student_recover.

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

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 in 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 will use 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