Monitor Flashback Area

Hi, there are times when you have a monitoring team and you don’t want to wait until your destination is full right.. to achieve this I have thought about a procedure, this procedure is really easy and makes basically what I needed, let’s check it…

CREATE or replace PROCEDURE FLASHBACK_WATCHER as 
threshold number;
BEGIN
select round(PERCENT_SPACE_USED) into threshold 
    from V$FLASH_RECOVERY_AREA_USAGE where file_type='FLASHBACK LOG' ;

if (threshold>80) then
    dbms_system.ksdwrt(2,'ORA-07445: Flashback is full to: '||threshold||'Please inform DB team.') ;
end if ;
EXCEPTION
        WHEN OTHERS THEN
        NULL ;
END;
/

exec dbms_scheduler.create_job(job_name=>'FLASHBACK_WATCHER_JOB', job_type=>'PLSQL_BLOCK', job_action=>'begin FLASHBACK_WATCHER end;',start_date=>SYSDATE,repeat_interval=>'FREQ=HOURLY;byminute=0; bysecond=0;') 
exec dbms_scheduler.enable(name=>'FLASHBACK_WATCHER_JOB')

Great,or? .. and once the message was written to alert.log we got alerted by a monitoring guy who automatically identified the problem. PS: we did not use FRA as location for backups or archlogs… so this monitoring was used only when we needed to setup flashback ad in a FAQ the ORA-7445 was described as very very bad, so they called us.

Advertisements

2 thoughts on “Monitor Flashback Area

  1. Hi
    What about changing it into:

    select round(sum(PERCENT_SPACE_USED)) into threshold
    from V$FLASH_RECOVERY_AREA_USAGE;

    It would be more flexible in a case u keep redologs there in example – I know you don’t keep backups and archs.

    What you think?

    Like

    • no.. we didn’t use OFA… we used TDA .. telekom defined architecture 😀
      it was like

      /db/pkg000/oracle
      /db/pkg000/oracle/products
      /db/pkg000/oracle/db[1-9]
      /db/pkg000/oracle/arch1
      /db/pkg000/oracle/redo1
      /db/pkg000/oracle/redo2
      each LVM mount point

      now if I would go back to telekom (t-systems slovakia) I would definitely change more things.. my excolleagues still believe that their solution is better (well, like other solutions too if you know about them)

      when we needed to enable flashback either a temporary filesystem was mounted or we added it to a filesystem with enough space..

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s