How to diagnose "Connection to the database xxxxx via listener hasn't been established yet"

• Article Type: General
• Product: Aleph
• Product Version: 21

Desired Outcome Goal:
Successful diagnosis and correction of "Connection to the database aleph21 via listener hasn't been established yet" error in AIK step 3.4 (or elsewhere)

Procedure:
Make values in listener.ora and tnsnames.ora match the database "service_names" value.

1. Get the database service_names value:

[root@us-elunaaleph admin]# sudo su - oracle
Execute oracle cshrc
Oracle 11.2.0.3.0
Oracle Home : /exlibris/app/oracle/product/11r2 Oracle SID : aleph21
oracle@us-elunaaleph(aleph21)~$sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 17 15:21:31 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter user-name: connect /as sysdba
Enter password: **********

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@ALEPH21> show parameters service
**** Hit return to continue ****

NAME TYPE
------------------------------------ -----------
VALUE
------------------------------
service_names string
aleph21.us-elunaaleph <----------------------------------


2. Correct version of listener.ora created by the AIK ...

a. version of listener.ora created by the AIK looks like this:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = us-elunaaleph)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)


sid_list_listener=(sid_list=
(sid_desc=
(global_name=us-elunaaleph)
(sid_name=aleph21)
(oracle_home=/exlibris/app/oracle/product/11r2)
)
)


b. Change it to the following:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = us-elunaaleph)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER))
)
)

sid_list_listener=(sid_list=
(sid_desc=
(global_name=us-elunaaleph)
(sid_name=aleph21)
(oracle_home=/exlibris/app/oracle/product/11r2)
)
)
SECURE_REGISTER_LISTENER = (IPC)


c. This involves two changes:


1) change "KEY = EXTPROC1521" to "KEY = REGISTER"

2) add the last line: "SECURE_REGISTER_LISTENER = (IPC)"


3. Correct version of tnsnames.ora created by the AIK ...

a. version of tnsnames.ora created by the AIK looks like this:

us-elunaaleph.aleph21_mts=(description=
(address=
(protocol=ipc)
(key=aleph21))
(address=
(protocol=tcp)
(host=us-elunaaleph)
(port=1521))
(connect_data=(service_name=aleph21.us-elunaaleph.corp.exlibrisgroup.com)(server=SHARED)))

us-elunaaleph.aleph21=(description=
(address=
(protocol=ipc)
(key=aleph21))
(address=
(protocol=tcp)
(host=us-elunaaleph)
(port=1521))
(connect_data=(service_name=aleph21.us-elunaaleph.corp.exlibrisgroup.com)(server=DEDICATED)))


b. Change it to the following:

us-elunaaleph.aleph21_mts=(description=
(address=
(protocol=ipc)
(key=aleph21))
(address=
(protocol=tcp)
(host=us-elunaaleph)
(port=1521))
(connect_data=(service_name=aleph21.us-elunaaleph)(server=SHARED)))

us-elunaaleph.aleph21=(description=
(address=
(protocol=ipc)
(key=aleph21))
(address=
(protocol=tcp)
(host=us-elunaaleph)
(port=1521))
(connect_data=(service_name=aleph21.us-elunaaleph)(server=DEDICATED)))


c. This involves changing the two occurrences of the "service_name=" value from:

aleph21.us-elunaaleph.corp.exlibrisgroup.com

to:
aleph21.us-elunaaleph

4. Stop/restart the oracle listener

a. (Assuming you are root) sudo su - oracle
b. lsnrctl stop
c. lsnrctl start
d. Note: "lsnrctl status" can be used to check the status of the listener

5. Wait for several minutes to allow the database time to register itself with the listener....

The following entry in the listener log file indicates the time the listener was started:
2013-04-26 05:47:27.200000 -05:00
[...]
Started with pid=14929

And this, the time that the database was registered:
26-APR-2013 05:48:26 * service_register * aleph21 * 0