Why am I getting a “no listener” error when using Rails with Oracle?

Question

I use Rails 2.3.5 and Oracle 10 as my database, use "oracle_adapter" and ruby-oci8 to connect to the Oracle host.

I get this exception:

Completed in 463ms (View: 18, DB: 166) | 200 OK [http://192.168.30.128/auctions?page=1]
/! \ FAILSAFE /! \ Mon Feb 01 19:02:11 +0800 2010
  Status: 500 Internal Server Error
  ORA-12541: TNS: no listener
    env.c: 257: in oci8lib.so
    /home/qichunren/.gem/ruby/1.8/gems/ruby-oci8-1.0.7/lib/oci8.rb:229:in `initialize '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-oracle-adapter-1.0.0.9250/lib/active_record/connection_adapters/oracle_adapter.rb:623:in `new '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-oracle-adapter-1.0.0.9250/lib/active_record/connection_adapters/oracle_adapter.rb:623:in `new_connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-oracle-adapter-1.0.0.9250/lib/active_record/connection_adapters/oracle_adapter.rb:659:in `initialize '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-oracle-adapter-1.0.0.9250/lib/active_record/connection_adapters/oracle_adapter.rb:35:in `new '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-oracle-adapter-1.0.0.9250/lib/active_record/connection_adapters/oracle_adapter.rb:35:in `oracle_connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in `send '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in `new_connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in `checkout_new_connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:in `checkout '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in `loop '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in `checkout '
    /usr/local/ruby187/lib/ruby/1.8/monitor.rb:242:in `synchronize '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:in `checkout '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in `connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:in `retrieve_connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in `retrieve_connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:in `connection '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/query_cache.rb:9:in `cache '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/query_cache.rb:28:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rbhaps61:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/string_coercion.rb:25:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/rack-1.0.1/lib/rack/head.rb:9:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/rack-1.0.1/lib/rack/methodoverride.rb:24:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/params_parser.rb:15:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/session/cookie_store.rb:93:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/failsafe.rb:26:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in `synchronize '
    /home/qichunren/.gem/ruby/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:114:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/reloader.rb:34:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:108:in `call '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/cgi_process.rb:44:in `dispatch_cgi '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:101:in `dispatch_cgi '
    /home/qichunren/.gem/ruby/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:27:in `dispatch '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/rails.rb:76:in `process'
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/rails.rb:74:in `synchronize '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/rails.rb:74:in `process'
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:159:in `process_client '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:158:in `each '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:158:in `process_client '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `initialize '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `new '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:268:in `initialize '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:268:in `new '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:268:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/configurator.rb:282:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/configurator.rb:281:in `each '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/configurator.rb:281:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/mongrel_rails:128:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/command.rb:212:in `run '
    /home/qichunren/.gem/ruby/1.8/gems/mongrel-1.1.5/bin/mongrel_rails:281
    /home/qichunren/.gem/ruby/1.8/bin/mongrel_rails:19:in `load '
    /home/qichunren/.gem/ruby/1.8/bin/mongrel_rails:19

It looks like the connection to Oracle is often disconnected. He is showing oracle error:**ORA-12541: TNS:no listener**.

How to fix it?

oci8.c: 270: in oci8lib.so: ORA-12541: TNS: no listener (OCIError)
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_oci_connection.rb:223:in `new'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_oci_connection.rb:223:in `new_connection'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_oci_connection.rb:328:in `initialize'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_oci_connection.rb:24:in `new'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_oci_connection.rb:24:in `initialize'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_connection.rb:9:in `new'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_connection.rb:9:in `create'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.4/lib/active_record/connec                                                                         tion_adapters/oracle_enhanced_adapter.rb:50:in `oracle_enhanced_connection'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/c                                                                         onnection_specification.rb:291:in `send'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/c                                                                         onnection_specification.rb:291:in `connection='
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/c                                                                         onnection_specification.rb:259:in `retrieve_connection'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/c                                                                         onnection_specification.rb:78:in `connection'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:2438:in `quoted_table_                                                                         name'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1259:in `find_one'
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1250:in `find_from_ids                                                                         '
        from /opt/ruby-enterprise-1.8.7/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:504:in `find'
        from script/maintenance/adjust_settlement.rb:19

Answer 1

oracle_enhanced adapter, oracle . ruby-oci8 version 2.0.3. , Oracle . Oracle Instant Client 10.2.0.4

ORA-12541: TNS: , , Oracle. , , oracle_enhanced ruby-oci8 , .

Answer 2

, :

• ${ORACLE_HOME}/network/admin/tnsnames.ora.
• ORACLE_HOME oci8.

, host [: port]/sid .

. - mydbhost.myorg.org, - 1522, sid - useeedb, :

database: mydbhost.myorg.org:1522/employeedb

1521, .

Answer 3

, - . , , , , , . , , , , .

, , , , , , , , .

"" / "" , , select 1 , , .

"keep-alive" , , select 1, . , , "db_connection_up" false .

, . , , , , .

, , - Oracle, . , .

For more information, search for searches for "oracle timeout."

Answer 4

Kelvin's answer works for me. in addition, there is a different approach.

I used rails 4.1.x, Gemfile:

gem 'activerecord-oracle_enhanced-adapter', '~> 1.5.0'
gem 'ruby-oci8', '2.2.3'

database.yml:

# not work!
host:  218.5.173.xxx/my_db

# works, solution 1:  
database: 218.5.173.xxx/my_db


# works, solution 2: 
database: "(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=218.5.173.xxx)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=my_db))
)"