Blog do Flavio Soares

Infelizmente, para quem usa SQLPlus nos Linux sabe como é frustrante não ter o histórico dos comandos executados assim como é possível fazer no shell do Linux.

O RLWRAP é um ferramenta free e muito simples de utilizar que permite vizualizar os históricos executados através do SQLPlus.

Passo 1: Instale o RLWRAP através do YUM, ou abaixe através do site http://utopia.knoware.nl/~hlub/uck/rlwrap/.

$ yum install rlwrap

Passo 2: Mude seu .bashrc e adicione as seguintes linhas.

alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'

Pronto, após o logout do seu usuário os históricos do SQLPlus estarão habilitados.

Nos testes que realizei ele funcionou legal, somente em alguns casos a ação “CONTROL + C”, para cancelar alguma solicitação não funcionou, mas mesmo assim eu o recomendo. Essa é uma dica simples que garanto que vai ser de grande ajuda para muitos.

O Oracle como outros bancos de dados, deve gerenciar múltiplos usuários acessando a mesma data simultaneamente, garantindo o controle de concorrência do banco de dados.

Conflitos de bloqueios (lock conflicts) ocorrem quase que frequentemente durante as operações das transações, mais geralmente são quase todas resolvidos pelo próprio mecanismo de fila da Oracle. Para entender melhor o lock conflicts observe a tabela abaixo.

A tabela mostra a atividade, de duas transações. Observe no horário das 08:15 que a transação 1 não conseguiu fazer o update, pois a transação 2 estava como lock na linha. Somente as 08:25, quando o commit da transação 2 é feito, o lock é liberado que a transação 1 consegui realizar a operação.

Como em nenhum ambiente o cenário é perfeito (como o do meu quadro apresentado), o usuário não vai ficar esperando cinco minutos um simples update de outra transação ser liberado para ele trabalhar, ele vai imediatamente contatar você que o banco está lendo, não esta funcionando, e por ai vai…

Evitando os lock conflicts.

Obviamente para se ter um pouco de prevenção dos conflitos, é necessário assegurar que nenhuma transação execute por longos períodos de tempo sem um commit (DDL, DCL). A cada mudança, assim que necessário, pedir para o LGWR trabalhar (processo de commit), fazendo a gravação dos dados persistente nos arquivos de Redo e consequentemente nos Datafiles

Detectando os lock conflicts.

É realmente fácil detectar usuário que sofrem bloqueios e os que fazem o bloqueio. Para isso criaremos um ambiente para o melhor entendimento do conceito. O script abaixo cria o usuário usr1 e o usuário usr2.

SQL> create user usr1
2 profile default
3 identified by oracle
4 quota unlimited on users
5 account unlock;

Concessão bem-sucedida.

Agora com o usuário usr1 criamos a tabela de produto e disparamos dois insert.

SQL> create table produto
2 (
3 id_produto number primary key,
4 valor number(10,2)
5 )
6 /

Commit concluído.


Ainda como o usuário usr1, vamos dar a permissão de update para o usuário usr2, assim garantimos que o usr2 poderá acessar a tabela de usr1.

SQL> grant update on produto to usr2;

Concessão bem-sucedida.


Agora sim, nosso ambiente está pronto para detectar os locks. Com o usuário usr1 faça um update atualizando a coluna valor para 35 onde o id_produto é igual a 1.

SQL> update produto set valor = 35
2 where id_produto = 1;

1 linha atualizada.


Em outro terminal, com o usuário usr2 também atualizamos a coluna valor onde o id_produto é igual a 1, observe o lock entrando em ação.

SQL> update usr1.produto set valor = 36
2 where id_produto = 1;
_


A sessão do usr2 fica aguardando algum commit da transação do usr1. Para conseguir detectar sessões que estão bloqueando outras sessões, basta executar o select baixo.

SQL> select username from v$session
2 where sid in (
3 select blocking_session from v$session
4 )
5 /

USERNAME
------------------------------
USR1


Verificando usuários que estão aguardando commit de outra sessão.

SQL> select sid, username, blocking_session
2 from v$session
3 where blocking_session is not null;

SID USERNAME BLOCKING_SESSION
----- ------------ ----------------
136  USR2        141


Usuários que estão com o lock, e usuário que estão aguardando a outra transação.


SQL> select sid, username, blocking_session
2 from v$session where blocking_session is not null
3 UNION ALL
4 select A.sid, A.username, A.blocking_session
5 from v$session A, v$session B
6 where A.sid = B.blocking_session;

SID USERNAME BLOCKING_SESSION
---- ---------- ----------------
136  USR2                 141
141  USR1


Resolvendo lock conflicts.

Somente é possível resolver conflitos de bloqueios através de duas maneiras:

1) Emitir um commit na sessão que está bloqueando
2) Terminar a sessão que está bloqueando.

A melhor maneira de resolver é contatar o usuário que está segurando o lock e pedir para liberar, afim de poder completar a transação. Como nem sempre está opção é possível, existe uma maneira mais rápida radical, que seria finalizar a sessão pelo modo kill do Oracle.

ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’ IMMEDIATE;


SQL> select sid, serial#
2 from v$session where username = 'USR1';

Sistema alterado.


Depois de alguns segundos, se voltarmos para o terminal do usuário usr2, pode observar que o update antes que estava aguardando terminar o lock do usuário usr1, é atualizada.


1 linha atualizada.

SQL>


Por fim, aqui está outras views com informações de lock disponível no Oracle:

V$ENQUEUE_LOCK
V$EVENT_NAME
V$LOCK
V$_LOCKV
$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$LOCK_ACTIVITY
V$LOCK_ELEMENT

Uma dica do nosso amigo Jorge, para ambientes RAC, as views como V$SESSION e V$LOCK mostram somente os dados atuais da instância em que o usuário está conectado. Para resolver esse problemas existem as views :

GV$ENQUEUE_LOCK
GV$LOCK_ELEMENT
GV$LOCKS_WITH_COLLISIONS
GV$LOCKED_OBJECT
GV$LOCK
GV$EVENT_NAME
GV$LOCK_ACTIVITY

Que mostram os dados independente, do cluster que ele estará.

Obrigado pela dica Jorge.

Existe no Oracle diversos mecanismos para otimizar a performance do banco de dados, uma dessas formas, é a possibilidade de criar index em tabelas.

Antes de fazermos nosso exemplo prático, deixarei algumas diretrizes que a Oracle recomenda em relação a index. Devemos sempre prestar atenção nessas recomendações afim de realizar um trabalho eficiente. Fique atento com a criação deles, se algum index for mal construído, poderá até afetar a performance do banco dados.

Veja algumas:

Quando criar index

Quando não criar index

Vamos iniciar nosso teste criando um ambiente para ele, execute o seguinte comando para criar a tabela T com os registros da tabela all_objects.

SQL> create table t as select object_name name, object_id id from all_objects;

Tabela criada.

Vamos dar um pouco mais de carga nessa tabela.

SQL> insert into t select * from t;

COUNT(*)
----------
99742

Agora com a tabela criada, vamos executar a seguinte query observando seu tempo:

SQL> set timing on
SQL> select name
2 from t
3 where upper(substr(name,5,2)) in
4 ('AA','V$','AR','OE','HS','AO','KO','NT','ZO','GQ',
5 'XH','JO','BS','ML','AL','WW','PX','XX','IU','AO',
6 'CZ','JX','CQ','CW','CE','CR','CT','CY','CU','CI',
7 'MT','AH','LV','EG','BB','MO','MY','BQ','AE','AT')
8 /

Decorrido: 00:00:04.32


O select acima, executou a consulta em 4 segundos e 32 milisegundos. Agora vamos executar a query novamente, mais antes disso, criaremos um index baseado em função na tabela T, e veremos novamente o tempo gasto para retornar a mesma consulta.


SQL> create index nameidxt
2 on T
3 (upper(substr(name, 5,2)));

Índice criado.


Agora esvaziamos o BUFFER_CACHE, para que o Oracle não apresse a consulta já executada anteriormente.

SQL> alter system flush buffer_cache;

Sistema alterado.


Executamos a mesma consulta, mais agora com o index criado na tabela, compare o tempo gasto da primeira vez que executamos, com a segunda vez.

SQL> select name
2 from t
3 where upper(substr(name,5,2)) in
4 ('AA','V$','AR','OE','HS','AO','KO','NT','ZO','GQ',
5 'XH','JO','BS','ML','AL','WW','PX','XX','IU','AO',
6 'CZ','JX','CQ','CW','CE','CR','CT','CY','CU','CI',
7 'MT','AH','LV','EG','BB','MO','MY','BQ','AE','AT')
8 /

Se compararmos o tempo gasto nas duas execuções veremos uma diminuição enorme no tempo, de 4.32 para 0.70, ou seja a mesma query executada apenas com a inclusão de um index baseado em função, nos permite estar em um nível maior de performance.

Apesar do index fornecer para esse tipo de situação, e para outros, notórios tempos de melhorias, é importante saber aonde criar cada um deles, observar atentamente as recomendações citadas acima, para que possa atender a sua real necessidade.

Auditoria é a habilidade do banco de dados Oracle poder gerar logs de auditoria (XML, tabelas, arquivos de SO, …) em atividades suspeitas do usuário, como por exemplo: monitorar o que um determinado usuário está fazendo ou até mesmo saber para qual valor a coluna de salário da tabela empregados está sendo atualizadas. Com a auditoria, esses dados são facilmente capturados e guardados para posteriormente serem analisados.

Realizar auditorias basicamente consiste em 2 passos:
1) Deixar o banco em modo auditoria.
2) Especificar os objetos/privilegio/instrução a serem auditados.

Para habilitar a auditoria, é necessário mudar o parâmetro de inicialização audit_trail, para que o Oracle inicie e reconheça o tipo de auditoria que você necessita. Ele suporta os seguintes valores, cada um com o seguinte função.

OS : Auditoria Habilitada, os registros vão ser gravados em diretorios do sistema em arquivos de auditoria.
DB ou TRUE : Auditoria é habilitada, os registros de auditoria serão armazenadas no database (SYS.AUD$)
XML : Auditoria é habilitada, os registros serão armazenados em formatos XML.
NONE ou FALSE : Auditoria é desabilitada.
DB_EXTENDED : Trabalha igual ao parâmetro DB, mais as colunas SQL_BIND e SQL_TEXT são preenchidas.

Quando se seleciona os modos OS ou XML, arquivos são criados com os registros de auditoria, eles se localizam parâmetro audit_file_dest.

Em nosso exemplo prático, vamos utilizar o tipo de auditoria DB, então executares o seguintes comandos:

SQL> ALTER SYSTEM SET audit_trail = “DB” SCOPE = SPFILE;

Total System Global Area 826277888 bytes
Fixed Size 1222072 bytes
Variable Size 222300744 bytes
Database Buffers 595591168 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.

Agora será necessário especificar as opções de auditoria, elas são em três partes, faremos exemplos das três. Antês disso, vamos criar o usuário user_audit, para trabalharmos com ele na auditoria.

SQL> conn sys as sysdba
Enter password:
Connected.

Grant succeeded.

Agora poderemos trabalhar com o usuário user_audit.

1) Auditoria de Instrução.

Sintaxe :

AUDIT clausula_instrução BY { SESSION | ACESS } WHENEVER [ NOT ] SUCCESSFUL;

BY SESSION : Resumi a escrita dos registros por sessão para o mesmo tipo de instrução em um mesmo objeto, essa é a opção default.
BY ACCESS : Escreve um registro para cada vez que a auditoria é executada.
WHENEVER SUCCESSFUL : Instrução bem-sucedidas, que não mostram mensagens de erros.
WHENEVER NOT SUCCESSFUL : Instrução não bem-sucedidas, que mostram mensagens de erros.

Queremos conceder o privilégio CREATE TABLE, mais queremos ter um registro de auditória quando o comando for executado.

Com o usuário sys, damos o privilégio de criação de tabela para o usuário USER_AUDIT, e criamos a auditoria de instrução TABLE, ou seja,as instruções afetadas na tabela, incluindo CREATE TABLE, DROP TABLE e TRUNCATE TABLE.

SQL>GRANT create table TO user_audit;

Audit succeeded.

Agora com o usuário USER_AUDIT, criamos a tabela table_audit.

SQL> create table table_audit
2 (
3 id number
4 )
5 /

Table created.

Para verificarmos se a auditoria foi bem sucedida, basta entrarmos como um usuário sysdba e verificar na view dba_audi_trail o registro criado.

SQL> column username FORMAT A10
SQL> column obj_name FORMAT A10
SQL> column sql_text FORMAT A10
SQL> column timestamp FORMAT A35
SQL> column action_name FORMAT A20
SQL> set linezie 300

USERNAME TIMESTAMP OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- --------------------
USER_AUDIT 27/09/2009 08:09:40 table_audit CREATE TABLE

Para desativar a auditoria do usuário user_audit, utilizamos o comando NOAUDIT, como a seguir:

SQL> noaudit index by user_audit
Noaudit succeeded.

Vamos fazer um outro exemplo, mais agora sobre a sessão do usuário. Há casos em que há a necessidade de auditar, tanto os logins bem sucedidos como os malsucedidos. Veja como é simples fazer isso com auditoria.

SQL> audit session whenever successful;

Audit succeeded.

Para verificar as trilhas basta:

SQL> select username, to_char(timestamp, 'DD/MM/YYYY HH24:MI:SS') Timestamp,
2 returncode, action_name
3 FROM dba_audit_trail
4 WHERE action_name in ('LOGON', 'LOGOFF');

USERNAME TIMESTAMP RETURNCODE ACTION_NAME
------------ --------------- ------------ ---------------
SYSMAN 27/09/2009 07:15:40 0 LOGOFF
USER_AUDIT 27/09/2009 09:20:25 1017 LOGON

Observe a coluna returncode no usuário user_audit, ela representa o número da mensagem de erro da Oracle, o ORA-1017 indica que a senha está incorreta, uma outra alternativa se estivéssemos interessados somentes nos logons e logoffs, poderiamos usar a view DBA_AUDIT_SESSION.

2) Auditoria de objetos.

Para auditar comandos DML em tabelas, devemos utilizar esse tipo de auditoria, a sintaxe é parecida com o de instrução. Se quiséssemos auditar todos os comandos update executados na tabela audit_table do usuário user_audit, poderiamos fazer …

SQL> audit update on user_audit.audit_table by access whenever successful

Se o usuário user_audit decidir atualizar a tabela, ele será auditado.

SQL> update table_audit set id = 1 where id = 2;

1 row updated.

Podemos verificar na view dba_audit_trail a auditoria.

SQL> select username, to_char(timestamp, 'DD/MM/YYYY HH24:MI:SS') Timestamp, action_name
2 FROM dba_audit_trail
3 WHERE username = 'USER_AUDIT'

USERNAME TIMESTAMP ACTION_NAME
--------------------- --------------------- -------------------
USER_AUDIT 27/09/2009 11:38:41 UPDATE
USER_AUDIT 27/09/2009 11:38:58 UPDATE


3) Auditoria de privilégios.

Auditoria de privilégios tem quase a mesma sintaxe básica dos outros tipos …

SQL> audit alter tablespace by access whenever successful;

Audit succeed.

Toda vez que o privilégio ALTER TABLESPACE é utilizado com sucesso, um novo registro é adicionado a SYS.AUD$.

Não há só a dba_audit_trail para a visualização dos registros de auditoria, para verificar todas as views disponível, podemos fazer uma busca na dba_views.

SQL> SELECT view_name FROM dba_views
2 WHERE view_name LIKE 'DBA%AUDIT%'
3 ORDER BY view_name;

14 rows selected.

Por fim, faltou falar sobre a auditoria FGA, que é uma auditoria refinada (Fine-grained object auditing), que é implementada pelo package DBMS_FGA. A auditoria padrão que acabamos de aprender, é feita facilmente em objetos que foram acessados e quem acessou, mais não se você quizer descobrir as colunas ou linhas que foram acessadas, é nessa parte que entra a auditoria refinada, em um outro artigo falarei somente sobre a FGA, até lá.

Hi guys.

Fico contente por a GPO blogs ter aberto esse caminho tão importante para mim. Tenho 20 anos, sou do interior de São Paulo, estou cursando o segundo ano de Sistemas de Informação. Em relação a Oracle, tenho os dois cursos preparatórios para as certificação IZ0-051 e IZ0-042. Atualmente estou me preparando para a certificação OCA, no próximo mês já gostaria de realizar a prova.

“Ainda” não sou um expert no universo Oracle, mais acredito que através desse blog possamos compartilhar informação para juntos crescermos.

Realmente espero que minhas explicações possam ser claras e objetivas, e que possam ajudar diversos estudantes e DBA, para que o grupo de profissional Oracle possa se fortalecer ainda mais.

Sem mais, eu gostaria de disponibilizar meu e-mail, flaviosoares@live.com, para qualquer dúvida, problema ou sugestão de post, que serão muito bem vindas.