Today we will learn how to resolve ORA-12170 Error while using DB Link. Let’s go through this in detail :
Table of Contents
Problem : ORA-12170 Error
SQL> connect TEST/XXXXXXXX;
Connected.
SQL> select sysdate from dual@TEST_LINK;
select sysdate from dual@TEST_LINK
ERROR at line 1:
ORA-12170: TNS:Connect timeout occurred
Resolution :
Step 1 : Check the db_link details for which we are getting timeout error:
Issue the below Query to find the details :
select owner,db_link,username,host,created from dba_db_links where db_link='TEST_LINK'; OWNER DB_LINK USERNAME HOST CREATED ---------- --------------- --------------- ------------------------------------------ TEST TEST_LINK JOHN 10.121.192.6:1567/DEVDB_AL 25-DEC-2019 09:00:32
Also, check if this is getting timeout for the same tns entry. Here it is trying to connect DEVDB where ‘10.121.192.6:1567/DEVDB_AL’ is the alias name here:
SQL> !tnsping 10.121.192.6:1567/DEVDB_AL
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2024 07:49:15
Copyright (c) 1997, 2023, Oracle. All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DEVDB_AL))(ADDRESS=(PROTOCOL=tcp)(HOST=10.121.192.6)(PORT=1567)))
TNS-12535: TNS:operation timed out
Step 2: Need to check for any recent change or if DB Link wrongly created
You need to check if recently any migration or activity has happened or not, or if the DB Link is wrongly created or any other activity was there. In my case, an activity was there and since then it was getting timed out, so I have dropped and recreated the DB Link with the new IP details where it has been migrated :
Check the details first and accordingly you need to take action. Here I have taken action below as per the reason :
SQL> connect TEST/XXXXXXXX; Connected. SQL> drop database link TEST_LINK; database link dropped SQL> create database link TEST_LINK connect to JOHN identifioed by john using '10.7.150.19:1567/DEVDB_AL';----->New IP Details database link created SQL> select sysdate from dual@TEST_LINK; SYSDATE ------------------- 28-NOV-2024 03:34:00
Test tnsping with the new IP as well. It will work fine.
SQL> !tnsping 10.7.150.19:1567/DEVDB_AL
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2024 08:25:46
Copyright (c) 1997, 2023, Oracle. All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DEVDB_AL))(ADDRESS=(PROTOCOL=tcp)(HOST=10.7.150.19)(PORT=1567)))
OK (0 msec)
Other Scenarios :
Also, this could happen because of any Network changes or Firewall Issue too. In that case, you need to contact the Network Team and Fix this issue with help of them.
Hope this helps to resolve ORA-12170 error. Also, for further reference, you can follow the Oracle Doc ID 2794952.1.
Similar ORA Error Related Articles :
