Created On: 23 September 2010
Problem:
I am currently encountering a problem connecting a COBOL program from Net Express to an Oracle Database in AIX. The database on AIX is configured in my Windows Oracle Net Manager with SID name (TEST1) specified. Test connections show as being successful. The database has also been added in the Oracle Enterprise Manager where I am able to browse through the tables and schemas. However, when I attempt to run a COBOL program with environment variable ORACLE_SID=TEST1, connection to the database failed with a TNS Adapter error.
Sqlerrmc
ORA-12560: TNS:protocol adapter error
Sqlcode
-0000012560
I also have a local Oracle Database installed on Windows with a SID name of TEST2. If I change the ORACLE_SID=TEST2, connection to the database from my COBOL program works without problem.
Sqlerrmc
ORA-12560: TNS:protocol adapter error
Sqlcode
-0000012560
I also have a local Oracle Database installed on Windows with a SID name of TEST2. If I change the ORACLE_SID=TEST2, connection to the database from my COBOL program works without problem.
Resolution:
Oracle classify this as a 'generic protocol adapter error'. This usually indicates that the Oracle client does not know what instance to connect to or what TNS alias to use. If running on the server (or workstation) that is running the database (i.e. if the database is local to the machine) then try explicitly setting 'ORACLE_SID'. e.g.
set ORACLE_SID=
And then try again. If this works then consider whether to make the change permanent by specifying it as a system variable.
(Possibly 8.1.5 and later:) If running on the server (or workstation) that is running the database then check that the service name 'OracleService' ties up with the actual database SID. If the database SID is changed without recreating the service then this can lead to this error. To recreate the service:
oradimxx -delete -sid
oradimxx -new -sid -intpwd AUTO -pfile
Try setting the environment variable 'LOCAL' to the required connection alias (typically the database SID) and then try again. (I am informed that for UNIX systems the environment variable TWO_TASK should instead be used.) Do be sure that the application that is being used picks up the new environment variable setting. Setting this to an invalid connection alias will typically produce ORA-12154: TNS:could not resolve service name.
Ignore the error and try connecting by specifying explicitly which connection alias to use.
Check that the network connection between the client computer and the server has not failed. From the client try pinging the server. (For general guidance on troubleshooting general network connectivity problems click here.)
If the database server has its IP address dynamically allocated by DHCP then its IP address may change. This is not likely to be a problem on a local network where DNS will take care of name resolution issues, but where the database server is on a remote network and its IP address needs to be explicitly specified in the client's lmhosts file then this can be a problem because it requires periodic updates to the client's lmhosts file. If faced with this scenario then push to get the database server allocated a permanent IP address.
set ORACLE_SID=
And then try again. If this works then consider whether to make the change permanent by specifying it as a system variable.
(Possibly 8.1.5 and later:) If running on the server (or workstation) that is running the database then check that the service name 'OracleService' ties up with the actual database SID. If the database SID is changed without recreating the service then this can lead to this error. To recreate the service:
oradimxx -delete -sid
oradimxx -new -sid -intpwd AUTO -pfile
Try setting the environment variable 'LOCAL' to the required connection alias (typically the database SID) and then try again. (I am informed that for UNIX systems the environment variable TWO_TASK should instead be used.) Do be sure that the application that is being used picks up the new environment variable setting. Setting this to an invalid connection alias will typically produce ORA-12154: TNS:could not resolve service name.
Ignore the error and try connecting by specifying explicitly which connection alias to use.
Check that the network connection between the client computer and the server has not failed. From the client try pinging the server. (For general guidance on troubleshooting general network connectivity problems click here.)
If the database server has its IP address dynamically allocated by DHCP then its IP address may change. This is not likely to be a problem on a local network where DNS will take care of name resolution issues, but where the database server is on a remote network and its IP address needs to be explicitly specified in the client's lmhosts file then this can be a problem because it requires periodic updates to the client's lmhosts file. If faced with this scenario then push to get the database server allocated a permanent IP address.
Incident #2178510
Old KB# 31773
