Listen Oracle 12c..Or: the chapter of letting the listener listen on a non-default port.

Just for fun and making things a little harder for myself, I decided to run the listener and databases on a non-default port..This is easy configured using the netca tool, and replace the 1521 occurrences with another number, e.g. 1234.

Although did looked like this was all, and indeed the listener was listening on port 1234, the listener did not find any databases to service..or the databases didn’t find the listener, which can also be the issue.

Optimistic as we are, we log in the database “deurpdb” (yes, a pluggable database ūüėČ )

$ sqlplus system@deurpdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 4 21:32:58 2013

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

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor.

Ouch. This was not supposed to happen..Well..on with the troubleshooting..

The usual checks gave these results:

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 04-OCT-2013 21:28:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test01)(PORT=1234)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date           04-OCT-2013 21:11:01
Uptime                    0 days 0 hr. 17 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/base/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/base/diag/tnslsnr/test01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test01)(PORT=1234)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1234)))
The listener supports no services
The command completed successfully

Ok, knowing this,we expect no results from a tnsping..but still, to make sure we run this anyway.

$ tnsping deurpdb

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 04-OCT-2013 21:32:35

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

Used parameter files:
/oracle/base/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test01)(PORT = 1234))) (CONNECT_DATA = (SERVICE_NAME = deurpdb)))
OK (0 msec)

Uhm..that’s weird..the listener has no database to service, but a ping does resolve..

Mind: this is from the same server the database is running on. Conclusion: it’s bypassing the listener, and connects through the socket.Fine. But this is not what we want, since to be able to do an import, we need to have a working TNSNames entry. So, we test from a remote client..

oracle@test02$ tnsping deurpdb

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 05-OCT-2013 10:53:40

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

Used parameter files:
/srv/oracle/base/db_home01/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

Ah, this is more what we expect. The listener has no clue, just as we thought.

Moving on, puzzled as we are, we’re going to see what the database thinks the listener is:

SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string
remote_listener                      string

Hey! It doesn’t know! The value for the string of the local_listener is empty..The database cannot identify itself to the listener..

That should not be difficult to fix: we change this value like this:

SQL> alter system set local_listener='deurdb' scope=both;

–> Be sure to use the CONTAINER database name, not the pluggable database name. <–

SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      deurdb
remote_listener                      string

That is looking better..Now let’s see if the listener agrees:

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 04-OCT-2013 21:58:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test01)(PORT=1234)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                04-OCT-2013 21:11:01
Uptime                    0 days 0 hr. 47 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/base/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/base/diag/tnslsnr/test01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test01)(PORT=1234)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1234)))
Services Summary...
Service "deurdb" has 1 instance(s).
Instance "deurdb", status READY, has 1 handler(s) for this service...
Service "deurdbXDB" has 1 instance(s).
Instance "deurdb", status READY, has 1 handler(s) for this service...
Service "deurpdb" has 1 instance(s).
Instance "deurdb", status READY, has 1 handler(s) for this service...
The command completed successfully

And the tnsping from the remote host shows:

$ tnsping deurpdb

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 05-OCT-2013 11:25:28

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

Used parameter files:
/srv/oracle/base/db_home01/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test01)(PORT = 1234))) (CONNECT_DATA = (SERVICE_NAME = deurpdb)))
OK (40 msec)

And we are able to login..Yay!

This concludes this little issue, hopefully it will save you some trouble and time..

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff and tagged , . Bookmark the permalink.

2 Responses to Listen Oracle 12c..Or: the chapter of letting the listener listen on a non-default port.

  1. Adi says:

    Awesome!!! saved my day dude ūüôā

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s