OUGN Spring Seminar 2013

Last week's conference is the second since I joined the board of our user group. Never been more worried, but it turned out better than ever, in my not so humble opinion.

I was really proud of our agenda, we had several new speakers with top content. 

Cary Millsap from Method R gave a keynote and two excellent presentations. This was his first time in Norway, but judging by the feedback we got he will be back soon. Actually having him here at our conference was a goal I committed to last year. Thanks, Cary, for excellent content and presentation!

Last OOW I met Connor McDonald at the blogger's meetup that Pythian organizes at OOW every year and I thought it would be cool if we could have him at our conference. After some severe lobbying with the rest of the board we decided on a plan to make it happen. Actually I had never seen Connor present before, but when he did his Odds and Ends presentation I understood what everybody was talking about. I think it is awesome that we can get the best, even if they come from the other side of the earth. We have not processed the feedback forms yet, but the impression so far is that Connor and Cary is sharing first place on content and presentation.

Tom Kyte, Bryn Llewellyn and Joel Goodman were back too. Always popular, each has his public (overlapping I assume) and genuinely dedicated. I am thankful for all the good publicity they are giving our conference.

There was also time for some psycho analysis and after a few beers Doug Burns and me discovered we are more or less soul mates. Always good to see the man and his presentations, he was mildly shocked when I showed up on one of his presos in stead of the one Cary was giving at the same time (confession: I had already seen the one Cary was giving). I learned a lot this time too. Good stuff! And thanks for the feedback, motivation and interests for our user group. You will always be on our MRU list. 

We have two areas where we have to improve; Java and MySQL. Markus Eisele helped us to get speakers for the Development/Java track in addition to speak himself. We have work to do on Java, and the same goes for MySQL, it is a different group of people. I know Oracle and many in the community have a lot of good stuff on Java and MySQL, but first we need to appear on those users'  radar and have them come to our table.

I spend much of my digital social life on Twitter. When I go to OOW or MOW I get to meet people whose Tweets and blogs I've been reading for a time. It is always a nice experience. This year two Twitter friends came to our conference; Philippe Fierens from Belgium (check out his nice post about the conference) and Jan Karremans from Netherlands. In fact the whole group from Netherlands was quite impressive.


It is a lot of hard work to organize a user group conference, the agenda requires a lot to get in place. For me it is the best networking opportunity, it is the place where I get to know what is not written in any manual or book, and meet friends IRL. Here is one, Martin Nash, and the whole Martin cluster.

The cruise part of Spring Seminar 2014 will start 2014-04-03, with a possible conference day in Oslo the day before. But first I'll take a short vacation next week, going to MOW2013.

Removing obsolete parameters from spfile

This is a common error message after upgrade of the database:

ORA-32004: obsolete or deprecated parameter(s) 
specified for RDBMS instance

In 11g (last tested with 11.2.0.3) even if your start up the database from an old type pfile without any obsolete parameters and then create an spfile from memory like this:

create spfile from memory;

Oracle will insert two obsolete parameters into the spfile. This query will list obsolete parameters in your spfile:

select p.name,p.value
from v$parameter p, v$spparameter s
where s.name=p.name
and p.isdeprecated='TRUE'
and s.isspecified='TRUE';

NAME                  VALUE
--------------------- ----------------------------------------------
background_dump_dest  /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace
user_dump_dest        /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace

They can be cleared with:

alter system reset background_dump_dest;
alter system reset user_dump_dest;

Corrupt database blocks II

After you have validated database as shown in previous post any corrupt database blocks will be reported in the view V$DATABASE_BLOCK_CORRUPTION. Now you want to find out the tables that used to have data in these blocks; those data have typically been lost if you have no backup of them or do not posses special tools like DUDE.

Before you start I recommend to purge the DBA Recycle Bin if it is in use, as reported here certain queries  seem to take longer time when it is not empty. Execute as SYSDBA:

purge dba_recyclebin;

Then create a temporary table with information on the corrupt blocks:

create table segments_with_corruption
as select FILE_ID,BLOCK_ID,blocks,OWNER,SEGMENT_NAME,
  start_corrupt,corrupt_blocks
from (
  select e.FILE_ID,e.BLOCK_ID,e.blocks,e.OWNER,e.SEGMENT_NAME,
    c.block# start_corrupt,c.blocks corrupt_blocks
  from DBA_EXTENTS E, V$DATABASE_BLOCK_CORRUPTION C
  where E.BLOCK_ID <= C.block# 
    and E.BLOCK_ID + E.blocks - 1 >= C.block#
  and e.file_id=c.file#
  union all
  select e.FILE_ID,e.BLOCK_ID,e.blocks,e.OWNER,e.SEGMENT_NAME,
    c.block# start_corrupt,c.blocks corrupt_blocks
  from DBA_EXTENTS E, V$DATABASE_BLOCK_CORRUPTION C
  where E.BLOCK_ID >= C.block# 
    and E.BLOCK_ID <= C.block# + C.blocks -1
  and e.file_id=c.file#
);

This query may take a long time to run, which is a reason to materialize it in a table while you are working on this. You can find a more advanced query on MOS in article 472231.1 To create this query I made a drawing for myself with all different scenarios on how interval of corrupt blocks could possible overlap with extents belonging to some object. Then I wrote down the criteria and merged them together.

Use this query to find owner, name and type of the segments you have to work with:

select distinct c.owner,c.segment_name, c.segment_type
from segments_with_corruption c, dba_segments s
where c.segment_name=c.segment_name and c.owner=s.owner;

For segment_type=INDEX you can fix it by simply rebuilding the index (hopefully none of the rows it indexes are in corrupt blocks).

The next post will be on how to salvage as much as possibly from the good blocks belonging to a table that has some corrupt blocks. I'll show a query you will run to generate other statements that will be used to extract data from blocks around the corrupt blocks.

Corrupt database blocks I

I worked with corrupt blocks in a database a few weeks ago and decided to write three posts about it so I don't have to rethink all of it next time it happens. Hardware and the database have improved a lot, because I rarely run into this problem.

The first and obvious thing to state is that corrupt blocks in the database should be detected early by proper fault management (if you use Enterprise Manager you are good). When detected it should be fixed by using RMAN, either by command:

recover corruption list;

or by recovering individual blocks, e.g.

recover datafile 42 block 5 to 6;

In case you don't have any backup or the error went undetected for a long time and you decide that block recovery is not feasible you need to clean it up some other way. If the RMAN backup detects the corrupt block it will abort at first corrupt block (unless you change the SET MAXCORRUPT FOR DATAFILE). This means that there may be more corrupt blocks in the database and you may just as well find them all while you are working on it.

The Database Verification tool is supposed to do find all corrupt blocks, but last time (11.2.0.2 on Linux x64) it failed to find new ones. This tool is documented in the manual Utilities. I looped over all the data files with command dbv, but it was a waste of time. This may have something to do that there are different types of corruptions. Instead I used VALIDATE in RMAN. You can validate individual data files or the entire database:

VALIDATE DATAFILE 42;
VALIDATE DATABASE;

This will not consider MAXCORRUPTIONS, but scan the data file / entire database and update the view v$database_block_corruption with info on corrupt blocks found.

Next post will be on how to find objects that have, or rather, used to have data in these blocks.

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.