Scipt to automatically move RAC 11gR2 services back to preferred instances

When instance fails in Oracle RAC, the services that were using this instance as a preferred instance are automatically relocated to instances marked as available for this service. But after the failed instance recovers and starts up again, the relocated services are not moved back and need manual srvctl relocate service command from administrator to move them back.

Here is a little Bash script to automate this process. Oracle Clusterware (Grid Infrastructure) can execute user callout scripts on FAN events, like INSTANCE up/down. Place this script under $GRID_HOME/racg/usrco/ and set the execute bits on the file. Then clusterware will execute (more...)

Clustering MySQL instances with Oracle Clusterware 11gR2

I've been doing lately quite many database clustering implementations; Oracle RAC and since we have many MySQL instances in production, had to find a good way to make MySQL instances highly available also.

One good solution for this is managing MySQL instances with clusterware and since we are planning to use Oracle RAC on Oracle Enterprise Linux anyway, then Oracle Clusterware is an excellent candidate for this task. Also... Oracle Clusterware is included with Oracle Enterprise Linux at no additional charge.

Requirements I had:

  • Multiple MySQL instances running in the same cluster, in case of node failure affected MySQL instances (more...)

Script to import a table to another schema in PostgreSQL

Tested with Centos 5 and PostgreSQL 8.4.

Sadly PostgreSQL does not have remap_schema parameter in its data import tool pg_restore, so importing tables to another schema in destination database is a little bit tricky.

Here is a Bash script I use to connect to the source database over SSH tunnel, export two tables (srv.stat_export and disp.trip) and then import them to the destination database under schema etl.

Download the script:

#!/bin/bash -l

# Open SSH tunnel to remote database
# Make sure, that passwordless authentication is enabled
ssh -C -N -L 15432:livedb2:5432  (more...)

Geolocation API

There is an interesting W3C Draft, that enables websites to just simply ask web browser to report the users geographical location, and then the web browser will try the best available location method, like GeoIP, WIFI location or GPS. I have currently tested it on Firefox 3.6 and Google Chrome; Internet Explorer 8.0 does not support it yet.

W3C Geolocation API Draft
Mozilla documentation for Geolocation

How to use it in APEX?

If you are just interested in recording the users location, then using an on-demand application process should be the easiest solution:

First, create two application items: (more...)

Authenticate database user against Active Directory – the poor man’s version

My use case for this: one big database, where applications have some shemas. But also, quite a lot of developers need access to the database, to see the production data and security requires each user to have his/her own personal account and of course also requires password expiration and complexity. Authenticating database users against organizations central Active Directory would be just perfect for this case.

Oracle offers this solution for Enterprise Edition, but its an extra cost option - Oracle Advanced Security. If these are not an option for you, then its also possible to achieve the same task, in (more...)

FUSE Linux kernel module for DBFS

I started to play around with DBFS (Database Filesystem), a new interesting feature in Oracle 11.2.

There are some excellent guides on how to set it up:

But both of them had one "not very clean" part in them - compiling FUSE kernel driver from source. Actually this is not necessary and there are precompiled FUSE rpm packages available:

After RPM package is installed, you need to add the OS user who will mount the filesystem (for example oracle) to OS group fuse.

usermod -a -G fuse oracle

Reading RSS with SQL

A small SQL code sample this time, how to read RSS file in SQL. I'm using it to display and aggregate some RSS feeds in APEX application.
All columns are directly read from XML (plus some additinal code to get the time zone correct), except pubdate_local that is pubdate converted to date datatype in local time zone. The first example is reading from a local file localnews.rss under directory DIR1.

SELECT title,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),