Monitorar flash_recovery_area em ambiente ASM – Dicas para otimizar o espaço de armazenamento

Monitorar flash_recovery_area em ambiente ASM

Na crontab coloque dessa forma:

#flash_recovery_area
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /u01/sisbd/flash_recovery_area/flash_SID.sh 1>/u01/sisbd/flash_recovery_area/flash_SID.sh.sucesso 2>/u01/sisbd/flash_recovery_area/flash_SID.sh.erro

cat  /u01/sisbd/flash_recovery_area/flash_SID.sh
cd /u01/sisbd/flash_recovery_area
./flash_asm.sh <ORACLE_SID>
./flash_asm.sh petronav

cat flash_asm.sh
#/bin/bash
servidor=`hostname -s`
. /home/oracle/.bash_profile
cd /u01/sisbd/flash_recovery_area
export ORAENV_ASK='NO'
export ORACLE_SID=$1
. oraenv
sqlplus -s /nolog <<FIM
connect / as sysdba
spool flash_$1.flag
set pause off
set heading off
#set feedback off
#set termout off
set verify off
set echo off
set pages 0
set linesize 200
COLUMN NAME FORMAT A50
Select case when perc_livre < 50 and free_mb <15000  then 'A' end
FROM (SELECT c.NAME,ROUND((1-(((c.total_mb-c.FREE_MB)/c.tot_disks)/(b.value/1024/1024)))*100,0) "PERC_LIVRE" ,c.TOTAL_MB/c.tot_Disks total_mb,b.value/1024/1024 "TOTAL_FLASH_MB",(c.total_mb-c.FREE_MB)/c.tot_disks usado_mb,((b.value/1024/1024)-(c.total_mb-c.FREE_MB)/c.tot_disks) free_MB,a.VALUE "DG"
FROM V\$ASM_DISKGROUP c,V\$PARAMETER a, V\$PARAMETER b,(select distinct decode(b.type,'HIGH',3,'NORMAL',2,1) tot_disks from v\$asm_disk a, v\$asm_diskgroup b where a.GROUP_NUMBER  =b.GROUP_NUMBER   and b.name = (select 'DG_'||NAME||'_FLASHN' FROM V\$DATABASE)) c
WHERE a.NAME='db_recovery_file_dest'
and   b.NAME='db_recovery_file_dest_size'
AND  '+'||C.NAME=A.VALUE),global_name
/
spool off
FIM
sqlplus -s /nolog <<FIM
connect / as sysdba
set feedback on
set termout on
set heading on
set verify on
set linesize 200

column global_name format a30
column perc format 999
column dg format a20
SELECT global_name,DG,PERC_LIVRE,total_mb,total_flash_mb,usado_mb,free_mb
FROM (SELECT c.NAME,ROUND((1-(((c.total_mb-c.FREE_MB)/c.tot_disks)/(b.value/1024/1024)))*100,0) "PERC_LIVRE" ,c.TOTAL_MB/c.tot_disks total_mb,b.value/1024/1024 "TOTAL_FLASH_MB",(c.total_mb-c.FREE_MB)/c.tot_disks usado_mb,((b.value/1024/1024)-(c.total_mb-c.FREE_MB)/c.tot_disks) free_MB,a.VALUE "DG"
FROM V\$ASM_DISKGROUP c,V\$PARAMETER a, V\$PARAMETER b,(select distinct decode(b.type,'HIGH',3,'NORMAL',2,1) tot_disks from v\$asm_disk a, v\$asm_diskgroup b where a.GROUP_NUMBER  =b.GROUP_NUMBER   and b.name = (select 'DG_'||NAME||'_FLASHN' FROM V\$DATABASE)) c
WHERE a.NAME='db_recovery_file_dest'
and   b.NAME='db_recovery_file_dest_size'
AND  '+'||C.NAME=A.VALUE),global_name
/
FIM
quant=`grep -n "A" /u01/sisbd/flash_recovery_area/flash_$1.flag | wc -l`
quant2=`ps -ef |grep $1_archivelog.sh|wc -l`
if [ $quant -eq 1 ] ; then
if [ $quant2 -eq 1 ] ; then
# fazer o backup de rman archivelog
cd /u01/sisbd/rman
./$1_archivelog.sh
fi
fi

./flash_asm.sh petronav
-bash-3.2$ ./flash_asm.sh petronav
The Oracle base remains unchanged with value /u01/app/oracle

GLOBAL_NAME                    DG                   PERC_LIVRE   TOTAL_MB TOTAL_FLASH_MB   USADO_MB    FREE_MB
------------------------------ -------------------- ---------- ---------- -------------- ---------- ----------
PETRONAV                       +DG_PETRONAV_FLASHN          95      51199          51000       2710      48290

1 row selected.

Obs. Se a area estiver cheia irá fazer backup dos archives via rman (./$1_archivelog.sh  => $1 = petronav ou outro ORACLE_SID). O script de archive sempre terá que ser <ORACLE_SID>_archivelog.sh.

No meu caso uso o diretorio /u01/sisbd/rman para os scripts de rman.