OceanBase AI Database provides record-level flashback queries, which allow you to obtain data of a specific historical version by using the AS OF SNAPSHOT syntax.
OceanBase AI Database allows you to set the tenant-level configuration item undo_retention to perform a flashback query. After you set undo_retention, you can use the flashback query feature to obtain data of multiple versions in the time range from T - undo_retention to the current time T. The default value of the tenant-level configuration item undo_retention is 1800, in seconds. For more information about this configuration item, see undo_retention.
Considerations
When you set the undo_retention configuration item to perform a flashback query, note that:
If you set the
undo_retentionconfiguration item at time T0, the setting does not take effect on data before T0 and takes effect only on data after T0.If the queried table is moved to the recycle bin, you must restore it from the recycle bin before you can query data in the table.
For more information, see Restore objects from the recycle bin.
Assuming the current time is
T, after settingundo_retention, if there were any DDL operations within the time periodT - undo_retention, then:For adding column operations, when performing a flashback query to view data before the operation, all newly added columns will have default values.
For operations like creating tables, dropping tables, or dropping columns, when performing a flashback query to view data before the operation, the system will report an error.
Flashback queries require additional storage space to retain historical data. If you increase the value of the
undo_retentionconfiguration item, the storage space required is increased. We recommend that you observe the change of the storage space after you increase the value of theundo_retentionconfiguration item.
Prerequisites
Before you use the undo_retention configuration item to perform a flashback query, you need to modify the value of the undo_retention configuration item. The default value of the undo_retention configuration item is 1800, in seconds.
Log in to the database as a tenant administrator.
Note
The tenant administrator is the
rootuser.Here is an example of the connection command. Use the actual database connection information in your production environment.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql#demo -p***** -AFor more information about how to connect to a database, see Overview of connection methods.
Execute the following statement to modify the value of the tenant-level configuration item
undo_retention.Here is an example:
obclient [(none)]> ALTER SYSTEM SET undo_retention=900;For more information about the tenant-level configuration item
undo_retention, see undo_retention.
Flashback query
Log in to the database as a user tenant.
Here is an example of the connection command. Use the actual database connection information in your production environment.
obclient -h10.xx.xx.xx -P2883 -uinfo@mysql#demo -p***** -AFor more information about how to connect to a database, see Overview of connection methods.
Execute a flashback query on a table.
Suppose the current value of
undo_retentionis900and you have a table namedtable1. Specify a historical point in time by usingAS OF SNAPSHOTand query the status and data of the table at this point in time.obclient [(none)]> SELECT * FROM table1 AS OF SNAPSHOT 1597306800000000000;Here,
1597306800000000000is a timestamp, in nanoseconds. The timestamp is the time since 08:00:00 (UTC+8) on January 1, 1970. You can convert the point in time to be queried into a timestamp based on your business needs.For example, if you want to perform a flashback query on table data at 16:20:00 on August 13, 2020, you can convert the time as follows:
obclient [(none)]> SELECT time_to_usec('2020-08-13 16:20:00') * 1000; +--------------------------------------------+ | time_to_usec('2020-08-13 16:20:00') * 1000 | +--------------------------------------------+ | 1597306800000000000 | +--------------------------------------------+ 1 row in set