BLOG RODRIGO ALMEIDA

Olá,

Muitos profissionais, principalmente iniciantes tem ou já tiveram bastantes problemas de conexão com o banco de dados Oracle, os conhecidos erros de TNS, sigla para Transparent Network Substrate. Esses erros ocorrem durante uma tentativa de conexão com o banco de dados ou até mesmo quando se está utilizando Database Link (DBLINK) para uma outra base de dados.

A origem desses erros podem ser diversos e sempre estarão ligados com o SQL*NET ou a sua infra-estrutura de rede LAN/WAN. Para quem não conhece o SQL*NET ou Net8 (Antes do Oracle8i), é um produto de middleware da Oracle que oferece suporte para as conexões (Transparent Connection) entre cliente/servidor, entre bancos de dados Oracle  ou ambientes não-Oracle (Transparent Gateways).

Porém, nossa meta não é conhecer as soluções e arquitetura de rede do Oracle Server, e sim, solucionar os principais problemas de TNS. Mas, antes devemos conhecer três arquivos que compõe uma arquitetura de rede Oracle e importantes para iniciar a nossa jornada, são eles:

Listener.ora

Arquivo de configuração de ouvinte no lado do servidor. Ele fornece as principais configurações como:

• Nome único do banco de dados (Unique Name)
• Protocolo de Comunicação e porta de acesso
• Serviços do Listener e Home Oracle associado a versão da base.

A origem desse arquivo é sempre em $ORACLE_HOME/network/admin em Unix\Linux e %ORACLE_HOME%\network\admin para Windows, onde ORACLE_HOME é a origem da sua instalação do Oracle Server. Abaixo segue um modelo simples do Listener.ora.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.72.0.171)(PORT = 1521))
      )
     )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ranet.world)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = ranet)
    )
  )

LEMBRETE!

Esse arquivo é geralmente encontrado no servidor que está instalado o banco de dados e não na máquina cliente. E resumidamente, é ele que fornece o suporte de conexão ao banco de dados.

Tnsnames.ora

Arquivo de configuração para acesso aos bancos de dados Oracle, configurado tanto no lado cliente ou servidor. Ele fornece as informações de destino dos banco de dados, como:

• Nome do banco de dados (SID);
• Tipo de Protocolo, Nome do servidor e Porta de conexão.

Abaixo segue um exemplo do contéudo de um arquivo Client:

Podemos encontrar o arquivo no diretório  $ORACLE_HOME\network\admin em Unix\Linux e %ORACLE_HOME%/network/admin em Windows da máquina cliente ou servidor, onde a instalação do Oracle Home pode ser de um simples Client Oracle ou Oracle Server.

ranet.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.72.0.171)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ranet)
 )
  )

LEMBRETE!

O arquivo tnsnames.ora deve ser configurado no lado cliente e servidor de banco de dados e aplicação, no lado cliente para permitir o usuário acessar o banco de dados, nos servidores de banco de dados para validar a utilização de DBLINKs e nos servidores de aplicação para permitir a conectividade.

Sqlnet.ora

Arquivo de configuração que habilita alguns recursos de rede para o cliente ou banco de dados, como:

• Tipo de nomeação de metódos;
• Habilita logs e traces;
• Recursos avançados de segurança e entre outros.

Assim como os outros arquivos, pode ser encontrado em $ORACLE_HOME\network\admin em Unix\Linux e %ORACLE_HOME%/network/admin em Windows da máquina cliente ou servidor.

Bom, depois de algumas suaves explicações dos arquivos de configuração, vamos discutir um check list antes de entrar nos problemas de TNS, esse check list tem como funcionalidade encontrar os possíveis problemas antes de realizar qualquer alteração nos arquivos do SQL*NET mencionados acima, o check list consiste nas seguintes tarefas:

PING

Antes de qualquer coisa, faça um ping para o IP do servidor e veja se ele está respondendo na rede, como o exemplo abaixo:

C:\>ping 10.72.0.171

Disparando contra 10.72.0.171 com 32 bytes de dados:
Resposta de 10.72.0.171: bytes=32 tempo=81ms TTL=60
Resposta de 10.72.0.171: bytes=32 tempo=70ms TTL=60
Resposta de 10.72.0.171: bytes=32 tempo=137ms TTL=60
Resposta de 10.72.0.171: bytes=32 tempo=96ms TTL=60
Estatísticas do Ping para 10.72.0.171:
Pacotes: Enviados = 4, Recebidos = 4, Perdidos = 0 (0% de perda),
Aproximar um número redondo de vezes em milissegundos:
Mínimo = 70ms, Máximo = 137ms, Média = 96ms

Ter a resposta do servidor já é um bom começo, caso não tenha, existe alguns possíveis problemas:

1. O servidor está com outro endereço IP;
2. O cabe de rede não está conectado ao servidor;
3. O servidor pode estar bloqueado no Firewall;
4. Verificar as regras de firewall do seu usuário para o servidor específico;
5. Para ambientes distribuídos, verifique se o link entre as unidades está ativo;
6. Verificar se a(s) placa(s) de rede do servidor estão habilitadas;
7. Verificar se o servidor está no domínio desejado ou em uma DMZ, se sim, verificar se possui acesso.

Todos os problemas mencionados acima, se trata diretamente da infra-estrutura e configuração do servidor em questão, portanto, não tem que realizar nenhum tipo de configuração ou manutenção no seu ambiente Oracle.

TNSPING

O SQL*NET fornece um aplicativo chamado TNSPING, que pode ser executado diretamente no servidor ou da máquina cliente, ele é utilizado para determinar se o Listener do banco de dados alvo está ativo ou não. Pois, em alguns momentos, podemos ter uma base com o status OPEN (online), porém, o seu serviço de Listener esta parado, deste modo não permite acesso dos usuários. Veja um exemplo:

C:\>tnsping ranet

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 – Production on 15-JAN-2009 10:42:10
Copyright (c) 1997,  2007, Oracle.  All rights reserved.
Arquivos de parâmetros usados:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Usado o adaptador TNSNAMES para resolver o apelido
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = 10.72.0.171) (Port = 1521)) (
ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = 10.72.0.171) (Port = 1521))) (CONNECT_DATA = (SID = ranet)))
OK (80 ms)

Diferente do PING, o TNSPING já faz um teste do serviço de conexão diretamente no banco de dados desejado, no exemplo, utilizei o alías RANET para o banco de dados RANET, que é o alías configurado em meu tnsnames.ora, a mensagem final de OK diz que é possível realizar a conexão com o banco de dados.

Mas para ter do sucesso de conexão para seu banco de dados, tudo vai depender do status que a sua instância se encontra, pois se o banco de dados estiver em NOMOUNT (não mountado) ou MOUNT (montado), o teste de conexão com o TNSPING irá funcionar, porém, o acesso não será possível, assim sendo, quado acessar à aplicação ou SQL*PLUS, terá um erro como o abaixo:

C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 – Production on Qui Jan 15 13:43:16 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
SQL> conn system@ranet
Informe a senha:
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Isso é porque o seu banco de dados se encontra em status NOMOUNT ou MOUNT, ou também como a mensagem diz, em processo de SHUTDOWN em modo  immediate, normal ou transacional.

Utilizando ainda o TNSPING, é possível medir o tempo de resposta do serviço de Listener, colocando um valor N para o teste, conforme o exemplo:

C:\>tnsping ranet 5

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 – Production on 15-JAN-2009 10:49:21
Copyright (c) 1997,  2007, Oracle.  All rights reserved.
Arquivos de parâmetros usados:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Usado o adaptador TNSNAMES para resolver o apelido
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = 10.72.0.171) (Port = 1521)) (
ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = 10.72.0.171) (Port = 1521))) (CONNECT_DATA = (SID = ranet)))
OK (50 ms)
OK (60 ms)
OK (60 ms)
OK (80 ms)
OK (80 ms)

Acima, foi realizado 5 tentativas, ou como alguns preferem, 5 saltos. Com essa opção do TNSPING é fornecida para cada salto o tempo de resposta da máquina origem para o banco de dados destino, e com isso também podemos entrar problemas de performance, caso o tempo de resposta esteja alto de um valor tolerado, que é de 0 a 200 ms (milisegundos), pode começar a verificar alguns problemas de rede.

Atráves do TNSPING, começamos a receber alguns erros de TNS, que podemos discutir abaixo:

TNS-03505

Esse erro acontece geralmente quando se tenta realizar um teste de conexão (TNSPING) para um banco de dados que não está registrado no tnsnames.ora da máquina cliente ou servidor, exemplo:

C:\>tnsping ranet99

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 – Production on 15-JAN-2009 10:57:52
Copyright (c) 1997,  2007, Oracle.  All rights reserved.
Arquivos de parâmetros usados:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
TNS-03505: Falha ao determinar o nome

Solução

Existem algumas possíveis soluções simples para esse erro específico de TNS, que são:

• Colocar o nome correto do banco de dados;
• Verifique se o alías que está tentando conecta está cadastrado no seu arquivo tnsnames.ora;
• Verifique se o alías necessita utilizar o .WORLD ao final do nome, o .WORLD significa que é de um domínio global, e sua configuração pode ser feita alterando o nome do alías no tnsnames.ora para RANET.WORLD;
• Talvez seja necessário colocar o parâmetro NAME.DEFAULT_ZONE = WORLD no arquivo sqlnet.ora;
• Verifique o tipo de nomeação utilizado pelo parâmetro NAMES.DIRECTORY_PATH.

TNS-12541

Esse erro é retornado quando o serviço do Listener não está online, ou está com outro tipo de configuração, exemplo:

C:\>tnsping ranet

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 – Production on 15-JAN-2009 11:09:24

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Arquivos de parâmetros usados:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Usado o adaptador TNSNAMES para resolver o apelido
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = 10.72.0.171) (Port = 1521))) (CONNECT_DATA = (SID = ranet)))
TNS-12541: TNS:nâo há listener

Solução

Nos casos mais simples, para resolver esse problema basta ativar novamente o serviço do Listener no banco de dados alvo, para isso, utiliza a ferramenta LSNRCTL (Listener Control) fornecido junto com o SQL*NET, atráves de linha de comando para habilitar novamente o serviço, exemplo:

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 – Production on 15-JAN-2009 11:13:41

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Bem vindo ao LSNRCTL, digite “help” para obter informações.

LSNRCTL> start LISTENER
Starting D:\oracle\product\10.2.0\db_1\bin\tnslsnr: please wait…

TNSLSNR for Windows: Version 10.2.0.4.0 – Production
System parameter file is D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to D:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.72.0.171)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.72.0.171)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Windows: Version 10.2.0.4.0 – Production
Start Date                15-JAN-2009 11:10:26
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File         D:\oracle\product\10.2.0\db_1\network\admin\listener.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.72.0.171)(PORT=1521)))
Services Summary…
Service “ranet” has 1 instance(s).
  Instance “ranet”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

Perceba que ao acessar o aplicativo LSNRCTL (Listener Control), emiti o comando start LISTENER, para iniciar o serviço do Listener, por padrão o nome do ouvinte é LISTENER, então eu poderia apenas deixar start que resolveria, porém, se coloquei outro nome ao LISTENER, basta iniciar com start <nome_do_listener>, e para saber se existe outro nome de Listener configurado no seu SQL*NET, basta verificar o seu arquivo listener.ora do servidor de banco de dados.

Existem outros checks que devem ser feitos para evitar esse tipo de erro, são:

• Verificar o hostname e porta do arquivo listener.ora;
• Verificar o nome do Listener atual no LSNRCTL;

ORA-12154

O mesmo que o erro TNS-12154, onde não há um Listener para conexão, porém, ele é emitido dentro do banco de dados Oracle, por isso o motivo do ORA no início, e esse erro é quando se está utilizando um DBLINK para um determinado banco de dados e o ALIAS utilizado no DBLINK não corresponde ao banco de dados. Exemplo:

select count(*) from NOTAS_FISCAIS@RANET_SP.WORLD;

select count(*) from NOTAS_FISCAIS@RANET_SP.WORLD
*
ERRO na linha 1:
ORA-12154: TNS:could not resolve service name

Solução

A solução para esse tipo de problema é igual ao do TNS-12154, porém necessita de mais detalhes, como:

Verificar qual o alías que o DBLINK está utilizando para realizar a comunicação, fazendo o SELECT abaixo:

select owner, db_link, username, host from dba_db_links;

OWNER      DB_LINK         USERNAME                       HOST
———- ————— —————————— —————
PUBLIC     RANET_SP.WORLD  dbara                          RANET_RO

1 linha selecionada

A coluna HOST fornece o nome do alías que será utilizado para realizar a comunicação com outro banco de dados, esse alías é o mesmo encontrado no arquivo tnsnames.ora, então, para resolver o problema, basta no arquivo de tnsnames colocar um alías chamado RANET_RO para o hostname, porta e SID corretos, deste modo o DBLINK volta a funcionar. Existem outras coisas que devemos prestar atenção como mencionado nos erros TNS acima, como:

• Verificar se é necessário utilizar o .WORLD para o domínio global;
• Verificar o NAMES.DIRECTORY_PATH e NAME.DEFAULT_DOMAIN do sqlnet.ora do servidor de banco de dados;
• Executar os testes de PING e TNSPING.

PRONTO! Com essa introdução aos erros de TNS e um pequeno overview dos principais arquivos do SQL*NET, já conseguimos realizar alguns testes simples e que podem nos ajudar e  nosso dia-a-dia de trabalho, lembrando que esse foi somente a primeira parte, terá a segunda parte com mais alguns tipos de erros TNS e suas respectivas soluções.

Abraços,

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:

1. Realizar um backup full do banco de dados.
2. Parar os serviços do Listener, exemplo: lsnrctl stop ou lsnrctl <nome_listener> stop;
3. Parar o banco de dados, com shutdown immediate, normal ou transactional.;
4. Para ambiente Windows: Depois que descer o banco de dados pelo SQL*PLUS, descer o serviço do windows, exemplo: net stop OracleService<nome_da_base>;
5. Anotar o horário de STOP GERAL, para saber com exatidão o momento da parada de todos os serviços;
6. Alterar o horário do servidor (Windows\Linux\Unix);
7. 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.
8. 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.

Abraços,