I installed Oracle XE 21c on a Windows 11 Pro laptop as a portable sandbox (an Oracle environment where I could try out SQL/PL-SQL ideas while on a plane, for example). Installation of Oracle Database XE and access to the cbd and pdb initially worked as expected after changing the Hostname to one shorter than 17 characters (this is apparently a known installation bug). From the beginning, my local PC was represented in Listener and TNS files as Bob-Laptop.attlocal.net (never as LocalHost, 127.0.0.1, or my attlocal.net IP address (currently 192.168.1.236). i work from home, use AT&T fiber internet and my PC IPs are assigned by DHCP.
After a background Windows update forced a reboot, the databases were no longer available in either SQL Developer or SQLPlus with different error messages. I invested a considerable amount of work into one of the database and I want to find a way to regain access to it (or, if that's impossible, recreate it from the data files). Of course, I could reinstall Oracle Database XE, but I think that would just set up new databases, not regain access to the existing databases.
In SQL Developer:
In SQLPlus:
C:\Users\Bob>SQLPlus sys/XXXXXXXXXX@xe
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Sep 14 08:29:26 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
However, the container database XE still exists according to the Database Configuration Assistant:
The pluggable database also still exists and is open for read and write according to the DCA:
The following is in my listener.ora file in the folder that the TNS_ADMIN environment variable points to:
LISTENER_XEPDB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Bob-Laptop.attlocal.net)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
lsnrctl status: (executed in Command Prompt, run as Administrator)
C:\Users\Bob>lsnrctl status
LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 14-SEP-2023 12:15:51
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_XEPDB1
Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date 14-SEP-2023 00:40:23
Uptime 0 days 11 hr. 35 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\Bob\product\21c\homes\OraDB21Home1\network\admin\listener.ora
Listener Log File C:\app\Bob\product\21c\diag\tnslsnr\Bob-Laptop\xepdb1\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Bob-Laptop)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
The listener supports no services
The command completed successfully
I believe the issue is indicated by "The listener supports no services". The services that existed and were working properly before the reboot seem to have disappeared after the reboot. In reading other threads similar to this one, it was mentioned that if the host was on DHCP and was assigned a different IP address after a reboot, that could be part of the issue with Oracle Database XE.
I attempted to create a new service using the srvctl command (with -db set to the Container database and -pdb set to the Pluggable database), but received the following errors:
C:\Windows\System32>srvctl add service -db xe -service xepdb1 -pdb xepdb1
PRCD-1027 : Failed to retrieve database xe
PRCR-1070 : Failed to check if resource ora.xe.db is registered
CRS-0184 : Cannot communicate with the CRS daemon.
Is there a way to either create a service that accesses the databases, or recreate the databases from the data files? The second option seems like a bit much if I have to do this every time the PC reboots.
Thanks
Bob