I am trying to create an Oracle connection string in Excel VBA. After lots of research, I have a working connection string:

Set Oracle_conn = New ADODB.Connection
Oracle_conn.Open "ODBC; Driver='Oracle in instantclient_19_9'; Data Source='Data Mart'; UserID=" & ID & "; Password=" & Password & ";"

However, this only works when my ODBC Data Source is a **User** DSN. When the ODBC Data Source is a **System** DSN, I end up with: "[Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied" The reason I am using **System** DSN is because my code runs from a shared PC, so System DSN works for all users without each individual user needing to create a **User** DSN. Any advice on how to tweak my code such that it will run with a **System** DSN would be greatly appreciated. Thank you. Edit 1: System DSN connects successfully if I manually go to Data ribbon > Get Data > From Other Sources > From ODBC Edit 2: I still haven't been able to figure out how to connect to a System DSN with VBA code. Maybe it's a bug with the ODBC driver? I realize this is a bit of an obscure question, but I'm still hopeful someone can help me connect. Thank you!

I installed Oracle Linux 21c on the remote machine, tested it in Linux shell, and it worked as expected (I can query the testing table I created). But I am stuck when trying to connect to the database from local SQL Developer client (Version 22.2.1.234). I downloaded 3 ODBC-related packages - instantclient-basic-windows.x64-21.7.0.0.0dbru.zip - instantclient-sdk-windows.x64-21.7.0.0.0dbru.zip - instantclient-odbc-windows.x64-21.7.0.0.0dbru.zip. Above were unzipped and put in C:\oracle\instantclient_21_7. I also set up the DSN following https://windowsreport.com/install-oracle-odbc-driver-windows-10/ . On my laptop (Windows 10), I found ORACLE_HOME location and then created the TNSNAMES.ORA file following https://www.databasestar.com/tnsnames-ora-file/ . I launched SQL Developer, but the Database Connection panel showed: *No TNS entries found in {USER Home dir=C:\Users\xxxx, TNS_ADMIN=C:\oracle\instantclient_21_7, ORACLE_HOME=C:\Users\xxxx\AppData\Local\Temp\Temp2_WINDOWS.X64_193000_db_home.zip\network\admin}*. I tried to ignore above warning and created an Oracle Connection (Connection Type = TNS; Network Alias = net_service_name), clicked Test button, and I got an error: *Status: Failed: IO Error: The Network Adaptor could not establish the connection…* I couldn’t figure out what went wrong. Can anyone help please? Thanks in advance.

I am using Oracle 10g Cluster. So, the listener on 1 Oracle machine hasn't been working for a week now. I get an error:

ORA-12541: TNS:no listener

When I want to start listener from oracle user, listener started without services.

lsnrctl status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 08-NOV-2021 11:05:53

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                02-NOV-2021 09:12:26
Uptime                    6 days 1 hr. 53 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/10.2.0/db/network/admin/listener.ora
Listener Log File         /oracle/10.2.0/db/network/log/listener_amrac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amrac1)(PORT=1521)))
The listener supports no services
The command completed successfully

But database not working. I am not changed IP, names of machines, etc. Please help resolve issue.

> I'm using Oracle 12c in Debian 8 (on a vm) > > This question is for educational purposes only. I'm not using any production servers, so anything that you can tell me won't have consecuences To start with, I found this link to delete registers in tnsnames.ora. I didn't test it yet because first, I needed to know if there were active connections in the database using the information of the tnsnames.ora. I'm asking here because I found no way of doing this, but it may be possible to do it. For you to know, I'm using this command for connecting to the database, so that I clearly specify a tnsname

rlwrap sqlplus sys as sysdba@tnstest

The contents of my tnsnames.ora are the following

tnstest =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

But, if I want to be 100% sure that this is working, I use this command

tnsping tnstest

Which results are...

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 23:51:38

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

Used parameter files:
/opt/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

After all of this I know that my configuration is working, I'm using a register in tnsnames.ora for the connection, but I don't know a way of checking... "Hey, now that I'm connected, I want to know which register I used from the tnsnames file for connecting, if I used that file of course" Is there any possibility that I could get this information? Well, my following attempts of trying to figure this out, were checking the v$session view, if there was any field that I could use to get this information, but I couldn't find one. After all of this, ultimately, I came here for asking. I don't know what else to try nor I have more ideas of what should I do next. > P.S: there's a chance that this problem is impossible to solve, because this is task I'm doing due to I'm a student of sysadmin, and our teacher intentionally added unsolvable questions. Even so, I still think there might be a solution for this particular case

Database version: Oracle 11gR2 We want to use DYNAMIC_REGISTRATION_LISTENER parameter with off option. Our listener.ora file like this: LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = oracle) ) ) ADR_BASE_LISTENER = /u01/app/oracle DYNAMIC_REGISTRATION_LISTENER = OFF But when we use this parameter, our web service request cannot connect to the database. SQL> SELECT dbms_xdb.gethttpport FROM dual; GETHTTPPORT ----------- 3010 **Without this parameter:** Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 07-FEB-2021 03:21:59 Uptime 0 days 0 hr. 2 min. 51 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=3010))(Presentation=HTTP)(Session=RAW)) **With this parameter:** Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 07-FEB-2021 03:21:59 Uptime 0 days 0 hr. 2 min. 51 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) We need to use this parameter. Web services also need to be connected. How is that possible? I could not find any clear solution. Best regards,

Listener and service are up and running. In SQL Plus, if I connect with no login, and execute '**conn sys/pwd@host:port/sid as sysdba**', connection is established successfully. But if I connect **/ as sysdba** or with **username and password** , it always **throws ORA-12560: TNS:protocol adapter error**. What are the possible reasons for this ?

I am trying to connect to a particular TNS name; in my tnsnames.ora file I can see that it is defined as being a TCP connection to foo-scan on port 39000. I can tnsping this name OK. I can successfully telnet foo-scan 39000 but when I try to connect in e.g. sqldeveloper I get the error ora-12545: connect failed because target host or object does not exist According to google it is likely that the listener on foo-scan is actually trying to forward my client to bar-vip, which no doubt I cannot connect to. Also according to google there is a utility called trcroute that will debug this issue and show me exactly where my client is trying to connect and what step is actually failing. But I cannot find this utility anywhere.

I think it's not reliable because those ports are used for FTP, SSH and SFTP but I am not sure that's the correct response in respect for Oracle DBA

I have recently installed Oracle XE 11 on my local machine and then installed SQL Developer. But when I try to create a new connection (**for my localhost**) or add from 'database detected local XE' it returns either **ora-12514** or **ora-12505**. I know the password that I entered during the installation. - OracleXETnsListener,OracleServiceXE service is running - Tried connecting via localhost, 127.0.0.1 and computer_name - Opened localhost tns and wrote the information there But I am still not able to connect. It bugs me that even a simple **localhost** connection is problematic when it comes to Oracle tools. It should be as easy as using Microsoft tools. I can't even connect to a localhost xe oracle. What is wrong? Edit: I may have found the issue.. I have installed Oracle XE 11.2 as **domain user** so probably there is some other configuration that needs to be done to access.?