Friday, September 20, 2013

Oracle Listener details on Peoplesoft Demo Image

If you ever wonder how to reach the databases hosted on a Peoplesoft Demo Images from a remote client…
Here from within the Image FSCM92000 (FSCMDB-SES-85302d).

For some reason, the listener has not been configured on the default and standard port 1521. The listener port is 1522 (listener name is listener1).
Moreover, whether you can choose the name of the database during the VM deployment, the service name is always appended with .us.oracle.com.
You should keep in mind those two things for your remote client connection.

[oracle@fscm92000 ~]$ export ORACLE_SID=EP92DM00
[oracle@fscm92000 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 20 11:11:50 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Oracle Label Security options

SQL> show parameter local_listener
NAME            TYPE     VALUE
--------------- -------- -----------------------------------------------------------------
local_listener  string   (ADDRESS = (PROTOCOL=TCP)(HOST=fscm92000.phoenix.nga)(PORT=1522))
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Oracle Label Security options

[oracle@fscm92000 ~]$ lsnrctl status listener1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-SEP-2013 11:12:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fscm92000.phoenix.nga)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                20-SEP-2013 10:47:53
Uptime                    0 days 0 hr. 24 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.x/db_1/log/diag/tnslsnr/fscm92000/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fscm92000.phoenix.nga)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "EP92DM00.us.oracle.com" has 1 instance(s).
  Instance "EP92DM00", status READY, has 1 handler(s) for this service...
Service "XDB.us.oracle.com" has 1 instance(s).
  Instance "EP92DM00", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@fscm92000 ~]$

In the end, your client tnsnames.ora file should look like this:
EP92DM00 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EP92DM00.us.oracle.com)
    )
  )

Just a little annoying but need to be kept it in mind.

Nicolas.

Addendum (23-Sept 2013):
Note there’s no consistency across images versions, for instance, the last one for HCM (HCM92002) has an other listener name, but still on port 1522:
[oracle@hcm92002 ~]$ cd $ORACLE_HOME/network/admin/
[oracle@hcm92002 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

psft_listener =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hcm92002.phoenix.nga)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

[oracle@hcm92002 admin]$ lsnrctl status psft_listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-SEP-2013 11:11:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hcm92002.phoenix.nga)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     psft_listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                23-SEP-2013 08:30:21
Uptime                    0 days 2 hr. 41 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.x/db_1/log/diag/tnslsnr/hcm92002/psft_listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hcm92002.phoenix.nga)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "HR92DM02.us.oracle.com" has 1 instance(s).
  Instance "HR92DM02", status READY, has 1 handler(s) for this service...
Service "XDB.us.oracle.com" has 1 instance(s).
  Instance "HR92DM02", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@hcm92002 admin]$

2 comments:

Ken Kaspersen said...

Great information. Thanks for posting!

Anonymous said...

Thanks a lot for this!!