How To Connect HVR to Oracle Database

Question

How can I connect HVR to Oracle database?

Environment

HVR 5

Answer

HVR uses Oracle's native connectivity to connect to the Oracle Database. Oracle's native connectivity has many options, and with that, there are many ways to connect HVR to the Oracle Database.

The most efficient connection to an Oracle Database is to connect locally to the database when running on the same server. Such a local connection bypasses the TNS listener and you should use it if possible, if HVR runs on the database server(s). In this case, you can connect by providing the path to the ORACLE_HOME from where the database runs and the SID. DBAs will know what these values are.

To get to the ORACLE_HOME you can use the remote browse capabilities in the HVR GUI.

NOTE: The HVR OS user must have access to the location of the ORACLE_HOME in order to have successful connection.

Make sure you use the ORACLE_HOME from where the database runs, and not some other ORACLE_HOME. For example, the one that is used for the Grid/ASM installation or if there are multiple installations of Oracle on the server.

The SID is case-sensitive on Linux/Unix environments and a typo in the SID will lead to connection failure.

In an Oracle RAC setup, we recommend you to use the SCAN listener (Oracle Clusterware 11gR2 introduced this feature) in HVR with the ability to run HVR on every node. We recommend it as HVR was either installed on every node, or because the software was installed on a shared disk accessible on every node. In a RAC setup, the remote listener runs on the same port (e.g. 4343) on every node. When using the SCAN listener HVR does not have prior information about the connection of the node in the cluster and initiates the connection to one of the nodes based on the response from the SCAN listener.

HVR will connect from there to the database using the service name that is provided as part of the connection information. For Oracle RAC this method of connection is preferable because it is independent of the availability of any one of the servers in the cluster, and if the cluster is expanded or shrunk then there is no need to make any changes to the HVR connectivity information.

However, in some of the following cases you can connect to the Oracle Database using TNS:

• The hub database schema is in a database on a different server.
• The connection is to an Oracle RAC that does not use the SCAN listener (typically an old cluster).
• The connection is to an Oracle Database service such as Amazon RDS (Relational Database Service).
• The system administrator or DBA does not allow the installation of any third-party software on the database server.

When HVR connects to the Oracle Database using TNS it still needs Oracle client libraries to connect. It is best to start with a regular client (or a full server Oracle home if it is available on the machine that is running HVR) to avoid an immediate workaround to use the Oracle Instant client. The connection to the Oracle Database can use the following:

• Environment variable TNS_ADMIN to find the required files to connect to the Oracle Database, starting with sqlnet.ora, specifying one or more ways the client can initiate a connection to the database and the order in which they are considered.
• Common entries in this file include TNSNAMES and EZCONNECT. If TNSNAMES is in the list then TNS_ADMIN is also where the connection will be looking for tnsnames.ora.
• ORACLE_HOME value to find within this in the directory network/admin sqlnet.ora, and if applicable tnsnames.ora.

You can provide TNS_ADMIN as an environment variable for HVR to use it. You can do it in the following ways:

• As part of the environment when running the HVR remote listener.
• In the connection dialog when connecting to the hub database.
• Using the Environment action in the context of a channel.

For more information on how to construct a connection string, see requirements for Oracle.