Guaranteed Restore Points (vgrp.sql)

What This Script Does

This script monitors guaranteed restore points, showing their names, SCN numbers, creation timestamps, and storage space consumption. Essential for managing flashback database functionality and monitoring space usage in the flash recovery area.

The Script

ttitle 'Guaranteed Restore Points'

col name format a30                 heading 'Guaranteed Restore Point'
col SCN                             heading 'Lowest SCN' format 9999999999999
col DATABASE_INCARNATION#           heading 'DB|INC' format 9999
col STORAGE_SIZE format 999,999.999 heading 'SPACE|USED|(MB)'
col time format a24                 heading 'Flashback Time'

compute sum of STORAGE_SIZE on report
break on report

select name
     , SCN
     , to_char(time, 'Mon DD, YYYY HH:MI:SS AM') Time
     , DATABASE_INCARNATION#
     , storage_size/(1024*1024) storage_size
 from V$RESTORE_POINT
 where GUARANTEE_FLASHBACK_DATABASE='YES'
 order by time
;

Parameters

The script prompts for:

None - this script requires no parameters

Usage

-- Basic usage
@vgrp.sql

Sample Output

                               Guaranteed Restore Points

Guaranteed Restore Point       Lowest SCN Flashback Time           DB   SPACE
                                                                  INC    USED
                                                                       (MB)
------------------------------ ---------- ------------------------ ---- --------
BEFORE_UPGRADE                12345678901 Jan 05, 2025 02:00:00 PM    1  245.675
BEFORE_PATCH                  12345789012 Jan 05, 2025 06:30:15 PM    1  156.234
MONTHLY_BACKUP_POINT          12345890123 Jan 01, 2025 12:00:00 AM    1  789.456
------------------------------ ---------- ------------------------ ---- --------
sum                                                                    1191.365