Search This Blog

Total Pageviews

Tuesday 2 August 2011

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;



<10 .00="" then="">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

******
<10 .00="" then="">
 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; 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;
<10 .00="" then="">

 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;
<10 .00="" then="">

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';

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

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;
<10 .00="" then="">

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;

Oracle DBA

anuj blog Archive