RECOVER TABLE|V4.3.0| docs|Distributed Database

RECOVER TABLE

Last Updated：2024-04-19 08:42:50 Updated

Purpose

You can use this statement to restore table data, that is, restore specified tables to an existing tenant from backup data. The existing tenant can be the owner tenant of the tables to restore, a tenant in the same cluster as the owner tenant, or even a tenant in a different cluster.

Limitations and considerations

Only user tables can be restored. Temporary tables, views, and indexes cannot be restored separately.
Foreign keys, triggers, and statistics on tables cannot be restored.
The source and destination tenants for table restore must be of the same tenant mode. For example, they must be both Oracle tenants or both MySQL tenants.
Similar to tenant restore, table restore supports data restore only to a tenant of the same or a later version. You cannot restore tables to a tenant of an earlier version, not even to an earlier minor version.
When you restore a table, the specified table name must be identical to the actual name of the table stored in the system. For example, assume that you have created a table named test in an Oracle tenant, which is stored by the name of TEST in the system. To restore this table, you must specify the table name TEST. Otherwise, the system will report an error indicating that the table does not exist.
An auxiliary tenant is required to restore a table. Therefore, before you execute this statement, make sure that you have created a resource pool for the auxiliary tenant in the cluster where the destination tenant resides. For more information, see Preparations.

Required privileges

Only the root user of the sys tenant (root@sys) can execute this 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 [=] descripiton];


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

remap_table_name_list:
    /* Retain the database name and change only the table name. */
    database_name.old_table_name:new_table_name
    /* Retain the table name and change only the database name. */
    |old_database_name.table_name:new_database_name.table_name
    /* Change both the database name and table name. */
    | old_database_name.old_table_name:new_database_name.new_table_name
    /* Restore all tables in the source database 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 list of tables to be restored, in the format of `database_name.table_name1,database_name.table_name2,...`. The table names are separated with commas (,). The `table_name` value must be identical to the actual name of the table stored in the system. For example, assume that you have created a table named `test` in an Oracle tenant, which is stored by the name of `TEST` in the system. To restore this table, you must specify the table name `TEST`. Otherwise, the system will report an error indicating that the table does not exist. If you want to restore all tables in a database, replace `table_name` with an asterisk (), that is, `database_name.`. If you want to restore all tables, replace `database_name` and `table_name` with asterisks (), that is, `.*`. Enclose special characters in `database_name` or `table_name` in backticks (``).
dest_tenant_name	The name of the destination tenant. The current version allows you to restore tables only to user tenants.
uri	The data backup and log archive paths used for restore. If the data backup is initiated by using `PLUS ARCHIVELOG`, you only need to specify one path. If not, you need to specify at least one data backup path and one log archive path, for example, `'file:///backup/archive, file:///backup/data'`. For more information about data backup methods, see BACKUP.
UNTIL	The inclusive timestamp or system change number (SCN) up to which data is restored. Join `TIME` or `SCN` and the value with an equal sign (`=`). By default, if you do not specify the `UNTIL` clause, data is restored up to the latest timestamp. `TIME ='timestamp'`: the inclusive timestamp up to which data is restored. `SCN = scn`: the inclusive SCN up to which data is restored.
restore_option	The restore options of the auxiliary tenant, including `pool_list`, `locality`, `primary_zone`, and `concurrency`, which are separated with ampersands (`&`). `pool_list`: required. The resource pool of the tenant. Multiple resource pools must be separated with commas (,). `locality`: optional. The locality of replicas of the auxiliary tenant, which must match the zone information of `pool_list` of the cluster to which the auxiliary tenant belongs. By default, if this option is not specified, a full-featured replica is set for each zone in `zone_list` of the resource pools specified by `pool_list`. Example: `locality='F@z1,F@z2,F@z3'` `primary_zone`: optional. The preferred position of the leader of the auxiliary tenant, which must match the settings of `pool_list` and `locality`. In other words, the value of this parameter must match the zone information and meet the constraint that the primary region has at least two Paxos members. If this option is not specified, the system randomly distributes the leader to zones specified by `zone_list` of `locality`. Example: `primary_zone='z1'` `concurrency`: optional. The restore concurrency. If `concurrency` is not specified, the `MAX_CPU` value assigned to the auxiliary tenant is used as the default value. Example: `concurrency=50`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	Optional. The key backup information of the encrypted tenant. You need to specify the key backup information for a tenant restore only when the tenant is configured with transparent encryption. `backup_key_path` indicates the backup path of the key. `password` indicates the encryption password set during key backup.
REMAP	The mapping relationship. If you do not specify `REMAP`, the system tries to restore the table to the same table space, table group, and database as the original table.
remap_table_name_list	The new name of the restored table. You can perform any of the following operations: rename the table without changing the database to which the table belongs; restore the table to another database without changing the table name; rename the table and restore the table to another database. Use a colon (:) to join the source object and the renamed object.
remap_tablegroup_list	The table group to which the renamed table belongs. By default, if the source table is bound to a table group, the system restores the table to a table group with the same name in the destination tenant. If the table group with the same name does not exist, the table fails to be restored. If the destination tenant has other table groups, you can specify this parameter to restore the table to another table group. Use a colon (:) to join the source object and the renamed object.
remap_tablespace_list	The tablespace to which the renamed table belongs. A tablespace is a logical unit in OceanBase Database mainly used for data encryption. By default, if the source table is bound to a tablespace, the system restores the table to the tablespace with the same name in the destination tenant. If the tablespace with the same name does not exist, the table fails to be restored. If the destination tenant has other tablespaces, you can specify this parameter to restore the table to another tablespace. Use a colon (:) to join the source object and the renamed object.
description	The description of the operation. This parameter is optional.

Examples

For the test database in the MySQL tenant obt, the data backup path is set to 'file:///data/backup/data', the log archive path is set to 'file:///data/backup/archive', log archiving is enabled, and a full data backup is completed. Assume that the student table is dropped by mistake. The following example shows how to restore the test.student table in a tenant to the timestamp 2023-08-21 19:47:20 and rename the table as student_recover.

Create resources required by the auxiliary tenant for table restore in the sys tenant. This example creates the resource unit recover_4c5g and uses it to create a resource pool.

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 under the obt tenant and rename the table as student_recover. The backup path is file:///data/backup/data, the log archive path is file:///data/backup/archive, the table is restored to the timestamp 2023-08-21 19:47:20, the resource pool used by the auxiliary tenant is 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 restore statement is executed, you can query the table restore progress from the CDB_OB_RECOVER_TABLE_JOBS view. The STATUS column specifies the restore stage. 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 restore