1

I have an Oracle RDS instance running on AWS and I need to access the tnsnames.ora file and check its configuration since I am getting this error

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

which is directly connected these two following resolutions

the problem is that AWS does NOT allow remote access to the Oracle RDS and I cannot find anything in the AWS console which would help me change or even access the contents of the tnsnames.ora.

Can I maybe do it using SQL? I'm at my wits end here... This error does not even seem to be consistent since for example some queries do work....

/// Adding more info after reading some comments:

Queries like the following are successfully executed:

SELECT COUNT(*) FROM (

SELECT
  DISTINCT tab.column_a, tab.column_a AS L0, tab.column_a AS L1, tab.column_a AS L2
FROM
  ADMIN.table_something tab
WHERE
  tab.column_a IS NOT NULL 

)

the ones that fail look like this

SELECT
  DISTINCT tab.column_a, tab.column_a AS L0, tab.column_a AS L1, tab.column_a AS L2
FROM
  ADMIN.table_something tab
WHERE
  tab.column_a IS NOT NULL

and by fail I mean producing the error I mentioned above. Executing these from a SQL command prompt produces no errors.

I am using this jdbc URL: 

jdbc:oracle:thin:@<host_URL>:1521:SID

I also tried using this format

jdbc:oracle:thin:@<host_URL>:1521/ServiceNames

But apparently the AWS RDS instance is not configured with service names. Most of the answers I've found refer to the tnsnames.ora as the place where the service names and sids are configured and therefore includes information on how to actually form a connection string. This is why I want to access it and if I find something suspicious then modify it.

Improve this question
5
  • Tell us what throws the ORA-12505 error and why do you think changing tnsnames.ora will solve that.
    – Balazs Papp
    Feb 14, 2020 at 17:21
  • @BalazsPapp I updated the question with the information you asked. Thanks for your comment.
    – Niko
    Feb 16, 2020 at 12:24
  • Is it java program or third party ide?
    – user168186
    Feb 17, 2020 at 3:56
  • @Kumar it is a Scala application build with Play framework.
    – Niko
    Feb 17, 2020 at 8:40
  • If executing from sqlplus has no problem implies that the problem lies with scala application and the error is most likely due to malformed jdbc url.Can you post result of tnsping?.
    – user153556
    Feb 17, 2020 at 15:45

1 Answer 1

Reset to default
-1

Please refer to section "Configuring tnsnames.ora" on the below article.

https://aws.amazon.com/blogs/database/configuring-and-using-oracle-connection-manager-on-amazon-ec2-for-amazon-rds-for-oracle/#:~:text=ora%20or%20tnsnames.,in%20Amazon%20RDS%20for%20Oracle.

Improve this answer
2
  • Please don't create link-only answers. They break and become useless if/when the address changes or disappears.
    – Colin 't Hart
    Sep 7, 2021 at 9:16
  • Please add further details to expand on your answer, such as working code or documentation citations.
    – Community Bot
    Sep 7, 2021 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.