oracle tutorial webinars

ORA-12560 Error Message TNS Protocol Adapter Error

Some errors in Oracle can take far more time to diagnose than to fix. Locating the source of an ORA-12560 error can feel a bit like a wild goose chase. However, once the problem is traced the mistake can typically be hashed out.

The cause of an ORA-12560 error is the occurrence of a generic protocol adapter mistake. This could result from something simple, such as the database not running due to the instance not being set to auto-start. It can also be a temporary error from an SQL*Net client that occurs intermittently. There are other, fewer frequent causes of an ORA-12560 as well.

Oracle docs recommend, prior to any other troubleshooting, to run a few basic checks through Oracle. It is suggested to check the error stacks for lower-level network errors. Also, make sure that the addresses entered are used for proper protocol specifications. To find out more on the error, turn on tracing and attempt the operation again (be sure to turn tracing back off afterward). To turn on tracing, follow this prompt:

TRACE_LEVEL_<<listener_name>> = admin
TRACE_DIRECTORY_<<listener_name>> = <<insert your directory>>
TRACE_TIMESTAMP_<<listener_name>> = true

This sequence should aid in locating the cause of your ORA-12560. Now how do you solve it? There are a couple of different ways. First and foremost, make sure you have all of the latest patches for your SQL*Net client software. This can be a straightforward but overlooked factor in the origin of the error. Next, clarify that the TNS_ADMIN parameter is properly set (this parameter determines the origin of your TNS administration files). You should also check file permissions on the PC client. Each file in your Oracle database has permissions that determine who has access to read, write and execute a file (in the case of a program).TNS_ADMIN parameter is properly set (this parameter determines the origin of your TNS administration files). You should also check file permissions on the PC client. Each file in your Oracle database has permissions that determine who has access to read, write and execute a file (in the case of a program).

You can also check your $ORACLE_HOME and $PATH variables to make sure they are in proper order. The error can also result from issues stemming from a network firewall. Depending on the firewall settings, a connection between servers could be seeing a degree of interference. If the ORA-12560 is occurring because of this, work with your network administrator to enable the firewall settings to correspond with Oracle.

Lastly, in the auto-start case referenced before, go to the command prompt. Set the Oracle system ID (SID) to C:>set oracle_sid=ORCL. Next, run the Net Start command (C:>net start oracleserviceORCL). This should resolve the ORA-12560 error in this situation.

As you can see, many of the resolutions are basic system and protocol checks, so being vigilant when working within Oracle and executing operations can go a long way to prevent problems, such as an ORA-12560. Remaining up to date on your patches and system updates, as well as aware of how your network firewall is interacting with Oracle, can help ease these mistakes. This set of steps and checks should cover the majority of fixes for the ORA-12560 error message. However, it is always helpful to work with a licensed Oracle consultant for further explanation of these types of errors as well as general knowledge on constructing a well-managed Oracle database foundation.