ETL Technical Tools Kettle Getting started note (i) Kettle connecting Oracle RAC Listener does not currently know of SID Error resolution

1 problem Phenomena:

Previously done Kettle connect an Oracle database for table extraction

The table input information for the script is as follows:

Error message in the table input report when executing (script uploaded to Linux machine with sh command) :

But in the machine with the Sqlplus command login can be successful:

2 resolution process:

After the problem, the first contact with the source data system manufacturers to see if they do not have a database restrictions.

By their view, there was no restriction on their side. There's no reason to find it here, and then we look at other systems.

134.64.197.198 is a node of a RAC with a floating address corresponding to the SID that is IPRANDB1. And Iprandb is the service-name of RAC. The service-name of RAC is not the same as SID.

Instance of the response service for the source system:

[Email protected] ~]$ lsnrctl status

Lsnrctl for linux:version 11.2.0.4.0-production on 10-sep-2014 17:35:09

Copyright (c) 1991, Oracle. All rights reserved.

Connecting to (Address= (PROTOCOL=TCP) (host=) (port=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for Linux:version 11.2.0.4.0-production
Start Date 28-jan-2014 04:29:12
Uptime 225 days hr. 5 min. $ sec
Trace level off
Security on:local OS Authentication
SNMP OFF
Listener Parameter File/opt/app/11.2.0/grid/network/admin/listener.ora
Listener Log File/opt/app/grid/diag/tnslsnr/iprandb1/listener/alert/log.xml
Listening Endpoints Summary ...
(Description= (address= (PROTOCOL=IPC) (Key=listener)))
(Description= (address= (protocol=tcp) (host=172.16.17.178) (port=1521)))
(Description= (address= (protocol=tcp) (host=172.16.17.181) (port=1521)) 172.16.17.181 corresponds to ip134.64.197.198
Services Summary ...
Service "Iprandb" has 1 instance (s).
Instance "IPRANDB1", status ready, have 1 handler (s) for the This service ...
Service "Iprandbxdb" has 1 instance (s).
Instance "IPRANDB1", status ready, have 1 handler (s) for the This service ...
The command completed successfully

Before the Sqlplus command with IPRANDB may be because after the @ is to put the service name Service-name

Possible JDBC Connection method requires a more granular database name to be required to the SID

The Kettle table input script inside the database name changed from IPRANDB to IPRANDB1 just fine

Because there are several manufacturers involved in the source system and platform configuration, so the problem is long time to record here.

3 Understanding RAC:

It's very rare to know about RAC before, so I take this opportunity to know something about it.

RAC is a shorthand for ORACle Real application cluster, meaning real-time application clustering, which is the core technology of Oracle Database support Grid computing environment.

Your understanding is that load balancing and a node down in the cluster can immediately switch to other nodes without affecting the normal operation of the upper-level application based on the database.

One node after the problem can automatically go to another node on the function is because there is a VIP this thing full name virtual IP He is floating IP can be automatically converted.

And then say it again.

Oracle RAC Services Many people may not understand the difference between Oracle service name and SID name

(The following contents are reproduced from http://blog.csdn.net/leshami/article/details/8124232)

I. Services and SERVICE_NAME

Services

For a client application, the only thing that matters is what services the database provides, without knowing exactly which database or instance it is connected to.

So on the database server side we can create one or more services for the client, which is the collectively known as one or more service_name.

For these services, Oracle registers it with the listener for external connections.

You can see how many instances of the current service should be serviced by LSNRCTL status [Listener_name].

More detailed information can also be viewed through LSNRCTL service [Listener_name], including current connection status, IP, port number, etc.

Service_Name

Refers to the service name that the client connects to the instance. At Oracle 8i, there is the concept of service_name, which is often used instead of oracle_sid in Tnsnames.ora.

After 9i, Oracle recommends using SERVICE_NAME instead of SIDS.

You can differentiate between different user connections by defining a number of different service names, and the default format for this parameter is db_name.domain_name.

The following is a client's Tnsnames.ora, two different connection identifiers are used next to Oracle_sid, one using service_name, both of which are feasible.
Sybo2sz_sid=
(description=
(address=
(PROTOCOL=TCP)
(host=192.168.7.2)
(port=1915)
)
(Connect_data=
(ORACLE_SID=SYBO2SZ) #此处使用了ORACLE_SID =<&gt, or you can directly use the sid=<>
)
)

sybo2sz=
(description=
(address=
(PROTOCOL=TCP)
(host=192.168.7.2)
(port=1915)
)
(Connect_data=
(SERVICE_NAME=SYBO2SZ) Recommended for use with service_name after #Oracle 9i
)
)

II. Benefits of using services

As mentioned earlier, you can create several different services for the same database to serve different client groupings. For a single instance, although you can create several different services for it, the provision of these services begins

is a single database single instance, so performance is not obvious.

In the case of multiple instances, it is possible to determine the distribution of different services to different instances at different times or according to different business logic rules, as well as to consider

Services sets the preferred instance, the standby instance. Once a single point of failure occurs for the preferred instance, services automatically failover to the standby instance.

If the current RAC database is defined with 3 nodes Srv1,srv2,srv3

There are two different service sales.2gotrade.com and settlement.2gotrade.com running in the current database

The Sales department establishes the connection through the Sales.2gotrade.com service name, and the Settlement department establishes the connection by settlement.2gotrade.com the service name.

Second, the load of the sales section is typically run on srv1,srv2, and its corresponding standby node is srv3, which means that all sales-based connections and loads will be transferred to the node Srv3 when the node srv1,srv2 fails.

Assuming that the settlement department load is usually small, so that the preferred node is srv3 and the standby node is SRV1, the node srv3 a single point of failure, then all settlement departmental connections and loads are transferred to SRV1.

All connections to the current two departments do not need to be concerned about which database is currently connected to the instance on that node.

From the above description

Each node connection is transparent to the client, and the user does not have to care about the database and the instance it connects to, leaving behind a complex background configuration.

In addition, 1 service-name can also correspond to several different SIDs, as the above problem says.

ETL Technology Tools Kettle Getting started note (i) Kettle connecting Oracle RAC Listener does not currently know of SID Error resolution