Experience data protection and recovery

Misoperations such as accidental data updates, accidental data deletions, and accidental table drops are the most common data security risks in database usage. OceanBase Database provides multi-layered data protection: record-level flashback queries allow you to view and retrieve data from any recent historical version; object-level recycle bins enable quick restoration of accidentally dropped tables or databases; and physical-level backup and recovery provide the ultimate protection. This topic simulates two typical types of misoperations to help you experience first-hand how OceanBase Database protects data against misoperations.

This topic covers the following experiences:

• Experience flashback query: Simulate accidental deletion of all data in a table, and use a flashback query to view and retrieve historical data within the undo_retention time window.
• Experience flashback delete: Simulate an accidental table drop, and restore the table along with its data from the recycle bin.
• Understand backup and recovery: Learn about the complete set of data protection capabilities, including physical backup and recovery beyond the flashback window.

Prerequisites

Before you perform the operations described in this topic, ensure that the following conditions are met:

Overview of data protection capabilities

OceanBase Database provides multi-layered protection against misoperations and for data recovery, and each layer complements the others:

Among these capabilities, flashback queries rely on multi-version data. OceanBase Database controls the retention period of historical versions through the tenant-level parameter undo_retention, whose default value is 1800 (in seconds, that is, 30 minutes). Once the parameter is set, you can query any multi-version data in the range from T - undo_retention to the current time T. To view the value in flash_tenant, execute SHOW PARAMETERS LIKE 'undo_retention'; (it is a tenant-level parameter, so SHOW VARIABLES cannot return it).

Create a test tenant

1. Log in to the sys tenant of the cluster as the root user (the password is randomly generated during deployment; obtain it as described in the prerequisites above):

   obclient -h127.0.0.1 -uroot@sys -P2881 -p -A oceanbase
   

2. Create a resource unit, resource pool, and tenant named flash_tenant with specifications of 2C4G:

   obclient [oceanbase]> CREATE RESOURCE UNIT flash_unit MAX_CPU=2, MIN_CPU=2, MEMORY_SIZE='4G';
   obclient [oceanbase]> CREATE RESOURCE POOL flash_pool UNIT='flash_unit', UNIT_NUM=1, ZONE_LIST=('zone1');
   obclient [oceanbase]> CREATE TENANT IF NOT EXISTS flash_tenant CHARSET='utf8mb4', PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('flash_pool') SET ob_tcp_invited_nodes='%';
   

   Tenant creation takes tens of seconds (about 6 seconds in the environment tested here). No output is displayed during this period, which is normal; just wait for the command to return.

3. Log in to flash_tenant (the default password for the root user of a newly created tenant is empty, so just press Enter when prompted). All subsequent operations are performed in this tenant:

   obclient -h127.0.0.1 -uroot@flash_tenant -P2881 -p -A test
   

Experience flashback queries

This section first creates a business table and writes data into it to record a point in time before a misoperation. Then, it simulates an accidental deletion of all table data. Finally, it uses a flashback query to view historical data and restore it.

Procedure

1. Create a test table named flash_tbl and write business data into it:

   obclient [test]> CREATE TABLE flash_tbl (
                       id INT PRIMARY KEY,
                       name VARCHAR(32),
                       amount INT
                    );
   obclient [test]> INSERT INTO flash_tbl VALUES (1,'alice',1000),(2,'bob',2000),(3,'carol',3000);
   

2. Confirm that the data was written successfully:

   obclient [test]> SELECT * FROM flash_tbl;
   

   The return result is as follows:

   +----+-------+--------+
   | id | name  | amount |
   +----+-------+--------+
   |  1 | alice |   1000 |
   |  2 | bob   |   2000 |
   |  3 | carol |   3000 |
   +----+-------+--------+
   3 rows in set
   

3. Record the time point before the misoperation. Execute the following statement and record the returned time (this example uses 2026-10-12 15:54:41; use the actual time returned by your query):

   obclient [test]> SELECT NOW();
   

   +---------------------+
   | NOW()               |
   +---------------------+
   | 2026-10-12 15:54:41 |
   +---------------------+
   1 row in set
   

4. Simulate the misoperation: execute a DELETE statement without a WHERE condition to accidentally delete all data in the table:

   obclient [test]> DELETE FROM flash_tbl;
   Query OK, 3 rows affected
   

   Query the table again; the data is lost:

   obclient [test]> SELECT * FROM flash_tbl;
   Empty set
   

5. Convert the time recorded in step 3 to a nanosecond timestamp. In MySQL-compatible mode, you can specify a historical time for a flashback query using AS OF SNAPSHOT, where the timestamp unit is nanoseconds. You can use the time_to_usec function for conversion (replace the time in the example with the actual time you recorded in step 3):

   obclient [test]> SELECT time_to_usec('2026-10-12 15:54:41') * 1000;
   

   +---------------------------------------------+
   | time_to_usec('2026-10-12 15:54:41') * 1000 |
   +---------------------------------------------+
   |                         1791791681000000000 |
   +---------------------------------------------+
   1 row in set
   

   Record the returned value (in this example, it is 1791791681000000000), which will be used to specify the historical point in subsequent flashback queries.

6. Execute the flashback query to view the data before the accidental deletion:

   obclient [test]> SELECT * FROM flash_tbl AS OF SNAPSHOT 1791791681000000000;
   

   The return result shows the three rows of data from before the accidental deletion:

   +----+-------+--------+
   | id | name  | amount |
   +----+-------+--------+
   |  1 | alice |   1000 |
   |  2 | bob   |   2000 |
   |  3 | carol |   3000 |
   +----+-------+--------+
   3 rows in set
   

7. After confirming the historical data is correct, use the result of the flashback query as the data source to restore the accidentally deleted data back into the table:

   obclient [test]> INSERT INTO flash_tbl SELECT * FROM flash_tbl AS OF SNAPSHOT 1791791681000000000;
   

   Query OK, 3 rows affected
   Records: 3  Duplicates: 0  Warnings: 0
   

8. Verify that the data has been restored:

   obclient [test]> SELECT * FROM flash_tbl;
   

   If the return result is identical to the state before the misoperation, the data has been successfully restored:

   +----+-------+--------+
   | id | name  | amount |
   +----+-------+--------+
   |  1 | alice |   1000 |
   |  2 | bob   |   2000 |
   |  3 | carol |   3000 |
   +----+-------+--------+
   3 rows in set
   

Interpretation

Experience flashback deletion

Accidentally dropping a table is a more serious misoperation than accidentally deleting rows. OceanBase Database provides a recycle bin feature: after the recycle bin is enabled, objects such as tables and databases dropped by the DROP statement are moved to the recycle bin instead of being deleted directly, and can be restored quickly with the FLASHBACK statement.

Procedure

1. Create another test table flash_drop_tbl and write data into it:

   obclient [test]> CREATE TABLE flash_drop_tbl (
                       id INT PRIMARY KEY,
                       remark VARCHAR(64)
                    );
   obclient [test]> INSERT INTO flash_drop_tbl VALUES (1,'critical data'),(2,'daily report');
   Query OK, 2 rows affected
   

2. Check the status of the recycle bin feature. The recycle bin is controlled by the system variable recyclebin. It is disabled by default for new tenants:

   obclient [test]> SHOW VARIABLES LIKE 'recyclebin';
   

   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | recyclebin    | OFF   |
   +---------------+-------+
   1 row in set
   

   It is recommended to enable the recycle bin before simulating the accidental deletion operation. The enabling methods are as follows:

   • Enable globally (recommended): Execute SET GLOBAL recyclebin = on;. Modifications to global variables do not take effect in the current session. You need to exit the current session and log in to flash_tenant again for the change to take effect.
   • Enable for the current session only: Execute SET @@recyclebin = on;, which takes effect only for the current session.

   After the recycle bin is enabled, re-execute SHOW VARIABLES LIKE 'recyclebin'; and confirm that ON is returned before you continue with the subsequent steps (the following steps assume that the recycle bin is enabled).

3. Simulate the misoperation: accidentally drop the table flash_drop_tbl:

   obclient [test]> DROP TABLE flash_drop_tbl;
   Query OK, 0 rows affected
   

4. Check the recycle bin to confirm that the deleted table has entered the recycle bin:

   obclient [test]> SHOW RECYCLEBIN;
   

   A sample return result is as follows (OBJECT_NAME is automatically generated by the system, in the format __recycle_$_<internal number>_<timestamp>, which is different from the tenant's external TENANT_ID. Record the actual object name returned in your environment):

   +------------------------------------+----------------+-------+----------------------------+
   | OBJECT_NAME                        | ORIGINAL_NAME  | TYPE  | CREATETIME                 |
   +------------------------------------+----------------+-------+----------------------------+
   | __recycle_$_10001_1791791724617904 | flash_drop_tbl | TABLE | 2026-10-12 15:55:24.617397 |
   +------------------------------------+----------------+-------+----------------------------+
   1 row in set
   

   Description of each field in the result:

   Field	Description
   OBJECT_NAME	The name of the object in the recycle bin, globally unique.
   ORIGINAL_NAME	The name of the object before it was deleted.
   TYPE	The type of the object, such as table, database, index, or tenant.
   CREATETIME	The time when the object entered the recycle bin.

5. Restore the table from the recycle bin:

   obclient [test]> FLASHBACK TABLE flash_drop_tbl TO BEFORE DROP;
   Query OK, 0 rows affected
   

   In the FLASHBACK TABLE statement, you can specify either the original name of the table (such as flash_drop_tbl in this example) or the name of the object in the recycle bin (such as __recycle_$_10001_1791791724617904). Because the names of objects in the recycle bin are globally unique, when multiple original objects with the same name exist in the recycle bin, we recommend that you use the recycle bin object name to make sure that the expected object is restored.

6. Verify that the table and data have been restored:

   obclient [test]> SELECT * FROM flash_drop_tbl;
   

   +----+---------------+
   | id | remark        |
   +----+---------------+
   |  1 | critical data |
   |  2 | daily report  |
   +----+---------------+
   2 rows in set
   

Interpretation

More data protection capabilities

Flashback queries and the recycle bin are suitable for recovering data shortly after an accidental operation occurs. When an accidental operation exceeds the undo_retention time window, or in scenarios such as storage media failure or data center-level disasters, backup and recovery capabilities are required as a last resort:

• Physical backup: Consists of two main functions: log archiving and data backup. Log backup continuously maintains the logs generated by the tenant, while data backup maintains backups of snapshot points. Combining the two allows data to be restored to a specified point in time.
• Recovery capability: Supports restoring a tenant to a specified point in time, and also supports table-level recovery, restoring a specified table from backup data into an existing tenant.

Next steps

After you complete the experiences in this topic, you can explore the following:

1. Experience online DDL: Execute common DDL operations such as adding columns and creating indexes on a test table with continuous read and write activity, and observe business continuity. For more information, see Experience online DDL.
2. Deepen your understanding of backup and recovery: Learn about the complete process of log archiving and data backup. For more information, see Simplified experience with physical backup and recovery.
3. Review data efficiency capabilities: Learn about parallel import and high compression. For more information, see Experience parallel import and high compression.

References

• For detailed operations and examples of flashback queries, see Flashback query.
• For an introduction to the principle of flashback queries, see Flashback query (Data Protection).
• For detailed descriptions of the tenant-level parameter undo_retention, see undo_retention.
• For features and limitations of the recycle bin, see Recycle bin overview.
• For switching the recycle bin on or off, see Enable or disable the recycle bin.
• For viewing objects in the recycle bin, see View recycle bin objects.
• For the complete syntax of restoring recycle bin objects, see Restore recycle bin objects.
• For cleaning up recycle bin space, see Clean up the recycle bin.
• For an architecture description of backup and recovery, see Backup architecture.
• For a streamlined experience with physical backup and restore, see Streamlined physical backup and restore.