0

I recently faced a weird problem where I cannot use/log into any Oracle user nor even System account as well as not being able to access my database. I have a Windows 10 64 bits Home edition, and I did multiple new installation of both Oracle XE 11g release 2 and Oracle XE 18c. When I installed the XE 11g R2, and when trying to connect to SQLplus using the command line I get the following error enter image description here

So searched for this error and I found that it is probably related to the database as it is not being mounted correctly everytime: I did the following: enter image description here

Now I get the ORA-00119 & ORA00130 ERRORS, and I honeslty don't know where is the problem especially because all of these problems do not occur when I'm using an Internet Connexion using WIFI at home. If anyone got any idea on how I can fix this please share! Here are the following settings I currently have for Oracle:

LSNRCTL status: enter image description here

the C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listner.ora

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) (PROGRAM = extproc) ) )

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = ASUS-AlexLutinNoir)(PORT = 1521)) ) )

DEFAULT_SERVICE_LISTENER = (XE)

**the C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora **

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ASUS-AlexLutinNoir)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

The services that are running for oracle : enter image description here

My envirement variables: enter image description here

2 Answers 2

2
sqlplus / as sysdba
create pfile from spfile;
exit

Now edit C:\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora. Remove the line with local_listener, you use the default 1521 port on your local machine, you do not need that line. Once done:

sqlplus / as sysdba
create spfile from pfile;
startup

The problem is that your hostname is ASUS-ALEXLUTINNOIR, but only the first 15 characters was put in the local_listener parameter (ASUS-ALEXLUTINN). The database can't resolve that hostname when starting up. In such a simple configuration you do not need the local_listener set, just leave it blank (remove), so the database will register itself to the listener running on the default port (1521).

4
  • Here is what I get from the create pfile from spfile; command: ERROR at line 1: ORA-01565: error in identifying file '%ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) Le fichier sp┐cifi┐ est introuvable. ( means that the following file could not be found) Jun 28, 2019 at 16:04
  • Sounds like environment variables weren't set. For XE, this can be caused by not installing as an Administrator. I prefer to "right click->run as administrator" for installation. Jun 28, 2019 at 16:10
  • in the initXE.ORA file I only find the following line:SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora' Jun 28, 2019 at 16:15
  • so previously the command didn't work, and when opening the initXE file I get a path to SPFILEXE to 'C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora I manually went to that directory and copy/paste the file into my C:\oraclexe\app\oracle\product\11.2.0\server\database I was able to execute the 1st block of the asnwer where it says: create pfile from spfile; now I have some content into my initXE.ora file but there is no local_listener line anywhere, only audit_file_dest, .compatible, .control_files,.db_name,DB_RECOVERY_FILE_DEST_SIZE,... Jun 28, 2019 at 16:26
0

So I solved my problem by: 1) changing my computer name so it fits the 15 15 characters. 2) Re-installing the Oracle XE11gR2 and running the setup as administrator (even tho' the pop up window would still ask for admin perm with or without the run as admin option). 3) Shutdown and startup mount were not followed with errors anymore and everything seems to be working, except that I still have no spfile in oracle_home\database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.