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

Purpose

The ALTER SYSTEM RECOVER TABLE statement is used to recover table data. Specifically, it recovers 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 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, and indexes cannot be recovered separately.
When recovering a table, foreign keys, triggers, and statistics on the table cannot be recovered.
When recovering a table, the source and destination tenants must be compatible. 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 a lower version backup to the same version or a higher version. It 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.
When using the ALTER SYSTEM RECOVER TABLE statement to recover a table, an auxiliary tenant is required. 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, in the format of `database_name.table_name1,database_name.table_name2,...`. Multiple tables are separated by commas (,). `table_name` must be consistent with the actual table name stored in the system. For example, if a table named `test` is created in an Oracle tenant, the actual stored table name is `TEST`. Therefore, when restoring the table, you must specify the table name as `TEST`. Otherwise, an error will be reported indicating that the table does not exist. If you want to restore all tables in a database, you can use `` to replace `table_name`, i.e., `database_name.`. If you want to restore all tables, you can use `` to replace both `database_name` and `table_name`, i.e., `.*`. If `database_name` or `table_name` contains special characters, they must be enclosed in backticks (`).
dest_tenant_name	The name of the target tenant for table-level restore. In the current version, only user tenants are supported.
uri	The path for data backup and log archiving. If the data backup was initiated using the `PLUS ARCHIVELOG` method, you only need to specify one path. Otherwise, you need to separately input the paths for data backup and log archiving (at least 2 paths), for example, `'file:///backup/archive, file:///backup/data'`. For more information about data backup methods, please refer to the BACKUP command.
UNTIL	The endpoint for restore, up to and including this point. When specifying a TIME or SCN, you must use `=` to connect to the specified value. If the `UNTIL` clause is not specified, the default is to restore to the latest point. `TIME ='timetamp'`: The timestamp for restore. `SCN = scn`: The System Change Number (SCN) for restore, including this SCN point.
restore_option	The `pool_list`, `locality`, `primary_zone`, and `concurrency` of the auxiliary tenant, separated by `&`: `pool_list` is a required parameter. Specify the resource pool of the user. Multiple resource pools are separated by commas (,). `locality` is an optional parameter. Specify the Locality information of the auxiliary tenant's replica distribution, which must match the Zone information in the `pool_list` of the cluster where the auxiliary tenant is located. If this parameter is not specified, the default is to set one F replica for each Zone based on the `zone_list` in `resource_pool`. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone` is an optional parameter. Specify the preferred location for the leader replica of the auxiliary tenant, which must match the `pool_list` and `locality`, i.e., the Zone information must match, and there must be at least two Paxos members in the `primary_region`. If this parameter is not specified, the system will randomly distribute the leader replica across the Zones specified in `locality`. Example: `primary_zone='z1'`. `concurrency` is an optional parameter. Specify the concurrency level for restore. If not specified, the default is equal to the MAX_CPU value assigned to the auxiliary tenant. Example: `concurrency=50`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	Specifies the encrypted tenant key backup information, which is optional. This parameter is required only when the source tenant is configured with transparent encryption. The parameters are as follows: `backup_key_path`: specifies the backup path for the key. `password`: specifies the encryption password set when backing up the key.
REMAP	Specifies the mapping relationship. If the `REMAP` parameter is not specified, the system will attempt to place the target table in 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, leaving the database name unchanged; you can also keep the table name unchanged and rename it to another database; or you can rename both the table name and the database name. 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 will default to restoring 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 there are other table groups in the target tenant, 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 tablespace to which the table belongs. In OceanBase Database, a tablespace is a logical unit, primarily used for data encryption. If the source table is bound to a tablespace, the system will default to restoring the table to the same-named tablespace in the target tenant. If the same-named tablespace does not exist, the table restore will fail. If there are other tablespaces in the target tenant, you can also use this statement to restore the table to other tablespaces. The source object and the renamed object are connected by a colon (:).
description	The description of the operation, which is optional.

Examples

In the test database of the MySQL mode 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 shows how to restore the test.student table in a specific tenant 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 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 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

Perform table-level recovery

Purpose

Limitations and considerations

When recovering a table, only user tables can be recovered. Temporary tables, views, and indexes cannot be recovered separately.
When recovering a table, foreign keys, triggers, and statistics on the table cannot be recovered.
When recovering a table, the source and destination tenants must be compatible. 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 a lower version backup to the same version or a higher version. It 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.
When using the ALTER SYSTEM RECOVER TABLE statement to recover a table, an auxiliary tenant is required. 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, in the format of `database_name.table_name1,database_name.table_name2,...`. Multiple tables are separated by commas (,). `table_name` must be consistent with the actual table name stored in the system. For example, if a table named `test` is created in an Oracle tenant, the actual stored table name is `TEST`. Therefore, when restoring the table, you must specify the table name as `TEST`. Otherwise, an error will be reported indicating that the table does not exist. If you want to restore all tables in a database, you can use `` to replace `table_name`, i.e., `database_name.`. If you want to restore all tables, you can use `` to replace both `database_name` and `table_name`, i.e., `.*`. If `database_name` or `table_name` contains special characters, they must be enclosed in backticks (`).
dest_tenant_name	The name of the target tenant for table-level restore. In the current version, only user tenants are supported.
uri	The path for data backup and log archiving. If the data backup was initiated using the `PLUS ARCHIVELOG` method, you only need to specify one path. Otherwise, you need to separately input the paths for data backup and log archiving (at least 2 paths), for example, `'file:///backup/archive, file:///backup/data'`. For more information about data backup methods, please refer to the BACKUP command.
UNTIL	The endpoint for restore, up to and including this point. When specifying a TIME or SCN, you must use `=` to connect to the specified value. If the `UNTIL` clause is not specified, the default is to restore to the latest point. `TIME ='timetamp'`: The timestamp for restore. `SCN = scn`: The System Change Number (SCN) for restore, including this SCN point.
restore_option	The `pool_list`, `locality`, `primary_zone`, and `concurrency` of the auxiliary tenant, separated by `&`: `pool_list` is a required parameter. Specify the resource pool of the user. Multiple resource pools are separated by commas (,). `locality` is an optional parameter. Specify the Locality information of the auxiliary tenant's replica distribution, which must match the Zone information in the `pool_list` of the cluster where the auxiliary tenant is located. If this parameter is not specified, the default is to set one F replica for each Zone based on the `zone_list` in `resource_pool`. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone` is an optional parameter. Specify the preferred location for the leader replica of the auxiliary tenant, which must match the `pool_list` and `locality`, i.e., the Zone information must match, and there must be at least two Paxos members in the `primary_region`. If this parameter is not specified, the system will randomly distribute the leader replica across the Zones specified in `locality`. Example: `primary_zone='z1'`. `concurrency` is an optional parameter. Specify the concurrency level for restore. If not specified, the default is equal to the MAX_CPU value assigned to the auxiliary tenant. Example: `concurrency=50`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	Specifies the encrypted tenant key backup information, which is optional. This parameter is required only when the source tenant is configured with transparent encryption. The parameters are as follows: `backup_key_path`: specifies the backup path for the key. `password`: specifies the encryption password set when backing up the key.
REMAP	Specifies the mapping relationship. If the `REMAP` parameter is not specified, the system will attempt to place the target table in 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, leaving the database name unchanged; you can also keep the table name unchanged and rename it to another database; or you can rename both the table name and the database name. 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 will default to restoring 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 there are other table groups in the target tenant, 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 tablespace to which the table belongs. In OceanBase Database, a tablespace is a logical unit, primarily used for data encryption. If the source table is bound to a tablespace, the system will default to restoring the table to the same-named tablespace in the target tenant. If the same-named tablespace does not exist, the table restore will fail. If there are other tablespaces in the target tenant, you can also use this statement to restore the table to other tablespaces. The source object and the renamed object are connected by a colon (:).
description	The description of the operation, which is optional.

Examples

In the sys tenant, create the resources required for the table-level recovery process. In this example, create a resource pool with a 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 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;
```

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 recovery

OceanBase

Customer Stories

Documentation

RECOVER TABLE

Purpose

Limitations and considerations

Privilege requirements

Syntax

Parameters

Examples

References

RECOVER TABLE

Purpose

Limitations and considerations

Privilege requirements

Syntax

Parameters

Examples

References