Forum OpenACS Q&A: TNS Problem on Oracle 10g / Solaris x86 / Aolserver 4.5

We are having trouble getting AOLServer 4.5 with nsoracle 2.7 to resolve the TNS name of its datasource. The setup works when AOLServer and Oracle are on the same physical machine, but not when they are on separate servers.

Both the web and database servers are running Solaris 10 x86 (SunOS 5.10 Generic_127112-11 i86pc i386 i86pc).

The database server has Oracle 10g Standard Edition (Oracle Database 10g Release 10.2.0.1.0 - Production).

The web server has the Admin install of Oracle Client (10.2.0)

I can connect to Oracle from the web server machine via the command-line with sqlplus and with tnsping, which implies that TNS resolution is working in general. It is only inside AOLServer/nsoracle that we see the problem. The nsdb and nsoracle drivers load with no errors:


[02/May/2008:14:36:07][936.1][-main-] Notice: modload: loading '/usr/local/aolserver/bin/nssock.so'
[02/May/2008:14:36:07][936.1][-main-] Notice: modload: loading '/usr/local/aolserver/bin/nslog.so'
[02/May/2008:14:36:07][936.1][-main-] Notice: nslog: opened '/usr/local/aolserver/servers/server1/modules/nslog/access.log'
[02/May/2008:14:36:07][936.1][-main-] Notice: modload: loading '/usr/local/aolserver/bin/nsdb.so'
[02/May/2008:14:36:07][936.1][-main-] Notice: modload: loading '/usr/local/aolserver/bin/nsoracle.so'
[02/May/2008:14:36:07][936.1][-main-] Notice: ora10 driver LobBufferSize = 16384
[02/May/2008:14:36:07][936.1][-main-] Notice: ora10 driver PrefetchRows = 0
[02/May/2008:14:36:07][936.1][-main-] Notice: ora10 driver PrefetchMemory = 0
[02/May/2008:14:36:07][936.1][-main-] Notice: nsoracle.c:897:Ns_DbDriverInit: entry (hdriver 808e008, config_path ns/db/driver/ora10)
[02/May/2008:14:36:07][936.1][-main-] Notice: Loaded ArsDigita Oracle Driver version 2.7, built on 14:17:07/May 2 2008
[02/May/2008:14:36:07][936.1][-main-] Notice: nsoracle.c:918:Ns_DbDriverInit: driver `Oracle8' loaded.
[02/May/2008:14:36:07][936.1][-main-] Notice: nsoracle.c:4015:ora_server_init: entry (server1, db, ora10)

However, when I try to allocate a handle from the pool I see the following error in the web server log:


Error: nsoracle.c:1037:ora_open_db: error in `OCIServerAttach ()': ORA-12154: TNS:could not resolve the connect identifier specified

SQL: [nil]
[02/May/2008:14:39:15][936.5][-conn:0-] Error: dbdrv: failed to open database 'ora10:DS02'
[02/May/2008:14:39:15][936.5][-conn:0-] Error: Tcl exception:
could not allocate 1 handle(s) from pool "p1"
while executing
"ns_db gethandle"
invoked from within
"set myhandle [ns_db gethandle]"
(file "/usr/local/aolserver/servers/server1/pages/sk_dbtest.tcl" line 6)
invoked from within
"source $file"
invoked from within
"ns_sourceproc cns0 {}"

Any help with this issue would be greatly appreciated.

Best regards,
-SK

**************** CONFIGURATION INFO *******************

Here is the nsoracle section of our server config:


ns_section ns/db/drivers
ns_param ora10 nsoracle.so

ns_section ns/db/driver/ora10
ns_param maxStringLogLength -1
ns_param CharExpansion 2
ns_param debug on

ns_section ns/db/pools
ns_param p1 p1

ns_section ns/db/pool/p1
ns_param MaxIdle 1000000000
ns_param MaxOpen 1000000000
ns_param Driver ora10
ns_param Connections 3
ns_param datasource DS02
ns_param User xxx
ns_param Password yyy
ns_param Verbose on
ns_param logsqlerrors on
ns_param ExtendedTableInfo on

ns_section ns/server/server1/db
ns_param Pools *
ns_param DefaultPool p1

Here is the tnsnames.ora:


DS02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Dev02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GCDEV.Dev02)
)
)

And here are the Oracle environment variables for the web server user:


ORACLE_SID=GCDEV
ORACLE_BASE=/opt/app/oracle
TNS_ADMIN=/opt/app/oracle/product/10.2.0/client_1/network/admin
ORACLE_HOME=/opt/app/oracle/product/10.2.0/client_1
Hello SK

If you do tnsping to DS02, is it work?

tnsping db02

If you don't have tnsping command execute sqlplus with Oracle enviroment with web user. Is it work?

why is ORACLE_SID=GDEV in web server?

I think that tnsnames.ora isn't correctly write.
It could be:
DS02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GCDEV)
)
)

Collapse
Posted by S Karlson on
Hi Mario,

That's the strange thing-- tnsping and sqlplus both work fine:


Dev01$ tnsping DS02

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 05-MAY-2008 09:10:23

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

Used parameter files:
/opt/app/oracle/product/10.2.0/client_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Dev02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = GCDEV.Dev02)))
OK (0 msec)

The tnsnames.ora should not refer to localhost:1521 since the Oracle server is not on the local machine (correct?).

Sqlplus also behaves correctly:


Dev01$ sqlplus xxx/yyy@DS02

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 5 09:17:43 2008

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

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL>

It's only nsoracle that is not working. Is nsoracle picky about how any of these parameters are named or formatted?

I think it might be loading a wrong driver because of this:
[02/May/2008:14:36:07][936.1][-main-] Notice: nsoracle.c:918:Ns_DbDriverInit: driver `Oracle8' loaded.

Is any other version of Oracle installed in that server? If so, make sure you compile nsoracle with Oracle10 headers.

This thread might be helpful
http://www.openacs.com/forums/message-view?message_id=282605

Collapse
Posted by S Karlson on
Update:

We have found a workaround, which is to dump the entire database connection string into the server parameter file like this:


ns_param Datasource "\(DESCRIPTION = \(ADDRESS_LIST = \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = Dev02\)\(PORT = 1521\)\)\) \(CONNECT_DATA = \(SERVICE_NAME = GCDEV.Dev02\)\)\)"

I guess that bypasses TNS name lookup? It looks a little clunky but AOLServer is talking to Oracle now.

Collapse
Posted by S Karlson on
We were able to find a workaround for the TNS lookup problem (see my reply downstream). However, with regard to your question-- no, there is no other version of Oracle installed on the server and we did compile with Oracle10 libraries.

From what I was able to find through research, the Oracle8 driver name is pretty much arbitrary and does not influence what database version the driver actually looks for. I did read that previous versions of AOLServer are picky about the driver name and the name of the nsoracle file itself, but that version 4.5 is not.

Sk,
you have reason, localhost is only when oracle and aolserver are in the same machine. sorry, but I didn't read it before.

If you can connect with sqlplus and add the lines in Datasource work but you aren't connecting with database with the name then I think that tnsnames.ora aren't reading correctly by aolserver.

are Oracle environment variables for web user write correctly?

Review TNS_ADMIN variable and check persmissions for the folder and file.

Hello S, I don't know if this is your problem but you need 3 pools for OpenACS. Here's a sample from one of my AOLservers:
#
# Database Pools: This is how AOLserver  ``talks'' to the RDBMS. You need
# three for OpenACS: main, log, subquery. Make sure to replace ``yourdb''
# and ``yourpassword'' with the actual values for your db name and the
# password for it.

# AOLserver can have different pools connecting to different databases
# and even different different database servers.
#
ns_section ns/db/pools
ns_param   pool1     "Pool 1"
ns_param   pool2     "Pool 2"
ns_param   pool3     "Pool 3"

ns_section ns/db/pool/pool1
ns_param   maxidle            0
ns_param   maxopen            0
ns_param   connections       10
ns_param   verbose            $verbose
ns_param   extendedtableinfo  true
ns_param   logsqlerrors       $sqldebug
ns_param   driver             ora10
ns_param   datasource         $datasource
ns_param   user               $db_name
ns_param   password           $db_password

ns_section ns/db/pool/pool2
ns_param   maxidle            0
ns_param   maxopen            0
ns_param   connections       10
ns_param   verbose            $verbose
ns_param   extendedtableinfo  true
ns_param   logsqlerrors       $sqldebug
ns_param   driver             ora10
ns_param   datasource         $datasource
ns_param   user               $db_name
ns_param   password           $db_password

ns_section ns/db/pool/pool3
ns_param   maxidle            0
ns_param   maxopen            0
ns_param   connections       10
ns_param   verbose            $verbose
ns_param   extendedtableinfo  true
ns_param   logsqlerrors       $sqldebug
ns_param   driver             ora10
ns_param   datasource         $datasource
ns_param   user               $db_name
ns_param   password           $db_password

ns_section ns/server/${server}/db
ns_param   pools              "*"
ns_param   defaultpool        pool1