Using Python 3

I have been writing some python scripts for awr analysis and trending. Since python 2.7 is no longer being enhanced, i have now switched to using python 3. Lot of python applications and frameworks still does not support python 3 (Notably the Django framework). Good news is that cx_oracle works with python 3.

The steps to install cx_oracle with python 3 are very similar to the steps that i had outlined in my previous post on installing cx_oracle with python 2.7.

The difference is that

– You have to first install python3 and python3-dev (On ubuntu, you can just use the ubuntu software center to do this)

– Then download the cx_oracle 5.1.1 source code only tar ball from

– login as root, untar the tar file, cd to the cx_Oracle-5.1.1 directory

– Then run /usr/bin/python3 install

That does it and now oracle connectivity is in place.

I’ve also been using the matplotlib library along with Python to plot graphs with the awr and oswatcher data files. matplotlib also works with python 3.

– You have to first install libpng, libpng-dev, libfreetype6, libfreetype6-dev (Use the ubuntu software center)

– Download the numpy source code tar ball.

– Extract the tar file, login as root, cd to the directory and run /usr/bin/python3 install

– Installing matplotlib Ref :

– Download the matplotlib source code tar file

– Login as root, cd to the directory

– /usr/bin/python3 build

– /usr/bin/python3 install

Now you should have matplotlib working with python3

Enjoy your python scripting

Installing Ruby 1.9.2 And Rails 3.1.1 with Oracle on Ubuntu 11.10 Oneiric

Here are the steps to install and configure Ruby on rails with oracle on 32 bit Ubuntu 11.10 Oneiric.

First install the pacakges needed by oracle

sudo apt-get install x11-utils rpm ksh lsb-rpm libaio1
sudo ln -s /usr/include/i386-linux-gnu/sys /usr/include/sys

Download the oracle instant client

Download the following .zip files from the oracle instant client download site.

Install the oracle InstantClient

Create a directory /u01/11gr2

cd /u01/11gr2

unzip the above 3 .zip files into this directory

You will have a new subdirectory named instantclient_11_2

Create a softlink to

cd /u01/11gr2/instantclient_11_2
ln -s


Setup the Oracle environment

Add the following to your .bashrc file (And source the file, . ./.bashrc)

export ORACLE_HOME=/u01/11gr2/instantclient_11_2


Create the tnsnames.ora file in /u01/11gr2/instantclient_11_2

Add service name entry for your oracle database  to the tnsnames.ora

RK01 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = burl5vb1)(PORT = 1521))
      (SERVICE_NAME = rk01)


Install Ruby 1.9.2

sudo apt-get install curl
sudo apt-get install git-core

git config --global "YourNameHere"
git config --global user.emailbash YourEmailHere

bash << (curl -s

sudo apt-get install build-essential bison openssl libreadline6 libreadline6-dev curl git-core zlib1g zlib1g-dev libssl-dev libyaml-dev libsqlite3-0 libsqlite3-dev sqlite3libxml2-dev libxslt-dev autoconf libc6-dev ncurses-dev

rvm install 1.9.2
rvm --default use 1.9.2

Install Rails 3.1.1

gem install rails

Installing and using the oracle driver.

You can include the download and install of the oracle-advanced driver and the oci8 driver in the Gemfile for your application.

So that when you do the bundle install, it will install those gems for you.

Example shown below.

rails new testora
cd testora


Add the following lines to your Gemfile (In the application base directory)

gem 'activerecord-oracle_enhanced-adapter', :git => 'git://'
gem 'ruby-oci8', '~> 2.0.6'

Save and quit from Gemfile

Run the following command to install all the gems you need for the application

bundle install

Remove all the other entroes and add the database connection entry to your database.yml file (Under testora/config).

  adapter: oracle_enhanced
  database: rk01
  username: scott
  password: tiger

Create your application and run it

rails generate scaffold purchase name:string cost:float
rake db:migrate
rails server

You can access the application from the following URL.


Now you should be able to run your application.

Rman, duplicate from incrementally updated backup

In the last post i addressed how to create and maintain an incrementally updated rman backup, and how to switch to the backup, in case some corruption happens to the production database.

In this post, instead of doing the switch, we will use the incrementally updated backup to create a cloned copy of the original database. The practical use of this method will be that, if you are creating incrementally updated backups every night, you can use the backup as the source to clone the database (ie you dont have to run a whole backup of your database again).

rman has a command named duplicate that allows you to clone an existing database.

In this case we are cloning a database named rk01, to another database named rk02 on the same host, using a incrementally updated backup, with the rman duplicate command.

First follow the steps to create the incrementally updated backup from my previous post.

Enable Archivelog And Block Change Tracking
Run a level 0 full image copy backup of the database
Perform updates in the database
Perform an incremental level 1 backup
Apply the incremental level 1 backup to the level 0 image copy

At this point after the incrementally updated backup is created, in the previous post, i was doing a switch. Instead of that we will do the rman duplicate.

Create a spfile for the rk02 database (I usually make a pfile from spfile from rk01, edit the pfile change all the occurances of rk01 to rk02 and create the spfile for rk02 from this pfile. Also create the necessary directories for bdump,adump etc.)

Create a password file for the rk02 database (orapwd file=$ORACLE_HOME/dbs/orapwrk02 entries=30)

Startup the database rk02 in nomount mode

export ORACLE_SID=rk02

sqlplus / as sysdba

startup nomount

Switch the current logfile in the rk01 database

This is an important step. Miss this and your rman duplicate will likely fail. The reason is that the scn until which the incremental backup is taken (And was applied to the original backup copy), is still in the online redo log. When you do a duplicate rman looks for a backup that has a ckp scn that is < the first_change# in your current online redo log. Once you do a switch, and then run the duplicate, rman will be able to find the incrementally updated backup as the source to clone from.

export ORACLE_SID=rk01

sqlplus / as sysdba

alter system archive log current;

Perform the rman duplicate.

Then issue the rman duplicate command (If you are cloning to a physically separate host, you will have to create appropriate listener.ora entries, tnsnames.ora entries and make the location of the backup copy available to your target host).

export ORACLE_SID=rk02

run the following script.

rman target="sys/manager@rk01" auxiliary / log=/u01/oraback/clonefromiub.log << EOF
  SET NEWNAME FOR DATAFILE 1 TO '/u01/oradata/rk02/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/u01/oradata/rk02/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/u01/oradata/rk02/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/u01/oradata/rk02/users01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/u01/oradata/rk02/example01.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/rk02/temp01.dbf';
      GROUP 1 ('/u01/oradata/rk02/redo01a.log',
               '/u01/oradata/rk02/redo01b.log') SIZE 4M REUSE,
      GROUP 2 ('/u01/oradata/rk02/redo02a.log',
               '/u01/oradata/rk02/redo02b.log') SIZE 4M REUSE;

Rman, incrementally updated backups, switch to copy

Some oracle customers use SAN based technologies to keep a backup copy of the database in sync (Think EMC Timefinder and BCV’s). The typical backup strategy tends to be, to keep a full copy of the database, and on a daily basis to update this backup copy of the database (Typically a BCV synchronize  operation), with only the incremental changes that have happened at the disk level.

In case off a loss of file system in the primary disks, they can switch the database to run from another copy which is maintained as a backup by the Storage subsystem.

A similar strategy can be implemented for  oracle databases, using RMAN incrementally updated backups (Without requiring any expensive SAN level software for maintaining these copies). These backup copies can be updated on a daily basis using just the incremental changes in the database. In case of a loss in the database, you can switch to this copy to be used as the database. You can also generate clones from these backups using the rman duplicate command.

At a high level the steps involved are

  • Configure the database to run in archivelog mode
  • Configure block change tracking at the database level
  • Create a level 0 full image copy of the database using rman
  • Create level 1 incremental backups of the database and update the level 0 copy with this incremental backup
  • In case of need, use the rman switch command to switch the database to this incrementally updated backup.

Below is an example of how this is done.

Enable Archivelog and Block Change Tracking

sqlplus / as sysdba
startup mount;
alter database archivelog;
alter system set log_archive_dest_1=’LOCATION=/u01/orarch/rk01′ scope=spfile;
alter database open;
USING FILE ‘/u01/orarch/rk01/rman/rman_change_track.f’ REUSE;

Run a level 0 full image copy backup of the database.

A sample script to run the full image copy backup is below
rman target=”sys/manager@rk01″ log=/u01/oraback/fullcopy.log << EOF
DISK FORMAT ‘/u01/oraback/%b’;
DISK FORMAT ‘/u01/oraback/%U’;
In the above script there are two distinct rman scripts.
The first one backs up the database and the second one backs up the control file and archivelogs.
I am using the %b format string for the database backup to keep the database file names intact (In preparation for a switch) (You dont have to keep the datafile names the same, just makes it easy to understand).
You cannot do controlfile and archivelog backups using the %b format string. This is why i have two separate scripts.
I tag the backup with a name ‘fullcopy’, which later helps us with applying the incremental backup to this tag.

Now perform some updates in the database. (This simulates database changes, after which we can run an incremental backup).

sqlplus hr/hr@rk01 <<EOC
set echo on;
select employee_id,first_name,last_name from
employee_id = 194
update employees set last_name=’Adams’
select employee_id,first_name,last_name from
employee_id = 194
I updated the last_name for the employee number 194.

Now perform an incremental level 1 backup of the database

rman target=”sys/manager@rk01″ log=/u01/oraback/incr.log << EOF
DISK FORMAT ‘/u01/oraback/%U’;

Now Apply the incremental backup to the original level 0 image copy

rman target=”sys/manager@rk01″ log=/u01/oraback/incrapply.log << EOF
DISK FORMAT ‘/u01/oraback/%U’;
Run the following commands to capture the location of the datafiles for the database
SQL> select file_name from dba_data_files;

Now switch the database to run from the copy

Shutdown and startup and mount the database; (Do not open the database)
oracle@rramads-us2:~$ rman target /
Recovery Manager: Release – Production on Sat Nov 6 17:44:40 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: RK01 (DBID=2035601811, not open)
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy “/u01/oraback/system01.dbf”
datafile 2 switched to datafile copy “/u01/oraback/sysaux01.dbf”
datafile 3 switched to datafile copy “/u01/oraback/undotbs01.dbf”
datafile 4 switched to datafile copy “/u01/oraback/users01.dbf”
datafile 5 switched to datafile copy “/u01/oraback/example01.dbf”
RMAN> recover database;
Starting recover at 06-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oraback/system01.dbf
destination for restore of datafile 00002: /u01/oraback/sysaux01.dbf
destination for restore of datafile 00003: /u01/oraback/undotbs01.dbf
destination for restore of datafile 00004: /u01/oraback/users01.dbf
destination for restore of datafile 00005: /u01/oraback/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oraback/14lsbd4b_1_1
channel ORA_DISK_1: piece handle=/u01/oraback/14lsbd4b_1_1 tag=FULLCOPY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-NOV-10
RMAN> alter database open;
database opened
Now if you query the file_names they will point to the /u01/oraback directory

SQL> select file_name from dba_data_files;
Check if the changes we made to the hr.employees table are still there (To ensure that the incremental backup did work).
SQL> select last_name from hr.employees where employee_id = 194;

Installing cx-oracle with on ubuntu

cx_Oracle is a Python extension module that allows access to Oracle databases and conforms to the Python database API specification. Below are the steps i used to setup cx_Oracle, with, python 2.6 on Ubuntu 10.04 (Lucid Lynx).

1) Installed Oracle Enterprise Edition on Ubuntu (You can also configure this by just installing the instant client too)

2) Download cx_Oracle Source code from

3) Install python-dev on ubuntu (Or else you will get compile errors (Like file Python.h not found) when you try to install cx-oracle)

– sudo apt-get install python-dev

4) Login as root

5) export ORACLE_HOME=/u01/11gr2/db_1;export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

6) cd <dir-where-cx-oracle-untarred>/cx_Oracle-5.0.4

7) python install

Once it is installed you can run the sample program from to make sure it works.

Date and Time Arithmetic in the bash shell

Here are some ways to add hours, days, months etc to a given date and get the result from the bash shell

To add seconds to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 seconds”
Thu Aug 12 17:30:45 EDT 2010

To add minutes to a given date in the bash shell you can

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 minutes”

Thu Aug 12 17:45:30 EDT 2010

To add  hours to a given date in the bash shell you can

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 hours”

Fri Aug 13 08:30:30 EDT 2010

To add days to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 days”

Fri Aug 27 17:30:30 EDT 2010

To add months to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 months”
Sat Nov 12 17:30:30 EST 2011

Perl and database resident connection pooling

If you use perl with oracle 11g databases, you should consider using database resident connection pooling to reduce the overheads associated with connecting and disconnecting from oracle. Much has been written about how Php applications benefit by using database resident connection pooling (Because Php does not have a connection pooling mechanism of its own, unlike Java). Similar benefits can be derived by Perl Applications Too.

Mostly perl 5 applications will be using DBI and DBD – Oracle to interact with oracle databases. Since DBD – Oracle uses OCI to communicate with the oracle database, it can benefit by using database resident connection pooling.

When the database is configured for database resident connection pooling, the oracle database creates and maintains a pool of database connections. These connections are then shared by applications connecting to the oracle database. The advantage of this is that the connections are already created, so you do not incur the overhead of establishing a brand new connection to the database. You are just reusing an existing one. This is especially helpful if you have an application that establishes connections and disconnects from the oracle database very rapidly/frequently.

A connection pool can be configured and started in the database as follows

SQL> execute dbms_connection_pool.configure_pool(null,minsize=>2,maxsize=>4);

SQL> execute dbms_connection_pool.start_pool;

A connect string can be configured in the tnsnames.ora to connect to this connection pool using the following syntax

(ADDRESS = (PROTOCOL = TCP)(HOST = rramdas-us)(PORT = 1521))

The perl program can then establish the connection to the database using this connect string in tnsnames.ora


use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:Oracle:RK01POOL‘,
) || die “Database connection not made: $DBI::errstr”;

Thats all it takes, and now you can reap the benefits of using oracle database resident connection pooling with Perl.

You can use dbms_connection_pool.stop_pool to stop the connection pool in the database.

You can use the data dictionary views dba_cpool_info, and dynamic views v$cpool_cc_info, v$cpool_cc_stats, v$cpool_stats to monitor database resident connection pools.

Shell script to generate awr reports for a range of snapshots

We have been doing some performance testing in the recent days. We were running some workload using an application, capturing awr snapshots every 15 minutes on a 8 node rac cluster. I needed to generate the awr reports between each of the snapshots for each of the nodes. Running awrrpt.sql for each 15 minute intervals for each one of the 8 nodes would have been a tedious task. So i wrote a bash shell script that generates those awr reports.

It takes the database id, instance number, start snapshot id and end snapshot id as arguments (you can query dba_hist_snapshot to find out the start and end snapshot id’s you want to use). The directory in which it generates the reports is hardcoded in the script. The script has to be run as the oracle user  and it logs in as sys to generate the reports (You can modify as you need if you want it to be run as a different user). I have tested it only on Linux.

You can run it as ./ dbid  instancenumber beginsnap endsnap, to generate the reports.  This means that you have to run it once for each instance you need the report for. The script can be found below.


if [ $# != 4 ]
echo “Syntax dbid instanceId startsnapid endsnapid”
exit 1

let l_end_snapid=$4-1

# For all snapshot id’s
# Set the next snapshot id as current snapshot id + 1
# Spool a log file
# Log into sqlplus and call dbms_workload_repository.awr_report_text
# To generate the awr

for i in `seq $l_start_snapid $l_end_snapid`
let l_next_snapid=$i+1;

sqlplus -s / as sysdba << EOC
set head off
set pages 0
set lines 132
set echo off
set feedback off
spool $l_awr_log_file
spool off

11g database web services

Many applications today are architect ed and built to conform with  the principles of Service Oriented Architecture . When implementing SOA, many loosly coupled services are built and are orchestrated to work together to deliver a business function. The services can be built using any programming language, like c#, java, perl etc.  Usually the programs that consume the services (Or services that consume other services), communicate with the service using the standard protocol named SOAP (Simple Object Access Protocol) .  SOAP messages use standard XML messages that conform with the SOAP specification.

The description of a service, the location of the service and the operations the service exposes are exposed in form of an XML document named WSDL. Typically the deployment of services, use an Application server like Oracle Fusion middleware, Jboss etc.

In oracle 11g  database, you can create and deploy web services right from the database, without really using any application server. The steps to accomplish this in an database (should work in too) are below. This is all possible because XMLDB in the database, comes with a protocol server, which supports the HTTP(S) protocol. So we can enable HTTP access to the database.

Setup the http port

Login as sys to the database

EXEC dbms_xdb.sethttpport(8080);

Configure the web services servlet

SERVLET_NAME VARCHAR2(32) := ‘orawsv’;
DISPNAME => ‘Oracle Query Web Service’,
DESCRIPT => ‘Servlet for issuing queries as a Web Service’,
SCHEMA   => ‘XDB’);
DBMS_XDB.addServletMapping(PATTERN => ‘/orawsv/*’,

You can run the following query from sqlplus  to determine if the configuration was successful

XQUERY declare default element namespace “”;

Create the User that will own the pl/sql procedures that will be used as the web services.

create user rk01srv identified by rk01srv
quota unlimited on users;

grant connect,create table,create procedure to rk01srv;



Create the table and  Pl/Sql procedure

Drop table Books
Create Table Books
Isbn        Varchar2(15),
BookName    Varchar2(100),
Author        Varchar2(100)
alter table Books add constraints pk_books primary key (Isbn)

Insert into Books (BookName,Isbn,Author) Values (
‘Learning Perl’,’0596004788′,’Randal L. Schwartz’)
Insert into Books (BookName,Isbn,Author) Values (
‘Perl Cookbook’,’1565922433′,’Tom Christiansen’)
Insert into Books (BookName,Isbn,Author) Values (
‘Programming perl’,’0937175641′,’Larry Wall’)
Insert into Books (BookName,Isbn,Author) Values (
‘Agile Web Development with Rails’,’1934356166′,’Sam Ruby’)
Insert into Books (BookName,Isbn,Author) Values (
‘Advanced Rails Recipes’,’0978739221′,’Mike Clark’)
Insert into Books (BookName,Isbn,Author) Values (
‘Code Complete’,’0735619670′,’Steve McConnell’)
Insert into Books (BookName,Isbn,Author) Values (
‘Art of Computer Programming Volume 1′,’0201038013’,’Donald E Knuth)
p_isbn          IN  Books.isbn%TYPE,
p_bookname OUT Books.BookName%TYPE) AS
INTO   p_bookname
FROM   Books
WHERE  isbn = p_isbn;

Test access to the service

Invoke your browser and type in the following URL to display the WSDL for the service named Get_Name


You will be prompted for the username and password. This is the database username and password. Enter rk01srv for the username and password.

Create a perl script  that calls the service

You need to have installed the perl library SOAP::Lite. This library has methods you can use to generate SOAP calls to web services.

The full script can be found belowCreate a perl script  that calls the service


#use SOAP::Lite;
use SOAP::Lite ;
$l_isbn = shift;

sub SOAP::Transport::HTTP::Client::get_basic_credentials {
return ‘rk01srv’ => ‘rk01srv’;

print “The response from the server was: “;
print SOAP::Lite
->call (SOAP::Data->name(“GET_NAMEInput”),SOAP::Data->name(“P_ISBN-VARCHAR2-IN” => $l_isbn),SOAP::Data->name(“P_BOOKNAME-VARCHAR2-OUT” => “”))

The method ” SOAP::Transport::HTTP::Client::get_basic_credentials”, from the SOAP::Lite library is used to setup the username and password to be passed to the SOAP call.

You have to specify the URL for the soap service and the proxy name for the soap service (The theory being that one proxy can host multiple services). Then you specify the service name  (SOAP::Data->name(“GET_NAMEInput”))and each of the arguments (SOAP::Data->name(“P_ISBN-VARCHAR2-IN” => $l_isbn),SOAP::Data->name(“P_BOOKNAME-VARCHAR2-OUT” => “”)), exactly as the WSDL displayed those element names (Note that the names are different from the actual procedure name and the argument names we defined in Pl/Sql).

Then you can run the perl script as following

./ “0201038013”

It will print out the book name that matches that ISBN code.

So using web services in the database is a good way of creating multiple loosely coupled services which can then be accessed by web based applications. The good part is that we just had to write the logic in Pl/Sql, the database took care of enabling the web service for the procedure. So this reduces a lot of work.

Cautionary note : In the example above, I use http for the protocol. This means that the database username and password you provide is transmitted to the web server in clear text, which means that someone could sniff the network and read your packets and get your username and password. So you should consider using https instead.

Simple Rails Program

Thought building applications  using Microsoft Access was easy ? try building one in Rails, it is Easy…

Install and configure ruby and rails as described in my earlier post.

First create a directory to hold your Rails code. Let us say /u01/ror

cd /u01/ror

Issue the following command to create the necessary rails directory structure

rails test

cd to the config directory

cd test/config

Edit the database Connection properties file

vi database.yml

Modify the details for the development environment as follows (You can remove test and production). Make sure you use oracle_enhanced as the adapter, and not just oracle.

adapter: oracle_enhanced
database: RK01
username: scott
password: tige

cd /u01/ror

Create the Model/View and Controller

script/generate scaffold Customer name:string contact:string phone:string email:string

Create the table named Customers in the Oracle database

rake db:migrate

Start the web server

ruby script/server

Then you can access the application you just created by going to your browser and putting in the URL http://localhost:3000/customers.

This application can now view/add/modify/delete customer information

“That was easy”

Simply Rails 2” by patrick lenz is a great book for Rails beginners and includes Rails 2 information.