My blog has moved to wordpress

I have moved my blog to a new location.
Now you can find me here:
http://sysdba.wordpress.com

Pls spread the news,
google has kicked me out now!
=;-)

Automatic Memory Management in Oracle 11g

Since quite a few days I had the chance to look at parts of the beta documentation for the Oracle 11g database and since a few days I also have the beta software.

One of the new features coming up is the enhencement of automatic memory management.

The whole automatic memory thing had already started with the release Oracle 9i where the parameter SGA_MAX_SIZE was introduced, to limit the maximum memory Oracle can use for the SGA. Within SGA_MAX_SIZE a number of caches were dynamically, but still manually adjustable. These caches included the
DB BUFFER CACHE, the
SHARED POOL, the
JAVA POOL and the
LARGE POOL.

In those days the DBA had to check statistics in v$views in order to find out if the caches needed manual adjustment.

Starting with Oracle 10gR1 the new parameter SGA_TARGET was introduced which allowed us to limit the amount of SGA_MAX_SIZE which can be used by Oracle.
If you set SGA_TARGET to a value other than 0 (zero) then Automatic Shared Memory Management (ASMM) is enbaled in 10g. This meens that we allow Oracle to adjust these caches as the workload needs it. We can increase this value dynamically, but manually by adjusting SGA_TARGET up to SGA_MAX_SIZE. In most cases it does not make sense to set SGA_TARGET to a value different from SGA_MAX_SIZE. This is only for systems like a SUN FIRE which allow the dynamic reconfiguration of the server (adding memory boards while the server is running).
Within SGA_TARGET a number of caches are autotuned, including the
DB BUFFER CACHE, the
SHARED POOL, the
JAVA POOL, the
LARGE POOL and starting from 10gR2 also the new in 10gR1
STREAMS POOL.
At startup time SGA_TARGET is allocated and first of all the non dynamically adjustable caches get their catch. Afterwards the others, the automatically tunable caches get their share.
SGA_MAX_SIZE is reserved for ORACLE at startup time, but as long as it is not touched it is not used.
As of Oracle 11g we will have one parameter which we can use to allow Oracle not only to adjust these five caches in the SGA but also we allow Oracle to shrink and grow the entire SGA memory in order to hand over memory to the PGAs (process memory) and vice versa.
This parameter is called MEMORY_TARGET. With this we can specify how much memory we want to allow ORACLE to use all over, including SGA and PGAs.
MEMORY_TARGET is a dynamic parameter and can be changed with an ALTER SYSTEM SET... statement. It can be adjusted up to the value of MEMORY_MAX_TARGET. Now we do not only have Automatic Shared Memory Mamangement but Automatic Memory Management (system memory plus process memory).
Here is what the reference says:
MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

This is a feature we had expected to come up since a long time and I hope that it will work fine for at least most of the systems.
But I would suggest to first test it thoroughly before using it in production because all these new features which have AUTOMATIC in their names can have their issues at least in the first releases. And this is why they never are the default right away.

DBA course under special conditions

This week I have delivered a 10g DBA course in Baden which was very special.
1. The course was in English which is not a problem
2. I had customers from three different nationalites which was very interesting

There was an Parminder, an Indian

and a Palestinean, and an Israeli.
It made me very happy to see the Hazim and Tsachi
sitting next to each other discussing techincal things
and even political and religious topics peacefully and having fun with each other.







3. The Baden Oracle plant is undergoing reconstruction
at the moment and it was not easy to keep up business as normal.
This picture show Barbara Haller wearing a dust mask because behing the reception there was heavy work going on.






Does anybody recognize the training room from last week?

INSIDE ORACLE seminar

This week I have delivered my INSIDE ORACLE special seminar for the first time.
The seminar is a two day event which deals with Oracle 10g key features and upgrade issues.
I had 18 delegates with different backgrounds who were highly interested in memory management, new performance tuning techniques and backup strategies.
In between the paticipants had the opportunity to test some of the features hands on in the seminar room where we had a munber of system instaled with Oracle 10g database.
I have received a number of emails with very positive feedback in the meentime.
This seminar is available internationnally from now on and I already have the first bookings for it.
In May I will be delivering in Germany and Turkey and Greece hopefully as well.
And there are other talks going on in the background at the moment.

statistics_level translated

I just found my blog translated into another language:
http://translate.google.com/translate?hl=ja&sl=en&u=http://luhartma.blogspot.com/2006/09/statisticslevel-in-oracle-10gr2.html&sa=X&oi=translate&resnum

Can anybody tell me which language this is?

Enterprise Manager Packages Petition

Today I received a comment on my post about licensing of the Enterprise Manager packages.
Edgar pointed out a petition which has already been signed by 140 people.
This petition requests that Oracle Corporation should make all of its Enterprise Manager management packs available to all of its customers at the same price, regardless of the edition of the Oracle product that they have purchased.

I am not sure if this will be successfull but it is worth a try, don't you think so?
=;-)

new OCPs and OCAs

This week I have deliverd a specially condensed 10g New Features with Exam course for Oracle Partners.
After 4 days of exam cram three of the delegates have managed to pass the examinatin tests in teh Oracle University testing center in Baden.
Here you can see the two new OCPs Klaus and Martin as well as the new OCA Renato.
Congratulations!

Enterprise Manager Packages Licensing Information Part II

A few weeks ago I have already posted about the licensing agreements for the Oracle 10g Enterprise Manager Packages.
I have pointed out that you use these packages implicitly if you even do not use Enterprise Manager at all, but only
- run certain scripts from $ORACLE_HOME/rdbms/admin/
or
- call certain procedures from certain packages
and so on
Pls see my first post about this for a detailed list of functionalities which use these packages implictily..

Just recently I received a comment on this post by Russel who asked about ADDM in Standard Edition, which made me wonder..
First of all I looked up the licensing information in the Online Ducumentation where I found the Table 1-1 Feature Availability for Oracle Database Editions which shows me:


Feature/Option SE1 SE EE Notes

Manageability
Oracle Change Management Pack	N	N		Y	Extra cost option
Oracle Configuration Management Pack	N	N		Y	Extra cost option
Oracle Diagnostic Pack	N	N		Y	Extra cost option
Oracle Tuning Pack	N	N		Y	Extra cost option, also requires the Diagnostic Pack
Duplexed backup sets	N	N		Y
Database Resource Manager	N	N		Y

The Documentations explains to us the following about Table 1-1:
Table 1-1 lists feature availability for Oracle Database Standard Edition One (SE1), Standard Edition (SE), and Enterprise Edition (EE). The Y value in a column means that the feature is available in the specified release; N means that it is unavailable. This table can help you understand the difference between the editions and determine whether you require Enterprise Edition (or Personal Edition for a single-user environment).

Russel had asked in his comment about addmrpt.sql script for Standard Edition.
So I have installed SE under Linux and found out to my surprise that all the Enterprise functionalites where there ADDM, AWR, all the Enterprise Manager Packages which are not available, as the docu says. They were all in place and functionable in SE!
So I called Oracle support and here is what I was told:

SE has the same code base as EE, and all the EE-functionalities are also working in SE, they are even used by ORACLE internally for self tuning ,
BUT,
you are not allowed to use them.

And now you maybe should look at my first post again in order to find out what is not allowed and what brings you in danger od needing even not only an additional license for the packages but, also you might need a license for Enterprise Edition on top because the packages which are not available as of the Docu, but which are in place and functionable, and are implicitly used if you just klick to one little link in the browser or call just one procedure or run only the wrong skript from $ORACLE_HOME/rdbms/admin, are not combinable with an SE license.

The nice new functionality called database usage statistics high water marks will let you and everyone who can look at it know, which feature was used in the past and how often and so forth:
Haved a look at DBA_FEATURE_USAGE_STATISTICS and you will know if you already need a license for Enterprise Edition.


Thanks a lot for asking Russel!!!!
I was not aware of this.

Brand new book on RMAN Backup & Recovery out!

Finally it has been published:
the ultimat RMAN book for Oracle 10g
by M. Hart & R. Freeman!

I have been waiting for the 10g version of the book since quite a while already.
It was one of the very good books for Oracle 9i and I can tell you that the new one is as good. http://www.mhprofessional.com/product.php?cat=112&isbn=0072263172

ISBN: 0072263172 / 9780072263176

What I like most about the book:
- It contains a number of workshop for advanced recovery scenarios and
points out all these RMAN functionalities which are much too less used and know like BLOCK RECOVERY, PROACTIVE CHECKING FOR CORRUPT BLOCKS and so on.
- it has good index
- it contains info about memory utilization of RMAN for tuning

The book also covers new 10g features, such as Flashback, Transportable Tablespaces Cross Platform ...

It is a must for everybody who needs to know how RMAN works and how to utilize it to the max.

Table of contents
Part I: Getting Started with RMAN in Oracle Database 10g

Chapter 1: Oracle Database 10g Backup and Recovery Architecture Tour

Chapter 2: Introduction to the RMAN Architecture

Part II: Setup Principles and Practices

Chapter 3: RMAN Setup and Configuration

Chapter 4: Media Management Considerations Chapter

5: Oracle Secure Backup

Chapter 6: Enhancing RMAN with VERITAS NetBackup for Oracle Chapter

7: Configuring EMC NetWorker Module for Oracle Chapter

8: RMAN and Tivoli Storage Manager

Part III: Using RMAN Effectively Chapter

9: RMAN Backups Chapter

10: RMAN Restore and Recovery Chapter

11: Using Oracle Enterprise Manager for Backup and Recovery Chapter

12: RMAN Advanced Recovery Topics Chapter

13: Surviving User Errors: Flashback Technologies Chapter

14: Maintaining RMAN Chapter

15: Monitoring and Reporting on RMAN Chapter

16: Performance Tuning RMAN Backup and Recovery Operations

Part IV: RMAN in the Oracle Ecosystem

Chapter 17: Duplication: Cloning the Target Database

Chapter 18: RMAN and Data Guard

Chapter 19: RMAN and Real Application Clusters

Chapter 20: RMAN in Sync and Split Technology

Chapter 21: RMAN in the Workplace: Case Studies

Part V: Appendixes

Appendix A: RMAN Syntax Reference Guide

Appendix B: Exploring the Recovery Catalog

Appendix C: Setting Up an RMAN Test Environment

INDEX

Oracle Database 10g Real Application Clusters Handbook

I have just received the recent new book (FEB. 2007!)
by K. Gopalakrishnan
Oracle Database 10g Real Application Clusters Handbook

Gopalakrishnan is a Senior Principal Consultant in Oracle Corporation and was avarded "Oracle Author of the year 2005" by Oracle Magazine in for his 2005 published book "Oracle Wait Interface", which was one of the few books of the last year about Oracle which were really worth their money. Pls see my post about this book also.

After having a first glance at the new book I am very enthusiastic about it, because it contains large chapters about cache fusion (36 pages!) and the Global Resource Directory GRD (>20 pages) explained from scratch , amoung other intereseting chapters about troubleshooting RAC, RAC deployment...
Again we find the high quality we already have enjoyed in Gopalakrishnan´s Wait Interface book, and I bet it will be a success story as well.

Table of contents

Part I: High Availability Architecture and Clusters
Chapter 1: Introduction to High Availability and Scalability
Chapter 2: Clustering Basics and History
Chapter 3: RAC Architecture
Part II: Installation, Configuration, and Storage
Chapter 4: RAC Preinstallation
Chapter 5: RAC Installation
Chapter 6: Automatic Storage Management
Part III: RAC Administration and Management
Chapter 7: RAC Basic Administration
Chapter 8: RAC Advanced Administration
Chapter 9: RAC Backup and Recovery
Chapter 10: RAC Performance Management
Part IV: Advanced Concepts in RAC
Chapter 11: Global Resource Directory
Chapter 12: A Closer Look at Cache Fusion
Chapter 13: Workload and Connection Management
Chapter 14: RAC Troubleshooting
Part V: Deploying RAC
Chapter 15: RAC Extensions
Chapter 16: Developing Applications for RAC
APPENDIX A: RAC REFERENCE
APPENDIX B: ADDING AND REMOVING CLUSTER NODES
APPENDIX C: REFERENCES
INDEX

ISBN 007146509X / 9780071465090

Paperback, 421 pages

INSIDE ORACLE 10g special seminar

At the moment I am preparing a special two day seminar about Oracle 10g.
I have entirely created it on my own.

Its title will be INSIDE ORACLE 10g
and I will deliver it for the first time on
February 19th./20th. in Zurich.

on the first day of seminar I deal with the following topics:
- Memory Management:
- manual & automatic Shared Memory management
- manual & automatic Process Memory management
- temporary tablespace usage
- managing optimizer statistics and upgrade issues

On the second day I will talk about:
- New Performance Tuning Approach in 10g
- New RMAN Features and how they work
- The new ORACLE recommended backup strategy
- FLASHBACK Functionalities in 10g

The seminar will take place at Digicomp Academy in Zurich
and is handled by Oracle University.
The number of seats is limited to 30.
The prise is 1700 CHF for the two days.
There are already several bookings placed.

If you are interested in attentding you can book simply
through my company sysdba database consulting GmbH
by cutting and pasting the below lines, filling them in with your data and sending them to
sysdba database consulting GmbH by FAX
under the following number: +41 56 470 2264


Order Form for the two day seminar INSIDE ORACLE 10g
on February 19th./20th. 2007 in Zurich:

Yes, I want to enroll
LAST NAME...............................................................
FIRST NAME.............................................................
COMPANY..................................................................
Number of Seats.........................................................
Telefone Number:......................................................
FAX Number..............................................................

DATE...........................................................................

SIGNATURE..............................................................

Certifying CERN again

This week I am in Geneva again,
delivering a special certification event for Oracle University.
Again a bunch of DBAs want to get certified for Oracle 10g.
We compress a pensum of 10 normal education days into 5.
I guess that again this will be no problem for these guyes -
they are CERN!
I am looking foreward to these delicious Sushis in the basement of Cornavin Station.

Oracle ACE award

Yesterday I received my Oracle ACE reward from Oracle Technology Network.
The picture shows me with Ralph Bellinghausen from Oracle University Switzerland.














OTN sent me a plaque with my name and a pullover.

Thanks OTN!

New Demos on 10gR2 RAC available on OTN

There is a series about Oracle 10gR2 in Oracle By Example on OTN which provides us with a large number of really brilliant mini lessons in the form of little java viewlets which you can run in your browser.
Specially the RAC demos appletts are very handy.

The man behind this is Jean-Francois Verrier. He works as a course developer for Oracle University and puts a lot of effort into this series.
Here is a list of this brilliant collection on OTN which has been updated just recently with a number of new demos.
It is worth checking it out definitely!

This is what you can find there at the moment for Oracle Database 10g Release 2 and especially Oracle 10g RAC:

Manageability:
Using SQL Baselines
Using Metric Baselines
Transport a tablespace version to another database
Automatic Storage Management (ASM):
Install ASM single instance in its own home
Install ASM single instance in the same home
Migrate a database to ASM
Setup XML DB to access ASM
Access ASM files using ASMCMD
Real Application Clusters (RAC)
RAC Deployment Series (Beta):
Setting Up RAC Storage
Setting Up Openfiler Storage
Setting Up iSCSI On Client Side
Using fdisk to Partition Storage
Setting Up Multipathing On Client Side
Installing and Configuring ASMLib
Setting Up Storage Permissions On Client Side
Installing Oracle Clusterware
Installing Real Application Clusters
Configuring ASM Storage
Installing Oracle Database Single Instance Software (Part I)
Installing Oracle Database Single Instance Software (Part II)
Creating Single Instance Database
Protecting Single Instance Database Using Oracle Clusterware
Converting Single Instance Database to RAC Database
Adding a Node to Your Cluster
Extending Oracle Clusterware to Third Node
Extending RAC Software to Third Node
Extending RAC Database to Third Node
Rolling Upgrade Your Entire Cluster
Creating a RAC Physical Standby Database
Installing and Configuring OCFS2
Setting Up RAC Primary Database in Archivelog Mode
Backing Up RAC Primary Database
Configuring Oracle Network Services on Clustered Standby Site
Creating RAC Physical Standby Database Using OCFS2 Storage
Checking RAC Physical to RAC Standby databases Communication
Converting RAC Physical Standby Database to RAC Logical Standby Database
Rolling Upgrade Oracle Clusterware
Rolling Upgrade Oracle Clusterware on Clustered Primary Site (10.2.0.1 to 10.2.0.2)
Rolling Upgrade Oracle Clusterware on Clustered Standby Site (10.2.0.1 to 10.2.0.2)
Upgrading your RAC Standby Site
Upgrading RAC Standby Database From 10.2.0.1 to 10.2.0.2 (Part I)
Upgrading RAC Standby Database From 10.2.0.1 to 10.2.0.2 (Part II)
Switching Primary and Standby Databases Roles
Upgrading your old RAC Primary Site
Upgrading RAC Old Primary Database From 10.2.0.1 to 10.2.0.2 (Part I)
Upgrading RAC Old Primary Database From 10.2.0.1 to 10.2.0.2 (Part II)
Switching Back Primary and Standby Databases Roles
Miscellaneous:
RAC scale example
RAC speedup example
Use Transparent Application Failover (TAF) with SELECT statements
Oracle Clusterware:
Use Oracle Clusterware to protect the apache application
Use Oracle Clusterware to protect the Xclock application
RAC Voting Disk Multiplexing
Patch Oracle Clusterware in a Rolling Fashion
CSS Diagnostic Case Study
RAC OCR Mirroring
Services:
Runtime Connection Load Balancing example
Basic use of services in your RAC environment
Installs and Enterprise Manager:
Install ASM in its own home in a RAC environment
Convert a single-instance database to a RAC database using Grid Control
Push Management Agent software using Grid Control
Clone Oracle Clusterware to extend your cluster using Grid Control
Clone ASM home to extend your cluster using Grid Control
Clone database home to extend your cluster using Grid Control
Add a database instance to your RAC database using Grid Control
RAC Concepts:
RAC VIP Concepts
RAC Object Affinity Concepts
Rolling Release Upgrade (Beta): 10.2.0.1 to 10.2.0.2:
Upgrading your Standby Site
Upgrading RAC Standby Database From 10.2.0.1 to 10.2.0.2 (Part I)
Upgrading RAC Standby Database From 10.2.0.1 to 10.2.0.2 (Part II)
Switching Primary and Standby Databases Roles
Upgrading your old Primary Site
Upgrading RAC Old Primary Database From 10.2.0.1 to 10.2.0.2 (Part I)
Upgrading RAC Old Primary Database From 10.2.0.1 to 10.2.0.2 (Part II)
Switching Back Primary and Standby Databases Roles

Oracle DBA Toolbar

Sorry, do not install the Oracle DBA toolbar!!!
It seems to have problems with the up to date internet explorer!!! (see comments!!!)

A few weeks ago there was a download link on OTN for the
ORACLE DBA toolbar, a little plugin for Microsoft Internet Explorer.

After I got a new maschine I had to search for a while to find it again:
I like this little tool very much. so I got it again.
It is an .exe file you download of 880 k size, which installs th toolbar into the browser.



After the installation you have links in the browser which you can use to access the software downloads from OTN, Metalink, your local SQL*PLUS and a little collection of favorites and other usefull links.

I hope that it will be fixed and available for firefox soon!!!

=;-)

Got the I-Pod with an Oracle course

tzerland to find out
Today one of my customers received a 4GB i-Pod nano
since he had booked two Oracle courses in Switzerland
between Dec. 1st. 2006 and Feb. 28th. 2007.








This is a really cool promo!
And it is still running!
You can contact the toll free number
0844 844 822 in Switzerland to find out more about it.
Or ask per email: info@digicomp.ch,
or use this link

Licensing Oracle Clusterware?

Recently I have posted about how to protect a single instance database with Oracle Clusterware and have received a number of comments and requests concerning licensing information for this feature.
So I have just asked Oracle PreSales about this and here we are with the details:
The good news is:
You do not need an additional license to use the Oracle Clusterware, even with single instance database!
But!: the emphasis is on additional!
You need to have at least one license for at least one standard edition database in the cluster if you want to use the clusterware to protect your single instance database!

Steven Feuerstein coming to Zurich

The PL/SQL guru and Oracle ACE Steven Feuerstein will be in Zurich on March 29.th-30th. next year.

These seminars are an absolute MUST for a PL/SQL-programmer!

He will deliver special seminars on programming techniques at the Sonnenberg Convention Center:
This is a summary contents for the seminars:

• Overview:
  Over its lifetime, PL/SQL has grown increasingly robust and complex. Many programmers are barely aware of the new capabilities of PL/SQL, much less fully understand the implications for their programs. And virtually all PL/SQL developers are too pressured by deadlines to have the time to think through the best way to apply PL/SQL's many features.
  
  This two-day seminar, created specially for Oracle UK, teaches attendees about the most important features of the PL/SQL language, focusing on the major advances in Oracle 9i Database and Oracle Database 10g. Steven then takes you beyond the basics to show you how to apply these features in the context of best practices, so that you write highly optimized and easily maintained and enhanced applications.
  
  
• Prereqs :
  You should have a working knowledge of PL/SQL and at least a year's worth of experience with the language. All developers, from beginner to expert, will benefit from Steven's ideas and examples.
  
  Steven will demonstrate techniques and principles that apply to all versions of Oracle, but will also showcase new features in Oracle Database 10g that can dramatically improve the quality and performance of code that you write.
  
  
• Objectives:
  To improve query and DML performance
  Use "six simple steps to unit testing happiness" to test your PL/SQL subprograms
  Optimize SQL statements in PL/SQL programs
  Use modular, reusable code
  
  
• Topics:
  Improve query and DML performance Unit test your PL/SQL subprograms using "six simple steps to unit testing happiness"
  Optimize the construction of your SQL statements in PL/SQL programs
  Build modular, reusable code
  Manage errors by following a standard, consistent process
  Apply collections (a critical but under-utilized data structure)
  Take advantage of both forms of dynamic SQL in PL/SQL
  

For more information and booking you can also contact edu-buch_ch@oracle.com .

How to use Oracle Clusterware to protect a single instance database

In Oracle 10g it is possible to use the Oracle Clustersoftware to protect single instance databases as well as cluster databases.

There is a new set of code samples on OTN and a brilliant white paper by the authors Philip Newlan and Roland Knapp, which shows how to protect a Single Instance Oracle Database with Oracle Clusterware.

It is packed into a .zip file and contains the white paper plus a number of little perl scripts:
- a script to start / stop / check the Oracle Listener in a cold failover configuration.
- a script for start / stop / check the Oracle Instance in a cold failover configuration.
- a script for start / stop / check the Oracle ASM Instance in a cold failover configuration

The white paper covers three different case studies:
(here is an excerpt from the white paper)

- Case 1 No Oracle Software installed on either node
- Case 2 Oracle with datafiles installed into a local instance of ASM
- Case 3 Oracle with datafiles installed onto a local filesystem

The ‘ending case’ configuration for each of these is as follows:
- Case 1 Oracle Clusterware installed onto both nodes, Clustered ASM installed onto both nodes in the cluster, Single Instance Database Home on each node. In this case Oracle Clusterware automatically protects the ASM instance (by default). This section discusses scripts to protect the single Instance database.
- Case 2 Oracle Clusterware installed onto both nodes, ASM instance ready to be started on new node. New database home on 2nd node, . This paper discusses scripts to protect the Listener, ASM and single Instance database. In this case the
ASM instance is non-clustered.
- Case 3 Oracle Clusterware installed onto both nodes, OCFS 2 installed as a
common shared filesystem for the datafiles. Local Oracle Homes on each node. This paper discusses scripts to protect the listener and single Instance database.

Nice one!!

It's not easy to become an Oracle Certified Master

Last week I was to take the Oracle 10g OCM exam in Berlin.
It is the second time that I had registered for this challenge this year
and it is the second time that the event was cancelled by
Oracle University Germany.

I had already booked my flight, made reservations for acommodation,
even taken a few days off of work, not to speak about all the prep efforts I had put into it again.

It is a pity that the next possible date is December 1st. in London.
I will be speaking in Parma at the Italian Usergroup Meeting at that time.

I think I will not try it a third time this year.
Hopefully the chances for such an event to actually take place will be better next year.

New special last week was the rental car I had rented at the airport.
It was a nice little Alfa Romeo, only that its software was a little buggy obviousely.
I all of a sudden could not close the windows on teh drivers side any more.
So I had to drive about 100 km across the highway with an open window.

It was not my week obviousely.

Oracle ACE awarded

Today I received an email with the following text:
... I am pleased to inform you that you have been nominated by XXX to receive the Oracle ACE award. You have been chosen based on your significant contribution and activity in the Oracle technical community. Like your fellow Oracle ACEs, you have demonstrated a proficiency in Oracle technology as well as a willingness to share your knowledge and experiences with the community....






This makes me feel very proud and I would like to thank all those people who have encouraged me to keep on blogging and writing on Oracle New Features in variouse tech mags.

I'll stay in touch with you!

The best Sushi in Geneva

Today I went for lunch to a little Sushi Take Away Restaurant in Geneva
with my group from CERN.














It is located under the train station of Cornavin.
Here are the contact data:
Zim Zun
Metro Shopping Gare Cornavin
Tel.: 022 7310182









They have this very huge variety of freshly made Sushies of all kinds












and the prices are really reasonable.
You pay between CHF 2 and 3,80 per piece













And the women who sell the stuff, there are only women working,
are so lovely!












... and they even sell other asian specialities from the wok.


I really want to recommend this restaurant!!

Teaching CERN how to pump the data

This week I deliver a special training in Geneva for a bunch of DBAs from the CERN.
These guyes are really special.


They are always a few steps ahead with their questions.
Very advanced students! They really give me a hard time.
And we have a lot of fun.
Of course they are more interested in the internals and the background than in GUIs, which makes it more challenging and more fun to teach them.

They told me that CERN is stiil in the testing phase and their large databases are in the terabytes at the moment.

Once they will go life with the experiments they will have databases in the petabytes range.
Of course they use ASM for the storage because they have periodes of very high high volume throughput. They also use Grid Control and of course everything is clustered at the CERN. Only RAC databases with 6 nodes for each cluster.
Today I will demonstrate the datapump and ASM and how to migrate a database into an ASM storage with RMAN.

30000 th pageload this year

I reached the 30000 th poageload this year with my blog.


The tendency is clear!
Thanks for reading my blog!

	Page Loads	Unique Visitors	First Time Visitors	Returning Visitors
Total	30,256	21,641	17,501	4,140
Average	2,751	1,967	1,591	376
Month	Page Loads	Unique Visitors	First Time Visitors	Returning Visitors
Nov 2006	2,712	2,081	1,674	407
Oct 2006	5,618	4,193	3,369	824
Sep 2006	3,608	2,691	2,125	566
Aug 2006	4,709	3,320	2,784	536
Jul 2006	3,770	2,766	2,211	555
Jun 2006	3,459	2,376	1,870	506
May 2006	3,504	2,355	1,913	442
Apr 2006	1,713	1,234	1,066	168
Mar 2006	704	431	360	71
Feb 2006	160	86	66	20
Jan 2006	299	108	63	45

Raising my own business

After six years of being an Oracle employee
I have decided to leave the company and
raise my own business.






Starting from January 2007
I will be available for the following projects :
- Oracle consulting projects
- Oracle coaching
- Oracle trainings

It was a great pleasure for me to deliver courses for Oracle University.
Therefore I will continue to deliver courses for Oracle University Switzerland and its partners.

Also I am available for projects internationally
and still have free capacitie for the first weeks of 2007.

If you are interesting in booking me,
pls use my email contact in my profile here for now.
I will have a new website for my company soon with a new contact
and will publish it here also.

I have been an instructor for Oracle 8i, 9i and 10g core courses and have delivered
in Switzerland, Germany, Great Britain and the U.S.A. the following courses:
- Database Administration,
- New Features,
- Performance Tuning,
- Advanced Backup & Recovery with RMAN,
- Database Programming with PL/SQL,
- Technical Overviews


I have delivered special courses and events like:
- Oracle Expert night (Zurich , Geneva)
- New Features Workshop 10gR2 (New York, Chicago, New Jersey, London)
- Performance Tuning Workshop - Memory Management Oracle 10g
- The new Oracle 10gR2 suggested Backup Strategy (Swiss Oracle Usergroup)

I am still working on upcoming special events about:
- Oracle 11g New Features Overview
- RAC 10gR2 overview
- Oracle Data Guard

DBMS_SERVICE and net service names

Oracle 10gR1 was the first release to come with the package DBMS_SERVICE.
This package can be used to manage services for single instances.
There are procedures for creation , activation, deactivation, and removing of services.
We can measure workloads and limit resources through services and there are more and more concepts which are using services.
A RAC has the capability of managing services across instances.

The package contains a number of constants, exceptions and subprogramms.

Here is an example for the creation of a service on a single instance:

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=> 'HALLO', NETWORK_NAME=> 'OTTO')
PL/SQL procedure successfully completed.

SQL> exec dbms_service.START_SERVICE('HALLO')
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string OTTO

The confusing thing here is that the parameter NETWORK_NAME is in fact the value returned in the initialization parameter SERVICE_NAMES!

And the parameter SERVICE_NAME is the values used for the net service name in tnsnames.ora!!

This is crazy I think! How confusing and misleading this is!

Therefore you should use the same value for both parameters in the call of the procedure!

I will present @ Italian Oracle User Group in Parma

On December 1st. I will deliver a presentation of
Oracle's new recommended backup strategy
at the annual meeting of the Italian Oracle User Group in Parma.

I will use two ASM diskgroups,
and migrate a database from file system into the ASM storage using RMAN.

Then I will demonstrate how we can get around a full restore
by switching to an image copy of the database in the
flash recovery area, which I have incrementally updated.

Looking foreward to Parma!

Oracle Enterprise Linux available

In his Oracle OpenWorld keynote on October 25th, Larry Ellison announced Oracle Unbreakable Linux Support Program, a support program that provides enterprises with industry-leading, global support for Linux.
Oracle has been talking about even having its own Linux since quite a while now.
I had asked Tom Kyte about Oracle's ambitions in becoming kind of an OS itself and he said that it could definitely happen in the near future because it would make sense definitely very much.
Please see also unbreacable linux @ OTN
and Sergio Leunissen's Oracle Blog on Oracle's Linux project.
He is a member of Oracle's Linux Team.






It is already possible to download ISOs for Oracle's enterprise linux!

11g new features

I just listened to an interview Tom Kyte gave at the Oracle Open World today in San Francisco.
It is available on OTN for download.
He talks about some interesting new features which will be available in Oracle 11g.
It looks as if there are a number of really interesting new functionalities comming soon.
=;-)

My perfomance tuning workshop

Today I have delivered a specially tailored workshop in Zurich.
A few weeks ago Tom Kyte had delivered a Q&A session for a bunch of DBAs when he was in Zurich. I was booked for today to deliver a follow up as a workshop in order to discuss some details on memory management in Oracle database 10g.
We had a close look at ASMM (Automatic Shared Memory Management), Automatic PGA Management and the use of temporary tablespaces and temporary tablespace groups in Oracle 10g.
I had compiled specially tailored courseware from different Oracle University courses, such as the New Features for 10g Adminstrator, 10g Performance Tuning and some white papers and metalink notes.
I was teaching a group of about 15 DBAs who are very experienced and deal with very large systems of sizes in the 10s of terabytes.
We also had some very interesting discussions on some features which can cause headaches when upgrading to Oracle 10g, such as bind variable peeking, system statistics, and automatic statistics gathering and its possible effects.

Tablespace 101% full!

Today I had a strange effect in my DBA class:
I filled up my UNDO TABLESPACE which has only one non autoextensible datafile and waited for the alert to show up in DATABASE CONTROL console.
And it did.
See what it showed: => tablespace 101% full.
How can this be?
This is funny, eh!

How to use rlwrap to get a command history in sql*plus

sql*plus does not have a command history function under Linux and Unix.
Lately I listened to Tom Kyte at one of his seminars he delivered in September in Zurich. He used a virtual Linux machine and had a command history for his sql*plus obviousely.
He told us that he used a utility called rlwrap for this.

rlwrap is a readline wrapper for shell commands which uses input from the controlling terminal.
It adds a persistent input history for each command and supports user-defined completion.

I simply had to get it!
And I think that this is worth sharing it.
Here is how it works and how you can get it too.

You can download the sources for the rpm for rlwrap from
here. The most recent version I could find is version 0.26.
There you also find a README and the manpage for rlwrap.

After downloading and unpacking the tar.gz I ran as root (#)
./configure
make
make check
make install

and that was it.

Now I could call sqlplus this way:
$ rlwrap sqlplus user/password@sid.

Finally I create an alias
alias sqlp ="rlwrap sqlplus"
(pls see Laurent Schneider's comment on this post about alias sqlplus!)
Now I can simply call sqlplus as I always have done
and have a commend history with the ↑ and ↓ keys on my keyboard.

This will put an end to the stoneage habits we have all got used to too much!

SOUG Newsletter 4/2006 shipping

The Newsletter 4/2006 of Swiss Oracle User Group is shipping since last week.
I have an article about the Active Session History in Oracle Database 10g published in it.
You can find it here on my blog as well.

Interview with Tom Kyte

This is an interview which I made with Tom Kyte in Zurich in September 2006

L.H.: Mr. Kyte, you are Vice President of the world's largest database software company and you also are the man behind the http://asktom.oracle.com/ website.

Since more than 6 year you have provided kind of support to the Oracle community for their every day problems in your forum.
Judging from your experience, what are the most common mistakes made about database applications?

T.K.: This is a timely question, I just finished my "Worst Practices" material for some upcoming conferences (Oracle Open World included).

The worst practices I see out in the field are:

1) Blindly believing everything just because it is printed. I cannot even guess what percentage of technical material about Oracle published on the Internet is wrong. During the construction of my material for this Worst Practices talk - it took me about 10 minutes to find 10 really "good" (meaning really bad) examples of technically incorrect information. I used http://www.google.com/ to search for "Oracle Tuning Tips" and found some really bad material quickly.

2) Not using bind variables! Developers for whatever reason make this common mistake time and time and time again. Not using bind variables in most applications will cause 4 things to happen:
a) they will run slower in single user mode, a large percentage of the program run time will be spent parsing SQL, not executing it.
b) they will use memory entirely inefficiently, the shared pool will be a point of contention.
c) their applications will not scale as users are added - hard parsing is a point of much serialization in the database.
d) their application will be subject to SQL injection issues (put SQL Injection into the search box on http://www.google.com/ and see how many articles you get back on that).

So, by not using bind variables - the developers help to ensure they will have a slow running program that creates memory issues in the shared pool, that cannot scale and will be a huge security risk.

3) Implementing very generic data base designs. Generic code can be useful, in some rare cases - however, if you want something maintainable, understandable and performant - you will actually design your system, from day one.

4) Not having a credible test environment. For example - not testing to scale (if you are going to support 100 concurrent transactions, you need to be able to test that you can do that - and then test it!). Or - testing against an empty database; that will not give you any indication that your developed application will actually perform in real life.

L.H.: After 14 year of work with Oracle Database, what is the feature to your mind today which makes Oracle Database unique among other database providers?

T.K.:http://www.oracle.com/technology/oramag/oracle/05-may/o35asktom.html

My favorite feature - Oracle's concurrency model.

L.H.: The average database of today is by far larger than what has been considered a Very Large Database only a few years ago.
What are the most challenging task for a database administrator today?

T.K.: Keeping up with the technology! Many of the things we used to spend a lot of time doing manually are much more automated than they were a decade ago. And there are hundreds of new features to learn about. In the last decade we've gone from having a 1 Terabyte database be considered "huge" to being considered just "average" in size. Many things we did not use or need to use a decade ago - such as partitioning (did not exist) - are things we need to have mastery over today. Therefore, the working set of knowledge a DBA needs today is very much different from the working set of knowledge they had to have 10 years ago. We need to "forget" about some of the things that were automated (such as undo management - I'm glad I don't have to figure out exactly how many rollback segments of what size I need anymore) and keep up with all of the new tools we have to manage data such as partitioning, data guard, streams and so on.

L.H.: The trend of the Oracle Database Server goes clearly into the direction of self management and self maintenance.
Will there be a need for database administrators for the database of the future and what will be their main tasks?

T.K.: Absolutely - see the points above. Every time we automate something - 3 or 4 other "new things" are added. Sure - we automated PGA memory management in 9i, but we added Streams and Change Data Capture. The list of things DBAs do is not necessarily shrinking, but it is ever changing.

L.H.: Will the database server of the future be able to also kind of auto-tune the application?

T.K.: This is why I say "tuning is not dead", but how we tune has to change. In the past - we would tune by tuning SQL. Nowadays the database is pretty good at tuning SQL - with SQL profiles, an ever smarter optimizer, the index advisor, the materialized view advisor, the SQL advisor and so on - tuning SQL is something we spend less and less time on.

What we have to focus on is how we do things - our algorithms. Maybe far far out in the future, the database can detect what an application is doing and "fix it", but for the time being - only we can.

For example, the datab ase will not turn logic such as:

for x in (select * from t1)
loop
insert into t2 values X;
end loop;
into
insert /*+ APPEND */
into t2 select * from t1;
That is our job and will be for the immediate future.

L.H.: Oracle already has its own logical volume manager (Automatic Storage Management) its own cluster file system and since March 2006 also its own media management server (Oracle Secure Backup), which can be used to not only backup the database but also the entire Oracle software stack including application servers, the collaboration suite as well as OS-files.
Will Oracle be kind of an operating system some day?

T.K.: We tried that once before actually, a thing called "Raw Iron" http://news.com.com/2100-1001-219075.html

It was not widely adopted - maybe it was before its time. I can certainly see the usefulness of such a thing - I personally already consider Oracle to be my operating system in many cases. I'm not saying Oracle is working on it (I have no knowledge to that effect), just that I could see it happening sometime in the future. A database appliance, much like you have network attached storage.

L.H.: Thank you very much Mr. Kyte.

STATISTICS_LEVEL in Oracle 10gR2

The initialization parameter STATISTICS_LEVEL controls quite a number of functionalities in the Oracle 10g database.
Its default value is TYPICAL.

By setting it to BASIC you switch off functionalities like automatic statistics collection for performance baselines, statistics sampeling for the active session history, table monitoring and a lot of more functionalities. If the parameter is set to BASIC it is still possible to manually set the parameters DB_CACHE_ADVICE, TIMED_STATISTICS and TIMED_OS_STATISTICS.

The parameter can also be set to ALL which causes the collection of additional statistics for timed operating system statistics and for the row source executions.
Oracle strongly recommends to collect the statistics at least at TYPICAL level.

STATISTICS_LEVEL can be set at system as well as at session level. If set on session level the following advisories or statistics get turned on or off, but their systemwide setting is not changed:
- Timed Statistics
- Timed OS Statistics
- Plan Excution Statistics

In order to find out which functionalities are controlled by STATISTICS_LEVEL you can query the dynamic performance view v$statistics_level which displays the status of the statistics/advisories controlled by STATISTICS_LEVEL:

SYS @10gR2 SQL > show parameter statistics_l
NAME TYPE VALUE
------------------------- ----------
statistics_level string TYPICAL

SYS @10gR2 SQL > DESC v$statistics_level
Name Null? Type
----------------------------------------- -------- ----------------------------
STATISTICS_NAME VARCHAR2(64)
DESCRIPTION VARCHAR2(4000)
SESSION_STATUS VARCHAR2(8)
SYSTEM_STATUS VARCHAR2(8)
ACTIVATION_LEVEL VARCHAR2(7)
STATISTICS_VIEW_NAME VARCHAR2(64)
SESSION_SETTABLE VARCHAR2(3)

SYS @10gR2 SQL > SELECT STATISTICS_NAME, ACTIVATION_LEVEL, SYSTEM_STATUS, STATISTICS_VIEW_NAME, SESSION_SETTABLE
2 FROM v$statistics_level;
STATISTICS_NAME ACTIVAT SYSTEM_S STATISTICS_VIEW_NAME SES
------------------------ ------------ ---------------------------------------------------- ---
Buffer Cache Advice TYPICAL ENABLED V$DB_CACHE_ADVICE NO
MTTR Advice TYPICAL ENABLED V$MTTR_TARGET_ADVICE NO
Timed Statistics TYPICAL ENABLED YES
Timed OS Statistics ALL DISABLED YES
Segment Level Statistics TYPICAL ENABLED V$SEGSTAT NO
PGA Advice TYPICAL ENABLED V$PGA_TARGET_ADVICE NO
Plan Execution Statistics ALL DISABLED V$SQL_PLAN_STATISTICS YES
Shared Pool Advice TYPICAL ENABLED V$SHARED_POOL_ADVICE NO
Modification Monitoring TYPICAL ENABLED NO
Longops Statistics TYPICAL ENABLED V$SESSION_LONGOPS NO
Bind Data Capture TYPICAL ENABLED V$SQL_BIND_CAPTURE NO
Ultrafast Latch Statistics TYPICAL ENABLED NO
Threshold-based Alerts TYPICAL ENABLED NO
Global Cache Statistics TYPICAL ENABLED NO
Active Session History TYPICAL ENABLED V$ACTIVE_SESSION_HISTORY NO
Undo Advisor, Alerts and FastRamp up TYPICAL ENABLED V$UNDOSTAT NO
16 rows selected.

bits and bites with Tom Kyte

I had organized a little dinner for Tom Kyte and some special guests yesterday evening.
We went to a nice Spanish restaurant in the Old City of Zurich.
It was a relaxing evening with lots of fun, paella and good Spanish wine.

Tom Kyte @ Oracle Education Switzerland

Yesterday Tom Kyte flew into Zurich from Paris.
He will stay for three days to deliver a number of special trainings and lectures.
After a Q&A-session at the UBS bank in Zurich I took him over to Baden where he delivered a lecture about the importance of "instrumentation" of code and the possible methods of instrumenting your code in an Oracle environment both in the database (stored procedures) as well as outside the database (java, C, VB and the like).
The demo I liked best yesterday was about the usage of the package dbms_monitor. With this demo he pointed out how important it is that the devleopers use the possible instruments to hand over client information (through all middle tiers) into the database in order to enable the DBA to trace back (through all middle tiers) who exactely did what (in the browser). This a package I also show in my "Oracle 10g New Features For Administrators" classes.

Today and tomorrow Tom Kyte will
deliver a number of lectures
at a special event of Oracle Education
at the Zurich Development Center.

10 weeks => 10000 hits

Yesterday my blog encountered its 20000th. pageload since I launched it in November of last year.
There were 10000 visitors since June 30th. !
Thank you very much to all of you blog-readers!

	Page Loads	Unique Visitors	First Time Visitors	Returning Visitors
Total	19,816	13,768	11,224	2,544
Average	2,202	1,530	1,247	283
Month	Page Loads	Unique Visitors	First Time Visitors	Returning Visitors
Sep 2006	1,498	1,092	891	201
Aug 2006	4,709	3,320	2,784	536
Jul 2006	3,770	2,766	2,211	555
Jun 2006	3,459	2,376	1,870	506
May 2006	3,504	2,355	1,913	442
Apr 2006	1,713	1,234	1,066	168
Mar 2006	704	431	360	71
Feb 2006	160	86	66	20
Jan 2006	299	108	63	45

crs_stat Utility

I am in Geneva this week, teaching part of a RAC 10g class at the World Trade Center togehter with Tamás Kerepes from Budapest. He is an enormously skilled collegue of mine and I am happy to get to know him.

I have demonstrated the migration of an entire cluster database from OCFS to ASM life in the class yesterday, including all datafiles, redologs, controlfiles, spfile and backups. This was a really thrilling and a little tricky process, but I succeeded at the end!
I will post about the procedure soon.

I came across a nice little option for the crs_stat utility which I was not aware of and which Tamás pointed out to me:
crs_stat -t gives a short listing of the contents of the cluster registry rather than the long listing of the simply scr_stat without the -f.
This can come in very handy with a large cluster registry!
Look at the difference!
Here is a demo:
[oracle@ed-toraclin9a oracle]$ crs_stat --help
Usage: crs_stat [resource_name [...]] [-v] [-l] [-q] [-c cluster_member]
crs_stat [resource_name [...]] -t [-v] [-q] [-c cluster_member]
crs_stat -p [resource_name [...]] [-q]
crs_stat [-a] application -g
crs_stat [-a] application -r [-c cluster_member]
crs_stat -f [resource_name [...]] [-q] [-c cluster_member]
crs_stat -ls [resource_name [...]] [-q]
[oracle@ed-toraclin9a oracle]$ crs_stat
NAME=ora.RDBB.RDBB1.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9a

NAME=ora.RDBB.RDBB2.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9b

NAME=ora.RDBB.db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9b

NAME=ora.ed-toraclin9a.ASM1.asm
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9a

NAME=ora.ed-toraclin9a.LISTENER_ED-TORACLIN9A.lsnr
TYPE=application
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.ed-toraclin9a.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9a

NAME=ora.ed-toraclin9a.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9a

NAME=ora.ed-toraclin9a.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9a

NAME=ora.ed-toraclin9b.ASM2.asm
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9b

NAME=ora.ed-toraclin9b.LISTENER_ED-TORACLIN9B.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9b

NAME=ora.ed-toraclin9b.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9b

NAME=ora.ed-toraclin9b.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9b

NAME=ora.ed-toraclin9b.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on ed-toraclin9b

[oracle@ed-toraclin9a oracle]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....B1.inst application ONLINE ONLINE ed-t...in9a
ora....B2.inst application ONLINE ONLINE ed-t...in9b
ora.RDBB.db application ONLINE ONLINE ed-t...in9b
ora....SM1.asm application ONLINE ONLINE ed-t...in9a
ora....9A.lsnr application ONLINE OFFLINE
ora....n9a.gsd application ONLINE ONLINE ed-t...in9a
ora....n9a.ons application ONLINE ONLINE ed-t...in9a
ora....n9a.vip application ONLINE ONLINE ed-t...in9a
ora....SM2.asm application ONLINE ONLINE ed-t...in9b
ora....9B.lsnr application ONLINE ONLINE ed-t...in9b
ora....n9b.gsd application ONLINE ONLINE ed-t...in9b
ora....n9b.ons application ONLINE ONLINE ed-t...in9b
ora....n9b.vip application ONLINE ONLINE ed-t...in9b

TOM KYTE is back in Zurich in September

As he had promised before he left last December, Tom Kyte will come back to Zurich on September 21st./22nd. and deliver another two days of his very advanced seminars on database architecture and programming techniques.
After the last seminar in December which was completely sold out, we have booked for a larger room this time, again at the Zurich Development Center, which is a great location to have a seminar at.
Again Tom Kyte will deliver two sets of sessions each of them about 60-90 minutes long.

Tom Kyte will cover the following topics:
"Things you "know"
Many developers and DBAs (not all, but many) approach the database with little or no understanding of how it actually works. Developers approach Oracle assuming it must work just like SQL Server or even worse with the frame of mind that says, "It doesn't matter how it works, I'm using a layer of abstraction to protect me." DBAs approach the server sometimes with a cursory knowledge of how things work, leading them to do things like, "We can skip backing up undo data-it isn't our stuff, we don't need it" or erasing archives because they ran out of space.
This presentation will present some of these foibles and explain how not understanding how the database actually works will lead to disaster. Some examples of things you just might not know about yourself will be explored. Oracle is big and it is a moving target. Understanding it is an ongoing process that we need to continually do.
Advanced analytic functions
Analytic functions are the coolest thing to happen to the SQL language since the introduction of the keyword SELECT . This session explores the use cases for analytic functions, demonstrates how and where you should use them, and explains the (nontrivial) syntax behind them.
Efficient schema design
Based entirely on chapter 7 of Tom's book "Effective Oracle by Design", this session takes you through the fundamentals of physical schema design. It demonstrates the various structures (hash clusters, B*-tree clusters, index-organized tables, ...) and tells when and where you want to use them. The session closes with indexing and compression techniques.
PL/SQL or Java?
Is one language "better" than the other? Or maybe there is room for both? This session explores when you might want to use one over the other SQL Techniques
The presenter will describe common SQL techniques he has encountered and utilizes day to day to tune query performance. Features such as scalar subqueries, using rownum (yes, to 'tune'), analytics, some hints and more will be demonstrated. Emphasis will be on when they work (where the 'trick' applies) as well as when they don't workwhere they do not apply. Care will be taken to show these not as a "top 10 things to do" but rather techniques to keep in mind when looking at problems in general.
Building test cases
When you say "it doesn't work", or "it isn't working right", you will need to build a small, concise, yet 100% complete test case that demonstrates the issue at hand (and removes all of the irrelevant bits). This session walks through how Tom Kyte builds his own test cases -- and how he many times finds his own mistakes in the development of them (such as: it wasn't a database bug in the first place!) - or, even if it turns out to be a problem in the database, how the development of the test case often leads to finding a workable "workaround".
Bits and bytes
This session covers some Oracle 10g features that are really "cool" but not played up in the Oracle marketing messages. Mundane features that make our day to day lives that much nicer. Presented as a top 10 for 10g Release 1 and top ten for 10g Release 2
Versioning of data
A common requirement today is to maintain a complete history of data: every change made to a row over time, for rows in a table or set of tables. This session explores the methods available to do this, from "do it yourself" versioning (via triggers/application code) to letting the database do it for you with workspace management. Key focuses of this session are time to develop, performance, and implementation



Every participant of this seminar will not only get the printed courseware for the sessions of course but also a copy of Tom Kyte's book on Oracle 9i and 10g Database Architecture.
Also there will be the sought-after Tom Kyte-T-Shirt again.

There are still a few seats available!
If you are interested in taking part here are the detail again:
Date: September 21st., 22nd.
Time: 9:00h -16:00h
Location: Zurich Development Center
Price: CHF 1800,-
Registration: edu-buch_ch@oracle.com

Using the Cluster Verification Utility to check RAC setup

Starting with Oracle database 10g Release 2 we can use the new Cluster Verification Utility (CVU) called cluvfy to check a cluster environment before and after a RAC installation.
This comes in very handy when you want to check your hardware and software setup before making the big step of installing the cluster ready services and rdbms software for the cluster db.
The CVU is shipped with the clusterware, but you can also get it directly from Oracle Technology Network.
It is already available for the following platforms:
Linux
Solaris
HP
AIX
Windows

Under linux you must first of all install the cvuqdisk rpm .

CVU requires Java 1.4.1 installed, needs about 30Mb of diskspace on the invocation node and about 25 Mb of working direcory on every node in the nodelist. The working directory must exeist on all nodes and the invoking user must have write access there.

Let's take a short look at what we can do with cluvfy:
[oracle@ed-olraclin1b bin]$ /stage/10gR2/rdbms/clusterware/cluvfy/runcluvfy.sh -help
USAGE:
cluvfy [ -help ]
cluvfy stage { -list -help }
cluvfy stage {-pre-post} [-verbose]cluvfy comp { -list -help }cluvfy comp [-verbose]

Let's provoke an error in order to get more help!
[oracle@ed-olraclin1b bin]$ /stage/10gR2/rdbms/clusterware/cluvfy/runcluvfy.sh stage -pre
ERROR:Invalid command line syntax.
USAGE:
cluvfy stage {-pre-post} [-verbose]
SYNTAX (for Stages):
cluvfy stage -post hwos -n [ -s ] [-verbose]
cluvfy stage -pre cfs -n -s [-verbose]
cluvfy stage -post cfs -n -f [-verbose]
cluvfy stage -pre crsinst -n [-r { 10gR1 10gR2 } ]

[ -c ] [ -q ]

[ -osdba ]

[ -orainv ] [-verbose]

cluvfy stage -post crsinst -n [-verbose]
cluvfy stage -pre dbinst -n [-r { 10gR1 10gR2 } ]

[ -osdba ] [-verbose]

cluvfy stage -pre dbcfg -n -d [-verbose]

As we see, cluvfy uses stages, which are states of the system, either pre- or post. With the pre-stages you can check whether the requirements for an installation are met, with the post-stages you can check if everything with the installation worked well.

Here is an example for a pre-installation check before installing clusterware on a linux cluster:

[oracle@ed-olraclin1a .ssh]$ /stage/10gR2/rdbms/clusterware/cluvfy/runcluvfy.sh stage -pre crsinst -n ed-olraclin1a,ed-olraclin1b
Performing pre-checks for cluster services setup
Checking node reachability...
Node reachability check passed from node "ed-olraclin1a".
Checking user equivalence...
User equivalence check passed for user "oracle".
Checking administrative privileges...
User existence check passed for "oracle".
Group existence check passed for "oinstall".
Membership check for user "oracle" in group "oinstall" [as Primary] passed.
Administrative privileges check passed.
Checking node connectivity...
Node connectivity check passed for subnet "138.2.147.0" with node(s) ed-olraclin1b,ed-olraclin1a.
Node connectivity check passed for subnet "192.168.1.0" with node(s) ed-olraclin1b,ed-olraclin1a.
Suitable interfaces for VIP on subnet "138.2.147.0":ed-olraclin1b eth0:138.2.147.72ed-olraclin1a eth0:138.2.147.70
Suitable interfaces for the private interconnect on subnet "192.168.1.0":ed-olraclin1b eth1:192.168.1.12ed-olraclin1a eth1:192.168.1.11
Node connectivity check passed.

Checking system requirements for 'crs'...
Total memory check passed.
Free disk space check passed.
Swap space check passed.
System architecture check passed.
Kernel version check passed.
Package existence check passed for "make-3.79".
Package existence check passed for "binutils-2.14".
Package existence check passed for "gcc-3.2".
Package existence check passed for "glibc-2.3.2-95.27".
Package existence check passed for "compat-db-4.0.14-5".
Package existence check passed for "compat-gcc-7.3-2.96.128".
Package existence check passed for "compat-gcc-c++-7.3-2.96.128".
Package existence check passed for "compat-libstdc++-7.3-2.96.128".
Package existence check passed for "compat-libstdc++-devel-7.3-2.96.128".
Package existence check passed for "openmotif-2.2.3".
Package existence check passed for "setarch-1.3-1".

Group existence check passed for "dba".
Group existence check passed for "oinstall".
User existence check passed for "nobody".
System requirement passed for 'crs' Pre-check for cluster services setup was successful.

This is what we want to see at the end of the test!
Only with this result you can be sure that an installation of of the cluster ready services will be successful.

There are a number of good metalink notes on CVU available.
As a first entry I would recommend Note:339939.1
Subject: Running Cluster Verification Utility to Diagnose Install Problems
Here you find detailed information about the supported platform-versions as well as how to install CVU and how it works.

Oracle 10g Enterprise Manager Packages usage and licensing

The Enterprise Manager for Oracle 10g comes with a number of packages which need an additional license if you want to use them. These packages get installed automatically with Enterprise Edition and can be accessed from inside the EM-console (database control, grid control) as well as through APIs from inside the database.
These packages are as follows:
- Database Diagnostics Pack
- Database Diagnostics Pack
- Configuration Management Pack

What many DBAs do not know is the fact that you also need the additional licenses if you do not use the EM at all but use certain functionalities such as reports created with scripts located in $ORACLE_HOME/rdbms/admin or just by using certain procedures from a number of advisor packages, because these functionalities use the above listed packages.

Here is a list of functionalities which need additional licensing in Oracle 10g Enterprise Edition and how you access them:

- Database Diagnostic Pack includes:
- use of DBMS_WORKLOAD_REPOSITORY package
- use of DBMS_ADVISOR PACK if:
- when using any ADDM prefix for the value of the ADVISOR_NAME parameter
- when using any ADDM prefix for the value of the TASK_NAME parameter
- use of the view V$ACTIVE_SESSION_HISTORY
- use of any Data Dictionary view the DBA_HIST_ prefix in the name of the view
- use of any Data Dictionary view the DBA_ADVISOR_ prefix in the name of the view if query to these views retuns values from the ADDM or ADVISOR_NAME column or a value of ADDM* inthe TASK_NAME column or the corresponding TASK_ID

- use of any of the following reports created by using one of the following scripts from $ORACLE_HOME/rdbms/admin:
awrrpt.sql
awrrpti.sql
addmrtp.sql
addmrpti.sql
awrrpt.sql
awrrpti.sql
addmrpt.sql
addmrpti.sql
ashrpt.sql
ashrpti.sql
awrddrpt.sql
and awrddrpi.sql

- Database Tuning Pack includes (needs Diagnostics Pack as prerequisit!):
- use of the DBMS_SQLTUNE
- use of DBMS_ADVISOR if the parameter ADVISOR_NAME is set to SQL Tuning Advisor or SQL Access Advisor
- use of the script $ORACLE_HOME/rdbms/admin/sqlrpt.sql

- Configuration Management Pack
- software and hardware inventory with history function
- patching
- cloning
- policy framework

It is possible to disable the access to these packages from inside the EM-console by navigating to the SETUP link. If you cannot efford to buy these licenses you can still use the classical methods for performance tuning like statspack, sql tracing and performance statistics from the v$views.

New RAC demos on Oracle By Example

A new demo viewlet has been added to the very handy collection of demos for Oracle 10gR2 REAL APPLICATION CLUSTERS in the Oracle By Example series.
It shows how virtual IPs are used.
The man behind these nice little demos is Jean-Francois Verrier.
He designs a number of Oracle's core courses which are delivered by Oracle Education.

RAC TTT in Utrecht finished

Today we'll finish the Train The Trainer event for
Oracle 10gR2 Real Application Clusters
in Utrecht, The Netherlands.

Jean-Francios Verrier

It was such a pleasure and fun also to meet Jean-Francois Verrier again who has designed this course. He is also responsible for a number of other Oracle classes.








This picture shows all the delegates taking part in the training.
From the left there are:
John Boyle, Joel Goodman, Harald van Breederode, Jean-Francois Verrier, , Lothar Auert, John Abrahams, Andreas Reinhardt, Roman Niehoff, Thomas Hoogerwerf
and in the first row Pete Jones and me.
This week was so densly filled with highly compressed knowledge and inspiring talks.
I am so happy that I had the chance to get to know all these highly skilled experts and I hope that I can stay in touch with them.
It was a pitty that two of the German trainers had to leave one day too early.

How to convert a single instance database to a RAC database

There are multiple ways in Oracle 10gR2 to convert a single instance database into a
clustered database.
You can use the following methods to do this:
- dbca
- enterprise manager
- rconfig utility (new in 10gR2!)

After you have checked if your operating system requirements are met you can choose either of these methods to do the conversion.
You should make sure first that your hardware and operating system are supported.
To do so you check with your hardware vendor and for the platforms you check metalink.

Once this is done you go on with either methode:
- dbca does most of the tasks of the conversion automatically
- grid control provides fully support the conversion process
there is a very nice demo for this in the OBE (Oracle By Examples demos for RAC 10gR2 section)
- new with 10gR2 there is utility called rconfig which is located in $ORACLE_HOME/bin.
It uses a file called $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml which you need to edit in order to adjust some parameters as needed and then save it under a new name. It contains the parameter for the Convert verify option which accepts three different values:
CONVERT VERIFY= "YES" -- performs checks to make sure everything is in place before starting the conversion
- "NO" -- starts conversion right away without checking the prerequisits
- "ONLY" -- only performs the prerequisits-check

You then call the utility and hand over your modified xml file:
$ rconfig my_db_conversion_file.xml

Harald van Breederode training the trainers

Harald van Breederode,
a blind trainer from the Netherlands,
delivered a training on RAC installation
and also a special one on storage configurations
like SAN NAS and ASM today .
This was a very impressing lecture because
Harald is a very knowledgeable teacher who has
been delivering trainigs on RAC since 4 years
and also has been a UNIX administrator for
a long time as well.


The training is held in his classroom and it is really amazing how he can navigate though the location and how he even uses the whiteboard and develops complex pictures on it without seeing anything.
This picture shows his special braille display. It has 80 left and 80 right mice buttons. And he uses a voice synthesizer to get the powerpoints read out to him. Which is done in a speed that nobody else but him can follow.






This picture shows him demonstrating
his special equipment to us.

Arrived in Utrecht (The Netherlands)

Yesterday I have arrived in Utrecht in Holland.
Here I will take part in the Train The Trainer class
for the Oracle 10gR2 Real Application Cluster Class in De Meern.

10'000 hits on this blog, thank you all !!

On last Friday my blog has reached 10'000 pageloads since I started blogging last December.
There are about 500 unique visitor and 100 returning visitors average every week coming from all over the world.


Thank you very much for your interest!
I'll keep on blogging.

Table Compression in Oracle 9iR2 and beyond

Starting with Oracle database 9iR2 (Enterprise Edition!)
it is possible to create compressed heap organized tables and compressed table partitions as well as compressed materialized views.

This feature is very interesting for data warehouses because it can save a lot of disk space.

Compression has already been availble for b-tree indexes and index organized tables
since Oracle 8.1.5.

Here is an example:

LUTZ @10gR2 SQL > CREATE TABLE compress1 (col1 NUMBER, col2 VARCHAR2(30)) COMPRESS;
Table created.

LUTZ @10gR2 SQL > CREATE TABLE not_compress1
(col1 NUMBER,
col2 VARCHAR2(30));
Table created.

LUTZ @10gR2 SQL > SELECT table_name, compression
FROM user_tables;

TABLE_NAME COMPRESS
------------------------------ --------
NOT_COMPRESS1 DISABLED
COMPRESS1 ENABLED


LUTZ @10gR2 SQL > BEGIN FOR i IN 1..100 LOOP
INSERT INTO not_compress1 VALUES(i, 'value' || i);
END LOOP;
FOR i IN 1..14 LOOP
INSERT INTO not_compress1 SELECT * FROM not_compress1;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.

LUTZ @10gR2 SQL > SELECT COUNT(*) FROM not_compress1;
COUNT(*)
----------
819200

LUTZ @10gR2 SQL > SELECT COUNT(DISTINCT col2) FROM not_compress1;
COUNT(DISTINCTCOL2)
-------------------
100

LUTZ @10gR2 SQL > INSERT /*+ APPEND*/
INTO compress1
SELECT *
FROM not_compress1;
819200 rows created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
('NOT_COMPRESS1',
'COMPRESS1');

EXTENTS BYTES SEGMENT_NAME
---------- ------- -------------------

27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1

Serial INSERT with append hint, as well as Parallel INSERT (with parallel hint) and also
Direct path insert with SQL*Loader into segments with compression enabled cause compression.
Inserting into a compressed table by using an ORDER BY CLAUSE for the column with lowest cardinality (least distinct values) incerases the compression rate!

With the COMPRESS clause is also possible to
create a table with a subselect as a compressed segment:

SYSTEM @10gR2 SQL > CREATE TABLE compress2 COMPRESS
AS SELECT * FROM lutz.not_compress1;
Table created.

SYSTEM @10gR2 SQL > CREATE TABLE to_be_compressed AS
SELECT * FROM lutz.compress1;
Table created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
('NOT_COMPRESS1',
'COMPRESS1',
'COMPRESS2',
'TO_BE_COMPRESSED');

EXTENTS BYTES SEGMENT_NAME
----------- ----------- ------------------------------

27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1
30 15728640 TO_BE_COMPRESSED
27 12582912 COMPRESS2

It is also possibel to create a tablespace with compression as default:

SYSTEM @10gR2 SQL > CREATE TABLESPACE lutz_compress
DATAFILE '/u01/app/oracle/oradata/orcl/lutz_compress.dbf'
SIZE 10m AUTOEXTEND ON
DEFAULT COMPRESS;
Tablespace created.

SYSTEM @10gR2 SQL > CREATE TABLE lutz.not_compress2
TABLESPACE lutz_compress
AS SELECT * FROM lutz.not_compress1;
Table created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
('NOT_COMPRESS1',
'COMPRESS1',
'COMPRESS2',
'TO_BE_COMPRESSED',
'NOT_COMPRESS2');

EXTENTS BYTES SEGMENT_NAME
------------- ---------------- -----------------------------
27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1
30 15728640 TO_BE_COMPRESSED
27 12582912 COMPRESS2
27 12582912 NOT_COMPRESS2

This week in Baden Daettwil

This week I deliver in Baden Daettwil after a long periode of traveling.
I teach Oracle Database 10g: New Features for Oracle 8i DBAs.
This is a 5 day event and we walk through the entire process of
installation,
patching,
upgrading,
character set conversioning and
all the new features since Oracle 8.1.7.

This is a hard one to deliver because there are so many different topics to discuss.
We will have to really hurry up in order to cover all of it.

Licensing information for Oracle 10gR2 Database and Oracle Secure Backup

Many of the Oracle 10g new features are only available in Enterprise Edition.
In order to find out which feature is included in which Oracle License you can go to the
Licensing Information Section in the
Online Documentation which is available at Oracle Technology Network (OTN).
Go here for licensing info for the database and
here for licensing information for Oracle Secure Backup.

Heading back to Zurich

After three days of New Features
with a very nice group of very
highly skilled DBAs in London.

It was strange for me to switch to English English
after almost four weeks of American English.
Also I had to deal with people of a very
different mentality compared to the New Yorkers
and even more compared to the San Francisco People.


I will fly back to Zurich today.
The foto shows Juliet,. Phil,
David, Maulya,
Rajen, Rob and Pratab and me
at an Asian restaurant in London.


Ian Burgess

And again I got to know a very nice collegue
who I want to say thank you
to for being so very caring and helpful to me
when I felt so lost and locked out in a bazar location.
You are a real good fellow, Ian Burgess.

DML ERROR LOGGING an ORACLE 10gR2 New Feature

If a constraint violation raises a runtime error during execution of a DML statement we encounter a statement level rollback.
Oracle server creates a so called implicit savepoint before it excutes a DML statement, to which it can automatically rollback.
This can be very time and resource consuming especially with bulk DML operations.

Starting with Oracle database 10g release 2 it is possible to let a DML statement continue without erroring out although constaint violations are created.
For this we can use the new PL/SQL-package DBMS_ERRLOG to create an error logging table which can be used to capture information about rows which have caused a constraint violation during DML-execution.
Additionally we can define a limit of violations which will cause the statement to fail.
If the rejected rows are less than the specified limit the statement will succeed despite the contraint violations.

Here is a little demo:
-- create a test table
HR @10gR2 SQL > create table error_test
(
col1 number,
col2 number constraint error_check check (col2 > 100)
);
Table created.

-- populate the test table
HR @10gR2 SQL > BEGIN
2 FOR i IN 100..200 loop
3 INSERT INTO error_test
4 VALUES (i, i+50);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.

-- create the error log table
HR @10gR2 SQL > exec DBMS_ERRLOG.CREATE_ERROR_LOG('ERROR_TEST','error_log_tab')
PL/SQL procedure successfully completed.

HR @10gR2 SQL > desc error_log_tab
Name Null? Type
----------------------------------------- -------- -----------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
COL1 VARCHAR2(4000)
COL2 VARCHAR2(4000)

-- cause constraint violations less than reject limit
HR @10gR2 SQL > UPDATE error_test
2 SET col2=col2-100
3 LOG ERRORS INTO error_log_tab
4 REJECT LIMIT 80;
50 rows updated.
-- look at the error loggin table
HR @10gR2 SQL > SELECT ora_err_number$,
ora_err_mesg$,
ora_err_rowid$,
col1,
col2
FROM error_log_tab;

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ COL1 COL2
--------------- ---------------------------------------------------- ---------------------- ---------- ----------
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAA 100 50
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAB 101 51
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAC 102 52
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAD 103 53
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAE 104 54
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAF 105 55
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAG 106 56
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAH 107 57
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAI 108 58
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAJ 109 59
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAK 110 60
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAL 111 61
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAM 112 62
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAN 113 63
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAO 114 64
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAP 115 65
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAQ 116 66
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAR 117 67
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAS 118 68
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAT 119 69
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAU 120 70
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAV 121 71
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAW 122 72
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAX 123 73
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAY 124 74
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAZ 125 75
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAa 126 76
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAb 127 77
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAc 128 78
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAd 129 79
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAe 130 80
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAf 131 81
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAg 132 82
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAh 133 83
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAi 134 84
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAj 135 85
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAk 136 86
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAl 137 87
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAm 138 88
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAn 139 89
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAo 140 90
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAp 141 91
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAq 142 92
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAr 143 93
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAs 144 94
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAt 145 95
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAu 146 96
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAv 147 97
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAw 148 98
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAx 149 99
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAy 150 100

51 rows selected.
-- rollback
HR @10gR2 SQL > rollback;

-- cause constraint violations more than reject limit
HR @10gR2 SQL > HR @10gR2 SQL > UPDATE error_test
2 SET col2=col2-100
3 LOG ERRORS INTO error_log_tab
4 REJECT LIMIT 20;
UPDATE error_test
*
ERROR at line 1:
ORA-02290: check constraint (HR.ERROR_CHECK) violated

Arrived in London

I have just arrived in London City.
This week I will deliver another customized
10gR2 New Features for Administrators
course as a private event.









I am just checking out the training center
and prepare my environment for tomorrow and then I'll take a look around the city.

DOAG sent my article after me

The DOAG News has sent a copy of the latest issue
with my articel after me to my hotel in
California. It is the Greman version of my article about the Shared Pool Size implementation in Oracle 10g.
Thank you very much for this first class service!
And greetings from Redwood Shores to Berlin.

Heading back to Europe

Yesterday I had a last meal with the two project managers of the
Real Application Cluster course who have delivered the
RAC 10g pilot class for release 2, Jean-Francois Verrier and Jim Womack.
The foto shows J.F., Jim, my very dear collegue from Düsseldorf Michael Cebulla and me on Friday after we had gone to the movy theaters (Da Vinci Code) and had dinner together afterwards.


I am at SanFrancisco Airport right now,
waiting for my flight to Munich to depart.
Have just waved goddbye to Michael at the gate. He is on his way back to Düsseldorf already now.


I was so happy to find Michael in San Francisco also attending the same RAC pilot class with me. We had a fun time for five days.

Demos @ Oracle By Example on OTN

There is new site on Oracle Technology Network (OTN) with a large number of very up to date demos for best practices and new features for ORACLE 10gR2 real application clusters .

At the moment you find these 10gR2 demos under the link for Database 10gR1 in the Oracle By Example Series.

Here is a list of the demos you can find there at the moment, there will be more comming up:

-Using SQL Baselines
-Using Metric Baselines
-Install ASM single instance in its own home
-Install ASM single instance in the same home
-Migrate a database to ASM
-Setup XML DB to access ASM
-Access ASM files using ASMCMD
-Transport a tablespace version to another database
-RAC scale example
-RAC speedup example
-Use Oracle Clusterware to protect the apache application
-Use Oracle Clusterware to protect the Xclock application
-Use Transparent Application Failover (TAF) with SELECT statements
-Runtime Connection Load Balancing example
-Install ASM in its own home in a RAC environment
-Convert a single-instance database to a RAC database using Grid Control
-Push Management Agent software using Grid Control
-Clone Oracle Clusterware to extend your cluster using Grid Control
-Clone ASM home to extend your cluster using Grid Control
-Clone database home to extend your cluster using Grid Control
-Add a database instance to your RAC database using Grid Control
-Basic use of services in your RAC environment

Arrived at Redwood Shores

I just arrived at the head quarter of ORACLE in Redwood Shores (CA.). There is a "traffic sign" showing a family of Linux Pinguines crossing the street at Dolphin Drive.


I will attend the pilot class for the 10gR2 Real Application Cluster Course this week.

SQL Server World Wide Usergroup want money from me to read my own article!!!

I cannot believe it!
I just found a link to my article about flashback any error in oracle 10gR2 on the web.
It leads you to the site of the so called "SQL Server Worldwide Usergroup".
There you can pay to have access to my article,
which you of course can access for free an my blog, it is right here.
I had written this article for the Swiss Oracle Usergroup Newsletter where it was published at the beginnig of 2006.
Is it legal anyway, to just copy my article? Can I screw them?
If you know anything about the legal situation with such cases pls inform me through this blog by comment or email.

What a strange habit!
These people take my article and try to make money with it.
I guess that you can find almost any article they have there for free on the web,
but only if you know how to browse the web of course.

My New York class

Have just finished my 10gR2 New Features for Administrators class in New York.
Again we squeezed five days in to three which was a tough time for everyone of us.
And again these very highly skilled and experienced people challenged me a lot wioth their hands on questions like their collegues in Chicago an New Jersey had done before.

These are the attendants of my class this week:

Ram

Kalyan

Navin, never had an attendant
who could ask so many questions
in such a short time!

Winarko

Nitin

Eugene

We have had a lot of fun with each other.

And again I got to know a very nice collegue
of mine from New Jersey.
Wonjun Choi


Thanks a lot for
helping me out
when I felt so
left alone and locked out!

Arrived in Manhatten

After 8 hours of flight,
reading Jonathan Lewis' Cost Based Oracle,
I have arrived in New York.
I will deliver a special training
here next week.
The city is not very crowdy
because most New Yorkers
have left for a long weekend.





Contemporary art is all around here.
I will take a day off tomorrow
and get into it.
No forums, newsgroups, blogs
tomorrow...
we will see!

Beautiful Bern

This week I was in Bern, the capital of Switzerland for two days. I have delivered a specially condensed New Features training there.





Bern is one of the most charming cities I have visited .








I was happy to see that not much damage
is left from the great flood last year.

How to use Fast Incremental Backups with Block Change Tracking with Oracle 10g

In Oracle 9i we could create incremental backups with level 0 to 4.
A level 0 backup is a full backup.
In Oracle 10g there are only incremental level 0 and 1 backups.

Starting with Oracle 10g RMAN can take incremental backups without having to read the entire datafiles in order to find out which blocks have changed since the last backup.
This new feature is called FAST INCREMENTAL BACKUP.
The new technology used for this feature is called BLOCK CHANGE TRACKING.

You enbale block change tracking by making an entry in the controlfile:
SQL> alter database enable block change tracking;
alter database enable block change tracking
*
ERROR at line 1:
ORA-19773: must specify change tracking file name

If you do not have DB_CREATE_FILE_DEST specified (used for OMF => oracle managed files)
you will encounter an error if you do not specify a name fot the change tracking file
SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

So I try to alter this parameter:
SQL> alter system set db_create_file_dest='/home/oracle/file_create';
alter system set db_create_file_dest='/home/oracle/file_create'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_create_file_dest destination string cannot be
translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory

Oracle checks if the location existes before it allows us to change this parameter!
SQL> ! mkdir -p /home/oracle/file_create
SQL> alter system set db_create_file_dest='/home/oracle/file_create';
System altered.

Now we can enable block change tracking
SQL> alter database enable block change tracking;
Database altered.
As an alternative you can specify a user managed filename if you do not want to use the parameter:
alter database enable block change tracking using file '/home/oracle/change.trc';

Let's take a look at the alert_log now:
alter database enable block change tracking
Thu May 18 06:39:14 2006
ORA-19773 signalled during: alter database enable block change tracking...
Thu May 18 06:39:46 2006
alter database enable block change tracking using file
'/home/oracle/change.trc'
Thu May 18 06:39:46 2006
Block change tracking file is current.
Starting background process CTWR
CTWR started with pid=21, OS id=26871
Block change tracking service is active.
Thu May 18 06:39:47 2006
Completed: alter database enable block change tracking using file
'/home/oracle/change.trc'

This is what oerr returns for this error:
[oracle@kr12-KL2 bdump]$ oerr ora 19773
19773, 00000, "must specify change tracking file name"
// *Cause: No file name was specified with the ALTER DATABASE ENABLE
// CHANGE TRACKING command, and the DB_CREATE_FILE_DEST parameter
// was not set.
// *Action: Either specify a file name, or set the DB_CREATE_FILE_DEST
// parameter.

Let's take a look at the change tracking file:
[oracle@edchr3p8 oracle]$ cd file_create/
[oracle@edchr3p8 file_create]$ ll
total 4
drwxr-x--- 3 oracle oinstall 4096 May 3 15:55 ORCL
[oracle@edchr3p8 file_create]$ cd ORCL/
[oracle@edchr3p8 ORCL]$ ll
total 4
drwxr-x--- 2 oracle oinstall 4096 May 3 15:55 changetracking
[oracle@edchr3p8 ORCL]$ cd changetracking/
[oracle@edchr3p8 changetracking]$ ll
total 11348
-rw-r----- 1 oracle oinstall 11600384 May 3 15:55 o1_mf_25l66r5c_.chg
# this is an OMF change tracking file!

The change tracking file has an initial size of 10Mb.
How to monitor the change treacking file from inside Oracle:
SQL> desc v$block_change_tracking
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(10)
FILENAME VARCHAR2(513)
BYTES NUMBER

SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
---------- -------------------------------------------------- ----------
ENABLED /home/oracle/change.trc 11599872

Now that we have enabled block change tracking a new background process called CTWR (change track writer) is started and it will automatically be started for every new instance.
[oracle@kr12-KL2 ~]$ ps -ef grep orcl
oracle 9531 1 0 May16 ? 00:00:00 ora_pmon_orcl
oracle 9533 1 0 May16 ? 00:00:02 ora_psp0_orcl
oracle 9535 1 0 May16 ? 00:00:00 ora_mman_orcl
oracle 9537 1 0 May16 ? 00:00:06 ora_dbw0_orcl
oracle 9539 1 0 May16 ? 00:00:16 ora_lgwr_orcl
oracle 9541 1 0 May16 ? 00:00:17 ora_ckpt_orcl
oracle 9543 1 0 May16 ? 00:00:25 ora_smon_orcl
oracle 9545 1 0 May16 ? 00:00:00 ora_reco_orcl
oracle 9547 1 0 May16 ? 00:00:03 ora_cjq0_orcl
oracle 9549 1 0 May16 ? 00:00:18 ora_mmon_orcl
oracle 9551 1 0 May16 ? 00:00:02 ora_mmnl_orcl
oracle 9554 1 0 May16 ? 00:00:00 ora_d000_orcl
oracle 9556 1 0 May16 ? 00:00:00 ora_s000_orcl
oracle 9560 1 0 May16 ? 00:00:00 ora_qmnc_orcl
oracle 9574 1 0 May16 ? 00:00:06 ora_q000_orcl
oracle 10746 1 0 May16 ? 00:00:00 ora_q001_orcl
oracle 26854 26850 0 06:38 ? 00:00:00 oracleorcl (DESCRIPTION=
(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26871 1 0 06:39 ? 00:00:00 ora_ctwr_orcl
oracle 27778 1 0 06:50 ? 00:00:00 ora_j000_orcl
oracle 27780 27153 0 06:50 pts/0 00:00:00 grep orcl

CTWR will track addresses of blocks which have changed since the last backup in the change tracking file from now on.
RMAN can use this information for the next incremental backup. It will be able to find out which block must be written to the backupset by just reading the change tracking file.
RMAN will not have to read the entire datafiles into the SGA in order to find out which blocks must be backed up as it had to do before 10g.
This methode is much faster.

Now what happens if we lose the change tracking file or if it gets corrupted?
Let's corrupt is:
[oracle@edchr3p8 changetracking]$ echo 'hallo' > o1_mf_25l66r5c_.chg
And now let us startup the database with a corrupt change tracking file!
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file:
'/home/oracle/file_create/ORCL/changetracking/o1_mf_25l66r5c_.chg'
ORA-27038: created file already exists
Additional information: 1
ORA-27047: unable to read the header block of file
Linux Error: 2: No such file or directory
Additional information: 1
Wed May 3 16:03:30 2006
CHANGE TRACKING is enabled for this database, but the
change tracking file can not be found. Recreating the file.

Oracle tries to automaqtically recreate the missing file but it cannot overwrite an existing on!

Let us create some changed blocks now so Oracle cannot track them in the change tracking file!

SQL> update hr.employees set salary=salary*1.1;
107 rows updated.
SQL> commit;
Commit complete.


Now let us take a look at the ALERT_LOG:

CHANGE TRACKING ERROR 19756, disabling change tracking
Wed May 3 16:07:24 2006
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_ctwr_6628.trc:
ORA-19756: corrupt block number 18 found in change tracking file
ORA-19750: change tracking file: '/home/oracle/file_create/ORCL/changetracking/o1_mf_25l6qckh_.chg'
Block change tracking service stopping.
Deleted Oracle managed file /home/oracle/file_create/ORCL/
changetracking/o1_mf_25l6qckh_.chg

Oracle automatically deletes a currupted change tracking file and stops block change tracking.

Let us now take an incremental backup with RMAN:
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 03-MAY-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/
2006_05_03/ o1_mf_nnnd1_TAG20060503
T160745_25l6x2s1_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/
backupset/2006_05_03/o1_mf_ncsn1_TAG20060503
T160745_25l6z4mv_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-MAY-06

RMAN was able to take an incremental backup but now it had to use the old methode => read all the blocks in order to find out which ones have changed since the last backup;

Next I replace the change tracking file with an old version in order to see what happens when I try to take an incremental backup:

SYS @10gR2 SQL > ! mv /home/oracle/change.old /home/oracle/change.trc
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 19-MAY-06
using channel ORA_DISK_1
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_nnnd1_TAG20060519T123227_26v7owgo_.bk p tag=
TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_example_26v7oyjx_.dbf tag=TAG20060519T123227
recid=3 stamp=590848373
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/sales1.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_sales_26v7pr8j_.dbf tag=TAG20060519T123227 rec id=4 stamp=590848386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_users_26v7qb8t_.dbf tag=TAG20060519T123227 rec id=5 stamp=590848394
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_ncsn1_TAG20060519T123227_26v7qfb5_.bk p tag=TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
RMAN> list backup of tablespace sysaux;
There is no backup!!!
RMAN has found out that I tried to cheat and that it was not able to create an incremental backup for all files by using the change tracking file.
This is what oerr says about ORA-19694:
[oracle@lutzasm ~]$ oerr ora 19694
19694, 00000, "some changed blocks were not found in the change tracking file"
// *Cause: A backup or copy found that some changed blocks had not been
// recorded in the change tracking file. The details of which files
// and blocks are affected will be in an Oracle trace file.
// *Action: This indicates that there is a problem with the change tracking
// feature. Disable change tracking and re-start the backup.

How to check if a tablespace is transportable

In order to find out if a tablespace or a tablespace set is transportable you can use the package DBMS_TTS.

This is the interface of the package:
sys@10gr2 SQL> desc dbms_tts
PROCEDURE DOWNGRADE
FUNCTION ISSELFCONTAINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN
FULL_CHECK BOOLEAN IN
PROCEDURE KCP_CKCMP
FUNCTION TRANSPORT_CHAR_SET_CHECK RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
ERR_MSG VARCHAR2 OUT
PROCEDURE TRANSPORT_CHAR_SET_CHECK_MSG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
PROCEDURE TRANSPORT_SET_CHECK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN DEFAULT
FULL_CHECK BOOLEAN IN DEFAULT

This is how you use the package:
sys@10gr2 SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('LUTZ_TS', TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK('SYSTEM', TRUE)
BEGIN dbms_tts.TRANSPORT_SET_CHECK('SYSTEM', TRUE); END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 899
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 838
ORA-29351: can not transport system, sysaux, or temporary tablespace 'SYSTEM'
ORA-06512: at "SYS.DBMS_TTS", line 867
ORA-06512: at line 1

After you have checked the tablespace with the procedure TRANSPORT_SET_CHECK you can use a table call transport_set_violations which is owned by SYS to look at error messages from the procedure.
This table will contain the messages from the last run of the procedure and it will retained only for the duration of the session.
In other words, it will be refreshed with every new session.

sys@10gr2 SQL> desc transport_set_violations
Name Null? Type
----------------------------------------- -------- ---------------------------- VIOLATIONS VARCHAR2(2000)


Now SYS creates a table in the tablespace I want to check ...
sys@10gr2 SQL> create table test_sys tablespace lutz_ts as select * from hr.test;
Table created.

And I check if the tablspace is transportable...

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK('LUTZ_TS', TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------Sys owned object TEST_SYS in tablespace LUTZ_TS not allowed in pluggable set

This would be the error message if the tablespace is not self containing...

sys@10gr2 SQL> sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Constraint TESXT_FK between table SYS.TEST_SYS in tablespace USERS and table HR.
TEST in tablespace LUTZ_TS

Starting with Oracle 10gR2 we can create transportable tablespaces from backup.

CS CRUISING ORACLE

Right after I had arrived from New Jersey I delivered a short training at a very special event which Credit-Suisse had aranged.
They had hired a ship on the Lake Zurich and invited their top DBAs to come and cruise Oracle for one day.

I had the first 30 minute slot and talked about New Features of RMAN in Orcale 10gR2.

All together there were five speakers on this event
amoung others
Christina Antonigni,

who talked about Automatic Segment Space Management


and Jonathan Lewis

who flew in by helicopter so that he could make it in time from his Indexing and Troubleshooting Seminar at the Zurich Development Center. He was picked up by the boat on the way.

Jonathan Lewis talked about the propper usage of V$ and X$-Views.

I got to know Lutz Fröhlich, who is an ORACLE 9i CERTIFIED MASTER and was recently hired by CS. He is the leader of the DWH-team now. Sorry, but I forgot to take a picture

Jonathan Lewis Event in Zurich May 11.th-12th. 2006

I am so lucky that I made it to Zurich in time and can take part in this very special session at least.

Jonathan Lewis delivers a very highly condensed presentation in a two day event.
The first day, which I missed most of, what a pity!, was about INDEXING STRATEGIES.
Today he speakes about TROUBLE-SHOOTING and TUNING.


There are about 40 people attending.
Again Oracle Education chose this fantastic










ZURICH DEVELOPMENT CENTER as location for the event.










Jonathan made a very funny "demo of ORACLE 11g"
which he faked, and I believe most of us believed he already had access to the next release. Look at this, it's Oracle 11g but it is faked , dont't believe what you see here!










He "demonstrated" something he called HAL
(stands for Heuristic Algorythmic Linguistic:

=> sees what happened
=> worksout what to do next
=> tells you all about it)
and was "implemented" through a hint /*+go_faster*/



This was really fun!

One lucky attendee won a huge model of the Oracle yacht which was taking part the last Americas Cup.

Every attendee got a copy of Jonthan Lewis' latest book on the CBO.

Iselin NJ Event part 2

Yesterday I have finished my special Class in New Jersey education center.
These guyes again gave me a hard time , trying to even be harder than their collegues in Chicago last week.

It was a group of also very experienced DBAs
and we had a fun time.
These are Ying, Mark, Alex, Miguel, Mangerh,
Sridhar and me on the last day






In the New Jersey Oracle Education Center
I got to know a very nice collegue of mine from Dallas.
His name is Lester Wells.
He delivered a Grid Control class there.
Thanks for being a light in the dark for me Lester!


Right after the event I headed for Kennedy Airport





and left for Zurich.



On the plane was a large group of orthodox Jews
who all of a sudden assembled in the middle of the plane

for praying.

Having arrived in Zurich I headed directely to the
Zurich Development Centerin order not to miss
Jonathan Lewis.

I will report in my next post.

Iselin NJ Event Part 1

Just arrived in Iselin New Jersey.
I will deliver a specially tailored
New Features for Administrators 10gR2 this week once again.

My Chicago Training

This week I delivered a customized training for a bunch of very advanced DBAs in Chicago.



The location of the Training Center is so sophisticated (the walls in the building are all made of green marble from ground to top floor, well at least to 14th. floor, that's where the Oracle Training Center is located)



These guyes have really given me a hard time.
They were not interested in the courseware at all
but only in the technical internals of the New Features.
Hardest course I had, ever!
But fun also.

Somashekar

Alan

Selvam

Bhavesh

Warren

Sorry, I forgot to take a picture of Ravi.

They are all very highly skilled DBAs, never trusting any new features, always trying everything out until they really have proved that it does what they expect it to do.
It was a pleasure meeting you guys.

Active Session History in Oracle 10gR1+2

This article has been published in the Swiss Oracle User Group's Newsletter 4/2006 (October 2006).

This article descibes the new on Oracle Database 10g Active Session History, a kind of data warehouse for session statistics.

A lot of the information in this post I have taken from the book Oracle Wait Interface by the
authors R.Shee, K.Deshpande and K.Gopalakrishnan
which I can strongly recommed to everybody
who has to deal with performance tuning for ORACLE of which release ever.
It covers the Oracle versions until 10gR1 and tells us everything we have ever wanted to know about latches, enqueues and all the wait events.


Starting with the version 10gR1 Oracle stores information about session activity on disk in the Automatic Workload Repository (AWR) which sits, amoung other things, in the new mandatory SYSAUX tablespace.
This information comes from statistics about active sessions which the Oracle Kernel collects automatically.
The active session history is enabled by default( influenced by the hidden parameter _ASH_ENABLE=true).

The main components of the ASH are
- two new background procresses, MMON and MMNL,
- a rolling buffer, which sits in the SGA fixed area and holds the historical statistical information of active sessions

This buffer has a minimum size of 1Mb in ORACLE 10gR1 and can grow to a maximum of either 30Mb, or 5% of SHARED_POOL_SIZE, or Numer of CPUs * 2Mb (whichever is smaller!).

This formula has been changed in 10gR2 to not linearly scale with
the number of CPUs.
All we get to know about the sizing is that "...the ASH will
auto-configure itself to try and hold at least one-hour worth of
instance activity without wasting too much value SGA space and will
never be bigger than 2% of SGA_TARGET or 5% of SHARED_POOL (if AutoSGA
is not used)".


The contents of the rolling buffer get written down to disk on a regular basis by MMNL
( with every snapshot created and when the buffer gets full).
It gets flushed to the table WRH$_ACTIVE_SESSION_HISTORY in the AWR which is implemented like a facts table in a DWH with its columns as dimensions.
This is enbled with the hidden parameter _ASH_DISK_WRITE_ENABLE=true.

The information in the ASH is used, among others, by automatic tuning utilities like teh SQLTUNING Advisor (dbms_sqltune) for the Automatic Tuning Optimizer (ATO). This one will look for historical data about sql-statements and their exetutions in the ASH (see buttom) as well, anong other activities it can perform in order top find a potentially better execution plan.

There are a lot of DBA_ views we can use to read the historical information from the AWR:
SELECT table_name
FROM dictionary
WHERE table_name like 'DBA/_HIST/_%' ESCAPE '/'
ORDER BY table_name;

TABLE_NAME
-----------------------------
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_BASELINE
DBA_HIST_BG_EVENT_SUMMARY
DBA_HIST_BUFFERED_QUEUES
DBA_HIST_BUFFERED_SUBSCRIBERS
DBA_HIST_BUFFER_POOL_STAT
DBA_HIST_COMP_IOSTAT
DBA_HIST_CR_BLOCK_SERVER
DBA_HIST_CURRENT_BLOCK_SERVER
DBA_HIST_DATABASE_INSTANCE
DBA_HIST_DATAFILE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_DLM_MISC
DBA_HIST_ENQUEUE_STAT
DBA_HIST_EVENT_NAME
DBA_HIST_FILEMETRIC_HISTORY
DBA_HIST_FILESTATXS
DBA_HIST_INSTANCE_RECOVERY
DBA_HIST_INST_CACHE_TRANSFER
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_LATCH
DBA_HIST_LATCH_CHILDREN
DBA_HIST_LATCH_MISSES_SUMMARY
DBA_HIST_LATCH_NAME
DBA_HIST_LATCH_PARENT
DBA_HIST_LIBRARYCACHE
DBA_HIST_LOG
DBA_HIST_METRIC_NAME
DBA_HIST_MTTR_TARGET_ADVICE
DBA_HIST_OPTIMIZER_ENV
DBA_HIST_OSSTAT
DBA_HIST_OSSTAT_NAME
DBA_HIST_PARAMETER
DBA_HIST_PARAMETER_NAME
DBA_HIST_PGASTAT
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_PROCESS_MEM_SUMMARY
DBA_HIST_RESOURCE_LIMIT
DBA_HIST_ROWCACHE_SUMMARY
DBA_HIST_RULE_SET
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
DBA_HIST_SERVICE_NAME
DBA_HIST_SERVICE_STAT
DBA_HIST_SERVICE_WAIT_CLASS
DBA_HIST_SESSMETRIC_HISTORY
DBA_HIST_SESS_TIME_STATS
DBA_HIST_SGA
DBA_HIST_SGASTAT
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_SNAPSHOT
DBA_HIST_SNAP_ERROR
DBA_HIST_SQLBIND
DBA_HIST_SQLSTAT
DBA_HIST_SQLTEXT
DBA_HIST_SQL_BIND_METADATA
DBA_HIST_SQL_PLAN
DBA_HIST_SQL_SUMMARY
DBA_HIST_SQL_WORKAREA_HSTGRM
DBA_HIST_STAT_NAME
DBA_HIST_STREAMS_APPLY_SUM
DBA_HIST_STREAMS_CAPTURE
DBA_HIST_STREAMS_POOL_ADVICE
DBA_HIST_SYSMETRIC_HISTORY
DBA_HIST_SYSMETRIC_SUMMARY
DBA_HIST_SYSSTAT
DBA_HIST_SYSTEM_EVENT
DBA_HIST_SYS_TIME_MODEL
DBA_HIST_TABLESPACE_STAT
DBA_HIST_TBSPC_SPACE_USAGE
DBA_HIST_TEMPFILE
DBA_HIST_TEMPSTATXS
DBA_HIST_THREAD
DBA_HIST_UNDOSTAT
DBA_HIST_WAITCLASSMET_HISTORY
DBA_HIST_WAITSTAT
DBA_HIST_WR_CONTROL
78 rows selected.

The rolling buffer can be viewed by querying the relevant v$view:
SELECT table_name
FROM dictionary
WHERE table_name like 'V$%SESSION%HISTORY'
ORDER BY table_name;

TABLE_NAME
------------------------------
V$ACTIVE_SESSION_HISTORY
V$SESSION_WAIT_HISTORY
2 rows selected.
You can use a query like this to look at the session activity in a certain peride of time with a query like this:
SELECT session_id, event, COUNT(*), SUM(time_waited)
FROM v$active_session_history
WHERE session_state= 'WAITING'
AND time_waited > 0
AND sample_time >= (sysdate-&MinutesAgo/(24*60))
GROUP BY session_id, event;

Enter value for minutesago: 5
old 5: AND sample_time >= (sysdate-&MinutesAgo/(24*60))
new 5: AND sample_time >= (sysdate-5/(24*60))

SESSION_ID EVENT COUNT(*) SUM(TIME_WAITED)
---------------- -------- ------------- ------------------------------
149 db file sequential read 3 40857
149 db file scattered read 1 1530
164 db file sequential read 1 12208

The autors also describe how to dump the contents of the rolling buffer to a trace file in USER_DUMP_DEST in order to use sqlldr to load the information into another database.

ALTER SESSION SET events 'immediate trace name ashdump, level 10';

You can also use the utility oradebug to dump the rolling buffer:


SYS @10gR2 SQL > oradebug setmypid
Statement processed
SYS @10gR2 SQL > oradebug unlimit
Statement processed
SYS @10gR2 SQL > oradebug dump ashdump 10
Statement processed
SYS @10gR2 SQL > oradebug tracefile_name
Statement processed
SYS@10gR2 SQL > oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5851.trc

-- first lines: column names for data
-- trace data displayed as comma separated list of values
-- can use sqlldr to load the data into another DB for further analysis
-- can be helpfull in cases of hanging system

Ginza Restaurant in Chicago Downtown - A must for Sushi lovers!

Tonight I had luch at nice little Sushi Restaurant

It is wonderful little place, not too crowded with a very relaxing ambiente: silent japanese music and nice people.
Here I got the best Sushi I have ever tasted.
Tonight they had a very special sushi with an egg of a japanese snapper. Never had that before. Wow!
Here is the contact:
Ginza Restaurant (Japanese Cuisine)
Chicago, IL
19 E. Ohio St.
Fon. 312-222-0600
Fax. 312-222-0919

Lunch at the Hooters

These guyes took me out for lunch at the Hooters today.
It is a funny place where you can get loads of very good chicken wings and not only chicken wings...

this blog is a success!! thank you for using it!

Looking at the statistics of my blog I get very happy.
I seam to write about things which are interesting for people.

Thank you for coosing my blog!
And thank you for your feedback and comments!

Chicago I

Just arrived in Chicago Downtown.
I will deliver a customized 10gR2 New Features for Administrators as a private event next week (May 1st.-3rd).

Haved been reading "Cost based Oracle" by Jonathan Lewis during the flight. This book is like a thriller for me - really exciting!

ORACLE SECURE BACKUP now available

ORACLE has announced general availability of its new Media Management Server Oracle Secure Backup and Oracle Secure Backup Express.

This is Oracle's new tape backup management software, delivering secure, high performance network tape backup for Oracle databases and file systems.
With Oracle Secure Backup (OSB) you can not only backup database files but also the entire ORACLE stack including
- application servers
- collaboration suites
as well as OS-file systems.

OSB is capable of discovering tape libraries which are available within a so called administrative doamin (which you define), it can handle barcode readers and change tapes and you can use it to backup any maschine on which either an OSB client is running or an NDMP-demon (network data management protocol) is started.
OSB is strongly integrated with RMAN and can be accessed
- through a web interface
- a command line tool
- enterprise manager

For more info pls see the following locations:
The official announcement is here.
The product description can be found here.
Technical information for DBAs and Developers can be found here.
A white paper about OSB on OTN is here.
OSB on OTn is here.

How to adjust the high watermark in ORACLE 10g - ALTER TABLE SHRINK

Hanging around in my hotel in Chicago because it is raining, I have been cruising around in the OTN forums which inspired me to write something about the High Watermark and the Oracle 10gR1 New Feature SEGMENT SHRINKING.

The High Watermark is the maximum fill-grade a table has ever reached.
Above the high watermark are only empty blocks.
These blocks can be formatted or unformatted.

First let's have a look at the question when space is allocated

- when you create a table at least one extent (contiguous blocks) is allocated to the table
- if you have specified MINEXTENTS the number of MINEXTENTS extents
will be allocated immedaitely to the table
- if you have not specified MINEXTENTS then exactely one extent
will be allocated (we will look at extent sizes later in another post).

Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.

When you insert rows into the table the high watermark will be bumped up step by step.
This is done by the server process which makes the inserts.

Now let us take a look at when space is released again from a segment like a table or index:

Let's asume that we have filled a table with 100'0000 rows.
And let's asume that we deleted 50'000 rows afterwards.
In this case the high watermark will have reached the level of 100'000 and will have stayed there. Which means that we have empty blocks below the high watermark now.
Oracle has a good reason this: it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary
(=> dba_free_space, dba_extents, dba_segements ...) .
Furthermore the physical addresses of the deleted row get recycled by new rows.

These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:

1. seriell direct load:
INSERT /*+ APPEND */
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

2. parallel direct load:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLLEL(hr.employees,2)
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

3. direct path loads:
sqlldr hr/hr control=lcaselutz.ctl ... direct=y (default is direct=n)

All the above actions case that the SGA is not used for the inserts but the PGA:
there wil be temporary segements filled and dumped into newly formatted blocks above the high watermark.

So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.

So how can we release unused space from a table?

There are a number of possible options which are already available before Oracle 10g:
- What we always could do is export and import the segment.
After an import the table will have only one extent.
The rows will have new physical addresses and
the high watermark will be adjusted.
- Another option would be to TRUNCATE the table.
With this we would loose all rows which are in the table.
So we cannot use this if we want to keep existing records.


With Oracle 9i another possibilty was implemented:
ALTER TABLE emp MOVE TABLESPACE users;
This statement will also cause that
- the rows will have new physical addresses and
- the high watermark will be adjusted.
But for this:
- we need a full (exclusive) table lock
- the indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.


Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management.
In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:
- the High High Watermark referred to as HHWM, above which alle blocks ar unformatted.
- the Low High Watermark referred to as LHWM below which all blocks are formatted.
We now can have unformatted blocks in the middle of a segment!

ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle
to move rows which are located in the middle or at the end of a segment
further more down to the beginning of the segment and by
this make the segment more compact.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing
ALTER TABLE emp ENABLE ROW MOVEMENT;
ROWIDs are normally assigned to a row for the life time of the row at insert time.

After we have given Oracle the permission to change the ROWIDs
we can now issue a shrink statement.
ALTER TABLE emp SHRINK SPACE;

This statement will procede in two steps:
- The first step makes the segment compact
by moving rows further down to free blocks at the beginning of the segment.
- The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,
but for a very short moment only.

Table shrinking...
- will adjust the high watermark
- can be done online
- will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
- indexes will be maintained and remain usable
- can be made in one go
- can be made in two steps
(this can be usefull if you cannot get a full table lock during certain hours:
you only make the first step and adjust the high watermark later
when it is more conveniant:

- ALTER TABLE emp SHRINK SPACE; -- only for the emp table
- ALTER TABLE emp SHRINK SPACE CASCADE; -- for all dependent objects as well

- ALTER TABLE emp SHRINK SPACE COMPACT; -- only makes the first step (moves the rows)
)

The following restrictions apply to table shrinking:

1.) It is only possible in tablespaces with ASSM.
2.) You cannot shrink:
- UNDO segments
- temporary segments
- clustered tables
- tables with a colmn of datatype LONG
- LOB indexes
- IOT mapping tables and IOT overflow segments
- tables with MVIEWS with ON COMMIT
- tables with MVIEWS which are based on ROWIDs

The Oracle 10g Oracle comes with a Segment Advisor utility.
The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates.

This advisor is run automatically by an autotask job on a regular basis in the default maintainance window.

You can use the built in package DBMS_SPACE to run the advisor manually as well.
I will blog about this later on some time.

Jonathan Lewis, the master of the cost based optimizer

I am reading the latest book by Jonathan Lewis, Cost-Based Oracle Fundamentals right now.
It is a compilation of all the in depth knowlege about the Cost-Based Optimizer we have been looking for since ages.
And it covers the history of the CBO from ORACLE 8i until ORACLE 10g.

It is really amazing how Jonathan Lewis explains, step by step,
what the CBO is all about and how it works.
And this in a way which can be understood even by non-insider-gurus,
although he really goes into details.
Jonathan Lewis encourages the reader to try the examples from the book practically in real life and find out what happens. The sample code is available for download from the apress site.

As Tom Kyte wrote in the foreward of the book:
"The insight that Jonathan provides into the working of the CBO will make a DBA a better designer and a developer a better SQL coder. Both groups will become better troubleshooters".

I can only recommed this book to everybody who has to deal with ORACLE performance issues.
It will be worth the money.

I am really looking foreward to meeting Jonathan Lewis in Zurich on May 11th.-12th.
where he will deliver some very special trainings about troubleshooting and indexing.
For everybody who might be interested in taking part in this event, here is the info about bookings.

Asynchronous Commit - a New Feature in ORACLE 10gR2

"In the old times" the LGWR had to write to the redo-logs with every COMMIT.

Starting with ORACLE 10gR2 we can use the initialization parameter
COMMIT_WRITE to change the commit behaviour
on SYSTEM- as well as on SESSION-LEVEL.

We can allow LGWR to consider itself
- when to write to disk and
- when control is returned to the client.

By specifying WAIT or NOWAIT you can influence when LGWR returns control to the client.
By specifiying IMMEDIATE or BATCH you can influence when LGWR will write to disk.


If you do not explicitly specify this advanced parameter:
- - the default is IMMEDIATE, WAIT
- the database by default will write commit records to disk for every commit
- control is returned to the client after the writing is completed.
- the client receives the massage COMMIT COMPLETE.

If you set COMMIT_WRITE to NOWAIT and you do not specify anything else:
- the default is IMMEDIATE, NOWAIT
- LGWR will write to the redologs directely after every commit
- it will not wait until the writing is completed before it sends the next writing process to the redologs
- the client will have control before the writing is completed.

If you set COMMIT_WRITE to BATCH and you do not specify anything else:
- the default is BATCH, WAIT
- LGWR will consider itself when to write to disk from the LOG_BUFFER
- LGWR will not write with every COMMIT
- the client must wait until the I/O of LGWR is completed

Changing this parameter has its drawbacks and advantages and it is you who has to descide which death to dye:
- the majour drawback is obviousely that you cannot be sure anymore that your changes are persistently saved even after commit which can mean that you might lose even committed data in the case of an instance crash.
- the advantage can be that concurrently committing sessions do not have to to wait for each other 's commit to complete => less wait events.
This parameter, again, is not a basic but an advanced parameter, which means that the default is o.k. in the first place and you would think about changing its value to whatever only after you encounter significant numbers of wait event with concurrent commits.
Because it is good to know that the committed changes are persistently changed and can be applied again in case of crash recovery.

•

You change the parameter by providing the values separated by a comma:
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT;

POSSIBLE COMBINATIONS:
- IMMEDIATE,NOWAIT
- IMMEDIATE,WAIT
- BATCH,NOWAIT
- BATCH,WAIT

Transportable Tablespaces from Backup with RMAN in Oracle 10g Release 2

Before Oracle 10g it was also possible to create transportable tablespaces
(they were introduced with ORACLE 8i):
we have used export-utility (exp) to create them.
- first we had to make the tablespace READ ONLY before exporting,
- then we had to used export utility to dump the tablespace metadata.
(In the exp-dumpfile only the metadata of the tablespace are dumped)
- and after we shipped the datafiles from the source system to the target system we could use the import utility(imp) to import the metadata from the dumpfile and
- finally we could take the tablespace in the target system read write.

This procedure had a number of significant limitations:
1. The source- and target-platforms had to be exactely the same(up to the last OS-package)
2. The source- and target-database had to exactely the same version (up to the last patchset)
3. The tablespace had to be made READ ONLY before exporting the metadata

Since ORACLE 10gR2 we can now use RMAN to create a transportable tablespace-set from Backup.
Now we do not need to make the tablespace(s) READ ONLY any more.

Here are the steps RMAN will take to do so:
1. RMAN connects to the source-database
2. RMAN creates an AUXILIARY instance and an AUXILIARY database which constists of a SYSTEM- and an UNDO-tablespace and the tablespace(s) we want to export. For this RMAN uses a backupset of the source database.
3. RMAN recovers the AUXILIARY database to the desired point in time
4. RMAN uses export-datapump (expdp) to create a dumpfile-set:
this consists of:
- the the datafiles which represent the transportable-tablespace-set,
- the dumpfile of the export con taining the metadata of the tablespace-set
- a sql-script which can be used to import the metadata into the target system
5. RMAN removes the AUXILIARY instance from the host.

And here is a demo:
I have written a little sql-script which contains an RMAN-run-block:

[oracle@lutz1 oracle]$ rman target / @tts.sql

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 7 02:57:33 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1106297266)
RMAN> run {
2> TRANSPORT TABLESPACE 'LUTZ_TEST'
3> AUXILIARY DESTINATION '/home/oracle/AUX/tts'
4> DUMP FILE 'lutz_test.dmp'
5> EXPORT LOG 'lutz_test.log'
6> IMPORT SCRIPT 'lutz_test_im.sql'
7> TABLESPACE DESTINATION '/home/oracle/AUX/tts'
8> UNTIL TIME="to_date('07-02-2006:02:57:00', 'dd-mm-yyyy:hh24:mi:ss')";
9> }

using target database control file instead of recovery catalog
RMAN-05026: WARNING: presuming following set of tablespaces applies
to specified point in time

List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1

Creating automatic instance, with SID='yigu'

initialization parameters used for automatic instance:
db_name=ORCL
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ORCL_yigu
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/home/oracle/AUX/tts
control_files=/home/oracle/AUX/tts/cntrl_tspitr_ORCL_yigu.f


starting up automatic instance ORCL

Oracle instance started
Total System Global Area 201326592 bytes
Fixed Size 1218508 bytes
Variable Size 146802740 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until time "to_date('07-02-2006:02:57:00', 'dd-mm-yyyy:hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 07-FEB-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=36 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/ORCL/
autobackup/2006_02_07/o1_mf_s_581741757_1yhzkgp5_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/opt/oracle/flash_recovery_area/ORCL/
autobackup/2006_02_07/o1_mf_s_581741757_1yhzkgp5_.bkp tag=TAG20060207T025557
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06
output filename=/home/oracle/AUX/tts/cntrl_tspitr_ORCL_yigu.f
Finished restore at 07-FEB-06

sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_SBT_TAPE_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('07-02-2006:02:57:00', 'dd-mm-yyyy:hh24:mi:ss')";
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination filename for restore
set newname for clone datafile 3 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 6 to
"/home/oracle/AUX/tts/lutz_test.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 3, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 3 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "LUTZ_TEST", "SYSTEM",
"UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 07-FEB-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy recid=2 stamp=581741278 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_system_1yhyzg1w_.dbf
destination for restore of datafile 00001:
/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001
output filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_system_1yhzoc4n_.dbf recid=8 stamp=581741970
channel ORA_AUX_DISK_1: restoring datafile 00002
input datafile copy recid=5 stamp=581741387 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_undotbs1_1yhz5np1_.dbf
destination for restore of datafile 00002: /home/oracle/AUX/tts/
TSPITR_ORCL_YIGU/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00002
output filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_undotbs1_1yhzrccl_.dbf
recid=9 stamp=581741983
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy recid=3 stamp=581741352 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_sysaux_1yhz2s7c_.dbf
destination for restore of
datafile 00003: /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00003
output filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_sysaux_1yhzrm6x_.dbf
recid=10 stamp=581742036
channel ORA_AUX_DISK_1: restoring datafile 00006
input datafile copy recid=6 stamp=581741397 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_lutz_tes_1yhz63wv_.dbf
destination for restore of datafile 00006:
/home/oracle/AUX/tts/lutz_test.dbf
channel ORA_AUX_DISK_1:
copied datafile copy of datafile 00006
output filename=/home/oracle/AUX/tts/lutz_test.dbf
recid=11 stamp=581742044
Finished restore at 07-FEB-06

datafile 1 switched to datafile copy
input datafile copy recid=12 stamp=581742047 filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_system_1yhzoc4n_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=13 stamp=581742048 filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_undotbs1_1yhzrccl_.dbf
datafile 3 switched to datafile copy
input datafile copy recid=14 stamp=581742048 filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_sysaux_1yhzrm6x_.dbf
datafile 6 switched to datafile copy
input datafile copy recid=15 stamp=581742048 filename=/home/oracle/AUX/tts/lutz_test.dbf

sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 6 online

Starting recover at 07-FEB-06
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 10 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/
archivelog/2006_02_07/o1_mf_1_10_1yhzo2j8_.arc
archive log filename=/opt/oracle/flash_recovery_area/ORCL/
archivelog/2006_02_07/o1_mf_1_10_1yhzo2j8_.arc
thread=1 sequence=10media recovery complete, elapsed time: 00:00:01
Finished recover at 07-FEB-06
database opened

contents of Memory Script:
{
#mark read only the tablespace that will be exported
sql clone "alter tablespace LUTZ_TEST read only";
# create directory for datapump export
sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''
/home/oracle/AUX/tts''";
# export the tablespaces in the recovery set
host 'expdp userid=\"/@\(DESCRIPTION=\(ADDRESS=(PROTOCOL=beq\)(PROGRAM=/opt/oracle/product/db10_2/bin/oracle\)
\(ARGV0=oracleyigu\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)
(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=yigu^'\)\)
\(CONNECT_DATA=\(SID=yigu\)\)\) as sysdba\" transport_tablespaces=
LUTZ_TEST dumpfile=
lutz_test.dmp directory=
STREAMS_DIROBJ_DPDIR logfile=
lutz_test.log';
}
executing Memory Script

sql statement: alter tablespace LUTZ_TEST read only
sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/home/oracle/AUX/tts''

Export: Release 10.2.0.1.0 - Production on Tuesday, 07 February, 2006 3:01:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)
(PROGRAM=/opt/oracle/product/db10_2/bin/oracle)(ARGV0=oracleyigu)
(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\))
(ENVS=ORACLE_SID=yigu))(CONNECT_DATA=(SID=yigu))) AS SYSDBA" transport_tablespaces= LUTZ_TEST dumpfile=lutz_test.dmp directory=STREAMS_DIROBJ_DPDIR logfile=lutz_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/AUX/tts/lutz_test.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 03:02:44

host command complete
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'lutz_test.dmp' transport_datafiles= /home/oracle/AUX/tts/lutz_test.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/home/oracle/AUX/tts/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/home/oracle/AUX/tts';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'lutz_test.dmp';
dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'lutz_test.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /home/oracle/AUX/tts/cntrl_tspitr_ORCL_yigu.f
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_system_1yhzoc4n_.dbf
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_undotbs1_1yhzrccl_.dbf
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_sysaux_1yhzrm6x_.dbf
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_temp_1yhzvhkk_.tmp
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog/
o1_mf_1_1yhztxdl_.log
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog/
o1_mf_2_1yhzv0p5_.log
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog/
o1_mf_3_1yhzv4gp_.log
deleted

Recovery Manager complete.

[oracle@lutz1 oracle]$ ll /home/oracle/AUX/tts/
total 10344
-rw-r----- 1 oracle oinstall 10493952 Feb 7 03:01 lutz_test.dbf
-rw-r----- 1 oracle oinstall 65536 Feb 7 03:02 lutz_test.dmp
-rw-r--r-- 1 oracle oinstall 2075 Feb 7 03:03 lutz_test_im.sql
-rw-r--r-- 1 oracle oinstall 1130 Feb 7 03:02 lutz_test.log
drwxr-x--- 4 oracle oinstall 4096 Feb 7 02:58 TSPITR_ORCL_YIGU

[oracle@lutz1 oracle]$ cat /home/oracle/AUX/tts/lutz_test_im.sql
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'lutz_test.dmp' transport_datafiles= /home/oracle/AUX/tts/lutz_test.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS
'/home/oracle/AUX/tts/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS
'/home/oracle/AUX/tts';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'lutz_test.dmp';
dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'lutz_test.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first
IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------

[oracle@lutz1 oracle]$ cat /home/oracle/AUX/tts/lutz_test.log

Export: Release 10.2.0.1.0 - Production on Tuesday, 07 February, 2006 3:01:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
- Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)
(PROGRAM=/opt/oracle/product/db10_2/bin/oracle)(ARGV0=oracleyigu)
(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=yigu))
(CONNECT_DATA=(SID=yigu))) AS SYSDBA" transport_tablespaces= LUTZ_TEST dumpfile=lutz_test.dmp directory=STREAMS_DIROBJ_DPDIR logfile=lutz_test.log
Processing object type
TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type
TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table
"SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/AUX/tts/lutz_test.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01"
successfully completed at 03:02:44

[oracle@lutz1 oracle]$ ls -rtl /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/*
/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog:
total 0

Resumable space allocation in ORACLE 10g

Since Oracle 9i we can cause a session which runs into a space allocation problem
to halt adn wait for the resolution of the space problem
instead of rolling back the staement
by using the resumable space allocation option.

In 9i this could be done only on session level.
Since Oracle 10gR1 the parameter RESUMABLE_TIMEOUT
can also be set on system level for all sessions;

Parameter type	Integer
Default value	0 (seconds)
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	0 to 231 - 1 (in seconds)
Real Application Clusters	Multiple instances can have different values.

Here is a little demo on session level:

SYS @ orcl AS SYSDBA SQL > CREATE USER lutz IDENTIFIED BY lutz
DEFAULT TABLESPACE lutz_klein
QUOTA 1m ON lutz_klein;
User created.

SYS @ orcl AS SYSDBA SQL > GRANT CREATE SESSION , CREATE TABLE TO lutz;
Grant succeeded.

LUTZ @ orcl SQL > BEGIN FOR i IN 1..10000 LOOP
2 INSERT INTO lutz_small VALUES(i);
3 END LOOP;
4 COMMIT;
5 END;
6 /
PL/SQL procedure successfully completed.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'LUTZ_KLEIN'

LUTZ @ orcl SQL > SELECT COUNT(*) FROM lutz_small;
COUNT(*)
----------
40000
As we can see, the server made a transaction level rollback.

LUTZ @ orcl SQL > ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
ERROR:
ORA-01031: insufficient privileges

SYS @ orcl AS SYSDBA SQL > GRANT RESUMABLE TO lutz;
Grant succeeded.
LUTZ @ orcl SQL > ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
Session altered.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;

alert_orcl.log ===>>
Thu Apr 6 10:12:48 2006pr 6 10:59:03 2006
statement in resumable session 'User LUTZ(62), Session 142, Instance 1' was suspended due to
ORA-01536: space quota exceeded for tablespace 'LUTZ_KLEIN'

SYS @ orcl AS SYSDBA SQL > DESC dba_resumable
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID NUMBER
SESSION_ID NUMBER
INSTANCE_ID NUMBER
COORD_INSTANCE_ID NUMBER
COORD_SESSION_ID NUMBER
STATUS VARCHAR2(9)
TIMEOUT NUMBER
START_TIME VARCHAR2(20)
SUSPEND_TIME VARCHAR2(20)
RESUME_TIME VARCHAR2(20)
NAME VARCHAR2(4000)
SQL_TEXT VARCHAR2(1000)
ERROR_NUMBER NUMBER
ERROR_PARAMETER1 VARCHAR2(80)
ERROR_PARAMETER2 VARCHAR2(80)
ERROR_PARAMETER3 VARCHAR2(80)
ERROR_PARAMETER4 VARCHAR2(80)
ERROR_PARAMETER5 VARCHAR2(80)
ERROR_MSG VARCHAR2(4000)


SYS @ orcl AS SYSDBA SQL > SELECT name, SQL_TEXT, STATUS, TIMEOUT FROM dba_resumable;
NAME SQL_TEXT STATUS TIMEOUT
---------- ------------------------------------------------- --------- ----------
User LUTZ( insert into lutz_small select * from lutz_small SUSPENDED 600
62), Session 142, Instance 1


SYS @ orcl AS SYSDBA SQL > ALTER USER lutz QUOTA UNLIMITED ON lutz_klein;
User altered.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;
40000 rows created.

alert_orcl.log ===>>
Thu Apr 6 11:05:29 2006
statement in resumable session 'User LUTZ(62), Session 142, Instance 1' was resumed

How to check for and repair block corruption with RMAN in Oracle 9i and Oracle 10g

Problem: the application encounters an ORA-01578 runtime error because there are one or more corrupt blocks in a table it is reading.

How can corrupt blocks be caused?
First of all we have two diffent kinds of block corruption:
- physical corruption (media corrupt)
- logical corruption (soft corrupt)
Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;
Logical corrution can amoung other reasons be caused by an attempt to recover through a NOLOGGING action.
There are two initialization parameters for dealing with block corruption:
- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)
causes 1-2% performance overhead
- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)
causes 1-10% performance overhead
If performance is not a big issue then you should use these!

Normally RMAN checks only for physically corrupt blocks
with every backup it takes and every image copy it makes.
This is a common misunderstanding amoung a lot of DBAs.
RMAN doesn not automatically detect logical corruption by default!
We have to tell it to do so by using CHECK LOGICAL!
The info about corruptions can be found in the following views:

SYS @ orcl AS SYSDBA SQL > select * from v$backup_corruption;
RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#
---------- ---------- ---------- ---------- ---------- ---------- ----------
BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
---------- ------------------ --- ---------
1 586945441 586945402 3 1 5 81
4 0 YES CORRUPT
-- SYS @ orcl AS SYSDBA SQL > select * from v$copy_corruption;

Here is a case study:

HR @ orcl SQL > select last_name, salary
2 from employees;

ERROR at line 2:
ORA-01578: ORACLE data block corrupted (file # 5, block # 83)
# this could be an ORA-26040 in Oracle 8i! and before
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/
example01.dbf'

This is what you find in the alert_.log:
Wed Apr 5 08:17:40 2006
Hex dump of (file 5, block 83) in trace file
/u01/app/oracle/admin/orcl/udump/orcl_ora_14669.trc
Corrupt block relative dba: 0x01400053 (file 5, block 83)
Bad header found during buffer read
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.0006d162 seq: 0x1 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0xd1622301
check value in block header: 0x63be
computed block checksum: 0xe420
Reread of rdba: 0x01400053 (file 5, block 83)
found same corrupted data
Wed Apr 5 08:17:41 2006
Corrupt Block Found
TSN = 6, TSNAME = EXAMPLE
RFN = 5, BLK = 83, RDBA = 20971603
OBJN = 51857, OBJD = 51255, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =

Starting with Oracle 9i we can use RMAN
to check a database for both physically and logically corrupt blocks.
Here is the syntax:
RMAN> backup validate check logical database;
Starting backup at 05-04-2006:08:23:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/
system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/
sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/
example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/
undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/
users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-04-2006:08:24:10

RMAN does not physically backup the database with this command
but it reads all blocks and checks for corruptions.
If it finds corrupted blocks it will place the information about the corruption into a view:

SYS @ orcl AS SYSDBA SQL > select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 81 4 0 CORRUPT

this is what we find in the alert_.log:
Corrupt block relative dba: 0x014000b1 (file 5, block 177)
Bad header found during backing up datafile
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.0007bc77 seq: 0x3 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0xbc772003
check value in block header: 0xb32
computed block checksum: 0xe4c1
Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/
example01.dbf.
found same corrupt data

Now we can tell RMAN to recover all the blocks
which it has found as being corrupt:
RMAN> blockrecover corruption list;
# (all blocks from v$database_block_corruption)
Starting blockrecover at 05-04-2006:10:09:15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/
oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbf
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/
flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc
archive log thread 1 sequence 1 is already on disk as file
/u01/app/oracle/oradata/
orcl/redo01.log
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 05-04-2006:10:09:24

this is in the alert_.log:
Starting block media recovery
Wed Apr 5 10:09:22 2006
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/
archivelog/2006_04_05/o1_mf_1_2_%u_.arc
Wed Apr 5 10:09:23 2006
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/
archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc ( restored)
Wed Apr 5 10:09:23 2006
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Wed Apr 5 10:09:23 2006
Completed block media recovery

I recommend you to check your database for corrupt blocks
with RMAN on a regular basis, proactively.
If you do so you RMAN finds out about block corruptions
before your application runs into an ORA-01578 and
before you find out that you have backed up the corrupt blocks again and again.

There have been incidents when DBAs found out
that they did not have a backup with the un-corruted block any more,
because you have deleted the last one with a not corrupted version.
They could not recover the block any more!

For more detailed info about recovering corrupt blocks
(without and with RMAN, releases 7-10g)
pls see metalink also:
Subject: Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g
Doc ID: Note:28814.1 Type: BULLETIN
Last Revision Date: 26-MAR-2006 Status: PUBLISHED

DROP DATABASE INCLUDING BACKUPS with RMAN in Oracle 10g Release 2

"In the old times" it could be a problem to get rid of all the Backup information in the RMAN CATALOG after you had deleted a database with dbca for instance.
You had to manually remove all the records from the catalog.

Starting with Oracle 10gR1 we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG.

Here is a demo how it works:
$ rman target / catalog rman_user/oracle@hugo
RMAN> startup mount
RMAN> drop database including backups;

database name is "ORCL" and DBID is 1114494726
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 04/05/2006 07:00:59
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

RMAN> sql 'alter system enable restricted session';
RMAN> drop database including backups;

database name is "ORCL" and DBID is 1114494726
Do you really want to drop all backups and the database (enter YES or NO)? yes
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1402 1399 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586867060_234d2o9y_.bkp
1872 1858 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset
/2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrj9_.bkp
1873 1859 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzs0w_.bkp
1874 1860 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp
1875 1865 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/autobackup/
2006_04_04/o1_mf_s_586867060_234d2o9y_.bkp recid=38 stamp=586867060
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrj9_.bkp recid=48 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzs0w_.bkp recid=49 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp recid=50 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp recid=55 stamp=586886044
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp recid=50 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp recid=55 stamp=586886044
Deleted 5 objects
...
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - ------------------- ---------- ------------------- ----
1228 5 A 04-04-2006:10:34:35 950407 04-04-2006:10:32:56
/u01/app/oracle/oradata/orcl/example01.dbf

List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - ------------------- ----
1557 1 6 A 04-04-2006:11:02:00 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_6_234gj4c1_.arc
1558 1 7 A 04-04-2006:11:19:00 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_7_234gs7rf_.arc
1559 1 8 A 04-04-2006:11:23:51 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_8_234hdf3t_.arc
1560 1 1 A 04-04-2006:11:34:05 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_1_234hpx9w_.arc
1561 1 2 A 04-04-2006:11:39:40 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_2_234hqjto_.arc
...
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_6_234gj4c1_.arc recid=46 stamp=586869540
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_7_234gs7rf_.arc recid=47 stamp=586869831
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_8_234hdf3t_.arc recid=48 stamp=586870445
deleted archive log
...
datafile copy filename=/u01/app/oracle/oradata/orcl/example01.dbf
recid=11 stamp=586866875
Deleted 1 objects
...
database name is "ORCL" and DBID is 1114494726
database dropped
database name is "ORCL" and DBID is 1114494726
database unregistered from the recovery catalog
RMAN> exit


Rman will not delete the entry in the /etc/oratab, so we have to do this either by editing /etc/oratab or by using dbca to delete the service





For further information pls also see Metalink Note: 251412.1

How to calculate the minimum size of the SHARED POOL in ORACLE 10g

(This is my article which will be published in the upcoming SOUG Newsletter (April 2006))

This article deals with the changes of the implementation of the Shared Pool and what you should take into consideration when upgrading instances to Oracle 10g.

In prior releases of Oracle the value of the SHARED_POOL_SIZE parameter did not represent the total amount of memory allocated by the shared pool at startup. It just represented the largest part of the shared pool memory.

Starting with Oracle Database 10g Rel. 1, if you are not using Automatic Shared Memory Management (ASMM), the value specified at startup time for SHARED_POOL_SIZE does represent the total memory allocated by the shared pool.
Now the shared pool memory calculated at startup time is exactly the same as the value of SHARED_POOL_SIZE after rounding by the granule size (add 1 byte to 80mb, and you’ll find that we allocate 84, 88, or 96mb – depending on platform.) Thank you T.K. for pointing this out to me!

In former releases the total allocated shared pool memory was calculated by the value of SHARED_POOL_SIZE plus a certain additional overhead allocated at startup time, let’s call this the Internal SGA (ISGA).

So before Oracle 10g the TOTAL SGA was:
FIXED_SGA + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFER + DB_CACHE_SIZE +SHARED_POOL_SIZE+OVERHEAD (ISGA).

You could see the difference between the parameter value of SHARED_POOL_SIZE and the real memory allocated by the shared pool with the following query:

SYS@ora9iR2> SHOW PARAMETER SHARED_POOL_SIZE

NAME TYPE VALUE
--------------------- ------------- ------------
Shared_pool_size big integer 83886080

SYS@ora9iR2> SELECT SUM(BYTES)
FROM v$sgastat
WHERE pool =’shared pool’;

SUM(BYTES)
------------------
100663296
This is a difference of exactly 16MB;

Since with Oracle 10g the ISGA overhead is included in the parameter, we must find a formula to calculate how much memory we really have available there for Library Cache and Rowcache.
If we assume that the amount of overhead is still the same as it was in legacy releases the effective memory available after starting up would be, in our case, 16MB less in Oracle 10g than it was in Oracle 9i.

You can query the value of the ISGA overhead in Oracle 10g from the view v$sgainfo;

SYS@orcl10gR2> SELECT NAME, BYTES
FROM v$sgainfo;

NAME BYTES
---------- ----------
Fixed SGA Size 1218992
Redo Buffers 2973696
Buffer Cache Size 180355072
Shared Pool Size 92274688
Large Pool Size 4194304
Java Pool Size 4194304
Streams Pool Size 0
Granule Size 4194304
Maximum SGA Size 285212672
Startup overhead in Shared Pool 37748736
Free SGA Memory Available 0

The upgrade utilities in 10g ($ORACLE_HOME/rdbms/admin/utlu101i.sql in 10gR1 and $ORACLE_HOME/rdbms/admin/utlu102i.sql in 10gR2 use the values of the pre-upgrade instance and take a number of other values into account in order to recommend a new minimum value for the post-upgrade instance, such as the values of SESSIONS and CPU_COUNT. Looking at the procedures these scripts execute we can find the formula for the calculation of the new values:

(This is an excerpt from the utlu102i.sql script)
-- On a large database, the minimum of 144M may not be enough for shared pool size,
we have to factor in the number of CPU,
the number of session,
and some new added features.
So here is the formula: -- Recommended minimum share_pool_size = -- mp(sp_idx).minvalue -- + -- (Num_of_CPU * 2MB) + -- (Num_of_sessions * 17408) + -- (10% of the old shared_pool_size for overhead)…

If in 10g you startup an instance using Manual Shared Memory Management (MSMM) with a too small value for the SHARED POOL this will cause an:
Error: ORA-00371 (ORA-371)
Text: not enough shared pool memory, should be at least %s bytes
Cause: Init.ora parameter shared_pool_size is too small
Action: Increase the parameter value

In releases prior to 10g the error messages for ORA-00371 would have looked slightly different:

in Oracle 9i=>
Error: ORA-00371 (ORA-371)
Text: not enough shared pool memory
Cause: Init.ora parameter shared_pool_size is too small
Action: Increase the parameter value Oracle 8i and older =>
Error: ORA 371
Text: no free buffer handles available
Cause: The value of the initialization parameter DB_HANDLES is too small.
Action: Shut down the instance, increase the value of DB_HANDLES in the
initialization parameter file, and then restart the instance.
Under normal circumstances, let DB_HANDLES take its default value by omitting the parameter from the initialization parameter file.

The Default Value for SHARED_POOL_SIZE in Oracle 10g with MSMM is platform specific:
(32 MB for 32 bit platforms,
64 MB for 64 bit platform) rounded up to next granule.

With ASMM (SGA_TARGET > 0) the default SHARED_POOL_SIZE value is 0 (Oracle adjusts the value internally) and the current size is stored in the parameter __SHARED_POOL_SIZE (double underscore!) in the spfile if you use any. And this value will be used for the next startup. This is another good reason to use an spfile instead of the ASCII pfile.

If you use Automatic Storage Management (ASM) then additional structures must be added to the shared pool to store extent maps for storage management activities:
You can calculate the amount of this additionally needed memory by computing aggregates of current database structures like datafiles, controlfiles, logfiles and tempfiles.

SELECT SUM(bytes) / (1024*1024*1024)
FROM v$datafile;
SELECT SUM(bytes) / (1024*1024*1024)
FROM v$logfile a, v$log b
WHERE a.group#=b.group#;
SELECT SUM(bytes) / (1024*1024*1024)
FROM v$tempfile
WHERE status='ONLINE';

These values would have to be added to the shared pool memory
as well as another portion which is depending on the redundancy level (number of mirrors) of the asm disk groups used by the instance:
This would be:
(1MB of additional shared pool for every 100GB of disk space) + additionally 2MB for external redundancy (no mirroring),
(1MB of additional shared pool for every 50GB of disk space) + additionally 4MB for normal redundancy (two mirrors)
and
(1MB of additional shared pool for every 33GB of disk space) + additionally 6MB for high redundancy (three mirrors)

All these considerations only have to be made for a migration to Oracle10g and only if you do not use Automatic Shared Memory Management.

There is no such problem if you allow Oracle to auto tune the five dynamically tunable Caches in the SGA, which are:
DB_CACHE_SIZE
SHARED_POOL_SIZE
JAVA_POOL_SIZE
LARGE_POOL_SIZE
STREAMS_POOL_SIZE (this is a new cache introduced with Oracle 10gR1 and was made auto tunable in 10g Release 2)

This new feature is not enabled by default and can be changed dynamically.

SOUG yearly meeting 2006

I'm delivering a 9i DBA part I class in Bern this week.
We skweaze the 5 day contents into 4 days,
so that I could take part at the yerly meeting of the
Swiss Oracle User Group on March 29th.2006 in Baden.
It took place at the ABB.


They have this impressing fountain at the ABB.
I enjoyed to watch it very much.






.

I was surprised to meet so many people whome I know from my trainings. It was fun to meet again in such an event. Specially dinner with a guy from Latin America who had im pressed me in a programing class about a year ago withhis capability to transform questings into SQL-statements straight away without having to think a lot. He had given me a hard time then and I had to invent new chestings again and again in order to no let him get bored.
It's a pitty, I forgot to take a picture of him.


There were a number of very interesting trainings delivered about real life projects like the first experiences with Grid Control in large enterprises as the UBS,...








... the does and don'ts in an implementation of STREAMS

...and topics like security, LDAP & OID, DBMS_SCHEDULER...



I delivered a 90 minute training about the New Backup Strategy with RMAN in Oracle 10g, see my previouse post

Oracle Education @ Oracle Day World Trade Center Zurich

Today we had a great event in Switzerland

Event: Oracle Day
Date: March 16th. 2006
Location: World Trade Center Zurich





.. and of course ORACLE EDUCATION was present with staff
and a presentation desk.

There were a lot of people who came to listen to
a number of very interesting speaches and presentations
about the latest Features and Trends of Oracle and Partners.






Many people were interested in having a look at the lastest courseware of the NEW FEATURES Course for 10g Release2.
We had an online-presentation about new features of RMAN and FLASHBACK.

A number of bookings were ordered for our special events on
May 11th./12th.=> Jonathan Lewis lecturing on:
INDEXING and TROUBLE SHOOTING&TUNING and
September 21st./22nd. =>Tom Kyte with his best practice lectures.

There are still a few rare seats available for Jonathan Lewis!
(For details pls see here)

Mark Soth has passed away

I have just received a message informing me that Mark Soth died a few days ago at the age of 37.




This makes me feel very sad and I am deeply shocked.
A few weeks ago I have had a very intense week with Mark in my DBA-class in Zurich. He had applied for the position of an Oracle Instructor for Oracle´s training Partner Digicomp in Switzerland.
We had made arrangements to prep for the OCM-exam this year together.
I have got to know Mark as a very happy and joyfull person.
He had a clear idea of what he wanted and was going to build his fortune with his own hands.
He was trying to stand on his own two feet independantly with his just newly established own consulting company.

I wish we had had more time to get friends.

Why restore before recover? The new Oracle 10g Backup Strategy

Delivering Oracle 10g R2 Administration Workshop II we had a closer look at the new recommended backup strategy for Oracle.

With Oracle 10g R2 we can recover datafile copies like we recover the real datafiles.
This gives us the oportunity to recover the entire database without having to restore it from backup first.
Which of course saves very valuable time in case of a disaster.

Here is how you could do it:

- use a "flash recovery area" to store everything needed for recovering, such as:
- an image copy of the entire database
- one incremental backup of the database
- the archived redo logs which have been created since the last incremental backup

The flash recovery area is a location on disk which you specify by the parameter db_recovery_file_dest. It can be changed dynamically without having to restart.
With the also dynamically changeable parameter db_recovery_file_dest_size you can limit the amount of disk space you want to use for this area.

Once you use it rman will create backupserts and image copies of datafiles there by default.

- create an image copy of the entire database:

RMAN > BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY DATABASE;

This will first search for a parent image copy to recover. If there is none yet, it will create one.
If there is an image copy of the database already, it will create an incremental level 1 backup.

- after you have create the first incremental backup for your image copy you can update these image copies of the datafiles by applying the incremental backup to them.

RMAN > RECOVER COPY OF DATABASE;

This is what you do every night.
Then you will have a copy of the database which is always
at least at the level of last night.

In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:

RMAN > SWITCH DATABASE TO COPY;
You can only do this in MOUNT.
- Now you are already where you were last night and you only have to recover what has changed since last incremental update without having to restore the database at first.

This is a very handy way to perform recovery.
We were able to switch datafiles to copies before 10g already, but could not incrementally update them.

RMAN does (not?) backup empty blocks | what is NULL COMPRESSION ? | what is BINARY COMPRTESSION ?| what is UNUSED BLOCK COMPRESSION ?

In my class this week I have pointed out an important point about RMAN which is commonly misunderstood by many DBAs:
Some believe that RMAN does not backup empty blocks at all.
Others believe that RMAN does not backup empty block above the HIGH WATER MARK.
TRUE IS:
RMAN in ORACLE 9i and before does not backup empty blocks which have never been used!!!
in other words: RMAN does not backup empty blocks above the high watermark which are not formated!!
This is called NULL COMPRESSION.

In Oracle 10g Release 2 RMAN does not backup unused blocks (empty blocks below the High watermark).
This new feature is called UNUSED BLOCK COMPRESSION, see online doku 10gr2:
RMAN now creates more compact backups of datafiles, by skipping datafile blocks that are not currently used to store data. In previous releases, RMAN only supported NULL compression, which skipped space in datafiles that had never been allocated. No extra action is required on the part of the DBA to use this feature.

AS of ORACLE 10g RMAN can even compress backupsets. (Binary backupset compression), which can reduce the space needed for the backup to one fifth of size of an uncompressed backup.

RMAN> configure device type disk backup
type to compressed backupset;
# make it the default!
or
RMAN> backup as compressed backupset database;
-- only compress this single one 

Here is my demo for NULL COMPRESSION from my class:
1. I create a new tablespace with one datafile of size 10m.
2. I backup the datafile with RMAN, => very small backupset!
3. I create a table in this datafile and insert 1000000 rows.
4. I take another backup with RMAN, => big backupset.
5. I delete the rows and commit;
6. I take another backup with RMAN, => still big backupset.
7. I truncate the table, high watermark is down to first block in first extent!!
8. I take another backup with RMAN, => still big backupset.

Here is the demo-code:

sys@orcl9iR2 SQL> CREATE TABLESPACE lutz_test
2 DATAFILE '/opt/oracle/oradata/orcl/lutz_test_01.dbf' SIZE 10m
sys@orcl9iR2 SQL> SELECT file#
2 FROM v$datafile
3 WHERE name = '/opt/oracle/oradata/orcl/lutz_test_01.dbf' ;
FILE#
----------
9
sys@orcl9iR2 SQL> SELECT bytes, blocks, user_bytes, user_blocks
2 FROM dba_data_files
3 where FILE_ID=9;
BYTES BLOCKS USER_BYTES USER_BLOCKS
---------- ---------- ---------- -----------
10485760 1280 10420224 1272
sys@orcl9iR2 SQL> SELECT (10485760-10420224)/1024 FROM dual;
(10485760-10420224)/1024
------------------------
64
# 64K contain extent bitmap for tablespace
[oracle@KR11-KL1 oracle]$ rman target /
Recovery Manager: Release 9.2.0.7.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1108329042)
RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0nh9sopv_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ll /home/oracle/BACKUP/RMAN/0nh9sopv_1_1.rman
-rw-r----- 1 oracle oinstall 98304 Jan 27 06:50 /home/oracle/BACKUP/RMAN/0nh9sopv_1_1.rman
sys@orcl9iR2 SQL> SELECT 98304/1024 FROM dual;
98304/1024
----------
96
# size of backupset with empty datafile


sys@orcl9iR2 SQL> ! ls -l /opt/oracle/oradata/orcl/lutz_test_01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jan 27 06:50 /opt/oracle/oradata/orcl/lutz_test_01.dbf
sys@orcl9iR2 SQL> SELECT 10493952/1024 FROM dual;
10493952/1024
-------------
10248
# size of empty datafile on disk

sys@orcl9iR2 SQL> CREATE TABLE lutz_tab
2 ( sp1 NUMBER) TABLESPACE lutz_test;
sys@orcl9iR2 SQL> BEGIN
2 FOR i IN 1..1000000 LOOP
3 INSERT INTO lutz_tab values(i);
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
sys@orcl9iR2 SQL> COMMIT;
Commit complete.

RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0ph9spfo_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ls -l /home/oracle/BACKUP/RMAN/0ph9spfo_1_1.rman
-rw-r----- 1 oracle oinstall 9535488 Jan 27 07:02 /home/oracle/BACKUP/RMAN/0ph9spfo_1_1.rman
sys@orcl9iR2 SQL> SELECT 9535488/1024 FROM dual;
9535488/1024
------------
9312
# size of backupset with datafile after inserts

sys@orcl9iR2 SQL> DELETE FOM lutz_tab;
1000000 rows deleted.
sys@orcl9iR2 SQL> COMMIT;
Commit complete.
RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0rh9spll_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ls -l /home/oracle/BACKUP/RMAN/0rh9spll_1_1.rman
-rw-r----- 1 oracle oinstall 9535488 Jan 27 07:05 /home/oracle/BACKUP/RMAN/0rh9spll_1_1.rman
# size of backupset with datafile AFTER DELETING all rows

sys@orcl9iR2 SQL> TRUNCATE TABLE lutz_tab;
Table truncated.
RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0th9spok_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ls -l /home/oracle/BACKUP/RMAN/0th9spok_1_1.rman
-rw-r----- 1 oracle oinstall 9535488 Jan 27 07:07 /home/oracle/BACKUP/RMAN/0th9spok_1_1.rman
# size of backupset with datafile AFTER TRUNCATING
!!! empty formatted blocks backed up!!!


Related Metalink Notes:
Note: 247705.1 RMAN BACKUP COMPRESSION
Note: 255973.1 Oracle10g Recovery Manager - Backup Compression Methods

Flashback any error with Oracle 10g release 2?

(this is my article published in SOUG newsletter 1/2006)

Oracle 10g comes with a number of flashback technologies. You can use these functionalities to go back to a point in time in the past on different levels in the event of a logical error. You can, on row-, table- and even on database level undo human errors now.

A variety of functionalities are summarized under the name of flashback, which in fact use very different technologies.
This article deals with all of these functionalities.

Let us first take a short look at the history of flashback functionalities.

Starting with Oracle 9i already the first flashback functionalities were made available:
The Oracle 9i Release 1 package DBMS_FLASHBACK enables a DBA to reset a session to a point in time in the past:
exec dbms_flashback.enbale_at_time (sysdate-10/(24*60))
resets the current session to a point in time 10 minutes ago. From now on, when the session opens a cursor, it will see the read consistent image of the committed information in the database as of 10 minutes ago. This was not possible for a session which is connected as sys. With Oracle 10g it is possible.

Flashback query, an Oracle 9i Release 2 functionality, enables us to select values from a table as of a point in time in the past:
SELECT *
FROM hr.employees
AS OF TIMESTAMP (sysdate-10/(24*60));

And already in Oracle 9i it was possible to create a table as a copy of another table as of a point in time in the past.

CREATE TABLE hr.emps
AS
SELECT *
FROM hr.employees
AS OF TIMESTAMP
(sysdate-10/(24*60));

This functionality was improved in Oracle 10g with a new technology (further details at the bottom of this page under flashback table).
All these Oracle 9i flashback functionalities use data from undo segments. Thus, precondition for all of them to work is that all before-images needed for a query must be still available. The new in Oracle 9i dynamic initialization parameter UNDO_RETENTION provides us with the possibility, to tell the server how long before images should be preserved at least, even after commit. In Oracle 9i there is no guarantee that all these images will be available for the period of undo_retention. The server will try its best, however under space pressure in the undo tablespace it will overwrite before images even before undo_retention is over. If before-images, which are needed for a flashback query or flashback transaction query are missing, an ORA-1555 snapshot too old will occur.

This is how it was in ORACLE 9i. In Oracle 10g we now can have an undo tablespace with guaranteed undo_rentention. If such an undo tablespace runs full, the application will stop because no further before-images can be saved in the undo segments. So we can now make it directive that the server will have to keep all the before images needed. It is not just a request anymore.
The implementation of undo_retention was modified in 10g R1 and again in 10g R2. Oracle can now auto tune undo_retention, which is another step towards a self-tuning database. This parameter will become a hidden _parameter in future releases probably.


Starting with Oracle 10g a number of new flashback extensions have been implemented such as:
flashback versions query
flashback transaction query
flashback database
flashback drop
flashback table

Let’s take a look at them one by one.

Flashback versions query is an extension of flashback query. Now we can look at all the different versions, which a particular row had over a period of time in the past defined by a pair of SCNs ore timestamps:

SELECT employee_id, salary, versions_xid
FROM hr.employees
VERSIONS BETWEEN SCN MINVALUE AND SCN MAXVALUE
WHERE department_id=90;

This query will return all the versions of the salaries in the employees table, which are still available. The versions_xid pseudocolumn returns the ID of the transaction.

Once we have retrieved the ID of a transaction, we can now use it in order to find out what else this particular transaction has changed, by using the Static Data Dictionary View flashback transaction query:



desc flashback_transaction_query

XID RAW(8) Transaction identifier
START_SCN NUMBER Transaction start system change number (SCN)
START_TIMESTAMP DATE Transaction start timestamp
COMMIT_SCN NUMBER Transaction commit system change number (null for active transactions)
COMMIT_TIMESTAMP DATE Transaction commit timestamp (null for active transactions)
LOGON_USER VARCHAR2(30) Logon user for the transaction
UNDO_CHANGE NUMBER Undo system change number (1 or higher)
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)

SELECT ''''||rowid||'''' AS king_rowid
FROM hr.employees
WHERE employee_id=100;


SELECT xid,
commit_scn,
TO_CHAR(commit_timestamp,
'yyyymmddhh24mihhsscc')
AS commit_timestamp,
operation
FROM flashback_transaction_query
WHERE table_name = 'EMPLOYEES'
AND table_owner = 'HR'
AND row_id =& king_rowid
ORDER BY commit_timestamp;

This will return all the versions of a single row and the transaction IDs. It can be useful for instance in a multi developer environment to find out who changed what and what else was change by a specific transaction:

SELECT *
FROM flashback_transaction_query
WHERE xid = HEXTORAW ('&XID');

To query the view flashback_transaction_query you need the select any transaction system privilege

Flashback versions query and flashback transaction query again use undo data.

Another new flashback technology was introduced in Oracle 10g release 1 to extend the functionality of flashback at table level. It is called flashback table and it uses a mechanism called row movement, which was introduced for partitioned tables already in Oracle 8i to permit updating a partition key and let the row move from partition to partition.
With row movement enabled for a table in Oracle 10g we can flashback the table to a point in time or SCN in the past. Row movement just gives the permission to Oracle to change the ROWID of a row. A ROWID is assigned on commit and is usually immutable.
If you find out that you have chosen the wrong point in time you can simply flashback the table again. Here is how it works:

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;
FLASHBACK TABLE hr.employees TO TIMESTAMP to_timestamp(‘2005-27-10:19:00:00’, ‘YYYY-DD-MM:HH24:MI:SS’).

A flashback table to a specific SCN is also possible.

Flashback Database again is something completely different from all the other flashback functionalities from an implementation point of view. It enables the “roll back” of a complete database, including the data dictionary.

You first have to enable flashback logging before you can use flashback database. This is done by an entry in the controlfile (ALTER DATABASE FLASHBACK ON ;). The database needs to be running in ARCHIVELOG mode.
There are three relevant parameters for flashback database. All of them are dynamically changeable:
recovery_file_dest (location of the Flash recovery area)
recovery_file_dest_size (size of the Flash recovery area)
db_flashback_retention_target (time in minutes how far you can flashback the database)

The technology used behind the scenes are so called flashback logs which are written from the buffer cache to the new flashback buffer (max. 16MB) in the SGA. From there the also new background process CTWR copies them to a file which you can imagine as some kind of giant rollback segment in the flash recovery area on disk. This area can optionally be used by multiple databases and does not only contain the flashback logs but can be used for all other files needed for backup and recovery as well, such as archived redologs, backupsets and image copies. In this area Oracle creates a structure of subfolders for every database using this flash recovery area and for the different kinds of files stored in it including the flashback logs.
These flashback logs are just images of changed blocks stored on disk. Once the server starts generating them you can ‘rollback’ the entire database to a point in time or an SCN in the past. Like a rewind button of a tape-recorder. The amount of flashback logs that needs to be preserved on disk is determined by the initialization parameter flashback_retention, as well as the amount of DMLs happening on the system.

Now a TRUNCATE TABLE by mistake is not a case for a full restore and a point in time recovery any more. We simply ‘rewind’ the database to before the truncate statement. The server will, in a first step flashback the database to a point in time short before the truncate by using the flashback logs for restore. And then, as a second step, recover the database forward to the desired point in time or SCN by applying redo from archived redo logs. We can open the database READ ONLY first in order to check if the flashback database was successful. We can try again multiple times if the point in time we have flashbacked to was the wrong one.
The flash recovery area, which holds the flashback logs is self-maintaining. The system will delete flashback logs as soon as they are not needed any more.
Oracle recommends the use of this new disk area to store all information needed for backup and recovery, such as flashback logs, archivelogs, image copies and backupsets.
A flashback database statement will fail if there is not enough flashback logs to rollback the database to the desired point in time in the past.

Flashback Drop uses an entirely different technology:
We now have a “recycle bin” in Oracle where a dropped table ends up.
We can restore a dropped table from the recycle bin and we even can query a dropped table from within the recycle bin.
How does this work?
When a table is dropped in 10g the space it uses is freed for reuse by other segments. You can in fact see the freed extents of the dropped table in DBA_FREE_SPACE.
However, Oracle will try to not overwrite this space as long as possible. And as long as this has not happened yet, we can read from the table residing in the recycle bin.
We can do so by using the table’s new name. It has just been renamed internally and we now have to use the new name to query it. We can see which objects are in the recycle bin by querying DBA_RECYCLEBIN.
A DROP TABLE ... PURGE statement bypasses the recycle bin and the table will be gone straight away.
This new drop-behavior can be switched off in Oracle 10g R2 in order to get the same behavior as we had in former releases by setting the initialization parameter recyclebin to off. It was a hidden _parameter in Oracle 10gR1.

Starting with Oracle 10g release 2 we can even flashback the database to a previous incarnation which means we can rollback the system to an SCN which was created before the last open resetlogs. A flashback database in Oracle 10g R1was restricted to the same incarnation of the database.

Additionally we now have the option to create so-called named restore points and even guaranteed named restore points. Like savepoints in a transaction named restore points can be used as “saved points” for the entire database to which you can go back to any time. This is a handy alternative to the use of SCNs or timestamps.
Again flashback logs are used. This functionality works even without having enabled flashback logging enabled.
Flashing back a database can save recovery time dramatically because in fact there is no need for a full restore like before Oracle 10g with an incomplete recovery. On the other hand flashback logging has a drawback, which Oracle describes as a minor one: its cost in performance is in the single digit range.

All together we have a lot of different functionalities now, which can be useful to repair logical errors. Now it is ‘just’ a matter of having enough space available on disk to store all the information need for flashback.

For further information please do not hesitate to contact me by email:

lutz.hartmann@oracle.com
Senior Instructor Oracle Education

Baden, Switzerland
November 2005

Oracle Education Special Events this Year

Oracle Education Switzerland presents a number of special workshops in 2006:
For details and booking on all events please contact edu-buch_ch@oracle.com

May 2006:
The Tuning Expert Jonathan Lewis, who has just published his latest book about the Cost Based Optimizer will deliver a two day workshop in Zurich on May 11th. and 12th 2006.
There will be two blocks of seminar sessions:
- on the first day Jonathan Lewis will deal with indexing strategies
- on the second day he will talk about troubleshooting and tuning concepts.

This workshop is an absolute must for all developers and DBAs who want to get an into depth insight into the funtioning of the oracle machine.


September 2006:
There are the first people booking for Tom Kyte in Zurich in September 2006 already!!! After the very successful workshop in December 2005 Tom Kyte will come over here and deliver trainings on Sept. 21st. -22nd. 2006 again because there is such a big demand coming from the audience. It will be an event similar to the one in December 2005, but only two days this time.

deferred constraints part 2