DROP RESTORE POINT

Purpose

This statement is used to drop a restore point.

Syntax

DROP RESTORE POINT restore_point;

Parameters

Examples

1. View existing restore points for the tenant.

   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
   

2. Drop the restore point rp1.

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

Purpose

This statement is used to create a restore point. This feature allows you to retain a data snapshot at the current moment and query data from this snapshot later. Currently, you cannot roll back data to this restore point.

After creating a restore point, you can query its SNAPSHOT value from the V$RESTORE_POINT view and then query data from the snapshot using the SELECT * FROM table_name AS OF SNAPSHOT snapshot; statement.

Notice

System tenants cannot create restore points.

Syntax

CREATE RESTORE POINT restore_point;

Parameters

Considerations

The usage of the restore point feature is subject to the following limitations:

• Physical backups are not supported.

• Standby databases are not supported.

• Each tenant is limited to 10 restore points.

• If you execute a DDL statement on a table that existed before the restore point was created, an error will be returned.

• Since the restore point feature relies on GTS to maintain global consistency snapshots, GTS must be enabled when using restore points.

  The SQL command to enable GTS is as follows.

  obclient> SET GLOBAL ob_timestamp_service='GTS';
  

Examples

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

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

2. Create a restore point rp1.

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

3. Insert some more data into the test1 table and commit.

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

4. Query the current version of the test1 table.

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

5. View the information of the created restore point rp1 and query historical data based on the 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
   
   obclient> SELECT * FROM test1 AS OF SNAPSHOT 1630407064663511;
   +------+------+
   | c1   | c2   |
   +------+------+
   |    1 |    1 |
   +------+------+
   1 row in set