CODEWITHSUNDEEP
oracle-databaseoracle-xeoracle-xe-18.4
user3230660
user3230660

Reputation:

OracleXE installation and permission problems

Reference:

How to connect using PDB userid on Oracle 18c XE?

TNS Protocol adapter error

Product I am trying to install / configure: OracleXE 18c

Problem 1:

Attempting to log in as described in the Installation Guide returns the error shown below:

sqlplus / as sysdba

ORA-12560: TNS:protocol adapter error

Problem 2:

The following command fails immediately before asking for a password:

sqlplus system@localhost:1521/XEPDB1

 ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: system

Enter password:

ERROR:

ORA-12560: TNS:protocol adapter error

Problem 3:

I tried ALTER PLUGGABLE DATABASE ALL OPEN; as is mentioned in one of the the reference issues cited at the top of this post. The command completes successfully. However trying run STARTUP as mentioned here results in an error:

SQL> startup;
ORA-01031: insufficient privileges
ORA-01078: failure in processing system parameters
SQL>

Running whoami at the windows command prompt returns my user name which has admin priviliges and is the same one used to install OracleXE.

Additional Background:

I am able to log in as follows:

sqlplus system@localhost:1521

When I installed Oracle XE it created tnsnames.ora and listner.ora using mymachine.mydomain.com as host. The completion screen of the installer told me to log in using localhost:1521 which does not work. After several hours of research I discovered that if I change mymachine.mydomin.com to localhost in tnsnames.ora and listener.ora I could log in as system@localhost:1521. The above three problems remain however.

Running select value from v$parameter where name='service_names' as described in the ticket referenced above returns only one row: XE. Should it also return a row for XEPDB1?

tnsnames.ora

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

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

listner.ora

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\myname\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\myname\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )
    (SID_DESC =
      (SID_NAME=XE)
      (ORACLE_HOME=C:\app\myname\product\18.0.0\dbhomeXE)
      (GLOBAL_DBNAME=XE)
    )
    (SID_DESC =
      (SID_NAME=XEPDB1)
      (ORACLE_HOME=C:\app\myname\product\18.0.0\dbhomeXE)
      (GLOBAL_DBNAME=XEPDB1)
    )
  )

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

lsnrctl status

LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 23-APR-2021 15:55:00

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 - Production
Start Date                23-APR-2021 12:09:44
Uptime                    0 days 3 hr. 45 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\OracleClient\listener.ora
Listener Log File         C:\app\myname\product\18.0.0\diag\tnslsnr\mymachine\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
Service "XEPDB1" has 1 instance(s).
  Instance "XEPDB1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\OracleClient\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Upvotes: 0

Views: 609

Answers (2)

Bjarte Brandt
Bjarte Brandt

Reputation: 4471

If you omit password when connecting through sqlplus you need escaping. This was my experience when I used WindowsOS. I just tried Easy Connect on my MacOS where it works with or without escaping.

REM password in clear text. 
C:\>sqlplus user/password@host:port/SERVICE_NAME
REM you will be prompted for password
C:\>sqlplus user@"\host:port/SERVICE_NAME\"

Best of luck!

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

  • Problem 1:

To use OS authentication on Windows your user should be in ORA_DBA group and sqlnet.ora must contain

SQLNET.AUTHENTICATION_SERVICES= (NTS)

updated: I see NTS was configured already, so just check if you are in the ORA_DBA group

  • Problem 2:

EZCONNECT: try this syntax with //

sqlplus system@//localhost:1521/XEPDB1
  • Problem 3:

You need to check the alert.log. You can save init parameters and post your parameter here:

alter system create pfile='c:/temp/init.ora' from spfile;

and then just post this file and startup errors from alert.log

Upvotes: 0

Related Questions