@Rocky how to flashback table in starrocks like oracle

StarRocks provides different features that correspond to Oracle's "Flashback" capabilities depending on whether you want to recover a dropped table or query historical data. 1. Recovering a Dropped Table (Flashback Drop) StarRocks has a

RECOVER

command that allows you to restore a database, table, or partition that was mistakenly dropped. * How it works: When you run

DROP TABLE

, StarRocks moves the data to a "Trash" folder instead of deleting it physically immediately. * Default Retention: Data is kept for 1 day by default. This is controlled by the FE configuration parameter

catalog_trash_expire_second

. * Usage:

sql
    RECOVER TABLE [db_name.]table_name;

* Important Notes: * If you used

DROP TABLE ... FORCE

, the table is deleted immediately and cannot be recovered. * Data deleted via

TRUNCATE TABLE

cannot be recovered using this command. 2. Querying Historical Data (Flashback Query / Time Travel) Starting from v3.4.0, StarRocks supports Time Travel for Iceberg Catalog tables. This allows you to query data as it existed at a specific point in time or version. * Usage (Iceberg Tables only):

sql
    -- Query by specific timestamp
    SELECT * FROM iceberg_table TIMESTAMP AS OF '2024-01-01 12:00:00';

    -- Query by specific snapshot ID
    SELECT * FROM iceberg_table VERSION AS OF 123456789;

* Internal Tables: For StarRocks' native internal tables (Duplicate, Primary Key, etc.), there is currently no direct "Flashback Query" syntax equivalent to Oracle. To restore an internal table to a previous state after a DML error (like an incorrect

UPDATE

or

DELETE

), you must use the standard

BACKUP

and

RESTORE

mechanism from a previously created snapshot. Summary Comparison Oracle Feature | StarRocks Equivalent | Command / Feature Flashback Drop | Supported |

RECOVER TABLE <name>

Flashback Query | Supported (Iceberg v3.4+) |

SELECT ... TIMESTAMP AS OF

Flashback Table (DML Undo) | Partial / Manual |

RESTORE

from backup (for internal tables) Retention Config |

undo_retention

|

catalog_trash_expire_second

(for dropped objects) References • data_source/catalog/iceberg/iceberg_timetravel.md • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky how to recover Data deleted via

TRUNCATE TABLE

Unfortunately, data deleted via

TRUNCATE TABLE

cannot be recovered using StarRocks' internal commands like

RECOVER

. Unlike the

DROP TABLE

command—which moves data to a "Trash" folder for a period of time—the

TRUNCATE

command is designed to be a permanent operation. According to the official documentation: * Immediate Deletion: Storage space is released immediately after the

TRUNCATE

command is executed. * No Trash: Data removed by

TRUNCATE

does not go into the catalog trash, so the

RECOVER

command will not work. * Manual Backups only: The only way to restore the data is if you have a previously created backup using the

BACKUP

and

RESTORE

commands. How to avoid this in the future: 1. Use DROP and RECOVER: If you want the safety net of being able to undo a deletion, consider using

DROP TABLE

instead of

TRUNCATE

. You can then use

RECOVER TABLE <name>

to bring it back within the expiration window (default 24 hours). 2. Regular Backups: Configure automated backups to a remote repository (like S3 or HDFS) using the StarRocks backup and restore framework. 3. Temporary Partitions: If you are clearing data to reload it, consider using Temporary Partitions and the

ALTER TABLE ... REPLACE PARTITION

syntax. This allows you to prepare new data and swap it in safely, rather than truncating and loading into a live table. References • sql-reference/sql-statements/table_bucket_part_index/TRUNCATE_TABLE.md • sql-reference/sql-statements/backup_restore/RECOVER.md • faq/operation_maintenance_faq.md References • administration/Data_recovery.md • faq/operation_maintenance_faq.md • sql-reference/sql-statements/backup_restore/RECOVER.md • faq/Sql_faq.md • administration/Meta_recovery.md • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/TRUNCATE_TABLE.md

@Rocky how to Backups and restore starrocks

To back up and restore data in StarRocks, you use a three-step workflow: create a repository, create a backup snapshot, and then restore from that snapshot. 1. Create a Repository A repository is a mapping to a remote storage system (S3, HDFS, GCS, or MinIO). You only need to do this once. Example: AWS S3 Repository

sql
CREATE REPOSITORY my_s3_repo
WITH BROKER
ON LOCATION "<s3a://my-bucket/starrocks_backups>"
PROPERTIES(
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "us-east-1"
);

Note: For S3 and GCS, StarRocks requires the

s3a://

prefix. 2. Back Up Data Backups are asynchronous snapshots of specific tables, partitions, or entire databases. Example: Back up a specific table

sql
-- Syntax for v3.4.0 and later:
BACKUP DATABASE my_db SNAPSHOT my_snapshot_01
TO my_s3_repo
ON (TABLE my_table);

-- Traditional syntax (compatible with older versions):
BACKUP SNAPSHOT my_db.my_snapshot_01
TO my_s3_repo
ON (my_table);

Monitor Progress:

sql
SHOW BACKUP FROM my_db;

3. Restore Data You can restore a snapshot into the same cluster or a different one (provided the new cluster has the same repository registered). Example: Restore a table

sql
RESTORE SNAPSHOT my_db.my_snapshot_01
FROM my_s3_repo
ON (my_table);

* Automatic Creation: If the table does not exist in the destination database, StarRocks will create it automatically. * Aliases: You can restore a table under a different name using

ON (my_table AS new_table_name)

. Monitor Progress:

sql
SHOW RESTORE FROM my_db;

Important Considerations * Shared-data Clusters: This backup/restore mechanism is not supported for shared-data (compute-decoupled) clusters. * v3.4.0+ Enhancements: Starting with v3.4.0, StarRocks supports backing up more objects, including logical views, UDFs, and external catalog metadata. * No "Incremental" Backup: Standard StarRocks backups are full snapshots of the data at that point in time. For very large tables, it is recommended to back up by partition to reduce the impact of job failures. * Manual Deletion: The

DROP REPOSITORY

command only removes the link in StarRocks; it does not delete the actual data in S3/HDFS. You must delete those files manually in your storage system. References • administration/management/Backup_and_restore.md • faq/Exporting_faq.md • sql-reference/sql-statements/backup_restore/BACKUP.md • sql-reference/sql-statements/backup_restore/RESTORE.md References • faq/Exporting_faq.md • administration/management/Backup_and_restore.md