- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Unable to connect using Oracle database and ODBC
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
09-15-2016 07:24 AM
Hi team,
i have access to an oracle database but for some reason I cannot access the database and keep getting error message stating "Oralce: ORA-12541: TNS: No Listener". I have tried the following as stated other knowledge base
1. Update the tnsname file
2. Update the host information in listener and tnsname
3. uninstall and reinstall the oracle instant client
4. updated the environment variables
Having said the above, I went to verify if my connection from the ODBC driver works fine and I'm able to connect successfully but cannot connect via Data > New Query > Database > Oracle Database and the same applies for ODBC.
Please advise....A!
Solved! Go to Solution.
Accepted Solutions
Re: Unable to connect using Oracle database and ODBC
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-15-2016 01:04 PM
@v-yuezhe-msft Apologies for getting back so late but I found a solution for this to work.
New Query > connect to DB > Oracle Database > in the servername the syntax to use is "hostname:[port number] /SID" which I was using only as host ID. After this update, it all worked fine.
Thanks again for the help.
Kind regards,
Anand
All Replies
Re: Unable to connect using Oracle database and ODBC
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
09-15-2016 08:37 PM - edited 09-15-2016 08:37 PM
Hi @ranand1482,
Do you use Power BI Desktop to connect to remote Oracle database? If that is the case, there are some proposals for you troubleshooting this issue.
1. Make sure you have installed the latest version of Oracle client following the instructions in this article. Ensure that your Oracle client, Oracle Server and Power BI Desktop have the same bitness(32bit or 64bit).
2. Make sure the Host IP you update in listener.ora is correct, you can use ipconfig command to verify the Host IP.
3. Restart LISTENER with the following commands.
lsnrctl stop
lsnrctl start
4. Use TNSPING to test the connectivity from Desktop to Oracle server.
5. When connecting to Oracle from Power BI desktop, enter the connection details in TNSnames.ora file, then check if it is successful. Alternatively, you can connect to the Oracle Database via EZConnect connection string. For more details, please review this similar blog.
Thanks,
Lydia Zhang
Re: Unable to connect using Oracle database and ODBC
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
09-22-2016 10:49 AM - edited 09-22-2016 11:03 AM
Hi Lydia,
I have tried the following and still have the same issue
1. manually updated the listener.ora file with the host information (available used based on cmd > hostname)
2. deleted and re-configured listener.ora using the net assistant part of Oracle home
3. I've both Power BI for desktop and the Oracle server running 64-bit
4. tried to run the commands lsnrctl > start or services but i get message as mentioned below
TNS-12560: TNS: protocol adapter error
TNS-00530: Protocol adapter error
Not sure what am I doing incorrect here. Please advice.
Kind regards,
Anand
Re: Unable to connect using Oracle database and ODBC
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
09-27-2016 04:52 AM
4. tried to run the commands lsnrctl > start or services but i get message as mentioned below
TNS-12560: TNS: protocol adapter error
TNS-00530: Protocol adapter error
Hi @ranand1482,
About the above error message, I would recommend you post it in Oracle forum to get dedicated support.
Thanks,
Lydia Zhang
Re: Unable to connect using Oracle database and ODBC
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-15-2016 01:04 PM
@v-yuezhe-msft Apologies for getting back so late but I found a solution for this to work.
New Query > connect to DB > Oracle Database > in the servername the syntax to use is "hostname:[port number] /SID" which I was using only as host ID. After this update, it all worked fine.
Thanks again for the help.
Kind regards,
Anand
Re: Unable to connect using Oracle database and ODBC
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-19-2017 08:54 AM
I was having the same issue and could not work out what was wrong...this change allowed me to connect fine;
hostname:[port number] /SID
THANK YOU!!
Re: Unable to connect using Oracle database and ODBC
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-21-2017 07:25 AM
Thank you very much. This worked for me as well. One update I would make is that for me "hostname:[port number] /SID" didn't work, but "hostname:[port number] /[SERVICE_NAME]" is what worked. I had tried pulling the SID from the listener.ora on the server but still couldn't get connected. When I used the "[SERVICE_NAME]" it connected right away. Thanks again!
Re: Unable to connect using Oracle database and ODBC
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-15-2017 01:24 PM
Kind of a necromancer reply. But let's say that I work in an environment where I have 32 bit oracle instances holding some data, and 64 bit oracle instances holding other data. Do I need two installs of Power BI to get this to go? What I want to blend the data?