7 Replies Latest reply on Jun 10, 2011 8:24 AM by 772262

    ORA-12505, TNS:listener does not currently know of SID using JDBC driver

    772262
      <?xml version="1.0" encoding="UTF-8" ?>
      - <repositories>
      - <connection>
      <name>DWH_ETL_REPO</name>
      <server>cc2pre01cl</server>
      <type>ORACLE</type>
      <access>Native</access>
      <database>DWH</database>
      <port>1521</port>
      <username>DWH_ETL_REPO</username>
      <password>DWH_ETL_REPO</password>
      <servername />
      <data_tablespace />
      <index_tablespace />
      - <attributes>
      - <attribute>
      <code>FORCE_IDENTIFIERS_TO_LOWERCASE</code>
      <attribute>N</attribute>
      </attribute>
      - <attribute>
      <code>FORCE_IDENTIFIERS_TO_UPPERCASE</code>
      <attribute>N</attribute>
      </attribute>
      - <attribute>
      <code>IS_CLUSTERED</code>
      <attribute>N</attribute>
      </attribute>
      - <attribute>
      <code>PORT_NUMBER</code>
      <attribute>1521</attribute>
      </attribute>
      - <attribute>
      <code>QUOTE_ALL_FIELDS</code>
      <attribute>N</attribute>
      </attribute>
      - <attribute>
      <code>SUPPORTS_BOOLEAN_DATA_TYPE</code>
      <attribute>N</attribute>
      </attribute>
      - <attribute>
      <code>USE_POOLING</code>
      <attribute>N</attribute>
      </attribute>
      </attributes>
      </connection>
      - <repository>
      <id>KettleDatabaseRepository</id>
      <name>DWH_ETL_REPO</name>
      <description>DWH_ETL_REPO</description>
      <connection>DWH_ETL_REPO</connection>
      </repository>
      </repositories>



      Error occured while trying to connect to the database

      Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
      Listener refused the connection with the following error:
      ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
      The Connection descriptor
      used by the client was:
      cc2pre01cl:1521:DWH

      We are trying to connect from pentaho command line to a 11g RAC database using repository.xml(above file) via jdbc driver. Please can I know how to resolve this error.

      <server>cc2pre01cl</server> -- clustername
      <database>DWH</database> -- RAC database name


      thanks.
        • 1. Re: ORA-12505, TNS:listener does not currently know of SID using JDBC driver
          sb92075
          from DB Server system issue following commands

          lsnrctl status
          lsnrctl service

          COPY commands & results then PASTE all back here
          • 2. Re: ORA-12505, TNS:listener does not currently know of SID using JDBC driver
            772262
            [oracle@bnl11237dat01 trace]$ lsnrctl status

            LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:33:36

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
            STATUS of the LISTENER
            ------------------------
            Alias LISTENER
            Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
            Start Date 02-JUN-2011 15:56:50
            Uptime 5 days 1 hr. 36 min. 46 sec
            Trace Level off
            Security ON: Local OS Authentication
            SNMP ON
            Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
            Listener Log File /u01/app/oracle/diag/tnslsnr/bnl11237dat01/listener/alert/log.xml
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.32.24.55)(PORT=1521)))
            Services Summary...
            Service "DWH" has 1 instance(s).
            Instance "DWH11", status READY, has 1 handler(s) for this service...
            Service "DWH1XDB" has 1 instance(s).
            Instance "DWH11", status READY, has 1 handler(s) for this service...
            Service "MDM1" has 1 instance(s).
            Instance "MDM11", status READY, has 1 handler(s) for this service...
            Service "MDM1XDB" has 1 instance(s).
            Instance "MDM11", status READY, has 1 handler(s) for this service...
            The command completed successfully


            [oracle@bnl11237dat01 trace]$ lsnrctl service

            LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:34:06

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
            Services Summary...
            Service "DWH" has 1 instance(s).
            Instance "DWH11", status READY, has 1 handler(s) for this service...
            Handler(s):
            "DEDICATED" established:4 refused:0 state:ready
            LOCAL SERVER
            Service "DWH1XDB" has 1 instance(s).
            Instance "DWH11", status READY, has 1 handler(s) for this service...
            Handler(s):
            "D000" established:0 refused:0 current:0 max:1022 state:ready
            DISPATCHER <machine: bnl11237dat01, pid: 8294>
            (ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat01)(PORT=31718))
            Service "MDM1" has 1 instance(s).
            Instance "MDM11", status READY, has 1 handler(s) for this service...
            Handler(s):
            "DEDICATED" established:6144 refused:0 state:ready
            LOCAL SERVER
            Service "MDM1XDB" has 1 instance(s).
            Instance "MDM11", status READY, has 1 handler(s) for this service...
            Handler(s):
            "D000" established:0 refused:0 current:0 max:1022 state:ready
            DISPATCHER <machine: bnl11237dat01, pid: 8206>
            (ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat01)(PORT=18921))
            The command completed successfully


            ON NODE 2
            [oracle@bnl11237dat02 ~]$ lsnrctl status

            LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:34:40

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
            STATUS of the LISTENER
            ------------------------
            Alias LISTENER
            Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
            Start Date 19-MAY-2011 12:17:45
            Uptime 19 days 5 hr. 16 min. 55 sec
            Trace Level off
            Security ON: Local OS Authentication
            SNMP ON
            Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
            Listener Log File /u01/app/oracle/diag/tnslsnr/bnl11237dat02/listener/alert/log.xml
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.32.24.56)(PORT=1521)))
            Services Summary...
            Service "DWH" has 1 instance(s).
            Instance "DWH12", status READY, has 1 handler(s) for this service...
            Service "DWH1XDB" has 1 instance(s).
            Instance "DWH12", status READY, has 1 handler(s) for this service...
            Service "MDM1" has 1 instance(s).
            Instance "MDM12", status READY, has 1 handler(s) for this service...
            Service "MDM1XDB" has 1 instance(s).
            Instance "MDM12", status READY, has 1 handler(s) for this service...
            The command completed successfully
            [oracle@bnl11237dat02 ~]$


            [oracle@bnl11237dat02 ~]$ lsnrctl service

            LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:34:51

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
            Services Summary...
            Service "DWH" has 1 instance(s).
            Instance "DWH12", status READY, has 1 handler(s) for this service...
            Handler(s):
            "DEDICATED" established:11 refused:0 state:ready
            LOCAL SERVER
            Service "DWH1XDB" has 1 instance(s).
            Instance "DWH12", status READY, has 1 handler(s) for this service...
            Handler(s):
            "D000" established:0 refused:0 current:0 max:1022 state:ready
            DISPATCHER <machine: bnl11237dat02, pid: 9488>
            (ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat02)(PORT=16589))
            Service "MDM1" has 1 instance(s).
            Instance "MDM12", status READY, has 1 handler(s) for this service...
            Handler(s):
            "DEDICATED" established:22053 refused:0 state:ready
            LOCAL SERVER
            Service "MDM1XDB" has 1 instance(s).
            Instance "MDM12", status READY, has 1 handler(s) for this service...
            Handler(s):
            "D000" established:0 refused:0 current:0 max:1022 state:ready
            DISPATCHER <machine: bnl11237dat02, pid: 2688>
            (ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat02)(PORT=12553))
            The command completed successfully
            [oracle@bnl11237dat02 ~]$
            • 3. Re: ORA-12505, TNS:listener does not currently know of SID using JDBC driver
              sb92075
              http://www.orafaq.com/wiki/JDBC

              need to decide whether connecting to SID or SERVICE & utilize proper syntax & name

              Old syntax

              jdbc:oracle:thin:@[HOST][:PORT]:SID

              New syntax

              jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE


              Oracle correctly threw error for posted request
              • 4. Re: ORA-12505, TNS:listener does not currently know of SID using JDBC driver
                772262
                thanks for the url

                I am having a rac database ,, connect with a cluster name and service name
                the example given is for a single instance
                ORCL =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
                (CONNECT_DATA =
                (SID = ORCL)
                )
                )




                so can I use service name DWH as I don't have SID
                DWH =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = cc2pre01cl)(PORT = 1521))
                (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = DWH)
                )
                )
                • 5. Re: ORA-12505, TNS:listener does not currently know of SID using JDBC driver
                  sb92075
                  JDBC does not utilize or require any tnsnames.ora file or entry.
                  • 6. Re: ORA-12505, TNS:listener does not currently know of SID using JDBC driver
                    772262
                    <?xml version="1.0" encoding="UTF-8" ?>
                    - <repositories>
                    - <connection>
                    <name>DWH_ETL_REPO</name>
                    <server*>//cc2pre01cl*</server>
                    <type>ORACLE</type>
                    <access>Native</access>
                    <database*>/DWH*</database>
                    <port>1521</port>
                    <username>DWH_ETL_REPO</username>
                    <password>DWH_ETL_REPO</password>
                    <servername />
                    <data_tablespace />
                    <index_tablespace />
                    - <attributes>
                    - <attribute>
                    <code>FORCE_IDENTIFIERS_TO_LOWERCASE</code>
                    <attribute>N</attribute>
                    </attribute>
                    - <attribute>
                    <code>FORCE_IDENTIFIERS_TO_UPPERCASE</code>
                    <attribute>N</attribute>
                    </attribute>
                    - <attribute>
                    <code>IS_CLUSTERED</code>
                    <attribute>N</attribute>
                    </attribute>
                    - <attribute>
                    <code>PORT_NUMBER</code>
                    <attribute>1521</attribute>
                    </attribute>
                    - <attribute>
                    <code>QUOTE_ALL_FIELDS</code>
                    <attribute>N</attribute>
                    </attribute>
                    - <attribute>
                    <code>SUPPORTS_BOOLEAN_DATA_TYPE</code>
                    <attribute>N</attribute>
                    </attribute>
                    - <attribute>
                    <code>USE_POOLING</code>
                    <attribute>N</attribute>
                    </attribute>
                    </attributes>
                    </connection>
                    - <repository>
                    <id>KettleDatabaseRepository</id>
                    <name>DWH_ETL_REPO</name>
                    <description>DWH_ETL_REPO</description>
                    <connection>DWH_ETL_REPO</connection>
                    </repository>
                    </repositories>



                    I have given //<cluster_name> and /<database_name>
                    .. still I get the same error
                    • 7. Re: ORA-12505, TNS:listener does not currently know of SID using JDBC driver
                      772262
                      <?xml version="1.0" encoding="UTF-8" ?>
                      - <repositories>
                      - <connection>
                      <name>DWH_ETL_REPO</name>
                      <server/>
                      <type>ORACLE</type>
                      <access>Native</access>
                      <database>//cc2pre01cl:1521/DWH</database>
                      <port/>
                      <username>DWH_ETL_REPO</username>
                      <password>DWH_ETL_REPO</password>
                      <servername />
                      <data_tablespace />
                      <index_tablespace />
                      - <attributes>
                      - <attribute>
                      <code>FORCE_IDENTIFIERS_TO_LOWERCASE</code>
                      <attribute>N</attribute>
                      </attribute>
                      - <attribute>
                      <code>FORCE_IDENTIFIERS_TO_UPPERCASE</code>
                      <attribute>N</attribute>
                      </attribute>
                      - <attribute>
                      <code>IS_CLUSTERED</code>
                      <attribute>N</attribute>
                      </attribute>
                      - <attribute>
                      <code>PORT_NUMBER</code>
                      <attribute>1521</attribute>
                      </attribute>
                      - <attribute>
                      <code>QUOTE_ALL_FIELDS</code>
                      <attribute>N</attribute>
                      </attribute>
                      - <attribute>
                      <code>SUPPORTS_BOOLEAN_DATA_TYPE</code>
                      <attribute>N</attribute>
                      </attribute>
                      - <attribute>
                      <code>USE_POOLING</code>
                      <attribute>N</attribute>
                      </attribute>
                      </attributes>
                      </connection>
                      - <repository>
                      <id>KettleDatabaseRepository</id>
                      <name>DWH_ETL_REPO</name>
                      <description>DWH_ETL_REPO</description>
                      <connection>DWH_ETL_REPO</connection>
                      </repository>
                      </repositories>


                      Cannot connect to Oracle 11g (11.2.0.2) RAC database using JDBC (ojdbc14-10.2.0.3.0.jar ) driver.

                      I followed this link http://www.orafaq.com/wiki/JDBC. but still can't connect to RAC database.

                      Any help will be highly appreciated.