Monday, July 25, 2016


Due to account issues blog is moving to a new location, old content will stay indexed for a while. New address, see you there!

Friday, July 22, 2016

Nagios custom script checklist


#cd /usr/lib64/nagios/plugins
- create script with EXIT 0/1/2  ie "check_raid_disks"
#nano /etc/nagios/nrpe.cfg
- define command[check_raid_disks]=/usr/bin/sudo /usr/lib64/nagios/plugins/check_raid_disks
#/etc/init.d/nrpe restart

* please notice that command definition includes sudo, normally nrpe scripts do not require this, but here we will be running custom executable inside our script. If sudo is not used nrpe responds with "NRPE: Unable to read output". Sudo-ers workaroud follows:

#nano /etc/sudoers (add nrpe user rule, comment out TTY requirement)
   nrpe ALL=(ALL) NOPASSWD:/usr/lib64/nagios/plugins/check_raid_disks
   # Defaults    requiretty
#setenforce 0 (disable selinux)

Nagios server

#/usr/lib64/nagios/plugins/check_nrpe -H -c check_raid_disks
#nano /etc/nagios/servers/awsdb.cfg  (server name file)
- define service{
        use                             generic-service
        host_name                       awsdb
        service_description             Raid Disks
        check_command                   check_nrpe!check_raid_disks
#/etc/init.d/nagios restart

Example script


# Exit codes

# server awsDB - virtual drives (vd's) count: 3
# server awsDB - physical drives count: 8

hds=$(/root/bin/MegaCli64 -PDList -aALL | grep state | grep Online -c)
# echo $dds

# Check for missing parameters
if [[ -z "$hds" ]]; then
  echo "Script error, check setup, environment or parameters"
  exit 2
if [[ "$hds" = "8" ]]; then
  echo "OK - virtual disk count: $hds"
  exit 0

if [[ "$hds" -lt "8" ]]; then
  echo "CRITICAL - not all disks are Online: $hds/8"
  exit 2

echo "WARNING - Script error"
exit 1

Nagios debug command notes

#/usr/lib64/nagios/plugins/check_nrpe -H -c check_cpu
#nagios -v /etc/nagios/nagios.cfg

Friday, June 10, 2016

Apex 3.2 idle session cleanup and WWV_FLOW_DATA cleanup


When users login they get a temporary 'nobody' session with a few populated variables (or many if you have public translations or custom values), after login user gets an assigned session id and starts populating WWV_FLOW_DATA with item values.


If not maintained WWV_FLOW_DATA explodes and performance degrades.
I suspect that session idle setting is not working in Apex 3.2, tested many settings and checked Apex tables - nothing changes no matter what the settings are. Sessions pile up during the day, default cache purge procedures only allow cleaning up old sessions by date, not by idle period. Thus I can only run it at night or not at all (if environment gets international).


With this query we can find sessions which had no activity for more then 4 hours:

SELECT last, userid, session_id
(SELECT to_char(max(time_stamp),'DDMMYYYY HH24:MI') last, userid, session_id, max(time_stamp) mx
FROM apex_030200.wwv_flow_activity_log1$ l
--where userid = 'ADAM'
WHERE exists (select 1 from apex_030200.wwv_flow_sessions$ s where id = l.session_id)
GROUP BY userid, session_id
WHERE mx < sysdate-4/24;

Was digging Apex 3.2 packages for a couple of hours looking for a procedure to logout or cleanup. Looks like they where only introduced in 4.1. All the existing procedures are relying on Cookie values or session variables which I was not able to set manually.
Eureka moment. Even though Apex packages are wrapped - there was a comment or piece of code visible, that cleanup procedures simply delete from the views. So likely there are some instead triggers running which do the job.


1) grant delete on apex_030200.wwv_flow_sessions$ to ;
2) Create procedure in your schema:

create or replace procedure "S_CLEAR_SESSIONS"
  n NUMBER := 0;
  FOR c IN (SELECT last, userid, session_id
              (SELECT to_char(max(time_stamp),'DDMMYYYY HH24:MI') last, userid, session_id, max(time_stamp) mx
               FROM apex_030200.wwv_flow_activity_log1$ l
               --where userid = 'ADAM'
               WHERE exists (select 1 from apex_030200.wwv_flow_sessions$ s where id = l.session_id)
               GROUP BY userid, session_id
            WHERE mx < sysdate-4/24
            SELECT last, userid, session_id
              (SELECT to_char(max(time_stamp),'DDMMYYYY HH24:MI') last, userid, session_id, max(time_stamp) mx
               FROM apex_030200.wwv_flow_activity_log1$ l
               WHERE nvl(userid,'nobody') = 'nobody'
               AND exists (select 1 from apex_030200.wwv_flow_sessions$ s where id = l.session_id)
               GROUP BY userid, session_id
            WHERE mx < sysdate-1/24
    ---htp.p('dead session: '||c.userid || ' sess: '||c.session_id);
    DELETE FROM apex_030200.wwv_flow_sessions$
    WHERE id = c.session_id;

3) Run the procedure:


select count(*) from apex_030200.wwv_flow_data;

Returns 130364

begin S_CLEAR_SESSIONS(); end;
select count(*) from apex_030200.wwv_flow_data;

Returns 129318

And thats only while writing this article. My first drop was from 1300000 items to just 200000, then added a shorter 'nobody' session killer as you can see in the code.


Nightly cleanup used to look like this:

Implemented a session cleanup at 15:00:

And here is one more from couple of days later when I added 1 hour grace period for all 'nobody' sessions independent of their activity:

Tuesday, February 2, 2016

Oracle Apex internals: template, source, conditional SQL storage


While working with Apex I was building, backing up, customizing applications. I also had a few custom themes, theme-rebuilds. Now I need to update interfaces for 22 applications. Update includes custom CSS, JS'es, styles and HTML customizations for regions, region sources, templates, items, buttons. My applications contain hundreds of regions and items, its time to start digging Apex internals. Some of them can be found documented in forums, some of them I was not able to locate.


Any mistype or abuse may damage Apex IDE, usability, upgradability and your own application sources. Consider yourself warned.

General remarks

Flow_id - Apex application ID, consider always including in your query or at least limiting to a range below 4000.
Flow_step_id - application page ID.
Make database and application backups. At least copy some stuff to comments column.
If you have at least a couple applications most of the columns are self explanatory and follow same naming notation across all Apex tables.

Main Apex tables and their purpose

Apex schemas are named HTMLDB or APEX with trailing version number (examples: HTMLDB_020000, APEX_030200, APEX_040100, APEX_050000, dont recall if WEBDB had its own naming pattern). Tables are mainly starting with WWV_(FLOW) prefix.

WWV_FLOW_STEP_ITEMS - page items (like P101_USERNAME), I mainly use this table for mass item template changes (ITEM_FIELD_TEMPLATE), label alignment change (LABEL_ALIGNMENT), some very specific template changes (I use simple items to display clickable icons). My personally favorite field is TAG_ATTRIBUTES - custom HTML, CSS, Javascript code input.

WWV_FLOW_REGION_REPORT_COLUMN - report-type region column description and customization. I mainly use report columns for links with icons (COLUMN_LINKTEXT), other uses may include massive label or value alignment changes.

WWV_FLOW_ROW_TEMPLATES - report templates (not regions), I use them for massive HTML/CSS changes, class updates. Almost every column in this table is obvious and usable.

WWV_FLOW_STEP_BUTTONS - page buttons (obviously). I mainly use this table when I change button template (remember that application template ID's are different - I change one item in Apex, then update the rest based on changes in table). Column BUTTON_IMAGE. Can also be used for massive button alignment, condition or image changes.

WWV_FLOW_PAGE_PLUGS - hardest one to find or come up with, but also one of the most important ones - Region definition and sources. I do have some conditional SQL which displays certain icons (URL from webserver storage), I use PLUG_SOURCE column for mass updates when changing design templates. Can be used for condition, template or title changes.

WWV_FLOW_WORKSHEET_COLUMNS - this one may also be tricky to locate - interactive report region column desciptions. I mainly use this table for icon/image changes, mass link or interactive report column alignment changes.

Monday, January 25, 2016

Drupal performance improvement (valid for version 7)


Drupal version 7, objective is to improve both Desktop and mobile versions of your website, improve SEO ratings like page loading speed, text to HTML ratio, number of CSS files, Javascript footprint size, overall page footprint size, number of files to load.

Improvement process

Drupal is module based CMS, so you are going to need only a couple of modules added to your installation. Each module and its use follows. Hopefully this article will be updated quite a few times.

PHP filter + Browscap

There are a couple of Drupal modules that allow hiding content based on device size (mobile or desktop). Tried quite a few Mobility modules, but none of them seem to work, where old or required modules like Cache to be turned off. And its not what I wanted.

In mobile versions I sometimes disable large slideshow blocks or page images, if you are up for that, here is what you do: download browscap, deploy it and enable both modules (PHP filter is native Drupal module). Open you block, enable PHP condition and paste this snippet:

$mobile_agent = browscap_get_browser() ;
if($mobile_agent['ismobiledevice'] == "false"){return true;} 


This module simply removes all the unnecessary characters from your HTML and Javascript files. Compresses and creates copies of all you Javascript includes. This makes your files download faster. Some of the files are not being compressed due to their already small footprint, pay no attention to them. I will not describe how to use this module, its self explanatory. Once deployed you will find it under cache/performance management. Link for download. Suggesting enabling it when production ready since output HTML and JS files are hardly human readable.


Full module name is Advanced CSS/JS Aggregation. This module combines (even cached) css files into a single file, which gives quite a few performance points in some of SEO evaluation tools. Combining your CSS files also reduces text/html ratio. Setup has quite a few tabs, but just follow the setup and enable CSS compression.


Usage of all these modules above may improve your website SEO evaluation by up to 10%. If not, then they will at least improve your page loading speed and overall performance compared to your competitors. Any feedback to this article is highly appreciated.

Thursday, January 7, 2016

Oracle Apex Textarea printing with line breaks


Use Text Fields or Text Area fields for text input to build a document template generator. Oracle Apex version 4.


With version 4 Apex introduced quite a few variable session handling changes, if you had a long experience with V3 - you will be stuck quite a few times. When you enter text in Rich Text editor or Text Area field and later try to print it in HTML area - either all HTML tags are exposed or removed. In the end you get plain text with different level of garbage. In V3 you could solve this by copying text to a simple Display Only item and use it hidden, then all the HTML tags could be prevented. Here are the three Text Areas used. Text with simple line breaks () or paragraph breaks (+) - which are invisible in UI of Text Area. 


In HTML area add <pre> and </pre> tags for the Text Area fields. Same tag applies if you change your item to read-only on display. Print preview looks great.

Wednesday, December 9, 2015

ImageMagick and ghostscript delegate problem: sh gs command not found


Recent ImageMagick update kicked a little in the nuts. I mainly convert PDF to PNG and then use them for OCR. Here is how it welcomed me after an update to 6.7.2:

sh: gs: command not found
convert: missing an image filename

It works ok from a command line, but fails from Java or any external environments. At first the error may look like a command line syntax change, but it isnt, dont start moving your flags places or in-front/after PDF file - this is not the problem here.


1) # which gs
2) locate delegates.xml file in your system
3) open the file for editing (# nano /etc/ImageMagick/delegates.xml)
4) find all occurrences of "gs", which will be simply put in html quotes:  <...>command=""gs" -q <..> and replace it with the output of you "which gs" result, in my case it looked like this: <..>command=""/usr/local/bin/gs" -q <..>. Leave the surrounding syntax intact.

After that just save the file and run you external command again. There will be around 10 occurrences, replace them all to be on the safe side, I suspect that its only PS configurations that had to be changed.