[resolved] tOracleConnect will not connect to Oracle Service vs. SID

[Anonymous]

Beginner user of Talend OpenStudio here.
Oracle service connection not successful with tOracleConnect component from this & other client machines. We can connect to the same target service from other apps on the same clients, even at the same time that tOracleConnect won't. (For example SQL Developer from Oracle connects just fine.) We have made multiple Oracle SID connections to other databases via tOracleConnect from multiple client machines.
I suspect I'm missing something basic here. Been over the manual, searched online, been over the forums and the bug database. If the answer is out there I'm not finding it - which is certainly possible.
Glad to investigate and provide whatever might help. Can I find the connect actual connect string it is generating somewhere? Get logging info from the JDBC driver? Additional logging from OpenStudio?
Tech Environment:
- Talend 3.2.0 (I see no changes to the Oracle connect component associated with 3.2.1)
- JDK 1.6.0_16 (from Sun.)
- All "thin" drivers (No OCI).
- Oracle InstantClient installed on client machine.
- Windows XP client, current patch level.
- Going after the "spare" in a hot-standby configuration of an 10.x or 11.x Oracle DB - we're getting that last bit defined
- We have the node name, service name, etc. of course.
Configuring tOracleConnect directly. Have tried accepting input from "tsnames.ora", and manual parameters with & w/o quotes.
- Accepting configuration from tsnames.ora fails the same as entering quoted parameters by hand.
- Unquoted parameters, build fails with: "Error in the component's properties ..."
- Quoted parameters, run fails with a "service not available" initial error:
" connecting to socket on port 3577
connected Exception in component tOracleConnection_1
java.sql.SQLRecoverableException: ORA-01033: ORACLE initialization or shutdown in progress at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101) ..."
Any help appreciated ...
- Jim

[Anonymous]

Hello

java.sql.SQLRecoverableException: ORA-01033: ORACLE initialization or shutdown in progress

It is a common oracle exception, it could be caused by many reasons, some db file lost? need to restart oracle services?
Best regards
shong

[Anonymous]

Hi Shong,
Thanks for getting back. I am aware that that particular error is pretty common. The error appears to be a sort of "catch all" for when a client gets to a service but can't complete the access. Thing is, I'm getting to that exact service & instance all day with Oracle's SQL Developer, before, during and after trying to access with the tOracleConnect job.
Is there an easy way to dump the JDBC connect string that the OpenStudio job (one component - tOracleConnect) is creating?
I'd like to compare that connect string to the string SQL Developer is using to connect successfully. I can hook up some logging around the oJDBC driver to pick that off, but that's a bit of work. I would rather not if there's an easier way.
I'm beginning to suspect that this has something to do with our going after the hot-standby / backup instance of a mission critical db. So *same* service name, but on the hot-standby node, which we specify explicitly. I'm suspicious that this unusual case gets mangled in constructing the connect string.

So, other stuff going on ...
- I'm getting to the same service & schema via Oracle's "SQL Developer" at exactly the same time as the error is reported by the talend job.
- Interestingly, another Eclipse-based client "PL/SQL Developer" has the same issue as the OpenStudio job.
- More interesting, Oracle's "SQL Developer" *doesn't* reach the service when I put in the credentials by hand, vs. read them from tsnames.ora. The OpenStudio tOracleConnect job *still fails* when I allow it to read the configuration from the same tsnames.ora file.
- When I change the node name or service name or character set, I get a different error from the routing fabric that there's no such thing.
- Other changes to the routing report a third error about talk-back from the listener

So, I think I'm *getting to* something like the service in some fashion. We have credentials (user name / pwd) for access to the hot standby instance, but not the production instance (for good reasons.) I'm wondering if in this hot standby configuration, the "naive" connect string generated by OpenStudio, PL/SQL Developer, and SQL Developer isn't pointing us to the production DB.
We can talk to the database group. BUT, they are a different team in a large, corporate environment. Better to have all my ducks in a row before going to them. If I can't show them the connect strings, it's pretty much a non-starter.
Onward ...
- Jim

[Anonymous · Accepted solution]

Hello Jim

Better to have all my ducks in a row before going to them. If I can't show them the connect strings, it's pretty much a non-starter.

When you open the 'Code' tab, you could see the connection code as below:

		 *  start
				 */
				ok_Hash.put("tOracleConnection_1", false);
				start_Hash.put("tOracleConnection_1", System
						.currentTimeMillis());
				currentComponent = "tOracleConnection_1";
				int tos_count_tOracleConnection_1 = 0;
				String url_tOracleConnection_1 = "jdbc:oracle:thin:@"
						+ "192.168.0.117" + ":" + "1521" + ":" + "talend";
				globalMap.put("connectionType_" + "tOracleConnection_1",
						"ORACLE_SID");
				String userName_tOracleConnection_1 = "system";
				String password_tOracleConnection_1 = "111111";
				java.sql.Connection conn_tOracleConnection_1 = null;
				java.lang.Class.forName("oracle.jdbc.driver.OracleDriver");
				conn_tOracleConnection_1 = java.sql.DriverManager
						.getConnection(url_tOracleConnection_1,
								userName_tOracleConnection_1,
								password_tOracleConnection_1);
				conn_tOracleConnection_1.setAutoCommit(false);

Best regards

shong

[Anonymous]

Thanks Shong.
I didn't see your comment until just now. (Odd, since it says I'm "subscribed" to the topic. Well, I'll figure that out later.) When I have this all sussed out, I'll post a complete report - configurations, variations, connect strings, and so on.

/Detail so far ...
So, far, I got tOracleConnect working by configuring to connect to what's supposed to be the *production* node *to which we don't have credentials.* So far I have:
- SQL Designer connects when it picks up the tnsnames.ora. Talend's tOracleConnect does not when it loads from the same file.
- Talend's tOracleConnect *doesn't work* when configured by hand *to the same parameters that show up on screen for the working SQL Designer connection.*
- ***This is interesting*** SQL Designer doesn't connect when configured by hand *to the same parameters it shows on screen, when it loads from tnsnames.ora. (What?)
I'll pick off connect strings reported within the client tools, traffic logged from w/in JDBC and finish my investigation of the syntax of tnsnames.ora + how fail-over nodes work. Will report back with all that in hand.
We have to understand this. We're building an enterprise data-feed & my team just got tagged with overall responsibility for the solution (Vs. being a subcontractor before. "Reaping the rewards of success" I suppose.) So, something that works but we don't understand why isn't good enough.
For all I know, all the tech could be working as it's supposed to. But until we - meaning I - know why, we have to keep digging.
Thanks for the help so far.
- Jim