How to Configure the Oracle Client on the MDM Server

Document ID : KB000048004
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Following the steps in the Product Documentation to configure the Oracle client on the MDM server may not be enough for MDM to communicate successfully with the Oracle Database server. This document describes the steps required to prepare the Oracle client prior to the installation of MDM.

Solution:

On the Oracle database server, Create a user for the database. This user must have the following permissions and settings:

  • Role - Connect, Resource
  • System Privileges - Create Table, Create Trigger, Create View, Create Sequence, Create Procedure, Unlimited Tablespace.

To allow the planned CA MDM server to communicate with the Oracle database server, create a net service on the Oracle database server.

Install the Oracle database client on the planned CA MDM server in this order:

  1. Oracle Database 11g Client 32-bit

  2. Oracle Database 11g Client 64-bit

The reason for this is to have the Oracle 64 bit install\bin\ folder in the %PATH% statement first. This should be the result of installing in the order above.

Verify that the tnsnames.ora file contains the net service names that are mapped to the connect descriptors for the local naming method. By default, the tnsnames.ora file is located in the following directory:

 

 %ORACLE_HOME%\network\admin 

 

ORACLE_HOME specifies the directory where the Oracle database client is installed. In the CA MDM server, navigate to both the 32-bit and 64-bit Oracle database client installations.

The following is a snippet of the tnsnames.ora file:

 

 net_service_name specifies the net service name. For example, camdmdb_net_service.       
 net_service_name= 
 (DESCRIPTION=
 (ADDRESS=(protocol_address_information))
 (CONNECT_DATA=
 (SERVICE_NAME=service_name)))
 DESCRIPTION specifies the connect descriptor. For example, address, protocol, host, port, and service name information.       
 ADDRESS specifies the protocol address. The following is an example for the address description: 
 (PROTOCOL=tcp)
 (HOST=172.16.0.0)
 (PORT=1521)

 

CONNECT_DATA specifies the database service identification information. The following is an example for the connect data description:

 

 	SERVICE_NAME=camdmdb 

 

You may use the Oracle "netca" tool as well to configure the TNS Names on the client.

Verify connectivity from the MDM server to the Oracle database server using sqlplus.

For example:

 

 	sqlplus mdm/password@serviceName 

 

Register the following dll using these commands. In the example below, the "client_1" folder is the 32 bit location.

 

cd C:\app\Administrator\product\11.2.0\client_1\BIN
                execute : regsvr32  OraOLEDB11.dll
cd C:\windows\syswow64
execute : .\regsvr32.exe C:\app\Administrator\product\11.2.0\client_1\BIN\OraOLEDB11.dll

 

Reboot the MDM server and proceed with the installation of CA MDM.