Wednesday, April 24, 2013

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.

Sunday, April 14, 2013

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 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:
from v$parameter p, v$spparameter s
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;

Sunday, April 7, 2013

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
from (
  select e.FILE_ID,e.BLOCK_ID,e.blocks,e.OWNER,e.SEGMENT_NAME,
    c.block# start_corrupt,c.blocks corrupt_blocks
  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
  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.