cannot connect to newly installed Oracle 19c single instance DB

Question

I just installed a new Oracle 19c single instance database and I cannot connect to it, and I'm not sure how to debug the problem.

I defined the SID to be "globaldb". The listener is started and is listening on port 1539. tnsping is happy. lsnrctl status shows output that makes me believe the DB is up. But I am not able to connect to this DB using either sqlplus or sqldeveloper. Can you advise me how to approach debugging this problem?

% tnsping globaldb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 30-APR-2022 06:11:45

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

Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = GLOBALDB))) OK (0 msec)

I have, for temporary debug purposes, disabled the firewall:

[root@pca-flexcube-5 bin]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) [root@pca-flexcube-5 bin]#

% sqlplus 'system/Welcome1@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1539)))(CONNECT_DATA=(SID=GLOBALDB)))'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 30 06:12:05 2022 Version 19.3.0.0.0

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

ERROR: ORA-12537: TNS:connection closed

Enter user-name:

listener.ora:

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 100.111.161.49)(PORT = 1539)) ) )

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = globaldb) ) )

tnsnames.ora:

LISTENER_CDB = (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1539))

globaldb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = GLOBALDB) ) )

CDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB) ) )

PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB1) ) )

[root@pca-flexcube-5 opc]# systemctl status dbora.service -l ● dbora.service - The Oracle Database Service Loaded: loaded (/usr/lib/systemd/system/dbora.service; enabled; vendor preset: disabled) Active: active (running) since Sun 2022-05-01 03:29:11 GMT; 8s ago Main PID: 48553 (start_all.sh) Tasks: 41 (limit: 99958) Memory: 4.8G CGroup: /system.slice/dbora.service ├─48553 /bin/bash /home/oracle/scripts/start_all.sh ├─48569 /bin/sh /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart /u01/app/oracle/product/19.0.0/dbhome_1 ├─48615 /bin/sh /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart /u01/app/oracle/product/19.0.0/dbhome_1 ├─48667 sqlplus
├─48672 ora_pmon_globaldb ├─48674 ora_clmn_globaldb ├─48676 ora_psp0_globaldb ├─48678 ora_vktm_globaldb ├─48709 ora_gen0_globaldb ├─48712 ora_mman_globaldb ├─48718 ora_gen1_globaldb ├─48721 ora_diag_globaldb ├─48723 ora_ofsd_globaldb ├─48726 ora_dbrm_globaldb ├─48728 ora_vkrm_globaldb ├─48730 ora_svcb_globaldb ├─48732 ora_pman_globaldb ├─48734 ora_dia0_globaldb ├─48736 ora_dbw0_globaldb ├─48738 ora_lgwr_globaldb ├─48740 ora_ckpt_globaldb ├─48742 ora_lg00_globaldb ├─48744 ora_smon_globaldb ├─48746 ora_lg01_globaldb ├─48748 ora_smco_globaldb ├─48750 ora_reco_globaldb ├─48752 ora_w000_globaldb ├─48754 ora_lreg_globaldb ├─48756 ora_w001_globaldb ├─48758 ora_pxmn_globaldb ├─48762 ora_mmon_globaldb ├─48764 ora_mmnl_globaldb ├─48766 ora_d000_globaldb ├─48768 ora_s000_globaldb ├─48770 ora_tmon_globaldb ├─48771 oracleglobaldb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) ├─48774 ora_m000_globaldb └─48776 ora_m001_globaldb

May 01 03:29:11 pca-flexcube-5 systemd[1]: Started The Oracle Database Service. May 01 03:29:11 pca-flexcube-5 bash[48553]: The Oracle base remains unchanged with value /u01/app/oracle May 01 03:29:11 pca-flexcube-5 bash[48553]: Processing Database instance "globaldb": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log [root@pca-flexcube-5 opc]#

Answer 1

The key problem here was that my only experience with Oracle databases had taken place with their old products before they changed to the multitenant architecture of recent years. Under the multitenant architecture, the pluggable database does not automatically come up. So it was necessary to connect to the container database and request that it be brought up:

sqlplus / as sysdba alter pluggable database YOUR_DATABASE_NAME open;

By default you have to do this after every machine restart. To avoid this burden you can do the following and make it automatic that the pluggable database comes up on reboot:

alter pluggable database YOUR_DATABASE_NAME save state;