Ahmed is an Oracle Apps DBA based in South Africa & his White Paper focuses on a client site where he has worked & how implementing Oracle Dataguard has reduced the cloning time dramatically for them & the impact this has had on the business. A must-read for any DBA/Technical Managers, DBAs, Apps DBAs & disaster recovery teams.

Alexander is a certified Oracle DBA based in Canada & his White Paper focuses on integrating Oracle Forms 11g with Oracle BPEL/Workflow included in the Fusion Middleware 11g platform. A must-read for any Oracle Forms Developers, SOA Architects & Project Managers.

Many thanks for your contributions guys!

The people who created them probably left the company long ago and there is little documentation on the databases. Nobody is certain whether or not they are still being used, so they just sit around using up disk space.  If the databases are started up, then they’re also using memory, even though they may not be doing anything productive.

Rather than prolonging this situation, it’s a good idea to remove these databases - once you’ve confirmed that no-one uses them anymore.  Unfortunately, within a large business it may not be easy finding out who within the company is using which databases, if documentation has not been kept up to date.

As a starting point you can look in the following places:
(i) Database alert log
If the database is currently shut down, then what date was it shut down? If this was a long-time ago (i.e. more than 1 year), then the chances are that the database is no longer used, or is so out of date with current business data that it would need to be refreshed/updated before it could be of any use. 

Note: Just because things such as configuration changes (i.e. extending datafiles) are shown in the alert log, it doesn’t mean to say that it’s actually in use.  A DBA can carry out maintenance tasks on a database, whether or not there are any business users carrying out work.

Similarly, you may find that a backup tool such as RMAN regularly connects to the database.  This also doesn’t mean to say that the database is being used by the business - just that it’s being backed up.

(ii) Listener log
Search in the listener log for the last entries for that database. Even if the database is shutdown, this will let you know the last time that anyone was using it.  Be careful that you don’t think that someone is using the database just because there is an automated application process or reporting tool that connects to the database regularly.  If individual users are not connecting to the database, then it may not be in use anymore. 

Note: Double-check with any reports or application support teams as well, because the database may just be a repository, in which case individual user connections could be rare.

(iii) Datafile timestamps
If the database is shutdown, What is the last modified date shown in the filesystem for the datafiles?  I’ve come across situations where you can’t find any entries for the SID in the listener log and the alert log for the database has been removed, so this is a good way to find out when the database was last open.

Note: This is assuming that someone hasn’t just copied the files from another location, without preserving the original file timestamps.
(iv) Run AWR/ASH or Statspack reports
These will let you know if anyone has been using the database within the last week or longer - depending on the retention period configured for the database.  (If performance snapshots are not configured, then you may also find useful information on long-running transactions in V$SESSION_LONGOPS).

Note: Just because a database hasn’t been used in the last week, doesn’t mean to say it’s not required anymore.  It could be used for monthly, weekly or annual reporting, so you may not see regular activity.
 (v) Database Auditing
If database auditing has been enabled, it will give you an idea who has been accessing the database. (e.g. query views such as DBA_AUDIT_SESSION and DBA_AUDIT_TRAIL).
(vi) Standby databases
It’s worth running the query SELECT DATABASE_ROLE FROM V$DATABASE; If this returns “PHYSICAL STANDBY” or “LOGICAL STANDBY”, then it could mean that someone has deleted a primary database in the past, but neglected to remove the associated standby database. 
 

Before deleting any databases, it would be advisable to email anyone that may have an interest in the database and then ensure that a copy of the database files and configuration information is archived to tape or other storage for a pre-defined period of time.  Ensure that you have approval from all interested parties before carrying out any work like this, which could have an impact on the business.

Many thanks to the following authors for their White Papers, your contributions are much appreciated & help to share Oracle knowledge on a global scale:

Martin Dvorak - “How to Plan & Deliver Oracle eBusiness Suite Training with UPK”

Claire Aukett - “Absence Management using R12 Oracle HCM & CRM”

Elwyn Lloyd Jones - “Upgrade Strategies for OWB Environments”

If you would like a copy of any of these papers, please register with the White Paper area of our website where you can gain access to our full White Paper library & if you are interested in becoming a White Paper author yourself, please contact me at: kirsten.campbell@oraclecontractors.com

New papers are also being released in September too so watch this space!

The main arguments against the exams seem to be along the following lines:

The exams only deal with theoretical situations. You can’t beat real-life experience. 
This is true, but the exams do demonstrate that you are able to understand technical issues.  In order to resolve problems, you need to know how the software works.  You also need real-life experience of using your theoretical knowledge in a practical manner, before you can become an effective DBA.

Outside of a test laboratory or classroom, you have real users, applications and software from multiple vendors. Once exposed to these environments you become a much better DBA.

 
The exam is just a memory test.
That’s true to some extent - but you have to understand the question and which of the possible answers is the correct one. You still have to understand what you’ve remembered.  Even though you may forget the exam topics over time, at least you have positive proof that at the time you took the exam, you knew that area of Oracle in detail.

I don’t need to take the exams to show that I keep up to date.
Whilst you can just read the documentation, at least the exams prove that you’ve made the effort to keep your skills current. Otherwise everyone else just has your word for it that you have.  

The other issue is that you can read the documentation but not understand it properly. Passing the exam is proof that you understood the concepts in sufficient depth to pass the exam.

Taking the exams also provides a more focused way of keeping up to date.

 
Why bother learning about lots of features that you’re never going to use?
There are lots of features that you may never use, but if a new problem arises - if you’ve kept up to date - then you’re are aware of all the possible solutions.  You don’t always have several days or hours to go away and research all the available options. Even having a high-level overview of a solution can mean that you not only resolve issues more quickly, but that you’re more likely to come up with the most effective solution.  If you aren’t aware of other solutions then you never will use them. You’ll just end up doing things the same way that they’ve been done for years.

Another reason for learning about many features is that unless you can predict the future, how do you know what features you will never use? 

Knowledge of lots of functionality is useful when resolving issues because more options that were once separate from the main database installation are now integrated into it. Sometimes these options can cause errors even though your application isn’t actually using them.

The exams also demonstrate that you’re interested enough in the technology to want to keep up to date. Nobody forces you to take them.

Whether or not you decide to take the exams is a personal choice, but I would say that they can be useful as a starting point to differentiate between two DBA’s who have a similar level of experience.  There is still no substitute for real-world experience and just because someone passes the exam doesn’t necessarily mean that they’ll be a better DBA. 

(In case you’re wondering, I have taken the exams!)

This is a pretty straightforward task and can often highlight useful information to diagnose issues such as incorrect ODBC drivers or driver versions, or attempting to use incorrect database connection information. 

The Scenario
————-
To demonstrate ODBC tracing, we’ll first log into an Oracle 11.2.0.1.0 Enterprise edition database called “ORCL11″ and create an account called “odbc1″:   

create user odbc1 identified by odbc1;

(Note: You may want to make your password more secure than this! Remember also that 11g has case-sensitive passwords by default)

grant create session, create table to odbc1;
alter user odbc1 default tablespace users;
alter user odbc1 quota 10M on users;

Next we’ll connect as our new user and create a test table with a small amount of data.

connect  odbc1/odbc1

create table odbc_test_tab (col1 varchar2(40));
insert into odbc_test_tab values (’TEST’);
insert into odbc_test_tab values (’TEST2′);
commit;

Then we create an ODBC connection to our ORCL11 database.  Create the connection using a System DSN called “EXCEL_TEST11″. Use the Oracle ODBC Driver 11.2.00.01 and the odbc1 database account to connect.

The last step is to create a new Excel 2010 spreadsheet called “odbctesting.xlsx” 

Note: To keep this post brief, I haven’t included full details of the steps to create the ODBC connection, or of setting up the connection in Excel. If anyone wants detailed instructions on how to do this, please let me know.  For the Excel connection the main steps are to go to the Data tab - “From Other Sources” - “From Data Connection Wizard” - “ODBC DSN” - Next and then select the “EXCEL_TEST11″ ODBC connection.  (Even though you’ll see a large listing of database objects, the ODBC1.ODBC_TEST_TAB is in the list - near the end. All the other objects are database views and tables to which PUBLIC - i.e. all users - have been  granted access).   Don’t select the option to save the password to the file.
 

Turning on tracing
——————
Next we’ll turn on ODBC tracing, so that we can see what’s happening when the connection is being made. From a Windows XP client, as you need to do is:

Start - control panel - Administrative Tools - Data Sources (ODBC)   (or you can go  Start - run - odbcad32)

Then: Go to the “Tracing” tab - Click on the “Start Tracing Now” button

Once you’ve clicked on the “Start Tracing Now” button, you’ll notice it will change to be a “Stop Tracing Now” button - Apply - OK.   This closes the ODBC Data Source Administrator.
Notes:
(i) To change the location of the logfile, click on the “Browse” button on the Tracing tab. You can also change the name of the log file. Then click Save.
(ii) Be aware, that this will turn on tracing for ALL ODBC connections running on this client.
(iii) Tracing could have a serious performance impact on your application, so only enable it if neccessary. 
(iv) Microsoft support article ID: 942976 notes that 64-bit versions of windows have two versions of the ODBC Administrator tool:

%systemdrive%\Windows\SysWoW64 folder.   - 32-bit version of Odbcad32.exe
%systemdrive%\windows\System32 folder.   - 64-bit version - also called Odbc32.exe

If running odbcad32 to edit 32-bit DSN’s, then specify the full path to the executable. 32-bit System DSN’s will only appear in the 32-bit version of odbc32.exe and 64-bit System DSN’s will only appear in the 64-bit version  of odbc32.exe.  However, be aware that User DSN’s will appear in both versions. Please refer to the Microsoft support note for more details.  
 

Viewing a successful connection
——————————-
To see a successful connection, we can open our spreadsheet and select the “Refresh All” option on the Data tab.
The ODBC tracing logfile contains a large amount of information. An extract is shown below. (I’ve added comments pre-fixed by “#”, but you obviously won’t see these in the logfile).

…
e               8fc-dd0 EXIT  SQLAllocConnect  with return code 0 (SQL_SUCCESS)   # successfully connected to the database.
…
  WCHAR *             0×03B9F460 [      37] “SELECT * FROM “ODBC1″.”ODBC_TEST_TAB”"  # select statement to run against our table.
…

e               8fc-dd0 EXIT  SQLDescribeColW  with return code 0 (SQL_SUCCESS) # Description of column data within the table follows
  …
  WCHAR *             0×0013FA20 [       4] “COL1″                # Column name
…
  SQLULEN *           0×024FEA68 (40)                             # Column length      
…
e               8fc-dd0 EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)  # Successfully executed SQL select statement
…
e               8fc-dd0 EXIT  SQLFetch  with return code 0 (SQL_SUCCESS) # Successful fetch of data from the first row in our table
…
e               8fc-dd0 EXIT  SQLFetch  with return code 0 (SQL_SUCCESS) # Successful fetch of data from the second row in our table
…
e               8fc-dd0 EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND) # No more data to be fetched from the table - there are only 2 rows.
…
e               8fc-dd0 EXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)  #Disconnect from the database
 

Viewing an unsuccessful connection attempt due to an incorrect password
————————————————————————
Here we refresh our spreadsheet again, but deliberately use an incorrect password:

Excel error messages:

[Oracle][ODBC][Ora]ORA-01017:invalid username/password; logon denied
[Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed

ODBC Trace file error messages:

  DIAG [28000] [Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
 (1017)
  DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)

Wrong Database Service name specified
————————————-
We refresh our connection, but put an incorrect entry in the “Service Name” box:

Excel error messages:

[Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred
[Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed

ODBC Trace file error messages:
  DIAG [S1000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred (12170)
  DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)

Listener is down
—————-
We shut down the listener and then refresh the spreadsheet. Excel error messages:

[Oracle][ODBC][Ora]ORA-12541: TNS:no listener

ODBC Trace file error messages:

DIAG [S1000] [Oracle][ODBC][Ora]ORA-12541: TNS:no listener (12541)
DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)
 

Database is down
—————–
Startup the listener and shut down the 11g database. Refresh the Excel spreadsheet:
Excel error messages:

[Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ODBC Trace file error messages:

DIAG [S1000] [Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (12514)
DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)

Note: You might think, why bother tracing ODBC if you get the error messages in Excel anyway?  That’s fine, but the main purpose of this post is to illustrate that if you are using an application which doesn’t supply detailed messages, you may be able to find out the cause of any issues by turning on ODBC tracing.
 

Turning off ODBC Tracing
————————
To stop tracing, you can then just open up the ODBC Data Source Administrator as before - go to the “Tracing” tab and click on the “Stop Tracing Now” button - Apply - OK. 
Note: Don’t leave tracing turned on permanently, otherwise it could fill up the local drive on the client. ODBC tracing can generate a lot of information. Remember to delete or archive old trace files.

Other references:
http://support.microsoft.com/kb/268591/EN-US

Datafiles
select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;
That’s fine to start with, but you should also include tempfiles, which are used when an operation such as a large sort is too big to fit into the relevant memory allocated to the session.
Tempfiles
select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;
 

Your redo logs can also use up a large amount of disk space - especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).
 

Redologs
select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;

The database obviously needs controlfiles to record information such as which datafiles belong to the database.  If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.

Controlfiles
select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;

From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time. 

Flash Recovery Area
select * from v$recovery_file_dest; 
select * from v$flash_recovery_area_usage;  

These views will show sizing details and free space available. 

Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).

If you are using RMAN incremental backups and have block change tracking enabled, then include this file:

Block change tracking file
select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;
 

Files referenced by database directories or the utl_file_dir parameter
Your application may read from, or write to external files via database directories or the utl_file_dir parameter.

Other examples of using external directories are for

(a) External tables -

select a.owner||’.'||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from
dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;
(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).

select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;
 

Miscellaneous files
There are a large number of files which you could also include in your sizing if you wanted to. Though most of these are really external to the database.
Examples include:

(a) The spfile/pfile and any ifile referenced files.
(b) Any external scheduler jobs (i.e. program_type=’EXECUTABLE’ and program_action which points to a shell script).
(c) Configuration files such as Oracle wallet files and database gateway/hs services files.                   

(d) Oracle networking files. (e.g. tnsnames.ora, sqlnet.ora, listener.ora)
(e) Passwordfile.
(f) Any application code that needs to be deployed to the database server.
(g) Any database management or monitoring scripts that need to be on the server. 
(h) Files referenced by the audit_file_dest parameter, if audit_trail is set to use the “OS” or “XML” options.
(i) Archived redo logs and standby redo logs. Be aware of space usage related to the workload of your database and whether or not you have multiple destinations defined.
(j) Any software that needs to be deployed to the server. (e.g. The oracle software itself takes up several gigabytes of space).

If you wanted to be very precise, you could remove unused space from the calculations above, but I haven’t done that here, in order to keep things more straightforward. In real-life you would probably be better leaving the extra space available to allow for future growth of the database - which you should also plan into your size calculations.

As you can see, calculating size requirements for an Oracle database is not always as simple as you would think.  I’ve tried to include all elements in this post, though in reality a lot of them won’t apply to most databases. Please feel free to share any other items not on the list, that you feel should be included.

Whilst in many cases this may be true, it’s always a good idea to check your scripts from time to time.  If you don’t, then you could miss some of the following issues:
 

Recoverability
————–
1) When did you last test that you can actually restore from your backups?  (e.g. Are you running test restores, or using the RMAN validate commands?) 

2) Does your script look for logical corruptions?  (RMAN will pick up physical corruptions by default, but you need to run specific commands to detect logical corruptions).
 

Space Issues
————
3) Do you review available disk space before running backups?  Do these take into account additional disk space requirements if the backup files are compressed?  (i.e. They may take up less space once compressed, but the initial compression operation will temporarily use a lot more space). 
4) Are you looking at the size of your backup files? (e.g. If running a cold backup have you run out of disk space?  Do any file transfer tasks complete successfully?)
 

Backup Timings
————–
5) Are your backups over-running into the on-line day and affecting database performance for business users? 

Are they running at a time when you have an overnight batch job running? 

As the database size and its workload increases over time, you’ll need to adjust your timings accordingly.
6) If you have a backup to disk, followed by a backup to tape or a lower-level of tiered storage, is this still working correctly?  (i.e. You don’t want to have your disk backup still running when the secondary backup starts, otherwise you could experience various performance and corruption issues). 

If your tablespaces are being put into backup mode to take a hot backup or snapshot of the database, ensure that this change is still happening at the correct point in time.
 

Error checking
————–
7) Are you searching for the correct error messages?   Is it OK to just look for ORA- errors or would it be advisable to also check for other messages such as “warning”, “unsuccessful”, “usage”, “error”, “RMAN-” etc.
8) Are you aware of the date and time of your backup logs?  Just doing a tail of the last few lines of the log without checking this, could mean that you miss any new errors.
 

Backup Options
————–
9) Unless you’re using Oracle-aware software (i.e. Oracle agents) to backup an online database, then is there any point in backing up database files?  Should you just be backing up your backup files.  (If you’re not using oracle-aware software, then backups of open database files will be unusable - though you may not see any errors).

If you’re using a tool that is Oracle-aware, are you definitely backing up all the database files.  It’s easy to miss out a tickbox in a GUI tool and find out later that you have an incomplete backup.

10) Are you using the latest backup and recovery features?   Has your script been optimised in terms of performance and functionality? Could it be:

(a) Faster,  so that it has less of a performance impact? 
(b) Smaller to save on storage costs?    
(c) More secure? 

11) Would it be easier to use separate version-specific scripts, so that it’s easier to use and test new features?  One large script for all releases can become very complex over time.  Issues can take longer to resolve.

 Record Keeping
—————
12) Are you maintaining a log or history of backups and their contents?  This is straightforward if you’re using an RMAN recovery catalog, or backing up to some storage or backup management software. Even something as simple as a spreadsheet could be used to record a history of your backups.
 

These are just a few things to think about when checking your backups.  As a DBA, backups should be your number one priority. If you can’t restore from them in a DR situation, then you may find that your number one priority suddenly becomes finding a new job!

Obviously there will be some users - such as developers or testers - who require connection strings that shouldn’t be available to ordinary users.  One way of getting around this issue is the following:

(i) Point the TNS_ADMIN variable on the local client to a shared local tnsnames.ora file.
(ii) Within the shared tnsnames.ora file, add an ifile entry which points to a shared location on a mapped network drive. 

Using this method, clients have access to the shared information on the network drive, but are also able to add their own private connection strings to the local file. To create an ifile entry you just add a line similar to that shown below to your tnsnames.ora file:

ifile=U:\SHARED_TNS\tnsnames.ora 

Note: There should be no spaces before or after the “=” sign, otherwise SQLDeveloper can have issues connecting.

In this example, we have specified a tnsnames.ora file on the U: drive.  The file can be called anything that you like.  It is also possible to have several ifile entries in your local tnsnames.ora file, if you want to sub-divide access to selected databases. It is advisable to set permissions on the remote file to read-only, in order to prevent clients using GUI tools such as Net Configuration Assistant or Net Manager from over-writing the file.

You should be aware that where you place the ifile entry is important.

If you place the ifile entry at the start of the local client tnsnames.ora file, then a duplicate entry in the local file would take precedence over an entry in the ifile-referenced file, as the entry in the local file would be read last.

If you place the ifile entry at the end of the local client tnsnames.ora file, then a duplicate entry in the ifile-referenced file would take precedence over an entry in the local tns file, as the ifile entry would be read last.
 

Disclaimer:

(a) Having a central tnsnames.ora file may reduce support calls and reduce the administrative problems of maintaining several files, but some sites may view this as being a central point of failure - especially if the clients are pointing to a tnsnames.ora file on a remote network drive. To resolve this, you could have another copy on another network drive which is updated regularly.
(b) Always test that the ifile setting does not have an adverse effect on client connections to the database. (Some Java clients may experience issues when using this parameter). 
(c) You can experience connection issues if you have a session open and you edit the ifile or tnsnames.ora file without exiting your session.
(d) If a shared tnsnames.ora file is not suitable for your needs, then check the Oracle documentation for other methods to identify Oracle database services. (e.g. Oracle Names Server, Oracle Internet Directory etc.)

A common problem found at many sites is having Windows clients with several Oracle products installed.

If each installation uses its own tnsnames.ora file to identify the database services that the client can connect to, then users often experience connection issues. 

If a new database service is added, it’s not always clear which tnsnames.ora file(s) need to be updated.

Some sites get around this problem using scripts or batch files. Only one tnsnames.ora file is updated and then it is automatically copied to the other locations on the client.  Other sites just update each file manually.

A better method is to use the TNS_ADMIN environment variable.  Setting this variable to point to the directory of your choice ensures that all Oracle installations use the same copy of the tnsnames.ora file.

Note 111942.1 describes this as: 

“The TNS_ADMIN parameter/environment variable is used to explicitly define the directory location for Oracle Net to resolve where to find its configuration files. TNS_ADMIN over-rides the default Oracle Net location.”

To add this setting for an Oracle 10 client running on Windows XP, all you would need to do is:

Right-click on “My Computer” - Properties - Advanced - Environment Variables - Under the “System variables” section - New -
In the “Variable Name” box type - TNS_ADMIN
For the “Variable Value” box type -  C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN  - OK - OK

Note: The location can be on a local or remote network drive. Just specify the directory that contains the file - not the name of the file. 
 

The Oracle client will search for the tnsnames.ora file in the following locations and order:
(i) The current directory. (i.e. When running from a Windows command prompt, if there is a tnsnames.ora file in your current directory, then this will be referenced first).

(ii) A location specified in your session (e.g. SET TNS_ADMIN=C:\SHARED_TNS )  or within a script. 

(iii) A global environment variable (i.e. setting TNS_ADMIN via My Computer).

(iv) A registry key entry. (e.g. You could use regedt32 to create a new String Value called TNS_ADMIN under the Oracle home branch. (see Note 290124.1 for further details).

Setting the TNS_ADMIN environment variable is a better solution to our issue than using a registry key entry, as we would need to create a registry key under every ORACLE_HOME on the client.  Note 1020336.102 mentions that

“Different versions of Oracle client software will not read a variable defined within another version’s HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key”

(v) %ORACLE_HOME%\network\admin  (or %ORACLE_HOME%\net80\admin for an Oracle 8.0 client).

Once you’ve configured the TNS_ADMIN variable, you can just type tnsping service_name  (e.g. tnsping ORCL10)  and then you should see an “OK” message.

If it’s still not working then check on Oracle Support, as there are various issues which could be affecting the connection (e.g. NAMES.DIRECTORY_PATH, NAMES.DEFAULT_DOMAIN and SQLNET.AUTHENTICATION_SERVICES settings in your sqlnet.ora etc.).

Also check that you don’t have duplicate tns connection string entries in the file.  If you do, then the last entry in the file is the one that will be used. 
Note:  If you have a command prompt window open, it won’t pick up the new setting dynamically - you’ll need to close it and open up a new window. 

Just type echo %TNS_ADMIN% and you should see the new setting.

You may not want all users to have access to every connection string that is available, so in part 2 of this blog, we’ll discuss how to deal with this situation.
Disclaimers:

(a) You should double-check that each of the Oracle products installed on the client still works correctly when using a central file. Some oracle applications require their own settings, which may not be compatible with other oracle installations.  As with any changes, you should always test any changes before rolling them out to clients.

(b) When un-installing Oracle software, you should un-set the TNS_ADMIN variable if it points to a shared location. Some of the GUI tools (e.g. 10g Net Configuration Assistant) could remove files specified at this location.