Express Edition 18c ORA-12505 listener does not currently know of SID given in connect descriptor

Brief Solution:

Oracle Database Express Edition (XE) 18c was easy to setup.  However, I was surprised when I tried to login to the database today and received the ORA-12505 error message.  The problem was a different IP Address assigned to my computer.  These IP addresses are located in 2 configuration files for the TNS Listener.

• ORACLE_HOME/network/admin/listener.ora
• ORACLE_HOME/network/admin/tnsnames.ora

I was able to connect after correcting the IP address for my computer in both files.

More Details:

First, the TNS Listener typically uses the listener.ora file for startup configuration.  The default name for the TNS Listener is LISTENER.  The LISTENER section of the listener.ora file includes the HOST.  This tells the LISTENER what interface/network to open for reading.

The XE 18c installation used an IP address for this HOST setting.  In my case, DHCP assigned a different IP address to my computer when I connected.  The old IP address would not allow the TNS Listener to run.  After correcting the IP address in the listener.ora file, the TNS Listener ran, but the database would not register with it.

The database parameter LOCAL_LISTENER tells the database where to find the TNS Listener.  The XE 18c installation sets this value to LISTENER_XE.  When the database attempts to register, it uses the tnsnames.ora file to find the LISTENER_XE alias.  Similar to the listener.ora file, the tnsnames.ora file has a LISTENER_XE section that includes a HOST value.  After correcting the IP address in the tnsnames.ora file, the database successfully registered with the TNS Listener.

Oracle Documentation:

About Service Registration (Net Services Administrator's Guide)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/understanding-oracle-net-architecture.html#GUID-A47F7687-1F80-48B1-8E76-7FF9D410560A

If the listener is not running when an instance starts, then the LREG process cannot register the service information. LREG attempts to connect to the listener periodically, but it may take up to 60 seconds before LREG registers with the listener after it has been started. To initiate service registration immediately after the listener is started, use the SQL statement ALTER SYSTEM REGISTER.

Overview of Oracle Net Listener (Net Services Administrator's Guide)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-A3263EB4-C3F2-4529-ABC2-ADE749114D33

Because the configuration parameters have default values, it is possible to start and use a listener with no configuration. This default listener has a name of LISTENER...

Registering Information with a Local Listener (Net Services Administrator's Guide)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-0E7C39E3-4627-403A-AE69-E9AA2C7E4C57

Synchronization occurs when the protocol address of the listener is specified in the listener.ora file and the location of the listener is specified in the initialization parameter file.
To have the LREG process register with a local listener ..., configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.


Protocol Parameters (Net Services Reference)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netrf/protocol-address-configuration.html#GUID-71A0702F-A5C6-4122-907D-5E9BFA1DCE45

The listener and Oracle Connection Manager are identified by protocol addresses.
The following table lists the parameters used by the Oracle protocol support: