Oracle User Group Estonia meetup and other speaking arrangements

I know it is a little late announcement, but Oracle User Group Estonia is having its first meetup after many years tonight. I'm presenting there "Making MySQL highly available with Oracle Grid Infrastructure".
More info and registration here: http://www.meetup.com/Oracle-User-Group-Estonia/events/165539962/.

So if anyone is in Tallinn today, then join us!

I'm also speaking at Oracle User Group Norway 2014 Spring conference (3-5 April 2014), topic "Making MySQL highly available with Oracle Grid Infrastructure". It (more...)

Sample code: Using Datapump API for metadata and data filtering

I was looking for PL/SQL examples to use metadata and data filtering in datapump API, but I didn't find any. So here is one example. It uses table reload_dev_tables to specify what schemas/tables should be exported using data pump and what where clause should be set.

Structure for reload_dev_tables:

 Name                                       (more...)

Exploring DBVisit Replicate

There are a few Oracle database replication solutions on the market:

  • Oracle Streams (powerful, included with RDBMS license (Oracle SE has trigger-based capture, EE mines redo logs and log buffer), but deprecated - no longer developed, complex to manage)
  • Oracle GoldenGate (powerful, but very expensive)
  • Tungsten (heterogeneous, but from Oracle (more...)

Binding IN-lists as comma-separated values

One link that I have to send to developers quite frequently is how to use XMLTABLE in SQL queries to bind comma separated list of values instead of generating large IN list directly to the query (and this way avoid new sqlid/cursor/wasted memory for each different value combination provided). The link that I usually send is this, but in this post I'd like to expand it a little, so it would work even when the string contains special XML characters.

For numbers, the usage is simple:

> var num_list varchar2(100)
> exec :num_list := '2668,2669,2670'

PL/SQL procedure successfully completed.

 (more...)

Using SSL client certificates for authentication in UTL_HTTP

Starting from 11.2 its possible to use SSL client certificates to authenticate yourself to a remote web service using SSL client certificates. I did not find much information on it using Google or documentation, that is why I'm writing this post.

Please refer to this post by Tim Hall to get started on connecting to HTTPS service using UTL_HTTP, all of this is needed before continuing with SSL client certificate authentication.

The first thing you need is to generate user certificate request inside Oracle Wallet, sign it by CA and load the returned certificate back to Wallet. I'm not (more...)

A little “side-effect” of having autocommit off in MySQL

Back-story: A developer came to me and wanted explanation for a weird behavior in MySQL. They inserted a record (to InnoDB table), committed, and after receiving a message (on another application) tried to read that inserted record immediately, but the newly inserted record was not found. Problem only happened in production, but not always (quite frequently).

After comparing the MySQL parameter files between production and development environments I discovered that in production autocommit was disabled to make MySQL behave more like Oracle. This setting was removed from development after we rebuilt the environment (to use multiple MySQL instances with Oracle (more...)

Calling Java procedure from database

Oracle Database has had the possibility to run Java code inside the database for a long time. It's a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn't successful in implementing it under 11.2.0.2).

First, load the Java source into database. (more...)

Extracting private key from Oracle Wallet

Oracle Wallet Manager and orapki do not let you extract the private key associated with user certificate located in Oracle Wallet. If you need it for some reason, for example testing with external tools like wget, then its possible to extract the private key using openssl, since Orale Wallet (ewallet.p12 file) is just PKCS#12 file.

[oracle@jfadboc1n01 wallet]$ openssl pkcs12 -in /home/oracle/wallet/ewallet.p12 -nocerts -out private_key.pem
Enter Import Password:
MAC verified OK
Warning unsupported bag type: secretBag
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
[oracle@jfadboc1n01 wallet]$ ls -l
total 16
-rw-r--r-- 1 oracle oinstall 11629 May  (more...)

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: import_table_to_another_schema.sh

#!/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:
http://www.oracle-base.com/articles/11g/DBFS_11gR2.php
http://blog.ronnyegner-consulting.de/2009/10/08/the-oracle-database-file-system-dbfs/

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:
http://dag.wieers.com/rpm/packages/fuse/

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,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                          (more...)

20. – 21. May EMEA Harmony Conference in Tallinn

An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. - 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.

Read the agenda here and register in OUGF home page (250€+VAT registration fee).

In addition, just before the conference, 17.-18. May, Chris Date will perform his "How to Write Correct SQL and Know It: A Relational Approach to SQL" seminar in Helsinki. More info here and more detailed information here.

The easy way of working with XML in Oracle database (part 2)

In this part I'll look at some features of Oracle Database for getting the data out in XML format, with SQL code only. In part 1 I looked at some features for reading/parsing XML with pure SQL.

Populating the tables for examples

Loading the departments into table DEPARTMENTS.
CREATE TABLE departments AS
   SELECT dep.*
     FROM xml, 
     XMLTABLE ('/company/employees/item[1]/departments/item'
        PASSING x
        COLUMNS id FOR ORDINALITY,
                name VARCHAR2 (100 CHAR) PATH 'name') dep;

Employee data to table EMPLOYEES.

CREATE TABLE employees AS
   SELECT emp.id,
          emp.first_name,
          emp.last_name,
          emp.country,
          dep.id active_department_id
     FROM xml,
          departments dep,
          XMLTABLE (
             '/company/employees/item'
                 (more...)

The easy way of working with XML in Oracle database (part 1)

I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle... And lately I got a task to rewrite some PL/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM API)! Actually, starting from 10.2, this job is much easier...

Here I will go over some methods for working with XML, that are usable directly from SQL. In this first part, reading and extracting data from XML.

XML for the examples

<?xml version="1. (more...)

Oracle Enterprise Manager Desktop Widgets

I love database performance visualization tools and now Oracle has released small desktop widgets that give a quick overview of the targets in OEM Grid Control.

The one I really like is High-Load Databases widget. That shows a quick overview of how the top databases are performing at the moment - total number of average active sessions and a graph how this load is divided between the CPU/IO/Other wait classes.

With a click of a button, the screen changes to the new rectangular style performance graph, also showing the latest ADDM findings.

One feature request to Oracle - please add (more...)

Hello World with Oracle XE

I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX. It's a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database :)

Oracle XE comes with a pre-configured XDB HTTP listener for APEX. I don't need the full APEX engine for my simple request server, just a PL/SQL package using PL/SQL Web Toolkit will do just fine and there is (more...)