Network DBA

Today I will discuss some NETWORK related issues which we database administrator usually face.
First understand which network files is important for oracle network connectivity.1. LISTENER.ORA file It is server side files which is created only on ORACLE SERVER not CLIENT side. It content information regarding listener.
2. TNSNAMES.ORA fileIt is Client side files which is created in both sites SERVER/CLIENT.
3. SQLNET.ORA fileIt is Client side files which is created in both sites SERVER/CLIENT. It is use for user AUTHENTICATION purpose.
NOTE: Above all three files is located in $ORACLE_HOME/network/admin DIRECTORY.
Always remembers LISTENER.ORA, TNSNAMES.ORA file syntax must be correct otherwise you will get a lot of error because of this syntax error.
Example of LISTENER.ORA file
SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)(PROGRAM = extproc)))
LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomainname) (PORT = 1521)))(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521)))))
If you have any syntax error with listener.ora file then you always get error message.
Now in windows and linus you have to set LISTENER services.ON Windows:cmd>set ORACLE_HOME=oracle-home-pathcmd>set PATH=%ORACLE_HOME%/bincmd>set TNS_ADMIN=%ORACLE_HOME%/network/admincmd>lsnrctl LSNRCTL>start
ON linus/solaris/unix:$]export ORACLE_HOME=oracle-home-path$]export PATH=$ORACLE_HOME/bin:$PATH$]export TNS_ADMIN=$ORACLE_HOME/network/admin$]./lsnrctl$]LSNRCTL>start
Example of TNSNAMES.ORA file
TRY = #it is “TNS_ENTRY”(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.13) (PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))
NOTE: As you see there is two names “TRY” it’s called “TNS_ENTRY” which we use with connect string like “username/pwd@TNS_ENTRY”” & “ORCL” is service_name (instance_name)
1. ORA-12154 : TNS:could not resolve the connect identifier specifiedThis error we received when we use “wrong” TNS_ENTRY” or “TNS_ENTRY” is not exist in TNSNAMES.ORA fileJust take above tnsnames.ora file there “tns_entry” is “TRY” and service_name is “ORCL” now see…
SQL> conn scott/tiger@TRYConnected.User is connected because TRY is exist in tnsnames.ora file.SQL> conn scott/tiger@orclERROR:ORA-12154: TNS:could not resolve the connect identifier specifiedUser is not connected because ORCL is not a tns_entry.
2. ORA-12170 : TNS:Connect timeout occurredThis error is very generic and difficult to figure out. But most common reason for above error is “Firewall is enable between CLIENT/SERVER” so first disable firewall setting and try.Also check “use_shared_socket=TRUE” in registry, this must be set to TRUE.
3. ORA-12203 : TNS:unable to connect to destinationThis error is occurring when client machine is not able to connect with server machine. It is just because of NETWORK connectivity.So first check you can able to ping server from client.cmd>ping SERVER_IP_ADDRESS
4. ORA-12500 : TNS:listener failed to start a dedicated server processThis error is occurring because of SGA memory is not sufficient to handle client request or “PROCESSES” parameter is set to low value. So increase SGA size or increase PROCESSES parameter (we need to restart ORACLE SERVER because PROCESSES is static parameter)http://dbaTRY.blogspot.com/2007/03/ora-12500.html5. ORA-12502 : TNS:listener received no CONNECT_DATA from clientThis error is occurring because of wrong configuration of TNSNAMES.ORA file just RECREATE tnsnames.ora file through NETCA tools.
6. ORA-12505 : TNS:listener could not resolve SID given in connect descriptorhttp://dbaTRY.blogspot.com/2007/02/ora-12505.html7. ORA-12508 : TNS:listener could not resolve the COMMAND givenThis error occurring when multiple oracle home install on same machine and multiple listeners are running must set all enviourment variable correctly to point recent version oracle home. And recreate listener.ora file through NETCA tools.
8. ORA-12514 : TNS:listener does not currently know of service requested in connect descriptorThis error is very generic, but most common reason it when database is not OPEN stage.SQL>select status from v$instance;http://dbaTRY.blogspot.com/2007/02/ora-12514.html
9. ORA-12515 : TNS:listener could not find a handler for this presentation*Cause: None of the listener's known service handlers are registeredas supporting the presentation protocol required by the connecting client.*Action: Check that the destination service is configured to accept thepresentation protocol.
10. ORA-12516 : TNS:listener could not find available handler with matching protocol stackThis error sometime happen due to user connection reach processes parameter limit so just increase processes parameter and try.
11. ORA-12518 : TNS:listener could not hand off client connectionThis error occurring because of memory so just increase oracle sga memory and try.
12. ORA-12519 : TNS:no appropriate service handler foundThis error occurring when listener not register database services so try to register database manually to listener.
13. ORA-12520 : TNS:listener could not find available handler for requested type of serverCommon reason for this error in user connection reaches “PROCESSES” parameter value. So increase processes parameter and try.http://dbaTRY.blogspot.com/2007/01/ora-12520.html14. ORA-12533 : TNS:illegal ADDRESS parametersThis error occurring because of TNSNAMES.ORA file content SYNTAX error so best solution is just recreate TNSNAMES.ORA file through NETCA tools.
15. ORA-12535 : TNS:operation timed outhttp://www.dbmotive.com/oracle_error_codes.php?errcode=1253516. ORA-12538 : TNS:no such protocol adapterThis error occurring because of TNSNAMES.ORA file content “(PROTOCAL= )” value is blank so just recreate TNSNAMES.ORA file and try.
17. ORA-12541 : TNS:no listenerThis error occurring when client machine didn’t find “LISTENER” on server, possible reason 1. A listener service is not started so first start and tries.
2. We have two oracle home of different version so TNS_ADMIN parameter didn’t point right network directory.Suppose: we have install oracle 9i or oracle 10g on same machine and TNS_ADMIN variable set to point oracle 9i home. And we trying to connect from client using oracle 10g client to oracle 10g server and we received “above” error means client not able to find 10g listener because TNS_ADMIN point to oracle 9i.
So set TNS_ADMIN variable to point oracle 10g directory and try.
18. ORA-12542 : TNS:address already in useCheck Metalink Note:1017513.102
19. ORA-12545 : Connect failed because target host or object does not existCommon reason when we use “HOSTNAME” instead of “IP ADDRESS” in tnsnames.ora so just change HOSTNAME with IP ADDRESS.
20. ORA-12546 : TNS:permission deniedThis error when user is not belongs to ORA_DBA group then just add OS user to ORA_DBA group and try.
21. ORA-12547 : TNS:lost contactCheck Metalink Note:102893.1
22. ORA-12560 : TNS:protocol adapter errorCommon error when oracle database service is not started.cmd>set ORACLE_SID=sidnamecmd>net start OracleService and try
23. ORA-12564 : TNS:connection refusedCause: The connect request was denied by the remote user (or TNS software). Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation.
24. ORA-12571 : TNS:packet writer failureCause: An error occurred during a data send. Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation. If error persists, contact Oracle Customer Support.
25. ORA-12638 : Credential retrieval failedhttp://dbaTRY.blogspot.com/2007/01/ora-12638-credential-retrieval-failed.html26. ORA-28545 : error diagnosed by Net8 when connecting to an agentCause: An attempt to call an external procedure or to issue SQL to a non-Oracle system on a Heterogeneous Services database link failed at connection initialization. The error diagnosed by Net8 NCR software is reported separately. Action: Refer to the Net8 NCRO error message. If this isn't clear, check connection administrative setup in tnsnames.ora and listener.ora for the service associated with the Heterogeneous Services database link being used, or with 'extproc_connection_data' for an external procedure call.
27. ORA-28546 : connection initialization failed, probable Net8 admin errorCause: A failure occurred during initialization of a network connection from the Oracle server to a second process: The connection was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Net8 adminitrative setup for database links or external procedure calls. The most frequent specific causes are: -- Database link setup for an Oracle-to-Oracle connection instead connects to a Heterogeneous Services agent or an external procedure agent. -- Database link setup for a Heterogeneous Services connection instead connects directly to an Oracle server. -- The extproc_connection_data definition in tnsnames.ora connects to an Oracle instanceinstead of an external procedure agent. -- Connect data for a Heterogeneous Services database link, usually defined in tnsnames.ora, does not specify (HS=). -- Connect data for an Oracle-to-Oracle database link, usually defined in tnsnames.ora, specifies (HS=). Action: Check Net8 administration in the following ways: -- When using TNSNAMES.ORA or an Oracle Names server, make sure that the connection from the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host machine to assure that this service name or SID connects to the correct program. -- Confirm in TNSNAMES.ORA or the equivalent service definition that sevice 'extproc_connection_data' does NOT contain (HS=), or that the service definition used by a Heterogeneous Services database link DOES contain (HS=).
28. ORA-28547 : connection to server failed, probable Oracle Net admin errorCause: A failure occurred during initialization of a network connection from a client process to the Oracle server: The connection was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Net8 administrative setup for database links or external procedure calls. The most frequent specific causes are: -- The connection uses a connect string which refers to a Heterogeneous Services agent instead of an Oracle server. -- The connection uses a connect string which includes an (HS=) specification. Action: Check Net8 administration in the following ways: -- When using TNSNAMES.ORA or an Oracle Names server, make sure that the client connection to the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host machine to assure that this service name or SID refers to the correct server. -- Confirm in TNSNAMES.ORA or the equivalent service definition that the connect string does NOT contain (HS=).
29. ORA-29260 : network error: %shttp://www.dbmotive.com/oracle_error_codes.php?errcode=29260NOTE: Above are some more famous error related to NETWORK.
TNS_ADMIN enviourment variableIt is very important parameter for network files.Also set this parameter to correct network files directory where LISTENER.ORA, TNSNAMES.ORA, SQLNET.ORA file exists.