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

Author Description

Leonardo Pedroso

Leonardo Pedroso, é administrador de banco de dados, possui amplo conhecimento em Oracle 11g e 12c, com atuação em empresas de telecomunicações, bancos, seguradoras e órgãos do governo brasileiro prestando serviços especializados em banco de dados. Atua ativamente em comunidades técnicas através de fóruns, eventos, blog e palestras.

Um erro muito comum em empresas que possuem equipes distintas de DBA, AD e Desenvolvimento é criar as estruturas de banco sem o devido cuidado em indexar as FK’s. Sistemas e melhorias são feitos diariamente e isso pode incluir novos dados, colunas, constraints e demais objetos no banco dedados.

Um script que costumo rodar semanalmente é o que verifica se as FK’s estão indexadas, pois dependendo do uso da tabela, podemso ter um aumento do wait TM CONTENTION. NO meu caso, todos os schemas começam com DB, portanto eu ajustei em 2 lugares para trazer somente os dados referente á esses schemas:

  AND a.owner LIKE 'DB%' 
  WHERE  c.index_owner LIKE 'DB%' 

Basta adaptar o script ás suas necessidades, ou se preferir não filtrar nada, retira esse trecho do código e execute-o.

O script é simples mas de grande utilidade, conforme pode ser visualizado abaixo:

SELECT CASE 
         WHEN b.table_name IS NULL THEN 'unindexed' 
         ELSE 'indexed' 
       END               AS status, 
       a.owner, 
       a.table_name      AS table_name, 
       a.constraint_name AS fk_name, 
       a.fk_columns      AS fk_columns, 
       b.index_name      AS index_name, 
       b.index_columns   AS index_columns 
FROM   (SELECT a.owner, 
               a.table_name, 
               a.constraint_name, 
               Listagg(a.column_name, ',') 
                 within GROUP (ORDER BY a.position) fk_columns 
        FROM   dba_cons_columns a, 
               dba_constraints b 
        WHERE  a.constraint_name = b.constraint_name 
               AND b.constraint_type = 'R' 
               AND a.owner LIKE 'DB%' 
               AND a.owner = b.owner 
        GROUP  BY a.owner, 
                  a.table_name, 
                  a.constraint_name) a, 
       (SELECT table_name, 
               index_name, 
               Listagg(c.column_name, ',') 
                 within GROUP (ORDER BY c.column_position) index_columns 
        FROM   dba_ind_columns c 
        WHERE  c.index_owner LIKE 'DB%' 
        GROUP  BY table_name, 
                  index_name) b 
WHERE  a.table_name = b.table_name(+) 
       AND b.index_columns(+) LIKE a.fk_columns 
                                   || '%' 
ORDER  BY 1 DESC, 
          2; 

Uma simples maneira de limpar o arquivo de log do listener (Listener.log) é escrevendo nele mesmo um valor simbólico.

Abaixo vamos aprender como limpar de maneira rapida o log do listener. É recomendável que você compacte e guarde em algum lugar para consultas futuras.

Ao listar
-rw-r—–   1 oracle dba   15G Jul 21 19:05 listener.log

Execute o comando:
# cat /dev/null > listener.log

E verifique que o arquivo foi sobrescrito:
-rw-r—–   1 oracle dba  1,1K Jul 21 19:09 listener.log

 

 

Em algumas situações a tablespace UNDO é preciso ser trocada, e apesar de parecer uma tarefa difícil, dada a importancia dessa tablespace, o procedimento é bem simples.

O parâmetro que controla a tablespace UNDO em uso é o: undo_tablespace, que pode ser verificado no sqlplus através do show parameter:

# show parameter undo_tablespace;

No nosso caso, a tablespace em uso é a UNDOTBS1. Para trocar a tablespace é preciso realizar os seguintes passos utilizando o SQLPLUS:

1º – Criar uma nova tablespace de UNDO:

# CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘+DATA_ASM_DG’ SIZE 2G AUTOEXTEND ON NEXT 1G;

 

2º – Alterar o parâmetro UNDO_TABLESPACE para o nome dessa nova tablespace criada no passo anterior:

# ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=SPFILE;

*Muitos DBA’s já alteram a tablespace com o SCOPE=BOTH, pois ela já realiza a alteração no SPFILE e em memória. Nesse caso a alteração já entra em vigor no momento da alteração e  é persistida no SPFILE após o restart da instância. Eu particularmente prefiro realizar o procedimento em um horário acordado com a gerência e já fazer o restart da instância alterando apenas no SPFILE.

 

3º – Monitorar o uso da tablespace UNDO anterior. O select abaixo informa se ainda há alguma transação na UNDO. Quando o count(*) trouxer o resultado igual zero é porque você já pode dropar a tablespace anterior:

SELECT count(*)
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS1'
);

*o nome a ser incluído aqui é o da tablespace original

 

4º – Por fim remova a tablespace para reutilizar o espaço do ASM:

# DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

 

5º – Ao final do processo, basta verificar no SQLPlus a tablespace que está em uso:

# show parameter undo_tablespace;

 

Esse é o procedimento para trocar a tablespace de UNDO. Vale lembrar que para o ambiente RAC você vai encontrar uma UNDO diferente para cada instância do cluster.

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.