Network DBA

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 fileIt is Client side files which created in both sites SERVER/CLIENT. 3. SQLNET.ORA fileIt 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 fileSID_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>startON linus/solaris/unix:$]export ORACLE_HOME=oracle-home-path$]export PATH=$ORACLE_HOME/bin:$PATH$]export TNS_ADMIN=$ORACLE_HOME/network/admin$]./lsnrctl$]LSNRCTL>startExample of TNSNAMES.ORA fileTAJ = #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 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 “TAJ” and service_name is “ORCL” now see…SQL> conn scott/tiger@tajConnected.User is connected because TAJ 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_ADDRESS4. 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://dbataj.blogspot.com/2007/03/ora-12500.html
ORA-12500
ORA-12500: TNS:listener failed to start a dedicated server processFor Above error there is couple of reason check below point.
1.Oracle Services is start.Windows > c:\>net start OracleServiceUnix > $ps -ef grep ora_smon2.Listener Services is start.3.Take a look on LISTENER.LOG file {%ORACLE_HOME%/network/log}4.if you received WINEXEC FORMAT ERROR 8C:\>net helpmsg 8Not enough storage is available to process this command.Contact OS vendor for more information.5.Sometimes it is happen due to PGA RAM shortage on database.You have to set below parameters.pga_aggregate_target, sort_area_size and hash_area_size.6.TNS-00517: Lost contact32-bit Windows Error: 54: Unknown errorIf your received above errorC:\>net helpmsg 54The network is busy.7.TNS-00507: Connection closed32-bit Windows Error: 109: Unknown errorC:\>net helpmsg 109The pipe has been ended.8.Recreate OracleService service through ORADIM utilityORADIM -DELETE -SID ORADIM -NEW -SID
5. 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://dbataj.blogspot.com/2007/02/ora-12505.html
ORA-12505
Reason When ORACLE_SID <> SERVICE_NAMESMy ORACLE_SID = db01My SERVICE_NAMES = tajSQL> select instance_name from v$instance;INSTANCE_NAME----------------db01SQL> show parameter service_namesNAME TYPE VALUE------------------------------------ ----------- ------------------------------service_names string TajSQL>Take a look on my tnsnames.ora fileSQL> host type C:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora# Generated by Oracle configuration tools.TAJ =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = taj.domainname.local)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)[B](SID = Taj)[/B]))SQL> conn system/oracle@tajERROR:ORA-12505: TNS:listener does not currently know of SID given in connectdescriptorWarning: You are no longer connected to ORACLE.SQL> --edit my tnsnames.ora file from SID to SERVICE_NAME.SQL> host type C:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora# Generated by Oracle configuration tools.TAJ =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = taj.domainname.local)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)[B](SERVICE_NAME = Taj)[/B]))SQL> conn system/oracle@tajConnected.
7. 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://dbataj.blogspot.com/2007/02/ora-12514.html
ORA-12514
ORA-12514: TNS:listener does not currently know of service requested in connect descriptorOne reason is default listener registration is DYNAMIC you have to change DYNAMIC to STATIC.edit your listener.ora file like below SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)(PROGRAM = extproc))(SID_DESC = (SID_NAME = orcl)(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1) ))C:\>LSNRCTL stopC:\>REM WAIT FEW SECONDSC:\>LSNRCTL start--------------------------------------------------------------------------
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://dbataj.blogspot.com/2007/01/ora-12520.html
ORA-12520
Oracle Version : 10.1.0.2.0OS : Windows XP sp2--------------------------------------------------SQL> show parameter processesNAME TYPE VALUE------------------------------------ ----------- ------------------------------processes integer 50SQL> conn scott/tiger@testERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserverClause : Process parameter value is LOW. we have to increase.on Listner.log file04-MAR-2007 10:23:38 * service_update * test * 004-MAR-2007 10:23:39 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=C:\oracle\product\10.1.0\Db_1\bin\sqlplus.exe)(HOST=0A49914)(USER=m.taj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1199)) * establish * test * 12520TNS-12520: TNS:listener could not find available handler for requested type of serverLsnrctl servicesService "test" has 1 instance(s).Instance "test", status READY, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:29 refused:0 state:blockedLOCAL SERVERSuggestion:1.You should wait for some minutes and try to connect. ( but it is temporary solution)2.You should increase PROCESSES parameter values.( increase PROCESSES parameter and SHUTDOWN + STARTUP DATABASE)Oracle Version : 10.1.0.2.0OS : Windows XP sp2------------------------------------Database configure in Shared Server Mode.--------------------------------------------SQL> show parameter dispatchersNAME TYPE VALUE------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)max_dispatchers integerSQL> show parameter shared_serversNAME TYPE VALUE------------------------------------ ----------- ------------------------------max_shared_servers integershared_servers integer 1------------------------------------------------------------------------------------------------after change shared server = 0 then i got error "ora-12520".SQL> alter system set shared_servers = 0;System altered.SQL> conn scott/tiger@shERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserverC:\>lsnrctl servicesService "sh" has 1 instance(s). Instance "sh", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:3 refused:0 current:2 max:1002 state:blocked DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))-------------------------------------------------------------------For above error you have to check configure two parameters.1.Shared_servers2.DispatchersSQL> alter system set shared_servers = 1;System altered.SQL> alter system set dispatchers = "(PROTOCOL=TCP)" ;System altered.Service "sh" has 1 instance(s). Instance "sh", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:4 refused:0 current:2 max:1002 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))The command completed successfully
14. 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=12535
ORA-12535: TNS:operation timed out
Probably there's something wrong with the host (server) name or ip address you are connecting to.First of all, open dos box or Unix shell script and do:tnsping yourdatabasenameto determine the host.This is the output that will be returned (Oracle 9.2 style)Used parameter files:c:oracleora92networkadminsqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = yourhostname)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA920.WORLD) (SERVER = DEDICATED)))
Timeout message
Now, try to ping the hostname or ip address that was returned by the tnsping command:ping hostnameIf the ping command doesn't succeed, there's something wrong with the networkPossibile causes are ip address conflicts, virtual ip addresses that can be resolved anymore, failed cluster groups or veritas cluster packages, DNS outages, ...The best way to solve this timeout is to look for any network issues.If the ping command succeeds, than it might be a firewall problem.Typically, when you connect to an Oracle listener (default port is 1521), a new process is spawned/thread created which listens on a different port.This port is send back to the client, and the client tries to make a TCP connection to the new oracle process/thread.So apart from adding the lsnrctl to the firewall rules, you should also add the oracle process to the firewall rule. The port given back to the client can range anywhere from 1024 to 65535. Windows users might have a look at the USE_SHARED_SOCKET = TRUE variable you can set in the registry ( HKEY_LOCAL_MACHINESOFTWAREORACLE)
16. 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.10219. 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.122. ORA-12560 : TNS:protocol adapter errorCommon error when oracle database service is not started.cmd>set ORACLE_SID=sidnamecmd>net start OracleService and try23. 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://dbataj.blogspot.com/2007/01/ora-12638-credential-retrieval-failed.html
1.Domain issueWhen Server DOMAIN is different FROM client DOMAIN.2.Password Change IssueWhen 0S password is different FROM APPLICATION password.3.NTS (Network Telephony Services)makes the Oracle client attempt to use your current Windows domain credentials to authenticate you with the Oracle server. This could fail for a couple of reasons:- The Oracle server is not configured to support Windows authentication- The credentials you use to login to your local machine are not sufficient to allow you to login to the server.In my case, our Network Administrator had changed the password OS user, while rebooting the system, the Oracle popped up with the Credential Failure.1.Replacing the line: NTS -----------> NONE (nts to none)SQLNET.AUTHENTICATION_SERVICES= (NTS)withSQLNET.AUTHENTICATION_SERVICES= (NONE)2. Install Oracle Client with LOCAL Administrator A/c.
26. 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.