Friday, July 5, 2024

Oracle Cloud Autonomous Database File Not Found for TLS Connection

The Attempt:

I created an Autonomous Database on Oracle Cloud.  I used TLS (protocol=tcps) to successfully connect from a Windows SQL*Plus Instant Client to the database on Oracle Cloud.  However, I was unable to connect from an Ubuntu (Gnome) SQL*Plus Instant Client to the database on Oracle Cloud.

The Error:

Each connection attempt from Ubuntu SQL*Plus Instant Client to the Autonomous Database on Oracle Cloud threw a "file not found" error.


I ran a "trace" in "sqlnet.ora" and found many "file not found" errors, all seemed to be related to Wallet, SSL, and Certificate Store.  In the trace file, I found SQL*Net was looking for the Certificate Store in "/etc/pki/tls/cert.pem".

The Solution:

I did not configure a wallet.  TLS uses CA Certificates instead of PKI certificates.

I found a single file (PEM bundle) in "/etc/ssl/certs/ca-certificates.crt".  This was confirmed at Ubuntu's Website: Ubuntu root CA certificate trust store location

I could not find a configuration in SQL*Net to change the location of the Certificate Store from "/etc/pki/tls/cert.pem" to "/etc/ssl/certs/ca-certficiates.crt".

I did find moscicki at GitHub had a Symbolic Link that I was missing.  After I created the symbolic link, I was able to make the TLS connection from Oracle Instant Client for Linux x86-64 Version to an Autonomous Database on Oracle Cloud without a wallet.

Run these as root in Ubuntu:

mkdir /etc/pki/tls
ln -s /etc/ssl/certs/ca-certificates.crt /etc/pki/tls/cert.pem

Tuesday, July 2, 2024

Git Clone GLIBCXX_3.4.29 Not Found on Xubuntu

The Attempt:

This was my first attempt to clone a Github repository on Xubuntu (XFCE Desktop) using VSCode. I did not have this problem with Ubuntu (Gnome Desktop).
  • VSCode Version
    Version: 1.90.2
    Commit: 5437499feb04f7a586f677b155b039bc2b3669eb
    Date: 2024-06-18T22:33:48.698Z
    Electron: 29.4.0
    ElectronBuildId: 9728852
    Chromium: 122.0.6261.156
    Node.js: 20.9.0
    OS: Linux x64 5.15.0-113-generic snap
  • Git Version
    root# git --version
    git version 2.34.1
  • XUbuntu Version
    root# lsb_release -a
    No LSB modules are available.
    Distributor ID: Ubuntu
    Description: Ubuntu 22.04.4 LTS
    Release: 22.04
    Codename: jammy

    root# dpkg -l '*-desktop' | grep ^ii | grep 'ubuntu-'
    ii xubuntu-desktop 2.241 amd64 Xubuntu desktop system

The Error Message:

I received an error that included the following:
git clone /home/USER/github/REPO --progress
/snap/core20/current/lib/x86_64-linux-gnu/ version `GLIBCXX_3.4.29' not found (required by /lib/x86_64-linux-gnu/
(USER is my username and REPO is the repository name.)


I searched Google and found a myriad of solutions that included:
  • Update from "snap20" to "snap22"
  • Remove/Reinstall VSCode
  • Modify "snapcraft.yaml"
I confirmed my "snap22" library contained the needed GLIBCXX_3.4.29 module. However, I didn't like any of those solutions because they required some underlying "tweeking" that has caused trouble in the past.

The Solution:

I stumbled onto this solution accidentally, and I don't know why it works. Run these commands using the "Git Bash Terminal" in VSCode
cd /home/USER/github
git clone REPO --progress

Thursday, June 13, 2024

ORA-12637: Packet Receive Failed - From Windows

What's the Problem?

I was intermittently getting an "ORA-12637: Packet Receive Failed" from Windows on a local network.

  • Running an Oracle Client on Windows.
  • Connecting to an Oracle Database on my Local Network.

What's the Fix?

Add an entry for the database server into the Windows "host" file.

I have a DHCP server that hands-out specific IPv4 addresses to each server.  I added the IPv4 address and hostname to the "C:\Windows\System32\drivers\etc\hosts" file.

Why Does this Work?

The mechanism that was supposed to resolve IP addresses from hostnames wasn't working correctly.

How to Troubleshoot?

1. Create a "C:\app\oracle\network\admin\sqlnet.ora" file with the following entries.  (My version 21.3 ORACLE_HOME is "C:\app\oracle")
# Trace Levels: off (0), user (4), admin (10), support (16)
2. Attempt to connect to the database.

3. Find a new trace file in "C:\app\oracle\network\traces" folder.

4. I found the following lines in the new trace file "cli_13580.trc":
(13580) [13-JUN-2024 11:59:26:485] nttbnd2addr: looking up IP addr for host: hostname1
(13580) [13-JUN-2024 11:59:26:485] snlinGetAddrInfo: entry
(13580) [13-JUN-2024 11:59:27:512] snlinGetAddrInfo: getaddrinfo() failed with error 11001
(13580) [13-JUN-2024 11:59:27:512] snlinGetAddrInfo: exit
(13580) [13-JUN-2024 11:59:27:512] nttbnd2addr:  *** hostname lookup failure! ***
(13580) [13-JUN-2024 11:59:27:512] nttbnd2addr: exit
5. After adding the "hosts" file entry and I no longer received the ORA-12637 error.

Friday, February 23, 2024

Synergy with Wayland Using Chromium

 This is a rather specific issue.

  • Running Synergy 1.11 on Ubuntu.
  • Sharing Keyboard/Mouse from Ubuntu to Other Computers.
  • Upgraded Ubuntu to 22.04.
  • Synergy stopped sharing.

The problem wasn't the network or the configuration because
  • Synergy successfully connected from all clients to the server.
  • The server showed all Synergy clients successfully connected.

The odd thing is Chromium.  If I start Chromium on the Ubuntu 22.04 server, the keyboard sharing starts working.  If I stop Chromium on the Ubuntu 22.04 server, the keyboard sharing stops working.  I also noticed the log messages on the Synergy client including "LANGUAGE DEBUG" warnings for every keystroke while the cursor was moved to the client.  I assume Chromium setup some debugging channel that Synergy was able to use.  (I did not research this.)

Takeaway 1:

The root cause of the problem appears to be the Ubuntu 22.04 switch to Wayland.  Synergy doesn't support Wayland, but there are plans:

Takeaway 2:

Synergy has an interim fix.  Don't use Wayland with Ubuntu...

Thursday, February 15, 2024

Unit Testing a Thick Database

Why Unit Test a Database?

In the Java (OO) world, unit testing specifically avoids testing persistence.  The persistence engine (database) is mocked so it doesn't interfere with the purity of the testing.  Also, Java (OO) purists attempt to remove all logic from the database.  However, we typically don't find such purity in practice.

What is a Thick Database?

A Thick Database is a database with lots of logic built-in. does a great job of explaining what it is and why it's useful.  In practice, we tend to see some logic implemented in the database for a variety of reasons.

What is a Unit in the Database?

Most references define a "unit" as something like the smallest bit of logic in a system.  For this discussion, "unit" will refer to an interface.  Interfaces in an Oracle database include the following:

  • Packages
    • Procedures (Public and Internal)
    • Functions (Public and Internal)
  • Procedures
  • Functions
  • Types with Methods
  • Tables
    • Check Constraints
    • DML Triggers
  • View Triggers
  • Session Triggers
  • Database Triggers

Is a Unit Test Framework Required?

No.  However, a unit test framework does allow easier integration with CI/CD.  Examples of database unit test frameworks include:

Alternatively, here is a bare-bones package that is useful when a framework is not available.

create package simple_ut
    C_FALSE           constant number := -1;
    C_TRUE            constant number := 0;
    C_BRIEF_OUTPUT    constant number := 0;
    C_NORMAL_OUTPUT   constant number := 1;
    C_VERBOSE_OUTPUT  constant number := 2;
    g_output_mode              number := C_NORMAL_OUTPUT;
    procedure ut_announce
            (in_str         in varchar2);
    function ut_assert
            (in_test_name   in varchar2
            ,in_test_string in varchar2)
        return number;
    procedure ut_assert
            (in_test_name   in varchar2
            ,in_test_string in varchar2);
    procedure demo;
end simple_ut;

create package body simple_ut
    procedure ut_announce
            (in_str         in varchar2)
        dbms_output.put_line('=== ' || $$PLSQL_UNIT || ' ' || in_str);
    end ut_announce;
    function ut_assert
            (in_test_name   in varchar2
            ,in_test_string in varchar2)
        return number
        l_result   number;
        execute immediate 'BEGIN'                        || CHR(10) ||
                          'if ' || in_test_string        || CHR(10) ||
                          'then'                         || CHR(10) ||
                             ':n1 := ' || C_TRUE  || ';' || CHR(10) ||
                          'else'                         || CHR(10) ||
                             ':n1 := ' || C_FALSE || ';' || CHR(10) ||
                          'end if;'                      || CHR(10) ||
                          'END;' using out l_result;
        return l_result;
    end ut_assert;
    procedure ut_assert
            (in_test_name   in varchar2
            ,in_test_string in varchar2)
        if ut_assert(in_test_name, in_test_string) = C_TRUE
            if g_output_mode != C_BRIEF_OUTPUT
                DBMS_OUTPUT.PUT_LINE(' -) PASSED: ' || in_test_name);
                if g_output_mode = C_VERBOSE_OUTPUT
                    DBMS_OUTPUT.PUT_LINE('             Details: "' ||
                        replace(in_test_string,CHR(10),';') || '"' );
                end if;
            end if;
            DBMS_OUTPUT.PUT_LINE('*** FAILED: ' || in_test_name);
            DBMS_OUTPUT.PUT_LINE('             Details: "' ||
                replace(in_test_string,CHR(10),';') || '"' );
        end if;
    end ut_assert;
    procedure demo
        ut_assert('This test passes', '1 = 1');
        ut_assert('This test fails', '0 = 1');
    end demo;
end simple_ut;

Where to start?

  1. Pick something to test, like a procedure in a package.
  2. Define a "No Data" Test Case for that procedure (nothing for the procedure to do).
  3. Create a Unit Test Package to contain you Unit Test code.
  4. Create a procedure in the Unit Test Package called "no_data_to_process".
  5. Write the "no_data_to_process" procedure to call the procedure (from step 1) with without any test data setup.
  6. Add "ut_assert" procedure calls from the "simple_ut" package to document results.
  7. Run the "no_data_to_process" procedure and check results in DBMS_OUTPUT.
  8. Add more tests.

Are there any Tips or Tricks?

  1. As part of the Test Case, test the data setup (if any) before the "actual" unit test.
  2. Run the "actual" unit test in a separate procedure with a dedicated Exception Handler.
  3. Create Unit Test Procedures/Functions to load Test Data into a single record variable.
  4. Use the single record variable to INSERT Test Data into a table.
  5. Setup Date/Time sensitive data for each run of a Unit Test.
  6. Create additional test customers/locations/actors as needed to re-run unit tests.
  7. Capture Sequence Generator values after the "actual" unit test.
  8. Include testing of logs after the "actual" unit test.
  9. Cleanup Test Data only when necessary to re-run unit tests.
  10. Develop a complete Test Data Set with a variety of Test Cases setup and ready for testing.

What is the downside?

If the database logic is unstable (constantly changing), it is very difficult to maintain the unit tests.  One strategy is to Unit Test only the functionality that is stable.

What is the Upside?

Several obvious, but sometimes unexpected outcomes of unit testing:
  • Testing code that serves no purpose results in removal of useless code.
  • Fault insertion testing results in much better error messages and error recovery.
  • Thinking through unit test cases results in simplification of overall implementation.

Wednesday, January 31, 2024

Monitoring Blogger with UptimeRobot

I use UptimeRobot to monitor this site. The free version of UptimeRobot includes:
  • Monitor 50 different URLs.
  • Check each URL every 5 minutes.
  • Monitor HTTP or HTTPS.
  • Test for keyword in response, with upper/lower case option.
  • Limit response times.
  • Send Email alert on failure.
  • Access to their comprehensive monitor dashboard.
Note: I am not paid by UptimeRobot. I do find it really useful.

It turns out Blogger is suspicious of servers at UptimeRobot.  The result reeks havoc when trying to test for a keyword on a Blogger page.  Instead of sending the expected page, Blogger sends something like this to UptimeRobot:

Our systems have detected unusual traffic from your computer network.  This page checks to see if it's really you sending the requests, and not a robot.

Obviously, UptimeRobot is a robot.  So, I need a work-around.

I use the "Full Response" link in UptimeRobot to review the entire response from Blogger.
  1. Go to UptimeRobot
  2. Setup a monitor on a Blogger URL and have it fail.
  3. Click on the new monitor in the navigation stack on the left.
  4. Click on "Details" at the far right of the "Incidents" region at the bottom.
  5. A "Full Response" button may be at the top of the pop-up window (Sometimes, it's not there).
  6. Right-Click and copy the URL for that button.
  7. Use something like "curl" or "wget" to pull that file for review.  (Windows Defender won't allow a browser to download the file in that link.)
Inside the contents of the "Full Response" file, I look for something unique to my Blogger page and use it for a keyword.   Yes, it technically is not loading a page from this site.  However, it does confirm Blogger is responding and it is sending something that is unique to this site.

Why did I make this post?  Blogger changed the response page and all my keyword monitors started failing.  I need this page for notes if/when it happens again...

Note: In order to keep things easy for UptimeRobot, I check all my URLs on a 4 hour interval.  Checking these URLs 6 times a day is plenty and it reduces the total load on this free service.

Thank you UptimeRobot.

Thursday, August 4, 2022

Installing ORDS 22.2 and Oracle APEX 21.2 on WSL-2/Docker

What are we installing?

Is this a best practice?

Oracle APEX Free Tier
  • To continue with this APEX/ORDS installation, an active Oracle Support Identifier is required to download the latest patch for recent APEX/ORDS releases.
Oracle APEX 21.2 Patch
Oracle Personal Database OnPremise

What do we need?

Container Name   WSL2/Ubuntu Path         Docker Path
OraEE213         /opt/install_files       /opt/install_files
TomCat9r0        /opt/install_files       /opt/install_files

Note: There is an excellent reference at Oracle-Base that includes setup of ORDS 22.1 onward and a link to setup ORDS versions previous to 22.1.


  1. Windows Setup Files for Installation
  2. Ubuntu Setup Files for Installation
  3. Create a PDB
  4. Install APEX in the New PDB
  5. Patch APEX in the New PDB
  6. Install APEX Images on Tomcat
  7. Configure and Install ORDS for the PDB on Tomcat

1) Windows Setup Files for Installation

Move/Copy these files to C:\tmp

2) Setup APEX/ORDS Files for Installation

  1. Start the Ubuntu App
  2. Run These Commands
sudo su -
apt install unzip
mkdir /opt/install_files
cd /opt/install_files
unzip /mnt/c/tmp/
mv apex apex212
unzip /mnt/c/tmp/
mv 33420059 apex212/p33420059
mv apex212/images apex212_images
cp -rv apex212/p33420059/images/* apex212_images/
rm -rf apex212/p33420059/images
chown -Rv 54321:54321 apex212
mkdir ords222
cd ords222
unzip /mnt/c/tmp/

3) Create a PDB

  1. Open Docker Desktop.
  2. Find the "OraEE213" container.
  3. Click the ">_" Icon (CLI) for that container to open a new window.
  4. Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
  5. Run the commands below in SQL*Plus:
create pluggable database "AP212PDB"
   admin user "PDB_ADMIN" identified by "PDB_ADMIN"
   default tablespace users
      datafile '/opt/oracle/oradata/EE213CDB/AP212PDB/users01.dbf'
               size 5M autoextend on
   FILE_NAME_CONVERT = ('pdbseed', 'AP212PDB')

alter pluggable database "AP212PDB" open;

*NOTE:* To remove the new PDB run `drop pluggable database "AP212PDB" including datafiles;`

4) Install APEX in the New PDB

Login to the Docker Container that is running the Oracle EE 21.3 Database.
  1. Open Docker Desktop.
  2. Find the "OraEE213" container.
  3. Click the ">_" Icon (CLI) for that container to open a new window.
  4. Run "cd /opt/install_files/apex212" to move into the apex folder.
  5. Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
  6. Run the commands below in SQL*Plus:
alter session set container = AP212PDB;

set serveroutput on size unlimited format wrapped

@apxsilentins.sql "SYSAUX" "SYSAUX" "TEMP" "/apex212_images/" \
  "Passw0rd!" "Passw0rd!" "Passw0rd!" "Passw0rd!"

select status, owner, count(*)
 from  dba_objects
 where owner in ('APEX_210200', 'FLOWS_FILES', 'APEX_LISTENER')
 group by status, owner
 order by status, owner;

alter user APEX_210200 identified by "Passw0rd!" account unlock;

NOTE: apxsilentins.sql values:
  • SYSAUX - Default Tablespace for APEX application user
  • SYSAUX - Default Tablespace for APEX file user
  • TEMP - APEX Temporary Tablespace for Tablespace Group
  • /apex212_images/ - Virtual Directory in Tomcat for APEX Images"
  • Passw0rd! - APEX Public User Account
  • Passw0rd! - APEX Listener Account
  • Passw0rd! - APEX REST Public User Account
  • Passw0rd! - APEX Internal Administrator User Account

5) Patch APEX in the New PDB

Login to the Docker Container that is running the Oracle EE 21.3 Database.
  1. Open Docker Desktop.
  2. Find the "OraEE213" container.
  3. Click the ">_" Icon (CLI) for that container to open a new window.
  4. Run "cd /opt/install_files/apex212/p33420059" to move into the apex folder.
  5. Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
  6. Run the commands below in SQL*Plus:
alter session set container = AP212PDB;

set serveroutput on size unlimited format wrapped


6) Install APEX Image Files on Tomcat

  1. Open Docker Desktop.
  2. Find the "TomCat9r0" container.
  3. Click the ">_" Icon (CLI) for that container to open a new window.
  4. Run "cd /opt/install_files/apex212_images" to move into the APEX Images folder.
  5. Run "cp -rv . "${CATALINA_HOME}/webapps/apex212_images""
  6. Confirm images are working using  A red oval with the work APEX should appear in the browser.

7) Configure and Install ORDS for the PDB on Tomcat

After I published this BLOG, I re-ran the scripts.  I found errors and missing information.  I will update as soon as possible.
  1. Open Docker Desktop.
  2. Find the "TomCat9r0" container.
  3. Click the ">_" Icon (CLI) for that container to open a new window.
  4. Run the commands below:
export PATH="$PATH:/opt/install_files/ords222/bin"

exec /bin/bash

export ORDS_CONFIG=/opt/ords222_config

mkdir "${ORDS_CONFIG}"


ords install \
  --log-folder      /opt/ords222_config/logs \
  --feature-db-api  true \
  --admin-user      SYS \
  --db-hostname     OraEE213 \
  --db-port         1521 \
  --db-servicename  AP212PDB \
  --db-user         ORDS_PUBLIC_USER \
  --feature-rest-enabled-sql true \
  --feature-sdw     true \
  --gateway-mode    proxied \
  --gateway-user    APEX_PUBLIC_USER \
  --proxy-user      \
  --password-stdin  <<EOF

ords config set security.verifySSL false

ords war ords222_ap212pdb.war

cp ords222_ap212pdb.war "${CATALINA_HOME}/webapps/"

  • Test Oracle APEX URL:
    • Paste "http://localhost:8080/ords222_ap212pdb" into a Browser
    • Workspace: INTERNAL
    • Username: ADMIN
    • Password: Passw0rd!
    • The APEX Administrator Page should appear
  • Test Web Based SQL*Developer:
    • Paste "http://localhost:8080/ords222_ap212pdb/sql-developer" into a Browser
    • Enter a Database User Name when prompted.
    • Enter a Database User Password when prompted.
    • The "Database Actions | Launchpad" page should appear.
  • REST Enable a Database Object:
    • Login to APEX as an APEX Developer.
    • Open the SQL Workshop.
    • Select Object Browser.
    • Select a table.
    • Click on the REST tab
    • "Rest Enable Object": YES
    • "Authentication Required": NO
    • Click on APPLY
    • Copy the RESTful URL
    • Past the URL into a Web Browser
    • A JSON document should return


  1. Remove ORDS from Tomcat
  2. Unplug the PDB

1) Remove ORDS from Tomcat

WARNING: This will remove the ORDS installation.  It will undo the work done in Step 7.

rm -f "${CATALINA_HOME}/webapps/ords222_ap212pdb.war

rm -rf "${CATALINA_HOME}/webapps/ords222_ap212pdb

2) Unplug the new PDB

WARNING: This will remove the PDB from the Database.  It will undo the work done in Step 3.

alter pluggable database "AP212PDB" close immediate;

alter pluggable database "AP212PDB" unplug
  into '/opt/oracle/oradata/EE213CDB/AP212PDB/AP212PDB.XML';

drop pluggable database "AP212PDB" keep datafiles;

zip -q ./ /opt/oracle/oradata/EE213CDB/AP212PDB/*

rm -rf /opt/oracle/oradata/EE213CDB/AP212PDB/*