5

I've had it. I just can't get this to work. I installed Oracle XE 21c on my Windows 10 desktop. The installation went ok, once I ran the Setup file as an administrator. I basically chose all the defaults.

So now it is running, and there was a default database XE create for me. But I get this when attempting to connect as SYSTEM using SQLPlus:


SQL*Plus: Release 21.0.0.0.0 - Production on Mon Nov 1 19:08:25 2021
Version 21.3.0.0.0

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

Enter user-name: SYSTEM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

I looked and there is no tnsnames.ora or listern.ora file, so I attempted to create them, though I am not sure they are correct.

C:\app\sysde\product\21c\dbhomeXE\network\admin\listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

C:\app\sysde\product\21c\dbhomeXE\network\admin\tnsnames.ora

LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

XE = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

I then stopped the listener as follows lsnrctl stop

which yielded this:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

The listener OracleOraDB21Home1TNSListener is no longer running in services.msc. When trying to restart it I get this...

Error 2: The system cannot find the file specified.

My environment variables are:

  ORACLE_HOME=C:\app\sysde\product\21c\dbhomeXE\
  TNS_ADMIN=C:\app\sysde\product\21c\dbhomeXE\network\admin

I know there are numerous "Can't connect to Oracle" posts. I've tried bits and pieces of them, but none are really suited to my situation and the parts I've tried simply haven't helped. Any suggestions you have to allow me to use the default XE database and connect to it through a GUI such as SQL Developer would be tremendously helpful.

JRomeo
  • 171
  • 1
  • 8
  • The first thing to look at on a ORA-12154 is your actual connection command - like 'sqlplus username/password@dbname'. In this example, 'dbname' is the connect string and must be found in your tnsnames.ora. You show your tnsnames.ora, but not the connect string. At this point the request was never even sent to the listener, so your listener issues are unrelated to your ORA-12154. Please update your post to show your sqlplus connection attempt and response -- all together as a continuous copy and paste from the command session, or a screenshot of your SQL Dev connection properties. – EdStevens Nov 02 '21 at 02:05
  • Post updated as requested. – JRomeo Nov 02 '21 at 02:10
  • And what did my previous comment say about sqlplus syntax - including the '@dbname'? In your case, given your tnsnames.ora, try 'sqlplus system/@xe' (emphasis on the '@xe'). or simply 'sqlplus' (as you apparently did but did not show) and when it prompts for username, respond with 'system@xe'. – EdStevens Nov 02 '21 at 02:24
  • When I open SQLPlus, it automatically prompts me for a username. Sorry, rookie mistake. – JRomeo Nov 02 '21 at 11:41

2 Answers2

4

As per the docs, from 21c onwards the default software installation is the read only home layout, which has been available since 18c

https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/read-only-oracle-home-default-366970591.html

So by default the sqlnet configuration files are not going to be under ORACLE_HOME/network/admin.

C:\oracle\product\21c\dbhomeXE\network\admin>dir
 Volume in drive C is System
 Volume Serial Number is EEB1-910D

 Directory of C:\oracle\product\21c\dbhomeXE\network\admin

13/10/2021  10:53 AM    <DIR>          .
13/10/2021  10:53 AM    <DIR>          ..
13/10/2021  10:51 AM    <DIR>          sample
13/10/2021  10:52 AM               263 sqlnet.ora.rooh
               1 File(s)            263 bytes
               3 Dir(s)  78,346,031,104 bytes free

C:\oracle\product\21c\dbhomeXE\network\admin>cd ..\..\..\homes\OraDB21Home1\network\admin

C:\oracle\product\21c\homes\OraDB21Home1\network\admin>dir
 Volume in drive C is System
 Volume Serial Number is EEB1-910D

 Directory of C:\oracle\product\21c\homes\OraDB21Home1\network\admin

13/10/2021  10:54 AM    <DIR>          .
13/10/2021  10:54 AM    <DIR>          ..
13/10/2021  10:53 AM               656 listener.ora
13/10/2021  10:53 AM               465 sqlnet.ora
13/10/2021  10:53 AM               263 sqlnet21101310AM5351.bak
13/10/2021  02:51 PM               852 tnsnames.ora
               4 File(s)          2,236 bytes
               2 Dir(s)  78,345,871,360 bytes free

You don't need to set ORACLE_HOME or TNS_ADMIN. You just need to edit the files in the 'homes' area and you'll be good to go.

A complete top to bottom install video is available here from the Windows team

https://youtu.be/DYleroLay5E

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • 1
    Thanks. This really helped. The video was especially helpful. I still got connection errors even though I followed the installation to the letter. The problem was I was using localhost as my server instance to connect, but the installer grabbed my assigned IP address instead and wrote that to the tnsnames and listener files. Changing that up did the trick! – JRomeo Nov 02 '21 at 11:39
2

OracleXE21 default path for tnsnames.ora and sqlnet.ora has been changed in Express Edition 21c. The new path will be like this: C:\oracle\product\21c\homes\OraDB21Home1\network\admin and there where you will find tnsnames.ora and sqlnet.ora.

Nasser Al-Mawaali
  • 21
  • 1