This page is my attempt to collect some common problems and their solutions with connecting to an Oracle database. In the hopes that someday I bump into my own page, searching for a solution for some ORA-…. error.
ORA-01017: invalid username/password; logon denied
ORA-01017: invalid username/password; logon denied Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is: username/password. Action: Enter a valid username and password combination in the correct format.
This already tells you something: You can connect to the database! Just the credentials are not right. Either the username or password is incorrect (or maybe both…). This error also occurs when you connect with a proxy user to a user, but the user hasn’t had a grant to be proxied. You can use (as a privileged user):
alter user [youruser] grant connect through [proxyuser];
This will solve your problem, now you can log in without getting an ORA-01017:
connect proxyuser[user]@database
… provided you use the right password belonging to the proxy user.
Other solutions:
On Windows, look in the sqlnet.ora if SQLNET.AUTHENTICATION_SERVICES= (NONE). This should be set to NTS, but… don’t just change this! There are applications that connect that won’t work with NTS! So check first with your application maintainer.
If the previous doesn’t help on Windows, look in the ora_dba Windows group if your Windows user is added. After adding your Windows user you should have no problem
ORA-28040: No matching authentication protocol
ORA-28040: No matching authentication protocol Cause There was no acceptable authentication protocol for either client or server. Action The administrator should set the values of the SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the client and on the server, to values that match the minimum version software supported in the system. This error ORA-28040 is also raised when the client is authenticating to a user account which was created without a verifier suitable for the client software version. In this situation, that account's password must be reset, in order for the required verifier to be generated and allow authentication to proceed successfully.
I got this one while trying to connect a 10G Oracle Designer client to a 19C database. I changed the sqlnet.ora on the database server side by adding this parameter:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
But did you know that you can also get this error if you have leading spaces in the sqlnet.ora before a parameter? So if your sqlnet.ora parameters itself seem in order, search for leading spaces before the parameter and delete those spaces.
ORA-12560: TNS:protocol adapter error
ORA-12560: TNS:protocol adapter error Cause: A generic protocol adapter error occurred. Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and reexecute the operation. Turn off tracing when the operation is complete.
As OERR says, this is a generic error. There can be many causes for this error. So you need to do some further digging to find out what the problem is exactly. You can check if the database server is up, (if on Windows) check if the database service is up and check the alert log of your database for more clues. You can find many underlying problems and solutions for this error on the internet. Here are some of mine.
I got this error while using sqlplus connecting to an Oracle database on the Windows server itself, connecting with sysdba like this.
sqlplus / as sysdba
You can work around this by using the listener instead of operating system authentication:
sqlplus sys@[database] as sysdba
With using the listener you use the entry in tnsnames.ora on the server to connect. But there is an actual problem here that you need to solve. So let’s fix this right: Add the Windows user you are logged in with to the Oracle DBA group.
If that doesn’t work, check the PATH enironment variable if it contains the Oracle installation directory, and check if it is the first directory. If it’s absent, add it to this path variable.
Another cause can be that in the sqlnet.ora the parameter SQLNET.AUTHENTICATION_SERVICES is set to none. Now I believe that some application require this parameter to be set, because they can’t work with NTS as a setting. So if this is the case, you do not unset or change this parameter. To be able to connect, use the listener solution to connect to the database.
Another case: I was checking a few Oracle databases in an Oracle Fail Safe cluster, to get some info like memory settings, NLS settings, etc for a migration to a newer Oracle Fail Safe cluster.
Connecting to every database went fine with sqlplus / as sysdba, but on one database, I got the:
ORA-12560: TNS:protocol adapter error.
So I looked if the Windows database service was up, and it was. But in a Fail Safe situation, that may not be enough. The database should be brought online not by solely starting a database service in Windows Services, but should be brought online while using the Fail Safe Manager (you can find it on the server, as it is a Fail Safe cluster node, choose the active node for the database).
Turned out that after checking in the Fail Safe Manager with the Oracle Resources, that the database was: Partial online. So I brought it online in Fail Safe manager and then I could connect.
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-12154: TNS:could not resolve the connect identifier specified Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached. Action: The following actions may be appropriate: If you are using local naming (tnsnames.ora file): Make sure that TNSNAMES is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile. Verify that a tnsnames.ora file exists and is in the proper directory and is accessible. Check that the net service name used as the connect identifier exists in the tnsnames.ora file. Make sure there are no syntax errors anywhere in the tnsnames.ora file. Look for unmatched parentheses or stray characters. Errors in a tnsnames.ora file may make it unusable. If you are using directory naming: Verify that LDAP is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile. Verify that the LDAP directory server is up and that it is accessible. Verify that the net service name or database name used as the connect identifier is configured in the directory. Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier If you are using EZCONNECT naming: Verify that EZCONNECT is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile. Make sure the host, port and service name specified are correct. Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.
Now that is quite a mouthful. Many things as you can see, can cause this error. For tnsnames.ora naming most common is a typo somewhere the tnsnames.ora. This can be in a tnsnames.ora entry but also character between the entries that should not be there. Within an entry concentrate on typo’s in the net service name, in the hostname or FQDN in the ADDRESS part or in the SERVICE_NAME.
It’s also possible that you have several Oracle Home’s and you are using the wrong home for a database and you should use the other. You can encounter this for example when performing a Data Pump export for a database, but the wrong Oracle home is set first in your PATH environment variable. Changing the path to the needed Oracle Home so it comes first solves your problem.
Here’s another one. I needed to set some grant selectson an existing view for a new user on a database I cloned. The database was also renamed on a PDB level (Oracle 19C). I had a whole bunch of grants on tables and view to perform. Most were granted without problems, but one of them didn’t, I got this:
ORA-12154: TNS:could not resolve the connect identifier specified when using grant select
First I was baffled, all other grants went fine. Until I looked in the script of the view I was performing a grant select on, I found that the table for the view was referenced under a database link. This link was a link to the database itself, but with the name before the clone. So the PDB name was the old one before the clone. You can question why you would want to use a databaselink to it’s own database (some developer made this). But after changing the database link to the new PDB name, doing the grant select on the view was succesful.
But these are not the only ORA-12154 error. There are several with different text, here is another one:
ORA-12154: TNS: Listener does not currently know of service requested in connect descriptor
Now here there is a connection to the listener, but it can not hand off the connection to the requested database service. So it could be that the database is not started, or the PDB is not registered to the listener for some kind of reason. If the later is the case, a possible cause could be that in the listener.ora the listener has to be set explicitly to an IPv4 adres instead of the hostname or FQDN. This is necessary if the server is also configured for IPv6. Best to turn off IPv6 on the server, so you can revert back to using a hostname or FQDN in the listener.ora.
ORA-12541: TNS: No listener
Probably this listener is not running or a firewall is preventing a connection to the listener. Check if you can telnet to port 1521 (or other set port) at the server. You can also get this error if you try to connect to a machine where there is no Oracle database running (and therefore no listener is running), aka your connecting to the wrong server.
ORA-12170: TNS: Connect timeout occurred
You are hitting a firewall that doesn’t pass to port 1521 (or your configured port) or nothing is registered what you are looking for in the tnsnames.ora. The listener is probably running though.
ORA-12557: TNS:protocol adapter not loadable
You can get this error when you have two (or more?) Oracle home versions installed on Windows and you want to connect to a database that is installed with the first Oracle home (or second or third, but never the last).
The problem is in the PATH variable of Windows. For a connection to a database Oracle uses information from the PATH variable, but only considers the first Oracle home in the path.
Now first check how what the PATH looks like, open a CMD on Windows and do:
echo %PATH%
You see that the home of the database you want to connect is not the first. Let’s say that you installed an Oracle 12C Home first, and after that a Oracle 19C Home. So the Oracle 19C home comes first in the PATH. And you want to connect to a Oracle 12C database. This is how it’s done and do know, it’s a temporary measure within the CMD session:
First set your ORACLE_HOME to the home of the database you want to connect to and check the variable:
set ORACLE_HOME=D:\oracle\ora12201 echo %ORACLE_HOME%
Now use the ORACLE_HOME variable to set this Oracle first in the PATH:
echo %PATH% set PATH=%ORACLE_HOME%\bin;%PATH% echo %PATH%
Now you set your oracle_sid:
set oracle_sid=cdb1
Now you can connect and check if you’re on the right cdb/pdb:
sqlplus / as sysdba select instance_name, status from v$instance; show con_name show pdbs
