Stopping XE 18c properly when shutting down CentOs 7

Category Oracle Database

You can configure automatically start and stop Oracle XE 18c database on operating system startup and shutdown. See details from installation guide.

I did install XE 18c on CentOs 7 and executed steps mentioned in installation guide.
When viewing database alert.log after system reboot, noticed that database wasn't actually stopped properly. Processes were just killed.
I didn't investigate more why that happens on my system. For workaround I did revert steps mentioned in installation guide and created service file for systemd.

First stop XE database and revert automatically start and stop:

systemctl stop oracle-xe-18c
systemctl disable oracle-xe-18c

Create service file:

cat >> /usr/lib/systemd/system/ora-xe-18c.service << EOF
Description=Oracle 18c XE databases service

ExecStart=/opt/oracle/product/18c/dbhomeXE/bin/dbstart /opt/oracle/product/18c/dbhomeXE &
ExecStop=/opt/oracle/product/18c/dbhomeXE/bin/dbshut /opt/oracle/product/18c/dbhomeXE


Backup and modify /etc/oratab:

cp /etc/oratab /etc/oratab.backup_`date +%Y%m%d`
sed -i 's/XE:\/opt\/oracle\/product\/18c\/dbhomeXE:N/XE:\/opt\/oracle\/product\/18c\/dbhomeXE:Y/g' /etc/oratab

Enable new service and start XE database again:

systemctl daemon-reload
systemctl enable ora-xe-18c
systemctl start ora-xe-18c

Caching APEX static files

Category APEX and Apache HTTPD

The APEX installation includes static files that are located on the web server. These files may changed normally only when you upgrade or apply patch to APEX. Because files are changing rarely, they are good subject stored to cache. If you use Apache HTTPD to serve static files, you can configure mod_file_cache for ones that are used most in your applications.

You might have also other static content e.g. images, JavaScript and CSS files that are stored to APEX workspace or application static files. Also these files aren't usually changed often and you can store files to cache and reduce calls to database. This can be done using HTTPD modules mod_cache and mod_cache_disk.

I provide here only basic information that you are aware about these HTTPD cache features and how those may used with APEX. Assumption is that you have knowledge how configure HTTPD. You need test and see how these works on your system.

First check are needed modules loaded to HTTPD. In Linux machine you can use command httpd -M to list all modules that are loaded.
You should look cache_module, cache_disk_module and file_cache_module. 
Output of command httpd -M lists HTTPD loaded modules

Create configuration file called e.g. apex_file_cache.conf to specify the APEX static files you like cache. Here is example configuration file content for APEX 5.1.4 when applications use Universal Theme and styles Vita:

# APEX 5.1.4 mod_file_cache configuration for Universal Theme style Vita
<IfModule file_cache_module>
  # CSS
  CacheFile /apex/images/app_ui/css/Core.min.css
  CacheFile /apex/images/app_ui/css/Theme-Standard.min.css
  CacheFile /apex/images/libraries/jquery-ui/1.10.4/themes/base/jquery-ui.min.css
  CacheFile /apex/images/libraries/font-apex/1.0/css/font-apex.min.css
  CacheFile /apex/images/themes/theme_42/1.1/css/Core.min.css
  CacheFile /apex/images/themes/theme_42/1.1/css/Vita.min.css
  # JavaScript
  CacheFile /apex/images/libraries/apex/minified/desktop.min.js
  CacheFile /apex/images/libraries/hammer/2.0.4/hammer-2.0.4.min.js
  CacheFile /apex/images/libraries/apex/minified/widget.apexTabs.min.js
  CacheFile /apex/images/libraries/apex/minified/widget.stickyWidget.min.js
  CacheFile /apex/images/libraries/apex/minified/widget.stickyTableHeader.min.js
  CacheFile /apex/images/themes/theme_42/1.1/js/modernizr-custom.min.js
  CacheFile /apex/images/themes/theme_42/1.1/js/theme42.min.js

In example I use CacheFile directive but you should test does your platform support it or do you get better result with MMapFile directive. Check also path where the files are located in server. Include configuration file to your httpd.conf file, run test for configuration and restart HTTPD.

NOTE! when you upgrade or patch APEX, you need check are same files still relevant for cache and do changes accordingly to the configuration file. Also you need restart HTTPD to avoid end up serving requests that are completely bogus as mod_file_cache documentation says.
You shouldn't try cache all APEX +12 000 static files, that installation contains, with mod_file_cache. Most probably your applications isn't using those all even in special cases. You need carefully select only most used files.

To configure cache for APEX workspace and application static files create configuration file called e.g. apex_cache_disk.conf. Here is example configuration for workspaces ws1, ws2, ws3 and ws4:

# APEX 5.1.4 mod_cache configuration for static workspace and application files
<IfModule cache_disk_module>
  CacheRoot      /var/cache/httpd/proxy
  CacheEnable    disk /apex/ws1/r/
  CacheEnable    disk /apex/ws2/r/
  CacheEnable    disk /apex/ws3/r/
  CacheEnable    disk /apex/ws4/r/
  CacheLock      on

You need check what are URL's in your system relating to APEX workspace and application static files and adjust configuration accordingly. Include configuration file to your httpd.conf file, run test for configuration and restart HTTPD.

Optionally you can also add CacheIgnoreCacheControl directive that clients can't force refresh cache in HTTPD. Then it is recommended where you refer static file add query string to end. That way developers can force HTTPD fetch new file when files are changed. You can use e.g. APP_VERSION substitution string like:


If you change file in workspace or application static files, change also version attribute in application definitions so HTTPD knows reload file.
After enabling mod_cache, you should also see and setup htcachecleanup.

Depending if you aren't using RESTful services from APEX workspaces, after configuring mod_cache for workspace and application files, you might like check and change ORDS configuration for APEX_REST_PUBLIC_USER and APEX_LISTENER users pool. See if you can lower those pools jdbc.MinLimit so there aren't connections waiting for nothing while HTTPD serves workspace and application static files from cache.

Changing Report Column Date Format

Category APEX Tricks

In APEX there is build in date formats SINCE and SINCE_SHORT that I use often in report date columns. Unfortunately you can't use SINCE format in application Globalization Attributes, so you need separately set it to columns. But, sometimes you need to see the actual date value in column.

Here is simple example how you can switch between application global date format and SINCE.

First create application item G_DATE_DISPLAY_FORMAT.
Application item

Create two entries to Navigation Bar list as follows.
First list entry:

  • List Entry Label: Show Since
  • Target page: &APP_PAGE_ID.
  • Condition: Value of User Preference in Expression 1 != Expression 2
    • Expression 1: U_DATE_DISPLAY_FORMAT
    • Expression 2: SINCE

Second list entry is almost same. Just change label, request and condition

  • List Entry Label: Show Date
  • Target page: &APP_PAGE_ID.
  • Condition: Value of User Preference in Expression 1 = Expression 2
    • Expression 1: U_DATE_DISPLAY_FORMAT
    • Expression 2: SINCE

Create On Load: Before Header application process.
PL/SQL Code:

  l_since varchar2(40);

  l_since := 
    then 'SINCE'
  :G_DATE_DISPLAY_FORMAT  := l_since;

  /* save user selection to preference */
     p_preference => 'U_DATE_DISPLAY_FORMAT'
    ,p_value      => l_since
    ,p_user       => :APP_USER


Set process conditionally by request containing SET_DATE_FORMAT_SINCE_ON and SET_DATE_FORMAT_SINCE_OFF
Application process condition

Application process stores information also to preference. If you like you can set application item value for new sessions in Post-Authentication Procedure.

Place code to authentication schema source PL/SQL Code and Enter to Post-Authentication Procedure Name field set_date_display_format

procedure set_date_display_format
  :G_DATE_DISPLAY_FORMAT := apex_util.get_preference(
    p_preference => 'U_DATE_DISPLAY_FORMAT'
    ,p_user => :APP_USER

Authentication schema

Then edit report columns and set format mask to application item substitution string &G_DATE_DISPLAY_FORMAT.
Report column format mask

Here you can see example where right top there is link to switch date format mask. When you click the link in navigation bar, you can see report Hired on column format changes.

Global Right Column



Subscribe in a Reader