7 Replies Latest reply on May 11, 2020 9:51 PM by Mike301

    sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB

    dougk
    dougk Apr 16, 2018 5:50 PM

      Trying to get sqlcl to connect / as sysdba to 11.2 RAC databases on our Exadata, which has 12.2.0.1 GI & 11.2.0.4 RDBMS home.  The JRE in the 11.2 ORACLE_HOME is Java 1.5, which is not compatible with sqlcl.  This configuration is able to make ezconnect connections to the databases on this platform.

       

      alias sqlcl='/opt/sqlcl/sqlcl/bin/sql'

      export ORACLE_SID=TEST11

      export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

      export JAVA_HOME=/usr/java/jdk1.8.0_144/jre

       

      Even with these settings, the CLASSPATH is configured with directories from the ORACLE_HOME before /opt/sqlcl/sqlcl/lib entries, although only ojdbc6.jar exists in this oracle home.

      /u01/app/oracle/product/11.2.0.4/dbhome_1/jdbc/lib/ojdbc8.jar:

      /u01/app/oracle/product/11.2.0.4/dbhome_1/ojdbc8.jar:

      /u01/app/oracle/product/11.2.0.4/dbhome_1/jdbc/lib/ojdbc7.jar:

      /u01/app/oracle/product/11.2.0.4/dbhome_1/ojdbc7.jar:

      /u01/app/oracle/product/11.2.0.4/dbhome_1/jdbc/lib/ojdbc6.jar:

      /u01/app/oracle/product/11.2.0.4/dbhome_1/ojdbc6.jar:

       

      [oracle@exa011 TEST11]$ sqlcl /nolog

       

      SQLcl: Release 18.1.1 Production on Mon Apr 16 10:04:20 2018

       

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

       

      SQL> connect / as sysdba

        USER          =

        URL           = jdbc:oracle:oci8:@

        Error Message = Could not initialize class oracle.jdbc.OracleDriver

        USER          =

        URL           = jdbc:oracle:thin:@127.0.0.1:1521:TEST11

        Error Message = Could not initialize class oracle.jdbc.OracleDriver

        USER          =

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

        Error Message = Could not initialize class oracle.jdbc.OracleDriver

        USER          =

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

        Error Message = Could not initialize class oracle.jdbc.OracleDriver

       

      Setting ORACLE_HOME to the wrong home, but one with a compatible JRE, does not raise any errors, but it obviously will not connect to the proper instance either.

      [oracle@exa011 TEST11]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1

      [oracle@exa011 TEST11]$ sqlcl /nolog

      [oracle@exa02dbadm01 AXSP11]$ sqlcl /nolog

       

      SQLcl: Release 18.1.1 Production on Mon Apr 16 10:41:14 2018

       

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

       

      SQL> connect / as sysdba

      Connected to an Idle instance, startup command available.

      SQL> exit

       

      I've pretty much run out of ideas.  I would expect that the proper ORACLE_HOME must be set for connect  / as sysdba, which is causing other issues.

       

      Doug

      • 4045 Views
      • Tags:
        • 1. Re: sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB
          Barry Mcgillin-Oracle
          Barry Mcgillin-Oracle Apr 17, 2018 3:43 PM (in response to dougk)

          So, heres whats happening.

          Because you have an Oracle home set, we're going to look for JAVA in your Oracle home and use that instead of what you have set.  The reason we do this is because we assume most people using sqlcl will want a bequeath connection to their database in exactly the way you are using that.  In this case, the lib.so files and the driver need to match for this to work.

           

           

          Let me ask you this.  Can you connect via ezconnect? If so, when you do a show connection what happens

          Also, is you unset your oracle home, and try again, what happens?  There is also a 'show java' command which will tell you what you are using in the tool.

           

          B

          • 2. Re: sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB
            dougk
            dougk Apr 18, 2018 12:47 AM (in response to Barry Mcgillin-Oracle)

            Hi Barry,

             

            On this platform, have been unable to establish any type of connection (bequeath or ezconnect) when ORACLE_HOME is set to the 11.2.0.4 home.  Ezconnect works from the 12.2 ORACLE_HOME without setting JAVA_HOME, but that home exists only as a future migration path.   The sole purpose of installing sqlcl here was to take advantage of the enhanced formatting options over sqlplus, to be used with bequeath connections only.

             

            With JAVA_HOME set to the system JRE and ORACLE_HOME unset, sqlcl can establish an ezconnect connection.

             

            1.  Launching sqlcl with JAVA_HOME unset and the 11.2.0.4 ORACLE_HOME set fails:

             

            [oracle@exa021 ]$ sqlcl /nolog

            Exception in thread "main" java.lang.UnsupportedClassVersionError: Bad version number in .class file

             

             

            2.  Ezconnect with JAVA_HOME set and ORACLE_HOME set fails:

             

            [oracle@exa021 TEST11]$ sqlcl /nolog

             

            SQLcl: Release 18.1.1 Production on Tue Apr 17 11:38:21 2018

             

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

             

            SQL> connect testuser/password@exa02-scan/TEST1

              USER          = testuser

              URL          = jdbc:oracle:oci8:@exa02-scan/TEST1

              Error Message = Could not initialize class oracle.jdbc.OracleDriver

              USER          = testuser

              URL          = jdbc:oracle:thin:@exa02-scan/TEST1

              Error Message = Could not initialize class oracle.jdbc.OracleDriver

              USER          = testuser

              URL          = jdbc:oracle:oci8:@exa02-scan:1521/TEST1

              Error Message = Could not initialize class oracle.jdbc.OracleDriver

              USER          = testuser

              URL          = jdbc:oracle:thin:@exa02-scan:1521/TEST1

              Error Message = Could not initialize class oracle.jdbc.OracleDriver

              USER          = testuser

              URL          = jdbc:oracle:thin:@exa02-scan/TEST1:1521/exa02-scan/TEST1

              Error Message = Could not initialize class oracle.jdbc.OracleDriver

             

            SQL> show java

            Java Detail

            -----------

            java.home= /usr/java/jdk1.8.0_144/jre

            java.vendor= Oracle Corporation

            java.vendor.url= http://java.oracle.com/

            java.version= 1.8.0_144

            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            os.arch= amd64

            os.name= Linux

            os.version= 4.1.12-61.57.2.el6uek.x86_64

            path.separator= :

            file.separator= /

            line.separator=

             

            user.dir= /home/oracle

            user.home= /home/oracle

            user.name= oracle

            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            Classpath=

            /u01/app/oracle/product/11.2.0.4/dbhome_1/jdbc/lib/ojdbc8.jar:/u01/app/oracle/product/11.2.0.4/dbhome_1/ojdbc8.jar:/u01/app/oracle/product/11.2.0.4/dbhome_1/jdbc/lib/ojdbc7.jar:/u

            01/app/oracle/product/11.2.0.4/dbhome_1/ojdbc7.jar:/u01/app/oracle/product/11.2.0.4/dbhome_1/jdbc/lib/ojdbc6.jar:/u01/app/oracle/product/11.2.0.4/dbhome_1/ojdbc6.jar:/opt/sqlcl/sq

            lcl/../rdbms/jlib/xdb6.jar:/opt/sqlcl/sqlcl/../jdbc/lib/ojdbc8.jar:/opt/sqlcl/sqlcl/../jlib/orai18n-utility.jar:/opt/sqlcl/sqlcl/../jlib/orai18n-mapping.jar:/opt/sqlcl/sqlcl/../jl

            ib/orai18n.jar:/opt/sqlcl/sqlcl/../modules/oracle.xdk/xmlparserv2.jar:/opt/sqlcl/sqlcl/lib/javax.json.jar:/opt/sqlcl/sqlcl/lib/xmlparserv2.jar:/opt/sqlcl/sqlcl/lib/commons-logging

            .jar:/opt/sqlcl/sqlcl/lib/orai18n.jar:/opt/sqlcl/sqlcl/lib/xmlparserv2-sans-jaxp-services.jar:/opt/sqlcl/sqlcl/lib/orai18n-collation.jar:/opt/sqlcl/sqlcl/lib/jackson-databind.jar:

            /opt/sqlcl/sqlcl/lib/xdb6.jar:/opt/sqlcl/sqlcl/lib/jackson-core.jar:/opt/sqlcl/sqlcl/lib/stringtemplate.jar:/opt/sqlcl/sqlcl/lib/oraclepki.jar:/opt/sqlcl/sqlcl/lib/jackson-annotat

            ions.jar:/opt/sqlcl/sqlcl/lib/osdt_core.jar:/opt/sqlcl/sqlcl/lib/httpmime.jar:/opt/sqlcl/sqlcl/lib/osdt_cert.jar:/opt/sqlcl/sqlcl/lib/httpcore.jar:/opt/sqlcl/sqlcl/lib/orajsoda.ja

            r:/opt/sqlcl/sqlcl/lib/httpclient.jar:/opt/sqlcl/sqlcl/lib/orai18n-utility.jar:/opt/sqlcl/sqlcl/lib/ojdbc8.jar:/opt/sqlcl/sqlcl/lib/dbtools-sqlcl.jar:/opt/sqlcl/sqlcl/lib/dbtools-

            net.jar:/opt/sqlcl/sqlcl/lib/dbtools-http.jar:/opt/sqlcl/sqlcl/lib/dbtools-common.jar:/opt/sqlcl/sqlcl/lib/orai18n-servlet.jar:/opt/sqlcl/sqlcl/lib/jsch.jar:/opt/sqlcl/sqlcl/lib/c

            ommons-codec.jar:/opt/sqlcl/sqlcl/lib/jdbcrest.jar:/opt/sqlcl/sqlcl/lib/orai18n-mapping.jar:/opt/sqlcl/sqlcl/lib/jline.jar:/opt/sqlcl/sqlcl/lib/antlr-runtime.jar:

            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            SQL>

            3.  Login with JAVA_HOME set and ORACLE_HOME unset successful:

             

            [oracle@exa021 ]$ sqlcl /nolog

             

            SQLcl: Release 18.1.1 Production on Tue Apr 17 17:03:44 2018

             

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

             

            SQL> connect testuser/password@exa02-scan/TEST1

            Connected.

            SQL> show connection

            CONNECTION:

            TESTUSER@jdbc:oracle:thin:@exa02-scan:1521/TEST1

            CONNECTION_IDENTIFIER:

            exa02-scan/TEST1

            CONNECTION_DB_VERSION:

            Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

            Data Mining and Real Application Testing options

            NOLOG:

            false

            PRELIMAUTH:

            false

            SQL> show java

            Java Detail

            -----------

            java.home= /usr/java/jdk1.8.0_144/jre

            java.vendor= Oracle Corporation

            java.vendor.url= http://java.oracle.com/

            java.version= 1.8.0_144

            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            os.arch= amd64

            os.name= Linux

            os.version= 4.1.12-61.57.2.el6uek.x86_64

            path.separator= :

            file.separator= /

            line.separator=

             

            user.dir= /home/oracle

            user.home= /home/oracle

            user.name= oracle

            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            Classpath=

            /opt/sqlcl/sqlcl/lib/dbtools-sqlcl.jar:/opt/sqlcl/sqlcl/../rdbms/jlib/xdb6.jar:/opt/sqlcl/sqlcl/../jdbc/lib/ojdbc8.jar:/opt/sqlcl/sqlcl/../jlib/orai18n-utility.jar:/opt/sqlcl/sqlc

            l/../jlib/orai18n-mapping.jar:/opt/sqlcl/sqlcl/../jlib/orai18n.jar:/opt/sqlcl/sqlcl/../modules/oracle.xdk/xmlparserv2.jar:/opt/sqlcl/sqlcl/lib/javax.json.jar:/opt/sqlcl/sqlcl/lib/

            xmlparserv2.jar:/opt/sqlcl/sqlcl/lib/commons-logging.jar:/opt/sqlcl/sqlcl/lib/orai18n.jar:/opt/sqlcl/sqlcl/lib/xmlparserv2-sans-jaxp-services.jar:/opt/sqlcl/sqlcl/lib/orai18n-coll

            ation.jar:/opt/sqlcl/sqlcl/lib/jackson-databind.jar:/opt/sqlcl/sqlcl/lib/xdb6.jar:/opt/sqlcl/sqlcl/lib/jackson-core.jar:/opt/sqlcl/sqlcl/lib/stringtemplate.jar:/opt/sqlcl/sqlcl/li

            b/oraclepki.jar:/opt/sqlcl/sqlcl/lib/jackson-annotations.jar:/opt/sqlcl/sqlcl/lib/osdt_core.jar:/opt/sqlcl/sqlcl/lib/httpmime.jar:/opt/sqlcl/sqlcl/lib/osdt_cert.jar:/opt/sqlcl/sql

            cl/lib/httpcore.jar:/opt/sqlcl/sqlcl/lib/orajsoda.jar:/opt/sqlcl/sqlcl/lib/httpclient.jar:/opt/sqlcl/sqlcl/lib/orai18n-utility.jar:/opt/sqlcl/sqlcl/lib/ojdbc8.jar:/opt/sqlcl/sqlcl

            /lib/dbtools-sqlcl.jar:/opt/sqlcl/sqlcl/lib/dbtools-net.jar:/opt/sqlcl/sqlcl/lib/dbtools-http.jar:/opt/sqlcl/sqlcl/lib/dbtools-common.jar:/opt/sqlcl/sqlcl/lib/orai18n-servlet.jar:

            /opt/sqlcl/sqlcl/lib/jsch.jar:/opt/sqlcl/sqlcl/lib/commons-codec.jar:/opt/sqlcl/sqlcl/lib/jdbcrest.jar:/opt/sqlcl/sqlcl/lib/orai18n-mapping.jar:/opt/sqlcl/sqlcl/lib/jline.jar:/opt

            /sqlcl/sqlcl/lib/antlr-runtime.jar:

            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             

             

            Doug

            • 3. Re: sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB
              dougk
              dougk Apr 20, 2018 10:58 PM (in response to dougk)

              Barry,

               

              From your last posting, I assume that LD_LIBRARY_PATH is somehow used to match lib.so files with the driver files for a bequeath connection.  Can you offer any guidance?

               

              I tried to strace launching of sqlcl to find the lib files being called, but didn't see anything useful in the output. 

               

              Doug

              • 4. Re: sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB
                skorupeck
                skorupeck Mar 11, 2020 1:27 PM (in response to dougk)

                Hello,

                 

                it doesn't work in your case, because when ORACLE_HOME is set, then JAVA_HOME is simply ignored or rather it is always set to java located in ORACLE_HOME. It happens in function checkADE of sqlcl:

                 

                #

                # if we are in ADE environement, check for JAVA_HOME

                #

                function checkADE {

                        #

                        # Resolve java path for development builds

                        #

                       if  [  "m$ORACLE_HOME" != "m" ]; then

                         if  [ -d "$ORACLE_HOME/jdk/jre" ]; then

                             JAVA_HOME="$ORACLE_HOME/jdk/jre"

                             PATH="$JAVA_HOME/bin:$PATH"

                         fi

                         export SQLPLUS_CLASSIC=true

                       fi

                }

                 

                To make it work, you need to comment lines doing that:

                 

                # if we are in ADE environement, check for JAVA_HOME

                #

                function checkADE {

                        #

                        # Resolve java path for development builds

                        #

                       if  [  "m$ORACLE_HOME" != "m" ]; then

                #        if  [ -d "$ORACLE_HOME/jdk/jre" ]; then

                #            JAVA_HOME="$ORACLE_HOME/jdk/jre"

                #            PATH="$JAVA_HOME/bin:$PATH"

                #        fi

                         export SQLPLUS_CLASSIC=true

                       fi

                }

                 

                After doing so, you should be able to connect as sysdba event to 11.2.0.4 database.

                 

                Best regards,

                Szymon

                • 5. Re: sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB
                  Mike301
                  Mike301 May 8, 2020 8:43 PM (in response to skorupeck)

                  Hello,

                  Does above solution works? Somehow it doesn't for me for v. 19.4

                   

                  I have SQLcl (19.4) folder running form 11g client directory, I have done below still no luck.

                  1. Unset ORACLE_HOME

                  2. export JAVA_HOME=/user/jdk1.8

                  3. comment out entries from checkADE function.

                   

                  Still receives error

                  "java.lang.UnsupportedClassVersionError Bad Version number in .class file"

                   

                  If I move SQLcl folder anywhere else and works after below

                  1. Unset ORACLE_HOME

                  2. export JAVA_HOME=/user/jdk1.8

                   

                  Any ideas on how to resolve it?

                  • 6. Re: sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB
                    cormaco
                    cormaco May 9, 2020 7:57 PM (in response to skorupeck)

                    I think commenting this line would be enough, if you already have JAVA_HOME defined:

                    JAVA_HOME="$ORACLE_HOME/jdk/jre"

                    • 7. Re: sqlcl 18.1.1 connect / as sysdba with 11.2.0.4 DB
                      Mike301
                      Mike301 May 11, 2020 9:51 PM (in response to cormaco)

                      Unfortunately still doesn't work, same error after commenting below

                       

                      >> JAVA_HOME="$ORACLE_HOME/jdk/jre"