#!/bin/ksh
#*******************************************************************************
#                          KSH PROGRAM
#*******************************************************************************
#
#    PROGRAM ID           : fbstat.ksh
#
#D   DESCRIPTION          : Show flashback overview
#D
#
#    INPUT PARAMETERS     : none
#
#    OUTPUT PARAMETERS    : none
#
#
#    INPUT FILES          : none
#
#    OUTPUT FILES         : none
#
#
#    SPECIAL LOGIC NOTES  : none
#
#*******************************************************************************
#                        MODIFICATION LOG
#
#
#    DATE                               SE
#       DESCRIPTION
#    ---------------------------------------------------------------------------
#    Tue Mar 29 14:19:55 CEST 2016      (Hajo)
#       initial version
#    ---------------------------------------------------------------------------
#
#*******************************************************************************

#------------------------------
# init variables
#------------------------------
export s_myName=`basename $0`                               # remember my name
export s_myNameBase=$(echo ${s_myName} | sed -e's/\.ksh//')


#------------------------------
# general information
#------------------------------
echo "-------------------"
echo "Instance: ${ORACLE_SID}"
echo "Host....: $(uname -n)"


#*******************************************************************************
# MAIN
#*******************************************************************************
echo "-------------------"
sqlplus -s "/ as sysdba" <<-EOSQL01

        set pages 0
        set lines 100
        set feedback off
        set heading off
        set serveroutput on

        select 'Archiving              : ' || case log_mode when 'ARCHIVELOG' then 'ON' else 'OFF' end from v\$database;
        select 'Flashback              : ' || case flashback_on when 'YES' then 'ON' else 'OFF' end from v\$database;
        select 'FB Retention (Min)     : ' || value from v\$parameter where name = 'db_flashback_retention_target';
        select 'Fast Recovery Area     : ' || value from v\$parameter where name = 'db_recovery_file_dest';
        select 'FRA Size (GB)          : ' || round(value/1024/1024/1024) from v\$parameter where name = 'db_recovery_file_dest_size';
        select 'FRA Used (GB)          : ' || round(space_used/1024/1024/1024) from v\$recovery_file_dest;
        select ' -> Usage Arch Log (GB): ~' || round(percent_space_used*(select value/100 from v\$parameter where name = 'db_recovery_file_dest_size')/1024/1024/1024) from v\$flash_recovery_area_usage where file_type = 'ARCHIVED LOG';
        select ' -> Usage FB Log (GB)  : ~' || round(percent_space_used*(select value/100 from v\$parameter where name = 'db_recovery_file_dest_size')/1024/1024/1024) from v\$flash_recovery_area_usage where file_type = 'FLASHBACK LOG';
--      select ' -> Usage Arch Log: ' || percent_space_used || ' %' from v\$flash_recovery_area_usage where file_type = 'ARCHIVED LOG';
--      select ' -> Usage FB Log  : ' || percent_space_used || ' %' from v\$flash_recovery_area_usage where file_type = 'FLASHBACK LOG';

        prompt
        prompt Restore Points:
        set heading on
        set pages 100
        col RESTORE-POINT for a20
        col SCN  for a20
        col CREATED for a19
        col database_incarnation# for a4
        select
          name "RESTORE-POINT",
--        to_char(scn) "SCN",
          to_char(time,'YYYY-MM-DD HH24:MI:SS')"CREATED",
          database_incarnation# "INC#",
          guarantee_flashback_database,
          round(storage_size/1024/1024/1024) "GB"
          from v\$restore_point
         order by 3 desc;


        quit
        EOSQL01


#*******************************************************************************
# FINE
#*******************************************************************************
exit