Database: XE18 running on windows 2019 standard edition
I recently upgraded to XE18 from XE11, with client apps connecting to it via 12c client, there were no issues once I did the initial upgrade and then imported needed schemas only after a few practice runs. There is only one PDB called XEPDB1. The NIC of the server has 3 ports, one for maintenance, one with a fixed IP and one with DHCP, only the maintenance & fixed IP ports being used.
When the tech powered down & moved the server, he at first plugged both network cables into the fixed IP and DHCP ports, then realized what he did, changed to the way it was before and restarted server.
Issue was I could no longer connect to the XEPDB1 database, with locally or from remote machines, I got the ora-12518 error, and a look at the listener log stack showed ORA-12518, ORA-12560, ORA-00530 then 64-bit Windows Error: 203 : Unknown error.
Lsnrctl status showed no service for XE, and the service for XEPDB1 showed as UNKNOWN.
XEPDB1 was static registered as a database in the listener.ora and I even added the XE database as static entry to see if that would help. In the CDB it only showed XE as a service_names parameter and local listener was called LISTENER_XE in the database, but was called LISTENER in the listener & tnsnames.ora file but changing that to match & restarting listener did not let me connect either.
What finally worked, and I don't know if it was one or more of the steps that fixed it was this:
alter system set service_names = 'XEPDB1, XE' scope=both;
ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))' SCOPE=BOTH;
alter system register;
restart listener
Now lsnrctl status shows both XE as well as now XEXDB services with status of READY and XEPDB1 with status still UNKNOWN, but I can connect now to XEPDB1 with application & SQL remotely and local.
It also shows 2 instances for XE and XEPDB1, which I assume is it registering dynamically and static. Should the XEPDB1 show as READY instead of UNKNOWN? But I can still connect to it so does the XE CDB just handle that when I connect to the XEPDB via TNS?
I assume it was because XE wasn't registering itself with the database for whatever reason that it couldn't connect to the XEPDB1, as I couldn't connect to XE with a tns alias either. And I'm note sure why it suddenly started registering okay, both dynamically and static, when this wasn't an issue before the move.
I just want to find out what went wrong and why, and what exactly fixed it.
thanks in advance.