How to install Oracle 9 client and ODBC on Windows 2000 and XP

Make sure you have rights to install software on your PC before you start.

• Click on the Install/Deinstall option which appears when the CD is loaded. You can check what is installed at this stage.
• On the File Locations screen, accept the Oracle Home name as Oracle90 and suggested path C:\oracle\ora90. If you have two hard disks, accept the drive it wants to use since the installation will fail otherwise.
  Note that some stuff will get put into Program Files though and there seems to be no way of avoiding this.
• Note that Oracle documentation is held on a separate CD-ROM.

Available products are now loaded

YOU CAN USE QUICK SEARCH ENGINE IN THE TOP FOR MORE DOWNLOADS OR DETAILS.

Types of installation

There are 3 types of installation:

• Administrator : management console, management tools, networking services, utilities, basic client software (647Mb). Not relevent unless you want the database administrator tools
• Runtime : tools for developing applications, netwoking services, basic client software, networking services and basic client software (486Mb). Get too many tools!
• Custom: choose own components- best option as you get more control over installation

Custom Installation

If you just want ODBC,choose Custom and then deselect the options in the correct order (if you deselect in the wrong order you will not be able to deselect ones you do not want).

• deselect Oracle HTTP server
• deselect Workflow client
• optionally select/deselect Windows Documentation
• deselect Oracle Internet Directory client (this must be done after deselecting HTTP)
• deselect Enterprise manager (this is only for database administrators)
• make sure Net services is ticked
• make sure Universal installer is ticked so that you can check what is installed
• make sure the Programmer component is selected, expand and make sure ODBC is ticked, and tick whatever else you need. You are unlikely to need the Cobol and C options. Oracle provider for OLEDB goes together with Oracle Objects for OLE and is unlikely to be used, as is the object type translater and the call interface. XML and XML SQL also go together (only select them if you want to use XML).
• make sure you select the security option for network security
• select net9 client. This will install:
  • Net9 client (to talk to remote Oracle database)
  • Net9 Assistant (
  • Net9 Configuration Assistant
  • Sqlplus (to be able to type in sql commands)
• Sit around for about 10 minutes while it installs loads of products including JDBC.
• It should then get to the configuration assistant and run it. Sometimes you just sit there like an idiot wondering why nothing is happening while being told that the network assistant is running. If this is the case, press Alt/tab and select the java coffee cup- for some reason this is not shown on the open programmes panel at the bottom of the screen.

Oracle Network Configuration Assistant

The assistant will take you through the configuration either for Directory Usage Naming Methods or you can choose to perform a typical configuration.

Leave typical configuration unticked and select next.

You are then given the choice to complete the configuration now or defer.
Answer No.

oracle/ora90/network/admin/ folder, renaming any default sqlnet.ora file if it exists. This is the recommended thing to do.
The sqlnet.ora file looks like:

# C:\ORANT\NET80\ADMIN\SQLNET.ORA Configuration  File:C:\ORANT\net80\admin\sqlnet.ora # Generated by Oracle Net8 Assistant  NAME.DEFAULT_ZONE = world  #sqlnet.authentication_services = (NONE)  SQLNET.EXPIRE_TIME = 0  NAMES.DIRECTORY_PATH= (ONAMES,TNSNAMES, HOSTNAME) #names.directory_path = (TNSNAMES)  NAMES.DEFAULT_DOMAIN = bris.ac.uk #NAMES.DEFAULT_DOMAIN = world  NAMES.PREFERRED_SERVERS =    (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = oranamesrvr0.bris.ac.uk)(PORT = 1532))      (ADDRESS = (PROTOCOL = TCP)(HOST = oranamesrvr1.bris.ac.uk)(PORT = 1575))      (ADDRESS = (PROTOCOL = TCP)(HOST = oranamesrvr2.bris.ac.uk)(PORT = 1575))      (ADDRESS = (PROTOCOL = TCP)(HOST = oranamesrvr3.bris.ac.uk)(PORT = 1575))      ) AUTOMATIC_IPC = OFF #TRACE_LEVEL_CLIENT = OFF 

You can pick up the tnsnames.ora file (if you find you need it- for example if you get unrecognised service name) from a previous installation or from Maggie Shapland and paste it into the newly created oracle/ora90/network/admin/ folder making sure the old tnsnames.ora that was created by default if you did not exit, is renamed first. For each connection, the entry looks like (where datasource, machine_address, and database_name must be specified):

 datasource =   (DESCRIPTION =     (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = machine_address)(PORT =1521))     )     (CONNECT_DATA =       (sid = database_name)     )   )  

eg

 hprod.bris.ac.uk =    (DESCRIPTION =      (ADDRESS_LIST =          (ADDRESS =            (COMMUNITY = tcp.bris.ac.uk)           (PROTOCOL = TCP)           (Host = sunshine.bris.ac.uk)           (Port = 1521)         )     )     (CONNECT_DATA = (SID = hprod)     )   ) 

If you really want to carry on with the network configuration, you are given the choice to complete the configuration or defer. Make sure you tick No at this stage

On naming methods configuration, choose host name

The version of Oracle is 8i or later

The service name is the name of the database

the network protocol is tcp

the hostname is the name of the server- which should be suffixed with .bris.ac.uk

the port number is 1521

At this stage you can perform a test, but be aware the default test username/password is scott/tiger which is not installed in this database, so try typing the username, password and service name you have been given

This will create tnsnames.ora and sqlnet.ora, but the domain name will have an extra suffix of your department, so rename these two files and copy in the files you have been given. You can use Notepad to edit

Connection Timeout

You are now ready to stop the connection being timed out after the default 10 minutes.

Run regedit as a Start command. This will edit the registry

Find connectiontimeout

Choose Modify and show decimal

Change 600 to 0 (so does not disconnect) or to something bigger (the number represents seconds)

Testing the connection

select the DOS command prompt from Accessories

Type tnsping hprod
(one of the Bristol databases, otherwise type the name of one you want to test). It should identify the connection and confirm OK

Creating a Data Source

You need to create a data source to be able to use Access as a front end to Oracle

• Select Microsoft ODBC administrator from Oracle/Network Adminstration
• Data source types
  • Select the user DSN tab if the data source will only be used from your PC for the logged on username (Typically, entries in this tab are added by applications that are installed locally and that use a data source, such as Microsoft Office) else select the System DSN tab.
  • The System DSN tab allows you to configure a specific data source to a client-server database. Using the System DSN tab to configure a connection will write an entry for that connection in the registry in HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources. The advantage of this is faster access, since it is in the registry. The disadvantage is that it will have to be created on each local machine.
  • The File DSN tab does the same thing as the System DSN tab, but the File DSN tab will create a file stored on the local server with the extension .dsn on the local drive in the folder at C:\Program Files\Common Files\ODBC\Data Sources. The advantage of this approach is that the file can be easily copied to other machines.
• Press Add
• You will be asked which driver to use. Choose Oracle in OraHome90 NOT Microsoft ODBC for Oracle since this is the driver that you have just installed and will not cause problems such as described in ODBC PROBLEMES USE THE QUICK SEARCH ENGINE.
• Choose a data source name- can be the same as the data source service that you specified above eg xxx or perhaps the username to be used
• Choose a description eg xxx
• Give a data source service name. This must relate to the definition in the tnsnames.ora file eg xxx
• Give the default username for that datasource
• Click OK
• You can now test the data source by using ODBC test from Oracle/Network Adminstration
• Press Connect
• Choose a data source from the Machine Data Source tab
• Give the passwd for the given service name and username
• Type some SQL eg select table_name from user_tables
• Press the Execute button and hopefully some data will appear
• If something goes wrong, test the service name by typing tnsping xxx on the Command Prompt found in Program /Accessories on the Start menu to check the database is up
• The Connection Pooling tab allows you to enable and configure connection pooling for a specified driver. Connection pooling uses a connection to a data source from a pool of connections, so that a connection does not have to be reestablished each time you access the data source. Unless you are having trouble establishing or maintaining a connection, you probably will not use this tab. Connection pooling timeout is set to 120 second by default.

YOU CAN USE QUICK SEARCH ENGINE IN THE TOP FOR MORE DOWNLOADS OR DETAILS.