You might have encountered below errors at sometime while trying to connect to Oracle:
- ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
- ORA-12518: TNS: listener could not hand off client connection.
- ORA-12560: TNS:protocol adapter error
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:
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).
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 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:
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.
Try connecting now, it should be a success.