Conteúdo voltado para banco de dados Oracle e SQL Server

Oracle Archive

A maior parte do tempo de um DBA é gasta usando ferramentas como SQL Developer, SQLPlus e RMAN. Essas duas últimas em específico são muito boas mas também tem algumas limitações banais. Quem nunca errou ao escrever um select e teve que apertar o CONTROL para sair deletando os caracteres? E aquele comando valioso que foi executado no RMAN e você tem que digitá-lo novamente porque o utilitário não acumula o histórico de comandos como é feito no linux.

No exemplo abaixo, ao tentar trocar a letra F por um * ele simplesmente escreve ^H^H^H^H^H^H. Para deletar é preciso apertar CONTROL  +BACKSPACE para que ele delete a linha:

Pois é, depois de passar muita raiva com isso e conviver com o “problema” por meses meus problemas acabaram somente após eu instalar a ferramenta RLWRAP nos meus servidores Linux e com isso usa-la como complemento ás ferramentas SQLPlus e RMAN. Ela traz recursos tais como:

Autocompletar com TAB

Deletar caracteres apenas com o backspace

Usar a setinha pra cima traz o histórico de queries executadas

Chega de prosa e vamos por a mão na massa, siga os passos abaixo e seja feliz:

1 – Instalar o repositório e em seguida o rlwrap propriamente dito:

[root@server01]$ yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm -y

Após instalar o repositório, rode o comando abaixo:

[root@server01]$ yum install rlwrap -y

 

2 – Depois de feita a instalação, precisamos alterar o arquivo ~/.bashrc do usuário oracle:

[root@server01~]# su – oracle
[server01:inst01:/home/oracle]$ vim ~/.bashrc

 

3 – Inserir as duas linhas abaixo no arquivo bashrc:

alias sqlplus=’rlwrap sqlplus’
alias rman=’rlwrap rman’

O seu arquivo bashrc deve ficar dessa maneira:

4 – Feito isso, basta salvar as alterações realizadas no arquivo bashrc e fazer logout do usuário oracle! Depois logue novamente para que as configurações do bashrc sejam aplicadas e abra normalmente o SQLPlus e o RMAN.

Agora será possível apagar os caracteres sem a necessidade de ficar apertando CONTROL, usar o recurso de auto-completar do SQLPlus e do RMAN dentre outros.

Sempre que preciso trabalhar com algo no SQLPlus rodo dois comandos básicos para tratar linhas e páginas:

SYS@inst01 AS SYSDBA> set lines 900;
SYS@inst01 AS SYSDBA> set pages 100;

Toda vez que logo no SQLPlus executo esses comandos, geralmente após rodar o select e ver que veio tudo desconfigurado. Uma dica simples e que me ajudou bastante foi configurar meu ambiente para sempre abrir o SQLPlus com essas configurações. Basta alterar o arquivo glogin.sql conforme passos abaixo:

1 – Logar no terminal com o usuário Oracle:

[root@server01 ~]# su – oracle

 

2 – Acessar o diretório do SQLPlus via variável $ORACLE_HOME

[server01 :inst01:/home/oracle]$ cd $ORACLE_HOME/sqlplus/admin

 

3 – Após entrar na pasta admin, digite o comando ls -l para listar os arquivos:

O arquivo a ser alterado é o glogin.sql, cujo nome está destacado em amarelo na imagem acima.

 

4 – Editar o arquivo através do vi: #vi glogin.sql e adicionar as linhas set line 900 e set pages 900 :

 

5 – Depois de realizar o passo acima, basta salvar as alterações, sair do vi e executar normalmente o SQLPlus que as configurações estarão de acordo com o configurado.

 

Dica simples que evita eu ter que setar a configuração de linhas e linhas por página toda vez que logo no SQLPlus.

 

Nós profissionais de T.I. estamos acostumados a trabalhar com ferramentas em inglês. Alguns programas traduzem termos ao pé da letra e fica complicado administrar o banco de dados com determinadas ferramentas.

O SQL Developer por default vem no idioma do S.O. do usuário, na maioria das vezes em Português. Para alterar o idioma basta seguir alguns simples passos:

1º – Entrar na pasta do SQL Developer e acessar o arquivo: \ide\bin\ide.conf 

Se você não souber onde está a pasta do SQL Developer, basta clicar com o botão direito no Ícone dele, ir em Propriedades e logo após clicar em Abrir

 

2º – Abrir o arquivo ide.conf com algum editor de texto (notepad, notepad++ e afins) e adicionar a linha AddVMOption -Duser.language=en no arquivo:

As linguagens disponíveis no SQL Developer podem ser vistas no arquivo ide.boot, item: oracle.translated.locales. São elas:

de = Alemão
es = Espanhol
fr = Francês
it = Italiano
ja = Japonês
ko = Coreano
pt_BR = Português
zh_CN = Chinês Simplificado
zh_TW = Chinês Tradicional

 

3º – Depois de ter editado o arquivo, basta salvar o arquivo e abrir novamente o SQL Developer através do atalho e ver que a interface mudou:

 

Feito isso basta usufruir do SQL Developer na linguagem preferida.

No Oracle 11g ao tentar enviar um e-mail pelo SGBD, o seguinte erro é retornado:

ORA-24247: acesso à rede negado pela ACL (access control list)
ORA-06512: em “SYS.UTL_TCP”
ORA-06512: em “SYS.UTL_TCP”

Isso ocorre devido ao fato da Oracle ter implementado melhorias de segurança no Oracle 11g em vários objetos, e dentre eles estão as procedures: UTL_MAIL e UTL_SMTP. Essas procedures estão diretamente ligadas ao envio de email. Para resolver o erro, basta criar os seguintes objetos:

Execute esse script, informando os dados

begin
dbms_network_acl_admin.create_acl (
acl => ‘grant_acl_envio_email.xml’,
description => ‘Envio de e-mail pelo Oracle’,
principal => ‘LEONARDO’, –Colocar o nome do usuário que fará o envio do e-mail ou uma role em maiúsculo
is_grant => TRUE,
privilege => ‘connect’
);
commit;
end;

E depois:

begin
dbms_network_acl_admin.assign_acl(
acl => ‘grant_acl_envio_email.xml’,
host => ‘NOME_DO_SERVIDOR_SMTP’ –Nome do servidor SMTP
);
commit;
end;

 

Feito isso o envio de e-mail é realizado com sucesso. Vale lembrar que isso é apenas para corrigir o erro do envio de e-mail, para configurar o Oracle para enviar e-mails é outro procedimento que será discutido em outro post.

 

Até mais.

Para dropar o banco de dados da instância do Oracle é preciso realizar algusn procedimentos além do DROP DATABASE, para realizar tal tarefa basta seguir os passos abaixo:

1 – Digite o comando shutdown abort

SYS@bd120 AS SYSDBA> shutdown abort;
ORACLE instance shut down.

2 – Inicie o banco no estado MOUNT:

SYS@bd120 AS SYSDBA> startup force mount;
ORACLE instance started.

Total System Global Area 1.0689E+11 bytes
Fixed Size 2265864 bytes
Variable Size 5.1808E+10 bytes
Database Buffers 5.4761E+10 bytes
Redo Buffers 323678208 bytes
Database mounted.

3 – Mudar o banco para o restricted mode através do comando alter system enable restricted session:

SYS@bd120 AS SYSDBA> alter system enable restricted session;

Verificar se o banco mudou o status:
SYS@bd120 AS SYSDBA> SELECT logins from v$instance;
LOGINS
———-
RESTRICTED

4 – O banco estando em MOUNT e RESTRICTED, basta digitar DROP DATABASE e aguardar o banco ser dropado:

SYS@bd120 AS SYSDBA> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

 

Esses são os passos necessários para dropar um banco de dados no ORACLE.

Os logs do Oracle são salvos em sua grande maioria no arquivo alert.log! Com o passar do tempo esse arquivo vai acumulando informações e se torna muito grande,  dificultando sua leitura e compreensão não hora do troubleshooting. Já tive arquivos na casa dos GB e a melhor alternativa era copia-lo para a Área de Trabalho e editar com o notepad++.

Para limpar os arquivos de logs podemos utilizar o utilitário ADRCI. Com o usuário Oracle logado digite o seguinte comando:

#adrci

Você receberá uma mensagem semelhante á essa:

ADRCI: Release 12.1.0.1.0 – Production on Wed Dec 21 21:30:43 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
ADR base = “/u01/app/oracle”

Em seguida digite show homes e espera o S.O. listar os homes de cada log gerenciado:

adrci> show homes;

ADR Homes:
diag/tnslsnr/server01/listener_scan4
diag/tnslsnr/server01/listener_scan1
diag/tnslsnr/server01/vs01
diag/tnslsnr/server01/listener_scan3
diag/asmtool/user_root/host_954678459_80
diag/rdbms/vs22/vs22

Agora é preciso setar o homepath de qual arquivo você vai limpar. Para escolhar o alert log digite no sqlplus:

#show parameter user_dump_dest
user_dump_dest string /u01/app/oracle/diag/rdbms/vs22/vs22/trace

Com base na informação acima, sabemos que o homepath do alert.log é o diag/rdbms/vs22/vs22!

#adrci> set homepath diag/rdbms/vs22/vs22

Depois de setar o homepath basta digitar o comando agora para limpar as entradas no alert.log, sendo que 1440 é referente á 1 dia (1440 minutos).

adrci> PURGE -age 1440 -type ALERT

Geralmente deixo os últimos 30 dias de logs para consulta, sendo assim ficaria 1440 x 30 = 43200:

adrci> PURGE -age 43200 -type ALERT

Feito isso o alert.log será limpo de acordo com o tempo que você desejar. É interessante criar uma rotina de expurgo para evitar fazer o serviço manualmente e por ventura esquecer de realizá-la periodicamente.

O erro: ORA-01591: lock held by in-doubt distributed transaction, ou em um bom português ORA-01591: bloqueio retido pela transação distribuída 3251.6.3549 geralmente é causado por timeout entre as instâncias de um Oracle RAC.

A consulta por transações retidas ficam na tabela: DBA_2PC_PENDING. Um simples select nessa tabela retorna informações sobre as transações locais e globais:

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,STATE,
‘EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘||””||LOCAL_TRAN_ID||””||’);’||chr(13)||’COMMIT;’
FROM DBA_2PC_PENDING
ORDER BY 1 ASC;

Para limpar as transações, basta executar o comando abaixo, onde o @local_tran_id é o campo LOCAL_TRAN_ID da tabela DBA_2PC_PENDING:

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘@LOCAL_TRAN_ID’);
COMMIT;

Em alguns casos é preciso realizar o rollback da transação através do comando ROLLBACK FORCE ‘@LOCAL_TRAN_ID’, ficando o script completo dessa maneira:

ROLLBACK FORCE ‘@LOCAL_TRAN_ID’;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘@LOCAL_TRAN_ID’);
COMMIT;

Para automatizar essa limpeza podemos utilizar o seguinte script no crontab ou no Oracle Scheduler e agendar da forma como seu ambiente se comporta, podendo ser diario, semanal ou mensal:

declare
xid varchar2(22);
begin
for c1 in (select LOCAL_TRAN_ID FROM DBA_2PC_PENDING) loop
dbms_transaction.PURGE_LOST_DB_ENTRY(c1.LOCAL_TRAN_ID);
commit;
end loop;
end;

Importante: Em situações que não for possível purgar a transação via pacote DBMS, o procedimento poderá ser feito de forma manual executando os 3 deletes abaixo:

delete from
sys.pending_trans$
where
local_tran_id = ‘LOCAL_TRAN_ID’;

delete from
sys.pending_sessions$
where
local_tran_id = ‘LOCAL_TRAN_ID’;

delete from
sys.pending_sub_sessions$
where
local_tran_id = ‘LOCAL_TRAN_ID’;

commit;

 

É importante frisar que esse procedimento resolve os problemas das transações retidas e não elimina a causa raiz do problema, procure encontrar evidências no ALERT.LOG, no log do GRID para resolver o problema por completo.

 

O Automatic Workload Repository (AWR) é um relatório muito útil para identificar gargalos e problemas de lentidão no Oracle. Esse relatório é disponível para quem tem o pacote DIAGNOSTICS TUNING licenciado, para quem não possui tem o recurso STATSPACK. O AWR pode ser gerado através do SQLPLUS em 4 simples passos:

  1. Qual tipo do relatório a ser gerado. Pode ser em .txt ou html. Eu particularmente prefiro o HTML, facilita bem mais a leitura.
  2. O tempo a ser analisado. Esse tempo deve ser colocado em dias. Exemplo: se quiser ver dados de 1 mês, digitar 30, 2 meses, 60 e assim em diante.
  3. O snapshot id inicial
  4. O snapshot id final

Para iniciar a geração do relatório entre no SQLPLUS:

[SRV:bd01:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 12 16:47:26 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

1º Passo: executar o script awrrpt.sql, ele fica localizado no diretório $ORACLE_HOME/rdbms/admin/awrrpt.sql! Para executar um script dentro do SQPLUS basta colocar o @? e o caminho do arquivo, conforme script abaixo:

SYS@bd01 AS SYSDBA> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
———– ———— ——– ————
3534058528 bd01 1 bd01

2º Passo: Informe o tipo do relatório que será gerado: HTML ou TXT. Se você não digitar nada e apertar enter, o oracle assume o valor padrão e deixa como HTML:

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: html

 

 

3º Passo: Especificar o número de dias a considerar, no meu caso escolhi 90 dias

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 90

Após digitar esse valor, o Oracle lista todos os snapshots disponíveis nesse range de data:

 

4º Passo: Informar o Snapshot inicial e final. No exemplo acima vamos colocar o snapshot 997 a 1001. Primeiro informe o Snapshot Id 997 e dê enter, depois informe o 1001 para o END Snapshot Id e dê enter novamente.

 

Após informar os ID’s, ele pede que você dê um nome ao arquivo, caso você ignore e aperte enter, ele será criado com o nome default: awrrpt_1_snapIdinicial_snapIdfinal.html !

Feito isso basta esperar o AWR ser gerado na pasta em que você estava antes de entrar no SQLPLUS, no meu caso, o usuário estava na pasta /home/oracle:

Ao abrir o AWR você terá algo do tipo:

Basicamente para gerar o AWR é seguir esses passos. Muitas vezes o suporte da Oracle solicita esse arquivo para análises de chamados e gargalos e durante o dia a dia o DBA pode utilizar para identificar problemas de desempenho.

Há alguns dias atrás precisei localizar a string PRAGMA AUTONOMOUS_TRANSACTION em todos os objetos do banco de dados. No SQL Server uma busca rápida pela sys.comments ou sys.sql_modules retorna quais objetos possuem essa string em seu código.

No Oracle a busca também é simples, existem 3 views que ajudam essa busca, são elas: ALL_SOURCE, DBA_SOURCE e USER_SOURCE.

  • ALL_SOURCE: lista todos os objetos que possuem a string desejada somente nos objetos que o usuário logado tem acesso.
  • DBA_SOURCE: lista todos os objetos que possuem a string desejada em todos os objetos da instância.
  • USER_SOURCE: lista somente os objetos cujo usuário logado seja dono.

O trecho PL-SQL para buscar os dados são:

SELECT *
FROM ALL_SOURCE
WHERE UPPER(text) LIKE UPPER(‘%PRAGMA AUTONOMOUS_TRANSACTION%’)
ORDER BY type, name, line;

Abaixo o exemplo da busca em minha base de dados encontrando os objetos que possuem essa string:

Feito isso basta editar a function FC_ASS e ir até a linha 6 para verificar a evidência do código.

Muitas pessoas ao executar operações DDL (alteração de tabelas, inclusão de FK’s, etc) recebem o seguinte erro no SQLPLUS, SQL Developer e afins:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Esse erro ocorre devido á tabela que está sendo alterada estar ocupada com outras operações de manipulação de dados. Nesse caso você sempre receberá o erro ORA-00054 enquanto a tabela que você deseja alterar não estiver disponível.

Para resolver o problema é preciso agendar um horário de baixa atividade no banco de dados, tais como períodos da madrugada ou finais de semana! Fazendo assim, você evita a concorrência durante o dia e consegue executar o script sem esperar por muito tempo. Para ajudar nesse trabalho, existem 2 parâmetros que podem facilitar muito a execução dessa tarefa. São eles:

  • ALTER SESSION ENABLE PARALLEL DDL;
  • ALTER SESSION SET DDL_LOCK_TIMEOUT= 600;

O parâmetro ENABLE PARALLEL DDL habilita a sessão para realizar operações DDL em paralelo com o intuito de reduzir o tempo da operação e o DDL_LOCK_TIMEOUT especifica o tempo em segundos que a sessão vai aguardar por um lock na tabela a ser alterada.

O padrão para o ENABLE PARALLEL DDL é zero, o que indica que você nunca vai esperar por um lock. Sempre deixo o padrão de 10 minutos (600 segundos) quando preciso realizar alguma operação em uma grande tabela no banco de dados.

Abaixo segue o exemplo de execução dos comandos:

enable-paralel-ddl

Feito isso basta rodar a query e aguardar o tempo necessário para executá-la.