Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Remote oracle connection from rails app

Posted on 2007-10-11
14
Medium Priority
?
640 Views
Last Modified: 2013-12-19
Hi,

I am trying to figure out oracle connection remotely and I have figured successfully for my oracle express that was installed in my local machine.
I followed the following site and it works fine in my local machine.
http://blog.andrewbeacock.com/2007/06/how-to-change-your-rails-database.html

This works fine.

development:
  adapter: oracle
  database: abc.xxx.org:1521/XE
  username: hr
  password: hr



Now, I am having trouble for remote oracle connection though and this is from the database.yml

development:
 
  adapter: oracle
  database: host_name:1521/ SID_NAME
  username: schema_name
  password: schema_name


This is from the remote oracle server
+++++++++
listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
      )
    )
  )
 
+++++++++++
tnsnames.ora

SID_NAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = sid_name)
 (SERVER = DEDICATED)
    )
  )
+++++++++++++
when I ping host_name, it works fine. However, I am having this error message from web browser.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I even tried oci and all other combination, but was successful.

Any ideas?

Thx,
Dan

0
Comment
Question by:dkim18
  • 8
  • 6
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20061013
Is the remote database you're connecting to self-registering to the default listener on 1521?
Is the database even up?
0
 

Author Comment

by:dkim18
ID: 20061613
>>Is the remote database you're connecting to self-registering to the default listener on 1521?
yes

>>Is the database even up?
yes

I see you put comments in my the other question. This is for configuring remote oracle connection through rails. I have tried oci(older way) and when I tried that I installed oracle instant client and set path. So, is this because I didn't set my oracle client configuration correctly?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20061720
no, that error is from the remote host, the listener on that box doesn't know what your db is that you're trying to connect to.

If your db is self-registering then maybe something happened to prevent that.

Try to force registering...

alter system register;
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:dkim18
ID: 20061793
To make things clear, I am only trying to access db that is located in remote oracle(DBA created the db.). I tried, but didn't work, so I worked with oracle express(I was able to connect to db in oracle express - in my local machine.) in my local machine and it worked.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20062337
ok, that seems to confirm that your remote db has listener problems.  Either the listener process on the remote server, the listener.ora on the remote server, or the remote db registering with the remote listener.

the listener.ora you showed above looks ok so probably one of the other two.
0
 

Author Comment

by:dkim18
ID: 20066986
well...since I am the DBA nor oracle expert, I don't have much clue and our DBA thinks there is something wrong my local set up. Could you tell me more about the listener process on the remote server and the remote db registering with the remote listener? BTW, do I need to install oracle client for this purpose?
0
 

Author Comment

by:dkim18
ID: 20066999
I meant *I am not the DBA...*
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20067596
Your error condition means you have a client (you might have more than one)

When you installed oracle xe you got a client.


This error, says your client is talking to your server.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This really should be an easy thing for your dba to decipher.

The service (database) you are trying to connect to has a name you are sending from your client,
either the database is not registering with that name, is not registering at all or is not running.

What is the service you are sending and what name is the db registering with?

If the service, host and port match up you won't get the error.

You said you could ping the host,  what happens when you tnsping the listener?

tsnping <service_name>

0
 

Author Comment

by:dkim18
ID: 20067927
>>What is the service you are sending and what name is the db registering with?
ATLANTIS

from my database.yml:

  adapter: oracle
  database: hrothgar:1521/ATLANTIS
  username: IPSIGNOUT_DBA
  password: IPSIGNOUT_DBA

from the remote server:

ATLANTIS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrothgar)(PORT = 1521)))
    (CONNECT_DATA =
      (SERVICE_NAME = atlantis.kki.org)))


>>what happens when you tnsping the listener?
I got this IF I add this in tnsnames.ora (under oracle ex) file
=========
atlantis =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hrothgar.kki.org)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = atlantis)
   )
 )
========

C:\Documents and Settings\kimda>tnsping atlantis

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-OCT-2
007 10:30:06

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hrothgar
.kki.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = atl
antis)))
OK (30 msec)

+++++++++++++++
If not, I have this error.

C:\Documents and Settings\kimda>tnsping atlantis

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-OCT-2
007 14:02:05

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

++++++++
BTW, this is the sqlnet.ora file:

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20068016
your sqlnet.ora is fine, since it doesn't specify that means you'll use tnsnames.ora
and you're already modifying that

your error/lack of error makes sense.  Your client doesn't know what "atlantis" is without a definition, that definition comes from tnsnames.ora (since your sqlnet.ora doesn't tell the client to look somewhere else)

Given that info, your dba should be able to look on the server  hrothgar.kki.org
and see if any database is registering with the name "atlantis"  either manually in the listener.ora or automatically.


The entry above you labeled  "from the remote server" looks like another tnsnames.ora entry, that doesn't help or hinder the process.  On the database server, what matters is the listener.ora
0
 

Author Comment

by:dkim18
ID: 20068116
this is listener.org from hrothgar and I see atlantis:

# LISTENER.ORA Network Configuration File: C:\OraHome1\network\admin\listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = hrothgar)(PORT = 1521))
      )
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\OraHome1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = atlantis.kki.org)
      (ORACLE_HOME = C:\OraHome1)
      (SID_NAME = atlantis)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ecentrc2.kki.org)
      (ORACLE_HOME = C:\OraHome1)
      (SID_NAME = ecentrc2)
    )
    (SID_DESC =
      (SID_NAME = as400reports)
      (ORACLE_HOME = C:\OraHome1)
      (PROGRAM = hsodbc)
    )
  )
 
0
 

Author Comment

by:dkim18
ID: 20079411
I tried to connect production DB and I got a same error. However, when I made connection through DB client program such as Aqua data studio, I was able to connect to the Db by entering all the given parameters.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 20079689
What happens if you do this?

sqlplus your_user_name/your_password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hrothgar)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=atlantis)))

And just to be clear, when you're trying to connect and it fails is that from your local machine or through a web browser running again some application/web server?  If the latter, then none of tests you do locally will matter if it's a client problem on your web server.

Try the above sqlplus long-form invocation on your machine and (if present) the web server.

0
 

Author Comment

by:dkim18
ID: 20157282
actually, that silmiliar approach worked. Also this worked too.

  adapter: oracle
  database: //server_name:1521/DB_name.xxx.org
  username: user_name
  password: pw

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month20 days, 23 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question