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.
