Please note: This file has all references to the original application and the company name replaced with XXXXX. Some parts of this file are very application specific (like the Config.INI file and modifying httpd.conf), other parts apply to all applications using DBI and DBD::Oracle.
Author: Alexander Foken
CVS ID: $Id: Oracle\040Troubleshooter\040HOWTO.html,v 1.8 2005/08/17 13:24:25 afoken Exp $
Experience from various installation shows that you should expect trouble with an Oracle "default" installation. So to avoid endless trouble shooting, check the following conditions after the database (client) has been installed, but before installing XXXXX. If you have worked though this HOWTO, using Oracle for XXXXX will be painless.
connect failedScan all local drives for OCIW32.DLL, using the Windows builtin file search engine (Start -> Search -> For Files and Folders or just press Windows - F).
If there is no OCIW32.DLL at all, you need to install at least the Oracle client software. Install the Oracle ODBC drivers, they are known use this DLL.
If there is more than one OCIW32.DLL, some other application has installed a (probably incompatible) version of this DLL, usually in C:\WinNT\System32. Rename that DLL to OCIW32.DLL-broken. The "good" DLL is usually the one installed in the Oracle directory. Some applications may have a private OCIW32.DLL in or below the application's directory, e.g. C:\Program Files\Pretty App\OCIW32.DLL. Those DLLs are usually harmless, unless the directory containing the DLL is listed in the PATH environment variable.
There are ancient versions of OCIW32.DLL, usually installed by third-party applications, that are not compatible with Oracle 8 and newer. Make sure to use the OCIW32.DLL that came with the Oracle client software. See also above.
Due to Windows internals, the directory containing OCIW32.DLL must be listed in the PATH environment variable. And as services usually do not use the account you use to login to Windows, you need to change the system-wide PATH settings. The location of this setting varies with the Windows version. For Windows 2000, open Control Panel -> System -> Advanced -> Environment variables and edit the system variables. Example: Change C:\WINNT;C:\WINNT\SYSTEM; to C:\ORANT\BIN;C:\WINNT;C:\WINNT\SYSTEM;).
Reboot after changing the environment variables for services.
NEW: XXXXX v2.4.2 introduced a section [OracleEnv] in Config.INI, setting the ORACLE_HOME environment variable automatically when needed. Before changing the system's environment, edit the setting in Config.INI.
Some Oracle installations work fine without ORACLE_HOME, some don't. Setting ORACLE_HOME does not hurt. The value of ORACLE_HOME is the base directory of the Oracle installation. You usually can derive it from the location of OCIW32.DLL, it is ORACLE_HOME\bin\OCIW32.DLL.
Setting ORACLE_HOME is very similar to changing the PATH variable. Please see above.
Reboot after changing the environment variables for services.
This is not strictly required, Oracle does a great job at backward and forward compatibility. But don't beg for trouble, make sure to install the client software that came with the server software, especially don't mix different major versions.
Depending on the Oracle version, this file is located either in the directory ORACLE_HOME\net80\admin ("old" Oracle versions up to 8) or in the directoryORACLE_HOME\network\admin ("new" Oracle versions from 8i and newer). Use the windows file search engine to find this file by name. This plain text file should contain the alias used in Config.INI [Database] DBI_STRING. To check this file for non-obvious errors, ask an Oracle Administator.
A typical TNSNAMES.ORA file (does not work unmodified on machines outside the XXXXX network):
# TNSNAMES.ORA Network Configuration File: C:\orant\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
# hacked by Alexander Foken
BRAINDB.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasun)(PORT = 1521))
)
(CONNECT_DATA =
(SID = BRN)
)
)
AFOKEN.XXXXX.NET =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = afoken)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = afoken.XXXXX.net)
)
)
Recent versions of Oracle feature SQLNET Authentication Services, but most times (especially but not only with Personal Oracle), those services are not installed. Nevertheless, the Oracle installer enables their use. This is a bug in the Oracle installer.
In the plain text file sqlnet.ora (located in the same directory as TNSNAMES.ORA, i.e. ORACLE_HOME\net80\admin or ORACLE_HOME\network\admin), you may find the following line:
SQLNET.AUTHENTICATION_SERVICES= (NTS)
Remove that line or prefix it with a comment sign, so that it read like this:
# SQLNET.AUTHENTICATION_SERVICES= (NTS)
Save that file. You may need to restart Oracle if it runs on the same machine. Restarting the entire machine may take longer, but it is easier to do.
PLEASE NOTE: There may be Oracle installations where the NTS Authentication Service is required. But for such installations, you can expect that the local Oracle administrator knows that it is required.
You get an error message like this when you want to use XXXXX:
'Menu': Could not load module 'XXXXX::Functions::Default': Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:A dynamic link library (DLL) initialization routine failed at C:/Perl/lib/DynaLoader.pm line 200.
at D:/XXXXX/Modules/XXXXX/Database/Oracle.pm line 18
Compilation failed in require at D:/XXXXX/Modules/XXXXX/Database/Oracle.pm line 18.
BEGIN failed--compilation aborted at D:/XXXXX/Modules/XXXXX/Database/Oracle.pm line 18.
Compilation failed in require at D:/XXXXX/Modules/XXXXX/Database.pm line 71.
[...]
The error message is technically correct, but it hides the real problem: The DBD/Oracle/Oracle.dll works well, but it can't load the proper Oracle Call Interface DLL OCIW32.DLL. Either there is not exactly one OCIW32.DLL, or the PATH environment variable is not set correctly. See above.
You get an error message like this when you want to use XXXXX:
Software Error 'Admin.Specialtask.Menu': Could not load module 'XXXXX::Functions::Admin::Specialtask': Unknown error Compilation failed in require at d:/cvshome/XXXXX/XXXXX/cgi-bin/../Modules/XXXXX/HTMLTools/CGIext.pm line 64. BEGIN failed--compilation aborted at d:/cvshome/XXXXX/XXXXX/cgi-bin/../Modules/XXXXX/HTMLTools/CGIext.pm line 64. Compilation failed in require at d:/cvshome/XXXXX/XXXXX/cgi-bin/../Modules/XXXXX/Functions/Admin/Specialtask.pm line 24. BEGIN failed--compilation aborted at d:/cvshome/XXXXX/XXXXX/cgi-bin/../Modules/XXXXX/Functions/Admin/Specialtask.pm line 24. Compilation failed in require at (eval 4) line 2. BEGIN failed--compilation aborted at (eval 4) line 2.
Check Config.INI [Database] DBI_STRING. The correct spelling is dbi:Oracle, not dbi:ORACLE!
The problem here is that you tell Perl - which is case sensitive - to load the module DBD::ORACLE from the file C:\PERL\site\lib\DBD\ORACLE.pm. Windows opens the file C:\PERL\site\lib\DBD\Oracle.pm, that file contains a module named DBD::Oracle, but no module named DBD::ORCLE. So Perl can't find the required module even though the file could be read without problems.
connect failedPossible reasons:
Run the script DBIReport (open D:\inst\1st\bin\scriptmenu.cmd and choose DBIReport from the Run Script menu). Compare the names of the preconfigured Oracle database(s) with the Config.INI [Database] DBI_STRING setting. Names not listed there are usually not available. The setting is case sensitive.
Run the script DBITest (open D:\inst\1st\bin\scriptmenu.cmd and choose DBITest from the Run Script menu). Use the defaults arguments. If you can connect from DBITest but not from XXXXX, you need to change the Apache configuration. Open C:\Program Files\Apache4XXXXX\conf\httpd.conf and append the following line to the end of the file:
PassEnv ORACLE_HOME
Save the file. Restart the Apache service(s) by typing the following commands into a DOS box:
net stop apache net stop apache-ssl net start apache net start apache-ssl
You need to repeat this steps after every new XXXXX installation on that machine, because the installer re-generates httpd.conf from the master configuration file.
It is recommended to protect the file after all installation has been done. Open the file. Ignore the warning not to change that file. The very first line should read:
##ConfWriter:ItIsOkToOverwriteThisFile##
Change it so that it looks exactly like this:
##ConfWriter:ItIsNoLongerOkToOverwriteThisFile## Added PassEnv ORACLE_HOME
Please see NTS Authentication Service must be disabled in a default installation.