Thursday, August 4, 2022

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

What are we installing?

Oracle APEX (Application Express)
ORDS (Oracle REST Data Services)
On an Oracle Database and Tomcat Server (Using Installing Oracle Database and Tomcat on WLS-2/Docker)

Is this a best practice?

It is much easier to avoid the APEX/ORDS installation by Getting Started with 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.

There are several ways to obtain an active Oracle Support Identifier:

Ask your employer/client to add you to their support contract.
Become an Oracle Partner Member (Development Only).
Purchase an Oracle Database Personal Edition (On-Premises) with Support (Development Only).

What do we need?

Oracle Installation Files for APEX and ORDS

APEX installation zip file (We will be using "apex_21.2_en.zip")
Patch Set Bundle for Oracle APEX 21.2 (Requires Oracle Support Login. The simplest way to get an Oracle Support Login is to purchase an Oracle Database Personal Edition License with support at Oracle Store. We renamed "p33420059_212_GENERIC.zip" to "apex_p33420059_212_GENERIC.zip")
ORDS installation zip file (We will be using "ords-22.2.0.172.1758.zip")

The Windows "W:" drive should be mapped to "\\wsl$\Ubuntu".
The following requires an Oracle Database and Tomcat server setup and running. For this example, I am using a setup described at Installing Oracle Database and Tomcat on WSL-2/Docker. I have both of these are running in Docker:

Note: ORDS 22.2 requires Oracle JDK version 11 or higher. The "tomcat:9.0" docker image includes OpenJDK 11.0.13. "Oracle JOK and OpenJDK builds are essentially identical from Java 11 onward"
These Docker Volume mappings should also be in place, per the Blog Post:

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:

Windows Setup Files for Installation
Ubuntu Setup Files for Installation
Create a PDB
Install APEX in the New PDB
Patch APEX in the New PDB
Install APEX Images on Tomcat
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

Start the Ubuntu App
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

Open Docker Desktop.
Find the "OraEE213" container.
Click the ">_" Icon (CLI) for that container to open a new window.
Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
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

Open Docker Desktop.
Find the "OraEE213" container.
Click the ">_" Icon (CLI) for that container to open a new window.
Run "cd /opt/install_files/apex212" to move into the apex folder.
Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
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

Open Docker Desktop.
Find the "OraEE213" container.
Click the ">_" Icon (CLI) for that container to open a new window.
Run "cd /opt/install_files/apex212/p33420059" to move into the apex folder.
Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
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

Open Docker Desktop.
Find the "TomCat9r0" container.
Click the ">_" Icon (CLI) for that container to open a new window.
Run "cd /opt/install_files/apex212_images" to move into the APEX Images folder.
Run "cp -rv . "${CATALINA_HOME}/webapps/apex212_images""
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.
Open Docker Desktop.
Find the "TomCat9r0" container.
Click the ">_" Icon (CLI) for that container to open a new window.
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

Remove ORDS from Tomcat
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

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:

There is a bug in Oracle's Dockerfile (shown below).
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

Dual Stack IPv4/IPv6 - Both IPv4 and IPv6 are available on the network.
Cisco RV042G - Cisco router for small business networks. (https://www.amazon.com/CISCO-Dual-Gigabit-WAN-Router/dp/B008CWW6VY)
Dual WAN Router - 2 ISPs (Internet Service Providers) are connected to the same 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.

https://docs.aws.amazon.com/vpc/latest/userguide/vpc-ip-addressing.html

"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:

https://[fc00::1]/home.htm

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

Sunday, May 26, 2019

Express Edition 18c ORA-12505 listener does not currently know of SID given in connect descriptor

Brief Solution:

Oracle Database Express Edition (XE) 18c was easy to setup. However, I was surprised when I tried to login to the database today and received the ORA-12505 error message. The problem was a different IP Address assigned to my computer. These IP addresses are located in 2 configuration files for the TNS Listener.

ORACLE_HOME/network/admin/listener.ora
ORACLE_HOME/network/admin/tnsnames.ora

I was able to connect after correcting the IP address for my computer in both files.

More Details:

First, the TNS Listener typically uses the listener.ora file for startup configuration. The default name for the TNS Listener is LISTENER. The LISTENER section of the listener.ora file includes the HOST. This tells the LISTENER what interface/network to open for reading.

The XE 18c installation used an IP address for this HOST setting. In my case, DHCP assigned a different IP address to my computer when I connected. The old IP address would not allow the TNS Listener to run. After correcting the IP address in the listener.ora file, the TNS Listener ran, but the database would not register with it.

The database parameter LOCAL_LISTENER tells the database where to find the TNS Listener. The XE 18c installation sets this value to LISTENER_XE. When the database attempts to register, it uses the tnsnames.ora file to find the LISTENER_XE alias. Similar to the listener.ora file, the tnsnames.ora file has a LISTENER_XE section that includes a HOST value. After correcting the IP address in the tnsnames.ora file, the database successfully registered with the TNS Listener.

Oracle Documentation:

About Service Registration (Net Services Administrator's Guide)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/understanding-oracle-net-architecture.html#GUID-A47F7687-1F80-48B1-8E76-7FF9D410560A

If the listener is not running when an instance starts, then the LREG process cannot register the service information. LREG attempts to connect to the listener periodically, but it may take up to 60 seconds before LREG registers with the listener after it has been started. To initiate service registration immediately after the listener is started, use the SQL statement ALTER SYSTEM REGISTER.

Overview of Oracle Net Listener (Net Services Administrator's Guide)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-A3263EB4-C3F2-4529-ABC2-ADE749114D33

Because the configuration parameters have default values, it is possible to start and use a listener with no configuration. This default listener has a name of LISTENER...

Registering Information with a Local Listener (Net Services Administrator's Guide)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/configuring-and-administering-oracle-net-listener.html#GUID-0E7C39E3-4627-403A-AE69-E9AA2C7E4C57

Synchronization occurs when the protocol address of the listener is specified in the listener.ora file and the location of the listener is specified in the initialization parameter file.
To have the LREG process register with a local listener ..., configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.

Protocol Parameters (Net Services Reference)

https://docs.oracle.com/en/database/oracle/oracle-database/18/netrf/protocol-address-configuration.html#GUID-71A0702F-A5C6-4122-907D-5E9BFA1DCE45

The listener and Oracle Connection Manager are identified by protocol addresses.
The following table lists the parameters used by the Oracle protocol support:

Wednesday, June 17, 2015

7 Ways to Log Data in the Oracle Database

Updated 14-Dec-2015: Corrected image for 6. Pipelined Function

Overview

Insert into Table

AUTONOMOUS_TRANSACTION

DBMS_APPLICATION_INFO

SET_SESSION_LONGOPS

DBMS_OUTPUT

Pipelined Function

DBMS_PIPE

Each section contains:

Executable code
An example output of the code
"The Good"
"The Bad"

All code has been tested in SQL*Plus on Oracle11gXE. The following setup must be run as "SYS as SYSDBA". This setup must be complete before running code in any section.

create user demo identified by demo
   default tablespace users;

grant connect, resource to demo;
grant select on SYS.V_$SESSION to demo;
grant execute on DBMS_LOCK to demo;
grant execute on DBMS_PIPE to demo;
grant create job to demo;

connect demo/demo

create table demo_tab
   (dtm  timestamp(6)
   ,usr  varchar2(30)
   ,sid  number
   ,txt  varchar2(4000)
   ,loc  varchar2(4000)
   );

create trigger demo_tab_bi before insert
   on demo_tab for each row
begin
   if :new.dtm is null then
      :new.dtm := systimestamp;
   end if;
   if :new.usr is null then
      :new.usr := USER;
   end if;
   if :new.sid is null then
      :new.sid := sys_context('USERENV','SID');
   end if;
   if :new.loc is null then
      :new.loc := DBMS_UTILITY.FORMAT_CALL_STACK;
   end if;
end demo_tab_bi;
/

create procedure log_demo (in_text in varchar2)
is PRAGMA AUTONOMOUS_TRANSACTION;
begin
   insert into demo_tab (txt) values (in_text);
   commit;
end log_demo;
/

create or replace type pipeline_demo_tab
  as table of varchar2(40);
/

create or replace function pipeline_demo
 return pipeline_demo_tab pipelined as
begin
  pipe row('Logging Demo');
  for i in 1 .. 5 loop
    dbms_lock.sleep(1);    -- delay between iterations
    pipe row('  Test: ' || i);
  end loop;
end;
/

create package pipe_demo as
   PIPE_NAME    CONSTANT varchar2(15) := 'Logging Demo';
   status       number;
   procedure log(in_txt in demo_tab.txt%TYPE);
   procedure run_server;
   procedure stop_server;
end pipe_demo;
/

create package body pipe_demo as
procedure log(in_txt in demo_tab.txt%TYPE) is begin
   dbms_pipe.reset_buffer;
   dbms_pipe.pack_message('LOG');
   dbms_pipe.pack_message(to_char(systimestamp,'YYYYMMDDHH24MISS.FF6'));
   dbms_pipe.pack_message(USER);
   dbms_pipe.pack_message(to_number(sys_context('USERENV', 'SID')));
   dbms_pipe.pack_message(DBMS_UTILITY.FORMAT_CALL_STACK);
   dbms_pipe.pack_message(in_txt);
   status := dbms_pipe.send_message(PIPE_NAME);
end log;
procedure run_server is
   cmd_buff     varchar2(30);
   dtm_buff     varchar2(25);
   usr_buff     demo_tab.usr%TYPE;
   sid_buff     demo_tab.sid%TYPE;
   loc_buff     demo_tab.loc%TYPE;
   txt_buff     demo_tab.txt%TYPE;
begin
   insert into demo_tab (txt) values ('Server started');
   commit;
   loop
      loop -- Allow the PIPE Read timeout to prevent "freezing"
         status := dbms_pipe.receive_message (PIPE_NAME, 10);  -- seconds
         exit when status != 1;   -- Status 1 is a timeout error
      end loop;
      if status != 0 then
         insert into demo_tab (txt) values ('Received status ' || status);
         commit; exit;
      end if;
      dbms_pipe.unpack_message(cmd_buff);
      if upper(cmd_buff) = 'LOG' then
         dbms_pipe.unpack_message(dtm_buff);
         dbms_pipe.unpack_message(usr_buff);
         dbms_pipe.unpack_message(sid_buff);
         dbms_pipe.unpack_message(loc_buff);
         dbms_pipe.unpack_message(txt_buff);
         insert into demo_tab (dtm, usr, sid, loc, txt)
            values (to_timestamp(dtm_buff,'YYYYMMDDHH24MISS.FF6'),
               usr_buff, sid_buff, loc_buff, txt_buff);
         commit;
      else
         insert into demo_tab (txt) values ('Received ' || cmd_buff);
         commit; exit;
      end if;
   end loop;
end run_server;
procedure stop_server is begin
   dbms_pipe.reset_buffer;
   dbms_pipe.pack_message('STOP');
   status := dbms_pipe.send_message(PIPE_NAME);
end stop_server;
end pipe_demo;
/

1. Insert into Table

connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap

insert into demo_tab (txt) values ('I want to log this text');

select dtm, usr, sid, txt from demo_tab order by dtm desc;

rollback;

select dtm, usr, sid, txt from demo_tab order by dtm desc;

The Good:

Simple.
Reasonably fast.

The Bad:

Not necessarily persistent - a ROLLBACK can remove uncommitted records from the DEMO_TAB table.
Unavailable to "other" sessions until COMMIT.

2. Autonomous_Transaction

connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap

BEGIN
   log_demo('No rollback on this record');
   rollback;
END;
/

select dtm, usr, sid, txt from demo_tab order by dtm desc;

The Good:

Persistent - a ROLLBACK will not remove records from the DEMO_TAB table.
Immediately available to "other" sessions.

The Bad:

Requires a procedure or a package.
Performance penalty - Redolog Buffer Flush during COMMIT.

3. DBMS_APPLICATION_INFO

connect demo/demo
column sid format 99999
column client_info format a20
column module format a20
column action format a30

BEGIN
   DBMS_APPLICATION_INFO.set_client_info
      (client_info => 'Logging Demo');
   DBMS_APPLICATION_INFO.set_module
      (module_name => 'Demo3' 
      ,action_name => 'Temporary Entry'); 
END;
/

select sid, client_info, module, action from V$SESSION
 where client_info = 'Logging Demo';

The Good:

Simple.
Very fast.
Immediately available to "other" sessions.

The Bad:

Security - Need access to V$SESSION
Not persistent - V$SESSION data is automatically removed from memory.
Not really a log - Only 1 V$SESSION entry per session.

4. SET_SESSION_LONGOPS

connect demo/demo
column sid format 99999
column opname format a30
column sofar format 9999
column units format a5

DECLARE
   ri  binary_integer := dbms_application_info.set_session_longops_nohint;
   sn  binary_integer;   -- slno
BEGIN
   DBMS_APPLICATION_INFO.set_session_longops
      (rindex => ri, slno => sn, units => 'un',sofar => 0,
       op_name => 'Logging Demo', totalwork => 1);
   DBMS_APPLICATION_INFO.set_session_longops
      (rindex => ri, slno => sn , sofar => 1,
      totalwork => 1);
END;
/

select sid, opname, sofar, totalwork, units from v$session_longops
 where opname = 'Logging Demo' order by start_time desc;

The Good:

Very Fast.
Automatically calculates TIME_REMAINING.
Immediately available to "other" sessions.

The Bad:

Security - Need access to V$SESSION_LONGOPS.
Can require some thought to properly setup.
Not persistent - V$SESSION_LONGOPS data is automatically removed from memory.

5. DBMS_OUTPUT

connect demo/demo
set serveroutput on

BEGIN
   DBMS_OUTPUT.put_line('Logging Demo');
   for i in 1 .. 5 loop
      dbms_lock.sleep(1);    -- delay between iterations
      DBMS_OUTPUT.put_line('  Test: ' || i);
   end loop;
END;
/

The Good:

Simple.
Very Fast.
Can read DBMS_OUTPUT buffer within session PL/SQL.

The Bad:

Not persistent - Stored in DBMS_OUTPUT buffers.
Client must wait for call to complete before reading buffer.
Never available to "other" sessions.

6. Pipelined Function

connect demo/demo
set arraysize 1

select * from table(pipeline_demo);

The Good:

Fast.
Results returned while PL/SQL is executing.

The Bad:

Not persistent - Results returned via SQL SELECT.
Must hide DML behind autonomous_transaction
Strange way to run PL/SQL, using an SQL SELECT.
Never available to "other" sessions.

7. DBMS_PIPE

connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap

BEGIN
   DBMS_SCHEDULER.create_job
      (job_name   => 'pipe_demo_server'
      ,job_type   => 'PLSQL_BLOCK'
      ,job_action => 'BEGIN pipe_demo.run_server; END;'
      ,enabled    => TRUE);END;
/

--  PAUSE HERE to allow the job to start
select job_name, session_id from user_scheduler_running_jobs;

BEGIN
   log_demo('Sending message on pipe.');
   pipe_demo.log('This is the pipe message');
   log_demo('Sent message on pipe.');
   pipe_demo.stop_server;
END;
/

--  PAUSE HERE to allow the job to complete
select job_name, session_id from user_scheduler_running_jobs;

select dtm, usr, sid, txt from demo_tab order by dtm desc;

The Good:

Very Fast.
If configured, can be available to multiple "other" sessions.

The Bad:

Complicated to Setup.
Not persistent while in the pipe.
Reading a pipe can hang, causing the database to "freeze'.

Cleanup

Cleanup code must be run as "SYS" or "SYSTEM".

BEGIN
   demo.pipe_demo.stop_server;
END;
/

drop user demo cascade;