CREATE RESTORE POINT

Purpose

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

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

Syntax

CREATE RESTORE POINT restore_point_name;

Parameters

Usage notes

Note the following limits for using 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 reports an error if you execute a DDL statement on a table that already exists before the restore point is 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.

  obclient> SET GLOBAL ob_timestamp_service='GTS';
  

Examples

Create a table named tbl1 and insert one row into it. Then, create a restore point named rp1 and insert two rows into the tbl1 table. Query the values in rp1.

obclient> CREATE TABLE tbl1 (col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1(col1,col2) VALUES(1,1);
Query OK, 1 row affected

obclient> CREATE RESTORE POINT rp1;
Query OK, 0 rows affected

obclient> INSERT INTO tbl1(col1,col2) VALUES(2, 2),(3,3);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

obclient> commit;
Query OK, 0 rows affected

obclient> SELECT * FROM tbl1;
+------+------+
 COL1    COL2   
+------+------+
    1     1 
    2     2 
    3     3 
+------+------+
3 rows in set

obclient> SELECT * FROM V$RESTORE_POINT;
+-----------+------------------+------------------------------+------+
 TENANT_ID  SNAPSHOT          TIME                          NAME 
+-----------+------------------+------------------------------+------+
      1002  1634207965611569  14-OCT-21 06.39.25.612683 PM  RP1  
+-----------+------------------+------------------------------+------+
1 row in set

obclient> SELECT * FROM tbl1 AS OF SCN 1634207965611569;
+------+------+
 COL1    COL2   
+------+------+
    1     1 
+------+------+
1 rows in set