Hit/Miss Ratios

Hit/Miss Ratios

Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio
BUFFER HIT RATIO NOTES:


Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat

Data Dict Hit Ratio
DATA DICTIONARY HIT RATIO NOTES:


Gets - Total number of requests for information on the data object.
Cache Misses - Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select sum(GETS),
sum(GETMISSES),
round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache

SQL Cache Hit Ratio
SQL CACHE HIT RATIO NOTES:


Pins - The number of times a pin was requested for objects of this namespace.
Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select sum(PINS) Pins,
sum(RELOADS) Reloads,
round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache

Library Cache Miss Ratio
LIBRARY CACHE MISS RATIO NOTES:


Executions - The number of times a pin was requested for objects of this namespace.
Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select sum(PINS) Executions,
sum(RELOADS) cache_misses,
sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycache

Oracle Database Risk Matrix - CVE - NVD

Procure na guia Description:
- This Critical Patch Update contains xx new security fixes across all products.

Procure por Oracle Database Risk Matrix:
- Na coluna Vuln#, vocë tera o nome da vulnerabilidade.

Acess o site:
- http://cve.mitre.org/cve/
- http://web.nvd.nist.gov/view/vuln/search?execution=e1s1

Coloque o numero da vulnerabilidade e veja as caracteristicas

Comandos UNIX

find . -name '*.ora' -exec ls -l {} \+
find . -name '*.ora' -exec ls -l \+
find . -name 'tns*.ora' -exec ls -l {} \+

/PAE and TIME_WAIT (TNS-12542)

32bit, dando problema na quantidade de memória alocada no Oracle.
No boot.ini do servidor tem que conter o parâmetro /PAE, conforme mostra abaixo, para que o Windows reconheça mais de 3.xx gb de memória.

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /NoExecute=OptOut /PAE



http://forums.oracle.com/forums/thread.jspa?messageID=2776359�

Cause
Problem is caused due to the fact that the free ports in the windows client has been exhausted and it is trying to make use of a port which is in TIME_WAIT state which gives the error TNS-12542: Address already in use.

There are 2 solutions available:

1.) Increase the free port range:
======================
Start Registry Editor (Regedt32.exe).
Locate the following key in the registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
On the Edit menu, click Add Value, and then add the following registry value:
Value Name: MaxUserPort Data Type: REG_DWORD Value: 65534
Valid Range: 5000-65534 (decimal) Default: 0x1388 (5000 decimal)
Description: This parameter controls the maximum port number used when an application requests any
available user port from the system. Normally, ephemeral (that is, short-lived) ports are allocated between the values of 1024 and 5000 inclusive.


2.) Decrease the value for TIME_WAIT state:
================================
TcpTimedWaitDelay (new in Windows NT versions 3.51 SP5 and later)
Key: Tcpip\Parameters
Value Type: REG_DWORD - Time in seconds
Valid Range: 30-300 (decimal)
Default: 0xF0 (240 decimal)
http://forums.oracle.com/forums/thread.jspa?messageID=2722180?
And also check this metalink doc id for the same.
Note:252614.1
HTH
Aman....

Pages: 1

Database Free space report

Database Free space report

Retirado de:
http://asktom.oracle.com/tkyte/Misc/free.html

Tablespace Name: name of tablespace, leading '*' indicates a good locally managed tablespace, leading blank means it is a bad dictionary managed tablespace. Second character of A implies ASSM managed storage, second character of M implies manually managed (pctused, freelists, etc are used to control space utilization)
Kbytes: allocated space of the tablespace, sum of kbytes consumed by all datafiles associated with tablespace.
Used: space in the tablespace that is used by some segment.
Free: space in the tablespace not allocated to any segment.
%Used: ratio of free to allocated space
largest: mostly useful with dictionary managed tablespaces, the size of the largest contigously set of blocks available. If this number in a dictionary managed tablespace is smaller than the next extent for some object, that object could fail with "out of space" even if the FREE column says there is lots of free space.
MaxPoss Kbytes: the autoextend max size (note CAN be smaller than the allocated size!!!! you can set the maxsize to be less than the current size of a file)
%Max Used: how much of the maximum autoextend size has been used so far




set linesize 121
-------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------

column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/

Format

column segment_name format a45;
select owner, segment_name, segment_type, round(bytes/1024/1024,1) meg
from dba_segments
where owner in ('STABS','STABS2')
order by owner, segment_type, meg desc
/

MAIL_FILES

- mail_files.sql
create or replace procedure mail_files (from_name varchar2,
to_name varchar2,
subject varchar2,
message varchar2,
max_size number default
9999999999,
p_oracle_directory varchar2,
p_binary_file varchar2)
is
v_smtp_server varchar2(100) := 'mailhost'; --change this to your mail server
v_smtp_server_port number := 25;
v_directory_name varchar2(100);
v_file_name varchar2(100);
v_line varchar2(1000);
crlf varchar2(2):= chr(13) || chr(10);
mesg varchar2(32767);
conn utl_smtp.connection;
v_slash_pos number;
v_file_handle utl_file.file_type;
invalid_path exception;
mesg_length_exceeded boolean := false;

PROCEDURE write_raw(p_conn in out nocopy utl_smtp.connection,
p_message in raw) is
BEGIN
utl_smtp.write_raw_data(p_conn, p_message);
END write_raw;

PROCEDURE write_mime_header(p_conn in out nocopy utl_smtp.connection,
p_name in varchar2,
p_value in varchar2) is
BEGIN
write_raw(p_conn => p_conn,
p_message => utl_raw.cast_to_raw(p_name || ': ' || p_value || utl_tcp.crlf));
END write_mime_header;

PROCEDURE write_boundary(p_conn in out nocopy utl_smtp.connection,
p_last in boolean default false) AS
BEGIN
if (p_last) then
utl_smtp.write_data(p_conn, '--DMW.Boundary.605592468--'||crlf);
else
utl_smtp.write_data(p_conn, '--DMW.Boundary.605592468'||crlf);
end if;
END write_boundary;

PROCEDURE end_attachment(p_conn in out nocopy utl_smtp.connection,
p_last in boolean default true) IS
BEGIN
utl_smtp.write_data(p_conn, utl_tcp.crlf);
if (p_last) then
write_boundary(p_conn, p_last);
end if;
END end_attachment;

PROCEDURE begin_attachment(p_conn in out nocopy utl_smtp.connection,
p_mime_type in varchar2 default
'text/plain',
p_inline in boolean default false,
p_filename in varchar2 default null,
p_transfer_enc in varchar2 default null) is
BEGIN
write_boundary(p_conn);
if (p_transfer_enc is not null) then
write_mime_header(p_conn, 'Content-Transfer-Encoding',
p_transfer_enc);
end if;
write_mime_header(p_conn, 'Content-Type', p_mime_type);
if (p_filename is not null) then
if (p_inline) then
write_mime_header(p_conn, 'Content-Disposition', 'inline;
filename="' || p_filename || '"');
else
write_mime_header(p_conn,'Content-Disposition', 'attachment;
filename="' || p_filename || '"');
end if;
end if;
utl_smtp.write_data(p_conn, utl_tcp.crlf);
END begin_attachment;

PROCEDURE binary_attachment(p_conn in out utl_smtp.connection,
p_file_name in varchar2,
p_mime_type in varchar2) is
k_max_line_width constant pls_integer default 54;
v_amt binary_integer := 672 * 3; /* ensures proper format; 2016 */
v_bfile bfile;
v_file_len pls_integer;
v_buf raw(2100);
v_modulo pls_integer;
v_pieces pls_integer;
v_file_pos pls_integer := 1;
v_data raw(2100);
v_chunks pls_integer;
BEGIN
begin_attachment(p_conn => p_conn,
p_mime_type => p_mime_type,
p_inline => TRUE,
p_filename => p_file_name,
p_transfer_enc => 'base64');
BEGIN
v_bfile := bfilename(p_oracle_directory, p_file_name);
v_file_len := dbms_lob.getlength(v_bfile);
v_modulo := MOD(v_file_len, v_amt);
v_pieces := TRUNC(v_file_len / v_amt);
if (v_modulo <> 0) then
v_pieces := v_pieces + 1;
end if;
dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
dbms_lob.read(v_bfile, v_amt, v_file_pos, v_buf);
v_data := null;
for i in 1 .. v_pieces loop
v_file_pos := I * v_amt + 1;
v_file_len := v_file_len - v_amt;
v_data := utl_raw.concat(v_data, v_buf);
v_chunks := TRUNC(utl_raw.length(v_data) / k_max_line_width);
if (i <> v_pieces) then
v_chunks := v_chunks - 1;
end if;
write_raw(p_conn => p_conn, p_message => utl_encode.base64_encode
(v_data));
v_data := null;
if (v_file_len < v_amt AND v_file_len > 0) then
v_amt := v_file_len;
end if;
dbms_lob.READ(v_bfile, v_amt, v_file_pos, v_buf);
end loop;
END;
dbms_lob.fileclose(v_bfile);
end_attachment(p_conn => p_conn);
EXCEPTION
when no_data_found then
end_attachment(p_conn => p_conn);
dbms_lob.fileclose(v_bfile);
END binary_attachment;

BEGIN
conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );
utl_smtp.helo( conn, v_smtp_server );
utl_smtp.mail( conn, from_name );
utl_smtp.rcpt( conn, to_name );
utl_smtp.open_data ( conn );
utl_smtp.write_data(conn, 'Subject: '||subject||crlf);
mesg:= 'Content-Transfer-Encoding: 7bit' || crlf ||
'Content-Type: multipart/mixed;
boundary="DMW.Boundary.605592468"' || crlf ||
'Mime-Version: 1.0' || crlf ||
'--DMW.Boundary.605592468' || crlf ||
'Content-Transfer-Encoding: binary'||crlf||
'Content-Type: text/plain' ||crlf ||
crlf || message || crlf ;
utl_smtp.write_raw_data ( conn, utl_raw.cast_to_raw(mesg) );
binary_attachment(p_conn => conn,
p_file_name => p_binary_file,
p_mime_type => 'text/plain;
name="'||p_binary_file||'"');
utl_smtp.close_data( conn );
utl_smtp.quit( conn );
END;
/


- From SQL*Plus:
conn system/XXXXXXXXXXX@smart;
create directory dir_spool as 'E:\SMART\SPOOL';
create directory dir_admin as 'E:\SMART\ADMIN';
@D:\Util\mail_files.sql;
exit;

- From cmd:
file C:\WINDOWS\system32\drivers\etc\hosts
add: xxx.xxx.xx.xx mailhost


- For call this package:
Example: BackupHot.bat
…..
…..
REM ### Send mails ###
D:\oracle\product\10.2.0\db_1\bin\sqlplus "system/xxxxxxx@%ORACLE_SID%" @d:\oracle\scripts\send_mails_chkbck.sql BackupHot%ORACLE_SID%%anio%%mes%%dia%.log

Example: send_mails_chkbck.sql
exec mail_files('racsfs@domain','eu@eu.com','Database SFS: Backup Daily','Backup SFS finished successfully. Please, check the attached file for errors. If so, send mail to OTHERS',9999999999,'DIR','&1');
exit

Horário de verão

Retirado de:
http://profissionaloracle.com.br/blogs/rodrigoalmeida/2009/02/14/horario-de-verao-os-impactos-no-banco-de-dados-oracle/

Os impactos no banco de dados Oracle

sábado, fevereiro 14th, 2009

Olá,

Nesse final de semana (14/02/2009 - DST Brazil) estamos terminando o horário de verão, que vale somente para a região Sul, Sudeste e Centro-Oeste. Com o termíno, deveremos atrasar nossos relógios em 1 hora, e isso será um grande problema para os DBAs que tem banco de dados Oracle na produção da empresa, pois apenas atrasar o horário do servidor com o banco de dados no ar (online) pode causar grandes danos.

Problemas

Ao atrasar o horário do servidor em 1 hora, e não realizar os procedimentos corretos para efetuar essa atividade, pode trazer diversos impactos ao banco de dados Oracle. E esses impactos podemos listar abaixo:

Sobreescrita na geração dos Archived Logs;
Problemas de comunicação com o LISTENER do Oracle Server;
Problemas de novos registros incluídos atráves da aplicação ou processos de ETL, pois se esses registros trabalham com funções de data como SYSDATE, TIMESTAMP ou SYSTIMESTAMP, podem ser invalidadas por primarys keys e constraints de check no modelo e banco de dados;
Para ambientes RAC (Real Application Cluster), mudar o horário pode trazer diversos problemas, desde o CRS (Cluster Registry Services), LISTENER e InterConnect, pois podem ocorrer sobreescritas na gravação dos logs e sincronização dos nós;
Para ambientes DataGuard ou Stand-by, podem ocorrer problemas também com sobreescritas dos redo logs, onde pode causar problemas e até mesmo erros do kernel do Oracle Server gerando os ORA-600;
Problemas nas mensagens gravadas no alert.log;
Problemas no agendamento de JOBS no banco de dados, que seja feito por DBMS_JOB ou DBMS_SCHEDULER;
Se ocorre a sobreescrita dos archived logs, terá problemas com o Point-in-Time-Recovery do seu banco de dados, e com isso, uma simples troca do horário pode ser uma catástrofe em seu backup e recover;
Pode ocorrer problemas com o JVM do Oracle;
Todos os impactos citados acima, estão resumidos e que podem ser afetados de imediato, existem outros impactos que podem aparecer depois de 2 ou 3 dias e até mesmo semanas. E para não correr esse risco, existe um procedimento bem básico para os DBAs.

Procedimento

Antes de realizar a troca do horário do servidor e futuramente do banco de dados, siga os procedimentos abaixo:

Realizar um backup full do banco de dados.
Parar os serviços do Listener, exemplo: lsnrctl stop ou lsnrctl stop;
Parar o banco de dados, com shutdown immediate, normal ou transactional.;
Para ambiente Windows: Depois que descer o banco de dados pelo SQL*PLUS, descer o serviço do windows, exemplo: net stop OracleService;
Anotar o horário de STOP GERAL, para saber com exatidão o momento da parada de todos os serviços;
Alterar o horário do servidor (Windows\Linux\Unix);
Após a troca do horário no servidor, esperar 1 hora para subir os bancos. Exemplo, se meu STOP GERAL foi as 00:05AM (antes da troca), anoto esse valor e espero 1 hora, realizo a troca do horário e quando for 01:05AM, meu horário será atrasado para 00:05AM novamente (ajuste para o fim do horário de verão), e a partir desse horário posso subir todos os serviços novamente a partir do horário que desceu, deste modo não regresso no tempo.
Subir todos os serviços novamento, pode ser pela ordem BANCO DE DADOS -> LISTENER -> APLICAÇÃO.
E pronto! Já estamos com nossos horários ajustados para o horário de Brasilia (Oficial Brasileiro).

Recomendação

Nunca deixem os servidores de banco de dados com o ajuste de horário de verão automático, pois a cada ano, as datas são de início e fim podem sofrer alterações e seja necessário patchs para os novos ajustes e fora que isso, pode trazer todos os problemas citados acima no banco de dados, então faça sempre manualmente.

Verifica Status SYS.AUD$

set verify off;
set lines 1000;
set pages 300;
column value new_val blksize;
select value from v$parameter where name = 'db_block_size';
select a.file_id,
a.file_name,
ceil((nvl(hwm,1)*&&blksize)/1024) smallest,
ceil(blocks*&&blksize/1024) currsize,
ceil(blocks*&&blksize/1024) -
ceil((nvl(hwm,1)*&&blksize)/1024) savings
from dba_data_files a,
(select file_id, max(block_id+blocks-1) hwm
from dba_extents where owner='SYS'
group by file_id) b
where a.file_id = b.file_id;




analyze table sys.AUD$ compute statistics;

select
a.blocks, b.blocks hwm, b.empty_blocks
from
dba_segments a, dba_tables b
where
a.SEGMENT_NAME = 'AUD$' and b.TABLE_NAME='AUD$';

Verifica Status Tablespace

spool D:\util\saving1.log;
set verify off;
set lines 1000;
set pages 300;
select sum(bytes)/1024/1024 as "TamanhoFisico(MB)" from dba_data_files;
col "FileSystem" format a12;
select substr(file_name,1,3) as "FileSystem", sum(bytes)/1024/1024 as "Tamanho(MB)" from dba_data_files group by rollup(substr(file_name,1,3)) order by substr(file_name,1,3);

select tbs.tablespace_name,tot.bytes/1024/1024 total_MB,tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 MB_used,sum(nvl(fre.bytes,0))/1024/1024 free_MB,(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,decode(greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 75),75, '', '*') pct_warn from dba_free_space fre, (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) tot, dba_tablespaces tbs where tot.tablespace_name = tbs.tablespace_name and fre.tablespace_name(+) = tbs.tablespace_name group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes order by 5, 1;

col "file_name" format a50;
select file_name, ceil( (nvl(hwm,1)*8192)/1024/1024 ) "SMALLEST-LIVRE", ceil( blocks*8192/1024/1024) "CURRSIZE-CORRENTE", ceil( blocks*8192/1024/1024) - ceil( (nvl(hwm,1)*8192)/1024/1024 ) "SAVINGS-SALVO", ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) smallest_safe, ceil( blocks*8192/1024/1024) - ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) savings_safe from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) <> 100 order by 4 desc;

select 'alter database datafile ''' file_name ''' resize ' ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) <> 100;

select sum(bytes)/1024/1024 as "TamanhoFisico(MB)" from dba_data_files;
col "FileSystem" format a12;
select substr(file_name,1,3) as "FileSystem", sum(bytes)/1024/1024 as "Tamanho(MB)" from dba_data_files group by rollup(substr(file_name,1,3)) order by substr(file_name,1,3);
spool off;

Monitorando o RMAN

Uma dica muito importante é sempre manter uma planilha com todos os bancos de dados, senhas e seus respectivos DBID armazenados após as criação do banco de dados para não correr risco de não saber o DBID do banco de dados criado.

list incarnation;


select sid,
serial#,
context,
sofar,
totalwork,
round(sofar/totalwork*100,2) “%_complete”
from v$session_longops
where opname like ‘RMAN%’
and opname not like ‘%aggregate%’
and totalwork != 0 and sofar <> totalwork;



col "OPERACAO" format a50;
select operation as “OPERACAO”,
object_type as “TIPO”,
status,
output_device_type as “MEDIA”,
to_char(end_time,’DD-MM-RRRR HH24:MI:SS’) as “DATA”,
round(MBYTES_PROCESSED/1024,2) as “TAMANHO(MB)”
from
v$rman_status
where
operation <> ‘CATALOG’
and trunc(end_time)>=trunc(sysdate-1)
order by
end_time;