CREATE RESTORE POINT

Purpose

You can use this statement to create a restore point. By using this feature, you can retain a data snapshot of the current moment and later perform queries based on this data snapshot. Currently, rolling back data to this restore point is not supported.

After creating a restore point, you can query the SNAPSHOT value of this restore point in the V$RESTORE_POINT view, and then retrieve the values in the data snapshot using the syntax SELECT * FROM table_name AS OF SNAPSHOT snapshot;.

Limitations

The limitations for the restore point feature are as follows:

• Physical backups are not supported.

• Primary/secondary database is not supported.

• Each tenant is limited to 10 restore points.

• After you create a restore point, if DDL statements are executed on tables that existed before the creation of the restore point, the system will throw an error.

• Since the restore point functionality depends on GTS for maintaining global consistent snapshots, GTS needs to be enabled when you use restore points.

  The SQL command to enable GTS is as follows:

  obclient> SET GLOBAL ob_timestamp_service='GTS';
  

Syntax

CREATE RESTORE POINT restore_point;

Parameters

Examples

1. Create a table named test1.

   obclient> CREATE TABLE test1 (c1 INT,c2 INT);
   

2. Insert a row of data.

   obclient> INSERT INTO test1(c1,c2) values (1,1);
   

3. Create a restore point rp1.

   obclient> CREATE RESTORE POINT rp1;
   

4. Insert additional data into the test1 table and commit these data.

   obclient> INSERT INTO test1(c1,c2) values (2, 2),(3,3);
   obclient> commit;
   

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

   obclient> SELECT * FROM test1;
   +------+------+
   | c1   | c2   |
   +------+------+
   |    1 |    1 |
   |    2 |    2 |
   |    3 |    3 |
   +------+------+
   3 rows in set
   

6. View the information of the created restore point rp1 and query the historical versions of data based on the version number.

   obclient> SELECT * FROM V$RESTORE_POINT;
   

   The result is as follows:

   +-----------+------------------+----------------------------+------+
   | TENANT_ID | SNAPSHOT         | TIME                       | NAME |
   +-----------+------------------+----------------------------+------+
   |      1001 | 1630407064663511 | 2021-08-31 18:51:04.665692 | rp1  |
   +-----------+------------------+----------------------------+------+
   1 row in set
   
   obclient> SELECT * FROM test1 AS OF SNAPSHOT 1630407064663511;
   +------+------+
   | c1   | c2   |
   +------+------+
   |    1 |    1 |
   +------+------+
   1 row in set