Showing posts with label virtualbox. Show all posts
Showing posts with label virtualbox. Show all posts

Thursday, 20 March 2025

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.






Friday, 7 June 2024

Running the Oracle 23ai Autonomous Database Free Container Image

 With the Oracle Database 23ai comes a new deployment option, the free Autonomous Database Free Container Image. It gives developers the option to develop with an Autonomous Database even when not connected to the internet, eg. when travelling. This post shows how to set it up on Oracle Linux 9.4, in my case it is running in a VirtualBox VM on my Windows desktop PC. For details, see the official documentation, this is only meant as a quick runthrough.

Following the documentation, start with

podman machine init 
podman machine set --cpus 4 --memory 8192 
podman machine start 

Maybe it is just my system, but when running podman machine init I get the error qemu-system-x86_64: executable file not found in $PATH. A post on forums.oracle.com described the following solution, which worked on my system:

sudo ln -s /usr/libexec/qemu-kvm /usr/bin/qemu-system-x86_64

The podman command should run now, so follow the steps from the documentation.

podman pull container-registry.oracle.com/database/adb-free:latest-23ai
podman images container-registry.oracle.com/database/adb-free:latest-23ai

That image should show up on your system:
It is important to choose a password which is compliant with the images policy "The ADMIN_PASSWORD must be between 12 and 30 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric".
Choosing a too simple password results in a runtime error, check with podman logs adb-free.
Then start a container with that image, in my case an ATP instance. 

podman run -d \
-p 1521:1522 \
-p 1522:1522 \
-p 8443:8443 \
-p 27017:27017 \
-e WORKLOAD_TYPE='ATP' \
-e WALLET_PASSWORD=*** \
-e ADMIN_PASSWORD=*** \
--cap-add SYS_ADMIN \
--device /dev/fuse \
--name adb-free \
container-registry.oracle.com/database/adb-free:latest-23ai

If nothing went wrong, you should see the container running.
So let's try some stuff. First open Database Actions by browsing https://localhost:8443/ords/sql-developer (and accept the self signed certificate), login with admin and your super secure password from above.
Also APEX works ootb, just login again with your password from above.
To access the ADB from outside with your favorite tooling, copy the wallet from to image to a folder. For SQLDeveloper, we need it as a zip.

podman cp adb-free:/u01/app/oracle/wallets/tls_wallet ~/tls_wallet
zip adb-free-wallet.zip tls_wallet/*

Use that wallet zip file in your tool of choice, in my case VSCodium with the Oracle SQL Developer Extension for VSCode.
Welcome to the Oracle 23ai Autonomous Database Free Container Image.
As long as your podman is ready to run and you respect the password compliancy, this is setup within minutes.

Tuesday, 2 May 2023

Installing ORDS 23.1 with Apex 23.1 on Oracle Database 23c Free Developer Release

This post is part of my Oracle Developer VM  Project series. It is not meant to replace the official documentation in any way and what I describe is no recommended configuration. It is only meant to be a guide, if you follow my DB Developer VM Project and want to add these services to your VM.

Part 1: Install Apex

Download the Apex install bundle and unzip it (eg. to /u01), which creates an apex folder. In the unpacked folder run the installer

sqlplus sys/your_password@freepdb1 as sysdba @apexins sysaux sysaux temp /i/

Leave the session open and create the Apex Admin user by running @apxchpwd.sql.
Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it:

1
2
3
4
5
6
7
BEGIN    
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
      host => '*',
      ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                         principal_name => 'APEX_230100',
                         principal_type => xs_acl.ptype_db));
END;

Part 2: Install ORDS

First, we need to make some settings. Add the following to .bashrc and source it.

export _JAVA_OPTIONS="-Xms3000M -Xmx3000M"
export ORDS_CONFIG=/u01/ords-config
export ORDS_HOME=/u01/ords-23-1
export PATH=$ORDS_HOME/bin:$PATH

Create both directories:

mkdir /u01/ords-23-1
mkdir /u01/ords-config

Download the latest ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip and unzip the contents to your target directory (eg. /u01/). Optional rename the ords directory from the archive and add the ORDS version number, eg. ords-23-1 (and reflect that in the $ORDS_HOME variable in .bashrc). This makes it easier to switch versions when neccesary.
As $PATH and $ORDS_CONFIG are already set, from anywhere run
 
ords install --interactive

ORDS will ask for some configuration parameters. In most cases, you can just accept the defaults.
If you have followed my guide so far, the only parameters, that need to be set, are:
  • Enter the database service name [orcl]: freepdb1
  • Provide database username with administrator privileges: sys (and your password)
  • Enter the APEX static resources location: /u01/apex/images/
After the setup, ORDS will keep running in standalone mode. We are not done yet, but give it a try to check if everything went fine so far. Wait until the ORDS has completely started: 
Open Firefox and enter http://localhost:8080/ords, that should give you the Apex login page (as this is the first call, it will take a few seconds).
Looking at the ORDS terminal window, you will see the following warnings:
Though this is not a production environment, I prefer to configure the system to run without these warnings. At least it makes it easier to notice, if something really goes wrong. In this single user environment, we can set both parameters to lower values like below. 
Go to /u01/ords-config/global/ and edit settings.xml. Add the following lines before the closing </properties>:

<entry key="jdbc.MaxLimit">30</entry>
<entry key="jdbc.InitialLimit">10</entry> 

I also got the following error:
INFO Disabling document root because the specified folder does not exist: /u01/ords-config/global/doc_root
To get rid of this INFO either create that directory or edit /u01/ords-config/global/settings.xml and set standalone.doc.root to an existing folder.

Sometimes I ran into errors were all like WARNING The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked
Probably I messed up my passwords when running the installer. This can be fixed by setting the passwords for the following users (and unlocking them, just in case):

1
2
3
4
5
6
7
8
alter user ORDS_PUBLIC_USER account unlock;
alter user APEX_PUBLIC_USER account unlock;
alter user APEX_LISTENER account unlock;
alter user APEX_REST_PUBLIC_USER account unlock;
alter user ORDS_PUBLIC_USER identified by oracle;
alter user APEX_PUBLIC_USER identified by oracle;
alter user APEX_LISTENER identified by oracle;
alter user APEX_REST_PUBLIC_USER identified by oracle;

Now ORDS should come up without errors.

Just to double check that everything is there, call http://localhost:8080/ords/sql-developer

Of course, you need a rest enabled schema to log in. That's it, everything is running.

Wednesday, 26 August 2020

The Oracle DB Developer VM: Installing SQLDeveloper

This post is part of my Oracle DB Developers VM series. Based on the previous postings on how to set up Oracle Linux 8 with the Oracle DB on VirtualBox, this post will show how to install the Oracle SQLDeveloper 20.2 in this environment on a beginners level.
Download the latest SQLDeveloper from Oracle. I recommend downloading it from your host OS and put it in some directory that is accessible from your VM. I use the version for 'Other platforms', which is not platform specific.
Extract it to the ~/bin directory in your home, next to your sqlcl folder. Start it with

~/bin/sqldeveloper/sqldeveloper.sh

After startup you are immediately prompted to connect to one of your databases.
Choose the one you want ...
... and enter your credentials
It will appear on the left in the Connections-tree and is immediately available for using.
Also add the PDB to your connection list.
You can either select the ORCLPDB entry from the Welcome Page or right-click on Oracle Connections, choose New Connection and fill out the dialog. To make sure that everything is correct, click Test before Connect.

Now for some convenience, and again here is where Gnome 3 gets more in my way than helping me. Create the file /home/oracle/.local/share/applications/sqldeveloper.desktop with an editor and insert the following

[Desktop Entry]
Type=Application
Name=SQLDeveloper
Comment=Oracle SQLDeveloper
Icon=/home/oracle/bin/sqldeveloper/sqldeveloper/doc/icon.png
Exec=/home/oracle/bin/sqldeveloper/sqldeveloper.sh
Terminal=false
Categories=Development;Programming

Then completely restart the VM as it seems that Gnome only loads this files on startup.
Now Gnome finds SQLDeveloper in it's Activities ...
... and also in the Applications Menu under Programming.

And again, you can use the same archive to also run SQLDeveloper on your host OS.
Just unzip the archive to any directory. In my case on Windows I copy the sqldeveloper folder from the zip to D:\Oracle and pin the sqldeveloper.exe to the Taskbar.
SQLDeveloper found my previous connectsions and also offers me to import the new ones from tnsnames.ora, as in my VM.

So with SQLcl and SQLDeveloper we have the basic tools every DB developer needs ready to use in both our VM and the host OS.

Thursday, 20 August 2020

The Oracle DB Developer VM: Upgrade SQLcl to latest version

This post is part of my Oracle DB Developers VM series. Based on the previous postings on how to set up the Oracle DB on Linux 8 in VirtualBox, this post will show how to upgrade the VM to use the latest SQLcl.

First we need to install java:

dnf in java-11-openjdk.x86_64
java -version

That should give you the info on OpenJDK:
Download the lastest SQLcl from https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip and unzip it to a directory for all your executables under your home directory. In my case:

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

Test it with

sqlcl/bin/sql

and type version. That should return
Add the following line to your .bashrc:

export PATH=/home/oracle/bin/sqlcl/bin
export SQLPATH=~/sql

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

set statusbar on
set highlighting on

This will enable some of the cool features of SQLcl. It is so easy, this is probably my shortest posting ever. 

But wait, there is more. As you already have downloaded the latest platform independent SQLcl, you can also use it to connect from your host OS to the database in your VM.

So in my case, use Windows Explorer to extract sqlcl to a folder on your drive, eg. D:\Oracle . Put the bin directory into your path. Of course, this SQLcl installation also needs a compatible Java runtime. So if you don't have one, install it first. On Windows better use the installer based version (jdk-11.0.8_windows-x64_bin.exe). I had issues with the JDK zip distributions, probably I have installed and uninstalled so many JDK's, that my Windows Registry is messed up. But the installer based version ran flawless.

sql.exe system@orcldevvm-ol8:1521/orclpdb

Try it with an easy connect string.
You can also connect via TNS. Create a directory like D:\Oracle\network\admin and an environment variable to point to it like

set TNS_ADMIN=D:\Oracle\network\admin

And better make that permanent. Create a tnsnames.ora file in that directory, here is an example to adapt:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcldevvm-ol8)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcldevvm-ol8)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

Now try it with TNS connect

sql.exe system@orclpdb

and that should work too.
SQLcl is a super fast and easy way to talk to your Oracle DB.

Tuesday, 18 August 2020

Installing Oracle DB 19c on Oracle Linux 8.2 in VirtualBox

This post is part of my Oracle DB Developers VM series. Based on the previous postings on how to set up Oracle Linux 8 on VirtualBox, this post will show how to install the Oracle DB 19.3 in this environment on a beginners level.
The Oracle DB installation on Oracle Linux is straight forward as expected. In this post, I will only list the needed steps for the Oracle Developer VM configuration. For a full documentation and/or installing a production environment, follow the official Oracle documentation and/or have a look at the guide on Oracle-Base. The official documentation is longer for a reason. It covers many more options like other Linux distros (inclusive Linux on IBM System Z), clustering, other storage and networking options. If these are not needed, this post is a quick installation guide.
In this example, we will use the new Image-Based Oracle Database Installation. Download the Oracle DB 19.3 software image from the Oracle Software Delivery Cloud or OTN. If you download it from the host OS, put the zip file somewhere into the shared folders of your VM. I recommend downloading from the host OS, because you can use the install media for several VM's without needing extra space on your HDD.

sudo mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
sudo chown -R oracle:oinstall

Create the Oracle install directory on the volume we created in the last episode and grant it to the oracle user and group.

Edit .bashrc and add the following to set a minimal environment:

export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORACLE_SID=orcl

export PATH=$ORACLE_HOME/bin:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

Also add the following line to your .bash_profile to run .bashrc on logon:

. .bashrc

Next extract the software to the Oracle home directory and run the installer, in my case

cd $ORACLE_HOME
unzip ~/ddrive/Install/Linux-x64/LINUX.X64_193000_db_home.zip
export CV_ASSUME_DISTID=OEL8.2
./runInstaller
The DISTID is needed to suppress a warning message.

The installation is straight forward, mostly clicking Next on each step:


 Give it a password and ignore the message

 Allow it to run the root script and give it your root password

Click Yes because that is what we gave it the root password for and get a coffee.
When finished, the installer invites you to Oracle Enterprise Manger Database Express
Close the installer and open the URL, accept the https exception in your browser and log in with system and your password.
Looks good. Close the browser and go back to the shell to double check if the command line tools are working.
Check the listener with lsnrctl stat.
Try sqlplus / as sysdba with show sga ...
... and it's younger brother SQLcl with sql / as sysdba  and any complex SQL statement you can think of.

To not always need to run .oraenv on every new shell, especially when mostly using the same database anyways, also add the following to your .bashrc:

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

By setting ORAENV_ASK back to YES, you can always use .oraenv to switch to a different database.

Add the PDB to your $ORACLE_HOME/network/admin/tnsnames.ora


  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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

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

Just copy the entry for ORCL and replace both occurrences with ORCLPDB.

To have at least one user to play with, enable the HR schema. Connect to the DB with sql system@orclpdb and enter:

alter user hr account unlock identified by hr;
grant connect, resource to hr;

Of course this are no security best practices and you will wisely choose a secure password and will grant fine granulated rights.

Optional: connect from the outside
If you only plan to use this DB with the tooling from inside the VM, you are already done. For connecting from the outside, make sure that the Listener listens on the host only network adapter.
Open /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora in your editor. If it only listens on localhost it will not listen to traffic on the host only network vor VirtualBox like here:


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

Change the localhost to the hostname of your VM, the same that you put into /etc/hosts.
(Note: for whatever reason, the hostname does not work on my system, so I used the IP address instead)

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

Restart your Listener with lsnrctl stop and lsnrctl start, then check it's status with lsnrctl stat.
It should now listen on both, your external and local IP addresses.

The fastest and easiest way of getting a development instance of the Oracle DB is using the Oracle Cloud. But when a local installation is needed, having a proper set up Linux machine or VM, installing a simple Oracle DB for Developers is quick and easy.

Tuesday, 28 July 2020

Preparing the Linux VM for Oracle DB installation

This posting is part of my Oracle DB Developers VM series. Based on the previous postings on how to set up Oracle Linux 8 on VirtualBox, this post will show how to prepare the Oracle Linux 8 VM for the installation of Oracle DB 19.3 on a beginners level.
First we need to allocate space for the Oracle software installation and database files. Following the Oracle Optimal Flexible Architecture, all Oracle software and data should be stored on volumes mounted to /u01, /u02 ... etc. For our dev system, we will put everything on one volume mounted under /u01, that needs to be created under VirtualBox first.
Shut down your VM first, if running. In the VirtualBox Manager, open the settings for your VM and go to the Storage section. Select the virtual SATA controller and click the icon to add a hard disk.
Click the button to create a new disk image.
 Accept the defaults on the first two pages of the wizard.
On the 3rd page, change the file name to something meaningful and make the disk as large as you like. It won't allocate much storage on your drive, as it is a dynamically allocated volume.
Select the newly created disk and click Choose.
The new disk should be listed in the list of connected storage devices. Now start your VM.
First assure that the new disk is visible to the OS by running a sudo fdisk -l . Your disk should be listed as /dev/sdb like shown above.
Next use fdisk to create a partition.  Run sudo fdisk /dev/sdb, n for new partition, accept all the defaults and w to write all changes to disk.
Now if you re-run sudo fdisk -l it should list partition 1 on /dev/sdb that we have just created as /dev/sdb1.
We now can format that partition. I always go with ext4 unless I have a very good reason to choose something else. All the other file systems have their strengths, but these mostly don't pay off in such a development VM. Run mkfs.ext4 /dev/sdb1 and accept all defaults.
Mount the volume with the following

sudo mkdir /u01
sudo mount /dev/sdb1 /u01
sudo chown oracle:oinstall /u01
touch /u01/asdf
ll /u01/

Now that the oracle user has access, let's make that mount permanent
Run sudo vi /etc/fstab and add the following line:

/dev/sdb1  /u01   ext4 defaults 0 0

So much for the file system, now lets configure networking. As this VM is intended to be run on a system that is already protected by a firewall, the Linux firewall can be switched off by typing

sudo systemctl stop firewalld
sudo systemctl disable firewalld

Set the hostname to something meaningful with

sudo hostnamectl set-hostname orcldevvm-ol8
hostnamectl
In VirtualBox Manager, check your IP range for the host-only network by clicking File|Host Network Manager...
So the VM's IP address will be from the 192.168.56.1/24 network. To find out the address, switch to your VM and type

ip address
The only IP address in that range is in my case the 192.168.56.104. This might differ in your installation, note this address. For the next steps, is important to use this address because later we will bind the Listener to that address so you can use your tooling from outside the VM if you want.
Edit /etc/hosts and add a line with this address and the hostname, eg.
For convenience, also add the hostname to your host OS, eg. for Windows use your favorite editor in admin mode and edit C:\Windows\System32\drivers\etc\hosts
To skip many other manual setup tasks, install the oracle-database-preinstall-19c.x86_64 package via

sudo dnf install oracle-database-preinstall-19c.x86_64
Answer y and wait until the packages are installed
So much for the pre-installation tasks. The VM is ready for an Oracle installation.