Summary:
The Oracle Connections Manager project aims to provide a communications routing solution for ASU departmental applications that require connections to the remote EPM and OASIS oracle databases hosted by CMS, but cannot directly communicate with them. Upon successful testing this solution will replace the currently less efficient and more cumbersome Oracle database links solution.
The database links solution requires a local Oracle database to act as an intermediate server or hop between the departmental applications and the remote CMS-hosted databases. To run SQL scripts and access data on the remote databases, the departmental applications have to first connect and forward their SQL statements to the local database. The local database in turn parses the SQL statements, connects to the remote databases using the database links, gets the needed data from the remote tables, executes the SQL statements locally, and returns the results back to the applications.
Our experience has shown that the features of the Oracle database links make this routing solution restrictive and inefficient (performance-wise) for complicated queries without the creation of views or procedures in the remote and local databases. This in turn can make the management and support of database links, security, and the local and remote views and procedures complicated and time-consuming for staff members at ASU and for CMSC.
In order to hopefully resolve the aforementioned performance issues and simplify support, we are testing the use of Oracle Connection Manager as a replacement for database links. The definition, architecture and features of the Oracle Connection Manager can be found in Oracle documentation. Briefly, it is a router (consisting of three processes instead of a full database server for the case of database links) running on a local LINUX host. It relays the SQL statements and database communication between the departmental applications and the remote databases without any local database processing. The SQL statements are parsed and executed entirely by the remote database.
In addition, Oracle Connection Manager has other attractive features such as better network scalability and secure network access control than the database link solution for our purposes. It can multiplex or funnel multiple database connections or sessions across a single network connection, thus reducing network traffic between the applications and the remote databases. It can also act as a firewall proxy by limiting database access to specific hosts or IPs.
For the initial set up, the clients and applications only need to change their Oracle connection configuration to point to the Connection Manager in a UTO local server. They do not need local database accounts. The Connection Manager will then transparently route the connections and database communication to the EPM databases. However, access to the Oracle Connection Manager from departmental applications or local database clients and tools is subject to the local firewall rules in force for the development and production environment at UTO.
