Warning: session_start(): open(/tmp/sess_66s7pci64ttmul3nipo5ttga51, O_RDWR) failed: No space left on device (28) in /razi/eraazi/darbari/mercy/index.php on line 15
EraAzi | Useful flashback related commands Useful flashback related commands
 

Below are the collection of useful flashback related commands.

1. How to check whether flashback is enabled or not:

select flashback_on from v$database;

2. Enable flashback in database:

--- make sure database is in archivelog mode:

alter system set db_recovery_file_dest_size=10G scope=both;
alter system set db_recovery_file_dest='/dumparea/FRA/B2PMT3' scope=both;
alter database flashback on;

3. Disable flashback in database:

alter database flashback off;

4. Create flashback restore point :

create restore point FLASHBACK_PREP guarantee flashback database;

5. Find the list of restore points:

-- From SQL prompt:
SQL>Select * from v$restore_points:

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

6. Drop restore point:

drop restore point FLASHBACK_PREP;

7. Flashback database to restore point:

--- Below are the steps for flashback database to a guaranteed restore point;

1. Get the restore point name:

SQL> select NAME,time from v$restore_point;

NAME                                                            TIME
--------------------------------          -----------------------------------------------
FLASHBACK_PREP                             21-MAR-17 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_PREP;

4. Open with resetlog:

alter database open resetlogs;

8. Flashback query as of timestamp:

SELECT * FROM EXATREE.EMP AS OF TIMESTAMP
TO_TIMESTAMP('2017-01-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');


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

9. Flashback database to particular SCN or timestamp:

shutdown immediate;
startup mount;
--FLASHBACK DATABASE TO SCN 202381; -- Use this for particular scn
--FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); - Use for flashback to last one hour 
--FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2018-03-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');- to specific timestamp:

alter database open resetlogs;

10. Flashback a table from recyclebin:

-- First get whether the table name exists in recyclebin or not:

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

 -- restore the table as same name:
FLASHBACK TABLE int_admin_emp TO BEFORE DROP;

-- Restore that table to a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP 
   RENAME TO int2_admin_emp;

11. Get flashback are usage info:

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; 


12. How far can we flashback:

--How Far Back Can We Flashback To (Time)

select to_char(oldest_flashback_time,-dd-mon-yyyy hh24:mi:ss-) -Oldest Flashback Time"
from v$flashback_database_log;

--How Far Back Can We Flashback To (SCN)

col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

flashback BACKUP & RECOVERY