OCI8 -- "Connect failed because target host or object does not exist" only on first atempt

Question

This is a ruby project, connecting to Oracle, using ActiveRecord's oracle-enhanced-adapter gem. This gem is a wrapper for OCI8.

ActiveRecord::Base.establish_connection(
    :adapter => "oracle_enhanced", 
    :database => "xxx.xxx.xxx.xxx:1521/nnnn",
    :username => "some_user_name",
    :password => "very_secret_password"
)
ActiveRecord::Base.pluralize_table_names = false

class All_tables < ActiveRecord::Base
end

OK, let's test it.

[1] pry(main)> All_tables.take
OCIError: ORA-12545: Connect failed because target host or object does not exist
from oci8.c:513:in oci8lib_200.so

Give it 5 seconds and try again.

[2] pry(main)> All_tables.take
#<All_tables owner: "SYS", table_name: "DUAL", tablespace_name: "SYSTEM",....

This workaround does the job, but feels kind of dirty.

retrylimit=5
begin
  All_tables.take
rescue
  sleep 2
  puts "retrying"
  retrylimit -= 1
  retry if retrylimit > 0
end

Looks like a classic timeout, but... IF it is a timeout, where and how can I configure it? Note: already tried :timeout => 5000 in establish_connection. Didn't work.

Answer 1

I'm assuming xxx.xxx.xxx.xxx is a host name. Check the DNS configuration on the ActiveRecord machine. Try using nslookup and ping to diagnose why - sometimes - the remote machine name cannot be resolved. It's also possible that the name resolution is too slow for OCI, so it is failing on first attempt. Then the resolution is cached the next attempt succeeds. Try using an IP address instead.

Enable SQL*Net tracing for the Oracle client. That may provide enough information to positively answer your question.