Flashback database commands

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
SQL > SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> SELECT estimated_flashback_size/1024/1024,flashback_size/1024/1024 
FROM v$flashback_database_log;


prompt -- What Flashback options are currently enabled for this database?
TTITLE 'Flashback Options Currently Enabled:'
COL name FORMAT A32 HEADING 'Parameter'
COL value FORMAT A32 HEADING 'Setting'

SELECT
name
,value
FROM v$parameter
WHERE NAME LIKE '%flash%' OR NAME LIKE '%recovery%'
ORDER BY NAME;



prompt -- status of the Flash Recovery Area ?
TTITLE 'Flash Recovery Area Status'
COL name FORMAT A32 HEADING 'File Name'
COL spc_lmt_mb FORMAT 9999.99 HEADING 'Space|Limit|(MB)'
COL spc_usd_mb FORMAT 9999.99 HEADING 'Space|Used|(MB)'
COL spc_rcl_mb FORMAT 9999.99 HEADING 'Reclm|Space|(MB)'
COL number_of_files FORMAT 99999 HEADING 'Files'

SELECT
name
,space_limit /(1024*1024) spc_lmt_mb
,space_used /(1024*1024) spc_usd_mb
,space_reclaimable /(1024*1024) spc_rcl_mb
,number_of_files
FROM v$recovery_file_dest;

prompt -- Is Flashback Database On ?

TTITLE 'Is Flashback Database Enabled?'
COL name FORMAT A12 HEADING 'Database'
COL current_scn FORMAT 9999999 HEADING 'Current|SCN #'
COL flashback_on FORMAT A8 HEADING 'Flash|Back On?'

SELECT
name
,current_scn
,flashback_on
FROM v$database;


prompt -- What Flashback Logs are available?
TTITLE 'Current Flashback Logs Available'
COL log# FORMAT 9999 HEADING 'FLB|Log#'
COL bytes FORMAT 99999999 HEADING 'Flshbck|Log Size'
COL first_change# FORMAT 99999999 HEADING 'Flshbck|SCN #'
COL first_time FORMAT A24 HEADING 'Flashback Start Time'

SELECT
LOG#
,bytes
,first_change#
,first_time
FROM v$flashback_database_logfile;



PROMPT How Far Back Can We Flashback To (Time)?
PROMPT

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

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT

col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;



PROMPT
PROMPT Flashback Area Usage
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a50

select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST;



=====



prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Flash Recovery Area ---

prompt -- ----------------------------------------------------------------------- ---

prompt

set heading off

select ' db_recovery_file_dest '||value from V$PARAMETER where name='db_recovery_file_dest'
union
select ' db_recovery_file_dest_size (G) '||to_char(value/1024/1024/1024) from V$PARAMETER where name='db_recovery_file_dest_size'
union
select ' db_flashback_retention_target (minutes) '||value||' (hours : '||value/60||')' from V$PARAMETER where name='db_flashback_retention_target'
union
select ' Flashback size estimated (G) '||to_char(round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024)) From V$FLASHBACK_DATABASE_LOG
; 

set heading on


column name format a40 heading "Name"
column sl format 9999999 heading "Space Limit|(Gb)"
column su format 9999999 heading "Space Used|(Gb)"
column sr format 9999999 heading "Space|Reclaimable|(Gb)"
column nf format 9999999 heading "N. Files"

Select
Substr(Name,1,40) name
, Space_Limit/1024/1024/1024 sl
, Space_Used/1024/1024/1024 su
, Space_Reclaimable/1024/1024/1024 sr
, Number_Of_Files nf
From V$RECOVERY_FILE_DEST
;

column ft format a15 heading "File Type"
column psu format 9999999 heading "Space|Used %"
column psr format 9999999 heading "Space|Reclaimable %"

Select
File_Type ft
, Percent_Space_Used psu
, Percent_Space_Reclaimable psr
, Number_Of_Files nf
From V$FLASH_RECOVERY_AREA_USAGE
;

column os format 99999999999999 heading "Oldest Flashback SCN"
column ot format a25 heading "Oldest Flashback Time"

Select 
OLDEST_FLASHBACK_SCN os
, TO_CHAR(OLDEST_FLASHBACK_TIME, 'DD-MM-YYYYD HH24:MI:SS') ot
From 
V$FLASHBACK_DATABASE_LOG
;

-- select * from V$RESTORE_POINT;
-- How much space is used by Flashback Logs for each GRP?
-- SQL> SELECT NAME, SCN, STORAGE_SIZE
-- FROM V$RESTORE_POINT
-- WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES';
-- How much space do all GRP Flashback Logs use?
-- SQL> SELECT SUM(STORAGE_SIZE)
-- FROM V$RESTORE_POINT
-- WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES';

How to flashback  ( values given below is just an example )

in the MOUNT (exclusive) mode then issue one of the commands:

FLASHBACK DATABASE TO SCN 600000

FLASHBACK DATABASE TO BEFORE SCN 66666666

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -2/24)

FLASHBACK DATABASE TO SEQUENCE 67777777

FLASHBACK TABLE persons TO SCN 566666666