Connecting to Oracle Database

[Former Member]

Ok, for starters I have read most of the posts in the forum regarding this. I have also reviewed the documentation and have created the server url correctly but I am still getting connection error when I view the status window for the Oracle connection I have setup. I copied the default oracle connection and updated the fields appropriately. We are using 11.5.

JDBC Driver: oracle.jdbc.driver.OracleDriver

Here are the server url setting I tried;

This is the string from our TNSNames file that one of the posts suggested to try.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(address=(protocol=tcp)(host=smesadevdb01)(port=1525))(address=(protocol=tcp)(host=smesadevdb02)(port=1525)))(CONNECT_DATA=(SERVICE_NAME=pack.mesaii.trw.com)))

jdbc:oracle:thin:@smesadevdb01:1525:pack_test

jdbc:oracle:thin:@smesadevdb01:1525:pack.mesaii.trw.com

I have a couple of questions......

1. Do you need to have the oracle client installed on the xmii server?

2. Is there any special configuration necessary on the oracle db side?

Any help is appreciated.

Thanks,

[Former Member · Accepted solution]

First check the xMII Logs for any specific errors (Authentication/Username , Pwd etc.).

If you are satisfied then try a

telnet <oracleserverIP> 1525 from the xMII server Command Prompt to check if 1525 on the Oracle server is open to recieve TCP requests.

[Former Member]

All,

Thanks for the assistance. It came down to trying to figure out what the SID value was. I was using the Service_Name that exists in out tnsnames file and it did not like that. We determined the SID by

logging on to the DB server and looking at the services running. Each DB instance is its own service and the SID is part of the service name. In this case OracleServicepack1, where pack1 is the SID. I used that along with port 1525 and like magic it works.

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor started us down that path.

Again, thanks for the input.

James.

Edited by: James Slaughter on Feb 6, 2008 1:48 PM

Edited by: James Slaughter on Feb 6, 2008 1:49 PM

[Former Member]

Sorry I forgot to mention that in my original post.... its 9i... I also tried to telnet to the db server as mentioned above. It looks like it connected... I did not get any firm rejection message that it didn't connect. But I also did not get any confirmation that it did connect. the command widow was just blank.

Edited by: James Slaughter on Feb 6, 2008 11:00 AM

[Former Member]

Check your xMII general log. Your Oracle user may not have the required permission to connect this way. If this is the case, it should be pretty clear by the message in the log.

[Former Member]

Here are the log entries.

2008-02-06 09:05:27,950 FATAL Connector Unable to create connection pool for PACKTest: Fatal error while filling connection pool for jdbc:oracle:thin:@smesadevdb01:1525:pack_test

2008-02-06 09:05:27,856 ERROR ConnectionPool Fatal error while filling connection pool for jdbc:oracle:thin:@smesadevdb01:1525:pack_test java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093376)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334) at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:418) at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:521) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:171) at com.lighthammer.Illuminator.tools.database.ConnectionPool.createConnectionObject(Unknown Source) at com.lighthammer.Illuminator.tools.database.ConnectionPool.fill(Unknown Source) at com.lighthammer.Illuminator.tools.database.ConnectionPool.initialize(Unknown Source) at com.lighthammer.Illuminator.tools.database.ConnectionPool.<init>(Unknown Source) at com.lighthammer.Illuminator.tools.database.ConnectionHolder.addDBConnectionPool(Unknown Source) at com.lighthammer.Illuminator.server.Server.createHolder(Unknown Source) at com.lighthammer.Illuminator.server.Server.setEnabled(Unknown Source) at com.lighthammer.Illuminator.server.Server.parseProperties(Unknown Source) at com.lighthammer.Illuminator.server.Server.<init>(Unknown Source) at com.lighthammer.Illuminator.server.ServerManager.add(Unknown Source) at com.lighthammer.Illuminator.server.ServerManager.update(Unknown Source) at com.lighthammer.Illuminator.gui.handlers.ServerHandler.processServer(Unknown Source) at com.lighthammer.Illuminator.gui.handlers.ServerHandler.process(Unknown Source) at com.lighthammer.Illuminator.servlet.Admin.service(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.newatlanta.servletexec.SERequestDispatcher.forwardServlet(SERequestDispatcher.java:638) at com.newatlanta.servletexec.SERequestDispatcher.forward(SERequestDispatcher.java:236) at com.newatlanta.servletexec.SERequestDispatcher.internalForward(SERequestDispatcher.java:283) at com.newatlanta.servletexec.SEFilterChain.doFilter(SEFilterChain.java:96) at com.lighthammer.cms.system.CMSFilter.doFilter(Unknown Source) at com.newatlanta.servletexec.SEFilterChain.doFilter(SEFilterChain.java:60) at com.newatlanta.servletexec.ApplicationInfo.filterApplRequest(ApplicationInfo.java:2159) at com.newatlanta.servletexec.ApplicationInfo.processApplRequest(ApplicationInfo.java:1823) at com.newatlanta.servletexec.ServerHostInfo.processApplRequest(ServerHostInfo.java:937) at com.newatlanta.servletexec.ServletExec.ProcessRequest(ServletExec.java:1091) at com.newatlanta.servletexec.ServletExec.ProcessRequest(ServletExec.java:973) at com.newatlanta.servletexec.ServletExecService.processServletRequest(ServletExecService.java:167) at com.newatlanta.servletexec.ServletExecService.Run(ServletExecService.java:204) at com.newatlanta.servletexec.HttpServerRequest.run(HttpServerRequest.java:487)

2008-02-06 09:05:27,856 WARN ConnectionPool Unable to detemermine maximum number of connections from JDBC driver

2008-02-06 09:05:27,653 DEBUG ConnectionPool Initializing pool...

2008-02-06 09:05:27,653 SYSTEM Connector Enabling server PACKTest

2008-02-06 09:05:27,653 SYSTEM Connector Disabling server PACKTest

2008-02-06 09:05:27,653 DEBUG Connector {Timeout=15, JDBCDriver=oracle.jdbc.driver.OracleDriver, UseCount=256, ConnectorType=SQL, Connector=IDBC, DatePrefix=TO_DATE(', ServerURL=jdbc:oracle:thin:@smesadevdb01:1525:pack_test, Password=apcktest, Description=Default Oracle Database, PoolMax=100, ServerPackage=com.lighthammer.Illuminator.connectors.IDBC, InternalDateFormat=MM/dd/yyyy HH:mm:ss, Enabled=true, PoolSize=1, InitCommand=, WaitTime=30, ValidationQuery=SELECT SYSDATE FROM DUAL, DateSuffix=','MM/DD/YYYY HH24:MI:SS'), Name=PACKTest, UserName=pack}

[salvatore_castro]

Looks like a networking issue "Io exception: Connection refused"

From the xMII server, open up command prompt type:

telnet smesadevdb01 1521

Should turn blank, if not your network is blocking this request.

Sam

[Former Member]

Ok, this is not a User permission error (Yet;))

This means either the xMII server cannot reach the Oracle server over the network, or the Oracle server is not configured to allow this kind of remote connection( This is most likely).

Unfortunatly I do not know how to do the Oracle configuration changes. Contact your DBA or google

[salvatore_castro]

James,

The default oracle port is 1521, I noticed that your Server URL is:

jdbc:oracle:thin:@smesadevdb01:1525:pack_test

which is using 1525....is that correct? This could also explain your issue.

Sam

[Former Member]

And double check your port. 1521 is the Oracle default.

[salvatore_castro]

Going to have to be faster than that....

[Former Member]

Googling ORA-12505 might help.

[Former Member]

Sam,

I did the telnet from the command prompt with port 1525 and 1521 and in both cases the cmd window went blank.

Edited by: James Slaughter on Feb 6, 2008 1:48 PM

[salvatore_castro]

What version of Oracle are you using? v11.5 comes with the 9i driver which may not be compatible....

Sam

[Former Member]

Hi James, Hope all is well.

Are you able to connect to the database from your xMII server using other tools like SQL developer?

We found our network was configured to block port 1521 for our xMII server.

FWIW we are connecting to version 10 with the xMII supplied Oracle driver.

[Former Member]

I installed a basic oracle 9i client without the sql+ developer tools. I'll install them and get back with you.