Forum Stats

  • 3,734,235 Users
  • 2,246,914 Discussions
  • 7,857,193 Comments

Discussions

Why does SQLcl fail to connect thinclient to the XE database?

I installed Database Express Edition (XE) 18c and can connect to the default XE database using SQL*Plus without issue.

I had a previous installation of SQLcl 20.4 that I can still use to connect to *other oracle databases as shown below. 

sql\sqlcl\bin> ./sql -verbose -noupdates /nolog

SQLcl: Release 20.4 Production on Wed Apr 14 09:53:06 2021

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

2021-04-14 09:53:06.668 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found ORACLE_HOME:C:\app\rmj6\oracle_express\18.0.0\dbhomeXE\dbs

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:null

2021-04-14 09:53:06.700 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:null


SQL> CONNECT rmj6/[email protected]//aup-banrodb11.it.usf.edu:1526/PROD_RO


2021-04-14 10:13:24.604 INFO  oracle.dbtools.raptor.utils.TNSHelper checkForTns Checking for tnsnames.* in :C:\Users\rmj6

2021-04-14 10:13:24.605 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking Registry for ORACLE_HOME

2021-04-14 10:13:24.606 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

2021-04-14 10:13:24.607 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found key for TNS_ADMIN:null

2021-04-14 10:13:24.607 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found ORACLE_HOME:C:\app\rmj6\oracle_express\18.0.0\dbhomeXE

2021-04-14 10:13:24.608 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSEntries Using TNS info from :C:\app\rmj6\oracle_express\18.0.0\dbhomeXE\network\admin

2021-04-14 10:13:24.720 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL Attempting to connect using URL= "jdbc:oracle:oci8:@//aup-banrodb11.it.usf.edu:1526/PROD_RO"

2021-04-14 10:13:24.759 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:<clinit>:38:No Message

2021-04-14 10:13:24.759 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:<clinit>:45::ORest driver loaded

2021-04-14 10:13:25.252 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:acceptsURL:80:No Message

2021-04-14 10:13:25.471 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL Attempting to connect using URL= "jdbc:oracle:thin:@//aup-banrodb11.it.usf.edu:1526/PROD_RO"

2021-04-14 10:13:25.472 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:acceptsURL:80:No Message

Connected.

2021-04-14 10:13:27.302 INFO  oracle.dbtools.raptor.scriptrunner.commands.NLSLANGListener runOnConnect NLS_LANG is not set


SQL>



HOWEVER I am unable to connect to XE using SQLcl


SQL> connect sys/[email protected]:1521/XE as SYSDBA


2021-04-14 10:50:12.025 INFO  oracle.dbtools.raptor.utils.TNSHelper checkForTns Checking for tnsnames.* in :C:\Users\rmj6

2021-04-14 10:50:12.025 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking Registry for ORACLE_HOME

2021-04-14 10:50:12.025 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

2021-04-14 10:50:12.030 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found key for TNS_ADMIN:null

2021-04-14 10:50:12.030 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found ORACLE_HOME:C:\app\rmj6\oracle_express\18.0.0\dbhomeXE

2021-04-14 10:50:12.030 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSEntries Using TNS info from :C:\app\rmj6\oracle_express\18.0.0\dbhomeXE\network\admin

2021-04-14 10:50:12.061 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL Attempting to connect using URL= "jdbc:oracle:oci8:@localhost:1521/XE"

2021-04-14 10:50:12.061 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:<clinit>:38:No Message

2021-04-14 10:50:12.077 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:<clinit>:45::ORest driver loaded

2021-04-14 10:50:12.187 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:acceptsURL:80:No Message

2021-04-14 10:50:12.279 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL Attempting to connect using URL= "jdbc:oracle:thin:@localhost:1521/XE"

2021-04-14 10:50:12.279 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:acceptsURL:80:No Message

 USER     = sys

 URL      = jdbc:oracle:oci8:@localhost:1521/XE

 Error Message = no ocijdbc21 in java.library.path

 USER     = sys

 URL      = jdbc:oracle:thin:@localhost:1521/XE

 Error Message = ORA-01017: invalid username/password; logon denied



I do not understand why I get this no ocijdbc21 in java.library.path error when connecting to one database but not another.


Any assistance would be greatly appreciated.

Best Answer

  • User_OOTEX
    User_OOTEX Member Posts: 2 Green Ribbon
    Accepted Answer

    https://helpcenter.netwrix.com/NA/Configure_IT_Infrastructure/Accounts/DCA_Oracle.html

     

    led me to use sqlplus to Grant “CREATE SESSION” to user JD

     

    PS C:\Users\rmj6> sqlplus /nolog

     

    SQL*Plus: Release 18.0.0.0.0 - Production on Tue May 4 15:17:08 2021 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved.

     

    SQL> conn system/[email protected]:1521/XEPDB1 as sysdba

    Connected.

    SQL> GRANT CREATE SESSION TO JD;

    Grant succeeded.

     

    Now miraculously, I can now FINALLY use SQLcl to:

     

    connect jd/[email protected]:1521/XEPDB1

     

    SQL> show connection

    CONNECTION:

    [email protected]:oracle:thin:@localhost:1521/XEPDB1

    CONNECTION_IDENTIFIER:

    localhost:1521/XEPDB1

    CONNECTION_DB_VERSION:

    Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

    Version 18.4.0.0.0

    NOLOG:

    false

    PRELIMAUTH:

    False

     

    For some reason I still can’t connect as user sys: 

    ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

    ..but that should not matter for my immediate purposes anyway.

     


    Thank you all for the assistance.

Answers

  • User_LO5J3
    User_LO5J3 Member Posts: 12 Red Ribbon

    may be the password is joedata, not Joedata

    you can local login without password like "sqlplus / as sysdba" (in windows login who installed xe) and reset password if you want

  • User_OOTEX
    User_OOTEX Member Posts: 2 Green Ribbon

    Thank you User_LO5J3 but no. This is NOT a password issue. 

    The issue seems to have something to do with the persistent java.library.path error


    The reason why I know it is not misspelled password issue is because I am able to connect to XE when using SQL*PLus as

    user:system

    password: Joedata

    as shown below.


    PS C:\Users\rmj6> sqlplus /nolog


    SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 21 08:59:32 2021

    Version 18.4.0.0.0

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

    SQL> conn system/[email protected]:1521/XEPDB1 as sysdba

    Connected.

    SQL>


    I can ALSO connect to XE using sqlplus as the newly created

    user: jd password: joedata

    As shown below.

    PS C:\Users\rmj6> sqlplus /nolog

    SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 21 09:03:19 2021

    Version 18.4.0.0.0

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

    SQL> conn jd/[email protected]:1521/XEPDB1 as sysdba

    Connected.

    SQL>


    So thank you for the thought/consideration but no, this is not a case of a misspelled password.

    Any further assistance regarding why SQLcl fails to connect thinclient to the LOCAL XE database with the  Error Message = no ocijdbc21 in java.library.path while it IS able to connect to other (not local) oracle databases would be GREATLY appreciated.

  • User_OOTEX
    User_OOTEX Member Posts: 2 Green Ribbon
    Accepted Answer

    https://helpcenter.netwrix.com/NA/Configure_IT_Infrastructure/Accounts/DCA_Oracle.html

     

    led me to use sqlplus to Grant “CREATE SESSION” to user JD

     

    PS C:\Users\rmj6> sqlplus /nolog

     

    SQL*Plus: Release 18.0.0.0.0 - Production on Tue May 4 15:17:08 2021 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved.

     

    SQL> conn system/[email protected]:1521/XEPDB1 as sysdba

    Connected.

    SQL> GRANT CREATE SESSION TO JD;

    Grant succeeded.

     

    Now miraculously, I can now FINALLY use SQLcl to:

     

    connect jd/[email protected]:1521/XEPDB1

     

    SQL> show connection

    CONNECTION:

    [email protected]:oracle:thin:@localhost:1521/XEPDB1

    CONNECTION_IDENTIFIER:

    localhost:1521/XEPDB1

    CONNECTION_DB_VERSION:

    Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

    Version 18.4.0.0.0

    NOLOG:

    false

    PRELIMAUTH:

    False

     

    For some reason I still can’t connect as user sys: 

    ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

    ..but that should not matter for my immediate purposes anyway.

     


    Thank you all for the assistance.

Sign In or Register to comment.