Ilmar Kerm

Oracle, databases, Linux and maybe more

Written by:
Category: Blog entry
Published:

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 15 11:38 ewallet.p12
-rw-r--r-- 1 oracle oinstall  1879 May 17 08:53 private_key.pem

Here private_key.pem contains the private key extracted from Oracle Wallet.

The first password that openssl asks (Enter Import Password) is the wallet password, the other password (Enter PEM pass phrase) is used to protect the exported key.

Written by:
Category: Blog entry
Published:

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 that script for all FAN events, but the script will start processing only for instance up event.

Why is it needed? We just switched over to 4-node RAC consisting of many different applications, almost each of them connecting to its own schema. We created each application its own service restricting it to 1 (or max 2) nodes (1 or 2 nodes as preferred, all other nodes listed as available). After the first rolling patching, I noticed that the connection count and load on each node was very unbalanced, vast majority of the connections were connected to node1 and the last patched node had almost none and it did not get better over a few hours. This was because most of the services ended up on node1 and I had to manually look over each service and relocate it back where it belongs. This script attempts to automate this process.

Tested on Oracle Linux 5.8 with Oracle Grid Infrastructure 11.2.0.3 and Oracle Database 11.2.0.2 and 11.2.0.3.

Written by:
Category: Blog entry
Published:

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 are moved to any other surviving node (least loaded)
  • Different MySQL instances may run different MySQL RDBMS software versions
  • Each MySQL instance is listening to its own dedicated and clusterware managed IP address (so MySQL can always respond on default 3306 port and can move independently around nodes without any dependencies to other MySQL instances)
  • Clusterware monitors each MySQL instance and in case of trouble automatically moves IP and starts instance on another cluster node

For this task I wrote some Bash scripts to help managing MySQL instances in Oracle Clusterware environment. All these scripts are available here: Google Code project page, also with documentation of the whole setup and how to use scripts: Documentation

All comments welcome!

Written by:
Category: Blog entry
Published:

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.

Written by:
Category: Blog entry
Published:

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: USER_LOC_LATITUDE and USER_LOC_LONGITUDE. They are used for storing users location.

Then, create an On Demand application process SAVE_USER_LOCATION. Create your necessary application logic in that process to handle the user location. The user location is available through application items USER_LOC_LATITUDE and USER_LOC_LONGITUDE.

And finally, include the following HTML code to your page. This uses APEX AJAX JavaScript API to call the created application process as soon as the users location becomes available for the browser. Please note also, that the browser asks for users permission for reporting the location.

<script type="text/javascript">

  if(navigator.geolocation) {
    navigator.geolocation.getCurrentPosition(function(position) {

      var get = new htmldb_Get(null, $x('pFlowId').value, 
        'APPLICATION_PROCESS=SAVE_USER_LOCATION', 0);
      get.add('USER_LOC_LATITUDE', position.coords.latitude);
      get.add('USER_LOC_LONGITUDE', position.coords.longitude);
      gReturn = get.get();
      get = null;

    });
  }

</script>

To continuously monitor user position, use the function navigator.geolocation.watchPosition instead of navigator.geolocation.getCurrentPosition.

Resolving coordinates to location name

Here is one package, that uses GeoNames.org database for resolving the location name. The package requires Oracle 11.2.

The geolocation package
One helper package, HTTP_UTIL, for downloading XML over HTTP

Ilmar Kerm