dbaStreet

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

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

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

RK01POOL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rramdas-us)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = RK01)
)
)

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

#!/usr/bin/perl

use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:Oracle:RK01POOL‘,
‘scott’,
‘tiger’,
) || 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.

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 ./genawrs.sh 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.

#!/bin/bash

if [ $# != 4 ]
then
echo “Syntax  genawrs.sh dbid instanceId startsnapid endsnapid”
exit 1
fi

l_dbid=$1
l_instid=$2
l_start_snapid=$3
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`
do
let l_next_snapid=$i+1;
l_awr_log_file=”/awrs/out/awrrpt_${2}_${i}_${l_next_snapid}.log”

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
SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_text
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOC
done

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 11.1.0.7 database (should work in 11.1.0.6 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

DECLARE
SERVLET_NAME VARCHAR2(32) := ‘orawsv’;
BEGIN
DBMS_XDB.deleteServletMapping(SERVLET_NAME);
DBMS_XDB.deleteServlet(SERVLET_NAME);
DBMS_XDB.addServlet(NAME     => SERVLET_NAME,
LANGUAGE => ‘C’,
DISPNAME => ‘Oracle Query Web Service’,
DESCRIPT => ‘Servlet for issuing queries as a Web Service’,
SCHEMA   => ‘XDB’);
DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
ROLENAME => ‘XDB_WEBSERVICES’,
ROLELINK => ‘XDB_WEBSERVICES’);
DBMS_XDB.addServletMapping(PATTERN => ‘/orawsv/*’,
NAME    => SERVLET_NAME);
END;
/

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

XQUERY declare default element namespace “http://xmlns.oracle.com/xdb/xdbconfig.xsd”;

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;

GRANT XDB_WEBSERVICES TO  rk01srv;

GRANT XDB_WEBSERVICES_OVER_HTTP 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)
/
CREATE OR REPLACE PROCEDURE get_name (
p_isbn          IN  Books.isbn%TYPE,
p_bookname OUT Books.BookName%TYPE) AS
BEGIN
SELECT BookName
INTO   p_bookname
FROM   Books
WHERE  isbn = p_isbn;
END;
/

Test access to the service

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

http://localhost:8080/orawsv/RK01SRV/GET_NAME?wsdl

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

#!/usr/bin/perl

#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
->uri(‘http://localhost:8080/orawsv/RK01SRV/GET_NAME’)
->proxy(‘http://localhost:8080/orawsv/RK01SRV/GET_NAME’)
->call (SOAP::Data->name(“GET_NAMEInput”),SOAP::Data->name(“P_ISBN-VARCHAR2-IN” => $l_isbn),SOAP::Data->name(“P_BOOKNAME-VARCHAR2-OUT” => “”))
->result;

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

./getbook.pl “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.

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.

development:
adapter: oracle_enhanced
database: RK01
username: scott
password: tiger

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.

In my previous post I have explained how to capture different statistics from the same table into temporary stattab’s for further analysis (Or to provide to support). In 10.2.0.4 and 11g, dbms_stats has some procedures that can help you get a report on the difference between the two sets of statistics.

DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB is one such procedure. Continuing from my last example where we created two separate stats tables stattab_old and stattab_new, you can now check the difference  between the two sets of stats by running the following sql statement

set long 500000 longchunksize 500000

select report, maxdiffpct from
table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(null,’DEPT’,'STATTAB_OLD’,'STATTAB_NEW’));

You will see an output similar to the one below (The stats are not really what you will see on a dept table, but you get the general idea)

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE	      : DEPT
OWNER	      : SCOTT
SOURCE A      : User statistics table STATTAB_OLD
	      : Statid	   :
	      : Owner	   : SCOTT
SOURCE B      : User statistics table STATTAB_NEW
	      : Statid	   :
	      : Owner	   : SCOTT
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME	SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

DEPTNO		A   1	    .000000203 YES  0	    3	 C105  C105  9363
		B   9	    .000000341 YES  0	    3	 C102  C10A  1465265
DNAME		A   21	    .000000203 YES  0	    6	 C4032 C4032 9363
		B   315     .003967048 YES  0	    6	 C4032 C4032 1465265
LOC		A   25110   .000039824 NO   0	    5	 C2061 C32D1 47114
		B   161368  .000006197 NO   0	    5	 C114  C33C3 1465265
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

NDV – Number of distinct values

Density – 1/NDV

Recently I wrote a perl script that adds partitions to an existing table
The scripts makes a few assumptions
- The table exists and is range partitioned on date
- There is one partition per day
- There is a local (pre-fixed or non pre-fixed) index on the table
- There is a partition (named tableshortname_default) that is the upperbound of the date range (This can be split into the partitions that we need).
- The partition names are of the format tableshortname_mmddyyyy

The script takes the following arguments
- The table full name
- The table short name (Which becomes the prefix for the partition names)
- The number of partitions to create

The script does the following
- Queries the data dictionary (user_tab_partitions) to find out the highest partition in the table (ie the highest date for which the partition has been created)
- Uses the perl function Add_Delta_Days (Which is the part of the perl library Date::Calc) to generate the future dates for which partitions need to be created.
- Generates partition creation scripts and executes them.
- When you create a table partition, oracle automatically creates the new local index partition too.

The script has been tested against an oracle 11g database.

#!/usr/bin/perl
#There are two arguments to this script
#The first argument is the tablename
#The second argument is the tableshortname used for prefixing the partition names
#The third argument is the number of days for which you want to create new partitions

#This program
#   Assumes that the table is partitioned on date with one partition per day
#   Assumes that the highest partition is the one defined with maxvalues
#   Assumes that the maxvalue partition is called tableshortname_default
#   Takes the tablename
#   Queries the data dictionary to find the partition that has the highest date
#   Creates alter table split partition statements to create the additional partitions (determined by the 3rd argument)
#When the table is split the local index automatically gets split
#But you will have to rename the index partition(So that it gets a good prefix) (The code for that has to be added to this script)

use DBI;
use Date::Calc qw(Add_Delta_Days);

if ( @ARGV < 3 )
{
print “Syntax : crpartitions.pl tablename tableshortname numberofpartitions \n”;
exit;
}

# Connect to the database
# LongReadLen and LongTruncOk are required to specify the buffer size
# For the high_value column from user_tab_partitions which is a long datatype

my $db = DBI->connect( “dbi:Oracle:OracleSid”, “username”, “password” )
|| die( $DBI::errstr . “\n” );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
$db->{LongReadLen}   = 5242880;
$db->{LongTruncOk}   = 0;

#Setup tablenames for target table
#Setup a tablename and tableshortname in upper case, to be used in queries

my $l_tablename=shift;
my $l_tableshortname=shift;
my $l_numpartitions=shift;

my $l_tablename_uc=uc($l_tablename);
my $l_tableshortname_uc=uc($l_tableshortname);

# Query user_tab_partitions to determine the highest partition
# And the date that sets the limit for rows that are in that partition

$l_highvalue = $db->selectrow_array(”
select high_value from user_tab_partitions
where table_name = ‘$l_tablename_uc’ and
partition_position = (select max(partition_position)-1 from user_tab_partitions
where table_name = ‘$l_tablename_uc’)
“);

# The output of the query is a string
# eg: TO_DATE(‘ 2008-11-16 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
# Parse the string to get the full date, year, month and day

$l_highdate = substr $l_highvalue,10,19;
$l_year     = substr $l_highdate,0,4;
$l_month    = substr $l_highdate,5,2;
$l_day      = substr $l_highdate,8,2;

# Loop through each new partition you need to create
# Use the Add_Delta_Days function from the Date::Calc perl library
# To add days to the start date to get the upper limit of dates that go into each partition
# Prepare the alter table split partition statement and then invoke the function createPartition
# to execute the statement

for ($i=1 ; $i <= $l_numpartitions; $i++) {
($n_year,$n_month,$n_day)=Add_Delta_Days($l_year,$l_month,$l_day,$i);

$l_alterstmt=”alter table “.$l_tablename_uc.” split partition “.$l_tableshortname_uc.”_default at (TO_DATE(‘”.$n_day.”-”.$n_month.”-”.$n_year.”‘, ‘DD-MM-YYYY’)) into
(PARTITION “.$l_tableshortname_uc.”_”.$n_month.$n_day.$n_year.”,partition “.$l_tableshortname_uc.”_default)”;

&createPartition($db,$l_alterstmt);

}

# Takes the statement passed in as argument and executes it
sub createPartition {

$ldb = shift;
$p_alterstmt=shift;
# execute alter table statement
my $sthd = $ldb->prepare(“$p_alterstmt”)
|| die(“could not prepare alter statement”. $sthd->errstr . “\n” );

$sthd->execute()
|| die(“could not execute drop statement”. $sthd->errstr . “\n” );

}

Below is what i think is the quickest (And possibly the only supported way to do this directly (instead of installing 10.2.0.2, finishing the grid control install and upgrading to 10.2.0.4))set of steps to install grid control 10.2.0.4, while using oracle database version 10.2.0.4 for the grid control management repository on Linux x86.

Typically you would install grid control 10.2.0.1, which installs a 10.1.0.4 database as the management repository, then you would upgrade enterprise manager to 10.2.0.4 and then eventually upgrade the database to 10.2.0.4. Apart from the numerous steps in this method you also take more time because the entreprise manager database is fully configured when you install 10.2.0.1 and then it takes even more time updating all this configuration when you install the 10.2.0.4 patch. In the method outlined below all the configuration is done when you run the configuration script in the end, saving you precious time.

• Install Oracle RDBMS 10.2.0.1
• Upgrade Oracle RDBMS to 10.2.0.4
• Create a listener
• Create a Database
  • When you create/configure the database make sure that, you DO NOT choose the option to enable database control for the instance. (If you choose to enable database control, you will have to de-configure it).
• Configure the Initialization parameters
  • Configure Initialization parameters
• Install Oracle Grid Control 10.2.0.1, software only.
  • Install Oracle Grid Control 10.2.0.1, software only, Subsection titled ” Enterprise Manager Grid Control Using an Existing Database“.
  • Here be extremely careful when you are configuring the em_using_existing_db.rsp file. When you are setting the value for the parameter s_reposPort=”1521″, remember to use the double quotes around the 1521 or else you will have issues with the installation.
• Install Oracle Grid Control 10.2.0.4, software only.
  • In the section of the documentation you were following above, follow instructions in “Step 7 Apply the 10.2.0.4 patch set to OMS“
• Apply the 10.2.0.4 patch set to the Agent on the management server
  • Follow instructions in “Step 9, “Apply the 10.2.0.4 patch set to Agent“
• Run the configuration scripts to configure grid control to use the already created 10.2.0.4 database.
  • Follow instructions in “Step 10, 11 Configure Enterprise Manager Grid control by running the ConfigureGc.pl….“

Instructions on how to upgrade this installation to Enterprise Manager 10.2.0.5 can be found in my followup post , Upgrading Enterprise Manager to 10.2.0.5.

Some Useful Metalink Notes for Grid control Installation and Upgrades

Documentation Reference for 10.2.0.5 Install and Upgrades

Installing Enterprise Manager 10.2.0.4 using an 11g Database for the repository

Steps to upgrade 10.2.0.2 or higher Repository to 11g

Install grid control 10.2.0.5 on enterprise linux 4

Install grid control 10.2.0.5 on enterprise linux 5

You can find samples of the rsp files below

em_using_existing_db.rsp

patchsetoms.rsp

Updated this post from Ubuntu Jaunty to Lucid.

Unless you have been living on one of the planets orbiting alpha centauri (or if you have nothing to do with computers, in which case you would not be reading this post), you must have heard of ruby on rails.

Below are the steps i went through to get ruby and rails installed and configured on a Ubuntu Jaunty (10.04) system.

Make sure that you have installed Oracle 11g or the Oracle 11g instant client on the Ubuntu server, before you start.

Get the ruby packages for ubuntu

sudo apt-get install ruby  ruby-dev  libopenssl-ruby1.8 irb ri rdoc

sudo apt-get install sqlite3

sudo apt-get install libsqlite3-ruby libsqlite3-dev

Download, Install setup Gems

sudo ln -s /usr/bin/gem1.8 /usr/local/bin/gem

Install Rails 2

sudo gem install rails

Download and install the ruby interface for oracle

Download ruby-oci8 from http://rubyforge.org/frs/?group_id=256

http://rubyforge.org/frs/download.php/69454/ruby-oci8-2.0.4.tar.gz

tar -xzvf ruby-oci8-2.0.4.tar.gz

cd ruby-oci8-2.0.4/

make

sudo make install

Get the Enhanced ActiveRecord adapter for oracle

wget http://rubyforge.org/frs/download.php/64456/activerecord-oracle_enhanced-adapter-1.2.2.gem

sudo gem install activerecord-oracle_enhanced-adapter-1.2.2.gem

You are all set to write and test your first ruby program using the rails framework .

Please see my followup post on writing a simple rails program with 11g