How may I troubleshoot the way my r9 mart connects to Oracle DBMS?

The user presses the Configure button on their Configure MartServer window and then receives a pop up window with messages, including Oracle error messages.

AND/OR they are doing a mart server upgrade and the martserverupgrade.log file has Oracle error messages within it.

1. Researching the Oracle error message usually provides the strategy to resolve.
2. r9 mart server and r9 erwin DM uses JDBC to connect to the underlying mart database.  The user does not have to install the JDBC as the mart server installation has already installed that for them.  The user can use Oracle SQL Developer to test their Oracle JDBC connectivity.  That is the purpose of this article. That test can help determine if there is an environmental problem connecting to Oracle.  This scenario is very similar to a problem where a software package can’t connect to a database and the reason is the user did not have client software installed or set up properly, or other environmental blockage.  It is recommended that the users DBA or help desk also be involved in this process.

Solution:

Use Oracle SQL Developer.

The martserverupgrade.log can contain informative clues about a mart server upgrade or installation.  That is usually located in the folder C:\Users\username\AppData\Local\CA\erwin MartServer\logs 

NOTE:  the r9.x mart server uses JDBC to connect to Oracle (except release r9.1 which required Oracle DBMS client software also installed).  JDBC is installed with the mart server software package at installation time, it is already present on the mart server machine.  By installing Oracle SQL Developer we can test the connectivity for JDBC to get further clues about the situation:

1. Please make sure you have a copy of Oracle SQL Developer installed on the machine where you are trying to make the connection.  If you are troubleshooting a mart server connection, install SQL Developer and run it from the mart server host machine.  If you are troubleshooting an erwin connection, install SQL Developer on the machine where erwin is installed and run SQL Developer from that machine.  If you do not have Oracle SQL Developer, you may obtain a copy using the below URL:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Please read the installation document and ensure that Java SDK is installed first.

2. The JDBC URL we are using is similar to this format:
jdbc:oracle:thin:@//DATABASE_SERVERNAME:DATABASE_PORTNO/DATABASE_DBNAME

For example, jdbc:oracle:thin:@//10BHTES-I8600:1521/orcl11g2.companyname.com

3. You can locate the file MartConfiguration.config which includes the parameters you have configured for the Mart server from the below path.
Windows XP or Windows 2003:
x:\Documents and Settings\All Users\Application Data\CA\erwin Mart Server
Windows 7:
x:\ProgramData\CA\erwin Mart Server

4. Find the value for DATABASE_SERVERNAME, DATABASE_PORTNO and DATABASE_DBNAME from MartConfiguration.config and then complete the above JDBC URL.
Please note that DATABASE_DBNAME should be a service name by JDBC thin format.

NOTE:  Using TNSPING SERVICENAME the user can discover their full-service name.  For further information, please see Oracle® Database Net Services Administrator's Guide 11g Release 2 (11.2) E41945-02, 15.2 Using the TNSPING Utility to Test Connectivity from the Client http://docs.oracle.com/cd/E11882_01/network.112/e41945/connect.htm#i427276

5. Open Oracle SQL Developer and create a new connection to configure the database we want to connect. Set the connection type to Advanced, then you will see the below dialog (figure 1).  Type the JDBC URL you completed in step 4.  Then press Test. If Test fails, that will show an error message. That error message may reveal an Oracle environment or configuration problem the user's DBA or help desk can help to resolve. 

Figure 1.  jdbc:oracle:thin:@//DATABASE_SERVERNAME:DATABASE_PORTNO/DATABASE_DBNAME