Oracle_Flashback_Important_Queries

Instance Information :

set linesize 300
select name||’ – ‘||instance_name||’@’||host_name||’ – ‘||DB_UNIQUE_NAME||’ – ‘||version||’ – ‘||open_mode||’ – ‘||to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) || ‘ – ‘|| database_role from v$database, v$instance;

FRA Usage :

set linesize 300
col file_type for a40
select * from V$FLASH_RECOVERY_AREA_USAGE;

Check Restore Point :

set linesize 300
col time for a35
set numwidth 30
col name for a30
col RESTORE_POINT_TIME for a25
select SCN,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,PRESERVED,NAME from v$restore_point;

Check FB DB Logfile :

set linesize 300
col name for a60
set pagesize 100
set numwidth 20
select NAME,LOG#,SEQUENCE#,to_char(first_time,’DD-MON-YYYY HH24:MI:SS’) from v$flashback_database_logfile;

Check DBA_Recyclebin :

select OBJECT_NAME, ORIGINAL_NAME, TYPE,owner,CREATETIME,DROPTIME from dba_recyclebin;

Check FB is ON or NOT?

select FLASHBACK_ON from v$database;

Change Flashback retention :

–Check what is current value.
show parameter reco
show parameter flashback_retention

–ALTER SYSTEM SET db_flashback_retention_target=1440 SCOPE=BOTH sid=’*’ (example) ;

Change FB recovery destination size
–Check what is current value.
show parameter reco
show parameter flashback_retention
–alter system set db_recovery_file_dest_size=300G scope=both sid=’*’ (example) ;