HOME

Listener error(s) while trying Oracle connection

Contact Me

You might have encountered below errors at sometime while trying to connect to Oracle:

Try following steps:


Check if you are able to connect to DB via OS authentication (if you can). Verify your ORACLE_SID before performing this check. Via:

sqlplus / as sysdba

Sometimes, connecting in above manner may fail with error ORA-01017: invalid username/password; logon denied, it seems about failed authentication as no user id/password was given, but we know that for OS authentication, no id/password should be required.

To rectify this, check that sqlnet.ora file should contain following entry:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

And your OS username should be a member of ora_dba Windows group. Check in corresponding dba group in Unix. If id is not there, just add it (you might need admin rights for this action).

check ora_dba group.jpg


Now try OS authentication connection method. If you can connect successfully, it means that database is up. But if you try to connect to DB via TNS, it will give some TNS error, i.e. following connection trial will fail:

sqlplus sys/xxxxx@orcl12c as sysdba

Then check if the service (here orcl12c) is up and running or not. If not, start it. below screen is from MS Windows, in Unix, check the process via ps command.

check oracle service status windows.jpg


Check if listener is currently aware of the service or not, via Oracle's lsnrctl utility.
Ideally it should be dynamic registration, but just check. Similar to below screen, your service (orcl12c) might also not be there:

listener screen.jpg




Now go to TNS_ADMIN (ORACLE_HOME/network/admin) directory, open listener.ora, and verify the presence of an entry like below, if its not there, just add it. (Set ORACLE_HOME as per need). Also verify/add this in tnsnames.ora file too.

   
(SID_DESC =
 (SID_NAME = ORCL12C)
  (ORACLE_HOME = C:\oracle\product\12.1.0\dbhome_1)
  (PROGRAM = ORCL12C)
)
	

Now stop and start listener to check if service shows up there. It should show-up now, meaning listener is now aware of the service.


listener screen 2.jpg


Try connecting now, it should be a success.



Thanks...

TOP