Throw out the garbage

The garbage in this case is the recycle bin in the database. Just as in any tidy house you should not wait too long before you empty the garbage bin. I've come across two reasons to empty it.

One is the ORA-3297 that is thrown when you try to downsize a data file you think have lots of free space at the end. I once wrote this SQL to find the lower limit that I can reduce the data file to:

Select File_Id,ceil(Hb.Min_Red*8192/1024/1024/1024) Minimal_Size_Gb ,round(Df.Blocks*8/1024/1024,2) Total_Gb,
Round((Df.Blocks-Hb.Min_Red)*8/1024/1024) higher_free_GB
from dba_data_files df join
(Select File_Id, Max(Block_Id+Blocks) Min_Red
From Dba_Extents
Group By File_Id) Hb Using(File_Id)
Order By  Higher_Free_Gb Desc;

On one occasion when I tried to reduce the size of the data file an ORA-3297 was thrown. Of course I could have checked DBA_RECYCLEBIN, but I was checking DBA_SEGMENTS and noticed several segments with names that started with BIN which immediately reminded me about the recycle bin. I purged the recycle bin and the resize operation went through.

The other reason is that certain queries against DBA_FREE_SPACE may actually take much longer when there are segments (tables) in the recycle bin. One third-party monitoring application had a query that took more time to complete than anything else, after a purge dba_recyclebin the query had a reasonable response time.

Some argue that one should not use the recycle bin in production. I think it depends; if your application creates and drops tables frequently, see if you can add PURGE to the DROP TABLE statement, if not turn the recycle bin off by setting the system parameter recyclebin to off. Also after a successful release of a new version empty the recycle bin when everything is verified OK. Accidents do happen, therefore I like to have the recycle bin enabled just in case. Except for the case already mentioned I think that dropping tables should not happen so often in a production environment that it becomes a problem.

Flashback database fails with read-only tablespace

Flashback database, (active) duplicate with RMAN, and tablespaces that are read-only. You can only have two out of three from my recent testing. One database with a few read-only tablespaces was cloned by using active duplicate in RMAN. Before the testing (using Real Application Testing replay) a restore point with guarantee flashback database was created. After the testing I tried to perform a flashback database, but it failed with:

SQL> flashback database to restore point b4_test;
flashback database to restore point b4_test
*
ERROR at line 1:
ORA-01122: database file 38 failed verification check
ORA-01110: data file 38: '+DATA/clonedb/datafile/foo_bar002.443.804434293'
ORA-01207: file is more recent than control file - old control file

The file in question belongs to a read-only tablespace and is certainly not newer than the control file. It looks to be a bug on 11.2.0.3 and I found a way to work around it if you take some actions before you start testing. I did not find the same error stack mentioned together with flashback, or anything directly relevant on My Oracle Support, but similar symptoms made me think the problem would go away if I set the read-only tablespaces read-write. I generated SQL with this:

select 'alter tablespace ' || tablespace_name || ' read write;'
from dba_tablespaces
where status='READ ONLY';

After I executed the generated statements, I created a new restore point with:

drop restore point b4_test;
create restore point b4_test guarantee flashback database;

For testing databases it is probably no big deal if you set all the tablespaces read only. If you try to set the tablespaces read-write after you have done some changes to the database you want to revert (i.e. testing) the flashback operation will probably fail, possibly with an ORA-600 error, but I have not tested it. In other words, the workaround must be in place before you create the restore point.

I don't know why this happens for databases that are duplicated with RMAN. Flashback database on the source database works OK even with read-only tablespaces. The source database was created from a restore of an RMAN backup taken on a physical standby database. Also the duplication itself works OK with the read-only tablespace. Two out of three, then.

Pro Oracle Database 11g RAC on Linux

This is not intended as a complete book review, but a warm recommendation of a book I've found very useful the last couple of months. That is, highly recommended if you are working with RAC or going to start with a RAC project soon.

I have been working on and off with RAC for several years, but I don't take any new RAC project lightly. Starting in November I was involved with a proof of concept for a large public customer. Before and during this project I have relied a lot on this book: Pro Oracle Database 11g RAC on Linux written by Steve Shaw and Martin Bach.

True, installation of RAC has become much easier on 11gR2, but the planning part is as important as ever. If you jump into the project without proper planning you are likely to encounter problems later on after the system is installed and running (more or less). Changing network layout or mostly anything else is a hassle later on, though this has also become easier with the added functionality to the commands crsctl and srvctl in 11gR2.

One reason I like this book as an important addition to the Oracle documentation is that it is very logical written from a project point of view. It starts out with a good introduction and then explains the important concepts and the arcitechture. This is stuff you need to get right before you start. There are also many decisions you have to make before you start. The book builds a solid ground under your cluster with a good focus on the OS (Linux in this case, but I think I will recommend  it for a similar project on Windows as well due to the book's structure). In other words, if you don't have time to read the necessary chapters in this book you will not have time to fix the errors later on either.

We're not exactly masters of logistics in this country, and I came in late for this project. I started the preparation for the project by reading this book, when I had read enough and felt prepared for the next day I could put the book aside and get some rest. The next day I would continue by reading and planning. Well, that might read as if this was my first RAC project, it was not, but again I don't imagine I remember every detail, the book serves as a checklist.

The project gave us several challenges due to a rather big database that we created from an unconventional RMAN backup,  Real Application Testing (RAT), super fast hardware that still needed tweaking on OS level. But not once did I run into problems due to wrong configuration of RAC or errors we could blame the RAC software.

Have a nice 2013! Go to conferences, user group meetings, and meet all the nice people in the community. Our conference, OUGN 2013, will be better than ever; we will have Oracle experts from Australia, USA and Europe.

Oracle Open World - lucky me

Last year was my first time at OOW. I made a write-up every day on my blog, but this year I will maximize, meaning it will be little time for blogging while being there. It is now less than four two one hours before I start on my trip OSL-SFO. Writing about what I plan to do seems like a great way to kill time. Also I'm doing some proactive jet-lag prevention research, may be staying up late will make the transition from CET to PT easier (or is it CEST and PST now?).

I have a schedule packed with interesting stuff, but based on experience from last year, I expect to feel some conference fatigue setting in around noon at Wednesday. I may just as well see Mogens in his office.

Besides all these good presos I am really looking forward to two events. One is the Oaktable World (formerly called Oracle Closed World; guess what happened). Good thing it is early in the week from Monday to Tuesday. Those guys are not giving out 101-courses, and will demand my attention.

The second event is the Bloggers Meetup organized and held by Pythian. This may very well be the best networking opportunity at OOW :-) Last year I met a bunch of people I have known virtually through Twitter and their blogs for years. Nothing beats a conversation in person. You will be surprised how many nice people there are out there that share our somewhat narrow interest.

I'm always interesting to talk to potential speakers at our annual conference. If you have any questions regarding our user group (Oracle User Group Norway) or our conference, please get in touch. Btw, here is our call for paper

Paying slightly attention to Twitter, I just noticed a link to the kick-off for the IOUG Big Data SIG. Think I want to check it out too. Not sure what I can make out of this Big Data thing, but it seems to attract a lot of smart brains.

Luggage is ready, and it includes a cool t-shirt from Method-R, Oaktable World will be a nice occasion to wear it.

VirtualBox 4.2 released

Version 4.2 of VirtualBox was released two days ago (so far for Linux only). Download it from here: https://www.virtualbox.org/wiki/Linux_Downloads

Probably not important, but I'm using Fedora 16 for the moment, and since I was running VirtualBox 4.1 I had to remove the previous package first and install the 4.2 version. This will not destroy the guest hosts.

rpm -e VirtualBox-4.1
rpm -ip VirtualBox-4.2-4.2.0_80737_fedora16-1.x86_64.rpm

After a new start with the VirtualBox Manager the old VM guests are in place.

Easier installation of Oracle on Linux

This is more a note to myself... Installing Oracle on Linux has become much easier with a package that prepares the OS before installation of the Oracle Server software. In previous versions the rpm package used to be called oracle-validated, but for 11gR2 on OEL6 it is called oracle-rdbms-server-11gR2-preinstall.

Probably the easiest way to install the Oracle database on Linux is to install Oracle Enterprise Linux 6 and before the installation of Oracle do two things:

1. Enable the public yum repository at Oracle:

cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol6.repo

Edit the file and enable the relevant version by setting enabled=1.

2. Install the package that prepares the server for Oracle database installation:

yum install oracle-rdbms-server-11gR2-preinstall

No need to run any commands after the yum install command. Continue with runInstaller.

Update 2013-07-12:
With the release of database 12c Oracle has released a new package to prepare Oracle Linux 6 for it.

Get the info from Oracle here. The procedure remains the same as for 11g, only the name of the rpm file changes. I installed it on a fresh new OL6 without any extra configuration of yum repository with:

yum update
yum install oracle-rdbms-server-12cR1-preinstall

The outline from Oracle tells you to do step 1 above, but the repo file was included in OL 6.4.

Oracle load testing - part 3 Hammerora results

I learned something very important when doing testing with Hammerora. The documentation is quite good and has a simple, but important point, the importance of planning and preparation. To quote the documentation for Oracle OLTP testing:

Planning and Preparation is one of the most crucial stages of successful testing but is often overlooked. Firstly you should fully document the configuration of your entire load testing environment including details such as hardware, operating system versions and settings and Oracle version and parameters. Once you have fully documented your configuration you should ensure that the configuration is not changed for an entire series of measured tests. This takes discipline but is an essential component of conducting accurate and measured tests.

From this I conclude that many tests I've seen and done myself have not been accurate. As stated earlier the goal for this testing was just to compare performance before and after migration from EVA to 3PAR.

Since this customer did not have the required license to run AWR I did the following change in the driver scipt to create a Statspack snapshot in place of a AWR snapshot. Search for string containing dbms_workload, replace

set sql1 "BEGIN dbms_workload_repository.create_snapshot(); END;"

with

set sql1 "BEGIN perfstat.statspack.snap; END;"

When testing with Hammerora I decided to run each test three times to see if the numbers where consistent. I recorded the numbers of each run in an spread sheet as shown in the following table for the tests on EVA:

Vusers	Run	Report	tpm	nopm	Avg_tpm
1	1	1_2	7158	2369	7634
1	2	11_12	7874	2645
1	3	21_22	7868	2804
3	1	31_32	16478	5765	17317
3	2	41_42	17678	6256
3	3	43_44	17794	6130
10	1	45_46	27847	9959	33225
10	2	51_61	32581	11600
10	3	71_81	39248	13701
20	1	91_101	47489	17441	47075
20	2	111_121	63062	22658
20	3	131_141	30674	11116
30	1	151_161	54349	19756	44186
30	2	171_181	45628	17331
30	3	191_201	32581	12733

Vusers is the number of virtual users in Hammerora, Run is 1 - 3 for each new setting of Vusers. Report  refers to the Statspack report created on snapshots before and after. Tpm and nopm as reported from Hammerora and finally Avg_tpm is the average in each group. Compare this to the numbers for the 3PAR:

Vusers	Run	Report	tpm	nopm	Avg_tpm
1	1	9_10	8246	2815	8262
1	2	11_12	7983	2717
1	3	13_14	8556	2956
3	1	15_16	22652	7854	22881
3	2	17_18	22652	7831
3	3	19_20	23339	7994
10	1	21_22	33539	11767	33191
10	2	25_26	39054	13729
10	3	27_28	26981	9428
20	1	29_30	47134	16462	47356
20	2	31_32	46436	16330
20	3	33_34	48497	17023
30	1	35_36	53197	18902	50788
30	2	37_38	44980	15994
30	3	39_40	54187	19033

The repeated tests for the same number of virtual users do not vary as much on the 3PAR compared to the EVA. Also the numbers for the EVA seemed to improve for each run, maybe due to some caching taking place.

The 3PAR seemed to be more reliable for the same number of virtual users as can be seen in these screen captures, the first for 20 virtual users on EVA:

The peformance on the 3PAR does not change much during the test (20 virtual users):

You'll see that in one instant the EVA seems to perform better, but I rather have stable and less erratic performance with the 3PAR than a system with occasional good performance.

All in all it was very easy to play around with Hammerora, it is very easy to set up so you can spend time on planning and executing the tests. Also I like how you can observe change of performance over time. Clearly Hammerora is a tool I will use more later.