Migrate APEX to Autonomous Database with custom ORDS (notes)

Posted on
  • OCI Identity Concepts
    • create user
    • create group (admin / user )
    • assign users to group
    • create policies ( enable [GROUP / SERVICE] to [manage/ inspect/ use/ read ] resources )
      • Click here to see more common policies
    • create compartments ( logical isolation)
    • within compartments create resources
OCI identity concepts
  • Virtual Network
    • All OCI resources needs to reside within a virtual network

Create And Manage Your Custom ORDs

  • Create a ATP in a private Subnet
  • In Subnet Security List open Port 1521, 1522 and 8080
  • Create VM (oracle linux cloud developer) this image has sqlplus installed in Private Or Public Subnet
  • Open Port 8080 with firewall-cmd (opc user)
  • reload Firewall-cmd (opc user)
  • download your ATP wallet and store it in /tmp folder on VM (opc user)
  • create folder /opt/oracle/network/admin (oracle user)
  • unzip /tmp/wallet **** .zip to /opt/oracle/network/admin (oracle user)
  • write : export TNS_ADMIN=/opt/oracle/network/admin(oracle user)
  • cd $TNS_ADMIN (oracle user)
  • nano sqlnet.ora (oracle user)
  • and change the location of the wallet, which should be the PWD (current working directory) (oracle user)
  • sqlplus admin@YOURADMINPASS (oracle user)
-- 1. Create the database user.
create user custom_ords_public_user identified by YOURPASSWORD;

-- 2. Allow the user to connect to the database.
grant connect to custom_ords_public_user;

-- 3. Perform some magic.
begin
    ords_admin.provision_runtime_role(
        p_user => 'CUSTOM_ORDS_PUBLIC_USER'
        , p_proxy_enabled_schemas => true
    );
end;
/

credits goes two these two links

https://fuzziebrain.com/content/id/2004/

https://www.linkedin.com/pulse/run-your-oracle-apex-apps-autonomous-database-own-domain-kurt-liu/

  • also set the CDN image location
begin 
  apex_instance_admin.set_parameter(
      p_parameter => 'IMAGE_PREFIX',
      p_value     => 'https://static.oracle.com/cdn/apex/22.2.0/');
  commit;
end;
/
# Use the following to verify:
SELECT apex_instance_admin.get_parameter('IMAGE_PREFIX') FROM dual;
  • and to reset it (in case u need it )
-- On-Premise Instance.
begin
  apex_instance_admin.set_parameter
   (p_parameter => 'IMAGE_PREFIX',   
    p_value     => '/i/' );
   commit;   
 end;
/
-- Autonomous example for APEX 22.1
-- Note: The value can change after Oracle applies patch sets 22.1.0, 22.1.1, etc.
begin
  apex_instance_admin.set_parameter
   (p_parameter => 'IMAGE_PREFIX',   
    p_value     => '/i/22.2.0/' );
   commit;   
 end;
/
  • intall ORDS (opc user ) sudo yum install ords -y
  • switch to Oracle user sudo su - oracle 
ORDS_CONFIG_DIR=/opt/oracle/ords/config && mkdir -p $ORDS_CONFIG_DIR/ords/conf

ORDS_USER=CUSTOM_ORDS_PUBLIC_USER
ORDS_PASSWORD=YOURPASSWORD
SERVICE_NAME=prd_low(change it to your own )
WALLET_BASE64=`base64 -w 0 /tmp/wallet_prd.zip`(change it to your own)


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

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">5</entry>
  <entry key="jdbc.MaxLimit">10</entry>
</properties>
EOF
# recently found a better to install 
yum install ords -y

ords install

# choose 1 to install ords on desired  db when prompt for 1, install , 2 pool and install , 3 pool . 

#follow up the requirements 


# this will create two files <databases> and <globals>
# control + C to get out standalone mode 
ords serve 
#to start it ords again

How to enable web sql-developer https://host/ords/sql-developer

two ways to enable it.

Go to Apex and enable rest for the parsing schema. make your the alias is in smallcase.

or

logon to the schema and write this plsql block to enable it. (read here and here for more information)

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => '${YOURSCHEMA}',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => '${yourschema}',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Leave a Reply