Monday, 10 October 2011

(OFFTOPIC) Searching a Long Column using PLSQL in Oracle Database 10g

This post is just the continuation of the post to search long column in SQL ,the only difference being this is done for searching in PLSQL

Here i have used the concept of global temporary table with clause on commit delete rows,this concept of global temporary tables is very interesting,for all the information check it in http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2213

create global temporary table LONG_TEST_TEMP
(
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
ROW_ID VARCHAR2(30),
CLOB_DATA CLOB
)
on commit delete rows;

The below is a normal table where you can get the result of the search item

create table CHECK_VALUE_INFO
(
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
ROW_ID VARCHAR2(30)
)

Compile the below program to search for the LONG column in table with and without the COMMIT to see one good difference between them

This program also supports a wild card search of a LONG column value within the schema with mainly the overhead on time and resources

create or replace procedure pr_check_value_long(p_value IN OUT VARCHAR2,
p_table IN VARCHAR2 DEFAULT NULL,
p_sql_col IN VARCHAR2 DEFAULT NULL) is
Cursor cr_usr is
select *
from user_tab_columns
where data_type = 'LONG'
and table_name = NVL(p_table,
table_name)
and column_name = NVL(p_sql_col,
column_name);
l_sql_text VARCHAR2(32000);
l_count NUMBER;
l_sql_col VARCHAR2(32000);
BEGIN
for rec in cr_usr
Loop
l_sql_col := '"' || rec.column_name || '"';
BEGIN
EXECUTE IMMEDIATE 'insert into long_test_temp select ' || '''' ||
rec.table_name || '''' || ',' || '''' ||
l_sql_col || '''' || ',' || 'rowid' || ',' ||
'to_lob(' || l_sql_col || ')' || ' from ' ||
rec.table_name;
l_sql_text := ' insert into CHECK_VALUE_INFO SELECT TABLE_NAME,COLUMN_NAME,ROW_ID
FROM long_test_temp WHERE clob_data LIKE ' || '''' || '%' ||
p_value || '%' || '''';
EXECUTE IMMEDIATE l_sql_text;
l_sql_text := NULL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
end loop;
p_value := NULL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Below is a example of doing the search for the column LONG_VALUE of datatype LONG and table name TEST_LONG_VALUE

DECLARE
P_VALUE VARCHAR2(4000):='131';
BEGIN
pr_check_value_lonG(p_value,'TEST_LONG_VALUE','LONG_VALUE');
END;

Also an example of how you can check the result

SELECT a.*  FROM test_long_value a WHERE a.ROWID IN
(SELECT ROW_ID FROM CHECK_VALUE_INFO b where b.table_name='TEST_LONG_VALUE' and b.column_name='LONG_VALUE')

Happy times searching :) 

Monday, 26 September 2011

(OFFTOPIC) Simplest method for searching a LONG column using SQL IN Oracle Database 10G

In this example we will see how we can search a LONG column using SQL.

The simple concepts used in the example are LONG column can be converted to CLOB using TO_LOB function ,CLOB column is searchable and rowid is used to uniquely identify a row in a table.

First create a Table with values as the rowid and LONG column of the table in which you want to search the LONG column.

Create table test_long_search as select rowid row_id, to_lob(message) message  from message_log;

Here Message_Log is the table which contains the LONG column you want to search.

Then you can search the value as

select * from test_long_search where message like '%FAILED FOR SOME REASON%'

you will get

ROW_ID
MESSAGE
AAAswsAAKAAAK8OAAA
<CLOB>
AAAswsAAKAAAK/kAAC
<CLOB>


Then in the main table you can search like

select * from message_log where rowid in ('AAAswsAAKAAAK8OAAA','AAAswsAAKAAAK/kAAC');

EMAILED
REFERENCE_NO
RECEIVER
MESSAGE
Y
123456
SAN
<Long>
Y
123456
SAN
<Long>

we hope this example was helpfull :)

Saturday, 24 September 2011

Debugging Oracle Forms Application server(9i and 10g)

There is a very good Oracle documentation on manually configuring  Oracle form trace and debugging available at Oracle site at Tips, Techniques and Debugging of Oracle forms Application server.

This link talks about the debugging and tracing options available in Oracle 9iAS but most of the things are applicable to Oracle 10gAS as well.

Documents available at this link mainly talk about Diagnostics methods

1)Enabling Form trace:-Topics like how to configure form trace,start  the trace and also how to convert trace file to readable XML files

2)Servlet logs:-

  • It provides a record of all Oracle9i Forms sessions, including session start and endtimes, and the user’s IP address and host name 
  •  Monitoring of Oracle9i Forms-related network traffic and performance 
  • It also gives Information for debugging site configuration problems (debug-level
    logging)
     
3)Debugging Client and Server crash  and also discussess about Common forms(FRM errors) and how to solve them.

 4)There is also a Sample Application which you can use to learn some functions in forms and as well as debug it.The code for the Sample Application is available at
http://sheikyerbouti.developpez.com/tutoforms10g/files/tutoforms10g.zip

In case you are not able to find the Debugging documentation or the link expires then do a google search for the following pdf files

1)tutoforms10g.pdf
2)trace9i-133379.pdf
3)forms9idiagnostics-129282.pdf

If you have any questions do mention in the Comments section

Happy Debugging

Tuesday, 20 September 2011

Oracle database Instrumentation Example using debug.f

In this post we would see a practical example of Oracle instrumentation.
This example shows the instrumenting mechanism of debug.f across sessions and across schema

First we have to download and install debug.f from
http://gerardnico.com/wiki/database/oracle/debugf
which is probably the only place you might find the debug.f code.

The different procedures available for the debug purpose are

1)debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');

This is used to intialize the debug the first parameter 'ALL' meaning all modules(can be function,procedure or package etc) and SYSTEM meaning the schema i want to debug

2)debug.f('the first is %s',v_word1);
This one works like printf in C and the maximum you can give is 10 parameters where v_word1 is a parameter

3)debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
This is same as debug.f but here you can give more than 10 parameters

4)debug.clear :-This is used to stop the debug.

The sample function which i have taken and modified to be instrumented is available at  http://www.shareoracleapps.com/search/label/Workflow.This site offers very good information about financial transactions and also contains code for general bank operation which would be helpful if you want to try creating a basic bank application as test project.

The sample function ruppee_to_word  is


CREATE OR REPLACE FUNCTION ruppee_to_word (amount IN NUMBER)   RETURN VARCHAR2 AS
v_length   INTEGER         := 0;  
v_num2     VARCHAR2 (50)   := NULL;  
v_amount   VARCHAR2 (50)   := TO_CHAR (TRUNC (amount));  
v_word     VARCHAR2 (4000) := NULL;  
v_word1    VARCHAR2 (4000) := NULL;
TYPE myarray IS TABLE OF VARCHAR2 (255);
v_str      myarray         := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN  
debug.f('the entered time %s',sysdate);
IF ((amount = 0) OR (amount IS NULL))   THEN    
v_word := 'zero';  
ELSIF (TO_CHAR (amount) LIKE '%.%')  
THEN    
IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)    
THEN         v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);

IF (LENGTH (v_num2) < 2)        
THEN            v_num2 := v_num2 * 10;        
END IF;

v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2), 'J'),'JSP'))  || ' paise ';  
debug.f('the first is %s',v_word1);    
v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);  
debug.fa('the second is %s and %s',debug.argv(v_word1,v_amount));      
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),'J'),'Jsp') || v_word;        
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
FOR i IN 1 .. v_str.COUNT        
LOOP          
EXIT WHEN (v_amount IS NULL);          
v_word :=                
TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
         2),
 'J'),                           'Jsp'                          )               || v_str (i)               || v_word;            v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);        
 END LOOP;    
END IF;  
ELSE    
v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');  
END IF;

v_word := v_word || ' ' || v_word1 || ' only ';  
v_word := REPLACE (RTRIM (v_word), ' ', ' ');  
v_word := REPLACE (RTRIM (v_word), '-', ' ');  
debug.f('the entered time %s and the word is %s',sysdate,v_word);

RETURN INITCAP (v_word);

END ruppee_to_word;

I have created the package debug and function rupee_to_word on SYSTEM user and given the necessary priviledges to other users where the debug package can be used for instrumentation

I have then created three separate sessions to show that we can initialize debug across session and get the information about other sessions.

The first session:-

DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=1234;
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
END;

The second session:-

DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=2132.43;
BEGIN
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
END;

the third session 

DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=456;
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
debug.clear;
END;

I had also created one more session in ATM schema to show that debugging can be done across schema  and run the debug initilation in SYSTEM Schema as
DECLARE
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','ATM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
END;

Then called the below block in ATM schema
 
DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=456;
BEGIN
n:=ruppee_to_word(l_amount);
END;

Please find the logged data for all the sessions below


Debug parameters initialized on 20-SEP-2011 22:20:43
           USER: SYSTEM
        MODULES: ALL
       FILENAME: C:\debugf123\temp\test.txt
      SHOW DATE: YES
    DATE FORMAT: DD/MM/YYYY HH24:MI:SS
    NAME LENGTH: 30
SHOW SESSION ID: YES

Session_id     Data time                            Owner.procedure                            logged message
600 -            20/09/2011 22:20:43(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP11 
 600 - 20/09/2011 22:20:43(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is ONE THOUSAND TWO HUNDRED THIRTY FOUR  only
600 - 20/09/2011 22:20:43(        SYSTEM.ANONYMOUS BLOCK    7) The Translation of Rupee 1234 is One Thousand Two Hundred Thirty Four  Only
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   23) the first is  AND FORTY-THREE paise 
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   25) the second is  AND FORTY-THREE paise  and 2132
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   28) the third is  AND FORTY-THREE paise  and 2
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is Two Thousand One Hundred Thirty Two  AND FORTY THREE paise  only
601 - 20/09/2011 22:21:10(        SYSTEM.ANONYMOUS BLOCK    7) The Translation of Rupee 2132.43 is Two Thousand One Hundred Thirty Two  And Forty Three Paise  Only
602 - 20/09/2011 22:21:24(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
602 - 20/09/2011 22:21:24(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX  only
602 - 20/09/2011 22:21:24(        SYSTEM.ANONYMOUS BLOCK    7) The Translation of Rupee 456 is Four Hundred Fifty Six  Only

Debug parameters initialized on 20-SEP-2011 22:25:17
           USER: ATM
        MODULES: ALL
       FILENAME: C:\debugf123\temp\test.txt
      SHOW DATE: YES
    DATE FORMAT: DD/MM/YYYY HH24:MI:SS
    NAME LENGTH: 30
SHOW SESSION ID: YES

611 - 20/09/2011 22:25:17(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
611 - 20/09/2011 22:25:17(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX  only
611 - 20/09/2011 22:25:17(           ATM.ANONYMOUS BLOCK    7) The Translation of Rupee 456 is Four Hundred Fifty Six  Only

Debug parameters initialized on 20-SEP-2011 23:10:17
           USER: ATM
        MODULES: ALL
       FILENAME: C:\debugf123\temp\test.txt
      SHOW DATE: YES
    DATE FORMAT: DD/MM/YYYY HH24:MI:SS
    NAME LENGTH: 30
SHOW SESSION ID: YES

617 - 20/09/2011 23:10:31(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
617 - 20/09/2011 23:10:31(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX  only

Sunday, 18 September 2011

Debugging Oracle Database(10g) and Application performance

Most often we find ourself in a position where we need to improve our application performance over a period of time when the Application has scaled due to large volume of data.

In a typical three tier Architecture(Application server,Webserver,Database server) or any Multi tier Architecture the poor performance may be due to any of the Components.

Each component needs to be debugged for performance issues.

The primary ways of identifying performance issues are

A) Instrumenting the Application:-  Instrumentation for example is logging information about important transactions of the Application.
Recording information such as time taken to perform a transaction and response transaction over a period of time,wait time for resources for a particular transaction etc.
It should be done during design of the application itself for later debugging.This Appproach saves a lot of time and resources which is required to identify performance bottle necks when the application has gone to production.

The important consideration to be made before instrumentation is

1)There should be always be an option to disable the logging of information since it would be additional overhead during normal operation of the Application

 2)There should be different levels of logging information.For example Warning level,debug level,information level.

3)It should be done for all the components of the Architecture

Although all the Components of the Application has to be instrumented we will cover only Oracle Instrumentation to be relevant with the topic.

Oracle DB:-Oracle Instrumentation which covers mainly the following categories

1. Debugging:-The characteristics of it are
  • Should be able to enable debugging for one or more “modules” in the code.
  • Should be able to activate debugging remotely for one or more “users”.
  • Debugging should be written to one or more “persistent” sources and preferably to a console for initial debugging and there needs to be enough debugging code to be useful.

2. Logging:-Although logging is dependent on the nature of the application here are some common requirements for application logging:
  •  Output to a persistent source
  •  Provide logging levels (i.e. informational or error)
  •  The ability to record errors and anomalies
  •  The ability to record runtime information
3. Runtime registration and Metric collection
The source code ,examples and other information of the oracle instrumentation are available at debugf,log4plsql,dbms_application_info,log4plsql example and user guide,Debug.f usage and other information
B) Profiling the Application:- the Application-It is nothing but gathering information about various component of the Application through external profilers which means not part of the Application environment and also integrated profilers part of the development environment.They defer from Instrumentation in the sense they are independent of the Application and are tools of the underlying technology.

Although all the Components of the Application has to be profiled we will cover only the database part

 
1)TKRPOF and TRCSESS:- These profilers are used for interpreting SQL TRACE which is used to provide information mainly about SQL statements and their processing .The details about how to enable and disable SQL trace,how to interpret trace files(TKPROF) and how to extract specific information from trace files (Trcsess)are available in SQL TRACE Details,Interpreting TKPROF,Oracle Documentation

 2)PLSQL Profiling:-To profile PL/SQL code, the database engine provides an API through the package dbms_profiler. With that package, the profiler integrated in the PL/SQL engine can be enabled and disabled at the session level. For more information kindly check DBMS_PROFILER

Friday, 16 September 2011

Oracle Database 10g Debugging Connection problems (contd)

This post is a continuation of the previous post in this topic .We have already mentioned about  how to enable trace and log options on the client.The below information is just for your recap if you have seen the prevoius post and also for those who are familiar about Net services in oracle and want to debug their connection issues

Note:-In this post we have discussed only about Windows Platform

The last option to find out connection issues is to enable log and trace files as below

I)The Common file where we can enable logging options in client is sqlnet.ora located in <ORACLE_HOME>/NETWORK/ADMIN

The logging parameters and Tracing parameters( TRACE_LEVEL_CLIENT being the most important)for client are

sqlnet.ora Parameter

LOG_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is the current working directory.

LOG_FILE_CLIENT :-Sets the name of the log file for the client. By default the log name is sqlnet.log.

TRACE_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.

TRACE_FILE_CLIENT :-Sets the name of the log file for the client. Sets the name of the trace file for the client. By default the trace file name is sqlnet.trc

TRACE_LEVEL_CLIENT :- This will decide the level of information provided values are
off (equivalent to 0) provides no tracing

user (equivalent to 4) traces to identify user-induced error conditions

admin (equivalent to 6) traces to identify installation-specific problems

support (equivalent to 16) provides trace information for troubleshooting information

In our case we need to troubleshoot hence set the level to SUPPORT unless problem requires the other options

Sample sqlnet.ora

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
LOG_DIRECTORY_CLIENT=D:\test
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_DIRECTORY_CLIENT=D:\test
TRACE_FILE_CLIENT=trace_file_test

Go to II) If you want to just debug the trace file without much information required or else check the below examples of connection issues which were debugged

A)First and foremost you have enabled the log and trace files and the trace file is not generating in the path mentioned in TRACE_DIRECTORY_CLIENT in sqlnet.ora or in the default path then check the below

1)If there are multiple Oracle homes make sure that the oracle client which you are using to connect is the default Oracle_Home

2) Suppose you have two oracle homes one D:\orant\bin and D:\Oracle10g\bin.
Suppose you want to debug the application using oracle client D:\Oracle10g\bin
then put this entry as the first entry in Environment Variable "PATH".

MyComputer-->Properties-->Advanced-->Environment variable

for example if PATH= D:\orant\bin;D:\Oracle10g\bin;<OTHER PATHS>

change it to

PATH= D:\Oracle10g\bin;D:\orant\bin;<OTHER PATHS>

3) If still Trace file is not generating then mention the backgorund and details in the comment section.

B)You are getting the error "ORA-12154: TNS:could not resolve the connect identifier specified" one possible error may be that your TNS Entry may be wrong if you are using local naming (TNSNAMES.ORA file)

Hence the trace file may contain this

Success:-

nnftrne: Using tnsnames.ora address (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.170.8.77)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FLEXCUBE.WORLD)
)
) for name CTEST.WORLD


Failure:-

nnftrne: Using tnsnames.ora address (DESCRIPTION = for name CTEST.WORLD


with out having the complete tns entry.

For a better understanding of this error ORA-12154 check the link ORA-12154:TNS:could not resolve the connect identifier specified

C)If you are getting the error "ORA-12545: Connect failed because target host or object does not exist"
the one possible reason may be the Host ip address has some special character in the TNS Entry if you are using local naming(TNSNAMES.ORA)
Hence the trace file may contain this

Failure:- 

snlinGetAddrInfo: Name resolution failed for 10.170.8.77,

for more information about this error check ORA-12545: Connect failed because target host or object does not exist

D)if you are getting the error ORA-12541: TNS:no listener

One possible reason might be the host name is mentioned wrongly.

Then the trace file will contain

Success:-

nttcni: trying to connect to socket 412.
snlinGetNameInfo: entry
snlinGetNameInfo: Using numeric form of host's address 10.170.8.77
snlinGetNameInfo: exit
nttcni: connected on ipaddr 10.170.8.77


Failure:-
nttcni: trying to connect to socket 420.
ntt2err: entry
ntt2err: soc 420 error - operation=1, ntresnt[0]=511, ntresnt[1]=61, ntresnt[2]=0
ntt2err: exit 


for more informnation on this error check ORA-12541: TNS:no listener

II)These are simple examples of what might contain in the trace file for Connection errors from Client machines

 For debugging all kinds of connection errors from client

1) Take the trace file from the client machine in which the Connection is successfull which have same configuration of oracle client and database as the client machine which has connection errors,if the oracle client is 10g or higher then remove the timestamp information  

2) Take the trace file from problematic Client machine ,if the oracle client is 10g or higher then remove the timestamp information 

3)Compare the two trace files to identify the connection problem and  google it for additional information .Also note the trace file for successfull connection might contain additional data which you can ignore.


We hope this information was useful

Thursday, 15 September 2011

Oracle Database 10g Debugging Connection problems

Even though this post is about solving Oracle Database 10g connection problems most of the things discussed here are same or similar for other versions of Oracle databases.
In order to diagnose Oracle database connection problems we need to know about Oracle Network services(Net 8).Oracle Net Services(Net 8) is a service which provides methods for understanding and resolving network problems.It uses logs and trace files to diagnose the problem.

Commonly there are two types of Diagnostics we can do to identify Connection Problems

Server Diagnostics:-To identify if there is connection issue in the database server you need the assistance of DBA or system administrator.Also two things you have to know is
1) Whether other systems are able to connect to the database server ,if it is yes then there is no issue with the server.
2)Whether any server,database or listener configuration has been changed recently ,if it is then there is a possibility of issue at the server side.

If there is a server issue Administrator has to do two types of test

1)Verify the database is running
2) Perform a loopback test
The Server Diagnostics is not our concern since it will be taken care by Administrator

Client Diagnostics:-To diagnose Client connection problems you have to know two things

If you have multiple Oracle homes on your client machine or you are establishing a connection from third Party Application(Visual Studio) to oracle,mention your connection problem in the Comments section,i would address them as much as possible

If the same Oracle Client installation has been done on your network and only your Client is not able to connect to the database server jump to point 5 otherwise see the below

1)Check that you have installed the same protocol support as was installed on the database server.This can be verified with the System Administrator.The Common protocol in unix system are
IPC
BEQ
TCP/IP
SSL
RAW

2)Check base connectivity for underlying network transport.For example for the TCP/IP Protocol check the PING Utility(which is very commonly used).Use the PING utility to test the network connection between client and Database server

3)Verify with the Local PC Support that all Net8 Services software for the client has been installed

4) Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files exist in the correct locations and then check Localized Configuration File Support for further details

5)If other client computers are  connecting to the selected Oracle database, back up your existing files and copy both the working tnsnames.ora and sqlnet.ora files from the working computer onto the non-working client

6)Perform further  test by verifying Testing Network Connectivity from the Client. While verifying this the TNSPING is not a reliable utility to check connection descriptor but anyhow for testing purpose suppose this is the Tnsnames entry for the database

ORA11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA11)
)
)
you have to go to Command Prompt and type

d:>TNSPING ORA11
to verify the connection service.On success it will give

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =ORA11)))
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (10 msec)
OK (10 msec)
OK (0 msec)

Also trcroute is not available in Oracle database 10g Windows platform.

7)IF the connection still fails your last option is to enable log and trace files

The Common file where we can enable logging options in client is sqlnet.ora located in <ORACLE_HOME>/NETWORK/ADMIN

The logging parameters and Tracing parameters( TRACE_LEVEL_CLIENT being the most important)for client are

sqlnet.ora Parameter
LOG_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is the current working directory.

LOG_FILE_CLIENT :-Sets the name of the log file for the client. By default the log name is sqlnet.log.

 TRACE_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.

TRACE_FILE_CLIENT :-Sets the name of the log file for the client. Sets the name of the trace file for the client. By default the trace file name is sqlnet.trc

TRACE_LEVEL_CLIENT :- This will decide the level of information provided values are
off (equivalent to 0) provides no tracing

user (equivalent to 4) traces to identify user-induced error conditions

admin (equivalent to 6) traces to identify installation-specific problems

support (equivalent to 16) provides trace information for troubleshooting information

In our case we need to troubleshoot hence set the level to SUPPORT unless problem requires the other options

Sample sqlnet.ora

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
LOG_DIRECTORY_CLIENT=D:\test
TRACE_LEVEL_CLIENT = SUPPORT
So on a concluding note kindlylet us know the background of the problem and copy the contents of the log and trace files in Comments section if you are not able to figure out the Connection Problem yourself.