Monthly Archives: May 2009

11g Rac Installation Checklists

In my numerous engagements with customers, assisting them with Oracle Rac installations, i have found that providing the customer with a checklist of tasks to be performed before the installation, and reviewing it with their Database/Server/Storage/Network  Administrator’s  help in a smooth RAC installation.The checklist only lists the tasks, but does not go into step by step details regarding how to perform the task. For the details, i then point them to the Oracle Installation and Configuration Guides (Links to which can be found in the checklist itself) and relavent metalink notes.

Below are links to the checklists that I use for 11g Installations on Linux and Solaris. All pointers to errors and improvements are welcome.

Oracle Real Application Clusters, 11g Installation Checklist for Redhat Enterprise Linux 5 and Oracle Enterprise Linux 5

Oracle Real Application Clusters, 11g Installation Checklist for Solaris

A look inside an Oracle DBA’s performance troubleshooting toolbox

From time to time, anyone who is an Oracle Database Administrator, would get summoned to troubleshoot a “database performance” issue. Once he/she has properly qualified and defined the performance issue, he/she settles down at his/her  laptop/desktop/netbook, accesses  ssh, sqlplus, enterprise manager and gets on their merry way trying to identify the root cause(s) of the performance issue and rectifying  the issue (At the same time twittering and posting facebook wall messages) (Maybe log a tar for the issue too).

In this process we often resort to using the numerous tools/concepts/techniques that are at our disposal to troubleshoot the issue. Over the years, i have had the privilege of using most of the tools/concepts/techniques that i have outlined below. These are just some of the tools and techniques that get you the best results quickly. And i know that the list is by no means exhaustive. Just wanted to give a shout out to all the DBA’s out there who carry this enormous amounts of information in their heads (And share a lot of it in blogs, collaborate,user group and openworld presentations) and apply the appropriate tools to resolve oracle performance issues.

Tools/Techniques

– AWR (Automatic workload repository)
– ADDM (Automatic database diagnostic monitor)
– ASH (Active session history)
– Real Time Sql Monitoring
– Optimizer statistics (dbms_stats)
– Extended Statistics
– Time Model Statistics
– Histograms
– Explain plan (dbms_xplan,gather_plan_statistics)
– event 10046 trace
– Tkprof
– event 10053 trace
– Oracle diagnostics pack
– Oracle tuning pack
– Sql profiles
– Sql Plan Baselines
– Stored outlines
– Optimizer Hints
– Oracle Real Applications testing (Database Replay, Sql Performance Analyzer)
– Oracle Advanced Compression (Reduces Number of blocks read)
– Oracle parallel query
– Indexes
– Partitioning
– Index Organized tables
– Clusters
– SGA Sizing/configuration
– Initialization parameters
– Cursor sharing
– SQLTXPLAIN (Metalink script)
– TRCANLZR  (Metalink script)
– OLAP cubes
– Materialized views
– Automatic storage management (ASM)
– Server Results Cache
– OCI Consistent Client Cache
– Logical/Standby , Active Dataguard (Offload queries)
– Asynchronous I/O, Direct I/O

– Table/Index Rebuild

– Patches for performance bugs

Concepts

– Database Non Idle Wait Events
– v$ views
– x$ tables
– SQL query structure/rewrite
– Sql tuning sets
– Star Schema Design

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 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.