Important Flashback Commands

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 17 Aug, 2023
  • 0 Comments
  • 2 Mins Read

Important Flashback Commands

1. Enable flashback in database:

P.S. Database should be in archive log mode.
alter system set db_recovery_file_dest_size=10G scope=both;

alter system set db_recovery_file_dest='/dumparea/FRA/D2PMS3' scope=both;

alter database flashback on;
2. How to check whether flashback is enabled or not:
select flashback_on from v$database;
3. Disable flashback in database:
alter database flashback off;
4. How far can we flashback:
Flashback To (Time) :
select to_char(oldest_flashback_time,’dd-mon-yyyy hh24:mi:ss’) “Oldest Flashback Time”
from v$flashback_database_log;

Flashback To (SCN) :
col oldest_flashback_scn format 99999999999999999
select oldest_flashback_scn from v$flashback_database_log;
 5. Flashback a table from recyclebin:

## First we need to check whether the table name exists in recyclebin or not:

SELECT object_name, original_name, createtime FROM recyclebin where original_name='EMP';

## Now restore the table with same name:
FLASHBACK TABLE emp TO BEFORE DROP;

## Restore that table with a new name:

FLASHBACK TABLE emp TO BEFORE DROP
RENAME TO emp1;
 6. Fetch flashback usage info:
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; 
7. Flashback database to particular SCN or timestamp:
shutdown immediate;
startup mount;

FLASHBACK DATABASE TO SCN 202381; ## this is for particular scn

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); ## This is for flashback to last one hour

FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2023-08-17 16:00:00', 'YYYY-MM-DD HH24:MI:SS'); ## This is to specific timestamp.

alter database open resetlogs;

8. Flashback query as of timestamp:

SELECT * FROM PDBUSER.EMP AS OF TIMESTAMP
TO_TIMESTAMP('2023-08-17 10:00:00', 'YYYY-MM-DD HH:MI:SS');


SELECT * FROM PDBUSER.EMP AS OF TIMESTAMP SYSDATE -1/24;

9. Flashback database to restore point:

Follow the steps for flashback database to a guaranteed restore point;

1. Fetch the restore point name:

SQL> select NAME,time from v$restore_point;

NAME TIME
-------------------------------- -----------------------------------------------
FLASHBACK_PRIM 17-AUG-23 03.41.33.000000000 PM

2. Shutdown database and start db in Mount stage:

shutdown immediate;
startup mount;

3. flashback db to restore point:

flashback database to restore point FLASHBACK_PRIM;

4. Open database with resetlog:

alter database open resetlogs;
10. Drop restore point:
drop restore point FLASHBACK_PRIM;
11. Create flashback restore point :
create restore point FLASHBACK_PRIM guarantee flashback database;
 12. Find the list of restore points:
## From SQL prompt:
SQL>Select * from v$restore_points:

## From RMAN prompt:
RMAN>LIST RESTORE POINT ALL;