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 stopandlsnrctl start(yes, I'm aware there is areloadcommand). - 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/[email protected]: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/[email protected]: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.oraandtnsnames.ora. (Note: ininit.orathe property is calledservice_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.
