CREATE RESTORE POINT

Purpose

You can use this statement to create a restore point. You can use this feature to create a data snapshot for the current moment and query data in this data snapshot in the future. The current OceanBase Database version does not support data rollback to this restore point.

After you create a restore point, you can query the SNAPSHOT value of this restore point in the V$RESTORE_POINT view, and then execute the SELECT * FROM table_name AS OF SNAPSHOT snapshot statement to query values in the data snapshot.

Notice

You cannot create restore points for the SYS tenant.

Syntax

CREATE RESTORE POINT restore_point;

Parameters

Notes

Note the following limits for using the restore points:

• A restore point does not support physical backup.

• A restore point is not applicable to the primary/standby cluster configuration.

• A maximum of 10 restore points can be created in a tenant.

• After a restore point is created, the system will generate an error if you execute a DDL statement on a table that has existed before the restore point was created.

• Restore points are used to maintain the global consistent snapshots based on the Global Timestamp Service (GTS). Therefore, GTS must be enabled when you use restore points.

  You can execute the following SQL statement to enable GTS:

  obclient> SET GLOBAL ob_timestamp_service='GTS';
  

Examples

1. Create table test1 and insert a row of data into it.

   obclient> CREATE TABLE test1 (c1 INT,c2 INT);
   Query OK, 0 rows affected (0.04 sec)
   
   obclient> INSERT INTO test1(c1,c2) values (1,1);
   Query OK, 1 row affected (0.01 sec)
   

2. Create restore point rp1.

   obclient> CREATE RESTORE POINT rp1;
   Query OK, 0 rows affected (0.01 sec)
   

3. Insert data into table test1 and commit the operation.

   obclient> INSERT INTO test1(c1,c2) values (2, 2),(3,3);
   Query OK, 2 rows affected (0.00 sec)
   Records: 2  Duplicates: 0  Warnings: 0
   
   obclient> commit;
   Query OK, 0 rows affected (0.00 sec)
   

4. Query the data of the current version in table test1.

   obclient> SELECT * FROM test1;
   +------+------+
    c1    c2   
   +------+------+
       1     1 
       2     2 
       3     3 
   +------+------+
   3 rows in set (0.00 sec)
   

5. Query restore point rp1 and data of earlier versions by version number.

   obclient> SELECT * FROM V$RESTORE_POINT;
   +-----------+------------------+----------------------------+------+
    TENANT_ID  SNAPSHOT          TIME                        NAME 
   +-----------+------------------+----------------------------+------+
         1001  1630407064663511  2021-08-31 18:51:04.665692  rp1  
   +-----------+------------------+----------------------------+------+
   1 row in set (0.01 sec)
   
   obclient> SELECT * FROM test1 AS OF SNAPSHOT 1630407064663511;
   +------+------+
    c1    c2   
   +------+------+
       1     1 
   +------+------+
   1 row in set (0.00 sec)