How to connect via ssh to Database from the Oracle SQL Developer Extension for VSCode

This post is inspired by a LinkedIn posting from Kris Rice, so kudos to him. Based on that, this post will show how to connect from your Windows host to a VirtualBox VM running Oracle Linux via ssh from the Oracle SQL Developer Extension for VSCode.

First check if the sshd in your VM is running

sudo systemctl status sshd

If you don't already have an ssh key pair on your Windows host, create one via ssh-keygen in the .ssh folder under your home directory

In your Linux VM add the public key from your Windows host by

sudo vi ~oracle/.ssh/authorized_keys

Paste your public key into that file and save it.

Test your connection from the Windows command line.

As you will not be able to choose a username when connecting from VSCode, create an ssh config file on Windows in your .shh folder like this:

Host 192.168.56.11
  User "oracle"
  IdentityFile c:\Users\Arne\.ssh\abruenin-lap

If you don't already have, install the Remote - SSH extension in VSCode

With that extension, you will get the Remote Explorer. It scans your .ssh config file and offers you the ssh hosts that it found.

Click the folder icon next to your connection to 'Connect in New Window...'

In the new server side window, VSCode will recommend you to install the old Oracle Developer Tools for VS Code, though these are marked as deprecated (at least my VSCode did that). Instead install the Oracle SQL Developer Extension for VSCode.

Once installed, you can use SQLDeveloper in the remote ssh window just as you would use it with a direct connection.

Oracle on Windows: out-of-place Patching with Virtual Account

This guide shows a way, how a Gold Image can be created from an Oracle Home on Windows and how that can be used to patch other Oracle Homes. This worked for me, but is not meant to be the only, best or officially recommended Oracle way.

Prepare the image source home

Say we have an unpatched 19.3 installed in c:\oracle\product\19.0.0\dbhome_1 with a database ORCL (non-PDB), installed with the option to use a Virtual Account, which is the default from the Oracle installer.

So the owner for the Oracle software home and datafiles has been created as ORA_OraDB19Home1_SVCACCTS by the installer.

Next we set up a new Oracle home and patch it, to make it the source of our gold image in c:\oracle\product\19.0.0\dbhome_2.

There is no standard oraenv.sh under Windows. As some tasks have to be done from the old and some from the new home, it helps to create two different oraenv scripts, eg. old-oraenv.cmd and new-oraenv.cmd , like in my environment:

old-oraenv.cmd

set ORACLE_BASE=C:\Oracle

set ORACLE_HOME=%ORACLE_BASE%\Product\19.0.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch\;%PATH%
set PATH=%ORACLE_HOME%\jdk\bin\;%PATH%

new-oraenv.cmd

set ORACLE_BASE=C:\Oracle
set OLDORA_HOME=%ORACLE_BASE%\Product\19.0.0\dbhome_1
set ORACLE_HOME=%ORACLE_BASE%\Product\19.21\dbhome_3
set PATH=%ORACLE_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch\;%PATH%
set PATH=%ORACLE_HOME%\jdk\bin\;%PATH%

Run your oraenv.cmd, unpack the software (you can use tar xfz under Windows for unzip) and run setup.bat, see the Database Installation Guide 19c for Microsoft Windows. 

C:\app>oraenv.cmd

C:\app>mkdir %ORACLE_HOME%
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_2>tar xfz z:\Install\Windows\WINDOWS.X64_193000_db_home.zip
C:\app\oracle\product\19.0.0\dbhome_2>setup.bat 

We only need to setup the software only and this is a single instance installation. Under Linux, we would just apply the RU and one-offs from the setup, but the options '-applyRU 'and '-applyOneOffs' are not available under Windows (see the comments on https://mikedietrichde.com/2022/05/17/simple-database-installation-with-applyru-and-applyoneoffs/, search for Windows).

Update opatch by following Doc ID 274526.1 from Oracle Support, in this case using opatch 12.2.0.1.41.

For the Gold Image, we will patch the 19.3 dbhome_2 up to 19.21 plus two one-off patches for the JVM. So download and unpack the patches to a given directory, in this example c:\app\oracle\patches. The patch numbers are 35638318, 35648110 and 35681552, these have to be applied manually by running opatch apply in every patch directory, starting with 35681552 which is the Bundle Patch 19.21.

Now the dbhome_2 is on 19.21 with two additional one-off patches and will serve as source for our Gold Image.

Create the Gold Image

Next, from the patched Oracle Home, we create a Gold Image following the documentation.

setup.exe -createGoldImage -destinationLocation c:\oracle\images 

Wait for the installer to finish, then you should have the newly created image in the directory stated by -destinationLocation. The dbhome_2 can be deleted with Oracle's deinstall program.

Out-of-place patching using the Gold Image

We now have an unpatched Oracle 19.3 home in dbhome_1 with the database ORCL and a gold image with version version 19.21 and two one-off patches.

For out-of-place patching, we need to create a new Oracle Home, where we unpack the Gold Image. 

C:\app>new-oraenv.cmd
C:\app>mkdir %ORACLE_HOME%
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.21\dbhome_3>tar xfz c:\app\oracle\images\db_home_2024-02-21_05-21-14PM.zip
C:\app\oracle\product\19.21\dbhome_3>setup

Run setup, choose software only and accept the default virtual account. If setup.exe crashes with a Java EXCEPTION_ACCESS_VIOLATION, follow MOS note 2772729.1.

rem --- Copy spfile and pwdfile to new ORACLE_HOME
copy %OLDORA_HOME%\database\SPFILEORCL.ORA %ORACLE_HOME%\database
copy %OLDORA_HOME%\database\PWDorcl.ora    %ORACLE_HOME%\database

Shutdown the database first, then copy the spfile and pwdfile to the new dbhome_3

The setup has created a new user ORA_OraDB19Home2_SVCACCTS and granted full control for the oradata directory, so the Oracle software from the new dbhome_2 can take over the files from the old dbhome_1.

On Unix/Linux systems, we would already be done, but on Windows we have services which need to be migrated to the new Oracle Home.

In the registry, we see that some services are linked to a given Oracle Home, so these need to be recreated.

set OLDORA_HOME=%ORACLE_BASE%\product\19.0.0\dbhome_1

rem --- use oradim to delete Oracle services ---
oradim -DELETE -SID orcl

For easier scripting, we point OLDORA_HOME to the Oracle home running the old version.

First delete the instance services with oradim, which will remove OracleServiceORCL, OracleVssWriterORCL and OracleJobSchedulerORCL. 

The Oracle Home specific services will remain. These will be deleted, when deleting the dbhome_1. 

rem --- stop remaining Oracle services ---

sc stop OracleOraDB19Home1MTSRecoveryService
sc config OracleOraDB19Home1MTSRecoveryService start= disabled
sc stop OracleOraDB19Home1TNSListener

sc config OracleOraDB19Home1TNSListener start=disabled

But as one advantage of out-of-place patching is, that you can go back to the old version, you probably want to keep them for some time, so we will just disable them.

The remaining services should now be stopped and disabled.

rem --- create new services for new Oracle home

oradim -NEW -SID orcl -STARTMODE auto -SRVCSTART system -SPFILE -PFILE %ORACLE_HOME%\database\SPFILEORCL.ORA

Then use oradim again to create the instance specific services

It recreates the OracleServiceorcl, OracleVssWriterorcl and OracleJobSchedulerorcl for the new Oracle home.

Double checking from the Registry, that we are now running on the patched dbhome_3

Also from SQLPlus, everything looks fine.

rem --- recreate remaining services
%ORACLE_HOME%\bin\oramtsctl -new
%ORACLE_HOME%\bin\lsnrctl start
sc config "OracleOraDB19Home3TNSListener" start= auto

The formerly disabled services from the old home need to be recreated for the new home. Starting the Listener automatically creates a new Service for the new home. That should be configured to start automatically.

Now we have migrated all services to the new Oracle home.

rem --- start the database and run datapatch
rem --- not needed with service auto start --- @echo startup | sqlplus / as sysdba
rem --- @echo alter pluggable database all open | sqlplus / as sysdba
rem --- @echo show pdbs | sqlplus / as sysdba

@echo @?/rdbms/admin/utlrp.sql | sqlplus / as sysdba
%ORACLE_HOME%\OPatch\datapatch

Before running the datapatch, make sure that all pds are open if already running on Mutlitenant architecture. When the open state has been saved by using ALTER PLUGGABLE DATABASE ... SAVE STATE, that step can be skipped. Here we open all PDB's before running the datapatch 

The database software and data dictionary should now be on the new version.

rem --- apply optimizer fixes for every PDB
@echo @?/rdbms/admin/dbmsoptim.sql | sqlplus / as sysdba
@echo @?/rdbms/admin/prvtoptim.plb | sqlplus / as sysdba
@echo execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES'); | sqlplus / as sysdba

Last step is enabling the optimizer fixes for the new version. That's it, we are running on 19.21 after out-of-place patching.

Finally, after some time, you might want to delete the old 19.3 Oracle home dbhome_1. Run %ORACLE_HOME%\deinstall\deinstall.bat to clean up the old installation.

After the deinstallation of the old home, only the patched 19.21 services from dbhome_3 will remain.

The complete scripting can be found on GitLab.

Oracle on Windows: create 19.21 Gold Image and use it for out-of-place patching

This guide shows a way, how a Gold Image can be created from an Oracle Home on Windows and how that can be used to patch other Oracle Homes. This worked for me, but is not meant to be the only, best or officially recommended Oracle way.

Prepare the image source home

Start with a fresh installed Oracle Home, in this case under D:\app\oracle\product\19.0.0\dbhome_1\.

There is no standard oraenv.sh under Windows, a minimal oraenv.cmd could look like that:

set ORACLE_BASE=C:\app\oracle
set ORACLE_HOME=%ORACLE_BASE%\product\19.0.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch\;%PATH%   

Run your oraenv.cmd, unpack the software (you can use tar xfz under Windows for unzip) and run setup.bat, see the Database Installation Guide 19c for Microsoft Windows. 

C:\app>mkdir c:\app\oracle\product\19.0.0\dbhome_1
C:\app>oraenv.cmd
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_2>tar xfz z:\Install\Windows\WINDOWS.X64_193000_db_home.zip
C:\app\oracle\product\19.0.0\dbhome_2>setup.bat 

In this example, we use an existing user oracle instead of a virtual account, which would be the default from the oracle installer. This makes directory handling easier for out-of-place-patching. Also we only need to setup the software only and this is a single instance installation. Under Linux, we would just apply the RU and one-offs from the setup, but the options '-applyRU 'and '-applyOneOffs' are not available under Windows (see the comments on https://mikedietrichde.com/2022/05/17/simple-database-installation-with-applyru-and-applyoneoffs/, search for Windows).

Update opatch by following Doc ID 274526.1 from Oracle Support, in this case using opatch 12.2.0.1.41.

For the Gold Image, we will patch the 19.03 dbhome_1 up to 19.21 plus two one-off patches for the JVM. So download and unpack the patches to a given directory, in this example c:\app\oracle\patches. The patch numbers are 35638318, 35648110 and 35681552, these have to be applied manually by running opatch apply in every patch directory, starting with 35681552 which is the Bundle Patch 19.21.

Now the dbhome_1 is on 19.21 with two additional one-off patches and will serve as source for our Gold Image.

Create the Gold Image

Next, from the patched Oracle Home, we create a Gold Image following the documentation.

setup.exe -createGoldImage -destinationLocation c:\app\oracle\images 

Wait for the installer to finish, then you should have the newly created image in the directory stated by -destinationLocation.

Create new Oracle Home and database

Next we create a new Oracle Home and a Database ORCL/ORCLPDB under dbhome_2. Create the new directory, change the ORACLE_HOME variable in oraenv.cmd to point to dbhome_2 and run it. Then unpack the 19.03 software to that directory the same way as dbhome_1 before.

C:\app>mkdir c:\app\oracle\product\19.0.0\dbhome_2
C:\app>oraenv.cmd
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_2>tar xfz z:\Install\Windows\WINDOWS.X64_193000_db_home.zip
C:\app\oracle\product\19.0.0\dbhome_2>setup.bat

Run setup.exe in dbhome_2 and this time also create a database ORCL with a PDB ORCLPDB.

So now we have a patched dbhome_1 as source for the Gold Image and an unpatched dbhome_2 running the database ORCL with the PDB ORCLPDB.

Out-of-place patching using the Gold Image

For out-of-place patching, we need to create a new Oracle Home, where we unpack the Gold Image. Change the ORACLE_HOME in oraenv.cmd to dbhome_3 and run it.

C:\app>oraenv.cmd
C:\app>mkdir c:\app\oracle\product\19.0.0\dbhome_3
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_3>tar xfz c:\app\oracle\images\db_home_2024-02-21_05-21-14PM.zip
C:\app\oracle\product\19.0.0\dbhome_3>setup

Run setup, choose software only and the oracle user instead of a virtual account. If setup.exe crashes with a Java EXCEPTION_ACCESS_VIOLATION, follow MOS note 2772729.1.

On Unix/Linux systems, we would already be done, but on Windows we have services which need to be migrated to the new Oracle Home.

In the registry, we see that the services are linked to a given Oracle Home, so these need to be migrated.

set OLDORA_HOME=%ORACLE_BASE%\product\19.0.0\dbhome_2

rem --- stop all Oracle services first ---
net stop OracleOraDB19Home2MTSRecoveryService
net stop OracleOraDB19Home2TNSListener
net stop OracleServiceORCL
net stop OracleVssWriterORCL

Stop all services from dbhome_2. For easier scripting, we point OLDORA_HOME to the Oracle home running the old version.

rem --- Copy spfile and pwdfile to new ORACLE_HOME
copy %OLDORA_HOME%\database\SPFILEORCL.ORA %ORACLE_HOME%\database
copy %OLDORA_HOME%\database\PWDorcl.ora    %ORACLE_HOME%\database

Copy the spfile and pwdfile to dbhome_3

rem -- Delete old services
%OLDORA_HOME%\bin\oramtsctl -delete
sc delete OracleOraDB19Home2TNSListener

Delete the MTS and Listener services from the old home.

rem --- Point services to new ORACLE_HOME
sc config OracleServiceORCL         binPath="c:\app\oracle\product\19.0.0\dbhome_3\bin\ORACLE.EXE ORCL"
sc config OracleJobSchedulerORCL    binPath="c:\app\oracle\product\19.0.0\dbhome_3\bin\extjob.exe ORCL"
sc config OracleVssWriterORCL       binPath="c:\app\oracle\product\19.0.0\dbhome_3\bin\OraVSSW.exe ORCL"

Migrate the OracleService, JobScheduler and VssWriter from the old to the new home.

rem --- create new services
%ORACLE_HOME%\bin\oramtsctl -new
%ORACLE_HOME%\bin\lsnrctl start
sc config "OracleOraDB19Home3TNSListener" start= auto

The formerly deleted services from the old home need to be recreated for the new home. Starting the Listener automatically creates a new Service for the new home. That one should be configured to start automatically.

rem --- start new services
oradim -EDIT -SID ORCL -STARTMODE auto -SRVCSTART system
net start OracleServiceORCL

Set the Oracle service to be started automatically and to start the database on service start. Then start the OracleService.

rem --- start the database and run datapatch
rem --- not needed with service auto start --- @echo startup | sqlplus / as sysdba
@echo alter pluggable database all open | sqlplus / as sysdba
@echo show pdbs | sqlplus / as sysdba
%ORACLE_HOME%\OPatch\datapatch

Before running the datapatch, make sure that all pds are open. When the open state has been saved by using ALTER PLUGGABLE DATABASE ... SAVE STATE, that step can be skipped. Here we open all PDB's before running the datapatch 

The database software and data dictionary should now be on the new version.

rem --- apply optimizer fixes for every PDB
@echo @?/rdbms/admin/dbmsoptim.sql | sqlplus / as sysdba
@echo @?/rdbms/admin/prvtoptim.plb | sqlplus / as sysdba
@echo execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES'); | sqlplus / as sysdba

Last step is enabling the optimizer fixes for the new version. The complete scripting can be found on GitLab.

Use sqlcl with Oracle Linux 9 in Windows Terminal on Windows Subsystem for Linux

This post shows a quick and easy way to use Linux commands on Windows in the Windows Terminal by using Windows Subsystem for Linux (WSL). Oracle Developers can - for example - use sqlcl from that environment.

First, install Windows Terminal from the Microsoft Store, or with winget if you have already installed it.

Windows Subsystem for Linux is a great way to have Linux functionality on Windows. For the first time usage, follow the Microsoft Documentation to install it. Oracle Linux 9 is available in the Microsoft Store as fully compatible, free RHEL/CentOS alternatives. That is a nice option, if you have to develop for RHEL, but want to save the license costs. 

And SQLcl can easily installed, as long as you have Java on the system.

Download Oracle Linux 9 from the Microsoft Store or use winget:

winget search 'oracle linux'
winget install 9MXQ65HLMC27

It installed within minutes and can immediately be used. A nice thing about Windows Terminal with WSL is, that WSL automatically registers with Windows Terminal as an option.

If, for whatever reason, the Oracle Linux 9 option does not show up, try the following. Open Windows Terminal settings, choose Add a new profile, select OracleLinux_9 from the select list and click on Duplicate.

Open an Oracle Linux 9 tab and first install all updates via sudo dnf update. 

...

For SQLcl install Java. As this is a minimal installation, unzip and ncurses are missing so also install them.

sudo dnf in java-17-openjdk.x86_64

sudo dnf in ncurses

sudo dnf in unzip.x86_64

Download latest SQLcl zip archive from https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip and unzip it

wget https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
unzip sqlcl-latest.zip

Add the path to the SQLcl binary to your .bashrc and source it. A cool feature of WLS is, that all your Windows drives are automatically mounted under /mnt/c, /mnt/d etc. So if you already have a network/admin directory under Windows, you can point your TNS_ADMIN to that location. Also set SQLPATH to enable some of the cool features of SQLcl. This is what my .bashrc ends with.

export PATH=/home/oracle/sqlcl/bin:$PATH
export TNS_ADMIN=/mnt/d/Oracle/network/admin

export SQLPATH=~/sql

Create a directory ~/sql and inside a file named login.sql with the above content.

set statusbar on
set highlighting on

 That't it. Open an Oracle Linux 9 tab in your Windows Terminal and connect to all your databases via sqlcl

Or just take it to use all usual Linux commands in Windows.

Use SQLcl in Oracle Linux 8 on Windows Subsystem for Linux

There is an update to this post on my Blog.

---8<-----------------------------------------------------------------------------------

Windows Subsystem for Linux is a great way to have Linux functionality on Windows. For the first time usage, follow the Microsoft Documentation to install it. Oracle Linux 7.9 and 8.5 are available in the Microsoft Store as fully compatible, free RHEL/CentOS alternatives. And SQLcl can easily installed, as long as you have Java on the system.

It installed within minutes and can immediately be updated via sudo dnf update. But when I tried so install Java, I got a 

RPM: error: db5 error(-30969) from dbenv->open: BDB0091 DB_VERSION_MISMATCH: Database environment version mismatch

Looks like the RPM repository is defect. I found the solution on GitHub:

sudo -s
cd /var/lib/rpm
rpm --rebuilddb
dd if=/dev/zero of=__db.001 bs=1M count=1
dd if=/dev/zero of=__db.002 bs=1M count=1
dd if=/dev/zero of=__db.003 bs=1M count=1
rpm -qa rpm

Follow the postings on GitHub. There seem to be easier solutions, but I did not double check them, because the first one worked for me.

sudo dnf in java-11-openjdk.x86_64

Now JDK installs without problems, for SQLcl 22.1 at least JDK 11.x is mandatory.

Download latest SQLcl zip archive from oracle.com/sqlcl and copy the contents to any directory, like ~/bin/sqlcl

Add the path to the SQLcl binary to your .bashrc and source it. A cool feature of WLS is, that all your Windows drives are automatically mounted under /mnt/c, /mnt/d etc. So if you already have a network/admin directory under Windows, you can point your TNS_ADMIN to that location. 

export PATH=/home/arne/bin/sqlcl/bin:$PATH
export TNS_ADMIN=/mnt/d/Oracle/network/admin

This is what my .bashrc ends with.

That gives me access from Linux to all databases I have configured under Windows.