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.  Dulcian.com 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
as
    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
as
    procedure ut_announce
            (in_str         in varchar2)
    is
    begin
        dbms_output.put_line('');
        dbms_output.put_line('========================================');
        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
    is
        l_result   number;
    begin
        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)
    is
    begin
        if ut_assert(in_test_name, in_test_string) = C_TRUE
        then
            if g_output_mode != C_BRIEF_OUTPUT
            then
                DBMS_OUTPUT.PUT_LINE(' -) PASSED: ' || in_test_name);
                if g_output_mode = C_VERBOSE_OUTPUT
                then
                    DBMS_OUTPUT.PUT_LINE('             Details: "' ||
                        replace(in_test_string,CHR(10),';') || '"' );
                end if;
            end if;
        else
            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
    is
    begin
        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.



Overview:

  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
  • apex_21.2_en.zip
  • apex_p33420059_212_GENERIC.zip
  • ords-22.2.0.172.1758.zip


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/apex_21.2_en.zip
mv apex apex212
#
unzip /mnt/c/tmp/apex_p33420059_212_GENERIC.zip
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/ords-22.2.0.172.1758.zip


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')
   STORAGE UNLIMITED TEMPFILE REUSE;

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

@catpatch.sql


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 http://127.0.0.1:8080/apex212_images/apex_ui/img/apex-logo.svg.  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}"

cd "${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
OraEE213#!
Passw0rd!
Passw0rd!
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

Removal

  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 ./AP212PDB_PDB.zip /opt/oracle/oradata/EE213CDB/AP212PDB/*

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

Saturday, July 9, 2022

Distributed (Offline) Issue (Bug) Tracker

Git is one of the most popular source (version) control tools today.

GitHub and GitLab are very popular hosting sites for Git Repositories.

As a developer, I have come to appreciate Git's ability to continue using source control when there is no internet connection.  This is especially useful when travelling.

Both GitHub and GitLab also have Wiki Pages.  These are very useful for documenting development procedures and other things that aren't included in product documentation.  Because GitHub and GitLab implement Wiki Pages in a Git Repository, the Wiki Repository can be downloaded and updated offline.

Then there are the Issue Trackers. Both the GitHub Issue Tracker and GitLab Issue Tracker are only available online.  Since these Issue Trackers are used to guide and document software development, they need to be available during development activities.  However, neither of these Issue Trackers are available offline.

The need for a distributed issue (bug) tracker is evident from the attention it is getting on the internet.  Several attempts have been made, but I have not been able to find anything stable and actively supported.

To solve this problem, I created a Wiki Based Issue Tracker using the GitHub Issue Tracker and basic Linux/GNU tools (bash, awk, curl, sed, jq, ls, head, and date).  It includes a utility that will capture GitHub issues and convert them to Wiki Based Issues (designed for one-time conversion of all issues).



Summary Report Screenshot (Partial)

What prevents 2 developers from modifying the same issue?

Nothing. Multiple developers can modify the same issue in the same way multiple developers can modify the same source code file in Git.  And, just like Git, the "merge" is where it all gets sorted.

What about a Kanban Board?

A basic issue reporting tool is provided.  This tool can be modified to create a Kanban Board style report.

What about an interactive Kanban Board?

Hmmm...

Monday, March 28, 2022

The Decline of Modern Management

I previously posted this for about a month, then took it down.  I am not in favor of people doing that, so I am posting it again.  I updated it from the original post.  I am still not happy with it.  I am removing it again.  I will leave this as a reference to what I had posted.

Monday, January 17, 2022

Installing Oracle Database and Tomcat on WSL-2/Docker

What are we installing?
  • Windows Subsystem for Linux (WSL2)
  • Docker Desktop for Windows
  • Oracle Database
  • Tomcat Web Server
What do we need?
  • Computer/Laptop
  • Windows 10 version 2004 and higher (Build 19041 and higher) or Windows 11
  • 16 Gb Memory Recommended
Overview:
  • Setup Windows Subsystem for Linux (WSL2).
  • Setup Docker for Windows.
  • Load and Run a Docker Image that has an Oracle Database.
  • Load and Run a Docker Image that has a Tomcat WebApp Server.
  • Failed ORDS Docker Image with Failure Source Identified.

Windows Subsystem for Linux (WSL2)

This procedure was sourced from Microsoft.com: Install WSL:
  • Run CMD as Administrator
    • wsl -l -v
      • Should show "NAME STATE VERSION" heading with a line underneath showing "Ubuntu Running 2".  If not, run the "wsl --install" in the next step.
    • wsl --install -d Ubuntu
      • A reboot will be required to activate the installation.
      • The Ubuntu App will run automatically after reboot.
      • The Ubuntu App will create a new username and password. - The username and password created in the Ubuntu App is specific to each separate Linux distribution that you install under WSL and has no relationship to your Windows user name.
      • If any problems, see Microsoft.com: installation-issues
    • wsl --shutdown - Run this if you need to shutdown WSL.
  • Start the Ubuntu App and Run These Commands
    • sudo apt update - Updates the list of packages available for Ubuntu.
    • sudo apt upgrade - Upgrades Ubuntu packages.
  • Review and Set advanced options as needed.  See Microsoft.com: Advanced settings configuration in WSL
See also: Microsoft.com: Setup WSL for Development


Docker Desktop for Windows

  • Go to Docker.com: Docker Desktop
  • Click on "Download for Windows"
  • Run "Docker Desktop Installer.exe"
  • Open Docker Desktop
    • "Settings" --> "Resources" --> "WSL Integration"
    • Turn on "ubuntu" for "enable integration with additional distros:"
    • Apply & Restart
  • Run Ubuntu App
    • (Optional) docker run hello-world

Oracle Database

This procedure will persist the database files outside of the Docker container at "/opt/OraEE213/oradata" in WSL2/Ubuntu.  After the database is created, it will re-open with all saved changes if the Docker container is restarted.
  • Open the Ubuntu App and Run These Commands:
sudo mkdir /opt/OraEE213/oradata

sudo chown 54321 /opt/OraEE213/oradata

docker network create oranet

docker login container-registry.oracle.com

docker pull container-registry.oracle.com/database/enterprise:21.3.0.0

docker run -d --restart unless-stopped --network=oranet -p 1521:1521 -p 5500:5500 -e ORACLE_SID=EE213CDB -e ORACLE_PDB=TESTPDB -e 'ORACLE_PWD=OraEE213#!' -v /opt/OraEE213/oradata:/opt/oracle/oradata -v /opt/install_files:/opt/install_files --name OraEE213 container-registry.oracle.com/database/enterprise:21.3.0.0
  • Run Docker Desktop
    • Select "Containers/Apps" in the menu list on the left.
    • Click on the "Logs" Icon in the "OraEE213" container line.
    • The log of the database installation will appear.
    • Wait for this banner before attempting to use the database:

#########################

DATABASE IS READY TO USE!

#########################

    • (Optional) Click the ">_" Icon (CLI) on the "OraEE213" container line and run "sqlplus / as sysdba" to login to the container database as "SYS".
    • (Optional) Connect to the container database from an Oracle client using the connect string "//localhost:1521/EE213CDB" and "system/OraEE213#!" login.
    • (Optional) Connect to the pluggable database from an Oracle client using the connect string "//localhost:1521/TESTPDB" and "system/OraEE213#!" login.
    • (Optional) Check OEM DB Express at url https://localhost:5500/em/login, Username: SYSTEM, Password: OraEE213#!, Container Name: (Blank)

Tomcat Web Server

  • Open the Ubuntu App and Run This Command
    • docker run -d --restart unless-stopped --network=oranet -p 8080:8080 -v /opt/TomCat9r0/webapps:/usr/local/tomcat/webapps -v /opt/install_files:/opt/install_files --name TomCat9r0 tomcat:9.0
  • Run Docker Desktop
    • Select "Containers/Apps" in the menu list on the left.
    • Click the ">_" Icon (CLI) on the "TomCat9r0" container line.
    • Run these commands:
cp -rf "/usr/local/tomcat/webapps.dist"/* "/usr/local/tomcat/webapps"

sed "-i_bak" -e '$s?^</tomcat-users>$?<role rolename="manager-gui"/><user username="manager" password="tomcat" roles="manager-gui"/></tomcat-users>?1' "/usr/local/tomcat/conf/tomcat-users.xml"

sed "-i_bak" -e '/RemoteAddrValve/d' -e '/0:0:0:0:0:0:0:1/d' "/usr/local/tomcat/webapps/manager/META-INF/context.xml"

sed "-i_bak" -e '/RemoteAddrValve/d' -e '/0:0:0:0:0:0:0:1/d' "/usr/local/tomcat/webapps/host-manager/META-INF/context.xml"
  • (Optional) Go to "http://localhost:8080/"
    • Click on "Manager App" button
    • Login with Username "manager" and password "tomcat"
    • Confirm Tomcat Web Applications are available and working.

Next Post: Installing ORDS and Oracle APEX

In the next post "Installing ORDS and Oracle APEX on WSL-2/Docker", we look at installing ORDS and Oracle APEX

Addendum: Failed ORDS Docker image

An alternative to the Tomcat installation is using Oracle's ORDS Dockerfile to create a Docker Container with ORDS.  However, there are 2 problems with this alternative:
  1. There is a bug in Oracle's Dockerfile (shown below).
  2. Multiple Docker Containers are required for each ORDS deployment.  Each deployment of ORDS requires a different HTTP Port Number.  With Tomcat, all ORDS servers share the same HTTP Port Number.
For the source of this section, see Oracle.com: Container Registry: "Database" --> "ords" --> "Oracle REST Data Services (ORDS) with Application Express"
  • Open the Ubuntu App and Run These Commands:
sudo mkdir "/opt/ords21.4.0"

echo 'CONN_STRING=SYS/OraEE213#!@//OraEE213:1521/TESTPDB' > "/tmp/conn_string.txt"

sudo mv "/tmp/conn_string.txt" "/opt/ords21.4.0"

docker run --rm --network=oranet -p 8181:8181 -v /opt/ords21.4.0:/opt/oracle/variables --name ords21.4.0 container-registry.oracle.com/database/ords:21.4.0
  • FAILS with this message:
INFO : This container will start a service running ORDS 21.4.0 and APEX 21.2.0.
INFO : CONN_STRING has been set as variable on container.
INFO : Database connection established.
INFO : Apex is not installed on your database.
INFO : Installing APEX on your DB please be patient.
INFO : If you need more verbosity run below command:
docker exec -it b402760c75a3 tail -f /tmp/install_container.log
INFO : APEX has been installed.
INFO : Configuring APEX.
INFO : APEX_PUBLIC_USER has been configured as oracle.
INFO : APEX ADMIN password has configured as 'Welcome_1'.
INFO : Use below login credentials to first time login to APEX service:
Workspace: internal
User: ADMIN
Password: Welcome_1
INFO : Preparing ORDS.
INFO : Installing ORDS on you database.
Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Connecting to database user: SYS as sysdba url: jdbc:oracle:thin:@////OraEE213:1521/TESTPDB
2022-01-18T00:14:23.731Z WARNING Failed to connect to user: SYS as sysdba url: jdbc:oracle:thin:@////OraEE213:1521/TESTPDB
IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]" (CONNECTION_ID=DUId2GdqTiaf5/93oFpqsw==)

java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]"  (CONNECTION_ID=DUId2GdqTiaf5/93oFpqsw==)
INFO : Starting ORDS....
2022-01-18T00:14:27.622Z WARNING     Failed to connect to user: ORDS_PUBLIC_USER url: jdbc:oracle:thin:@////OraEE213:1521/TESTPDB
IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]"  (CONNECTION_ID=Z2b/FsUxR1C7IzNDt5+RTQ==)

Problem: The "/opt/oracle/ords/startService.sh" script in the container splits the $CONN_STRING value into DB_USER, DB_PASS, DB_HOST, DB_PORT, and DB_NAME variables.  The DB_HOST value retains the leading "//" in front of the hostname in the $CONN_STRING value.  The "//" is required for the database connection, but fails when used by ORDS.

update: Changed "\" to "/" on database connect strings.

Sunday, June 16, 2019

Dual Stack IPv4/IPv6 on Cisco RV042G Dual WAN Router



I have several servers on AWS (Amazon Web Services). I pay a fee each month for an Elastic IPv4 address. I use this address for an OpenVPN server to access my EC2 instances.

I found that AWS provides free public IPv6 addresses for all EC2 instances in a VPC (Virtual Private Cloud) networks.  The key here is that a public IPv6 address is permanently assigned to the EC2 instance, whether it is running or not.  There is no need to pay the fee for an Elastic IP address.



"No distinction between public and private IP addresses. IPv6 addresses are public"

"An IPv6 address persists when you stop and start your instance, and is released when you terminate your instance."


I confirmed all my computers, network equipment, and ISPs would work with IPv6.  After upgrading my router to a Cisco RV042g, I had everything.  However, IPv6 addresses were not working.  I found the time to troubleshoot the problem and found a solution.

I have Spectrum (Time Warner Cable / Road Runner) on WAN1 and AT&T on WAN2.  Both are on a gigabit connection.  AT&T is metered, so I use Spectrum as primary and AT&T as backup.  I also route all voice-over-IP traffic through AT&T.  Both WANs are configured the same in the IPV6 configuration in the Cisco router.

Note: IPv6 will not automatically fail-over.  It looks like IPv6 is not getting rerouted to the backup WAN.  IPv6 seems to have an affinity for the last WAN it was using.  Resetting the network connection on the computer solves the problem, but that is not automatic.  I tried a smaller value for "Router Lifetime" (30 seconds) in the Router Advertisement configuration.  The IPv6 traffic did not get rerouted to the backup WAN. I updated to the latest firmware v4.2.3.09 (Jul 2 2018 14:38:22), but IPv6 would not reroute to the backup WAN.

https://blog.ipspace.net/2014/03/can-we-use-ipv6-router-advertisements.html




Enable ICMP (Ping) on Windows 10 Firewall (Inbound and Outbound):

I found this command to open ICMP on my Windows firewall
netsh firewall set icmpsetting type=all mode=enable
https://answers.microsoft.com/en-us/windows/forum/all/windows-10-firewall-filtering-icmp/527551d0-e477-4dd6-a0a0-eae724940ba3



Then, I disabled all the rules that allow inbound and outbound ICMPv4.














Cisco RV042g Configuration (non-default settings for IPv6):


Note: Some of these images were captured with the primary WAN (WAN1) disabled.

















These are Google's IPv6 DNS Servers:
  • 2001:4860:4860:8844
  • 2001:4860:4860:8888












After configuring IPv6, I was able to contact my Cisco router using this URL:





Spectrum (WAN1) IPv6 Test Results:


I ran these tests with the WAN2 port disabled on the router.













AT&T (WAN2) IPv6 Test Results:



I ran these tests with the WAN1 port disabled on the router.









"ipconfig" Shows my Ethernet Adapter Configuration:



Note:  Unlike IPv4, there are many IPv6 addresses on my network adapter.  Also, there are 2 sets of IPv6 addresses, one for each ISP (Dual WAN).

  • 2605:6000... - Spectrum
  • 2600:1700... - AT&T