Introduction to Using the "Flashback" Feature in GBase Database

Published on 2024-03-18

In database management, data protection and recovery have always been critical tasks. To address the high-risk scenario of accidental data deletion by developers, the shared-storage database cluster GBase 8s, from GBase, includes a built-in flashback tool. This flashback capability ensures data integrity and reliability—so careless developers no longer have to worry about data loss.

The GBase flashback tool, onflsbk.sh, provides table-level flashback capabilities, allowing users to revert a single table to a snapshot at a specific point in history. The process involves the following five steps:

Installing and Configuring the Flashback Tool

This step involves installing the tool and configuring its parameter settings. The flashback tool is provided as a portable tar package. Simply extract it to the designated server directory; however, ensure that the extracted directories and files have the owner with execute permission for onlog.

The flashback tool is configured via the parameters file config.properties located in the configuration directory: [installation_directory]/conf/. Parameters include JDBC URL, database username/password, character set, 32K support, database directory, and more.

Configuration Parameters for flashback.conf

Using the Flashback Tool

The flashback functionality is invoked by executing the shell script: [installation_directory]/onflsbk.sh.

Flashback Syntax Diagram

The flashback operation supports two modes: "Flashback by Time" and "Flashback by LSN."

a. Flashback by Time:

./onflsbk.sh -D database_name -d 'date_time' -r table_name 

  • -D database_name specifies the target database. Enter the name of the database to be used.

  • -d 'date_time' specifies the target recovery time. Enter the desired time in 'yyyy-mm-dd hh24:mi:ss' format. If this parameter is used, the flashback restores table data based on the time point, and the -l 'lsn_number' option must not be present in the command.

  • -r table_name specifies the table to be restored. This parameter must be used with either -l 'lsn_number' or -d 'date_time'. Enter the name of the table to be restored. The command must not contain -d 'date_time';

b. Flashback by LSN:

./onflsbk.sh -D database_name -l 'lsn_number' -r table_name

  • -D database_name specifies the target database. Enter the name of the database to be used.

  • -l 'lsn_number' specifies the LSN (Log Sequence Number) position in the logical log. Enter the LSN number to indicate the restore point. If this parameter is used, the flashback restores table data based on the specified LSN position.

  • -r table_name specifies the table to be restored. This parameter must be used with either -l 'lsn_number' or -d 'date_time'. Enter the name of the table to be restored. The command must not contain -d 'date_time';

c. Flashback to a New Table:

Syntax for flashback to a new table by LSN:
onflsbk.sh -D database_name -l 'lsn_number' -r table_name -t new_table_name

Syntax for flashback to a new table by time:
onflsbk.sh -D database_name -d 'date_time' -r table_name -t new_table_name

Example 1: The following demonstrates a flashback by time.

First, create a test table test_fls2:
create table test_fls2 (id int,name varchar(20),time1 datetime year to second,primary key (id));

Insert test data, with the two records inserted 5 seconds apart:
insert into test_fls2 values (1,'a',sysdate);
insert into test_fls2 values (2,'b',sysdate);

The query results are shown below:

Execute the flashback by time command:

./onflsbk.sh -D test -d "2023-08-15 13:38:50" -r test_fls2

An interactive prompt will appear, indicating that if DDL operations were performed between the current time and the specified recovery point, the flashback may fail or the recovered table data may be inaccurate.

Press Enter or enter "1" to continue, or enter "2" to exit.

In this case, enter "1" to continue:

Upon completion, check the table data in the tool; it has been rolled back to the state before the inserts:

Execute the flashback by time command again, choosing a time between the first and second inserts:
./onflsbk.sh -D test -d "2023-08-15 13:38:52" -r test_fls2

Upon completion, check the table data; it has been rolled back to the state before the inserts:

Execute the flashback by time command yet again, choosing another time between the first and second inserts:
./onflsbk.sh -D test -d "2023-08-15 13:38:58" -r test_fls2

Upon completion, check the table data; it has been rolled back to the state before the inserts:

Example 2: The following demonstrates a flashback by LSN:

Use onlog -l to view the LSN corresponding to a specific time:

Execute the flashback by LSN command:

./onflsbk.sh -D test -l '77:0xc5ec018' -r test_fls2

Upon completion, check the table data in the tool; it has been rolled back to the state before the inserts:

Use the next LSN to perform a flashback:
./onflsbk.sh -D test -l '77:0xc5ec050' -r test_fls2

Upon completion, check the table data in the tool; it has been rolled back to the state after the first insert: