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.

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;
}

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

APEX Office Print plug-in upgrade script

Run the script connected to SQLcl as the owner (parsing schema) of the application.
We need to repeat this script for each application.

DECLARE
    l_workspace_id   NUMBER;
BEGIN
    SELECT workspace_id
      INTO l_workspace_id
      FROM apex_workspaces
     WHERE workspace = 'OPTRUST';

    --
    apex_application_install.set_workspace_id (l_workspace_id);
    apex_application_install.set_application_id (&1);
    apex_application_install.generate_offset;
    apex_application_install.set_schema ('OPTRUST');
END;
/

@dynamic_action_plugin_be_apexrnd_aop_convert_da.sql
@dynamic_action_plugin_be_apexrnd_aop_da.sql
@process_type_plugin_be_apexrnd_aop.sql
COMMIT;

Export APEX application/workspace with SQLcl

Export one application

apex export -applicationid 102 -split -skipExportDate -expTranslations -expPubReports -expSupportingObjects N -dir c:\backup

Export all applications

apex export -instance -applicationid 100 -split -skipExportDate -expTranslations -expPubReports -expSupportingObjects N -dir c:\backup

Export all workspaces

apex export -expWorkspace -applicationid 100 -expFiles -skipExportDate -dir c:\backup

Tuning Tomcat 9 for APEX/ORDS in Production

Logging

Configuration File: $CATALINA_HOME/conf/logging.properties
Change all occurrences of FINE and INFO to WARNING or SEVERE

JVM Options

Run tomcat9w.exe
Change Initial Memory pool and Maximum memory pool to 4096.

Main Tomcat Server Configurations

Configuration File: $CATALINA_HOME/conf/server.xml

<Connector port="443" protocol="org.apache.coyote.http11.Http11AprProtocol" 
  SSLEnabled="true" scheme="https" secure="true"
  maxHttpHeaderSize="32767"
  compression="on"
  disableUploadTimeout="true">
  <UpgradeProtocol overheadDataThreshold="0" compression="on" className="org.apache.coyote.http2.Http2Protocol" />

...

</Connector>

Close to the end of server.xml

<Host name="localhost" appBase="webapps"
            unpackWARs="true" autoDeploy="true">

        <Context path="/ords" reloadable="false" />

...

</Host>

Oracle REST Data Service

Configuration File: defaults.xml
Change the limits

<entry key="cache.metadata.enabled">true</entry>
<entry key="jdbc.InitialLimit">20</entry>
<entry key="jdbc.MinLimit">20</entry>
<entry key="jdbc.MaxLimit">50</entry>
<entry key="jdbc.MaxStatementsLimit">20</entry>

Oracle APEX Upgrade

Upgrade
I am using Oracle APEX Static Resources on Content Delivery Network. There is no need to copy the images directory to tomcat as /i/

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@apexins.sql APEX_DATA APEX_DATA TEMP https://static.oracle.com/cdn/apex/20.2.0.00.20/
@apex_rest_config.sql

Install French Language
goto this directory apex/builder/fr

set NLS_LANG=American_America.AL32UTF8
sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
ALTER SESSION SET CURRENT_SCHEMA = APEX_200200;
@load_fr.sql

Apply Patch
unzip p32006852_2020_GENERIC.zip, goto patch directory

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@catpatch.sql

Re-compile all objects

EXEC utl_recomp.recomp_parallel;
SELECT COUNT (*)
  FROM dba_objects
 WHERE status = 'INVALID'; 

Oracle APEX dynamic style base on domain name

Add a new process with the following PL/SQL code to the login page Before Header Section.

Pre-Rendering->Before Header->Processes

DECLARE
  v_http_host varchar2(1000);
BEGIN 
  v_http_host := lower(owa_util.get_cgi_env('HTTP_HOST'));

  if v_http_host = 'domain1.com' or v_http_host like '%.domain1.com' then
    apex_theme.set_session_style(p_theme_number=> 242, p_name => 'my_style1');
  elsif v_http_host = 'domain2.com' or v_http_host like '%.domain2.com' then
    apex_theme.set_session_style(p_theme_number=> 242, p_name => 'my_style2');
  else 
    apex_theme.set_session_style(p_theme_number=> 242, p_name => 'my_style1');
  end if;
END;

Font APEX Icons

https://apex.oracle.com/pls/apex/f?p=42:4000

Viewing APEX Mail Log and Queue

Send email via APEX using email template

DECLARE
    l_id NUMBER;
BEGIN   
    APEX_UTIL.set_workspace (p_workspace => 'YOUR WORKSPACE NAME HERE');

    l_id :=
        APEX_MAIL.SEND (p_to                   => 'pchiu@xxxxxxx.com',
                        p_from                 => 'no-reply@xxxxxxx.com',
                        p_application_id       => 110,
                        p_template_static_id   => 'BUYBACK_LOA_YEAREND',
                        p_placeholders         => '{' || '}');

    APEX_MAIL.PUSH_QUEUE;
    COMMIT;
END;
/

First you need to change security group to your workspace name before running the query.

BEGIN   
    APEX_UTIL.set_workspace (p_workspace => 'YOUR WORKSPACE NAME HERE');
END;
/

SELECT * from APEX_MAIL_LOG ORDER BY LAST_UPDATED_ON DESC;

SELECT * from APEX_MAIL_QUEUE ORDER BY LAST_UPDATED_ON DESC;

APEX application passed Ministry of Health security check

One of the APEX applications I am working on just passed the government security audit with flying color. It is a portal where the cardholders can check out their medical claims history, payment etc. The database has the cardholder personal information, address, date of birth and claims history.

Needless to say, it must passed government security check before they let you open it up to the public. The last thing they want is on the 630 headline news like yahoo 😅

Here is want I have done.

1. Salted Password

In the worst case scenario, whoever has the login data still need year over year to brute-force the password.

2. APEX-SERT

Our application has 200+ pages, the first time I ran APEX-SERT, OMG, I was SHOCKED. There are over 2000 actionable items to fix. It took me over a week to clean up all the mess.

3. SQL Injection

All records more than one rows are displayed via Interactive report or classic report. They can add filters via the build-in UI, I don't take parameters directly. By design, I am pretty much immune from this problem.

4. SSL Server Test

First test our score was C, not good. We fixed the followings and end up with an A 😃

1. Reissue the SHA1 certificate with SHA256
2. Update java to JDK7 with UnlimitedJCEPolicyJDK7
3. Update tomcat to v7 and customize the server.xml

There are probably a few more minor items but I can't recall now.

Just want to say big thank you to

Oracle APEX team

APEX-SERT team

Defuse security who wrote the salted password article

QUALYS SSL Labs for their free SSL test site