Problem Summary
How do I connect HVR to an Oracle database?
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 should be used if possible i.e. if HVR runs on the database server(s). The way to connect in this scenario is to provide the path to the ORACLE_HOME out of which the database runs and the SID. DBAs will typically know what these values are.
To get to the ORACLE_HOME you can use the remote browse capabilities in the HVR GUI. Note that the HVR OS user must have access to the location of the ORACLE_HOME in order for this connection to succeed. Make sure to use the ORACLE_HOME that the database runs out of, and not some other ORACLE_HOME e.g. 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 name will cause the connection to fail.
In an Oracle RAC setup, it is strongly recommended you use the SCAN listener (Oracle Clusterware 11gR2 introduced this feature) in HVR with the ability to run HVR on every node, maybe because HVR was installed on every node, or because the software was installed on a shared disk accessible on every node. In a RAC setup, the HVR remote listener must be running on the same port (e.g. 4343) on every node. When using the SCAN listener HVR does not know ahead of time which node in the cluster it will be connected to and initiate the connection to one of the nodes based on the response from the SCAN listener. From there HVR will connect to the database using the service name that is provided as part of the connection information. For Oracle RAC this is the preferred way to connect 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.
In some cases however you may be connecting to the Oracle Database using TNS. For example:
- 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 a currently required workaround to use the Oracle Instant client.
The connection to the Oracle Database will be using:
- Environment variable TNS_ADMIN to find the required files to connect to the Oracle Database, starting with sqlnet.ora, specifying the 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.
Note that TNS_ADMIN may have to be provided as an environment variable in order for HVR to use it. This can be done in multiple ways:
- As part of the environment when running the HVR remote listener.
- In the connection dialog when connecting to the hub database.
- Using an Environment action in the context of a channel.
Please refer to the Oracle Documentation for more details on how to construct a connection string.