Oracle flashback info

Oracle Oracle flashback info  ...


Oracle flashback Hourly info http://anuj-singh.blogspot.com/2023/     Oracle flashback Hourly info
https://anuj-singh.blogspot.com/search?q=Oracle+flashback+Hourly+info




set linesize 300
COL name  FORMAT A50  HEADING 'Parameter'
COL value FORMAT A32  HEADING 'Setting'
SELECT decode(name,'db_flashback_retention_target','db_flashback_retention_target_In_Min->',name) name ,value FROM v$parameter 
WHERE NAME LIKE '%flash%' OR NAME LIKE '%recovery%' or name like 'log_archive_dest_1'
ORDER BY NAME;

OR 

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 line 300
select
FILE_TYPE,
PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES,PERCENT_SPACE_USED, 
case when ((100 - PERCENT_SPACE_USED) <10 .00="" then="">90.00)% full ##'
           else 'Good'
           end as "ATTENTION-FLASHBACK LOG"
           from v$recovery_area_usage -- v$flash_recovery_area_usage
where 1=1
-- and FILE_TYPE='FLASHBACK LOG'
;


alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select min(FIRST_TIME) min_time,max(FIRST_TIME) Max_time from  v$flashback_database_logfile;


set linesize 300
col FILE_TYPE for a30
col PERCENT_SPACE_USED for 999.99 heading 'PERCENT_SPACE_USED%'   
SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
       SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB,
       SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
       SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
       SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
       SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES,
       SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED
  FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);


show parameter reco 
show parameter flashback_retention 



set linesize 300
col NAME for a40
col value for a60 wrap
select (select value from v$parameter where NAME='log_archive_dest_1') value ,a.name,
  round((a.space_limit/1024/1024/1024),2) as Total_in_Gb,  round((a.space_used/1024/1024/1024),2) as flash_used_in_Gb, 
  round((a.space_reclaimable/1024/1024/1024), 2) as flash_reclaimable_Gb, sum(b.percent_space_used)  as "PERCENT_OF_SPACE_USED%"
from   v$recovery_file_dest a, v$flash_recovery_area_usage b
group by  a.name,space_limit, space_used ,   space_reclaimable ;




set linesize 300
column name format A30
column value format A50
select name, value
from   v$parameter
where  name in ('db_flashback_retention_target', 'db_recovery_file_dest','db_recovery_file_dest_size')
order by name;


set linesize 200
col name for a50
SELECT NAME, (SPACE_LIMIT/1024/1024 /1024) SPACE_LIMIT_GB,round(((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024/1024)) AS SPACE_AVAILABLE_GB, ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST ;


set pagesize 300 linesize 300
prompt -- status of the Flash Recovery Area ?
TTITLE 'Flash Recovery Area Status'
COL name   FORMAT A40 HEADING 'File Name'
COL spc_lmt_mb   FORMAT 999999999.99 HEADING 'Space|Limit|(GB)'
COL spc_usd_mb   FORMAT 999999999.99 HEADING 'Space|Used|(GB)'
COL spc_rcl_mb   FORMAT 999999999.99 HEADING 'Reclm|Space|(GB)'
COL number_of_files  FORMAT 999999999    HEADING 'Files'
SELECT
name
,trunc(space_limit /(1024*1024*1024),2) spc_lmt_gb
,trunc(space_used /(1024*1024*1024),2) spc_usd_gb
,trunc(space_reclaimable /(1024*1024*1024),2) spc_rcl_gb
,number_of_files
FROM v$recovery_file_dest;



The workaround is to lower the flashback retention target so that all changes fit in the FRA: <<<<<<<<<<<<<  if FRA full 

SQL> alter system set db_flashback_retention_target=1440;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=???G scope=both;



archive log list;


alter system set log_archive_dest_1='/xxx/backups' scope=memory;

ALTER SYSTEM SET log_archive_dest_1 ="LOCATION=+DATA" SCOPE=BOTH;

Delete archive log file 

run { 
crosscheck backupset of database;
crosscheck backupset of controlfile;
crosscheck backupset of archivelog all;
delete force  noprompt obsolete;
delete expired archivelog all;
delete noprompt obsolete;
delete noprompt obsolete orphan;
crosscheck archivelog all;
delete expired archivelog all;
}

delete noprompt archivelog all completed before 'sysdate - 1/24';     ---1 hour
 
delete noprompt archivelog all completed before 'sysdate - 4';


***************************************
Take Archive log Backup  
nohup rman target / log=Arch.log @arch.rmn &


cat arch.rmn
run {  backup as compressed backupset  format '/XXXX/Arch_%T_@_%s_%p_%t' (archivelog all delete input); }


*****************************************


prompt -- What Flashback options are currently enabled for this database?
TTITLE 'Flashback Options Currently Enabled:'


Oracle flashback info

set numf 99999999999999999999999999

*****


prompt  DB_DATA     Number of bytes of database data read and written during the interval

prompt  REDO_DATA Number of bytes of redo data written during the interval


set linesize 400 
col DB_DATA for 999999999999 
col FLASHBACK_SIZE for 999999999999 
col "FLASH BEGIN" for a14
col "FLASH END" for a14
SELECT TO_CHAR(A.BEGIN_TIME,'DDMMYY HH24:MI') "FLASH BEGIN",TO_CHAR(A.END_TIME,'DDMMYY HH24:MI') "FLASH END",A.DB_DATA "DB-READ-WRITE",a.REDO_DATA ,B.FLASHBACK_SIZE,
 (A.DB_DATA/B.FLASHBACK_SIZE)*100 "% COMPLETE" 
FROM V$FLASHBACK_DATABASE_STAT A, V$FLASHBACK_DATABASE_LOG B;
FLASH BEGIN    FLASH END         DB-READ-WRITE        REDO_DATA FLASHBACK_SIZE       % COMPLETE
-------------- -------------- ---------------- ---------------- -------------- ----------------
100423 12:02   100423 12:15         7406247936       7188010496   599147937792                1
100423 11:02   100423 12:02        31025061888      32838619136   599147937792                5
100423 10:02   100423 11:02        26978738176      29367271936   599147937792                5
100423 09:02   100423 10:02        22409895936      24943251456   599147937792                4

******

 col oldest_flashback_scn 			format 999999999999999 	heading 'oldest|flashback|scn #'
 col oldest_flashback_time 			format a20 				heading 'oldest|flashback|time'
 col retention_target 				format 999999999 		heading 'retention|target'
 col flashback_size 				format 999999999999999 	heading 'flashback|Gb-size'
 col estimated_flashback_size 		format 999999999999 	heading 'estimated|flashback|Gb-size'
select 
--CON_ID,
oldest_flashback_scn,to_char(oldest_flashback_time,'dd-mm-yyyy hh24:mi:ss') oldest_flashback_time,retention_target,flashback_size/1024/1024/1024 flashback_size,estimated_flashback_size /1024/1024/1024 estimated_flashback_size
from v$flashback_database_log;


SELECT CURRENT_SCN FROM V$DATABASE;


SELECT trunc(estimated_flashback_size/1024/1024/1024,2) estimated_flashback_size_GB ,trunc(flashback_size/1024/1024/1024,2) flashback_size_gb  FROM v$flashback_database_log;


db_flashback_retention_target




SELECT ROUND((A.SPACE_LIMIT/1024/1024/1024), 2) AS FLASH_IN_GB,   ROUND((A.SPACE_USED/1024/1024/1024), 2) AS FLASH_USED_IN_GB,   ROUND((A.SPACE_RECLAIMABLE/1024/1024/ 1024), 2) AS FLASH_RECLAIMABLE_GB,  SUM(B.PERCENT_SPACE_USED) as  "PERCENT_OF_SPACE_USED%"
FROM   V$RECOVERY_FILE_DEST A,  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY  SPACE_LIMIT,   SPACE_USED ,   SPACE_RECLAIMABLE ;



archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +LOGS




The workaround is to lower the flashback retention target so that all changes fit in the FRA:

SQL> alter system set db_flashback_retention_target=1440;



prompt -- Is Flashback Database On ?

TTITLE 'Is Flashback Database Enabled?'
COL name   FORMAT A12 HEADING 'Database'
COL current_scn  FORMAT 9999999999999 HEADING 'Current|SCN #'
COL flashback_on  FORMAT A8 HEADING 'Flash|Back On?'
SELECT
name
,current_scn
,flashback_on
FROM v$database;


set linesize 300 pagesize 300
alter session set nls_date_format='dd-mm-yyy mi:ss';
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 99999999999999  HEADING 'Flshbck|SCN #'
COL first_time   FORMAT A24   HEADING 'Flashback Start Time'

SELECT
LOG#
,trunc(bytes/1024/1024,2) MB
,first_change#
,first_time
FROM v$flashback_database_logfile;

====

with file 

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
 COL log#   FORMAT 9999   HEADING 'FLB|Log#'
 COL bytes   FORMAT 99999999  HEADING 'Flshbck|Log Size'
 col first_change#  FORMAT 99999999999999  HEADING 'Flshbck|SCN #'
  COL first_time   FORMAT A24   HEADING 'Flashback Start Time'
col NAME for a70
 SELECT
NAME,
    LOG#
   ,trunc(bytes/1024/1024,2) MB
    ,first_change#
    ,first_time
    FROM v$flashback_database_logfile
order by first_time ;

==

<10 .00="" then="">

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
set linesize 200
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)"  format 9999999999
col round(space_used/1048576) heading "Space Used (MB)"  format 9999999999999
col name Heading "Flashback Location"     format a50
select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST;



set linesize 200
col name for a50
SELECT NAME, (SPACE_LIMIT/1024/1024 /1024) SPACE_LIMIT_GB,((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024/1024) AS SPACE_AVAILABLE_GB, ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST ;
 /




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

FLASHBACK DATABASE TO SCN 5964663

FLASHBACK DATABASE TO BEFORE SCN 5964663

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24)

FLASHBACK DATABASE TO SEQUENCE 12345

FLASHBACK TABLE persons TO SCN 6039341


=====


set heading on  linesize 300
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 of 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 a30 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
;



                                  Space         Space
File Type                        Used % Reclaimable % N. Files
------------------------------ -------- ------------- --------
CONTROL FILE                          0             0        0
REDO LOG                              0             0        0
ARCHIVED LOG                          0             0        0
BACKUP PIECE                          0             0        0
IMAGE COPY                            0             0        0
FLASHBACK LOG                        70            23      219
FOREIGN ARCHIVED LOG                  0             0        0
AUXILIARY DATAFILE COPY               0             0        0

8 rows selected.


-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size= 20 G;
 
-- Or change the FRA to a new location :
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=' ';


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;



 col oldest_flashback_scn 			format 999999999999999 	heading 'oldest|flashback|scn #'
 col oldest_flashback_time 			format a20 				heading 'oldest|flashback|time'
 col retention_target 				format 999999999 		heading 'retention|target'
 col flashback_size 				format 999999999999999 	heading 'flashback|Gb-size'
 col estimated_flashback_size 		format 999999999999 	heading 'estimated|flashback|Gb-size'
select 
--CON_ID,
oldest_flashback_scn,to_char(oldest_flashback_time,'dd-mm-yyyy hh24:mi:ss') oldest_flashback_time,retention_target,flashback_size/1024/1024/1024 flashback_size,estimated_flashback_size /1024/1024/1024 estimated_flashback_size
from v$flashback_database_log;


select * from V$RESTORE_POINT;

-- How much space is used by Flashback Logs for each GRP?
SELECT NAME, SCN, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES';

How much space do all GRP Flashback Logs use?

SELECT SUM(STORAGE_SIZE) FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES';

============================================================


Set heading off;
select '+----------------------------------------+' from dual
union all
select '| Timestamp: '||to_char(systimestamp,'DD-MM-YYYY HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------+' from dual;
Set heading on
/

set echo on  feedback on  numwidth 30 pagesize 50000 linesize 300


alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select sysdate from dual;


show parameter retention


prompt ## v$database
select flashback_on from v$database;


prompt 	## v$rman_configuration
select * from v$rman_configuration;


prompt 	## v$restore_point
select * from v$restore_point;

prompt 	## V$FLASH_RECOVERY_AREA_USAGE
select * from V$FLASH_RECOVERY_AREA_USAGE;

prompt  ## V$RECOVERY_FILE_DEST
col name for a35
select * from V$RECOVERY_FILE_DEST;

prompt 	## v$flashback_database_log

col VALUE for a30
select * from v$flashback_database_log;

prompt 	##v$flashback_database_logfile
select * from v$flashback_database_logfile;

prompt 	##v$flashback_database_logfile
select min(first_time), min(first_change#) from v$flashback_database_logfile;


set linesize 300
col object_name for a30
col ORIGINAL_NAME for a30
col type for a10
col owner for a10
col CREATETIME for a20
col DROPTIME for a20
select OBJECT_NAME, ORIGINAL_NAME, TYPE,owner,CREATETIME,DROPTIME from dba_recyclebin;






 
 
col  NAME for a50
col value for a40
 
WITH flashback_database_log AS
(SELECT ROUND((SYSDATE - oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes,
retention_target retention_target_minutes,
flashback_size / 1048576 flashback_size_mb,
estimated_flashback_size / 1048576 estimated_flashback_size_mb
FROM v$flashback_database_log),
flashback_database_logfile AS
(SELECT COUNT(*) logs,
SUM(BYTES / 1048576) size_mb,
MIN(first_time) oldest_log,
MAX(first_time) latest_log
FROM v$flashback_database_logfile),
flashback_usage AS
(SELECT file_type,
ROUND(mb_used, 2) mb_used,
ROUND(mb_reclaimable, 2) mb_reclaimable,
DECODE(total_mb, 0, 0, ROUND(mb_used * 100 / total_mb, 2)) percent_space_used,
DECODE(total_mb, 0, 0, ROUND(mb_reclaimable * 100 / total_mb, 2)) percent_space_reclaimable,
number_of_files,
total_mb db_recovery_file_dest_mb,
flashback_retention_target,
oldest_record,
ROUND((sysdate - oldest_record) * 24 * 60, 2) oldest_record_age_sec
FROM (SELECT SUM(DECODE(NAME,
'db_recovery_file_dest_size',
VALUE / 1048576,
0)) total_mb,
SUM(DECODE(NAME, 'db_flashback_retention_target', VALUE, 0)) flashback_retention_target
FROM v$parameter
WHERE NAME IN ('db_recovery_file_dest_size',
'db_flashback_retention_target')),
(SELECT 'FLASHBACKLOG' file_type,
NVL(SUM(BYTES) / 1048576, 0) mb_used,
sum(CASE
WHEN last_time <= (sysdate - (tgt.value / 1440)) THEN
bytes / 1048576
ELSE
0
END) mb_reclaimable,
COUNT(*) number_of_files,
MIN(first_time) oldest_record
FROM (select bytes,
lead(first_time) over(order by first_time asc) last_time,
first_time
from v$flashback_database_logfile) fla_log,
(SELECT value value
FROM v$parameter
WHERE name = 'db_flashback_retention_target') tgt
UNION
SELECT 'BACKUPPIECE' file_type,
NVL(SUM(BYTES / 1048576), 0) mb,
SUM(CASE
WHEN dl.rectype = 13 THEN
(BYTES / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(start_time) oldest_record
FROM v$backup_piece bp, x$kccagf dl
WHERE is_recovery_dest_file = 'YES'
AND deleted = 'NO'
AND bp.recid = dl.recid(+)
AND dl.rectype(+) = 13
UNION
SELECT 'ARCHIVELOG' file_type,
NVL(SUM(blocks * block_size) / 1048576, 0) mb,
SUM(CASE
WHEN dl.rectype = 11 THEN
(LOG.blocks * LOG.block_size / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$archived_log log, x$kccagf dl
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
AND dl.recid(+) = log.recid
AND dl.rectype(+) = 11
UNION
SELECT 'ONLINELOG' file_type,
SUM(BYTES / 1048576) mb,
0 reclaimable,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$logfile lf,
(SELECT group#, BYTES, first_time
FROM v$standby_log
UNION
SELECT group#, BYTES, first_time FROM v$log) l
WHERE l.group# = lf.group#
AND lf.is_recovery_dest_file = 'YES'
UNION
SELECT 'IMAGECOPY',
NVL(SUM(blocks * (block_size / 1048576)), 0) mb,
0 reclaimable_mb,
COUNT(*) no_of_files,
MIN(creation_time) oldest_record
FROM v$datafile_copy
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
UNION
SELECT 'CONTROLFILE',
NVL(SUM(block_size * file_size_blks) / 1048576, 0) mb,
0 reclaimable,
COUNT(*) no_of_files,
NULL oldest_record
FROM v$controlfile
WHERE is_recovery_dest_file = 'YES'))
SELECT order_, NAME, VALUE
FROM(
SELECT 0 order_, NAME, VALUE
FROM v$parameter
WHERE NAME LIKE 'db_recovery_file%'
UNION
SELECT 3, 'oldest flashback log (minutes)', TO_CHAR(ROUND(oldest_log_minutes, 2))
FROM flashback_database_log
UNION
SELECT 1, 'retention target (minutes)', TO_CHAR(ROUND(retention_target_minutes, 2))
FROM flashback_database_log
UNION
SELECT 2, 'estimated size for flashback logs (MB)', TO_CHAR(ROUND(estimated_flashback_size_mb, 2))
FROM flashback_database_log
UNION
SELECT 2, 'Current flashback log count', TO_CHAR(logs)
FROM flashback_database_logfile
UNION
SELECT 3, 'Most recent flashback log (minutes)', TO_CHAR(ROUND((SYSDATE - latest_log) * 24 * 60, 2))
FROM flashback_database_logfile
UNION
SELECT 4, 'Total size of all files in MB', TO_CHAR(ROUND(SUM(mb_used), 2))
FROM flashback_usage
UNION
SELECT 5, 'Total size of reclaimable files in MB', TO_CHAR(ROUND(SUM(mb_reclaimable), 2))
FROM flashback_usage
UNION
SELECT 6, 'unused space in MB', TO_CHAR(ROUND(MIN(db_recovery_file_dest_mb) - SUM(mb_used)))
FROM flashback_usage)
ORDER BY order_, NAME;




   ORDER_ NAME                                               VALUE
---------- -------------------------------------------------- ----------------------------------------
         0 db_recovery_file_dest                              /oracle/db/fast_recovery_area
         0 db_recovery_file_dest_size                         5033164800
         2 Current flashback log count                        0
         3 Most recent flashback log (minutes)
         4 Total size of all files in MB                      0
         5 Total size of reclaimable files in MB              0
         6 unused space in MB                                 4800

7 rows selected.

<10 .00="" then="">===

<10 .00="" then="">
REM srdc_fra_details.sql – collect FRA information



define SRDCNAME='FRA_DETAILS'

set markup html on spool on TERMOUT off

COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
REM
spool &&SRDCSPOOLNAME..htm
Set heading off;
select '+—————————————————-+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+—————————————————-+' from dual
/

set echo on feedback on numwidth 30 pagesize 50000
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select sysdate from dual;
show parameter retention;
select flashback_on from v$database;
select * from v$rman_configuration;
select * from v$restore_point;
select * from V$FLASH_RECOVERY_AREA_USAGE;
select * from V$RECOVERY_FILE_DEST;
select * from v$flashback_database_log;
select * from v$flashback_database_logfile;
select min(first_time) from v$flashback_database_logfile;
select min(first_change#) from v$flashback_database_logfile;
set markup html off spool off

sqlplus text ----
set echo on feedback on numwidth 30 
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select sysdate from dual;
show parameter retention;
set pagesize 50000 linesize 400

col VALUE for a40
select flashback_on from v$database;
col NAME  for a40
col VALUE for 40
select * from v$rman_configuration;
select * from v$restore_point;
select * from V$FLASH_RECOVERY_AREA_USAGE;

col NAME for a70
select * from V$RECOVERY_FILE_DEST;
col NAME for a70
select * from v$flashback_database_log;
select * from v$flashback_database_logfile;
select min(first_time) from v$flashback_database_logfile;
select min(first_change#) from v$flashback_database_logfile;