Instalação e Uso PK_ABSTRATOS Introdução

Introdução
O pacote PK_ABSTRATOS fornece um conjunto de funções que auxiliam na administração do
banco de dados Oracle. Entre os principais podemos destacar :
• Verificar o consumo de CPU das sessões
• Verificar o consumo de I/O das sessões ( físico e lógico )
• Verificar espaços livre, total e usado das tablespaces
• Coletar informações de consumo de espaço em disco
• Verificar Consumo de Tempo das Sessões Ativas
• Analisar Perfil de Carga do Banco de Dados ( STATSPACK Requerido )
• Verificar a data da Coleta de estatísticas do CBO
• Verificar Jobs do Banco de Dados
• Verificar Informações sobre os Segmentos
• Eliminar Sessões dos Usuários ( INATIVAS ou ATIVAS ) com exceções
• Realizar a Engenharia Reversa de Objetos do Banco
• Monitoramento de Eventos do Banco de Dados ( Com notificações via email )
o Tablespaces com Pouco Espaço
o Número Processos Ativos
o Verificar Sucesso do Backup do RMAN (Dados e Archives)
o Verificar Percentual de Segmentos de Undo Ativos
o Configuração de Novos Eventos com limites parametrizáveis
1 - Instalar a Package de Instalação
Instalar como usuário SYS ou como usuário DBA privilegiado, usando o SQLPlus.
A versão 9i pode ser instalada no Oracle 10g ou 11g. Caso algum problema seja encontrado,
então recomendamos utilizar a versão correspondente ao banco de dados.
Para o Oracle 9i
SQL>@d:\abstratos\pk_abs_adm_9i.pksw
SQL>@d:\abstratos\ pk_abs_adm_9i.pkbw
Para o Oracle 10g ou 11g
SQL>@d:\abstratos\pk_abs_adm.pksw
SQL>@d:\abstratos\ pk_abs_adm.pkbw
1.1 - Criar a Tablespace
Basta criar apenas uma tablespace para os DADOS.
Caso a tablespace desejada já exista, continuar a partir do passo 1.2.
execute
pk_abstratos_admin.pr_criar_tablespace('TS_ABSTRATOS_D','D:\ORACLE\PRODUCT\10.2.0\
ORADATA\ABSTRA', 'TS_ABSTRATOS_D01.DBF',50);
Requisito
O usuário deverá ter o privilégio CREATE TABLESPACE;
GRANT CREATE TABLESPACE TO <USUARIO DBA USADO PARA REALIZAR A
INSTALAÇÃO>;
1.2 - Criar os Objetos
Requisito Importante ( Porém opcional )
O pacote STATSPACK deve ser instalado previamente, para dar suporte a algumas
funcionalidades adicionais.
execute pk_abstratos_admin.pr_instalar_objetos ('ABSTRATOS','DB1234AB',
'TS_ABSTRATOS_D', 'TEMP');
Caso o usuário no qual está sendo feita a instalação já exista, é possível apenas conceder os
privilégios necessários ao mesmo com o seguinte procedimento.
SQL>execute pk_abstratos_admin.pr_conceder_privilegios('ABSTRATOS');
1.3 – Importar o DUMP padrão
imp file=exp_abstratos.dmp log=imp_abstratos.log fromuser=abstratos
userid=abstratos/db1234ab buffer=50000000 ignore=y commit=y
1.4 – Ajustar as Configurações
Ajustar a tabela TB_PARMETROS com o servidor SMTP correto.
Ajustar a tabela de CONTATOS e GRUPOS de Notificação com os emails pertinentes
Ajustar a tabela TB_EVENTOS com os parâmetros pertinentes ao banco de dados do cliente,
como nomes de tablespaces, valores limites, etc..
1.5 - Instalar os Pacotes de Administração do Banco
Para o Oracle 9i
SQL>@d:\abstratos\pk_abs_9i.pksw
SQL>@d:\abstratos\pk_abs_9i.pkbw
Para o Oracle 10g ou 11g
SQL>@d:\abstratos\pk_abs.pksw
SQL>@d:\abstratos\pk_abs.pkbw
2 – Utilização da PK Abstratos
2.1 – Monitoramento de Espaço das Tablespaces
Função
Objetivo
Parâmetros
pnm_tablespace
Sintaxe
Observações
fu_ver_espaco_tablespace
Mostrar o espaço livre, usado e total das tablespaces, bem como o
percentual de utilização.
Valor
Valor Padrão
Nome da Tablespace
%
pk_abstratos.fu_ver_espaco_tablespace(<pnm_tablespace>)
Os valores são apresentados em mega bytes (MB)
2.1.1 - Mostrar os espaços da Tablespace SYSAUX
SQL>select * from table(pk_ABSTRATOS.fu_ver_espaco_tablespace('SYSAUX')) ;
NM_TABLESPACE VL_TOTAL
SYSAUX
280
VL_USADO
263,62
VL_LIVRE
16,38
PC_USADO
94,15
2.1.2 - Mostrar os espaços de todas as tablespaces
SQL>select * from table(pk_ABSTRATOS.fu_ver_espaco_tablespace()) ;
NM_TABLESPACE VL_TOTAL
SYSAUX
280
USERS
272,5
EXAMPLE
143,75
SYSTEM
610
TS_ABSTRATOS_D
50
UNDOTBS1
215
VL_USADO
263,62
175,19
140,81
606,31
0,69
47,19
VL_LIVRE
16,38
97,31
2,94
3,69
49,31
167,81
PC_USADO
94,15
64,29
97,95
99,4
1,38
21,95
2.2 – Processos com maior consumo de CPU
Função
Objetivo
Parâmetros
pqt_segundos
pqt_registros
Sintaxe
Observações
ver_consumo_cpu
Mostrar as sessões que estão consumindo mais CPU
Valor
Valor Padrão
Quantidade de Segundos para analisar
5
Quantidade de Registros a Mostrar
20
ver_consumo_cpu(<pqt_segundos>,<pqt_registros>)
Os valores apresentados estão em segundos consumidos pelas sessões.
2.2.1 - Mostrar as sessões que consumirão mais cpu nos próximos 5 segundos
SQL> select * from table(pk_ABSTRATOS.fu_ver_consumo_cpu);
NU_SID NU_SERIAL NM_USUARIO_DB NM_USUARIO_SO VL_ESTATISTICA
136
170
141
150
63
1
5 SYS
1
SYSTEM
SYSTEM
ESTWIN01\Abstra
SYSTEM
10
10
0
0
PC_PCT_
TOTAL
50
50
0
0
2.2.2 - Mostrar as 10 sessões que consumirão mais cpu nos próximos 20 segundos
SQL>select * from table(pk_ABSTRATOS.fu_ver_consumo_cpu(20,5)) ;
NU_SID NU_SERIAL NM_USUARIO_DB NM_USUARIO_SO VL_ESTATISTICA
136
170
141
150
13
63
1
5 SYS
1
20
SYSTEM
SYSTEM
ESTWIN01\Abstra
SYSTEM
ESTWIN01\Test
20
20
15
15
0
PC_PCT_
TOTAL
28,57
28,57
21,43
21,43
0
2.3 – Processos com maior consumo de IO
Função
Objetivo
Parâmetros
pqt_segundos
pqt_registros
Sintaxe
Exemplos
ver_consumo_io
Mostrar as sessões que estão consumindo mais I/O, físico e lógico.
Valor
Valor Padrão
Quantidade de Segundos para analisar
5
Quantidade de Registros a Mostrar
20
ver_consumo_io (<pqt_segundos>,<pqt_registros>)
1) Mostrar as sessões que consumirão mais I/O nos próximos 5 segundos
select * from table(pk_ABSTRATOS.fu_ver_consumo_io);
2) Mostrar as 30 sessões que consumirão mais I/O nos próximos 15
segundos
select * from table(pk_ABSTRATOS.fu_ver_consumo_io(15,30)) ;
2.4 – Ver Sessões
Função
Objetivo
Parâmetros
pnm_usuario_db
pnm_usuario_so
pnm_maquina
Sintaxe
fu_ver_sessoes
Mostrar informações as sessões e o tempo de ATIVIDADE e
INATIVIDADE
Valor
Valor Padrão
Filtro por usuário do banco que está
%
conectado
Filtro por usuário do S.O. que está
%
conectado
Filtro por máquina que está conectada
%
pk_abstratos.ver_consumo_io (<pnm_usuario_db>,< pnm_usuario_so>,<
pnm_maquina>)
2.4.1 - Mostrar as sessões de todos os usuários
SQL>select * from table(pk_ABSTRATOS.fu_ver_sessoes()) ;
NU_SID NM_USUARIO_DB NM_USUARIO_SO DS_SITUACAO VL_DURACAO_SITUACAO
136 ORACLE
139 SYS
SYSTEM
Altemir
ACTIVE
ACTIVE
15
0
141 SYS
150 ORACLE
152 ORACLE
ESTWIN01\Altemir
SYSTEM
SYSTEM
INACTIVE
ACTIVE
ACTIVE
3429
4182
2097
154 ORACLE
159 ORACLE
SYSTEM
SYSTEM
ACTIVE
ACTIVE
4191
3871
2.4.2 - Mostrar as sessões do usuário de banco chamado ABSTRATOS.
SQL>select * from table(pk_ABSTRATOS.fu_ver_sessoes('ABSTRATOS','%','%')) ;
NU_SID NU_SERIAL NM_USUARIO_DB NM_PROGRAMA DS_SITUACAO
141
7 ABSTRATOS
sqlplusw.exe
INACTIVE
VL_DURACAO_
SITUACAO
0
2.5 – Ver Tamanhos dos Segmentos
Função
Objetivo
Parâmetros
pnm_grupo
pfl_tipo
Sintaxe
Observações
fu_ver_tamanho_segmentos
Mostrar o tamanho dos segmentos agrupados por Esquema ou Tablespace e
Tipo de Segmento
Valor
Valor Padrão
Nome do Esquema ou Tablespace
%
Tipo de Consulta
1
1 – Esquema
2 – Tablespace
pk_abstratos.fu_ver_tamanho_segmentos (<pnm_grupo>,<pfl_tipo>)
Os tamanhos são apresentados em Mega Bytes (MB)
2.5.1 – Tamanhos dos Segmentos Agrupando por Esquema
Permite ver tamanhos dos segmentos agrupados por esquema e tipo de segmento : DADOS,
INDICES
SQL>select * from table(pk_ABSTRATOS. fu_ver_tamanho_segmentos('ABSTRATOS',1));
NM_ESQUEMA DS_TIPO VL_TAMANHO NM_TABLESPACE
ABSTRATOS
ABSTRATOS
ABSTRATOS
CLUSTER
TABLE
INDEX
0,13
15,94
32,31
2.5.2 – Tamanhos dos Segmentos Agrupando por Tablespace
Permite ver tamanhos dos segmentos agrupados por tablespace e tipo de segmento :
USERS2, TOOLS
SQL>select * from table(pk_ABSTRATOS.fu_ver_segmentos('USERS2',2));
NM_ESQUEMA
DS_TIPO
NESTED TABLE
CLUSTER
LOBINDEX
LOBSEGMENT
INDEX
TABLE
VL_TAMANHO
0,13
0,13
0,63
0,63
80,69
92,94
NM_TABLESPACE
USERS2
USERS2
USERS2
USERS2
USERS2
USERS2
2.6 – Ver Coleta de Estatísticas de Segmentos ( Tabelas e Índices )
Função
Objetivo
Parâmetros
pnm_esquema
pfl_tipo
Sintaxe
Observações
fu_ver_coleta_estatisticas
Mostrar o tamanho as datas de coleta de estatísticas dos segmentos
agrupados por tipo de segmento e por data de coleta, e mostrar também um
resumo sobre o percentual de estatísticas já coletadas de um determinado
esquema.
Valor
Valor Padrão
Nome do Esquema ou Tablespace
%
Tipo de Consulta
1
1 – Agrupada por Esquema
2 – % já Coletado
pk_abstratos.fu_ver_coleta_estatisticas (<pnm_esquema>,<pfl_tipo>)
O tipo de consulta 2 ajuda a acompanhar a execução do processo de coleta
de estatísticas.
2.6.1 – Ver Coleta Agrupando por Tipo de Segmento
select * from table(pk_ABSTRATOS.fu_ver_coleta_estatisticas('ABSTRATOS',1));
DS_TIPO
Tabelas
Tabelas
Tabelas
Tabelas
Tabelas
Índice
Índice
DT_ANALISE
25/7/2008
27/7/2008
24/7/2008
4/8/2008
24/7/2008
QT_OBJETOS PC_ANALISADO
6
1
2
9
1
1
6
2.6.1 – Ver percentual já coletado de um determinado esquema
select * from table(pk_ABSTRATOS.fu_ver_coleta_estatisticas( 'ABSTRATOS',2));
DS_TIPO
TABELA
INDICE
DT_ANALISE
QT_OBJETOS
PC_ANALISADO
68,42
85,71
2.7 – Ver Jobs do Banco de Dados
Função
Objetivo
Parâmetros
pnm_usuario
Sintaxe
fu_ver_jobs
Mostrar os jobs agendados no banco de dados.
Valor
Valor Padrão
Nome do Usuário dono do JOB
%
pk_abstratos.fu_ver_jobs(<pnm_usuario>)
2.7.1 – Mostrando Jobs de Todos os Usuários
SQL>select * from table(pk_ABSTRATOS.fu_ver_jobs()) ;
NU_JOB
DS_ACAO
NM_USUARIO
EMD_MAINTENANCE.EX
ECUTE_EM_DBMS_JOB
1 _PROCS();
SYSMAN
wb_rti_service_job.check
322 _service(1);
OWBREP
DT_PROXIMA DS_INTERVALO FL_QUEBRADO
sysdate + 1 / (24
27/8/2008 * 60)
N
sysdate +
27/8/2008 (6/1440)
N
2.8 – Ver Compressão de Índices
Função
Objetivo
Parâmetros
pnm_grupo
pfl_tipo
Sintaxe
fu_ver_compressao_indices
Mostrar a quantidade de índices que estão com compressão de chave ativada
por agrupando a informação por esquema ou por tablespace
Valor
Valor Padrão
Nome do Esquema ou Tablespace
%
Tipo de Consulta
1
1 – Agrupada por Esquema
2 – Agrupada por Tablespace
pk_abstratos. fu_ver_compressao_indices(<pnm_esquema>,<pfl_tipo>)
2.8.1 – Mostrando compressão de índices de todos os esquemas
SQL>select * from table(abstratos.pk_ABSTRATOS.fu_ver_compressao_indices('%',1)) ;
NM_GRUPO DS_SITUACAO QT_SEGMENTOS
SH
OWBREP
PERFSTAT
OWBRT_SYS
SH
PM
Desativado
Ativado
Desativado
Desativado
Ativado
Desativado
28
77
68
2
1
20
2.9 – Observar Outras Estatísticas de Funcionamento do Banco de Dados
SQL>select * from table(pk_ABSTRATOS.fu_ver_estatistica(<Código
Estatística>,<tempo>,<qtd registros>)) ;
SQL>select * from table(pk_ABSTRATOS.fu_ver_estatistica(12,5,10)) ;
Obs: 12 = CPU Usada pela sessão
3 – Operações de Manutenção no Banco de Dados
3.1 – Coletar de Estatísticas do Otimizador
execute pk_abstratos.pr_coletar_estatisticas( 'OE' )
3.2 – Coletar consumo de espaço
SQL> execute abstratos.pk_abstratos.pr_coletar_espaco
Instalação do JOB
SQL>variable job number;
SQL>execute dbms_job.submit(job=>:job, what=>'abstratos.pk_abstratos.coletar_espaco(''OBOECARD'',''S'');',next_date=>trunc(sysdate)+22/24,interval=>'trunc(sysdate+1)+22/24');
4 – Monitoramento do Banco de Dados
2.1 – Monitoramento de Eventos - ( pr_avaliar_eventos )
Avaliará, executará, os eventos configurados no banco de dados e enviará um email de
notificação caso seja necessário, caso um dos limites AVISO ou CRÍTICO seja atingido.
SQL> execute pr_avaliar_eventos.
Obs : É importante criar um JOB de banco de dados com execução a cada 5 minutos da rotina
acima.
2.1.1 - Instalação do JOB para monitorar eventos de 5 em 5 minutos.
SQL>variable job number;
SQL>execute dbms_job.submit(job=>:job, what=>'abstratos.pk_abstratos.pr_avaliar_eventos;',next_date=>sysdate+5/1440,interval=>'sysdate+5/1440';
2.2 – Executar Consultas Dinamicamente - ( fu_executar_consulta )
Usada para executar sentenças SELECT dinamicamente, onde o retorno da função será um
resultset que deverá ser manipulado com a instrução OPEN-FOR do PL/SQL ou por alguma
estrutura equivalente em outras linguagens de programação.
Exemplo :
Manipular o resultado de uma consulta dinâmica que mostre os 10 primeiros objetos da tabela
DBA_OBJECTS.
set serveroutput on
declare
cr_sql pk_abstratos.ty_rf_geral;
aux varchar2(100);
begin
cr_sql := pk_abstratos.fu_executar_consulta('select OBJECT_NAME from DBA_OBJECTS
WHERE ROWNUM <=10');
loop
fetch cr_sql into aux;
exit when cr_sql%notfound;
dbms_output.put_line('Dados ...:'||aux);
end loop;
end;
/
5
– Geração de Scripts
6
– Operações Auxiliares
6.1 – Montar a Sentença SQL a partir do HASH_VALUE da Consulta
Sintaxe
fu_montar_sql(<hash_value da v$session>)
Exemplo
select fu_montar_sql(1218005246)
from dual;
ANEXO I – Detalhes Monitoramento de Eventos
1 – Objetivo
O objetivo deste documento é descrever o funcionamento e a estrutura do aplicação de
monitoramento de ambiente de banco de dados e sistema operacional da Abstratos tecnologia.
2. – Funcionamento
2.1 Descrição do Funcionamento
A aplicação rodará como um JOB no banco de dados e a cada execução fará a
avaliação/execução dos que eventos estão ativos, observando a freqüência de
avaliação do mesmo.
O resultado da avaliação do evento podem estar em três situações :
N – Normal – Significa que não ultrapassou os valores limites
A – Alerta – Significa que ultrapassou o limite de aviso.
C – Crítico – Significa que ultrapassou o limite de crítico e uma ação de reparo
deve ser realizada com urgência.
Pode ser configurado um intervalo mais freqüente de avaliação do evento, quando
atinge o nível crítico ou de alerta.
O evento com estado NORMAL pode ter uma freqüência de notificação diferente
da freqüência de avaliação. Normalmente a freqüência de notificação será menor
do que a freqüência de avaliação.
A aplicação permite que se criem grupos de notificação os quais podem ter várias
pessoas de contato associadas. Cada evento fica associado a um grupo de
notificação. Quando uma notificação se fizer necessária, as pessoas do grupo
receberão o email com a mensagem adequada.
2.2 Trabalho Agendado
Ação : pk_monitora.pr_avaliar_eventos
Freqüência : 1 vez a cada minuto
3 – Tabelas da Aplicação
Tabela TB_PARAMETROS
Nome Campo
Descrição
DS_SERVIDOR_SMTP
Endereço IP ou nome do servidor SMTP para envio de mensagens
CD_CONTATO_ORIGEM
Código do contato que será usado como origem, “from” das
mensagens enviadas.
Tabela TB_CONTATOS
Nome Campo
Descrição
CD_CONTATO
Código seqüencial do pessoa/contato.
NM_CONTATO
Nome da pessoa/contato.
DS_EMAIL
Email da pessoa/contato.
Tabela TB_GRUPO_NOTIFICACAO
Nome Campo
Descrição
CD_GRUPO
Código seqüencial do grupo de notificação.
NM_GRUPO
Nome do grupo de notificação.
Tabela TB_GRUPO_NOTIF_CONTATO
Nome Campo
Descrição
CD_GRUPO
Código do grupo de notificação.
CD_CONTATO
Código do pessoa/contato.
Tabela TB_EVENTO
Nome Campo
Descrição
CD_EVENTO
Código seqüencial do evento
NM_EVENTO
Nome do evento.
DT_EVENTO
Data de criação do evento.
FL_TIPO_RESULTADO
Tipo de dados do resultado. (NUMBER, VARCHAR2, DATE)
FL_TIPO_OPERADOR
Operador usado na comparação do resultado : <, <=,>,>=,BETWEEN,
NOT BETWEEN, LIKE, NOT LIKE, IS NULL, IS NOT NULL
VL_LIMITE_AVISO1
Valor limite, ou limite inferior, para envio de mensagens de aviso
VL_LIMITE_AVISO2
Valor limite superior para envio de mensagens de aviso. Usado quando
o operador for BETWEEN ou NOT BETWEEN
VL_LIMITE_CRITICO1
Valor limite, ou limite inferior, para envio de mensagens críticas
VL_LIMITE_CRITICO2
Valor limite superior para envio de mensagens críticas. Usado quando o
operador for BETWEEN ou NOT BETWEEN
DS_CONSULTA
Consulta a ser realizada para obter o valor atual do evento. Deve
retornar somente uma única linha e uma única coluna cujo cabeçalho
obrigatoriamente deve ser chamado de “valor”. Ex: Select count(*) valor
from v$sesssion where username is NOT NULL.
FL_CONSULTA_CRIPTO
Indica se a consulta está criptografada ou não.
DS_OBSERVACAO
Observação sobre o evento.
FL_EVENTO_ATIVO
Flag que indica se o evento está ativo. (A para Ativo, I para Inativo )
DS_ACAO
Script ou sentença SQL que deve ser realizada em resposta a atuação
do evento em nível crítico.
FL_TIPO_ACAO
Tipo de ação a ser executada. 1-'SQL',2-'PL/SQL',3-'SCRIPT'
CD_GRUPO_NOTIFICACAO
Código do grupo de notificação a ser notificado quando o evento
acontecer.
VL_INTERVALO
Intervalo de tempo entre as execuções para avaliações do evento.
FL_UNIDADE_INTERVALO
Unidade de medida do intervalo de tempo. 'D' Dia,'H' Hora,'M'
Minuto,'S' Segundo
FL_SITUACAO_EVENTO
Situação apresentada pelo evento na última execução. 'N' Normal,'A'
Aviso,'C' Crítico
DT_ULTIMA_EXECUCAO
Data da última execução do evento.
VL_ULTIMA_EXECUCAO
Valor apresentado na última execução.
DS_UNIDADE_MEDIDA
Unidade de medida do evento monitorado. Exemplo : '%', Seg, Min,
Mega, Giga, etc.
VL_INTERVALO_NOTIFICA_NO
RMAL
Intervalo de notificação do evento normal
VL_INTERVALO_AVISO
Intervalo de avaliação do evento em nível de aviso
VL_INTERVALO_CRITICO
Intervalo de avaliação do evento em nível crítico
DT_ULTIMA _NOTIFICACAO
Data em que foi realizada a última notificação
QT_ATUAL_OCORRENCIA_CRI
TICO
Quantidade atual de ocorrências críticas do evento.
QT_MAXIMA_OCORRENCIA_C
RITICO
Máximo de ocorrências do evento crítico antes de executar a ação
corretiva.