KB43213: What is the current level of support for the use of MicroStrategy products with Oracle Real Application Cluster (RAC) as a warehouse?

Per Strategy's understanding, Oracle RAC involves three main features: 

1. Connection Failover - Essentially implies that the application should be able to connect to another node if a connection attempt on one node fails. This feature will be referred to as CF in the remainder of the document.
2. Load Balancing - Optimally distribute work load across various nodes. This feature will be referred to as LB in the remainder of the document.
3. Transparent Application Failover - If a communication link failure occurs after a connection is established, the connection is moved to another active Oracle RAC node in the cluster without the application having to re-establish the connection. This requires the application to re-initialize actions and the success of this feature depends on the type and complexity of the query, if the communication link failure occurs during the execution of a query. This feature will be referred to as TAF in the remainder of the document.

Strategy believes that TAF entails greater involvement on the application side, such as re-submission of the query, re-running of transactions that were rolled back, and other actions. Therefore, at this time, TAF support is not being considered by Strategy. The focus is on the first two features, CF and LB.
 
Connectivity to RAC
The ODBC driver plays an important role in facilitating connectivity to the RAC unit. In this document, the following two ODBC drivers are discussed: 

• Oracle ODBC driver (such as Oracle 10, or Oracle 11 driver)
• Strategy ODBC Driver for Oracle Wire Protocol

Oracle ODBC Driver
Using the Oracle ODBC driver, the application can directly connect to the Oracle Listener of the RAC unit. This listener then decides and determines the methodology for CF and LB. The connectivity to the listener unit is facilitated by specifying the Service Name (SERVICE_NAME parameter) in the tnsnames.ora file. Additional parameters may need to be specified in this file to explicitly instruct the listener to perform LB and CF. Such parameters include LOAD_BALANCE, FAILOVER, RETRIES and DELAY. However, users should note that this driver is not certified or supported for use with any Strategy products.
 
Strategy ODBC Driver for Oracle Wire Protocol
This driver is shipped with the Strategy product and it is the only certified driver for connectivity to Oracle. This driver is manufactured by DataDirect. For convenience, this driver will be referred to as the wire protocol driver in the remainder of the document. 
 
Connection Failover
The wire protocol driver can use the standard mode of connectivity i.e., providing server name, port number etc. to achieve CF, by specifying alternate servers in the connection parameters. This is the preferred method for achieving CF.  For simplicity, this approach will be called 'Alternate Server' approach. The wire protocol driver includes an option for providing names of alternate servers when creating a DSN with the standard parameters i.e., not using TNSNames.ORA, as shown below. A list of alternate servers can be specified in this text box. This is a client facilitated connection failover. Further information about this approach can be obtained from the following link: http://www.datadirect.com/resources/odbc/oracle-rac/failover.html
 
NOTE: It is strongly recommended to create a DSN for the Strategy ODBC Driver for Oracle Wire Protocol using the Strategy Connectivity Configuration Wizard . This tool is accessed from Start -> Programs -> Strategy -> Tools -> Connectivity Configuration Wizard. It is recommended that users not use the Microsoft Windows ODBC Data Source Administrator for creating DSNs for the Strategy drivers.

 
The wire protocol driver can also take advantage of the TNSNames.ORA file for facilitating connectivity to the RAC unit. When using the TNSNames.ORA, CF is provided on the server side and not from the client side. Users should specify the full path to the TNSNames.ORA file and the 'Server Name' referred to in the TNSNames.ORA file, when creating the DSN. 
 
 

 
 
The TNSNames.ORA file is supplied by Oracle's database administrator depending on the database and environment configuration parameters. The content of this file can be similar to the following:
 

Sample Code/Error

TEST_SERVER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ORASERVER1)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = ORASERVER2)(PORT = 1521))

    (LOAD_BALANCE = )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TEST_ORA)

    )

  )

• Use the client side approach: Connect to the Oracle server with standard parameters of Server Name, Port Number etc. and specify 'alternate servers' for connection failover. This is the preferred method.
• Use the server side approach: Let the Oracle RAC server  handle connection failover. As stated previously, do not specify a list of alternate node in the TNSNames.ORA file.

 
 
Load Balancing
When a DSN created using the alternate server approach is edited through the Windows ODBC Administrator, users have the option to enable 'Load Balancing' as shown below. However, it is not recommended to specify any load balancing parameter on the ODBC driver side (using either ‘Load Balancing’ check box in the ‘Failover’ tab in Windows or LoadBalancing parameter in odbc.ini) and is recommended to use the automatic LB provided by the Oracle RAC unit. The reason for this is load balancing achieved through this option is from the client side. The client cannot really achieve true load balancing since it does not have information about the exact load on the Oracle side. The best that it can do is to connect to the nodes either randomly or in a round-robin fashion (by turn). For more information regarding how this option works, refer to the following link: http://www.datadirect.com/resources/odbc/oracle-rac/load-balancing.html
 

 
 
For Load Balancing, it is recommended to use the server side LB provided by the Oracle RAC unit. The Oracle RAC unit Listener facilitates this automatically and the DataDirect driver can transparently take advantage of it. 
 
 
 
Miscellaneous Points 
In general, Oracle RAC is a complex unit involving several features as described in the document. Also, based on testing experience, optimal working of RAC features is environment specific. For this reason, it is difficult to give a Boolean answer such as whether RAC is supported or not supported for Strategy products. It is necessary to fully understand the RAC implementation before deciding what driver and what approach works best for a specific environment.
 
 
 
 
 
Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.