2

I have just installed an oracle 19c (19.3.0.0.0 to be exact) database on a centOS 8 virtual machine. The databases instances can all be started with sqlplus on the host machine. I have set up listener.ora and tnsnames.ora based on an existing (functioning) oracle 12c, which has a very similar setup.

I did the initial installation using the .rpm from Oracle, thinking that would make things easier. It probably didn't make things any more difficult, but it meant that the directory structure was not what I'd have expected. Still, it isn't as though it is unusable, just located differently. Having done that, I did a basic install of the DB instances I wanted (using pretty much all default settings) with dbca.

Finally, I setup listener.ora and tnsnames.ora based on the working 12c installation. The problem is, no matter what I do, the DB instances don't seem to be registering themselves with the listener.

The tnsnames.ora looks like this:

## initially copied from linuxoracle12 (20200617-1112)

LISTENER_TEMP =
  (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))


TMF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tmf.my-company-name.com)
    )
  )

LISTENER_TMFDE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))


LISTENER_TMF =
  (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))


TEMP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEMP.my-company-name.com)
    )
  )


TMFDE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TMFDE.my-company-name.com)
    )
  )

The listener.ora looks like this:

## initially copied from linuxoracle12 (20200617-1117)

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = TMF)
   (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
   (SID_NAME = TMF)
  )
  (SID_DESC =
   (GLOBAL_DBNAME = TEMP)
   (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
   (SID_NAME = TEMP)
  )
  (SID_DESC =
   (GLOBAL_DBNAME = TMFDE)
   (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
   (SID_NAME = TMFDE)
  )
 )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

As an example the initTEMP.ora looks like this:

## initially generated with dbca
## 20200617 added: *.local_listener='LISTENER_TEMP'
TEMP.__data_transfer_cache_size=0
TEMP.__db_cache_size=855638016
TEMP.__inmemory_ext_roarea=0
TEMP.__inmemory_ext_rwarea=0
TEMP.__java_pool_size=0
TEMP.__large_pool_size=16777216
TEMP.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
TEMP.__shared_io_pool_size=0
TEMP.__shared_pool_size=301989888
TEMP.__streams_pool_size=0
TEMP.__unified_pga_pool_size=0
*.audit_file_dest='/opt/oracle/admin/TEMP/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/temp/TEMP/controlfile/o1_mf_hgkpt5t0_.ctl','/opt/oracle/fast_recovery_area/TEMP/controlfile/o1_mf_hgkpt5w0_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/oracle/oradata/temp/'
*.db_domain='ubs-hainer.com'
*.db_name='TEMP'
*.db_recovery_file_dest='/opt/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TEMPXDB)'
*.local_listener='LISTENER_TEMP'
*.open_cursors=300
*.pga_aggregate_target=378m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1134m
*.undo_tablespace='UNDOTBS1'

So far so good... I thought. But regardless of what I try, I cannot get the DB instances to register with the listener. When I run lsnrctl services, I get the following:

[oracle@linuxoracle19 admin]$ lsnrctl services

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 12:11:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxoracle19.my-company-name.com)(PORT=1521)))
Services Summary...
Service "TEMP" has 1 instance(s).
  Instance "TEMP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "TMF" has 1 instance(s).
  Instance "TMF", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "TMFDE" has 1 instance(s).
  Instance "TMFDE", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

In various posts and what not, I have read that status UNKNOWN, whilst not seriously bad, actually means that the service in question is not registered with the listener. This is exemplified when I try to connect using NetBeans or SQLDeveloper. In both cases I get the error:

IO Error: The Network Adapter could not establish the connection

Before anybody asks, yes, I did try tnsping. When I did, I got:

[oracle@linuxoracle19 dbhome_1]$ tnsping TEMP 5

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 16:02:19

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

Used parameter files:
/opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEMP.my-company-name.com)))
OK (10 msec)
OK (0 msec)
OK (0 msec)
OK (0 msec)
OK (10 msec)
[oracle@linuxoracle19 dbhome_1]$ trcroute TEMP

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 16:05:39

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client        Time and address of entry into node:
-------------------------------------------------------------
17-JUN-2020 16:05:39 ADDRESS= PROTOCOL=TCP  HOST=linuxoracle19.my-company-name.com  PORT=1521

Node: Server        Time and address of entry into node:
-------------------------------------------------------------
17-JUN-2020 16:05:39 ADDRESS= PROTOCOL=TCP  HOST=linuxoracle19.my-company-name.com  PORT=1521

Can anybody out there tell me what is going wrong? I really have no idea why the current setup doesn't work.

Edit 20200618:

I have researched some more and made a few changes. Although these have not been successful, I feel it is important to mention them.

Firstly, after reading the answer from pifor and reading up on the database init parameters corresponding to the listener settings (Section 9.2.1 of the Oracle 19 Net Services Administrator's Guide) I added The following settings to the initTEMP.ora:

*.instance_name='TEMP'
*.service_names='TEMP.linuxoracle19.my-company-name.com'

At the same time, based on some more Oracle documentation, I altered the domain parameter to the host name of the server

*.db_domain='linuxoracle.my-company-name.com'

This also required changing the value of GLOBAL_NAME on the DB instance:

SQL> UPDATE GLOBAL_NAME SET GLOBAL_NAME ='TEMP.LINUXORACLE19.MY-COMPANY-NAME.COM';

Finally, I altered the the service name in tnsnames.ora

(SERVICE_NAME = TEMP.linuxoracle19.my-company-name.com)

I made corresponding changes for the other database instances. Then I took the following steps:

  • Stopped all 3 databases.
  • Restarted the listener with lsnrctl stop and lsnrctl start (yes, I'm aware there is a reload command).
  • Restarted the TEMP and TMF instances.

Checking the listener status with lsnrctl status revealed the following:

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-JUN-2020 11:57:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxoracle19.my-company-name.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                18-JUN-2020 11:51:40
Uptime                    0 days 0 hr. 5 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/linuxoracle19/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxoracle19.my-company-name.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "TEMP.linuxoracle19.my-company-name.com" has 2 instance(s).
  Instance "TEMP", status UNKNOWN, has 1 handler(s) for this service...
  Instance "TEMP", status READY, has 1 handler(s) for this service...
Service "TEMPXDB.linuxoracle19.my-company-name.com" has 1 instance(s).
  Instance "TEMP", status READY, has 1 handler(s) for this service...
Service "TMF.linuxoracle19.my-company-name.com" has 2 instance(s).
  Instance "TMF", status UNKNOWN, has 1 handler(s) for this service...
  Instance "TMF", status READY, has 1 handler(s) for this service...
Service "TMFDE.linuxoracle19.my-company-name.com" has 1 instance(s).
  Instance "TMFDE", status UNKNOWN, has 1 handler(s) for this service...
Service "TMFXDB.linuxoracle19.my-company-name.com" has 1 instance(s).
  Instance "TMF", status READY, has 1 handler(s) for this service...

I figured that looked pretty good. So I tried connecting with SQL Developer, NetBeans, and one of our own tools. In each case I got the error message:

IO Error: The Network Adapter could not establish the connection

In the case of SQL Developer , it also metioned a "Vendor Code 17002". Ishould mention that each of the tools in question use JDBC to connect to a database.

So, as yet I have no solution to the problem. Any further tips would be much appreciated.

Edit#2 20200618

In addition to the above, a colleague suggested that the host machine doesn't "know" what it's external name is. Therefore I should add the host name as used from a remote site to the loop-back address in /etc/hosts file. I was skeptical, but it makes sort of sense, since DNS plays no role when the machine is "talking to itself". So the /etc/hosts now looks like this:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 linuxoracle19 linuxoracle19.my-company-name.com
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 linuxoracle19 linuxoracle19.my-company-name.com

(Note the last two entries in the IPV4 and IPV6 loop-back entries.) Unfortunately this had no effect, even after restarting the listener.

A further suggestion from pifor was to try connecting on with sqlplus (I assume that meant "connect from a remote site"). I therefore logged onto the other oracle host (linuxoracle12) and tried it out. The result:

[oracle@linuxoracle12 admin]$ sqlplus tmf/xxxxxxxx@linuxoracle19.my-company-name.com:1521/TEMP

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 18 17:42:21 2020

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

ERROR:
ORA-12543: TNS:destination host unreachable

OK, that's something I can start with. To make sure I'd done the test correctly, I tried it in the other direction (i.e. connect to a DB instance on linuxoracle12 when logged onto linuxoracle19):

[oracle@linuxoracle19 bin]$ sqlplus tmf/xxxxxxxx@linuxoracle12.my-company-name.com:1521/TMF

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 18 16:46:17 2020
Version 19.3.0.0.0

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

Letzte erfolgreiche Anmeldezeit: Do Jun 18 2020 15:22:27 +02:00

Verbunden mit: 
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

So now I have to figure out why the first test failed whereas the second worked.

Edit#3 20200706

The preceding information makes it pretty clear that the problem is one in networking, rather than in the database setup, Following an extended chat session with pifor the following aspects were checked:

  • Ensure SELinux is disabled.
  • Ensure that no firewall is running on the host server.
  • Check that the port for the listener service is open (on Linux use nmap).
  • As mentioned by pifor in the answer below, ensure that the service names match between the init.ora and tnsnames.ora. (Note: in init.ora the property is called service_names, i.e. plural).
  • After a database or server restart, remember to restart the listener service. ;-)

Once I'd done all this, the database was usable.

2

Oracle Net configuration looks OK: local_listener looks OK. We should see the instance registered with the listener: doublecheck that local_listener parameter is the one used by database instance with show parameter local_listener. Are you sure that database instance is not using SPFILE and if yes, the displayed PFILE is the right one ?

What is missing in the database configuration is service_name: that does not explain why the instance is not registered but you need to fix this if you have in tnsnames.ora.

PS: check also that all hostnames used in any Oracle Net files are also defined in /etc/hosts or in DNS.

7
  • Firstly I should mention that the correct database config parameter name is service_names (plural). But that didn't help. Secondly, the host name is resolved with DNS, or should be, anyway. I access the server via a console with no trouble. To be sure though, I added it to the /etc/hosts on my local machine, but that also made no difference. I will edit the original post with the things i have researched an tried. – Achim Schmitz Jun 18 '20 at 10:26
  • You are right for the service_names typo. Instead of using SQL Developer, please use sqlplus command line so that we can check exactly what is the full connect string you are using: could you post the full sqlplus command line that triggers the same error message as SQL Developer ? – pifor Jun 18 '20 at 11:42
  • Now you have instance TEMP dynamically registered in listener because you have Instance "TEMP", status READY, has 1 handler(s) for this service : the difference is status READY meaning dynamic registration (vs UNKNOWN meaning static registration). – pifor Jun 18 '20 at 11:51
  • Static registration is a result of the entries for TEMP, TMF, and TMFDE in the listener.ora file. These entries are strictly speaking not required. The dynamic registration happens automatically when the database instance is started. I've been using sqlplus all along to make property changes, generate spfiles, and start/stop the DB instances. It's been working all along. What I haven't tried is to connect from a remote console. I guess that's the next step. – Achim Schmitz Jun 18 '20 at 13:23
  • I have done as you suggested. I've added the details to the original post. – Achim Schmitz Jun 18 '20 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.