Tuesday, December 30, 2025

Secure password storage for SQL*Plus

Create the C:\Program Files\instantclient\mkstore.bat

@echo off
rem inspiration: https://andriydmytrenko.wordpress.com/2013/07/01/using-the-secure-external-password-store-with-instant-client/

setlocal

rem get the command line arguments
set args=
:loop
  if !%1==! goto :done
  set args=%args% %1
  shift
  goto :loop
:done

rem set classpath for mkstore - align this to your local SQLcl installation
set sqlcl="C:\Program Files\sqldeveloper\sqldeveloper\lib"
set classpath=%sqlcl%\oraclepki.jar
set classpath=%classpath%;%sqlcl%\osdt_core.jar
set classpath=%classpath%;%sqlcl%\osdt_cert.jar

rem simulate mkstore command
java -classpath %classpath% oracle.security.pki.OracleSecretStoreTextUI %args%

endlocal

Create the wallet and store the credential

mdkir "C:\oracle_wallets"
mkstore -wrl "C:\oracle_wallets" -create
mkstore -wrl "C:\oracle_wallets" -createCredential paris chiup XXXXXXXX
mkstore -wrl "C:\oracle_wallets" -listCredential

Create C:\Program Files\instantclient\network\admin\sqlnet.ora

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\oracle_wallets)
    )
  )
SQLNET.WALLET_OVERRIDE = TRUE

Test it with SQL*Plus

sqlplus /@paris

Friday, December 12, 2025

Configure jobStatusRepository in Oracle Reports 12c

Create the service account

CREATE USER RW_SERVER_SVC
  IDENTIFIED BY "password"
  HTTP DIGEST DISABLE
  DEFAULT TABLESPACE SMALL_DATA
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

-- 1 Role for RW_SERVER_SVC 
GRANT RESOURCE TO RW_SERVER_SVC;
ALTER USER RW_SERVER_SVC DEFAULT ROLE ALL;

-- 1 System Privilege for RW_SERVER_SVC 
GRANT CREATE SESSION TO RW_SERVER_SVC;

-- 2 Tablespace Quotas for RW_SERVER_SVC 
ALTER USER RW_SERVER_SVC QUOTA UNLIMITED ON SMALL_DATA;
ALTER USER RW_SERVER_SVC QUOTA UNLIMITED ON USERS;

On Application Server

cd E:\oracle\penfax_app\Oracle_Home\reports\admin\sql
SQL> connect RW_SERVER_SVC/<password>@DEV
SQL> @rw_server.sql
SQL> grant select on rw_server_svc.rw_server_job_queue to optrust,rpa_api;

On Application Server

Edit this file E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration\rwserver.conf

Add this XML under the <server> tag

<jobStatusRepository class="oracle.reports.server.JobRepositoryDB">
    <property name="dbpassword" value="password"/>
    <property name="dbconn" value="xxxxxxxx.yyyyyy.local:1521:DEV"/>
    <property name="dbuser" value="rw_server_svc"/>
</jobStatusRepository>

Check the output after running a report

  SELECT *
    FROM RW_SERVER_SVC.RW_SERVER_JOB_QUEUE
   WHERE server = 'rep_wls_reports_parisas01p'
ORDER BY job_id DESC;

Monday, May 1, 2023

Tuesday, January 17, 2023

Oracle forms and reports OHS service stop working

We are running Oracle forms and reports server since 2018 and all of a sudden the OHS service crashed on one server and then next one and next one and next one... Not good.

After some debugging execises, the problem is due to

  1. Self-signed cwallet.sso expired and
  2. Weblogic nodemanger default keystore SSL certficate expired

Let's start from cwallet.sso

File location: E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\keystores\default\cwallet.sso
Let's check the expired date

orapki wallet display -wallet cwallet.sso
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY
Trusted Certificates:
Subject:        CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY
 
orapki wallet export -wallet cwallet.sso -dn "CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY" -cert cwallet.cer

Goto Certificate Decoder and paste the cwallet.cer content into there

The current cwallet.sso is expired. We need to create a new one

orapki wallet create -wallet ./ -pwd WalletPasswd123 -auto_login
orapki wallet add -wallet ./ -pwd WalletPasswd123 -dn "CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY" -keysize 1024 -self_signed -validity 3650

Replace E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\keystores\default\cwallet.sso with the new one.

Next, let's fix nodemanager SSL certificate

cd E:\oracle\penfax_app\Oracle_Home\oracle_common\common\bin\

E:\oracle\penfax_app\Oracle_Home\oracle_common\common\bin>wlst.cmd

Initializing WebLogic Scripting Tool (WLST) ...

Jython scans all the jar files it can find at first startup. Depending on the system, this process may take a few minutes to complete, and WLST may not return a prompt right away.

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> nmConnect(domainName='base_domain', username='weblogic', password='leave.me.alone')
Connecting to Node Manager ...
<Jan 17, 2023 11:23:07 AM EST> <Info> <Security> <BEA-090905> <Disabling the CryptoJ JCE Provider self-integrity check for better startup performance. To enable this check, specify
 -Dweblogic.security.allowCryptoJDefaultJCEVerification=true.>
<Jan 17, 2023 11:23:07 AM EST> <Info> <Security> <BEA-090906> <Changing the default Random Number Generator in RSA CryptoJ from ECDRBG128 to HMACDRBG. To disable this change, specify -Dweblogic.security.allowCryptoJDefaultPRNG=true.>
<Jan 17, 2023 11:23:07 AM EST> <Info> <Security> <BEA-090909> <Using the configured custom SSL Hostname Verifier implementation: weblogic.security.utils.SSLWLSHostnameVerifier$Null
HostnameVerifier.>
Traceback (innermost last):
  File "<console>", line 1, in ?
  File "<iostream>", line 111, in nmConnect
  File "<iostream>", line 553, in raiseWLSTException
WLSTException: Error occurred while performing nmConnect : Cannot connect to Node Manager. : General SSLEngine problem
Use dumpStack() to view the full stacktrace :
wls:/offline>

This doesn't tell much.

set WLST_PROPERTIES=-Djavax.net.debug=all -Dssl.debug=true
E:\oracle\penfax_app\Oracle_Home\oracle_common\common\bin>wlst.cmd

Initializing WebLogic Scripting Tool (WLST) ...

Jython scans all the jar files it can find at first startup. Depending on the system, this process may take a few minutes to complete, and WLST may not return a prompt right away.

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> nmConnect(domainName='base_domain', username='weblogic', password='leave.me.alone')


...


***
main, fatal error: 46: General SSLEngine problem
sun.security.validator.ValidatorException: PKIX path validation failed: java.security.cert.CertPathValidatorException: timestamp check failed
%% Invalidated:  [Session-1, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256]
main, SEND TLSv1.2 ALERT:  fatal, description = certificate_unknown
main, WRITE: TLSv1.2 Alert, length = 2
main, fatal: engine already closed.  Rethrowing javax.net.ssl.SSLHandshakeException: General SSLEngine problem
main, called closeOutbound()
main, closeOutboundInternal()
[Raw write]: length = 7
0000: 15 03 03 00 02 02 2E                               .......
wls:/offline>

Check the output close to the bottom, it tells us there is a "timestamp check failed" problem.

Let's generate new Custom Identity and Custom Trust jks file

E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -genkey -alias base_domain -keyalg RSA -keysize 2048 -dname "CN=base_domain, OU=Digital, O=Leave Me Alone, L=Toronto, ST=Ontario, C=CA" -keypass WalletPasswd123 -keystore identity.jks -storepass WalletPasswd123
E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -selfcert -v -alias base_domain -keypass WalletPasswd123 -keystore identity.jks -storepass WalletPasswd123 -storetype jks -validity 3650
E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -export -v -alias base_domain -file rootCA.der -keystore identity.jks -storepass WalletPasswd123
E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -import -v -trustcacerts -alias base_domain -file rootCA.der -keystore trust.jks -storepass WalletPasswd123

Copy identity.jks and trust.jks to E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\security

In Weblogic Admin Console

Custom Identity Keystore: E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\security\identity.jks
Custom Trust Keystore: E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\security\trust.jks

Edit E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\nodemanager\nodemanager.properties, add the following to the bottom.

StartScriptEnabled=true
KeyStores=CustomIdentityAndCustomTrust
CustomIdentityKeyStoreFileName=E\:\\oracle\\penfax_app\\Oracle_Home\\user_projects\\domains\\base_domain\\security\\identity.jks
CustomIdentityAlias=base_domain
CustomIdentityPrivateKeyPassPhrase=WalletPasswd123
CustomTrustKeyStoreFileName=E\:\\oracle\\penfax_app\\Oracle_Home\\user_projects\\domains\\base_domain\\security\\trust.jks

We need to update the java Trust certificate as well

E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -import -alias base_domain -trustcacerts -file rootCA.der -keystore E:\oracle\temp\java\jdk1.8.0_112\jre\lib\security\cacerts -storepass changeit

Finally, everything is done. Reboot the server and OHS should start automatically.

Thursday, December 15, 2022

ORDS Install/Upgrade (ORDS Version 22.1 Onward)

Install

unzip ords-22.4.0.340.1044.zip to C:\Users\chiup_ad\Downloads\
stop "Apache Tomcat 9" service
rmdir E:\tomcat9\webapps\ords 
copy ords.war E:\tomcat9\webapps\ 
start "Apache Tomcat 9" service

Upgrade

cd /D C:\Users\chiup_ad\Downloads\ords-22.4.0.340.1044\bin

Create a file password.txt with content below
SYS password here
ORDS_PUBLIC_USER password here

For each db pool, run this command to upgrade ORDS schema
ords --config E:\ords install --log-folder e:\ords\logs --admin-user SYS --db-pool ave  --db-hostname parisdb01poc --db-port 1521 --db-servicename paris.optrust.local --feature-db-api true --feature-rest-enabled-sql true --feature-sdw true --gateway-mode proxied --gateway-user APEX_PUBLIC_USER --proxy-user --password-stdin < password.txt

Friday, November 19, 2021

Service Unavailable after upgraded to APEX 21.2

I was freak out when I saw this right after APEX upgrade. I've been doing APEX upgrade since APEX 4 and this is the first time I encounter this error.

Reviewing the upgrade log and I found this foreign Key error

Error starting at line : 53 File @ C:\Users\chiup\Downloads\apex_21.2\apex\coreins5.sql
In command -
begin
    --
    -- We need to directly delete dependencies (to XDB), because the
    -- namespace switch from SERVER to DBTOOLS would result in a FK error.
    -- There is no suitable API in dbms_registry.
    --
    delete from sys.registry$dependencies d
        where cid = 'APEX';
    --
    -- Update registry
    --
    wwv_install_api.upgrade_registry;
end;
Error report -
ORA-02292: integrity constraint (SYS.REGISTRY_PROGRESS_FK) violated - child record found
ORA-06512: at "SYS.DBMS_REGISTRY", line 299
ORA-06512: at "APEX_210200.WWV_INSTALL_API", line 603
ORA-06512: at line 12
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

Checking the SYS.REGISTRY$PROGRESS table, I found an orphan record way back from APEX 5 days.

Delete the orphan record, drop APEX_210200 schema, re-run the upgrade process and I am back in business.

Saturday, October 30, 2021

Run Oracle Database Express Edition using Podman on an Oracle Cloud compute instance

In this article, I will demonstrate how to run Oracle Database Express Edition using Podman on an Oracle Cloud compute instance.

On Oracle Cloud Console

  1. create instance, name instance-21c-xe
  2. change image to Oracle Linux 8.0
  3. New VCN, New Public Subnet, Assign Public IPv4 address

It is not necessary to use a paid instance. The free instance will work as well.

Wait for the instance to be ready. Go to Security List, add Ingress Rule for port 1521

ssh into the new instance

sudo bash
ln -sf /usr/share/zoneinfo/Canada/Eastern /etc/localtime
dnf -y install podman podman-docker buildah skopeo dnf-utils zip unzip tar gzip git sqlcl
dnf -y update
podman pull docker.io/gvenzl/oracle-xe:21-full
podman run -d -p 1521:1521 -e ORACLE_PASSWORD=SysPassword1 --name 21cFull -v oracle-volume:/opt/oracle/XE21CFULL/oradata gvenzl/oracle-xe:21-full
podman ps

Give it 2 minutes to startup

podman logs 21cFull

You should find this in the output
#########################
DATABASE IS READY TO USE!
#########################

Testing via SQLcl

sql system/SysPassword1@//localhost/XEPDB1

Setup archivelog mode via opening a session into the container

podman exec -it --user=oracle 21cFull bash
sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Setup autostart on reboot

podman generate systemd --name 21cFull > /lib/systemd/system/container-21cFull.service
systemctl enable container-21cFull

All set. Reboot, wait for a few minutes and check again

Wednesday, August 25, 2021

Using Google Fonts in Oracle APEX

1. Pick the font you want from Google Fonts. In this example, I picked Roboto, and you should add all the styles to the font family.

2. Add this CSS via Theme Roller->Custom CSS
@import url('https://fonts.googleapis.com/css2?family=Roboto:ital,wght@0,100;0,300;0,400;0,500;0,700;0,900;1,100;1,300;1,400;1,500;1,700;1,900&display=swap');
:root {
  --a-base-font-family: 'Roboto', sans-serif;
}

Sunday, August 8, 2021

Add Prism Syntax Hightlighter to Blogger

1. Goto Usage with CDNs

2. Go to your blogger, Template->Edit HTML and paste the followings right before the </head> tag. We have also included Line Numbers and Copy to Clipboard Button plugin.
<head>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/themes/prism.min.css' integrity='sha512-tN7Ec6zAFaVSG3TpNAKtk4DOHNpSwKHxxrsiw4GHKESGPs5njn/0sMCUMl2svV4wo4BK/rCP7juYz+zx+l6oeQ==' referrerpolicy='no-referrer' rel='stylesheet'/>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/line-numbers/prism-line-numbers.min.css' integrity='sha512-cbQXwDFK7lj2Fqfkuxbo5iD1dSbLlJGXGpfTDqbggqjHJeyzx88I3rfwjS38WJag/ihH7lzuGlGHpDBymLirZQ==' referrerpolicy='no-referrer' rel='stylesheet'/>
<link crossorigin='anonymous' href='https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/toolbar/prism-toolbar.min.css' integrity='sha512-ycl7dIZ0VJ5535/dzskAMTwOI6OoTNZ3PeD+tfckvYqMmAzaEwQfJHqJTSqcV2iQeJnp5XxnFy5jKotibstp7A==' referrerpolicy='no-referrer' rel='stylesheet'/>
<style>
  pre {
    font-size:smaller;
   }
</style>
</head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/components/prism-core.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/line-numbers/prism-line-numbers.min.js" integrity="sha512-dubtf8xMHSQlExGRQ5R7toxHLgSDZ0K7AunqPWHXmJQ8XyVIG19S1T95gBxlAeGOK02P4Da2RTnQz0Za0H0ebQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/toolbar/prism-toolbar.min.js" integrity="sha512-YrvgEHAi5/3o2OT+/vh1z19oJXk/Kk0qdVKbjEFl9VRmcLTaWRYzVziZCvoGpJ2TrnV7rB8pnJcz1ioVJjgw2A==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/copy-to-clipboard/prism-copy-to-clipboard.min.js" integrity="sha512-pUNGXbOrc+Y3dm5z2ZN7JYQ/2Tq0jppMDOUsN4sQHVJ9AUQpaeERCUfYYBAnaRB9r8d4gtPKMWICNhm3tRr4Fg==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.24.1/plugins/autoloader/prism-autoloader.min.js"></script>

3. In your edit post screen, hit HTML and put this surrounding the code you want to hightlight.
<pre class="line-numbers"><code class="language-sql">
Your SQL Statement here
</code></pre>

Thursday, August 5, 2021

NGINX SSL Reverse Proxy for Tomcat/ORDS/APEX

This is a simple reverse proxy to achieve any URL structure you want for your APEX server. NGINX is responsible for handle almost everything, reverse proxy, URL redirection, HTTP/2, cache, gzip, HSTS, OCSP stapling, etc. Tomcat/ORDS/APEX is sitting behind NGINX, communicating with NGINX via HTTP.

This is the stack I am running at the moment.

  • NGINX 1.21.1
  • Tomcat 9.0.50
  • ORDS 21.2.0.r1741826
  • APEX 21.1.2
C:\Program Files\nginx\conf\nginx.conf
worker_processes auto;
pid        logs/nginx.pid;

events {
    worker_connections  1024;
}

http {
    include       mime.types;
    default_type  application/octet-stream;
    sendfile      on;
    keepalive_timeout 75;
	client_max_body_size 200M;
	proxy_cache_path "C:/Program Files/nginx/temp/proxy_cache" levels=1:2 keys_zone=STATIC:10m inactive=24h  max_size=1g use_temp_path=off;

server {
    listen 80 default_server;
    server_name _;
	
    rewrite ^ https://$host$request_uri? permanent;
}

server {
    listen 443 ssl http2 default_server;
    server_name _;
 
	gzip on;
	gzip_types	text/css text/plain text/javascript	application/javascript application/json application/x-javascript application/xml application/xml+rss application/xhtml+xml application/x-font-ttf application/x-font-opentype application/vnd.ms-fontobject image/svg+xml image/x-icon application/rss+xml application/atom_xml;
    gzip_proxied    no-cache no-store private expired auth;
    gzip_min_length 1000;
	
	ssl_certificate "E:/ssl/fullchain.cer";
	ssl_certificate_key "E:/ssl/leavemealone.com.key";
	ssl_protocols TLSv1.2 TLSv1.3;
	ssl_ciphers "EECDH+ECDSA+AESGCM EECDH+aRSA+AESGCM EECDH+ECDSA+SHA384 EECDH+ECDSA+SHA256 EECDH+aRSA+SHA384 EECDH+aRSA+SHA256 EECDH+aRSA+RC4 EECDH EDH+aRSA HIGH !RC4 !aNULL !eNULL !LOW !3DES !MD5 !EXP !PSK !SRP !DSS";
	ssl_ecdh_curve secp384r1; 
	ssl_prefer_server_ciphers on;	
	ssl_dhparam "E:/ssl/dhparam4096.pem";
	
	ssl_session_cache   shared:SSL:10m;
	ssl_session_timeout 10m;
	ssl_session_tickets off;
	
	ssl_stapling on;
	ssl_stapling_verify on;
	ssl_trusted_certificate "E:/ssl/ca.cer";

	add_header          Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
#	add_header          X-Content-Type-Options nosniff;
#   add_header          X-Frame-Options SAMEORIGIN;
#   add_header          X-XSS-Protection "1; mode=block";

    location / {
        proxy_connect_timeout       600;
        proxy_send_timeout          600;
        proxy_read_timeout          600;
        send_timeout                600;

		proxy_set_header Host $host;
		proxy_set_header X-Forwarded-Host $host:$server_port;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		proxy_set_header X-Forwarded-Proto $scheme;
		proxy_set_header Origin "";
        proxy_pass http://127.0.0.1:8080;
		proxy_http_version 1.1;
    }
	
	# cache apex application/workspace static files
	location ~* /ords/(.*)/r/([0-9/]*)files/static/v([0-9]+)/ {
		proxy_set_header Host $host;
		proxy_set_header X-Forwarded-Host $host:$server_port;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		proxy_set_header X-Forwarded-Proto $scheme;
		proxy_set_header Origin "";
        proxy_pass http://127.0.0.1:8080;
		proxy_http_version 1.1;
		
		proxy_redirect off;
		add_header X-Cache-Status $upstream_cache_status;
		expires 300d;
		proxy_cache STATIC;
		proxy_cache_key $host$uri$is_args$args;
		proxy_cache_valid 200 24h;
		proxy_cache_use_stale  error timeout invalid_header updating http_500 http_502 http_503 http_504;
	}
  }  	
}
Let me explain this line by line:
  • Line 14, proxy cache setting
  • Line 17-20, rediect all HTTP traffic to HTTPS
  • Line 24, setup HTTP/2
  • Line 27-30, enable gzip
  • Line 32-33, SSL key pair. fullchain.cer contains the server public certificate followed by immediate certificate in the same file
  • Line 34-38, SSL protocol, chipers setting
  • Line 40-42, SSL session cache setting
  • Line 44-46, OCSP stapling setting. ca.cer contains only the immediate certificate
  • Line 48, add HSTS header
  • Line 54-57, proxy timeout setting
  • Line 59-63, pass some extra headers to ORDS, so that your app can now where this request originally comes from
  • Line 64, Google Chrome enforces stricter CORS rules, than e.g. Firefox. By setting the Origin to blank we can make reverse proxying work, otherwise Chrome would block it
  • Line 65, the actual reverse proxy command saying that traffic is internally rerouted to http://127.0.0.1:8080
  • Line 80-86, proxy cache setting. We put every file found on a path like /ords/*/r/*files/static/vnnn/ subfolder for at least 24hrs and also send a 300 day expiry header to the client

There is not a lot of changes in Tomcat. Basically we need to ensure HTTP (port 8080) is working, limit access to localhost and adding the actual IP address %{X-Forwarded-For} to tomcat log file.

E:\tomcat9\conf\server.xml
<Connector port="8080" protocol="HTTP/1.1"
		scheme="https"
        proxyPort="443"
		maxHttpHeaderSize="32767"
		maxPostSize="-1"
		disableUploadTimeout="true" />
        
   <Engine name="Catalina" defaultHost="localhost">
      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">
            
...

		<Valve className="org.apache.catalina.valves.RemoteAddrValve"
			allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1"/>

		<Valve className="org.apache.catalina.valves.RemoteIpValve"
				internalProxies="127\.0\.[0-1]\.1"
				remoteIpHeader="X-Forwarded-For"
				requestAttributesEnabled="true"
				protocolHeader="x-forwarded-proto"
				protocolHeaderHttpsValue="https"/>
		
        <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
               prefix="localhost_access_log" suffix=".txt"
               pattern="%{X-Forwarded-For}i %h %l %u %t &quot;%r&quot; %s %b" />
      </Host>
   </Engine>
  • Line 2-3, in some situations APEX internally creates a redirect to a different URL path, e.g. during Authentication using Social-Login it will redirect to …/ords/apex_authentication.callback… With the verison of the stack I am using, these two lines might not be required anymore. I am still leaving them here for the peace of mind.
  • Line 14-15, to allow access only for the clients connecting from localhost
  • Line 17-26, adding the actual IP address %{X-Forwarded-For} to tomcat log file

Friday, July 30, 2021

Oracle SQL Explain Plan

Display the execution plan stored in the plan table. We are not executing the actual SQL.

EXPLAIN PLAN
    FOR SELECT /*+ gather_plan_statistics */
               COUNT (*) FROM dba_objects;

SELECT * FROM TABLE (DBMS_XPLAN.display (format => 'TYPICAL'));

Displays the actual execution plan used to run a query stored in the cursor cache. We have to execute the SQL first.

SELECT /*+ gather_plan_statistics */
       COUNT (*) FROM dba_objects;

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format => 'ALLSTATS LAST +cost +bytes +peeked_binds'));