How to use the flashback operation in Oracle database 19c
Posted by Mir Sayeed Hassan on September 20th, 2025
How to use the flashback operation in Oracle database 19c
Check the status of database
SYS> select name, open_mode, version from V$instance, v$database; NAME OPEN_MODE VERSION ------------------------------------ ORA19CDB READ WRITE 19.0.0.0.0
Check flashback is enable or not.
SYS> select flashback_on from v$database;
FLASHBACK_ON ------------ NO
Enable flashback in database:
Note: To enable the flashback of the database., your database has to be in archivelog mode and the set the path and size as shown below.
SYS> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 322 Next log sequence to archive 324 Current log sequence 324
Here i would see the database is enable.,
In case if not enable, fallow the below process
– Shutdown db : >shutdown immediate;
– Bring up database in mount mode: >startup mount;
– Enable the archivelog mode : >alter database archivelog;
– Bring back the database in open mode: >alter database open;
Set the path and size.
SYS> alter system set db_recovery_file_dest_size=50g; System altered.
SYS> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'; System altered.
Verify
SYS> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------------------------------------------------- db_recovery_file_dest string /u01/app/oracle/fast_recovery_area/ db_recovery_file_dest_size big integer 50G
Now enable the flashback
SYS> alter database flashback on; Database altered.
In case if you want to disable the flashback., you can issue below command.
Disable flashback in database:
SYS> alter database flashback off; Database altered.
Let us create the flashback restore point
SYS> create restore point flashback_guar_point_20sep25 guarantee flashback database; Restore point created.
Verify the created restore points
SQL>Select * from v$restore_points: SCN DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE TIME RESTORE_POINT_TIME PRESERVED NAME PDB_RESTORE_POINT CLEAN_PDB_RESTORE_POINT PDB_INCARNATION# REPLICATED CON_ID --------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- 31705398 2 YES 209715200 20-SEP-25 09.04.41.000000000 AM NULL YES FLASHBACK_GUAR_POINT_20SEP25 NO 0 NO 0
RMAN> list restore point all;
list restore point all;
SCN RSP Time Type Time Name -------------------------------------------------------------------------- 31705398 GUARANTEED 20-SEP-25 FLASHBACK_GUAR_POINT_20SEP25
flashback database to restore point
SYS> set linesize 300 SYS> select NAME,time from v$restore_point; NAME TIME --------------------------------------------------------------- FLASHBACK_GUAR_POINT_20SEP25 20-SEP-25 09.04.41.000000000 AM
Shutdown database and start db in Mount stage:
SYS> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
SYS> startup mount; ORACLE instance started. Total System Global Area 3070229912 bytes Fixed Size 9182616 bytes Variable Size 1728053248 bytes Database Buffers 1325400064 bytes Redo Buffers 7593984 bytes Database mounted.
Flashback db to restore point:
SYS> flashback database to restore point FLASHBACK_GUAR_POINT_20SEP25; Flashback complete.
Open database with resetlog.
SYS> alter database open resetlogs; Database altered.
You can drop restore point
SYS> drop restore point FLASHBACK_GUAR_POINT_20SEP25; Restore point dropped.
Some of the additional flashback queries
Get the last flashback scn.
SYS> col oldest_flashback_scn format 99999999999999999999999999 SYS> select oldest_flashback_scn from v$flashback_database_log; OLDEST_FLASHBACK_SCN --------------------- 31705315
If you want to bring back the database with above scn, issue the below query.
SQL>shutdown immediate;
SQL>startup mount;
–FLASHBACK DATABASE TO SCN 31705315; — Use this for particular scn
If you want to bring back the database with above time, issue the below query.
–FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); – Use for flashback to last one hour
–FLASHBACK DATABASE TO TIMESTAMP to_timestamp(’20-SEP-25 09.04.41′, ‘YYYY-MM-DD HH24:MI:SS’);- to specific timestamp:
If you want to bring back the drop table from the recyclebin, issue the below query.
SELECT object_name, original_name, createtime FROM recyclebin where original_name='TEST1';
Restore the table “TEST1” as same name.
SQL> flashback table TEST1 to before drop;
— Restore that table to a new name:
SQL> flashback table TEST1 to before drop rename to TEST2;
Get flashback are usage information from database.
SYS> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------------------------- ---------- --------------- --------------- -------------------------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 4.25 0 13 0 BACKUP PIECE 10.08 .11 11 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG .78 0 2 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0
Check the flashback with time.
SYS> select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log; Oldest Flashback Time ----------------------------- 20-sep-2025 09:02:19