RECOVER TABLE|V4.2.1| docs|Distributed Database

RECOVER TABLE

Last Updated：2023-12-25 08:49:41 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.

You can execute this statement in the sys tenant only.

Syntax

ALTER SYSTEM
    RECOVER TABLE table_name_list
    TO TENANT dest_tenant_name
    FROM uri UNTIL [ {TIME ='timetamp'}| {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

table_name_list:
    table_name [, table_name...]
    | 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 tables to be restored. Separate multiple tables with commas (,). When you specify `table_name`, you must specify `database_name`. If you want to restore all tables in a database, use * for `table_name`, that is, `database_name.`. If you want to restore all tables in the tenant, use for both `database_name` and `table_name`, that is, .. If you specify only the database, use the format `database_name.*`. 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 path to which data backups and log archives are transferred. 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 the data backup statement, see BACKUP.
UNTIL	The timestamp or system change number (SCN) to which data is restored. The value of this parameter must be in the format of `TIME =` or `SCN =`. If you do not specify the `UNTIL` parameter, data is restored to the latest timestamp. `'timestamp'`: the timestamp to which data is restored. `scn_no`: the SCN to which data is restored.
restore_option	The restore options, including `pool_list`, `locality`, `primary_zone`, and `kms_encrypt`. Separate the options 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 destination tenant, which must match the zone information of `pool_list` of the cluster where the destination tenant is located. 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 location of the leader of the destination tenant, which must match the settings of `pool_list` and `locality`. In other words, it must match the zone information and meet the constraint that `primary_region` must have at least two Paxos members. If this option is not specified, the system randomly distributes leaders in the zones specified by `zone_list` of `locality`. Example: `primary_zone='z1'` `kms_encrypt`: optional. This option specifies whether to restore key information. If the value is `true`, the source tenant is encrypted and key information needs to be restored. The default value is `false`, which indicates that the source tenant is not encrypted and you do not need to restore the key information.
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	Renames either the table, database, or both of them.
remap_tablegroup_list	OceanBase Database provides the unique table group feature. If the source table is bound to a table group, the destination table is created in the same table group in the destination tenant by default. If this table group does not exist in the destination tenant, the table fails to be restored. You can also remap the source table to another table group.
remap_tablespace_list	A tablespace is a logical unit in OceanBase Database mainly used for data encryption. If the source table is bound to a tablespace, the destination table is created in the same tablespace in the destination tenant by default. If this tablespace does not exist in the destination tenant, the table fails to be restored. You can also remap the source table to another tablespace.

Examples

For the test database in the obt tenant, 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 in the sys tenant. This example creates the resource unit recover_4c5g and uses it to create a resource pool.

# Create a resource unit
CREATE RESOURCE UNIT recover_4c5g MAX_CPU 4, MEMORY_SIZE = '5G', MAX_IOPS 1024, MIN_IOPS=1024;

# Create a resource pool
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.
```
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 successfully executed, you can check the restore progress in the DBA_OB_RECOVER_TABLE_JOBS view in the obt tenant. The STATUS field indicates the restore stage. In this example, the stage is RESTORE_AUX_TENANT, which means that the auxiliary tenant is being restored.

SELECT * FROM DBA_OB_RECOVER_TABLE_JOBS\G
*************************** 1. row ***************************
               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

Restore a specified table