Build and test plan for Oracle database full client in-place upgrade
Purpose of this document
This document is to provide technical instructions to install Oracle full client 11.2.0.1.0 32-bit to Windows XP 32-bit and Windows 7 64-bit. The only components selected during the Oracle full client installation are SQL plus and ODBC drive. These components are at the same version.
Change log26-Sep-2012, kz, version 5.0. Added navigation path to start the Oracle Universal Installer. Added detailed information on Oracle user accounts.19-Sep-2012, kz, version 4.0. Added a note on page 7 to grant local admin rights to the users.08-Aug-2012, kz, version 3.0. Reviewed, verified and executed the build plan with Client Support and Constructions IT group. Made the following updates: updated registry entries. Included the silent install command in a DOS batch file. Moved the install directory to O:\staff\Common\data\Client Services Public\Oracle Client\ora11g full client\client. Client Support has full access and other department IT support groups have read and execute privileges to the directory and its content.27-Jul-2012, kz, version 2.0 reviewed with Char. Confirmed with Paul K to test the build / test plan on Aug 8. Confirmed with Roxana that her Access application still works after the upgrade.25-Jul-2012, kz, version 1.0 reviewed with Char, Paul K and Darren.
25-Jul-2012, kz, version 1.0 reviewed with Char
24-Jul-2012, kz, version 1.0 created
Issue log
19-Sep-2012, kz, Roxana logged an issue. Normal windows users failed to initiate ODBC connections, after the Oracle full client 11.2.0.1.0 32-bit. The solution is to grant local admin rights to the affected users. The build plan was updated and sent to the TIPS: Productivity Tools Subcommittee distribution list on the same day.
Before you start
This section describes the pre-installation considerations and preparation steps prior to the upgrade.
- Log into Windows with local administrator privileges or as a member of the Administrators group.
- Most importantly, verify the database connectivity in ODBC first. Follow steps in Appendix A to start ODBC Data Source Administrator. Click on the User DSN or System DSN tab. Click on an existing ODBC data source that an end user is using. Click on the Configure button. Click on the Test Connection button. Enter Service Name, User Name and Password. All Oracle account access issues should be resolved before you start any work.
If you are doing the install for a technical verification purpose, verify or obtain Oracle user credentials first.
If you are doing the install for an existing Oracle user, ask the user to be available during the install or just obtain the user credentials from them prior to the install.
For resetting database user credentials, please open a Heat ticket with the following information (Helpline usually can reset the user password on the spot with the correct account information):
Database instance name (e.g., for saitdatamart Access app, it is using ‘prod’)
User name (e.g., for saitdatamart, it would be ‘<windows user name>_sdm’)
For database account creation request, please open a Heat ticket with the above account information and attach the access authorization form, have the ticket assigned to Computer Account Administrator in Heat.
- Make a backup of any important files located in ORACLE_HOME directory (C:\ora92) such as tnsnames.ora. You may need it to set up the new Oracle software later.
- Document the Oracle ODBC Data Source Administrator settings: User DSN and System DSN. Follow steps in Appendix A to start ODBC Data Source Administrator. Click on the User DSN and System DSN tab. Select each Data Source and then click on the Configure button. Take note of any relevant information.
- 32-bit of Oracle database client is required for both 32- and 64-bit Windows environments. The full path of the new Oracle Home should not contain spaces or parentheses (C:\Program Files (x86)\oracle).
- This guide assumes the Oracle 9 database client has been installed to C:\ora92 on the local computer with Windows XP 32-bit installed.
- All instructions are tested against Windows XP 32-bit. Appendix B contains instructions for creating an ODBC connection in Windows 7 64-bit.
- If no database client previously installed, skip the Perform a clean uninstall of Oracle software section.
Quick install, configuration and test instructions
Perform a clean uninstall of previous Oracle software
Follow the instructions below to perform a clean uninstall of Oracle software.
Stop all running Oracle Services
Navigation: Start menu à Control Panel à Administrative Tools àServices.
Stop any services that are running with the name Oraclexxx, if found.
Remove Existing Data source Names
Remove all existing User DSN and System DSN entries in the Oracle ODBC Data Source Administrator.
Uninstall all Oracle client software using the Oracle Universal Installer
You should be able to remove all components except the installer itself.
Start menu àAll programs àOracle Installation Products à Universal Installer.
Remove Oracle entries in Windows Registry
Note: Please back up the Windows Registry before you make any changes. The Windows Registry entries that you work with may be different. If any entries below are not found, that would be okay. When in doubt, please open a Heat ticket and assign to OAS administrator in Heat including the full path to the entries and screen dumps.
Start Windows Registry. Navigation: Start menu à run à type regedit in the text box and hit Enter key.
Locate the remove the following entries:
HKEY_USERS\<random number>\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs\Oracle – OraHome92
On a computer with multiple users, you will perform the above step many times for all users.
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oraclexxx
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet00x\Services\Oraclexxx
As a catch-all routine, perform a global search using keyword ‘OraHome92’. Remove all entries with their names or values containing OraHome92.
There are typically two control sets, ControlSet001 and ControlSet002. Your environment may be different.
Exit Windows Registry Editor.
Remove environment variables
Check the environment variables, both user environment variables and system environment variables, and remove any oracle entries. Save the changes and exit the Control Panel.
Remove Oracle Home directory on the hard drive using Windows Explorer
For Oracle 9 client install, you can find c:\ora92 directory. Rename the Oracle Home directory and reboot the computer. This will unlock the oci.dll file within the directory.
Once booted, remove the entire Oracle Home directory in Windows Explorer.
Remove remaining Oracle directories showing in the Windows start menu. C:\Documents and Settings\All Users\Start Menu\Program Files
Perform the new Oracle full client install
To start the install of Oracle full client, start a DOS session and enter the commands below.
Start menu à run, type cmd and then hit Enter key. At the DOS prompt,
Enter the commands:
O: (hit Enter key)
“O:\staff\Common\data\Client Services Public\Oracle Client\ora11g full client\client\install” (hit Enter key. Yes, please include the double quotes.)
A secondary DOS screen appears indicating the install is in progress. This screen shows the location of log files. When the install is complete this screen disappears.
What is done behind the scene?
The second command executes a DOS batch file called install.bat which will start the setup.exe in silent mode.
Post-install
Post-install configuration
Follow the instructions below to verify the new Oracle software environment.
Prepare new directory structure
Navigate to C:\oracle\product\11.2.0\client_1\network and create a directory named admin. The full path to the new directory is C:\oracle\product\11.2.0\client_1\network\admin.
Copy the tnsnames.ora file to the local computer
tnsnames.ora file defines the database instances.
Copy tnsnames.ora file from source location:
O:\staff\Common\data\Client Services Public\Oracle Client\ora11g full client\client\tnsnames.ora
to
destination location:
C:\oracle\product\11.2.0\client_1\network\admin\tnsnames.ora
Post-install verification
Overall install of the Oracle full client
Upon a successful install, you can expect the following:
Go to the log directory.
e.g. C:\Documents and Settings\kzheng\Local Settings\Temp\OraInstall2012-07-26_02-45-18PM
You should not see any of the following log files with file extension of .err or .out. e.g.
installActions2012-07-26_02-45-18PM.err
installActions2012-07-26_02-45-18PM.out
You should see only one log file with .log file extension. e.g.
installActions2012-07-26_02-45-18PM.log
In the log file, you shouldn’t see any errors or warnings. Also, take note of the following settings:
- Install Type : Custom
- Oracle Base : C:\oracle
- Oracle Home location : C:\oracle\product\11.2.0\client_1
- Inventory location : C:\Program Files\Oracle\Inventory
You should have c:\oracle directory on the local computer. This directory is the new ORACLE_BASE which contains the 32-bit oracle client software 11.2.0.1 full client install with ODBC drive and SQL plus. All Oracle components are at the same version.
At this point, you should reboot the computer. After the reboot, proceed with the following steps.
SQL Plus
Note: The following is an example of SQL Plus verification. You may or may not have the same uat account for your end user. Please use the actual Oracle account applicable to your user’s environment.
Start a DOS session and execute the following command at the DOS command prompt:sqlplus <username>@<db_instance>Where <username> is the actual database user name (e.g. kzheng_uat) and <db_instance> is the actual database instance name (e.g. uat) you would like to log into. If you are going to use the saitdatamart account, the database instance name is prod.
If you get the following error message:
ORA-12154: TNS:cound not resolve the connect indentifier specified.
Refer back to the Copy the tnsnames.ora file to the local computer section to correct the problem.
ODBC
Note: The following is an example of ODBC configuration. It shows the steps in setting up a new Oracle ODBC Data Source in System DSN tab. You may or may not have the same uat account for your end user. Please use the actual Oracle account applicable to your user’s environment.
Follow steps in Appendix A to start ODBC Data Source Administrator. Click on the System DSN tab. Click on the Add button. The Create Oracle Data Source screen appears.
In Create Oracle Data Source screen, scroll down and select Oracle in OraClient11g_home1 and click on the Finish button.
The Oracle ODBC Driver Configuration screen appears.
If the TNS Service Name dropdown box doesn’t populate or contain the database instance name, refer back to the Copy the tnsnames.ora file to the local computer section to correct the problem.
In the ODBC Driver Configuration screen, enter the database connection parameters and click on the Test Connection button.
In the Oracle ODBC Drive Connect screen, enter the user credentials and click on the OK button. The Testing Connection screen appears indicating the connection was made successfully.
Click on the OK button to exit the above screen. Click on the OK button to exit the Oracle ODBC Driver Configuration screen. In the ODBC Data Source Administrator screen, you should see the new System Data Source you just added.
On the client computer, grant local admin rights to all intended Windows users so that they can work with Microsoft Access database applications via ODBC connections.
You have completed all the steps of Oracle 11g database client install. You can log off now. An end user can log into Windows and test their applications against the Oracle database.
Appendix A - How to start ODBC Data Source Administrator
The 32 bit ODBC driver framework and 64 bit ODBC driver framework are separate and distinct. To use the Oracle ODBC driver with a 32 bit application, the 32 bit driver and Oracle client need to be installed, and the 32 bit ODBC administrator must used to setup a 32 bit DSN.
To start the ODBC Data Source Administrator on different platforms, follow the steps below.
Windows XP 32-bit
Navigation steps: Start menu à Control Panel àAdministrative Tools àData Source (ODBC).
Or,
Start menu à Programs àOracle –OraClient11g_home1 àConfiguration and Migration Tools à Microsoft ODBC Administrator
Windows 7 64-bit
Click on the shortcut to C:\windows\syswow64\odbcad32.exe. (With the introduction of 64 bit Windows operating system, Microsoft provides the ability to run 32 bit applications via the SYSWOW64 subsystem.)
Appendix B - Example of creating an ODBC connection in Windows 7 64-bit (excerpt from Heat 9.5 User Guide)
Creating an ODBC Connection to HEAT 8.3 Archive Database
1. Navigate to ‘ODBC Data Source Administrator’ wizard following the steps for the version of the Windows Operating System on your computer:
a. Windows 64bit OS:
b. Browse to the ‘C:\Windows\SysWoW64’ folder.
c. Click on the ‘Odbcad32.exe’ file.
d. Windows 32bit OS:
e. Browse to ‘Control Panel\Administrative Tools’ from the ‘Start’ Menu.
f.
g. Click on the ‘Data Source (ODBC)’ icon.
The ‘ODBC Data Source Administrator’ window will be displayed.
h. Select the ‘System DSN’ Tab.
i. Click on the ‘Add’ button.
The ‘Create New Data Source’ window will be displayed.
j. Select ‘SQL Server Native Client 10.0’ from the driver list.
k. Click on the ‘Finish’ button.
The ‘Create New Data Source to SQL Server’ Wizard window will be displayed.
l. Enter ‘HEAT 8.5 Archive’ in the ‘Name:’ text field.
m. Enter ‘HEAT 8.5 Archive’ in the ‘Description:’ text field.
n. Type in ‘VAPP1640.acdm.ds.sait.ca’ into the ‘Server:’ dropdown menu (It is not visible for selection).
o. Click on the ‘Next’ button.
The ‘Create New Data Source to SQL Server’ window will display the ‘How should SQL Server verify the authenticity of the login ID?’ pane.
p. Select the ‘With SQL Server authentication using a login ID and password entered by the user.’ Radio button.
q. Enter ‘heatprod’ into the ‘Login ID:’ text field.
r. Enter ‘heat$prod’ into the ‘Password:’ text field.
Note: If prompted enter the same credentials for SQL Server authentication if prompted when you run the ‘Call Logging’ module for the first time.
s. Select the ‘Connect to SQL Server to obtain default settings for the additional configuration options.’ checkbox.
t. Click on the ‘Next’ button.
The ‘Create New Data Source to SQL Server’ window will display the ‘Change the default database.’ pane.
u. Select the ‘Change the default database to:’ checkbox.
v. Select ‘HEAT 8 Archive’ from the dropdown list.
w. Select the ‘Use ANSI quoted identifiers’ checkbox.
x. Select the ‘Use ANSI nulls, paddings and warnings.’ checkbox.
y. Click on the ‘Next’ button.
z. The ‘Create New Data Source to SQL Server’ window will display the ‘Change the default database.’ pane.
aa. Select the ‘Perform translation character data’ checkbox.
bb. Click on the ‘Finish’ button.
The ‘ODBC Microsoft SQL Server Setup’ window will be displayed.
cc. Click on the ‘Test Data Source…’ button.
The ‘ODBC Microsoft SQL Server Setup’ window will display the ‘Test Result’ pane - ‘TEST COMPLETED SUCCESFSULLY’.
2. Click the ‘OK’ button to close ‘SQL Server ODBC Data Source Test’ window.
3. Click the ‘OK’ button to close ‘ODBC Microsoft SQL Server Setup’ window.
4. Click the ‘OK’ button to close ‘Create New Data Source to SQL Server’ Wizard window.
Appendix C – About Oracle full client and instant client