Daily Archives: May 1, 2021
Accessing Oracle Free Tier Instance using SQLcl
Here in these steps I have used the Oracle Free tier Compute Instance and Oracle Free tier Database. I have connected my local machine by ssh to Compute Instance VM running Oracle server 7.9. To move the Instance Wallet file I have used Winscp. After downloading the ssh key file I have used Puttygen to create .ppk file and mapped the .ppk file to authenticate in Winscp.
As SQLcl is already Installed and will directly go to the steps
- Download the Instance Wallet
- Open sqlcl
- Issue command set cloudconfig <downloaded_zip_file>
- Issue show tns
- Connect to available tns and move on.
Step – 1: Download the Instance Wallet
Once the Instance is created then click on DB connection to download the Wallet file.
Step – 2: Open sqlcl
[opc@oracle-linux ~]$ /opt/oracle/sqlcl/bin/sql /nolog
SQLcl: Release 21.1 Production on Sat May 01 13:08:45 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
SQL>
Step – 3: Issue command set cloudconfig <downloaded_zip_file>
SQL> set cloudconfig Wallet_testdb.zip
Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config8749035398540414270
Step – 4: Issue show tns
SQL> select * from dual;
SP2-0640: Not connected
SQL> show tns
TNS Lookup locations
--------------------
1. Third Party Path
/tmp/oracle_cloud_config8749035398540414270
2. USER Home dir
/home/opc
Location used:
-------------
/tmp/oracle_cloud_config8749035398540414270
Available TNS Entries
---------------------
testdb_high
testdb_low
testdb_medium
testdb_tp
testdb_tpurgent
Step – 5: Connect to available tns and move on.
SQL> connect admin@testdb_medium
Password? (**********?) ***************
Connected.
SQL> select * from dual;
DUMMY
________
X
SQL>
SQLcl Issue in Oracle Linux
I have Installed SQLcl In Oracle Server 7.9 using yum which in-turn java 8 was installed as a dependency. I had already java 16 version in the server. Now while executing the SQLcl I was getting the below Error.
$ sudo yum install sqlcl
While trying to launch SQLCL in Oracle server I was getting the below Error.
[opc@oracle-linux bin]$ /opt/oracle/sqlcl/bin/sql /nolog
Error occurred during initialization of VM
java.nio.charset.IllegalCharsetNameException: utf-8nLC_ALL=en_US
at java.nio.charset.Charset.checkName(java.base/Charset.java:305)
at java.nio.charset.Charset.lookup2(java.base/Charset.java:479)
at java.nio.charset.Charset.lookup(java.base/Charset.java:459)
at java.nio.charset.Charset.defaultCharset(java.base/Charset.java:605)
at java.io.OutputStreamWriter.<init>(java.base/OutputStreamWriter.java:110)
at java.io.PrintStream.<init>(java.base/PrintStream.java:110)
at java.io.PrintStream.<init>(java.base/PrintStream.java:152)
at java.lang.System.newPrintStream(java.base/System.java:1918)
at java.lang.System.initPhase1(java.base/System.java:2012)
I had two versions of Java Installed in the machine.
[opc@oracle-linux bin]$ alternatives --config java
There are 2 programs which provide 'java'.
Selection Command
-----------------------------------------------
1 /home/opc/soft/jdk/bin/java
*+ 2 /usr/java/jdk1.8.0_291-amd64/bin/java
Enter to keep the current selection[+], or type selection number:
[opc@oracle-linux bin]$ echo $JAVA_HOME
/home/opc/soft/jdk
[opc@oracle-linux bin]$ java --version
openjdk 16 2021-03-16
OpenJDK Runtime Environment (build 16+36-2231)
OpenJDK 64-Bit Server VM (build 16+36-2231, mixed mode, sharing)
While checking the java is still referrring to java 17 in the above path. Now we need to change path by the below command
[opc@oracle-linux jdk1.8.0_291-amd64]$ export JAVA_HOME=/usr/java/jdk1.8.0_291-amd64
[opc@oracle-linux jdk1.8.0_291-amd64]$ export PATH=$JAVA_HOME/bin:$PATH
Here in the above code, JAVA_HOME path was added to first line to new path and in the next command we are appending to PATH
[opc@oracle-linux jdk1.8.0_291-amd64]$ echo $PATH
/usr/java/jdk1.8.0_291-amd64/bin:/usr/java/jdk1.8.0_291-amd64/bin:/home/opc/soft/bat:/home/opc/soft/apache-maven-3.6.3/bin:/home/opc/soft:/home/opc/soft/jdk/bin/:/home/opc/soft:/home/opc/soft/apache-maven-3.6.3/bin:/home/opc/soft/bin:/home/opc/soft/bat:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/opc/.local/bin:/home/opc/bin[opc@oracle-linux
[opc@oracle-linux jdk1.8.0_291-amd64]$ java -version
java version "1.8.0_291"
Java(TM) SE Runtime Environment (build 1.8.0_291-b32)
Java HotSpot(TM) 64-Bit Server VM (build 25.291-b32, mixed mode)
Now while running sqlcl again was getting the same Error so I was stuck for some time. I have then executed man and it gave the Issue
[opc@oracle-linux ~]$ man sql
man: can't set the locale; make sure $LC_* and $LANG are correct
No manual entry for sql
[opc@oracle-linux ~]$ locale
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Cannot set LC_ALL to default locale: No such file or directory
LANG=en_US.utf-8nLC_ALL=en_US.utf-8n
LC_CTYPE="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_NUMERIC="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_TIME="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_COLLATE="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_MONETARY="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_MESSAGES="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_PAPER="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_NAME="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_ADDRESS="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_TELEPHONE="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_MEASUREMENT="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_IDENTIFICATION="en_US.utf-8nLC_ALL=en_US.utf-8n"
LC_ALL=
Now when I checked with root user I did not face any locale Issue.
[opc@oracle-linux ~]$ sudo su - Last login: Sat May 1 12:03:55 GMT 2021 on pts/3 [root@oracle-linux ~]# locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL=
Now when running with root user sqlcl works for me as with opc user there were some problems with locale
[root@oracle-linux ~]# java -version java version "1.8.0_291" Java(TM) SE Runtime Environment (build 1.8.0_291-b32) Java HotSpot(TM) 64-Bit Server VM (build 25.291-b32, mixed mode) [root@oracle-linux ~]# /opt/oracle/sqlcl/bin/sql /nolog SQLcl: Release 21.1 Production on Sat May 01 12:06:59 2021 Copyright (c) 1982, 2021, Oracle. All rights reserved. SQL> exit
After searching then executed the below command. Now locale Issue is solved.
$ export LC_ALL="en_US.UTF-8"
[opc@oracle-linux ~]$ export LC_ALL="en_US.UTF-8"
[opc@oracle-linux ~]$ locale
LANG=en_US.utf-8nLC_ALL=en_US.utf-8n
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8
[opc@oracle-linux ~]$
Now again I was getting the Error so probably i will solve this Error and let you know
[opc@oracle-linux ~]$ /opt/oracle/sqlcl/bin/sql /nolog
Error occurred during initialization of VM
java.nio.charset.IllegalCharsetNameException: utf-8nLC_ALL=en_US
at java.nio.charset.Charset.checkName(java.base/Charset.java:305)
at java.nio.charset.Charset.lookup2(java.base/Charset.java:479)
at java.nio.charset.Charset.lookup(java.base/Charset.java:459)
at java.nio.charset.Charset.defaultCharset(java.base/Charset.java:605)
at java.io.OutputStreamWriter.<init>(java.base/OutputStreamWriter.java:110)
at java.io.PrintStream.<init>(java.base/PrintStream.java:110)
at java.io.PrintStream.<init>(java.base/PrintStream.java:152)
at java.lang.System.newPrintStream(java.base/System.java:1918)
at java.lang.System.initPhase1(java.base/System.java:2012)
[opc@oracle-linux ~]$ man sql
No manual entry for sql
Now Issuing the below command was working
[opc@oracle-linux ~]$ LANG=en_US.UTF-8
[opc@oracle-linux ~]$ /opt/oracle/sqlcl/bin/sql /nolog
SQLcl: Release 21.1 Production on Sat May 01 12:28:09 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
SQL>
In a summary changing the java from openjdk to oracle-jdk dependent version and below two commands resolved this Error.
LANG=en_US.UTF-8
export LC_ALL=”en_US.UTF-8″
Thank you for reading. If you have any suggestions please let me know.
References
pacesettergraam 