Unanswered: ORA-12154: TNS:could not resolve service
I have been struggling with this error since long time.
my application is written in asp.net using Oracle database (10.2.0.4). We have oracle client with version 22.214.171.124 installed in the application server.
The problem is that my application some times (not consistence) throws ORA-12154: TNS:could not resolve service exception. Some times it works fine and some times we get this error message in different pages. Once this error start occouring it keep occouring and after some time it starts working by itself. Some times so happens that our application keep getting this error and our application is down, then we have to restart our IIS, which fixes the problem for couple of hours.
My connection string is Data Source=DatabaseSID.DOMAIN NAME; User Id=XYZ; Pwd=ABC
I am successfully being able to use TNSping. TNSping uses sqlnet.ora files which points to sqlnet.names. Sqlnet.names uses NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES). I think if i am able to use TNSping that indicates the sqlnet.ora/sqlnet.names file is fine .
I have observed that when traffic increases the number of above exception increases.
Q 1) we uses oracleConnection to establish a connection.
Q2) After we get exception on a page we are login the error in a table. To my surprise if i get TNS error, even after that the error is being loged in the database. I am unable to understand if the system could not resolve service name for request then how could it establish connection to database to log error? I am providing the stack trace below. Please help me get rid of this problem.
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMu ltiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery)
at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGe tMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionB ase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionB ase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations)
Further information :-
1. We have only one oracle client installed on our server. The oracle client components are
a. Oracle base network config 1.01
b. Oracle Net 126.96.36.199.0
c. oracle provider for OLEDB 188.8.131.52.0
d. Oracle data provider for .Net 184.108.40.206
e. ODBC Driver 220.127.116.11
I did one more experiment, i removed all the entries from sqlnet.names file and then run the application... and guess what!!! it was able to connect to database and do an update, insert at the same time is was not able to run Tnsping command... wired!!!!!!!!