Setup ORDS Standalone Against Autonomous Database

Why ORDS standalone

For standalone ORDS, Oracle use Jetty. Jetty is a very capable webserver that on my laptop scale to 200+ rest calls per second. There deeper details on it's scaling abilities here: http://www.eclipse.org/jetty/documentation/current/high-load.html The best advantage is it simply works, scales, easy to get up and running. The disadvantage is mainly it's a purpose built and configured web server for ORDS. If someone needs more general web server features, it'd best to use WLS / Tomcat / Glassfish.

Download the following software to the Compute Instance

• SQLcl
• APEX and Patch Set
• Autonomous database ADMIN wallet

Install software via yum

yum-config-manager --enable ol7_oci_included
yum update -y
yum install -y ords
yum install -y java
yum install -y jq
yum install -y oracle-release-el7
#yum search oracle-instant
yum install -y oracle-instantclient19.9-basic.x86_64
yum install -y oracle-instantclient19.9-tools.x86_64

Allow access to Port 8080

firewall-cmd --zone=public --add-port 8080/tcp --permanent
firewall-cmd --zone=public --add-port 8443/tcp --permanent
#firewall-cmd --permanent --zone=public --add-service=http
#firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload

Install SQLcl and ADMIN wallet

unzip sqlcl-20.4.1.351.1718.zip -d /opt
unzip Wallet_PROD.zip -d /usr/lib/oracle/19.9/client64/lib/network/admin
ln -s /opt/sqlcl/bin/sql /usr/lib/oracle/19.9/client64/bin/sql

add these to ~oracle/.bash_profile
export PATH=/usr/lib/oracle/19.9/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.9/client64/lib
export TNS_ADMIN=/usr/lib/oracle/19.9/client64/lib/network/admin

Install APEX and Patch Set

RELEASE=20.2.0.00.20
mkdir -p /opt/oracle/apex/images/$RELEASE
unzip apex_20.2.zip -d /tmp/
cp -R /tmp/apex/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/apex

unzip  p32006852_2020_Generic.zip -d /tmp/
cp -R /tmp/32006852/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/32006852

ORDS Setup

Create alternate ORDS_PUBLIC_USER2 user

sql admin@prod_low
create user ords_public_user2 identified by "Opt12345678901234567890!";
grant connect to ORDS_PUBLIC_USER2;
begin
    ords_admin.provision_runtime_role(
        p_user => 'ORDS_PUBLIC_USER2'
        , p_proxy_enabled_schemas => true
    );
end;
/

Setup enviornment variables

ORDS_CONFIG_DIR=/opt/oracle/ords/config
ORDS_USER=ORDS_PUBLIC_USER2
ORDS_PASSWORD=Opt12345678901234567890!
SERVICE_NAME=prod_low
WALLET_BASE64=`base64 -w 0 Wallet_PROD.zip`

Create directories

mkdir -p $ORDS_CONFIG_DIR/ords/conf
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/doc_root
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/etc
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/logs

Create $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="db.username">$ORDS_USER</entry>
  <entry key="db.password">!$ORDS_PASSWORD</entry>
  <entry key="db.wallet.zip.service">$SERVICE_NAME</entry>
  <entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF

Create $ORDS_CONFIG_DIR/ords/defaults.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="plsql.gateway.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>
</properties>
EOF

Create $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml

cat << EOF > $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml
<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure id="Server" class="org.eclipse.jetty.server.Server">
    <Ref id="Handlers">
      <Call name="addHandler">
        <Arg>
          <New id="RequestLog" class="org.eclipse.jetty.server.handler.RequestLogHandler">
            <Set name="requestLog">
              <New id="RequestLogImpl" class="org.eclipse.jetty.server.NCSARequestLog">
                <Set name="filename"><Property name="jetty.logs" default="/opt/oracle/ords/config/ords/standalone/logs/"/>ords-access-yyyy_mm_dd.log</Set>
                <Set name="filenameDateFormat">yyyy_MM_dd</Set>
                <Set name="retainDays">90</Set>
                <Set name="append">true</Set>
                <Set name="extended">false</Set>
                <Set name="logCookies">false</Set>
                <Set name="LogTimeZone">GMT</Set>
            </New>
          </Set>
        </New>
        </Arg>
      </Call>
    </Ref>
</Configure>
EOF

Edit /opt/oracle/ords/config/ords/standalone/standalone.properties

jetty.port=8080
standalone.context.path=/ords
standalone.doc.root=/opt/oracle/ords/config/ords/standalone/doc_root
standalone.scheme.do.not.prompt=true
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images
jetty.secure.port=8443
#ssl.cert=leavemealone.com.pem
#ssl.cert.key=leavemealone.com.key
#ssl.host=pws.leavemealone.com

Configure ORDS

ords configdir $ORDS_CONFIG_DIR

/etc/ords/ords.conf ORDS_BASE_PATH=/opt/oracle

Test run ORDS

ords standalone
wget http://localhost:8080/i/20.2.0.00.20/apex_version.txt

Auto Start ORDS

systemctl start ords
systemctl enable ords
systemctl status ords

Switch APEX static resources repository to Oracle Content Delivery Network (CDN).

BEGIN
    apex_instance_admin.set_parameter (
        p_parameter   => 'IMAGE_PREFIX',
        p_value       => 'https://static.oracle.com/cdn/apex/20.2.0.00.20/');

    COMMIT;
END;