dbaStreet

Most of oracle’s MAA documentation for transportable tablespaces, seems to recommend that one should put the tablespaces one is transporting, in read-only mode in the source database, before copying the datafiles to the target. This in most cases means application downtime.

In order to minimize the downtime the recommendations seem to be

• Create a dataguard physical standby database and use this standby database as the source for transport
• Create a duplicate (aka clone) of your source and use this new duplicate as the source for transport

If you are only transporting a subset of your tablespaces and you want to minimize your downtime, a 3rd good option is to create and use transportable tablespace sets from your already existing rman backups. This process is documented in the Backup and Recovery users guide, Chapter 26 (11gR2 manual).

Since this process uses an existing rman backup you incur no downtime (ie no need to place tablespaces in read-only mode) on your production systems.

Below are the steps to accomplish this (In my example rk01 is the source database and rk02 is the target database).

• First off, your source database should be running in archivelog mode

• Take a full database backup from your source database

export ORACLE_SID=rk01
rman target /

run {

allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’;

backup as BACKUPSET tag ‘%TAG’ database;

backup as BACKUPSET tag ‘%TAG’ archivelog all not backed up;

release channel oem_backup_disk1;

}

run {

allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’ maxpiecesize 1000 G;

backup as BACKUPSET tag ‘%TAG’ current controlfile;

release channel oem_backup_disk1;

}

exit;

• Create a transportable tablespace set for the tablespaces you need to transport

export ORACLE_SID=rk01

rman target /

RMAN>  transport tablespace example

2> tablespace destination ‘/u01/orarch/rk01/datafile’

3> auxiliary destination ‘/u01/orarch/rk01/tmp’;

Once the process is complete rman leaves a copy of the datafile (An operating system file, not a backup set file) for the tablespace example , in the directory /u01/orarch/rk01/datafile. It also leaves a export dump file that has the metadata needed for the transport in the same directory /u01/orarch/rk01/datafile.

• Do endianness conversions on the files, if you need to go cross platform (Use rman convert)
• Attach the tablespace to your target database

export ORACLE_SID=rk02

sqlplus / as sysdba

create directory tts_dir as ‘/u01/orarch/rk01/datafile’

/

grant all on directory tts_dir to public

/

Exit;

Before you run the next import, make sure that you have created the schema’s (with appropriate privileges) that are in the tablespace you are transporting in the target database rk02, also make sure any roles that are required are created in the target. Eg: The schema HR has objects in the example tablespace. use the create user command to create the HR user with appropriate privileges in the database rk02.

impdp system/manager dumpfile=dmpfile.dmp directory=tts_dir transport_datafiles=/u01/oradata/rk02/example01.dbf logfile=tts_import.log

So As you can see the whole process is executed without shutting down the source database rk01.

On any database server, there is often only a limited amount of resources (read cpu, memory, i/o). Oracle database servers are often subjected to highly concurrent, resource consuming, queries issued by database client programs. This often leads to contention for cpu, memory and i/o resources on a server. Once you have way more processes on a system actively executing, compared to the number of cpu cores you have, at some point the scheduling of these processes becomes inefficient at the operating system level.

The oracle Database resource manager can be used to throttle the Number of oracle database sessions actively running on a database, or to limit the amount of cpu resources a session gets, or to limit the degree of parallelism each session gets among other things.

Below are the steps to setup a resource plan that

Limits, maximum Degree of Parallelism each session can have, at 64.

Limits the maximum number of Concurrent active sessions that can be present for a user to 2. (The other sessions get queued).

Create a Consumer Group

Login to Enterprise Manager, click on the “Server” tab.

Under the “Resource Manager” section, click on “Consumer Groups”

Click on the “Create” Button

Enter a Name and Description for the Consumer group

Click on the “Add” button to add a database user to the consumer group

Check the box to the right of the user you want to add and click on “Select”

Now click on the “Database Instance” bread crumb to return to the “Server” tab

Click on “Consumer Group Mappings”

Click the radio button on the row that has the value “Oracle User” in the “View” column.

Click on “Add Rule for Selected Type”

In the drop down list “Selected consumer group”, choose “DOP_GROUP”.

In the “Available Oracle User” list, click on the user you want to add to this “DOP_GROUP” group and click on “Move”

Click Ok.

Click on Apply

Click on the “Database Instance” Breadcrump to return to the “Server Tab”

Click on “Resource Plans”

Click on “Create” to create a new resource plan

From the table “Available Group/Subplans” choose “DOP_GROUP” and click on Move. Click Ok.

Click on the tab named “Parallelism”

For the DOP_GROUP set the maximum degree of parallelism to be 64

Click on the tab named “Session Pool”

Set the number of active sessions to “2″ (Or any number you want (In the screen i have used 12))

Click “Ok”

In the “Resource Plans” screen, choose the DOP_PLAN, In the actions drop down list, choose “Activate” and click on Go.

Now the resource plan “DOP_PLAN” is active.

Let us say now we submit a bunch of queries (Let us say 20) simultaneously (That use parallel query)as the database user HR then

Only 2 of them will be allowed to run at the same time (The rest are queued)

Each session gets a maximum Degree of parallelism of 64

There are two new awr reports in 11gr2, which will be helpful to dba’s in Real Application Clusters Environments (RAC).

awrgrpt.sql

This is a cluster wide awr report, so you can see a lot of the information from all the nodes in the same section, and you can also see aggregated statistics from all the instances at the same time (You can see totals, averages and standard deviations).

awrgdrpt.sql

This is a cluster wide stats diff report (like you had awrddrpt.sql in 11gr1), comparing the stats differences between two different snapshot intervals, across all nodes in the cluster.

These are huge additions to the awr reports, that enable understanding the database performance in real application clusters environments.

If you are attending Oracle Openworld 2009, and are interested in learning a lot about oracle Rac and Grid computing, you can find a full list of Oracle Rac and Grid computing events Here (Starting at page 2 of the pdf doc).

In preparation for the event, you could read the following new 11gR2 white papers from Oracle, to understand the latest developments and arm yourself with questions.

Oracle Real Application Clusters 11g Release 2 Technical Overview

Oracle Real Application Clusters 11g Release 2 Overview of SCAN

Oracle Real Application Clusters One Node 11g Release 2 Technical Overview

Sometimes you would want to give only database monitoring access to some grid control users. You dont want them to get all other administrative privileges, like shutdown database, create tables, alter tables, drop tables etc. You can create such administrators in enterprise manager grid control by following the steps below.

Whenever you want to monitor a database target, you need to be able to login as a user to that database. Sometimes you might be logging in as SYSTEM or some other user that has DBA privileges. So the first step we need to perform, is to create a user in the target database, that has only limited privileges.

sqlplus system@target

create user oem_view identified by xxx

default tablespace users temporary tablespace temp;

grant create session, oem_monitor to oem_view;

OEM_MONITOR is a role in the database, that has some specific privileges granted to it. If you do not want to grant all those privileges to this user, you can then query the data dictionary to see which privileges are granted to OEM_MONITOR and then decide which subset of that you want to grant to your user OEM_VIEW.

Once the user in the target database is created, you can use enterprise manager grid control to create the new grid control administrator.

Login to enterprise manager grid control as SYSMAN (Or any super administrator)

Setup -> Administrators -> Create

Remove the “Public” role that is listed in the right hand side table

Under Create Administrator: System Privileges select ‘VIEW ANY TARGET’.

Under Create Administrator: Targets, choose all the targets this new admin should be able to view

Click Apply.

Refernce : Metalink Note 377310.1

Login as this new administrator user you created and set oem_view as the username for the database target in preferred credentials.

Yesterday i completed a 11g Release 2 real application clusters installation on 64 bit Oracle Enterprise Linux 4. The installation process is very similar to the 10g and 11gr1 installations, but much simpler. This was a two node cluster. There are some new concepts that are introduced in 11gR2 real application clusters. Below are some of my notes on 11gr2 new features for Rac and detailed steps that i followed to complete the installation.

Some new concepts in 11gR2 Rac

Oracle clusterware and ASM now are installed into the Same Oracle Home, and is now called the grid infrastructure install.

Raw devices are no longer supported for use for anything (Read oracle cluster registry, voting disk, asm disks), for new installs.

OCR and Voting disk can now be stored in ASM, or a certified cluster file system.

The redundancy level of your ASM diskgroup (That you choose to place voting disk on) determines the number of voting disks you can have.
You can place

• Only One voting disk on an ASM diskgroup configured as external redundancy
• Only Three voting disks on an ASM diskgroup configured as normal redundancy
• Only Five voting disks on an ASM diskgroup configured as high redundancy
  

The contents of the voting disks are automatically backed up into the OCR

ACFS (Asm cluster file system) is only supported on Oracle Enterprise Linux 5 (And RHEL5), not on OEL4.

There is a new service called cluster time synchronization service that can keep the clocks on all the servers in the cluster synchronized (In case you dont have network time protocol (ntp) configured)

Single Client Access Name (SCAN), is a hostname in the DNS server that will resolve to 3 (or at least one) ip addresses in your public network. This hostname is to be used by client applications to connect to the database (As opposed to the vip hostnames you were using in 10g and 11gr1). SCAN provides location independence to the client connections connecting to the database. SCAN makes node additions and removals transparent to the client application (meaning you dont have to edit your tnsnames.ora entries every time you add or remove a node from the cluster).

Oracle Grid Naming Service (GNS), provides a mechanism to make the allocation and removal of VIP addresses a dynamic process (Using dynamic Ip addresses).

Intelligent Platform Management Interface (IPMI) integration, provides a new mechanism to fence server’s in the cluster, when the server is not responding.

The installer can now check the O/S requirements, report on the requirements that are not met, and give you fixup scripts to fix some of them (like setting kernel parameters).

The installer can also help you setup SSH between the cluster nodes.

There is a new deinstall utility that cleans up a existing or failed install.

And the list goes on an on.

I have broken up the installation process into 3 distinct documents, which can be found below

Installing 11gr2 grid infrastructure

Installing 11gr2 Real Application Clusters

Creating the 11gr2 Clustered database

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

Oracle has for a while had a rac assurance team (A team within oracle support, in the HA/Rac support team) that engages proactively with new rac customers. The Rac Assurance team used to provide the new customers with a “starter kit” of documents that  include

1. A Rac best practices document
2. A Step by Step installation guide
3. Recommended patches
4. A test plan.

If the customer follows these best practices, it sets them up with a solid foundation to be successful with their new  Rac implementation.

Now these test kits are public, you can access them by accessing metlink note 810394.1

You can also log a tar in metalink and ask support for the “Rac Starter Kit”, and they will give you the platform specific starter kit that includes the list of recommended patches.

This is a simple calculator (Written using Javascript), that can be used to estimate the potential cost savings (As a result of reducing your total storage costs)  of using a Information Lifecycle Management (ILM) Strategy to store and maintain your oracle data.
When architecting your ILM strategy you may decide to use the following different types of storage

• High performance tier where all the important and frequently accessed data is stored (Smaller faster disks).
• Low cost storage tier where the less frequently accessed data is stored (Larger slower, ATA disks).
• Online archive storage tier where all the data that is hardly or never accessed is stored (Low cost ATA disks).

Once you decide your classifications you could partition your tables accordingly (And then place different tablespaces on different type of storage) and then use Oracle ILM Assistant to maintain the tables and their data.

This calculator can be used to estimate the cost savings you might get by implementing such a strategy. (Remember this is only a very rough first estimate. Use values that you have received from your hardware vendor to use as inputs for cost per Gb of different tiers of storage).

Click Here to Access the ILM Calculator.

All comments and suggestions, for corrections and improvements  are welcome.

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.