We help IT Professionals succeed at work.

Remote oracle connection from rails app

dkim18
dkim18 asked
on
797 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

Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
Is the remote database you're connecting to self-registering to the default listener on 1521?
Is the database even up?

Author

Commented:
>>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?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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;

Author

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.

Author

Commented:
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?

Author

Commented:
I meant *I am not the DBA...*
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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>

Author

Commented:
>>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)


Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Author

Commented:
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)
    )
  )
 

Author

Commented:
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.
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
actually, that silmiliar approach worked. Also this worked too.

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.