Oracle connection errors



In this post i´m going to try to describe some connection problems with oracle database. First of all, you should know that there are two main configuration files involved on that:
tnsnames.ora --> This file is located on client machine and has necessary information to connect to an oracle database trough a Listener.
listener.ora --> This file is in the server side and has a description of a process called listener that is running on server machine listening for new connections to a database.
both files are located on $ORACLE_HOME/network/admin directory in their respective servers.

TNS-03505: Failed to resolve name

This problem is due $ORACLE_HOME/network/admin/tnsnames.ora file do not has an entry for orcltest connection identifier as you can see:

[oracle@wcp12cr2 admin]$ tnsping orcltest

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 05:15:52

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

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

As you can see below, there is no entry for orcltest in $ORACLE_HOME/network/admin/tnsnames.ora file:


[oracle@wcp12cr2 admin]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLSTB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclstb)
    )
  )

in our tnsnames.ora are two connections for ORCL and ORCLSTB (in red) as you can see, but no one for orcltest

TNS-12543: TNS:destination host unreachable


In this case, ORCLSTB connection exist in our tnsnames.ora, but host 192.168.56.100 is unreachable from this machine,

[oracle@wcp12cr2 admin]$ tnsping ORCLSTB

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 05:17:08

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

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstb)))
TNS-12543: TNS:destination host unreachable

to check this you can make a ping to this server as you can see:

[oracle@wcp12cr2 admin]$ ping 192.168.56.100
PING 192.168.56.100 (192.168.56.100) 56(84) bytes of data.
From 192.168.56.101 icmp_seq=2 Destination Host Unreachable
From 192.168.56.101 icmp_seq=3 Destination Host Unreachable
From 192.168.56.101 icmp_seq=4 Destination Host Unreachable
^C
--- 192.168.56.100 ping statistics ---
4 packets transmitted, 0 received, +3 errors, 100% packet loss, time 3600ms
pipe 3



TNS-12541: TNS:no listener

In this example, an entry in our tnsnames.ora exist, server is reachable, but no listener in IP 192.168.56.100 and port 1521 is started:

[oracle@wcp12cr2 admin]$  tnsping ORCLSTB

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 05:29:45

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

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstb)))
TNS-12541: TNS:no listener

[oracle@wcp12cr2 admin]$ ping 192.168.56.100
PING 192.168.56.100 (192.168.56.100) 56(84) bytes of data.
64 bytes from 192.168.56.100: icmp_seq=1 ttl=64 time=0.407 ms
64 bytes from 192.168.56.100: icmp_seq=2 ttl=64 time=0.937 ms
^C
--- 192.168.56.100 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1498ms


To verify this you can do a 
telnet 192.168.56.100 1521
or check in the destination machine if listener is running in select IP:PORT as follows:

I firt check in the destination machine that a listner called LISTENER is running on destination machine:
[oracle@wcp12cr2_clone admin]$ ps -ef | grep tns
root        15     2  0 05:20 ?        00:00:00 [netns]
oracle    2315     1  0 05:42 ?        00:00:00 /oracle/db/ohome/bin/tnslsnr LISTENER -inherit
oracle    2327  2184  0 05:46 pts/0    00:00:00 grep tns

and with this command we can check Ip and port in which LISTENER is listening:

[oracle@wcp12cr2_clone admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 05:53:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                30-OCT-2017 05:42:13
Uptime                    0 days 0 hr. 11 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/db/ohome/network/admin/listener.ora
Listener Log File         /oracle/db/diag/tnslsnr/wcp12cr2_clone/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.100)(PORT=1522)))
Services Summary...
Service "orclstb" has 1 instance(s).
  Instance "orclstb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

So as you can see, this is listening in the correct IP, but port in which database is listening is 1522 instead of 1521 as we have setup in our tnsnames.ora

ORA-12514: TNS:listener does not currently know of service requested in connect


In this example tnsping works properly but when you try to connect trough sqlplus it gives you an error : ORA-12514, it is due to service name described in tnsnames.ora client machine do not match with service name described in the listener.ora database server machine

[oracle@wcp12cr2 admin]$ tnsping ORCLSTB

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 06:03:23

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

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@wcp12cr2 admin]$
[oracle@wcp12cr2 admin]$
[oracle@wcp12cr2 admin]$
[oracle@wcp12cr2 admin]$ sqlplus scott/tiger@ORCLSTB

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 30 06:05:11 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

In this example SERVICE_NAME = orcl in client machine, but on server machine SERVICE_NAME = orclstb as you can see below:

[oracle@wcp12cr2_clone admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 06:10:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                30-OCT-2017 06:02:37
Uptime                    0 days 0 hr. 7 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/db/ohome/network/admin/listener.ora
Listener Log File         /oracle/db/diag/tnslsnr/wcp12cr2_clone/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.100)(PORT=1521)))
Services Summary...
Service "orclstb" has 1 instance(s).
  Instance "orclstb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


ORA-01034: ORACLE not available
In this exmple tnsping works properly but when you try to connect torugh sqlplus it gives you an error : ORA-01034, it is due database is not started on destination host but listener is started...

[oracle@wcp12cr2 admin]$ tnsping ORCLSTB

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 06:12:30

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

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstb)))
OK (10 msec)
[oracle@wcp12cr2 admin]$ sqlplus scott/tiger@ORCLSTB

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 30 06:12:38 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0


ORA-28000: the account is locked

This is due that user is locked at database level

[oracle@wcp12cr2 admin]$ sqlplus scott/tiger@ORCLSTB

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 30 06:16:11 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-28000: the account is locked


SQL> select USERNAME, ACCOUNT_STATUS from dba_users where username like 'SCOTT';

USERNAME                                 ACCOUNT_STATUS
---------------------------------------- --------------------------------
SCOTT                                    LOCKED


ORA-01017: invalid username/password; logon denied

This is as you can deduce, the password for this user is wrong:

[oracle@wcp12cr2 admin]$  sqlplus scott/ti@ORCLSTB

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 30 06:19:08 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more