Archives gerados por hora/dia – Oracle

Neste artigo mostrarei um script que mostra a quantidade dearchives gerados por hora pelo RMAN.

Este script foi criado pelo Jeffrey M. Hunter. Este script ‘analisa’ a view v$log_history.

Este script pode servir como um troubleshooting simples, por exemplo: às 13hrs sempre são gerados, em média, cerca de 5 de archives, porém alguns dias depois você percebe que foram gerados 10 archives no mesmo horário, ou seja, pode ser indício que algum processo pesado foi executado. É importante lembrar que isto são apenas indícios e que uma análise mais detalhada deverá ser realizada.

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off

COLUMN H00   FORMAT 999     HEADING '00'
COLUMN H01   FORMAT 999     HEADING '01'
COLUMN H02   FORMAT 999     HEADING '02'
COLUMN H03   FORMAT 999     HEADING '03'
COLUMN H04   FORMAT 999     HEADING '04'
COLUMN H05   FORMAT 999     HEADING '05'
COLUMN H06   FORMAT 999     HEADING '06'
COLUMN H07   FORMAT 999     HEADING '07'
COLUMN H08   FORMAT 999     HEADING '08'
COLUMN H09   FORMAT 999     HEADING '09'
COLUMN H10   FORMAT 999     HEADING '10'
COLUMN H11   FORMAT 999     HEADING '11'
COLUMN H12   FORMAT 999     HEADING '12'
COLUMN H13   FORMAT 999     HEADING '13'
COLUMN H14   FORMAT 999     HEADING '14'
COLUMN H15   FORMAT 999     HEADING '15'
COLUMN H16   FORMAT 999     HEADING '16'
COLUMN H17   FORMAT 999     HEADING '17'
COLUMN H18   FORMAT 999     HEADING '18'
COLUMN H19   FORMAT 999     HEADING '19'
COLUMN H20   FORMAT 999     HEADING '20'
COLUMN H21   FORMAT 999     HEADING '21'
COLUMN H22   FORMAT 999     HEADING '22'
COLUMN H23   FORMAT 999     HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'


SELECT
    SUBSTR(TO_CHAR(first_time, 'DD/MM/RRRR HH:MI:SS'),1,10)                       DAY
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
GROUP BY 
	SUBSTR(TO_CHAR(first_time, 'DD/MM/RRRR HH:MI:SS'),1,10)
ORDER BY 1

Scripts Monitoração – Oracle

Neste artigo irei mostrar um script que encontrei no dba-oracle. Este script retorna informações uteis para um check no banco de dados.

Este script utilizará o spool (link documentação Oracle) para salvar em um arquivo txt os resultados.

Os checks feitos pelo script:

Uptime: tempo em que a instância está aberta;
Object Status: mostra o status dos objetos;
Invalid Objects: mostra os objetos que estão com o status inválido (entenda mais sobre invalid object);
Recover Files: mostra os arquivos que foram utilizados no Recover do Oracle;
Partitioned Table: mostra as tabelas particionadas na base de dados;
Free space in Tablespace: mostra o espaço livre das tablespaces;
Max Extents Status: mostra o status das Max Extents;
Any partitioned Object approaching to max extents: mostra quais objetos particionados estão próximos de chegar ao limite do seu Max Extens;
Block Contention: mostra quais os blocks contetion que estão ocorrendo na base de dados;
Latch Contention: mostra quias os latch contetion que estão ocorrendo na base de dados;
Top 10 Queries using more disk reads: mostra as 10 queries que estão consumindo mais disco neste momento;
Top 10 Queries using more buffer reads: mostra as 10 queries que estão consumindo mais buffer neste momento;
Buffer cache hit ratio: mostra o volume de buffers consumidos pela CPU no Oracle;
Reloads/Pins in v$librarycache: mostra o volume de Reloads/Pings na view LibraryCache;
Get miss ratio from v$rowcache: mostra mais algumas informações sobre Ratio (mesmo link no tópico anterior);
Redo log space request ratio in v$sysstat: mostra o espaço requerido pelo ratio dos redo logs;
RBS Contention: mostra o RBS Contention na base de dados;
Sorts stats: mostra os sorts stats na base de dados;
Datafiles I/O: mostra os arquivos (datafiles) das tablespaces.

Script:

set linesize 132 
set pagesize 1000
 
spool report.txt
 
prompt #### Up Time ####
 
 
select 'Hostname      : ' || host_name
      ,'Instance Name : ' || instance_name
      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||
       trunc( 24*((sysdate-startup_time) - 
       trunc(sysdate-startup_time))) || ' hour(s) ' ||
       mod(trunc(1440*((sysdate-startup_time) - 
       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
       mod(trunc(86400*((sysdate-startup_time) - 
       trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.v_$instance;
prompt 
prompt 
prompt ################################################################################
 
 
 
prompt .>>>>>>    Object status 
 
select object_type,status,count(*) cnt from user_objects  group by  object_type,status;
prompt 
prompt 
prompt ################################################################################
 
 
 
prompt .>>>>>>  Invalid objects 
 
 
select owner, object_type, substr(object_name,1,30) object_name
from sys.dba_objects
where status='INVALID'
order by object_type;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>>    Recover files
 
select * from sys.v_$recover_file;
prompt 
prompt 
prompt ################################################################################
 
prompt .>>>>>> partitioned table
 
select table_name from user_tables where partitioned='YES';
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Free space in Tablespace 
 
 
Select tablespace_name,sum(bytes/1024/1024) from sys.dba_free_space group by tablespace_name;
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>>Max Extents Status
 
select segment_name, segment_type, extents, max_extents from sys.dba_segments where max_extents-extents<100;
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>>  Any partitioned Object approaching to max extents
 
 
 
SELECT PARTITION_NAME,EXTENTS,MAX_EXTENTS,NEXT_EXTENT,max_extents-nvl(next_extent,0) FROM sys.dba_segments 
where max_extents-nvl(next_extent,0) < 1000
and partition_name is not null;
prompt 
prompt 
prompt ################################################################################
 
prompt .>>>>>>  Block Contention
 
SELECT class,sum(count) total_waits, sum(time) total_time FROM v$waitstat GROUP BY class;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Latch Contention
 
 
SELECT a.name,100.*b.sleeps/b.gets ratio1,100.*b.immediate_misses/decode((b.immediate_misses+b.immediate_gets),0,1) ratio2 
FROM v$latchname a, v$latch b WHERE
a.latch# = b.latch# AND b.sleeps > 0;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Top 10 Queries using more disk reads
 
    select disk_reads, sql_text from  
(    select disk_reads, sql_text from v$sqlarea
     where disk_reads > 10000 order by disk_reads desc) 
where    rownum < 11; 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Top 10 Queries using more buffer reads
 
 
select  buffer_gets,sql_text from v$sqlarea where buffer_gets>10000 and rownum < 11 order by buffer_gets desc;
 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Buffer cache hit ratio 
 
select (1- (sum(decode(a.name,'physical reads',value,0)))/
        (sum(decode(a.name,'db block gets',value,0)) +
        sum(decode(a.name,'consistent gets',value,0)))) * 100 pct
        from v$sysstat a;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Reloads/Pins in v$librarycache 
 
select sum(pins) "executions",sum(reloads) "cache Misses",sum(reloads)/sum(pins)*100 "Ratio" from v$librarycache;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Get miss ratio from v$rowcache  
 
select (sum(getmisses)/sum(gets)) * 100 "Hit Ratio" from v$rowcache;
 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Redo log space request ratio in v$systat 
 
select (req.value*5000)/entries.value "ratio" 
from v$sysstat req,v$sysstat entries 
where req.name='redo log space requests' and entries.name='redo entries' ;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> RBS Contention
 
 
select sum(waits)/sum(gets)*100 from v$rollstat; 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> sorts stats
 
 
Select * from v$sysstat where name like '%sorts%';
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> datafiles I/O
 
 
select name, phyrds, phywrts from v$datafile df, v$filestat fs where df.file# = fs.file# order by phyrds;  
 
prompt 
prompt 
prompt 
prompt 
 
spool off

Qualquer duvida referente às views que estão no script, você pode acessar este link. Neste link você pode procurar a view, que ele irá mostrar todas as descrições dos campos retornados.