Network DBA

Today I discuss some NETWORK related issues which we database administrator usually faced.

First understand which network files is important for oracle network connectivity.
1. LISTENER.ORA file
It is server side files which created only on ORACLE SERVER not CLIENT side. It content information regarding listener.

2. TNSNAMES.ORA file
It is Client side files which created in both sites SERVER/CLIENT.

3. SQLNET.ORA file
It is Client side files which 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 mistake.

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-path
cmd>set PATH=%ORACLE_HOME%/bin
cmd>set TNS_ADMIN=%ORACLE_HOME%/network/admin
cmd>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

TAJ = #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 “TAJ” 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 specified
This error we received when we use “wrong” TNS_ENTRY” or “TNS_ENTRY” is not exist in TNSNAMES.ORA file
Just take above tnsnames.ora file there “tns_entry” is “TAJ” and service_name is “ORCL” now see…

SQL> conn scott/tiger@taj
Connected.
User is connected because TAJ is exist in tnsnames.ora file.
SQL> conn scott/tiger@orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
User is not connected because ORCL is not a tns_entry.

2. ORA-12170 : TNS:Connect timeout occurred
This 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 destination
This 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 process
This 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://dbataj.blogspot.com/2007/03/ora-12500.html
5. ORA-12502 : TNS:listener received no CONNECT_DATA from client
This 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 descriptor
http://dbataj.blogspot.com/2007/02/ora-12505.html
7. ORA-12508 : TNS:listener could not resolve the COMMAND given
This 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 descriptor
This error is very generic, but most common reason it when database is not OPEN stage.
SQL>select status from v$instance;
http://dbataj.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 registered
as supporting the presentation protocol required by the connecting client.
*Action: Check that the destination service is configured to accept the
presentation protocol.

10. ORA-12516 : TNS:listener could not find available handler with matching protocol stack
This 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 connection
This error occurring because of memory so just increase oracle sga memory and try.

12. ORA-12519 : TNS:no appropriate service handler found
This 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 server
Common reason for this error in user connection reaches “PROCESSES” parameter value. So increase processes parameter and try.
http://dbataj.blogspot.com/2007/01/ora-12520.html
14. ORA-12533 : TNS:illegal ADDRESS parameters
This 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 out
http://www.dbmotive.com/oracle_error_codes.php?errcode=12535
16. ORA-12538 : TNS:no such protocol adapter
This 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 listener
This 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 use
Check Metalink Note:1017513.102

19. ORA-12545 : Connect failed because target host or object does not exist
Common reason when we use “HOSTNAME” instead of “IP ADDRESS” in tnsnames.ora so just change HOSTNAME with IP ADDRESS.

20. ORA-12546 : TNS:permission denied
This 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 contact
Check Metalink Note:102893.1

22. ORA-12560 : TNS:protocol adapter error
Common error when oracle database service is not started.
cmd>set ORACLE_SID=sidname
cmd>net start OracleService and try

23. ORA-12564 : TNS:connection refused
Cause: 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 failure
Cause: 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 failed
http://dbataj.blogspot.com/2007/01/ora-12638-credential-retrieval-failed.html
26. ORA-28545 : error diagnosed by Net8 when connecting to an agent
Cause: 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 error
Cause: 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 instance
instead 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 error
Cause: 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: %s
http://www.dbmotive.com/oracle_error_codes.php?errcode=29260
NOTE: Above are some more famous error related to NETWORK.

TNS_ADMIN enviourment variable
It 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.