Friday, August 17, 2007

Enterprise network issues

Just had a situation where I set up an enterprise user on a registered database. Logging on to the instance works on the machine itself:

SQL> create user global_id_schema_user identified globally;
User created.

SQL> grant connect to global_id_schema_user;
Grant succeeded.

SQL> connect bortel
Enter password:
Connected.
SQL> select sys_context('userenv','external_name') from dual;

SYS_CONTEXT('USERENV','EXTERNAL_NAME')
--------------------------------------------------------------------------------
cn=bortel,cn=users,dc=***,dc=nl

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

However, trying to connect from a remote station, I got the following error:
SQL> conn bortel@oinfra
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

This strikes as odd, as the database registered successfully.
The TNSPING utility shows

M:\>tnsping oinfra

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 17-AUG-2007 11:14:15

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

Used parameter files:
C:\oracle\DB92\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=machine.at.certain.domain)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=oinfra.machine.at.certain.domain)))
OK (10 msec)

Looks like the service_name is not within the listener. I know this setup uses hardcoded aliases in listener.ora (which is going to change - this client will switch to instances registering themselves, using local_listener). Sure enough, on the database server:

me@machine> lsnrctl services listener_machine

LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 17-AUG-2007 11:29:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=oinfra)))
Services Summary...
Service "oinfra" has 1 instance(s).
Instance "oinfra", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:136 refused:0
LOCAL SERVER
The command completed successfully

Not a trace of the fully qualified service name "oinfra.machine.at.certain.domain".
The solution to this is to add GLOBAL_DBNAME to the listener.ora file:

SID_LIST_LISTENER_MACHINE =
(SID_LIST =
(SID_DESC =
(SID_NAME = oinfra)
(global_dbname=oinfra.machine.at.certain.domain)
(ORACLE_HOME = /oracle/....)
(connection_data =
(sid = oinfra)
)
)
)

Then, do a reload of the listener configuration, and check the results:
me@machine>lsnrctl reload listener_machine

LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 17-AUG-2007 11:55:55
Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=oinfra)))
The command completed successfully
me@machine>lsnrctl services listener_machine

LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 17-AUG-2007 11:56:04
Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=oinfra)))
Services Summary...
Service "oinfra.machine.at.certain.domain" has 1 instance(s).
Instance "oinfra", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

After that, the remote login succeeds:

SQL> conn bortel@oinfra
Enter password:
Connected.

No comments: