Configuring Listener can be a tough job on a rare day & frustrating enough to pull your hair out. Especially when you are facing the below errors.
ORA-12170: TNS:Connect timeout occurred
OR
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oravm19c.practice.com)(PORT=1521)))
TNS-12535: TNS:operation timed out
TNS-12560: TNS:protocol adapter error
TNS-00505: Operation timed out
You have crosschecked LISTENER.ORA & TNSNAMES.ORA multiple times, everything seems to be in order and still can't figure out what's wrong. This post is for you.
Without wasting time, I am making pointers (with examples) below to check 1 more time & I am sure that you will find something in 1 of them.
1) See if the hostname is configured well.
[oracle@oravm19c admin]$ hostname
oravm19c.practice.com
[oracle@oravm19c admin]$ cat /etc/sysconfig/network
# Created by anaconda
# oracle-database-preinstall-19c : Add NOZEROCONF=yes
NOZEROCONF=yes
I am not having any entry in network file but you might have. It's good to check.
2) See if the IP is assigned properly and is reachable.
[oracle@oravm19c admin]$ ifconfig enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.29.67 netmask 255.255.255.0 broadcast 192.168.29.255 inet6 2405:201:4022:c0ce:d4fd:9df5:c923:bdd5 prefixlen 64 scopeid 0x0<global> inet6 fe80::aad7:43a4:2fe1:4340 prefixlen 64 scopeid 0x20<link> ether 08:00:27:28:05:a6 txqueuelen 1000 (Ethernet) RX packets 2533 bytes 251486 (245.5 KiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 1918 bytes 188812 (184.3 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 [oracle@oravm19c admin]$ ping 192.168.29.67 PING 192.168.29.67 (192.168.29.67) 56(84) bytes of data. 64 bytes from 192.168.29.67: icmp_seq=1 ttl=64 time=0.030 ms 64 bytes from 192.168.29.67: icmp_seq=2 ttl=64 time=0.048 ms 64 bytes from 192.168.29.67: icmp_seq=3 ttl=64 time=0.035 ms 64 bytes from 192.168.29.67: icmp_seq=4 ttl=64 time=0.035 ms 64 bytes from 192.168.29.67: icmp_seq=5 ttl=64 time=0.046 ms 64 bytes from 192.168.29.67: icmp_seq=6 ttl=64 time=0.200 ms ^C --- 192.168.29.67 ping statistics --- 6 packets transmitted, 6 received, 0% packet loss, time 5125ms rtt min/avg/max/mdev = 0.030/0.065/0.200/0.061 ms
3) See if you have configured your HOSTS file properly with IP entry and Fully Qualified Hostname.
[oracle@oravm19c admin]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.29.67 oravm19c.practice.com oravm19c
4) See if you are able to ping your hostname and it's responding well to assigned IP.
[oracle@oravm19c admin]$ ping oravm19c.practice.com
PING oravm19c.practice.com (192.168.29.67) 56(84) bytes of data.
64 bytes from oravm19c.practice.com (192.168.29.67): icmp_seq=1 ttl=64 time=0.030 ms
64 bytes from oravm19c.practice.com (192.168.29.67): icmp_seq=2 ttl=64 time=0.034 ms
64 bytes from oravm19c.practice.com (192.168.29.67): icmp_seq=3 ttl=64 time=0.031 ms
64 bytes from oravm19c.practice.com (192.168.29.67): icmp_seq=4 ttl=64 time=0.030 ms
^C
--- oravm19c.practice.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3081ms
rtt min/avg/max/mdev = 0.030/0.031/0.034/0.004 ms
5) See if you have added needed SID entries in LISTENER.ORA
[oracle@oravm19c admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdb1)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = cdb1)
)
)
6) See if you have needed entries in tnsnames.ora
[oracle@oravm19c admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = oravm19c.practice.com)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oravm19c.practice.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
7) In 5) or 6), crosscheck for any typo errors, if all seems good, reload your listener and it should show the instance status as ready if your instance is up and running OR it will show UNKNOWN till you start it.
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1.practice.com" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB.practice.com" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
8) Connect to your database and it should be successful now.
[oracle@oravm19c admin]$ sqlplus sys@cdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 13 12:06:17 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name, open_mode from V$Database;
NAME OPEN_MODE
--------- --------------------
CDB1 READ WRITE
SQL>
9) If still not working at this point after all the above checks, I'll recommend looking in listener log and hopefully you'll get the idea to what's wrong.
I hope this helps !!
No comments:
Post a Comment