Better Logic for Better Solutions
Feed aggregator
Free Oracle Cloud: 19. Oracle APEX upgrades (19.1 to 19.2)
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
Many people wonder how you can upgrade your Oracle APEX version to the latest version in your cloud account. The short answer is: you can't...
Oracle is doing this for you, whenever they believe it is the right time to do. The idea is that at some point you can tell Oracle when it would be a good time to upgrade your APEX environment in a specified window, but today, that is not there yet.
I read that a few days ago Oracle upgraded the APEX release to 19.2 on the free cloud.
I didn't use my Always FREE Oracle Cloud account for a few weeks, so it was stopped.
I went in the Oracle Cloud console to start it again and up we go.
Through the Tools menu, I open APEX and logged in into my CLOUD workspace. But, to my surprise my APEX version was still 19.1.
I was doing a few things when suddenly I got "Application Express is being upgraded to a newer version":
When trying to open APEX from the console, I couldn't anymore.
I waited a couple of minutes and there it was ... APEX came back and was upgraded to 19.2:
So it looks like Oracle is checking your APEX release when you start your database and when it sees it's an older version, it will just upgrade. I thought that probably because my account was stopped, Oracle will try again in the maintenance window, but my experience learns it's not waiting on that window.
I'm glad Oracle upgraded APEX for me automatically, but it would be nice to have a log somewhere or some info that it will do this. Hopefully, this blog post will help people who only fire up their instance occasionally when doing a demo. It's best to do it a bit before so the upgrade takes place before you need your APEX instance.
Many people wonder how you can upgrade your Oracle APEX version to the latest version in your cloud account. The short answer is: you can't...
Oracle is doing this for you, whenever they believe it is the right time to do. The idea is that at some point you can tell Oracle when it would be a good time to upgrade your APEX environment in a specified window, but today, that is not there yet.
I read that a few days ago Oracle upgraded the APEX release to 19.2 on the free cloud.
I didn't use my Always FREE Oracle Cloud account for a few weeks, so it was stopped.
I went in the Oracle Cloud console to start it again and up we go.
Through the Tools menu, I open APEX and logged in into my CLOUD workspace. But, to my surprise my APEX version was still 19.1.
I was doing a few things when suddenly I got "Application Express is being upgraded to a newer version":
When trying to open APEX from the console, I couldn't anymore.
I waited a couple of minutes and there it was ... APEX came back and was upgraded to 19.2:
So it looks like Oracle is checking your APEX release when you start your database and when it sees it's an older version, it will just upgrade. I thought that probably because my account was stopped, Oracle will try again in the maintenance window, but my experience learns it's not waiting on that window.
I'm glad Oracle upgraded APEX for me automatically, but it would be nice to have a log somewhere or some info that it will do this. Hopefully, this blog post will help people who only fire up their instance occasionally when doing a demo. It's best to do it a bit before so the upgrade takes place before you need your APEX instance.
Free Oracle Cloud: 7. Setup a web server on the Virtual Machine
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
In this blog post we will configure a web server on our Compute VM Instance. This allows us to host some websites and have a custom URL for our Oracle APEX instance and applications.
Lets start with connecting to our VM:
From a Terminal connect to your Oracle Cloud VM:
ssh -i ssh_key opc@public_ip
The first thing we do, is change to the root user, as we want to install a web server it will be easier to do it with the root user. Alternatively in front of every command you can add sudo.
We logged in as the OPC user, to become the ROOT user we do:
sudo su
Although it doesn't really have anything to do with setting up a web server, I do want to share this... The first thing I always like to do on a machine, is get the system updated, so all latest software is being used. To do this, run following command:
yum update
It will take some time the first time, but after a couple of minutes you should see that all packages were updated:
So the purpose of this post was to install a web server so when we type in a certain domain, it will arrive at our machine. As web server, I typically chose between Apache and Nginx. Which one to choose is a hard one... if you search Google for "Apache vs Nginx" you can start reading ;) Since last year I started to use Nginx for all my new systems, before I always used Apache.
Following steps show how you install the Nginx web server and run it:
yum install nginx
Now we need to start the web server:
systemctl start nginx
To see if Nginx is successfully running, do:
systemctl status nginx
You should see something like:
The next thing we have to do is open the firewall on the Linux box, so incoming connections are allowed. The first line will open HTTP, the second HTTPS and then we reload the firewall:
firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload
Opening the firewall on the Linux box is not enough. Oracle added some extra security layers around the VM (Compute Instance). We have to allow HTTP and HTTPS access to our machine in the Oracle Firewall too.
Click the Virtual Cloud Network link:
Click the Security Links:
And add two Ingress Rules, one for HTTP and one for HTTPS:
As Source add 0.0.0.0/0 so everything can connect to it and as destination port you specify the first time 80 (for HTTP) and the second time 443 (for HTTPS):
Once both Ingress Rules are added, your list looks like this:
Now you can navigate in a browser to your Public IP and you should see:
Now that we have a web server running and it's accessible through the IP address, we know things are working. Most of the time however you don't want to access your server through an IP address, rather you want people to use a domain name. To access my Free Oracle Cloud server for example I want to use the dgielis.com domain.
The first step to do, is in the domain provider you specify for the A record, the IP address of your Oracle Cloud VM (Compute) Instance. I typically also setup some CNAME so any sub-domain will work too. For example I could point apex.dgielis.com to Oracle APEX Builder.
Now that the domain points to our VM, we have to make sure our web server listens to this domain and knows what to do. We will need to configure Nginx for this dgielis.com domain.
Here are the steps to do this (do this in your Terminal which is connected to your VM):
vi /etc/nginx/conf.d/dgielis.com.conf
# Add following to the file (change dgielis.com by your domain):
server {
listen 80;
listen [::]:80;
server_name dgielis.com www.dgielis.com;
root /usr/share/nginx/html/dgielis.com;
index index.html;
try_files $uri /index.html;
}
# Create a directory where your website resides:
mkdir /usr/share/nginx/html/dgielis.com
# Add an index.html file to the directory
# Quickest way is vi or cp an index.html in this folder
# Or develop your site locally first and when ready upload with scp
# scp -i .ssh/oraclecloud /Users/dgielis/site.zip opc@132.145.215.55:/tmp
# to test Nginx configuration
nginx -t
# to restart Nginx
nginx -s reload
# note: in case nginx doesn't restart, kill the nginx process and try to restart again
ps -ef | grep nginx
kill ~pid~
When you go in your browser to your domain name, it should show your website!
This website runs over HTTP, but these days it's recommended to use HTTPS for your sites. Lets setup HTTPS for our website by using LetsEncrypt, a free service for SSL certificates.
First we have to install some supporting packages:
yum install certbot python2-certbot-nginx # not necessary
yum install python27-python-pip
scl enable python27 bash
pip install certbot
pip install setuptools --upgrade
pip install certbot-nginx
Once the supporting packages are there, we can run certbot to setup the SSL certificates for our domain:
certbot --nginx
After completion of the wizard, you should see something like below:
During the Certbot wizard which configures LetsEncrypt, it asks if you want to redirect all HTTP access to HTTPS and I would answer Yes here.
Now, regardless if you use HTTP or HTTPS on dgielis.com, you will always end-up with HTTPS.
HTTPS is better for Google, better for security, so no reason not to do it :)
If we want to use our Nginx web server as reverse proxy for our APEX environment we can do that by adapting our /etc/nginx/conf.d/dgielis.com.conf file (see the location sections):
vi /etc/nginx/conf.d/dgielis.com.conf
Add following to the server:
location /ords/ {
proxy_pass your_apex_url/ords/;
proxy_set_header Origin "" ;
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_connect_timeout 600;
proxy_send_timeout 600;
proxy_read_timeout 600;
send_timeout 600;
}
location /i/ {
proxy_pass your_apex_url/i/;
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
The final configuration file looks like this:
There's one other configuration change I would suggest you do straightaway; increase the size of the max_body_size in Nginx. Add following line to nginx.conf
vi /etc/nginx/nginx.conf
Test and reload the configuration of Nginx:
nginx -t
nginx -s reload
When going in a browser to https://dgielis.com/ords/ we arrive at Oracle APEX:
There's a lot of optimization we can do on the reverse proxy. To gain performance we can put the images folder of APEX on the Nginx server, as Nginx is super fast in transferring static files. We can add more redirects, for example, that apex.dgielis.com goes to the APEX builder and app.dgielis.com goes to one of our custom APEX apps. As this post is already long, I'm not including that in here.
Once Oracle provides vanity URLs, we don't need to do the above, and the URL will point directly to ATP.
Update 26-SEP-2019 (thanks Kris Rice): Note that setting the Origin would negate any CORS info that ORDS is enforcing. That could be a security issue for some people. Oracle is looking into the ability to have your ORDS running on your own Compute VM (the webserver we just setup), which would solve the issue. The vanity URLs would not have the CORS issue either.
In the next post, we will use this server to add an on-premises version of APEX Office Print (AOP), so we have everything we need to export data from the database in the format we want, for example in Excel and PDF.
Update 23-DEC-2019: If you are looking for other redirects see 17. Configure domain to redirect to APEX app
In this blog post we will configure a web server on our Compute VM Instance. This allows us to host some websites and have a custom URL for our Oracle APEX instance and applications.
Lets start with connecting to our VM:
From a Terminal connect to your Oracle Cloud VM:
ssh -i ssh_key opc@public_ip
The first thing we do, is change to the root user, as we want to install a web server it will be easier to do it with the root user. Alternatively in front of every command you can add sudo.
We logged in as the OPC user, to become the ROOT user we do:
sudo su
Although it doesn't really have anything to do with setting up a web server, I do want to share this... The first thing I always like to do on a machine, is get the system updated, so all latest software is being used. To do this, run following command:
yum update
It will take some time the first time, but after a couple of minutes you should see that all packages were updated:
So the purpose of this post was to install a web server so when we type in a certain domain, it will arrive at our machine. As web server, I typically chose between Apache and Nginx. Which one to choose is a hard one... if you search Google for "Apache vs Nginx" you can start reading ;) Since last year I started to use Nginx for all my new systems, before I always used Apache.
Following steps show how you install the Nginx web server and run it:
yum install nginx
Now we need to start the web server:
systemctl start nginx
To see if Nginx is successfully running, do:
systemctl status nginx
You should see something like:
The next thing we have to do is open the firewall on the Linux box, so incoming connections are allowed. The first line will open HTTP, the second HTTPS and then we reload the firewall:
firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload
Opening the firewall on the Linux box is not enough. Oracle added some extra security layers around the VM (Compute Instance). We have to allow HTTP and HTTPS access to our machine in the Oracle Firewall too.
Click the Virtual Cloud Network link:
Click the Security Links:
And add two Ingress Rules, one for HTTP and one for HTTPS:
As Source add 0.0.0.0/0 so everything can connect to it and as destination port you specify the first time 80 (for HTTP) and the second time 443 (for HTTPS):
Once both Ingress Rules are added, your list looks like this:
Now you can navigate in a browser to your Public IP and you should see:
Now that we have a web server running and it's accessible through the IP address, we know things are working. Most of the time however you don't want to access your server through an IP address, rather you want people to use a domain name. To access my Free Oracle Cloud server for example I want to use the dgielis.com domain.
The first step to do, is in the domain provider you specify for the A record, the IP address of your Oracle Cloud VM (Compute) Instance. I typically also setup some CNAME so any sub-domain will work too. For example I could point apex.dgielis.com to Oracle APEX Builder.
Now that the domain points to our VM, we have to make sure our web server listens to this domain and knows what to do. We will need to configure Nginx for this dgielis.com domain.
Here are the steps to do this (do this in your Terminal which is connected to your VM):
vi /etc/nginx/conf.d/dgielis.com.conf
# Add following to the file (change dgielis.com by your domain):
server {
listen 80;
listen [::]:80;
server_name dgielis.com www.dgielis.com;
root /usr/share/nginx/html/dgielis.com;
index index.html;
try_files $uri /index.html;
}
# Create a directory where your website resides:
mkdir /usr/share/nginx/html/dgielis.com
# Add an index.html file to the directory
# Quickest way is vi or cp an index.html in this folder
# Or develop your site locally first and when ready upload with scp
# scp -i .ssh/oraclecloud /Users/dgielis/site.zip opc@132.145.215.55:/tmp
# to test Nginx configuration
nginx -t
# to restart Nginx
nginx -s reload
# note: in case nginx doesn't restart, kill the nginx process and try to restart again
ps -ef | grep nginx
kill ~pid~
When you go in your browser to your domain name, it should show your website!
This website runs over HTTP, but these days it's recommended to use HTTPS for your sites. Lets setup HTTPS for our website by using LetsEncrypt, a free service for SSL certificates.
First we have to install some supporting packages:
yum install certbot python2-certbot-nginx # not necessary
yum install python27-python-pip
scl enable python27 bash
pip install certbot
pip install setuptools --upgrade
pip install certbot-nginx
Once the supporting packages are there, we can run certbot to setup the SSL certificates for our domain:
certbot --nginx
After completion of the wizard, you should see something like below:
During the Certbot wizard which configures LetsEncrypt, it asks if you want to redirect all HTTP access to HTTPS and I would answer Yes here.
Now, regardless if you use HTTP or HTTPS on dgielis.com, you will always end-up with HTTPS.
HTTPS is better for Google, better for security, so no reason not to do it :)
If we want to use our Nginx web server as reverse proxy for our APEX environment we can do that by adapting our /etc/nginx/conf.d/dgielis.com.conf file (see the location sections):
vi /etc/nginx/conf.d/dgielis.com.conf
Add following to the server:
location /ords/ {
proxy_pass your_apex_url/ords/;
proxy_set_header Origin "" ;
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_connect_timeout 600;
proxy_send_timeout 600;
proxy_read_timeout 600;
send_timeout 600;
}
location /i/ {
proxy_pass your_apex_url/i/;
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
The final configuration file looks like this:
There's one other configuration change I would suggest you do straightaway; increase the size of the max_body_size in Nginx. Add following line to nginx.conf
vi /etc/nginx/nginx.conf
Test and reload the configuration of Nginx:
nginx -t
nginx -s reload
When going in a browser to https://dgielis.com/ords/ we arrive at Oracle APEX:
There's a lot of optimization we can do on the reverse proxy. To gain performance we can put the images folder of APEX on the Nginx server, as Nginx is super fast in transferring static files. We can add more redirects, for example, that apex.dgielis.com goes to the APEX builder and app.dgielis.com goes to one of our custom APEX apps. As this post is already long, I'm not including that in here.
Once Oracle provides vanity URLs, we don't need to do the above, and the URL will point directly to ATP.
Update 26-SEP-2019 (thanks Kris Rice): Note that setting the Origin would negate any CORS info that ORDS is enforcing. That could be a security issue for some people. Oracle is looking into the ability to have your ORDS running on your own Compute VM (the webserver we just setup), which would solve the issue. The vanity URLs would not have the CORS issue either.
In the next post, we will use this server to add an on-premises version of APEX Office Print (AOP), so we have everything we need to export data from the database in the format we want, for example in Excel and PDF.
Update 23-DEC-2019: If you are looking for other redirects see 17. Configure domain to redirect to APEX app
Print PDF from Oracle APEX to Local or Remote Printer
On Twitter, I got a lot of reactions on my previous blog post Printing to local (Zebra) printer from Oracle APEX in the Cloud.
As more people asked the question of how to print to a local printer from Oracle APEX, I will cover 3 options in this post. All options make use of some features of APEX Office Print (AOP).
When you install the AOP Sample Application, you find the examples under the following sections:
1. Call Print Window of the browser directly from a button
Create a button on the page e.g. PDF and add a Dynamic Action to it. The Dynamic Action calls some JavaScript:
This JavaScript calls following AJAX Process, which uses the AOP PL/SQL API to generate a PDF:
When the PDF is produced, the printJS JavaScript function is being called, which opens the browser's print window. The end-user can change the Printer and hit the Print button.
So this option is not a direct print to a predefined printer, but a two-step process: open print window, hit print.
2. Print directly to an IP Printer
One of APEX Office Print's unique features is that the AOP Server can send a document directly to the IP address of a printer. AOP is really smart and if it sees the printer doesn't support native PDF or the document type we send, it will convert to PDF or Postscript, which all printers support.
The only thing you have to do, in order to print directly to an IP Printer is to add some global variables of AOP. In the below example it will send a PDF to the IP Printer on the same server AOP is running but on port 3000.
3. Use of Post-Process command
You can configure the AOP Server with some post-processing commands, which allow you to run some command-line tools before and after the AOP Server is creating the output. This is a really powerful feature and one use case is to send the document directly to a local (or network or remote) printer.
Just like with the IP Printer, you define the post-process command in some global variables of AOP.
This technique I also described in the previous blog post where we printed to a local Zebra printer. The only special thing in that post was that we used ngroc to make AOP known to the Oracle Cloud.
When you have the AOP Server running on-premises, you don't have that step as your APEX app will call the on-premises AOP Server. The only thing you need to make sure is that AOP Server knows the printer you configured in the post-process command.
There you go, three options to print from your Oracle APEX app directly to a local or remote printer using APEX Office Print.
As more people asked the question of how to print to a local printer from Oracle APEX, I will cover 3 options in this post. All options make use of some features of APEX Office Print (AOP).
When you install the AOP Sample Application, you find the examples under the following sections:
1. Call Print Window of the browser directly from a button
Create a button on the page e.g. PDF and add a Dynamic Action to it. The Dynamic Action calls some JavaScript:
This JavaScript calls following AJAX Process, which uses the AOP PL/SQL API to generate a PDF:
When the PDF is produced, the printJS JavaScript function is being called, which opens the browser's print window. The end-user can change the Printer and hit the Print button.
So this option is not a direct print to a predefined printer, but a two-step process: open print window, hit print.
2. Print directly to an IP Printer
One of APEX Office Print's unique features is that the AOP Server can send a document directly to the IP address of a printer. AOP is really smart and if it sees the printer doesn't support native PDF or the document type we send, it will convert to PDF or Postscript, which all printers support.
The only thing you have to do, in order to print directly to an IP Printer is to add some global variables of AOP. In the below example it will send a PDF to the IP Printer on the same server AOP is running but on port 3000.
3. Use of Post-Process command
You can configure the AOP Server with some post-processing commands, which allow you to run some command-line tools before and after the AOP Server is creating the output. This is a really powerful feature and one use case is to send the document directly to a local (or network or remote) printer.
Just like with the IP Printer, you define the post-process command in some global variables of AOP.
This technique I also described in the previous blog post where we printed to a local Zebra printer. The only special thing in that post was that we used ngroc to make AOP known to the Oracle Cloud.
When you have the AOP Server running on-premises, you don't have that step as your APEX app will call the on-premises AOP Server. The only thing you need to make sure is that AOP Server knows the printer you configured in the post-process command.
There you go, three options to print from your Oracle APEX app directly to a local or remote printer using APEX Office Print.
Best and Cheapest Oracle APEX hosting: Free Oracle Cloud
I really have to write about the Oracle Cloud Free Tier as it's the third time in my career I've been blown away by Oracle.
The first time was when I first saw and worked with the Oracle Database, the second time when I saw HTMLDB (now APEX) and today, again, with the announcement of the Free Autonomous Oracle Cloud.
The Free Oracle Tier is a hardcore smackdown on any other cloud offering which includes a virtual machine, database, and development environment!
The Free Oracle Cloud was announced by Andy Mendelsohn at Oracle Open World 2019.
You get a free Oracle Autonomous Database, 2 Virtual Machines (Compute), Storage and other services:
There's nothing like this Oracle offering on the market. The best database in the world, running on amazing hardware and all of this for free. This is not just for APEX Developers, this is for anybody who wants a datastore or virtual machine in the cloud without wanting to manage things themselves.
Jon, from JMJ Cloud, wrote RIP Exadata Express, where do I run my RAD stack now?, the answer today for me is in the Free Oracle Autonomous Oracle Cloud. For many of my customers, I also used the Exadata Express service before, as it was ideal to start developing new Oracle APEX apps. The Free Oracle Autonomous Database Cloud is a better version of what Exadata Express has ever been. I would always start my development in this new free Oracle Cloud offering as it allows you to scale up while you grow.
I really believe everybody should sign-up for this service. As with every new service, the beginning might have some hiccups, but to me, it's worth starting with this service straight away.
I signed up for the Free Oracle Cloud in multiple regions, and love it so far.
So I thought to write a series of blog posts on how you can get started:
1. Sign-up for Free Oracle Cloud
2. Setup Autonomous Transaction Cloud (ATP)
3. Connecting with SQL Developer (Desktop) to ATP
4. Connecting with SQL Developer Web to ATP
5. Setup APEX in ATP and create the first APEX app
6. Create a VM Instance (Compute Cloud)
7. Setup a Webserver on the Compute Instance (VM)
8. Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
9. Setup Object Storage and use for File Share and Backups
10. Running SQLcl and Datapump from the Compute Instance (VM) to ATP
11. Sending Emails with APEX_MAIL on ATP
12. Create a 2nd Compute Instance and a Load Balancer
13. Final things to take away
14. Message: Your Oracle Cloud Free Trial has expired (but FREE keeps running)
15. Error: The request could not be mapped to any database
16. Renewing Let's Encrypt certificate
17. Configure domain to redirect to APEX app
18. Monitoring your website and APEX app
The first time was when I first saw and worked with the Oracle Database, the second time when I saw HTMLDB (now APEX) and today, again, with the announcement of the Free Autonomous Oracle Cloud.
The Free Oracle Tier is a hardcore smackdown on any other cloud offering which includes a virtual machine, database, and development environment!
The Free Oracle Cloud was announced by Andy Mendelsohn at Oracle Open World 2019.
You get a free Oracle Autonomous Database, 2 Virtual Machines (Compute), Storage and other services:
There's nothing like this Oracle offering on the market. The best database in the world, running on amazing hardware and all of this for free. This is not just for APEX Developers, this is for anybody who wants a datastore or virtual machine in the cloud without wanting to manage things themselves.
Jon, from JMJ Cloud, wrote RIP Exadata Express, where do I run my RAD stack now?, the answer today for me is in the Free Oracle Autonomous Oracle Cloud. For many of my customers, I also used the Exadata Express service before, as it was ideal to start developing new Oracle APEX apps. The Free Oracle Autonomous Database Cloud is a better version of what Exadata Express has ever been. I would always start my development in this new free Oracle Cloud offering as it allows you to scale up while you grow.
I really believe everybody should sign-up for this service. As with every new service, the beginning might have some hiccups, but to me, it's worth starting with this service straight away.
I signed up for the Free Oracle Cloud in multiple regions, and love it so far.
So I thought to write a series of blog posts on how you can get started:
1. Sign-up for Free Oracle Cloud
2. Setup Autonomous Transaction Cloud (ATP)
3. Connecting with SQL Developer (Desktop) to ATP
4. Connecting with SQL Developer Web to ATP
5. Setup APEX in ATP and create the first APEX app
6. Create a VM Instance (Compute Cloud)
7. Setup a Webserver on the Compute Instance (VM)
8. Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
9. Setup Object Storage and use for File Share and Backups
10. Running SQLcl and Datapump from the Compute Instance (VM) to ATP
11. Sending Emails with APEX_MAIL on ATP
12. Create a 2nd Compute Instance and a Load Balancer
13. Final things to take away
14. Message: Your Oracle Cloud Free Trial has expired (but FREE keeps running)
15. Error: The request could not be mapped to any database
16. Renewing Let's Encrypt certificate
17. Configure domain to redirect to APEX app
18. Monitoring your website and APEX app
Important update of ORDS... release 19.4
A few days ago Oracle released Oracle REST Data Services (ORDS) 19.4.
In my opinion, it's a major release and most likely worth investigating to upgrade for everybody. For me, the following 3 improvements are worth the upgrade:
Performance of REST APIs
The performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. You can read more about this in the readme.
We have a customer hitting ORDS through a mobile app really hard (potentially 130 000 end-users), so any improvements in this area are awesome for those types of customers.
Removal of PDF Generation Support
As previously advised in the ORDS 18.4.0 Release Notes, the Apache FOP based functionality to produce PDF Reports from Oracle Application Express (APEX) has been removed in this release. This means that if you still want to print or export files in Oracle APEX, you most likely want to look at using APEX Office Print (AOP). AOP is the most integrated printing and exporting solution for Oracle Application Express and the defacto standard these days. It comes with an AOP Report which is similar to what ORDS provided to APEX: based on the print attributes it generates a PDF. But, AOP gives you tons more features and flexibility when you want to print and export from APEX!
When you install the AOP Plug-in, choose your own template, for example, and look at the different Data Types that are available.
SQL Developer Web
This release sees the introduction of Oracle SQL Developer Web, an ORDS hosted web application giving Oracle Database users an interface for executing queries and scripts, creating and altering database objects, building data models, accessing Performance Hub, and viewing database activity.
After setting the following properties in default.xml:
You can access SQL Developer Web through:
http(s)://your_server:your_port/ords/sql-developer
You log in with a database user and password who is REST enabled. In this script I REST enable the user DIMI:
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'DIMI',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'dimi',
p_auto_rest_auth => FALSE);
commit;
END;
/
This is what SQL Developer Web looks like, a browser-based version of SQL Developer (desktop) and a better version of what you find in SQL Workshop in APEX (although not all features are in yet, for example, I miss editing of a record):
SQL Developer Web includes another jewel... a Database Dashboard and Activity Monitoring!
When you log in with a user with the PDB_DBA role, you get a whole new section:
You find also more information in the ORDS 19.4 documentation.
Jeff Smith wrote a nice blog post about how to get started with SQL Developer Web too.
Really nice release!
In my opinion, it's a major release and most likely worth investigating to upgrade for everybody. For me, the following 3 improvements are worth the upgrade:
Performance of REST APIs
The performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. You can read more about this in the readme.
We have a customer hitting ORDS through a mobile app really hard (potentially 130 000 end-users), so any improvements in this area are awesome for those types of customers.
Removal of PDF Generation Support
As previously advised in the ORDS 18.4.0 Release Notes, the Apache FOP based functionality to produce PDF Reports from Oracle Application Express (APEX) has been removed in this release. This means that if you still want to print or export files in Oracle APEX, you most likely want to look at using APEX Office Print (AOP). AOP is the most integrated printing and exporting solution for Oracle Application Express and the defacto standard these days. It comes with an AOP Report which is similar to what ORDS provided to APEX: based on the print attributes it generates a PDF. But, AOP gives you tons more features and flexibility when you want to print and export from APEX!
When you install the AOP Plug-in, choose your own template, for example, and look at the different Data Types that are available.
SQL Developer Web
This release sees the introduction of Oracle SQL Developer Web, an ORDS hosted web application giving Oracle Database users an interface for executing queries and scripts, creating and altering database objects, building data models, accessing Performance Hub, and viewing database activity.
After setting the following properties in default.xml:
You can access SQL Developer Web through:
http(s)://your_server:your_port/ords/sql-developer
You log in with a database user and password who is REST enabled. In this script I REST enable the user DIMI:
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'DIMI',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'dimi',
p_auto_rest_auth => FALSE);
commit;
END;
/
This is what SQL Developer Web looks like, a browser-based version of SQL Developer (desktop) and a better version of what you find in SQL Workshop in APEX (although not all features are in yet, for example, I miss editing of a record):
SQL Developer Web includes another jewel... a Database Dashboard and Activity Monitoring!
When you log in with a user with the PDB_DBA role, you get a whole new section:
You find also more information in the ORDS 19.4 documentation.
Jeff Smith wrote a nice blog post about how to get started with SQL Developer Web too.
Really nice release!
Merry Christmas and Happy New Year !! (“Heroes”)
I would like to take this opportunity to wish all my readers a very Merry Christmas and a most happy, peaceful and prosperous New Year. My gift this year is not David Bowie and Bing Crosby doing their famous Christmas duet but a performance by Bowie of his classic “Heroes” as featured on the same […]
Free Oracle Cloud: 18. Monitoring your website and APEX app
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
As more and more people are putting their production apps on the Always Free Oracle Cloud, it's probably a good idea to start monitoring your instance. (but remember this is a no-SLA environment)
Over the last few weeks, it's happened to me - my EU test database was stopped and one of my compute instances was accidentally dropped and restored. Oracle notified me and took action themselves:
If I had monitoring on, I would have seen this myself. The above case I couldn't solve on my own, but it could happen that some software you are running goes down, or something else makes your site/app unavailable due to your own code. If that happens you want to take action and verify if things are ok.
There are many monitoring tools out there, some are paid, some are free. I've used Pingdom and UptimeRobot. It takes only a minute to set it up. I'll show how to do it with UptimeRobot.
Sign-up at uptimerobot.com
Click the New Monitor button and specify the URL you want to monitor.Add a type of connection where you want to be notified and you are done!
You can simulate what happens if you go into your compute instance and stop the webserver.
nginx -s stop
Depending on the monitoring interval you will get an email within 5 minutes when your URL can't be reached.
You can also log in and see in the dashboard how much uptime you had and when you were down:
You can restart your web server by doing:
systemctl restart nginx
A few minutes later UptimeRobot will notify you all is good again :)
And surely in the dashboard, we are UP again:
Happy monitoring!
As more and more people are putting their production apps on the Always Free Oracle Cloud, it's probably a good idea to start monitoring your instance. (but remember this is a no-SLA environment)
Over the last few weeks, it's happened to me - my EU test database was stopped and one of my compute instances was accidentally dropped and restored. Oracle notified me and took action themselves:
If I had monitoring on, I would have seen this myself. The above case I couldn't solve on my own, but it could happen that some software you are running goes down, or something else makes your site/app unavailable due to your own code. If that happens you want to take action and verify if things are ok.
There are many monitoring tools out there, some are paid, some are free. I've used Pingdom and UptimeRobot. It takes only a minute to set it up. I'll show how to do it with UptimeRobot.
Sign-up at uptimerobot.com
Click the New Monitor button and specify the URL you want to monitor.Add a type of connection where you want to be notified and you are done!
You can simulate what happens if you go into your compute instance and stop the webserver.
nginx -s stop
Depending on the monitoring interval you will get an email within 5 minutes when your URL can't be reached.
You can also log in and see in the dashboard how much uptime you had and when you were down:
You can restart your web server by doing:
systemctl restart nginx
A few minutes later UptimeRobot will notify you all is good again :)
And surely in the dashboard, we are UP again:
Happy monitoring!
Free Oracle Cloud: 17. Configure domain to redirect to APEX app
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
In a previous blog post, we configured the webserver on our Compute VM Instance. We added a website and configured the domain dgielis.com to point to this instance.
I got many requests on how to point the domain name to a specific Oracle APEX app. This is what I will cover in this blog post.
Lets start with connecting to our VM:
From a Terminal connect to your Oracle Cloud VM:
ssh -i ssh_key opc@public_ip
The first thing we do, is changing to the root user, as we want to configure the web server. Alternatively in front of every command you can add sudo.
We logged in as the OPC user, to become the ROOT user we do:
sudo su
# open the configuration file we created in the previous blog post
vi /etc/nginx/conf.d/dgielis.com.conf
# add following
location / {
rewrite ^/$ /ords/f?p=101:LOGIN_DESKTOP:0 permanent;
}
# save and quit (:wq)
My config file looks now like this:
# to test Nginx configuration
nginx -t
# to restart Nginx
nginx -s reload
That's it.
In a previous blog post, we configured the webserver on our Compute VM Instance. We added a website and configured the domain dgielis.com to point to this instance.
I got many requests on how to point the domain name to a specific Oracle APEX app. This is what I will cover in this blog post.
Lets start with connecting to our VM:
From a Terminal connect to your Oracle Cloud VM:
ssh -i ssh_key opc@public_ip
The first thing we do, is changing to the root user, as we want to configure the web server. Alternatively in front of every command you can add sudo.
We logged in as the OPC user, to become the ROOT user we do:
sudo su
# open the configuration file we created in the previous blog post
vi /etc/nginx/conf.d/dgielis.com.conf
# add following
location / {
rewrite ^/$ /ords/f?p=101:LOGIN_DESKTOP:0 permanent;
}
# save and quit (:wq)
My config file looks now like this:
# to test Nginx configuration
nginx -t
# to restart Nginx
nginx -s reload
That's it.
London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars
Places are filling up, but there are still some available at both of my acclaimed seminars that I’ll be running in London, UK in March 2020. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]
Oracle Database 19c Automatic Indexing – Indexed Column Reorder (What Shall We Do Now?)
I previously discussed how the default column order of an Automatic Index (in the absence of other factors) is based on the Column ID, the order in which the columns are defined in the table. But what if there are “other factors” based on new workloads and the original index column order is no […]
The best way to be productive with APEX Office Print (AOP)
We often get the question of "how to be the most productive creating a template for APEX Office Print (AOP)".
Here are two examples of people asking in different ways:
My short answer: I recommend to use the Fast Template Switcher in our AOP Sample App or when you use the on-premises version of AOP, connect to the server and use the AOP Web Editor.
In this blog post, I will do a step-by-step guide on how I believe you will be most performant building your templates and using them in AOP.
If you didn't install APEX Office Print (AOP) yet, please read my blog post Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
Now, we will add AOP to a button on a page in an APEX app.
Dynamic Action Plug-in
When you have a button on the page, right-click on it and choose "Create Dynamic Action".
Give it a name and as Action, specify APEX Office Print (AOP) - DA [Plug-in]
Data
The most important part of any report is the data it contains. So, the first step is to define where AOP can find all the data you want to use in the report. AOP gives you the ability to get all your data through a hierarchical SQL statement by using the cursor() or JSON syntax. For example, if we want to query all the orders and order lines (details) of a given customer we can specify in the Data Source:
select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor(
select
o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor(
select
p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price",
APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image",
40 as "image_max_width"
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from demo_orders o
where c.customer_id = o.customer_id
) "orders"
from demo_customers c
where customer_id = :P1_CUSTOMER_ID
) as "data"
from dual
In case your data is more complex than you can define in a hierarchical SQL statement, you can also specify a PL/SQL Function or a JSON data source.
AOP Template
Now you want this data in a specific look and feel. AOP allows you to specify your own template written in Word, Excel, Powerpoint, HTML, and Text. A good way to start your template is by using the AOP Template (which is selected by default). This means you don't specify your own template yet, but let AOP generate a starting template for you, based on the data you provided. AOP can generate a starter template in Word, Excel, and HTML.
Based on your data, it will generate a {tag} for every column you have. When AOP sees there are multiple records (a table), it will generate a loop statement {#tag}{/tag} as well.
Now you can iterate over your template to make it exactly as you want it to look like. Just rearrange the {tags} to the specific positions where you want your data to be. Apart from that, you are in Word (or Excel, Powerpoint, ...) and have access to all of the native Word features to create a gorgeous look and feel. If you already have a document, you can also copy and paste the tags from the AOP Template into your existing document.
What I see, is that people adjust the template, upload the template, adjust, upload, adjust, upload over and over. There's actually a much faster way to iterate through template development.
Local Debug
Behind the scenes, the AOP Plug-in generates a JSON file that is being sent to the AOP Server.
To get this JSON, you can go into Shared Components > Component Settings > APEX Office Print and set Debug to Local. This will enable AOP Debug for the entire application. If you just want to put AOP Debug on for the current button, add the following to the Init PL/SQL Code of the AOP Dynamic Action:
aop_api_pkg.g_debug := aop_api_pkg.c_debug_local;
This is what you should see in the Dynamic Action:
When you click the button, you will get the JSON file.
Quickly changing Templates
With AOP we ship an AOP Sample App. This is an APEX application which showcases many features of APEX Office Print:
In the Debugging section, you find a Quick Template Changer link:
The Quick Template Changer allows you to quickly try new templates based on an AOP JSON file.
You drag the JSON file in the first box under Exported JSON.
The JSON will be parsed and the content of the JSON is shown in the JSON Data field.
Next, you drag your template in the Template section and finally, you select the Output you want and hit the Process button.
You can now remove the template and drag-and-drop a new version of the template and hit the Process button again. This way you can quickly see the result while making changes to the template.
Web Editor in AOP On-Premises version
If you download the AOP On-Premises version and run the AOP Server (which is one executable) locally, or you navigate to the server URL where your AOP Server is running, you will see the AOP Web Editor.
This editor is really powerful and has more features than the Quick Template Changer you find in the AOP Sample App. Just as before you drag-and-drop the JSON file in the Exported JSON section.
The AOP Web Editor will parse the JSON and will show a link to the template to Download (unnamed.docx). If you want to change the Template, you just drag-and-drop another template in the Template section. On this screen, you can even prepend and append files and add sub-templates. If you don't like to always select a file from the file system, you can select all files or even a directory and drag-and-drop it entirely in the File cache section, so you can swap files even quicker!
Select the Output and hit the Process button, and presto, you see the output with the (new) template!
But that is not all, you can also change the Data on the fly by going into the Data tab:
Or see the new resulting JSON file after changing the data and template:
In case you want some examples, hit the Load Sample button and select a sample.
I really believe the AOP Web Editor will help you a lot in your development of AOP Reports.
This AOP Web Editor app is actually a nice showcase that AOP can be used with any technology. The Web Editor is written with React.js.
Hope this helps you to be even more productive with AOP!
Here are two examples of people asking in different ways:
My short answer: I recommend to use the Fast Template Switcher in our AOP Sample App or when you use the on-premises version of AOP, connect to the server and use the AOP Web Editor.
In this blog post, I will do a step-by-step guide on how I believe you will be most performant building your templates and using them in AOP.
If you didn't install APEX Office Print (AOP) yet, please read my blog post Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
Now, we will add AOP to a button on a page in an APEX app.
Dynamic Action Plug-in
When you have a button on the page, right-click on it and choose "Create Dynamic Action".
Give it a name and as Action, specify APEX Office Print (AOP) - DA [Plug-in]
Data
The most important part of any report is the data it contains. So, the first step is to define where AOP can find all the data you want to use in the report. AOP gives you the ability to get all your data through a hierarchical SQL statement by using the cursor() or JSON syntax. For example, if we want to query all the orders and order lines (details) of a given customer we can specify in the Data Source:
select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor(
select
o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor(
select
p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price",
APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image",
40 as "image_max_width"
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from demo_orders o
where c.customer_id = o.customer_id
) "orders"
from demo_customers c
where customer_id = :P1_CUSTOMER_ID
) as "data"
from dual
In case your data is more complex than you can define in a hierarchical SQL statement, you can also specify a PL/SQL Function or a JSON data source.
AOP Template
Now you want this data in a specific look and feel. AOP allows you to specify your own template written in Word, Excel, Powerpoint, HTML, and Text. A good way to start your template is by using the AOP Template (which is selected by default). This means you don't specify your own template yet, but let AOP generate a starting template for you, based on the data you provided. AOP can generate a starter template in Word, Excel, and HTML.
Based on your data, it will generate a {tag} for every column you have. When AOP sees there are multiple records (a table), it will generate a loop statement {#tag}{/tag} as well.
Now you can iterate over your template to make it exactly as you want it to look like. Just rearrange the {tags} to the specific positions where you want your data to be. Apart from that, you are in Word (or Excel, Powerpoint, ...) and have access to all of the native Word features to create a gorgeous look and feel. If you already have a document, you can also copy and paste the tags from the AOP Template into your existing document.
What I see, is that people adjust the template, upload the template, adjust, upload, adjust, upload over and over. There's actually a much faster way to iterate through template development.
Local Debug
Behind the scenes, the AOP Plug-in generates a JSON file that is being sent to the AOP Server.
To get this JSON, you can go into Shared Components > Component Settings > APEX Office Print and set Debug to Local. This will enable AOP Debug for the entire application. If you just want to put AOP Debug on for the current button, add the following to the Init PL/SQL Code of the AOP Dynamic Action:
aop_api_pkg.g_debug := aop_api_pkg.c_debug_local;
This is what you should see in the Dynamic Action:
When you click the button, you will get the JSON file.
Quickly changing Templates
With AOP we ship an AOP Sample App. This is an APEX application which showcases many features of APEX Office Print:
In the Debugging section, you find a Quick Template Changer link:
The Quick Template Changer allows you to quickly try new templates based on an AOP JSON file.
You drag the JSON file in the first box under Exported JSON.
The JSON will be parsed and the content of the JSON is shown in the JSON Data field.
Next, you drag your template in the Template section and finally, you select the Output you want and hit the Process button.
You can now remove the template and drag-and-drop a new version of the template and hit the Process button again. This way you can quickly see the result while making changes to the template.
Web Editor in AOP On-Premises version
If you download the AOP On-Premises version and run the AOP Server (which is one executable) locally, or you navigate to the server URL where your AOP Server is running, you will see the AOP Web Editor.
This editor is really powerful and has more features than the Quick Template Changer you find in the AOP Sample App. Just as before you drag-and-drop the JSON file in the Exported JSON section.
The AOP Web Editor will parse the JSON and will show a link to the template to Download (unnamed.docx). If you want to change the Template, you just drag-and-drop another template in the Template section. On this screen, you can even prepend and append files and add sub-templates. If you don't like to always select a file from the file system, you can select all files or even a directory and drag-and-drop it entirely in the File cache section, so you can swap files even quicker!
Select the Output and hit the Process button, and presto, you see the output with the (new) template!
But that is not all, you can also change the Data on the fly by going into the Data tab:
Or see the new resulting JSON file after changing the data and template:
In case you want some examples, hit the Load Sample button and select a sample.
I really believe the AOP Web Editor will help you a lot in your development of AOP Reports.
This AOP Web Editor app is actually a nice showcase that AOP can be used with any technology. The Web Editor is written with React.js.
Hope this helps you to be even more productive with AOP!
Alternative for Oracle Multimedia: APEX Media Extension
If you are reading this blog post you are probably searching for an alternative for the deprecated multimedia (or intermedia) feature of the Oracle Database... and you are in the right post as APEX Media Extension is that replacement!
Just like you, I loved the Oracle Multimedia feature in the Oracle Database. I used the feature in many different applications, mostly related to images. For example, when some students upload images of their work in an Oracle APEX app, or teachers upload images to include in tests, I made thumbnails of the images and resized them so they fit really nicely on the page.
Unfortunately, Oracle announced starting in Oracle Database 18c, Oracle Multimedia is deprecated. You can still use it, but you know it will go away at some point... and that is what happened with Oracle Database 19c. Oracle Multimedia is now desupported and doesn't even work anymore. The Oracle Multimedia packages are still there, so your code is still compiling and valid, but it's not doing anything anymore.
Just like you and others, I reviewed and tested different alternatives, but to make a long story short, I wasn't completely happy with any of them. So we at APEX R&D decided to offer a solution that is easy to use and fully supported. There's a PL/SQL API so you can use it just like Oracle Multimedia from the Oracle Database. As we love Oracle APEX, we also provide two APEX Plug-ins: one plug-in which sits on top of the PL/SQL API (which is a server-based solution) and another one which is a pure client-side (JavaScript) implementation. Our goal is that it's as easy as possible to integrate this solution into your Oracle Database and/or APEX app. We named this solution APEX Media Extension and it will be available before the end of the year (2019).
In this initial release, we've focussed on converting media, such as:
BUT that is not all... we decided to include this functionality also in APEX Office Print (AOP) (Gold and Enterprise versions)! So, if you already use AOP, in our upcoming version you are fully covered already :)
Interested? Please leave your email on the APEX Media Extension website and you will be first to know when it's released.
Just like you, I loved the Oracle Multimedia feature in the Oracle Database. I used the feature in many different applications, mostly related to images. For example, when some students upload images of their work in an Oracle APEX app, or teachers upload images to include in tests, I made thumbnails of the images and resized them so they fit really nicely on the page.
Unfortunately, Oracle announced starting in Oracle Database 18c, Oracle Multimedia is deprecated. You can still use it, but you know it will go away at some point... and that is what happened with Oracle Database 19c. Oracle Multimedia is now desupported and doesn't even work anymore. The Oracle Multimedia packages are still there, so your code is still compiling and valid, but it's not doing anything anymore.
Just like you and others, I reviewed and tested different alternatives, but to make a long story short, I wasn't completely happy with any of them. So we at APEX R&D decided to offer a solution that is easy to use and fully supported. There's a PL/SQL API so you can use it just like Oracle Multimedia from the Oracle Database. As we love Oracle APEX, we also provide two APEX Plug-ins: one plug-in which sits on top of the PL/SQL API (which is a server-based solution) and another one which is a pure client-side (JavaScript) implementation. Our goal is that it's as easy as possible to integrate this solution into your Oracle Database and/or APEX app. We named this solution APEX Media Extension and it will be available before the end of the year (2019).
In this initial release, we've focussed on converting media, such as:
- resizing images
- cropping images
- adding watermarks to images
- compressing images
- changing image formats (jpg, png, ...)
BUT that is not all... we decided to include this functionality also in APEX Office Print (AOP) (Gold and Enterprise versions)! So, if you already use AOP, in our upcoming version you are fully covered already :)
Interested? Please leave your email on the APEX Media Extension website and you will be first to know when it's released.
Free Oracle Cloud: 16. Renewing Let's Encrypt certificate
When you followed along with my series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud you most likely will have gotten an email from Let's Encrypt that your certificate is due for renewal.
To check your certificate, go to your site in a browser and click on the lock:
Yep, corresponds to the email, in 10 days my certificate will expire.
Let's get this fixed! So connect with ssh to your Compute instance (see the previous post in the series if you forgot those commands).
When I connect to my machines I typically first run yum update to get the latest packages installed so we are current with security patches, or just, in general, keep up with the latest software.
sudo su (to become root)
yum update
Time to renew our certificate. It's very easy to do, run
certbot certonly
Ha! Apparently not so simple after all?! We got an error. This might happen when packages are incompatible. Normally yum should take care of that, but as we installed Certbot with Pip, let's upgrade all those components too.
pip install -U pip
As the above error indicates an issue with cryptography, I will update that too.
pip install cryptography --upgrade
Now, let's try to renew our certificate again:
certbot certonly
and type your domain name(s):
Cool, that worked... your certificate is now updated.
To get the new certificate active we restart the webserver (after testing if all is ok):
nginx -t
nginx -s reload
Finally, we check the certificate in a browser to see if the new one is there:
All done, time to relax again for a couple of months.
To check your certificate, go to your site in a browser and click on the lock:
Yep, corresponds to the email, in 10 days my certificate will expire.
Let's get this fixed! So connect with ssh to your Compute instance (see the previous post in the series if you forgot those commands).
When I connect to my machines I typically first run yum update to get the latest packages installed so we are current with security patches, or just, in general, keep up with the latest software.
sudo su (to become root)
yum update
Time to renew our certificate. It's very easy to do, run
certbot certonly
Ha! Apparently not so simple after all?! We got an error. This might happen when packages are incompatible. Normally yum should take care of that, but as we installed Certbot with Pip, let's upgrade all those components too.
pip install -U pip
As the above error indicates an issue with cryptography, I will update that too.
pip install cryptography --upgrade
Now, let's try to renew our certificate again:
certbot certonly
and type your domain name(s):
Cool, that worked... your certificate is now updated.
To get the new certificate active we restart the webserver (after testing if all is ok):
nginx -t
nginx -s reload
Finally, we check the certificate in a browser to see if the new one is there:
All done, time to relax again for a couple of months.
Oracle Database 19c Automatic Indexing: Index Compression (Ghosteen)
In my previous post on Automatic Indexing, I discussed how the default index column order (in absence of other factors) is column id, the order in which the columns are defined in the table. In this post, I’ll explore if this changes if index compression is also implemented. By default, Automatic Indexing does […]
Free Oracle Cloud: 15. The request could not be mapped to any database
This post is the last post of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
At some point you might face the following message: "The request could not be mapped to any database":
Oracle is monitoring usage on your Always Free Account and whenever it finds there's no activity for 7 days, it will stop your database automatically. It will preserve the data in the database, but it won't be accessible anymore.
To fix the issue, log in to your Oracle Cloud Account and go to your Autonomous Database:
You will see the database is in a stopped state. Click the Start button:
The state will change to Starting...
And after a minute it becomes available again:
The above behavior is written in the end-user documentation:
Inactivity Monitoring and Database StoppagePersistently inactive Always Free Autonomous Databases are detected and handled as follows:
But this week there were some people complaining that although they had activity, their database was stopped anyway. I witnessed the same behavior in my account, so I reached out to Oracle and they confirmed their code to identify inactivity, is not properly accounting for APEX/ORDS usage. They are already working on a fix, which they hope to apply very soon. I will update this post when I get confirmation the fix is in the data centers.
At some point you might face the following message: "The request could not be mapped to any database":
Oracle is monitoring usage on your Always Free Account and whenever it finds there's no activity for 7 days, it will stop your database automatically. It will preserve the data in the database, but it won't be accessible anymore.
To fix the issue, log in to your Oracle Cloud Account and go to your Autonomous Database:
You will see the database is in a stopped state. Click the Start button:
The state will change to Starting...
And after a minute it becomes available again:
The above behavior is written in the end-user documentation:
Inactivity Monitoring and Database StoppagePersistently inactive Always Free Autonomous Databases are detected and handled as follows:
- After being inactive for 7 days, the database will be stopped automatically, preserving its stored data. Inactivity measurements leading up to 7 days are based on database connections. Successfully making a SQL*Net or HTTPS connection resets these measurements to zero.
- A database that is automatically or manually stopped and stays inactive for 90 days, cumulative, may be reclaimed and permanently deleted. Inactivity measurements leading up to 90 days are based on the database being inactive or in the stopped state. Starting a stopped database resets these measurements to zero.Start an Always Free Autonomous Database by clicking the Start button on the Oracle Cloud Infrastructure console. Start a stopped Always Free Autonomous Database before 90 days to avoid losing access to its data.
But this week there were some people complaining that although they had activity, their database was stopped anyway. I witnessed the same behavior in my account, so I reached out to Oracle and they confirmed their code to identify inactivity, is not properly accounting for APEX/ORDS usage. They are already working on a fix, which they hope to apply very soon. I will update this post when I get confirmation the fix is in the data centers.
Presenting at UKOUG Techfest19 Conference in Brighton, UK
I’m very excited to be attending my 3rd UKOUG Conference, this year re-badged as Techfest19. The fact it’s being held in Brighton is a little disconcerting for a Crystal Palace fan, but really looking forward nonetheless to what has always been one of the very best Oracle conferences on the yearly calendar. I have a […]
Announcement: Australia/NZ “Let’s Talk Database” Events October 2019 !!
I’ve very excited to announce the next series of Oracle “Let’s Talk Database” events to be run throughout Australia and New Zealand in October 2019. I’ll be discussing two exciting topics this series, “Oracle Database 19c New Features” and “Oracle Exadata X8“. As always, these sessions run between 9am-1pm, include a networking lunch and are free, […]
Oracle Database 19c Automatic Indexing: Default Index Column Order Part II (Future Legend)
In Part I, we explored some options that Oracle might adopt when ordering the columns within an Automatic Index by default, in the absence of other factors where there is only the one SQL statement to be concerned with. A point worth making is that if all columns of an index are specified within SQL […]
London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars !!
It’s with great excitement that I announce I’ll finally be returning to London, UK in March 2020 to run both of my highly acclaimed seminars. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]
Oracle Database 19c Automatic Indexing: Default Index Column Order Part I (Anyway Anyhow Anywhere)
The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column […]
Pages
