Tricks and traps with Oracle (as a source or target) on 64 bit servers 11 Nov ’09
Posted by: Mark Nolan
Installing RED on a Windows 64-bit server, with Oracle as a repository or source system
When installing RED on 64-bit Windows, the installer selects the following installation folder by default:
C:\Program Files (x86)\WhereScape\
This is where 32-bit applications normally reside on a 64-bit Windows platform.
However, if Oracle is involved, either as the data warehouse repository, or as a source system, installing RED in “Program Files (x86)” causes a problem.
The problem is that Oracle doesn’t like directories that have a parenthesis in the name - applications using Oracle cannot be located in a directory with parenthesis in the name, and this includes RED using an ODBC connection to Oracle. The result…RED will not be able to connect to Oracle.
This issue can be prevented by changing the installation directory of RED to:
C:\WhereScape
For more information about this Oracle issue, see
64-bit SQL Server data warehouse repository with Oracle as a source system
If you are running a 64-bit SQL Server data warehouse, and need to connect to one or more Oracle source systems, you will require both the 32-bit and 64-bit Oracle Clients installed on the 64-bit Windows Server:
1) 32-bit Oracle Client
The RED client is a 32-bit application, and requires a 32-bit ODBC source to connect to an Oracle database.
The 32-bit Oracle Client is required so you can set up a 32-bit ODBC source using the “Microsoft ODBC for Oracle” ODBC source.
2) 64-bit Oracle Client
For better data load performance, create a SQL Server “Linked Server” to connect to an Oracle source system.
The RED scheduler will use the Linked Server for the data loads.
64-bit SQL Server requires the 64-bit Oracle Client and ODAC (Oracle Data Access Components) to create the Linked Server - these are both included in the Oracle Database 10g Release 2 Client (10.2.0.4.0) for Vista and Windows 2008 (64 bit).
For installation instructions for the 64-bit Oracle Client, and setup of the Linked Server, see
http://www.mssqltips.com/tip.asp?tip=1433
We have tested this setup using the following software versions:
64-bit SQL Server 2005 as the DW repository on the 64-bit Windows Server
32-bit Oracle 10g Client installed on the 64-bit Windows Server
64-bit Oracle 10g Client installed on the 64-bit Windows Server
Oracle 9iR2 as a source system
Oracle 10g as a source system
