Fast Track: ElasticSearch on CentOS 6.x on DigitalOcean VPS

yum install wget
cd /opt
wget --no-cookies --no-check-certificate --header "Cookie:; oraclelicense=accept-securebackup-cookie" ""
tar xzf jdk-7u67-linux-x64.tar.gz
cd jdk1.7.0_67/
alternatives --install /usr/bin/java java /opt/jdk1.7.0_67/bin/java 2
alternatives --config java
alternatives --install /usr/bin/jar jar /opt/jdk1.7.0_67/bin/jar 2
alternatives --install /usr/bin/javac javac /opt/jdk1.7.0_67/bin/javac 2
alternatives --set jar /opt/jdk1.7.0_67/bin/jar
alternatives --set javac /opt/jdk1.7.0_67/bin/javac
rpm --import
vi /etc/yum.repos.d/elasticsearch.repo
name=Elasticsearch repository for 1.3.x packages
yum install elastic search
/sbin/chkconfig --add elastic search
service elastic search start
cd /usr/share/elasticsearch/bin/plugin -install polyfractal/elasticsearch-inquisitor
Posted in CentOS, ElasticSearch, Technical | Tagged , , , | Leave a comment

Installing MediaWiki 1.20.2 with Oracle 11g Express Edition

I encountered and worked around a couple of issues when installing MediaWiki 1.20.2 with Oracle 11g Express Edition as the database back-end. The solutions below can be also applied to MediaWiki 1.20.0 and 1.20.1.

I did the installation on top of Zend Server Community Edition, saving me the trouble of tinkering too much with apache, php and oracle drivers.

First out, the web installer did not accept the new Easy Connect string format, even though the help text encouraged such use. The Zend Server environment doesn’t play well with TNS based connect strings these days, so I worked around this by commenting out the validation code on line 90 and 91 includes/installer/OracleInstaller.php:

[roy@lonora02 installer]# diff OracleInstaller.php.orig OracleInstaller.php
< } elseif ( !preg_match( '/^[a-zA-Z0-9_\.]+$/', $newValues['wgDBserver'] ) ) {
< $status->fatal( 'config-invalid-db-server-oracle', $newValues['wgDBserver'] );
> // } elseif ( !preg_match( '/^[a-zA-Z0-9_\.]+$/', $newValues['wgDBserver'] ) ) {
> // $status->fatal( 'config-invalid-db-server-oracle', $newValues['wgDBserver'] );

The installer now accepted localhost/XE:POOLED quite nicely for my Oracle 11g XE database with Database Resident Connection Pooling (DRCP) enabled.

After a couple of attempts, I found that the installer failed to create a database user, so I created a user manually, I suppose this is a good practice in any event, based on maintenance/oracle/user.sql

[oracle@lonora02 ~]$ sqlplus "/as sysdba"

create user wikiuser identified by SECRET default tablespace users temporary tablespace temp quota unlimited on users;

grant connect,resource to wikiuser;

grant alter session to wikiuser;

grant ctxapp to wikiuser;

grant execute on ctx_ddl to wikiuser;

grant create view, create synonym, create table, create sequence, create trigger to wikiuser;

After installation successfuly completed, I found a bug that was introduced in MediaWiki 1.20.0, where an array would incorrectly translate to a variable thus breaking a lot of SQL queries and making the wiki all but unusable. Luckily, I was able to borrow an existing workaround from the Postgres database script and modified includes/db/DatabaseOracle.php to implode the array to a comma separated list before passing it on to the variable. I found that this problem occurred two places, around line 1165 and 1168.

[roy@lonora02 db]# diff DatabaseOracle.php DatabaseOracle.php.orig
< $ob = is_array( $options['GROUP BY'] )
< ? implode( ',', $options['GROUP BY'] )
< : $options['GROUP BY'];
< $preLimitTail .= " GROUP BY {$ob}";
> $preLimitTail .= " GROUP BY {$options['GROUP BY']}";
< $ob = is_array( $options['ORDER BY'] )
< ? implode( ',', $options['ORDER BY'] )
< : $options['ORDER BY'];
< $preLimitTail .= " ORDER BY {$ob}";
> $preLimitTail .= " ORDER BY {$options['ORDER BY']}";

I really think it’s great that the MediaWiki team has taken the time to support Oracle database, not too many open source products like this do. The bugs I found have been reported and hopefully these issues will be all fixed by the next stable release.

Posted in Oracle, PHP, Technical | 1 Comment

Open source software alternatives for Oracle database

Here’s a very short list of open source server software that I have found to run smoothly with an Oracle database back-end. All will work with the free Express Edition.

Blog or Photoblog

While a bit of an overkill for a personal or small organization blog,  Drupal is just about the only tool I have found that works well with an Oracle back-end. Combined with the right theme and an image gallery, it should also be able to run a photoblog.  I really wish someone would adapt tools like Pixelpost and WordPress which both make it very easy to get a good looking site running. There was a project named orablog that looked promising, but appears to have been abandoned years ago.


I was genuinely surprised by how easy it was to get up and running with phpBB  on Oracle 11g XE.


Whether you are small brochure style website or an elaborate e-commerce site, Drupal should be able to accommodate most or all of your requirements. Although a bit of an overkill, it can even be used for something like a personal blog or photoblog.


Mediawiki which runs Wikipedia supports Oracle database. See my blog post on Installing MediaWiki 1.20.2 with Oracle 11g Express Edition to work around a couple of issues I had with the current release.

Posted in Oracle, Technical | Tagged , , | Leave a comment

PHP and Oracle on Ubuntu

I always meant to let my php scripts on my Ubuntu server access Oracle databases, thus quite possibly making it into a Linux, Apache, Oracle and PHP server. I do wonder how you would smoothly pronounce the resulting acronym.

Anyway, I often have the feeling that open source systems and commercial databases are intentionally made to not play well together. Despite myself, I recently found a nice article on how to install Oracle 11g instant client and building php with support for OCI.

The relatively simple instructions are for Ubuntu 10.04 but seems to work quite nicely on my Ubuntu 12.04 server. With any luck I’ll be pulling all sorts of interesting data from Oracle 11g using PHP before the end of the day.

PHPOracle – Community Ubuntu Documentation.

Ps: Make sure you get the 64-bit version of the Oracle instant client if installing on a 64-bit system 🙂

Posted in Oracle, Technical | Tagged , , , | Leave a comment

Adding an additional listener to ODA

After deploying an Oracle Database Appliance, a standard set of production listeners are configured on bond0. This includes one listener on each of the virtual ip interfaces, and two SCAN listeners.

In my environment, I would very much like to have additional listeners on bond1. I use this as a management network, and find it convenient to let DBAs and operators run their tools over this network.

According to Oracle Support, there should not be any special considerations on ODA, and thus we can follow support note ID 1063571.1 just like we would with any other 11gR2 RAC infrastructure.

In my humble opinion, this particular support note isn’t too clear even if you have access to My Oracle Support, so I’ll describe the steps I used for my setup:

Production network on bond0 is
Management network on bond1 is

First out we need host file entries on both nodes for the new VIP interfaces:


# VIP HOSTNAMES oda1-vip oda2-vip oda1m-vip oda2m-vip

Next we add the virtual ip interfaces to CRS. The network resource (specified with the -k 2 option) should be create implicitly:


su – root
cd /u01/app/
./srvctl add vip -n oda1 -k 2 -A
./srvctl add vip -n oda2 -k 2 -A

We can verify that the network resource and the virtual ip interfaces have been created with crsctl:

[root@oda1 bin]# ./crsctl stat res -t |grep -E ‘net|vip’

The final step is to create the actual listener. Oracle reccomends that you do this with the  network configuration assistant netca. The command must be run from the grid user.

roy@kayna:~$ ssh oda1 -l grid -X
grid@oda1’s password:
Last login: Tue Oct 23 22:15:41 2012 from
/usr/bin/xauth: creating new authority file /home/grid/.Xauthority
[grid@oda1 ~]$ netca

Just select Listener confguration and then Add and walk through the wizard, selecting the correct network resource (subnet) along the way. Note that netca sees the original listener on  default port of 1521, and will not allow another on the same port. I went with a name of LISTENER_M and a port of 1522 for my configuration.

This will take care of both listener.ora and OCR configuration.

However, I really do want the new listener to be listening the default port of 1521. netca complains that another listener is already configured on this port, even though it’s not on the same interface. The workaround is to manually remove and recreate the listener with srvctl using the -s option.

su – grid
srvctl stop listener -l LISTENER_M
srvctl remove listener LISTENER_M
srvctl add listener -l LISTENER_M -s -p 1521 -k 2
srvctl start listener -l LISTENER_M

We’re all set, and can now see the new listener (along with the default listeners) on the correct interface and port using netstat.

[grid@oda1 ~]$ netstat -ln | grep 1521
tcp 0 0* LISTEN
tcp 0 0* LISTEN
tcp 0 0* LISTEN
tcp 0 0* LISTEN
[grid@oma1 ~]$ ssh oma2
[grid@oma2 ~]$ netstat -ln | grep 1521
tcp 0 0* LISTEN 
tcp 0 0* LISTEN
tcp 0 0* LISTEN
tcp 0 0* LISTEN

or with lsnrctl

[grid@oda2 ~]$ lsnrctl status LISTENER_M

LSNRCTL for Linux: Version – Production on 23-OCT-2012 22:47:00

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Version TNSLSNR for Linux: Version – Production
Start Date 23-OCT-2012 22:36:49
Uptime 0 days 0 hr. 10 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
Listener Parameter File /u01/app/
Listener Log File /u01/app/
Listening Endpoints Summary…
The listener supports no services
The command completed successfully

As you can see, the listener is up but supporting no services. Unless we want this to be a static listener, we need to tell the database to make it’s presence known.

We accomplish this by using the new LISTENER_NETWORKS parameter.

SQL> alter system set listener_networks = ‘((NAME=MGMT_NET)(LOCAL_LISTENER=oda1m-vip:1521)(REMOTE_LISTENER=oda2m-vip:1521))’ SID=’testdb1′ SCOPE=both;

System altered.

SQL> alter system set listener_networks = ‘((NAME=MGMT_NET)(LOCAL_LISTENER=oda2m-vip:1521)(REMOTE_LISTENER=oda1m-vip:1521))’ SID=’testdb2′ scope=both;

System altered.

A new look at the listener status will show that the instances are now properly registered:

Services Summary…
Service “TESTDB” has 2 instance(s).
Instance “TESTSB1”, status READY, has 1 handler(s) for this service…
Instance “TESTDB2”, status READY, has 1 handler(s) for this service…
The command completed successfully



Posted in ODA, Oracle, Technical | Tagged , , , , | Leave a comment

Restore database to new or redeployed ODA

Doing a restore from an RMAN backup to a freshly deployed Oracle Database Appliance seems like a conceivable scenario, so I figured it would be nice to have tried it at least once.

For this test, I have a backupset on /s01/backup/testdb/ and no backup catalog. I have deployed the ODA using the parameter file from the original deploy, so that network and grid infrastrcuture configuration as well as database and instance names are the same.

First, we need the DBID of the old database. One place to see it is in the file name of the control file auto backup. The first number is the DBID: testdb_c-60599356-20121022-03

We must also figure out the controfile autobackup format, I have it set to /s01/backup/testdb/testdb_%F

Similarly we need the location of the SPFILE on the new database, which we can find with show parameter spfile. In this case it’s +DATA/testdb/testdb.ora

Now we can shut down the instance on both nodes, and fire up RMAN on node 1 to restore the SPFILE.

set ORACLE_SID=testdb1
rman target /
set DBID =  60599356;
startup nomount;
restore spfile to pfile ‘/tmp/dummy.ora’ from autobackup;
shutdown immediate;
startup nomount pfile=’/tmp/dummy.ora’;
restore spfile to ‘+DATA/testdb/spfiletestdb.ora’ from autobackup;
shutdown immediate;

Ok, we first restored the spfile and converted it to a dummy pfile – just so we could get the newly deployed spfile out of the way, start the database from the pfile, and restore the spfile back to it’s original location. Next up is the control files:

startup nomount;
restore controlfile from autobackup;
shutdown immediate;
startup nomount;

That was rather easy. If you do have archive logs or backup pieces that are newer than the control file snapshot, they should now be cataloged:

catalog archivelog /s01/secondary_archive/2012_10_23_testdb_4445.arc;

I don’t have anything to add, so I’m all set for restore.

alter database mount;
restore database;
recover database;

Since I don’t have a copy of the most current archive log, I must then settle for an incomplete recovery and open the database with resetlogs;

alter database open resetlogs;

Database is back in business. Any changes between time of backup  and  time of failure are lost.

Posted in ODA, Oracle, Technical | Tagged , , | 1 Comment

Erase and Rewind – Redeploying Oracle Database Appliance (2.3.0 image)

After using an Oracle Database Appliance for a bit of testing, it was time to get it ready for production. I decided to start with a fresh deploy and re-initializing shared storage.

Note that this is the 2.3.0 base image, and that the procedure may differ somewhat for other versions.

The included script will pretty much just shut everything down, erase everything on the shared storage and reboot both nodes. It will leave the network configuration for the iLOM and most files stored on the system drives, including deployment configuration files you may have stored in /root/

It will remove network configuration and set the root password back to the default of welcome1.

[root@oma1 ~]# cd /opt/oracle/oak/onecmd
[root@oma1 onecmd]# ./

Please enter the root password for performing cleanup:
Re-enter root password:

About to clear up OAK deployment,public network connectivity will be lost,root password will be set to default and both nodes will be rebooted

Do you want to continue(yes/no):

The script starts by asking twice for the root password before requesting a final confirmation. Make no mistake, after this point all services will be shut down and all data will be erased!

After a few minutes, the script completes with

[root@oma1 onecmd]#
Connection to oma1 closed by remote host.
Connection to oma1 closed.

and we are ready to start a new installation.

The first thing we need to do is connect to the iLOM either by serial or ethernet, or connect a keyboard and monitor so we can set up the initial network configuration. The cleanup script will not mess with the current configuration of the iLOM ethernet interface, so web and ssh console should be available.

-> start /SP/console
Are you sure you want to start /SP/console (y/n)? y

Serial console started. To stop, type ESC (

Oracle Linux Server release 5.8 Kernel 2.6.32-300.32.3.el5uek on an x86_64

oak1 login: root
Last login: Mon Oct 22 15:53:20 from

[root@oak1 ~]# cd /opt/oracle/oak/bin/
[root@oak1 bin]# ./oakcli configure firstnet

To work around a bug with the we need to add a localhost entry to /etc/hosts on both nodes.

[root@oak1 ~]# echo localhost.localdomain localhost >> /etc/hosts

I’m connected to the iLOM serial console through ssh, so I have to reconnect to the newly configured interface to enable X support for the deployment GUI.  I have a configuration file stored in the /root folder, so I call the deploy using that file as parameter file.

Connection to oma1-ilom closed.
roy@kayna:~$ ssh oma1 -l root -X
root@oma1’s password:
Last login: Tue Oct 23 13:01:39 2012 from
[root@oak1 ~]# cd /opt/oracle/oak/bin/
[root@oak1 bin]# ./oakcli deploy -conf /root/oak_deploy.params
Log messages in /tmp/oak_1350990145126.log
Loaded configuration file /root/oak_deploy.params successfully
Running Oracle Appliance Manager

When deployment starts, depending on your network configuration, the deploy will sometimes make a mess of the default route.  If you see this line when running route

default * U 0 0 0 bond0

you will need to immediately delete the entry and set the correct default gateway (on both nodes), less the deploy may very well fail the ValidateEnv step, because it can’t access the DNS servers to check for the SCAN listener DNS entry.

[root@oak2 ~]# route delete default
[root@oak2 ~]# route add default gw

Hopefully the deploy will now complete successfully. If not, you can always jump back to the top, clean up the deploy and start fresh for another attempt.

Posted in General, ODA, Oracle | Tagged , , | 2 Comments

Designing an isolated front production network for ODA

With the introduction of Oracle Engineered Systems the DBA is moved somewhat closer to infrastructure design decisions, and expected to at least have some opinions on the network design requirements for the database.

As such, I’ve given some thought to how one can design a fully redundant production network for web applications with Oracle Database Appliance. I’m sure Oracle have some very clear ideas on how everything is meant to work together, but to me it was not all that clear. For a time, and even after reading all documentation and support notes related to ODA, I was convinced that the bonded interfaces meant setting up LACP or EtherChannel groups on the switches, thus requiring fully redundant distribution or core-level switches.

As it turns out, the ODA is factory configured with active-backup bonds. I’ve tested this to work well without any switch-side LACP/EtherChannel configuration, and each bonded connection may be split across two switches. In my tests, no simulated failure or network reconfiguration caused more than a a few hundred milliseconds worth of network outage.

This means that one can set up an isolated redundant front network for web applications using any layer two switches that are not inherently redundant.

Illustration of an isolated front production network

To avoid client connections taking the long way into the company network and back through the other switch (and thus making production dependent on that equipment), one can have a private VLAN that only lives on the two edge switches and on an EtherChannel trunk between them.

As such, only the application servers and the database appliance will exist on that virtual network segment.

I don’t see a way to control which path the connections from the application servers take to the database listeners, so the link between the two switches will have to be redundant, less this link becomes a single point of failure. This rules out using unmanaged switches without support for VLAN and either LACP or STP.

Using Cisco Catalyst 2960-series switches, I believe a combination of EtherChannel and Port Fast would be the better choice for a solid independent connection between the two. I would also use Port Fast on the ports for all the bonded connections to ODA and application servers.

Since the production network is isolated, one would need separate network connections for management, backup and connectivity to the rest of the company network.

Naturally, in order for this front production network to be fully self contained, any dependencies to external resources, such as DNS or authentication services, must also be resolved. Ideally production would be able to continue independently from any faults, ongoing maintenance or network outages anywhere else in the data center or company network.


Posted in ODA, Oracle, Technical | Tagged , , , | Leave a comment

Import data to a different tablespace using Data Pump

In a previous article I looked at how one could force imp to place user objects in the default tablespace of a user, in cases where the originating tablespace was not available in the destination database.

The newer Oracle Data Pump utilities, expdp and impdp provides a remapping option that lets you migrate data to a new tablespace during import. Data Pump was made the preferred way of doing imports and exports in Oracle 11g.

Data Pump reads and writes data to a directory on the database server, so the first thing we must do is define such a directory on both source and destination databases. The directory must be acessible by the oracle database.

SQL> CREATE DIRECTORY dpxfer1 AS ‘/u01/dpxfer1’;

We must also give the schema user access to the Data Pump directory:


Then we’re set for the actual export. A number of options from the exp utility are available and will automatically be replaced by their new expdp equivalent

expdp dbuser/password DIRECTORY=dpxfer1 DUMPFILE=dbuser.dmp LOGFILE=dbuser.log CONSISTENT=Y

Legacy Mode Parameter: “consistent=TRUE” Location: Command Line, Replaced with: “flashback_time=TO_TIMESTAMP(‘2012-10-15 14:43:06’, ‘YYYY-MM-DD HH24:MI:SS’)”

As soon as we have transferred the dump file /u01/dpxfer1/dbuser.dmp to the destination server, we’re ready to run the import with the REMAP_TABLESPACE option, in this case placing any objects from the USERS tablespace on the source database into the DBUSER table space on the destination database. Naturally the user and table space must already exist on the destination, and the data pump directory as well as the associated user privilege must be added just like we did on the source database.

impdp dbuser/password DIRECTORY=dpxfer1 dumpfile=dbuser.dmp logfile=dbuser_imp.log REMAP_TABLESPACE=users:dbuser


Posted in Oracle, Technical | Tagged , , , , | Leave a comment

Import data to a different tablespace using imp

In the old days, it was common to store objects from assorted users in the USERS tablespace. In more modern days, it sometimes seems more appropriate to store user objects in a tablespace dedicated to that schema.

So, as I was transferring data to a new Oracle server, I found myself wishing to import data into a different tablespace than on the source database.

While the impdp tool includes a remap option to take care of such a scenario, the traditional imp tool does not. A workaround is to force all user objects into the default tablespace of that user, however this only works if the source tablespace does not exist.

As an example, I have a user blog_data on OLDDB with it’s objects (in this case, a single table with a single row, and the associated primary key index) stored in the users tablespace.

I’ll export the schema using something like:

exp blog_data/secret@OLDDB file=blog_data.dmp

Then on the destination database, I need to create a user with appropriate privileges, default tablespace and make sure the USERS tablespace does not exist. Assuming this is a new installation, I can simply set a new default permanent tablespace for the database and delete the empty “USERS” tablespace:






GRANT connect,resource TO blog_data;

Now I can simply import the schema into my new user, forcing imp to place all data in the default tablespace:

[oracle@oratest1 impexptest]$ imp blog_data/secretpassword@NEWDB file=blog_data.dmp

Import: Release – Production on Fri Oct 12 23:15:44 2012

Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing BLOG_DATA’s objects into BLOG_DATA
. . importing table “PEOPLE” 1 rows imported
Import terminated successfully without warnings.

The import completes without warnings, and we can then see that the table is in the right place:

[oracle@oratest1 impexptest]$ sqlplus “blog_data/secretpassword”

SQL> select table_name,tablespace_name from user_tables;

—————————— ——————————

Posted in Oracle, Technical | Tagged , | 3 Comments