2 Replies Latest reply on Jul 18, 2018 10:48 AM by Gaz in Oz

    Connect problem

    Bjarke

      Hi, I have a somewhat "special" tnslistener file. Here it is:

      SIS_SC1=

      (DESCRIPTION =

           (SOURCE_ROUTE = YES)

          (ADDRESS =

            (PROTOCOL = TCP)(HOST = 193.162.253.211)(PORT = 1620)

          )

         (ADDRESS_LIST =

           (ADDRESS = (PROTOCOL = TCP)(HOST = sistest-db02.prod.umit.dk)(PORT = 1521))

         )

      (CONNECT_DATA =

         (SERVICE_NAME = SC1)

      )

      )

       

      The special thing is the SOURCE_ROUTE tag I guess? I can connect to this via sqlplus using sqlplus USER/PASS@sis_sc1. But if I try to connect to either off the ip's it doesnt work: sqlplus USER/PASS@193.162.253.211:1620/SC1, or sqlplus USER/PASS@sistest-db02.prod.umit.dk:1521/SC1.

       

      In SQL Developer, I have tried using the TNS connection. Doesnt work. And I tried using the ip's. Doesnt work.

       

      A colleage can connect using toad. Is there something in that TNS, that sql developer cant find out how to use?

       

      Any idea what to do?

        • 1. Re: Connect problem
          Bjarke

          Seemed to figure it out myself. I installed an oracle client and in properties -> database -> advanced I added the installled client home and asked sql developer to use it. I could then paste the connect string from tnsnames.ora into the connect identifier when specifying the connection as a TNS connection. Like this:
          (DESCRIPTION=(SOURCE_ROUTE=YES)(ADDRESS=(PROTOCOL=TCP)(HOST=193.162.253.211)(PORT=1620))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sistest-db02.prod.umit.dk)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SC1)))

          1 person found this helpful
          • 2. Re: Connect problem
            Gaz in Oz

            Basic and Advanced Oracle connection types can quite happily connect to cman or directly to listener and a custom tnsnames.ora with no use of an Oracle client, aside from SQL Developer itself.

            Listener is standard port 1521 and cman is custom port 1555 in below tested examples.

            F:\Oracle\SQL>type tnsnames.ora

            SIS_SC1=

               (DESCRIPTION =

                  (SOURCE_ROUTE = YES)

                  (ADDRESS =

                     (PROTOCOL = TCP)(HOST = 10.10.10.8)(PORT = 1555)

                  )

                  (ADDRESS_LIST =

                     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.8)(PORT = 1521))

                  )

                  (CONNECT_DATA = (SERVICE_NAME = ora12c))

               )

             

            F:\Oracle\SQL>

            SQL Developer cman connections without tnsnames.ora:

              Connection type  [Basic   ] Role [default ]

             

              Hostname         [ 10.10.10.8                  ]

              Port             [ 1555                        ] # or 1521

              ( ) SID          [                             ]

              (o) Service name [ ora12c                      ]

            ...and

              Connection type [Advanced ] Role [default ]

              jdbc:oracle:thin:@10.10.10.8:1555/ora12c

            and:

              jdbc:oracle:thin:@10.10.10.8:1521/ora12c

             

            ...and using your tnsnames.ora in a custom directory F:\Oracle\SQL,

            under Tools -> Preferences... -> +Database -> +Advanced -> Tnsnames Directory [F:\Oracle\SQL]: