Feed aggregator

  After much badgering from a number of you (you know who you all are), I’m pleased to finally announce the scheduling of 2 new webinars for February 2021 !! As usual, places are very strictly limited as I only run small classes to give every attendee the opportunity to get the most from the […]

In the last month, the three major components of an Oracle APEX environment got new versions:- on October 21, 2020, Oracle Application Express (APEX) 20.2 - on October 29, 2020, Oracle REST Data Services (ORDS) 20.3 - on November 15, 2020, APEX Office Print (AOP) 20.3 
In general, the releases are as follows (based on findings from 2018 onwards):- APEX has two releases a year, around March/April and one around September/October.- ORDS brings out one release per quarter, but from time to time they might skip a quarter.- AOP has three major releases per year and some smaller releases in between.
Do we upgrade our production environment with every new release? - APEX: yes, we upgrade typically once the patchset bundle is available, so every 6 months- ORDS: no, we upgrade ORDS once a year, unless we need a specific feature- AOP: yes, at least the major releases are followed for the on-premises AOP release. AOP Cloud always has the latest release by default.
When I look at our customers, many don't upgrade that fast. The bigger the corporation the slower the uptake, going from every 4 years to yearly upgrades.
This weekend we upgraded an environment to bring everything to the latest release. Here's a breakdown of my tasks and some tips.
1. Download all the latest software
- Go to the website Oracle Application Express (APEX) 20.2, click the download link, once you log in the download starts.  If you want to be on the latest patch set, then also click the link to download Patch Set Bundle for Oracle APEX 20.2  (32006852). You will need to have a valid support contract with Oracle in order to download the patchset bundle.
- Go to the website Oracle REST Data Services (ORDS) 20.3, click the download link, once you log in the download starts. 
- Go to the website APEX Office Print (AOP) 20.3, login (or signup if you don't have an account yet), go to the downloads section, and download the on-premise version of Linux or Windows.

2. Prepare installs
- Copy all the zip files from step 1 to the server(s). In our case, all of the above software is running on the same server, but many people have separate servers for the DB (where APEX is), ORDS, and AOP.
- Prepare the directories and unzip
- I typically export all Workspaces and APEX apps before doing the upgrade, in the case of APEX 20.1, and store them somewhere. This way I always have a copy of the app in that APEX version. Here's the command to do so:
/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -expWorkspace > workspaces.txt
/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -instance > applications.txt

3. Install Oracle APEX
Just as with the upgrade to Oracle APEX 20.1 I blogged about previously, I followed the same steps to maximize uptime during the APEX upgrade.
During the first two phases, the Oracle APEX apps were still running:
SQL> @apexins1.sql sysaux sysaux temp /i/

SQL> @apexins2.sql sysaux sysaux temp /i/

The first two phases took about 7 minutes.
After phase 2, I stopped ORDS as specified in the doc, but which is not necessary anymore as ORDS is smart and knows APEX is upgrading and automatically suspends activity by itself. (As I wanted to upgrade ORDS too I stopped it - read more in ORDS install about that)
SQL> @apexins3.sql sysaux sysaux temp /i/

It completed in about 3.30 minutes.
At the same time, phase 3 was running, I copied the images folder.Note: you can also use the CDN by running @reset_image_prefix.sql after phase 3 and specifying: https://static.oracle.com/cdn/apex/20.2.0.00.20/
With the above steps, the APEX 20.2 install completed. Now, I applied the patchset bundle, which took about 1 minute.
SQL> @catpatch

Finally, I ran the following command to allow APEX apps to access web services:
SQL> BEGIN    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(        host => '*',        ace => xs$ace_type(privilege_list => xs$name_list('connect'),                           principal_name => 'APEX_200200',                           principal_type => xs_acl.ptype_db));END;/ 
That was pretty much it to upgrade our Oracle APEX 20.1 to 20.2 release.

4. Install ORDS
Every pluggable database and APEX version has its own ORDS. Whenever I upgrade ORDS, I unzip it in a new directory. I copy the config of the previous ORDS version (the whole ords directory) in the new directory where I unzipped ORDS. In my case, I unzipped ORDS 20.3 in /u01/apex202/ords.Next to the config, I also copy the start and stop scripts and create a logs directory.

Next, I run the ORDS command to set the config directory in the new ords.war file.
java -jar ords.war configdir /u01/apex202/ords
To upgrade ORDS I run: 
java -jar ords.war 
This will connect to the database, identify the meta-data, upgrade it, and run ORDS.Once it's complete, I stop ORDS and run it with the start_ords.sh script, so proper logging is done. Note, in this environment we run ORDS in standalone mode.
Now, with the install of ORDS, I made a mistake... I thought to be smart, and upgrade ORDS at the same time when APEX was doing the final install step (see in APEX, when I stopped ORDS).This wasn't so smart to do! It looks like ORDS is looking at the APEX version to generate some repository views. It messed up things, as ORDS saw still APEX 20.1 as the flip of APEX versions was still going on. Anyway, I got an error. So I decided to wait until the APEX upgrade was finished, then I ran the ORDS command again and it completed fine.  All things were validated ok, start ORDS and we were up-and-running in APEX 20.2 and ORDS 20.3.  Tested the apps, all ok... but for one domain, after a few minutes, I saw an error in the ORDS logs:
2020-11-21T11:19:38.182Z INFO        <CaNhATfOdgVPIOr6aSAtzw> GET www.xxxx.com /ords/f?p=XXXX:LOGIN:0 403ProcedureForbiddenException [statusCode=403, logLevel=INFO, reasons=[Access to the procedure named: f is denied. ]]
I still can't explain why it happened. I solved it by running ORDS validate again and restarting ORDS.
java -jar ords.war validate
My guess is, that the connection pool was messed up by running ORDS while APEX was not finished yet installing. So my recommendation is to always install APEX first and only once that is complete upgrade ORDS (if you want to do it at the same moment), or the other way around, install ORDS first and once complete upgrade APEX.

5. Install AOP
Upgrading AOP is the most simple of the three components, as it doesn't have a repository. Unzip the download in a new directory, copy the config (aop_config.json) of the running AOP to the new folder and activate AOP:
./APEXOfficePrintLinux64 -a
Stop the old version of AOP, start the new version of AOP, and done.

Happy upgrading!

In this little series, I’m going to discuss how Automatic Indexing works in relation to Partitioning. I’ve discussed Indexing and Partitioning many times previously and how Oracle has various options when indexing a partitioned table: Non-Partitioned Index Globally Partitioned Index Locally Partitioned Index So the question(s) are how does Automatic Indexing handle scenarios with partitioned […]

In Part I of this series, we saw how Automatic Indexing will not create a viable Automatic Index if there are stale or missing statistics on the underlining segments. In Part II we saw how these SQL statements effectively become blacklisted and when segment statistics are subsequently collected, Automatic Indexing will still not create viable […]

    In my previous post, I discussed how having stale statistics, usually a bad idea, is especially problematic with regard Automatic Indexes as it usually results in viable automatic indexes only being created in an UNUSABLE/INVISIBLE state. If we were to now to collect the missing statistics:   If we now repeatedly re-run the […]

A “golden rule” when working with Automatic Indexing is that things don’t work properly if there are stale statistics on the dependant objects. Stale statistics can of course be problematic but they can be particularly troublesome when dealing with Automatic Indexing. In the Oracle Autonomous Database environments, this issue is addressed somewhat by the new […]

This post is part of the Getting Started with Plug-ins Pro APEX plug-ins series.

In many Oracle Application Express projects, there's a requirement that people can upload files. Typically the end-users want the ability to drag-and-drop images, PDFs, or other file types in their application. 

For example, if you use APEX Office Print, you might have added in your application the ability for end-users to upload their own templates which typically are docx, xlsx, pptx, html and text files. 

Those files are then stored in the database in a BLOB column or they might be stored on a file server or cloud storage.

In order to add this capability in your app, APEX provides a File Browse item.

There are a few settings for this item type; where do you want to store those files (in a BLOB column or in a TEMP table), if you want to allow multiple file uploads at once, and which types of file you want to accept.

But be careful with the accepted File Types feature. You can define image/* if you want your browser to only allow selecting of images. BUT specifying the file type doesn't prevent users from dragging-and-dropping other file types! So you can't rely on this feature to keep other file types out of your database. 

As this is a native HTML File Browse item, you can customize it with CSS (and HTML and JavaScript). For example, you can make it look like what APEX provides in the APEX Builder itself:

In most of the projects I'm involved in, the end-users want more than the standard HTML File Browse that Oracle APEX provides out-of-the-box. 

The most requested features are:

• Show which files were selected when adding multiple files
• A visual indicator of the progress of uploading
• Save files directly to the cloud (Oracle Cloud Object Storage, Amazon S3) instead of the database
• Limit the file types that will be accepted
• Only allow files with a size less than x MB
• Specific to upload of images: resize and compress images and add watermarks automatically
• Rename files

There are some excellent open-source JavaScript libraries that bring these features, one of the most known is Dropzone.js. Five years ago, Daniel Hochleitner even created an APEX Plug-in on top of it (with the last update 2 years ago). As this plug-in was really well written, we decided to use it as the starting point for our File & Image Uploader plug-in. You can see our plug-in as a supported version of Dropzone for Oracle APEX, with some extra features.

To use this plug-in, you first have to download and install it. I've covered that in my blog posts Plug-ins Pro: Getting started: Sign-up and install your first Oracle APEX Plug-in. So if you didn't import the File & Image Uploader Pro plug-in yet, read that post first.
I'll cover two use cases where we used our File & Image Uploader plug-in.
The first use case is for our RentMaster Platform software which assists in the renting of student homes. We have a form with the information of the contract, and a region which allows people to upload multiple files (up to 3, with a max file size of 5MB) that are linked to this contract. This information is stored in the CONTRACTS table and another CONTRACT_FILES table which has an FK to the CONTRACTS table. Here's a simplified version of the use case in action:

In order to create the above, we add a region with our plug-in on the left and a normal classic report on the right. We also have a Dynamic Action that fires when the files are uploaded so that the report on the right is refreshed.
The attributes of our plug-in region look like this. As storage type we use Custom Table:

The only thing we have to do is define the table in which we want to store the files and the relation (FK) with the parent (ID).
The plug-in exposes many dynamic action events, so you know exactly in which state the upload is in. We specified when Dropzone File Upload was successful:

Pretty simple and low code :)
In the second use case, our customer does auctions. With every auction, multiple images are included of the goods that are for sale. As there are so many images, all those images are resized and watermarked and put outside the database, on a file server and cloud storage. In this example, I will show how to do the image manipulation and save the files directly in the Object Storage of the Oracle Cloud.
Note: if you are interested in how to set up the Object Storage in the Oracle Cloud, read my series on the Always FREE Oracle Cloud.
In the next animated gif, you see again a simplified version in action. On the left the APEX application with the File & Image Uploader plug-in, on the right, the Oracle Cloud - Object Storage:

The attributes of our plug-in region look like this. As storage type we use this time Web Service:

Furthermore, in the attributes, we defined that we should only accept images with max size 3MB and only 10 files.
This time we also defined transformation parameters, a watermark and we rename the files on the fly.
In the United Codes File & Image Uploader Pro Sample Application, you will find an Attribute Builder, which makes it very easy to define what your transformations and watermark need to be, so you can just copy and paste in the attributes:

So we defined a Web Service... this web service can be anything. In my example, I created a web service in ORDS. In this web service, I make a call to the Object Storage REST API of the Oracle Cloud. The plug-in will pass some parameters to the web service: file, filename, mimetype, sessionid and appid:

When you look at the above, you might think... hmm, Dimitri is not doing any authentication to his Object Storage... that is correct. To simplify my example I setup Pre-Authenticated Requests for my bucket in the Object Storage. This provides me with a secure URL people can use to put files in my bucket.

I believe that most people will do proper authentication, which means you first do an extra call to get the authentication token. You may want to read JMJ Cloud's blog post if you want an example of how to do that from APEX. Adrian Png also did another blog post on how to deal with Object Storage from APEX.
Now back to the example... this is what the result looks like; the large image was scaled down, the quality was set to 80%, a watermark was added and the file was renamed - all on the fly and automatically!

So to recap this example: you add our region plug-in, you specify the actions you want to happen (transform, watermark, rename), define a web service which does the call to your cloud of choice and that's it, now whenever somebody uploads files in your Oracle APEX app, it will go straight to the cloud and all manipulations are done in the background!
There are many more use cases you can come up with. This plug-in is also part of our APEX Media Extension (AME). Using the Plug-ins Pro File & Image Uploader plug-in is great when you want things to happen on the client-side. When you already have images in your database, and you want to manipulate those images, you definitely want to check out AME, as it provides a PL/SQL API to do all that, and more (e.g. read meta-data of photos!). We built AME as a replacement for Oracle Intermedia (see blog post and here), we are even featured in the official Oracle documentation as a replacement.
Ok back, to this plug-in... just like any other Plug-ins Pro plug-in, there's extensive documentation and a great sample app that showcases the different features.
You can try the plug-in yourself for 1 month for free in your own APEX applications, so you can see what it can do for you. I also want to highlight that through the end of September 2020, there's a special running. You can find all the details on the Plug-ins Pro website!

  In my previous post, I discussed how the Automatic Indexing task by using Dynamic Sampling Level=11 can correctly determine the correct query cardinality estimates and assume the CBO will likewise determine the correct cardinality estimate and NOT use an index if it would cause performance to regress. However, if other database sessions DON’T use […]

In my previous few blog posts, I’ve been discussing some issues in relation to how Automatic Indexes handle SQL statements that accesses skewed data. In this post, I’m going to setup the scenario in which Automatic Indexing can potentially use Baselines to help address some of these issues. BUT, as we’ll see, I’m having to […]

As I discussed in Part I of this series, problems and inconsistencies can appear between what the Automatic Indexing processing thinks will happen with newly created Automatic Indexing and what actually happens in other database sessions. This is because the Automatic Indexing process session uses a much higher degree of Dynamic Sampling (Level=11) than other […]

Following on from my previous few posts on “data skew”, I’m now going to look at it from a slightly different perspective, where there is an inherent relationship between columns. The CBO has difficulties in recognising (by default) that some combinations of column values are far more common than other combinations, resulting in incorrect cardinality […]

  I’m going to expand just a tad on my previous posts on data skew and run a simple query that returns a few rows based on a column predicate AND another query on the same column that returns many rows. The following table has a CODE column as with previous posts with the data […]

In my previous post, I discussed an example with data skew, in which the Automatic Indexing process created a new index, but somehow the CBO when using the index estimated the correct cardinality estimate even though no histograms were explicitly calculated. In this post I’ll answer HOW this achieved by the CBO. Get some idea […]

When it comes to Automatic Indexes, things can become particularly interesting when dealing with data skew (meaning that some columns values are much less common than other column values). The next series of blog posts will look at a number of different scenarios in relation to how Automatic Indexing works with data that is skewed […]

In a previous post, I gave a bit more background on Plug-ins Pro. In this post, I will guide you on how to get started on your FREE trial. I plan to blog more about the specific plug-ins and will update this post with the links to the specific plug-in posts.
Let's get started!
Surf to plug-ins-pro.com and click the sign up button.
Note: If you already have an account on any of the other United Codes products, for example, APEX Office Print, you don't need to sign up anymore, you can just login with the same credentials.
Enter your email and hit the Sign Up button:
You get a confirmation an email is on the way:
A few seconds later you will receive the email which contains the link to confirm your email address:

During the confirmation, it will ask you to set a password:
Hit the Set Password button and there you go, you are in the Plug-ins Pro portal.
The first page you come to will explain how to get started.  Click the orange link "sign up for a FREE trial":

It will bring you to the Available Plug-ins section on the portal. Here you see an overview of all available plug-ins and a little bit of information. The icons also show which type of Oracle APEX plug-in it is.

Click on the plug-in that you are interested in. You will see some more info and some buttons that link to the documentation, sample app and there's also the FREE Trial button.

Once you click the button, the plug-in is added to your account, and a Download link becomes available:

Unpack the zip file:

Inside, there's a README.md file which gives you more info. But let's continue to import this plug-in into our own Oracle APEX application.
Go to your APEX app > Shared Components and click the Plug-ins link:

Click Import and select or drag and drop the file which has the plugin word in it to the section on the import page: e.g. I dragged file item_type_plugin_unitedcodes_ig_checkbox.sql

Follow the rest of the wizard:


And there we go; the plug-in is imported in our own application:

In order to use the plug-in we also have to run a script which will create some database objects the plug-in uses.
Go to SQL Workshop > SQL Scripts 

Click the Upload button

and Upload the file with the "ddl" in the filename:

Once uploaded, click the Run button next to the script:

And hit the Run Now button:

You should see that all is successful:

Next, go to SQL Commands:

And copy the following from the README.md file:

And enter the plug-in name and your API key which you can find in the Plug-ins Pro dashboard  by clicking on the plug-in to bring up the details:

Once replaced, hit the Run button and it should say the license is created:

That's it... now you are ready to use the plug-in.
** optional **
In case you try to install the plug-in in an environment  that doesn't have internet access you can license the plug-in manually.
In the README.md there's another option. Run the below in SQL Commands (replacing the plug-in name and API key as we did before):
      begin        uc_pluginspro.manual_license(          p_plugin      => 'PLUGIN_NAME',          p_api_key     => 'YOUR_API_KEY');      end;        /
This will give you a Key, copy the key:

Go to the Plug-ins Pro Portal and click the Generate License button:


Paste the key you copied earlier here:

Click the Generate License button and there you get your license key:

Do the second part of the README.md and copy your license key in the code:
      begin        uc_pluginspro.manual_license(          p_plugin      => 'PLUGIN_NAME',          p_api_key     => 'YOUR_API_KEY',          p_license_key => 'YOUR_LICENSE_KEY');      end;        /
Run the code and now you have manually licensed the plug-in:

**
In another post I will go into more detail on how to use the different plug-ins and in which scenarios they are useful. But before I finish off this post, I recommend that you import the Sample Application that comes with the plug-in. In the sample application, you will see many examples on how to best use the plug-in. Although probably not many people read documentation, we have it, and it's very extensive, so if you want to know the nitty-gritty details, it's a great resource.
And finally, if things are not going as planned and you need help, or you have questions or remarks, don't hesitate to contact the Plug-ins Pro support team at support@plug-ins-pro.com
This post was how to install the plug-in in your APEX app, in the next posts I will show you the different plug-ins. Here's a quick reference index which I will update on every blog post:

• Background on why we started Plug-ins Pro 
• Sign-up and install your first Plug-ins Pro Plug-in (this post)

Coming up soon:

• Client-Side Validation Pro
• Interactive Grid Checkbox Pro
• Interactive Grid Download (& Preview File) Pro
• Image Uploader Pro (* enhanced Dropzone Plug-in)
• Interactive JET Charts Pro
• An exciting new plug-in which we will announce :)
• More info on Plug-ins as a service, pricing, and the special offer

After APEX Office Print (AOP), APEX Media Extension (AME), and APEX Project Eye (APE), we launched our newest product and service, Plug-ins Pro, in June.

Since 2004 I've used Oracle Application Express (APEX) and I just love it. With every new release of APEX things become easier and new features get added going from new item types like switches to rich components like charts. But the web is moving fast, and customers want more and more which means you need to go outside of what APEX provides. The main building blocks of APEX are the Oracle database, SQL, PL/SQL, HTML, CSS, and JavaScript. You can use any of those technologies to extend Oracle APEX with custom components. This is a real benefit of APEX, you can go from Low Code to High Code in no time. Really, there's no limit... only your imagination and skills.
Over time I've created many extensions for APEX, going from building reusable PL/SQL packages to integrating some JavaScript library. The issue with building those one-off customizations is that you have to maintain them. Where plain Oracle APEX applications stand the test of time very well, for example, an app built in 2006 still runs today, even after upgrading to the latest Oracle APEX version. Whenever I used custom code, especially JavaScript, it was less obvious to keep that current, and it would be one of the first things that break. Next to that, you make the code work specifically for a project, and in the next project you need something similar, so you copy the solution and make changes specific for that project. It's always in the context of a project that things are built, never looking at the bigger picture.
As the Oracle APEX team identified the need to extend APEX and wanted to provide a way that makes it more reusable and easier to maintain between APEX applications, they introduced plug-ins in APEX 4.0, this is now 10 years ago!
The result was that the awesome APEX community started to create and share plug-ins. Most people offer those plug-ins as open-source. Initially, there was an APEX plug-ins website maintained by Itium that had a catalog of all APEX plug-ins, but today you find the plug-ins catalog on apex.world.

There are many great plug-ins from people like Bartosz Ostrowski, Daniel Hochleitner, and Ronny Weiß to name a few, but there are also many plug-ins now that are built for a specific purpose but not maintained any longer. If you included one of those plug-ins in your app, you are on your own. But even if it's still maintained, you can't expect the author to add new features to the plug-in when you need them or when you upgrade the APEX version and it breaks. 
Creating plug-ins and maintaining plug-ins takes a lot of time, especially when you want to think about the bigger picture and make them so good that they can be used in many different projects, they are fully documented and you have some sample code, so it's easy to include in any project, even when it's been a while since you last looked at it.
And this is how Plug-ins Pro was born... we want to take away the pain of having to build and maintain APEX plug-ins. These are not just plug-ins, those are rock-solid, state-of-the-art, professionally built plug-ins that behave just like native Oracle APEX components. They are accessible, translatable, universal theme ready, and most importantly fully documented, with many samples on how to use them AND supported! If you have an issue or you want an extension, you know who to go to.
With AOP we made printing and exporting of data easy in the Oracle database and APEX. With Plug-ins Pro we want to make extending the Oracle database and APEX easy!


Once I knew we wanted to bring high-quality, supported plug-ins to the APEX community, I knew we would need the best people to bring them to life.  Bartosz Ostrowski joined the team in January of this year and began creating our awesome plug-ins.  When we launched Plug-ins Pro we started with 3 highly demanded extensions for Oracle APEX:

• Client-Side Validation Pro
• Interactive Grid Checkbox Pro
• Interactive Grid Download (& Preview File) Pro

And over the last two months, we added two more plug-ins:

• Image Uploader Pro (* this is an enhanced version of the Dropzone Plug-in)
• Interactive JET Charts Pro

And we didn't go light on those plug-ins... the client-side validation plugin for example took over 400h to build!
We started from a requirements document:

We built the plug-in, tested it in on the different APEX versions (starting with 18.1), and finished off by creating extensive documentation and a full-fledged sample application! 


The goal of Plug-ins Pro is to make you successful, so you can turn your ideas into reality with Oracle APEX and the Database.
Our goal is not to have all the possible plug-ins, instead, we want to create plug-ins that make a difference, and that would take a lot of time to build yourself. We have some plug-ins in the pipeline, some I'm super excited about and can't wait to share with you :) but just like with APEX Office Print (AOP), we especially want to listen to you, what are you searching for, and what would make a difference for you.
This is why on our website, you can submit ideas for new plug-ins and vote on already submitted ideas!

I will write some more blog posts on the plug-ins we offer, but if you already want to try them, head over to the Plug-ins Pro website and try them 1 month for free!

Last but not least... through September 30th, 2020, we are running an Early Adopter Special Offer. Show your support for Plug-ins Pro and help us get off the ground running!  Get all the details at plug-ins-pro.com.

  I’ve had a number of questions in relation to DDL support for Automatic Indexes since my last post on how one can now drop Automatic Indexes, so decided to quickly discuss what DDL statements are supported with Automatic Indexes. Many DDL commands are NOT supported with Automatic Indexes, such as making indexes (IN)VISIBLE and […]

This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
One of the most popular posts in this series is how to Setup a web server on the Virtual Machine.
In that blog post we setup Nginx as our web server, and as a proxy in front of Oracle APEX running on the Autonomous Database Cloud, so we can serve our own domain name e.g. dgielis.com.
Since the release of ORDS 19.4.6 (and higher), Oracle supports that we run our own ORDS version in front of the Oracle Autonomous Database Cloud. This is really cool, for a couple of reasons:

• We can tune ORDS. Oracle limits our Free ATP to 20 concurrent connections, having our own ORDS we can allow more simultaneous connections to our database by changing the config files.
• We are able to create a reverse proxy on the same network as where ORDS is running, which will result in a more secure solution.
• We can have multiple ORDS running against the same database.
• We have more security options as we can choose where we run ORDS.

But before we go into the details on how to run ORDS on your own Compute VM and let it talk to the Autonomous Database Cloud, I also want to highlight a few consequences of doing this.There are two main points of attention running your own ORDS in front of your APEX instance:

• You are responsible to keep your own ORDS version the same as the on the Autonomous Database Cloud. Whenever Oracle upgrades ORDS, you have to upgrade your own ORDS too.
• You have to update your images folder whenever a patch or upgrade is done for APEX.

Here's a small script I use to check the version of both Oracle APEX and ORDS:
select 'APEX' as product, version_no, api_compatibility, case when patch_applied = 'APPLIED' then (select listagg('Patch ' || to_char(patch_number) || ' (' || patch_version || ') installed on ' || installed_on, ', ') within group (order by installed_on) as patches from apex_patches) end as applied_patches from apex_releaseunion allselect 'ORDS' as product, ords.installed_version as version_no, null as api_compatibility, null as applied_patches from dual;
This results in:

You could automate the check, and in case something changed that it will email you for example.
Now, let's get started with the installation of ORDS on the same machine as our Nginx webserver.
# Log in as the opc user to the Compute VM and install Nginx and ORDS:ssh 150.136.245.144 -l opc -i .ssh/oraclecloud
# connect as rootsudo su
# as a best practice, update all packages on Linuxyum update

# install Nginx webserveryum install -y nginx

# load repo which holds ORDSyum-config-manager --enable ol7_oci_included
# in case you get an error, add the repo first manually/etc/yum.repos.d
vi oci-included-ol7.repo
[ol7_oci_included]name=Oracle Software for OCI users on Oracle Linux $releasever ($basearch)baseurl=https://yum$ociregion.oracle.com/repo/OracleLinux/OL7/oci/included/$basearch/gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oraclegpgcheck=1enabled=1
yum-config-manager --enable ol7_oci_included
# install ORDSyum install -y ords
Let's also make sure we have the latest versions of SQLcl and the Oracle software and wallet.Download SQLcl here and check this blog post about the software and wallet and let's make sure we can still connect from our compute instance to our Autonomous Database.
# uninstall old Oracle Instant clientyum remove oracle-instantclient18.5-tools.x86_64
# install latest Oracle Instant clientyum install oracle-instantclient19.8-basic.x86_64 
# connect as oracle su - oracle
# set environment variablesexport PATH=/usr/lib/oracle/19.8/client64/bin:$PATHexport LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64/libexport TNS_ADMIN=/usr/lib/oracle/19.8/client64/lib/network/admin
# connect with SQLcl to see if we can connect from the VM to the DB./sql admin@dbdimi_high

If for some reason something is not working any longer, follow the steps again in the blog post how to run sqlcl from the compute instance to ATP.
We first have to create an ORDS user we will connect at, and it won't be the normal ORDS_PUBLIC_USER as that is used by ATP itself. We will create an ORDS_PUBLIC_USER2 as specified in the Installing and Configuring Customer Managed ORDS on Autonomous Database documentation.
CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY "changeme";
GRANT "CONNECT" TO "ORDS_PUBLIC_USER2";
BEGIN     ORDS_ADMIN.PROVISION_RUNTIME_ROLE(         p_user => 'ORDS_PUBLIC_USER2',         p_proxy_enabled_schemas => TRUE);END;/

Now let's configure ORDS to connect to this user in the database:
# make a base64 string of the zipmkdir /opt/oracle/ords/walletcd /opt/oracle/ords/walletcp /tmp/wallet_DBDIMI.zip .base64 -w 0 wallet_DBDIMI.zip > wallet_DBDIMI.zip.b64WALLET_BASE64=`cat wallet_DBDIMI.zip.b64`

# create the apex_pu.xml and defaults.xml configuration files for ORDScat << EOF > /opt/oracle/ords/config/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_PUBLIC_USER2</entry>  <entry key="db.password">!changeme</entry>  <entry key="db.wallet.zip.service">dbdimi_low</entry>  <entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry></properties>EOF
cat << EOF > /opt/oracle/ords/config/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">10</entry>  <entry key="jdbc.MaxLimit">20</entry></properties>EOF

Let's start ORDS manually first, so we can see if there are any errors:
cd /opt/oracle/ordsjava -jar ords.war standalone

Note: In case you get java.lang.OutOfMemoryError: Java heap space, you can fix that by doing: export JAVA_OPTIONS=-Xmx512M In July I got this error, when I tried again last week I didn't anymore.

Copy Oracle APEX images folder.
Download Oracle APEX and upload the zip file with your favorite SFTP program to your compute instance. E.g. I use Transmit on OSX or you can use wget or something else.
# Unzip the file in the /opt/oracle folderunzip /tmp/apex_20.1_en.zip -d /opt/oracle/
You need to make sure your APEX Images folder is in sync with the version of APEX running on ATP.Typically Oracle will apply some patches, so download from Oracle Support, the patch for Oracle APEX and download it to your Compute Instance:
# Unzip the file in the /tmpunzip p30990551_2010_Generic.zip
# Overwrite the files from the original Oracle APEX foldercp -rf /tmp/30990551/images /opt/oracle/apex
# Edit the standalone.properties to add the reference to the images foldervi /opt/oracle/ords/config/ords/standalone/standalone.properties
# Addstandalone.static.context.path=/istandalone.static.path=/opt/oracle/apex/images

Restart ORDS again, and once we are sure all is working, let's start ORDS as a service and enable it to autostart on reboot:
# make sure you are root userexit
# run ORDS service and enable with startupsystemctl start ordssystemctl enable ords

Next, we want to configure Nginx as a reverse proxy in front of ORDS.ORDS by default is running on port 8080. You may want to read my previous blog post on how to get started with Nginx. I didn't configure the domain yet, but I just wanted to access the IP.
The different bit is :
vi /etc/nginx/conf.d/dgielis.com.conf
Add the following:
server {    listen         80;    listen         [::]:80;    server_name    150.136.245.144;    root           /usr/share/nginx/html/dgielis.com;    index          index.html;    try_files $uri /index.html;
 location /i/20.1.0.00.13/ {  alias /opt/oracle/apex/images/20.1.0.00.13/; }
 location /ords/ {  proxy_pass http://localhost:8080/ords/;  proxy_redirect off;  proxy_set_header Host $host;  proxy_set_header X-Real-IP $remote_addr;  proxy_set_header X-Forwarded-Proto  $scheme;  proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; }
}
Next you want to reload Nginx:
nginx -s reload
The first time I tried to access http://150.136.245.144/ords/ I got a bad gateway error.
Looking at the logs I saw what was going on: 
tail -30 /var/log/nginx/error.log

To fix the issue I did:
cat /var/log/audit/audit.log | grep nginx | grep denied | audit2allow -M mynginxsemodule -i mynginx.pp
And there we go... it worked! Now we can access Oracle APEX with our own ORDS :)


Note: instead of using Nginx, you can also configure a Load Balancer in the Oracle Cloud. Adrian did a nice blog post on how to do that.

There you go... now you can choose to have your preferred domain name (custom URL) with your own ORDS in the Oracle Cloud.

Just a quick update on a previous post on dropping Automatic Indexes. As I discussed previously, one could drop Automatic Indexes by moving them to a new tablespace and then dropping the tablespace. This cumbersome technique was necessary because there was no direct facility to drop Automatic Indexes. Additionally, it’s worth noting this process isn’t […]

In Part I we looked at a scenario where an index was deemed to be too inefficient for Automatic Indexing to create a VALID index, because of the poor clustering of data within the table. In Part II we improved the data clustering but the previous SQLs could still not generate a new Automatic Index […]