Friday, March 28, 2014

42 Reasons to join me at #OUGN14






  1. Oracle User Group Norway (OUGN) organizes the biggest Oracle user conference in Scandinavia.
  2. As of March 28, 341 DBAs, developers, managers, sales people, and more have figured out why. Read on for more reasons if you have not registered yet.
  3. This year we are breaking many of our own records, for example we have 66 speakers from 15 different countries.
  4. We have added another track with a total of 8 different tracks.
  5. Our conference, also known as the Boat Conference is getting famous worldwide. The call for paper this time resulted in more than 200 submitted abstracts, from more speakers than any time.
  6. We  start even earlier to cram in more sessions - it is hard to say no to all good speakers out there, we have never had so many sessions, more than 100 in total.
  7. This year we have two roundtable sessions - meet the experts and join the conversations.
  8. Something completely new: Women in Technology - WIT. See the abstract here http://bit.ly/OVrwgR If you have been to conferences before you know why this is important.
  9. Meet Oracle’s coolest product manager, for SQL Developer Jeff Smith, check his blog here http://www.thatjeffsmith.com/ and you get the idea.
  10. Are you a DBA and wonder why Java? Do you code in Java and wonder “What now, Oracle?” Meet Oracle’s PM on Java, Bruno Borges. Coming all the way from São Paulo, Brazil to talk about Java and more.
  11. The Java track has never been better, with more Norwegian speakers, including Aslak Knutsen from Red Hat. Markus Eisele (who knows everybody in the Java community) will be there too.
  12. We can’t of think any better place in Norway to mend the sometimes broken relationship between Java coders and DBAs.
  13. How is your system performance? Learn optimization from the expert Cary Millsap.
  14. DBAs blame developers for bad design, developers blame the database for being old and not scalable. Who is right? Learn how these groups can work together from the same man.
  15. Have you been to an Oracle presentation and been so entertained that you lost sense of time? You will after going to one of Connor McDonnald’s presentations. Last year his presentations received record high scores on content and delivery.
  16. Still confused about fusion? The middleware track will help you and your future will look green.
  17. How often have you googled for a solution and ended up with a nice write-up at oracle-base.com ? Now you can meet the man who wrote all of it, Tim Hall aka Oracle Base, look for a man in an Oraclenerd T-shirt.
  18. Not sure if Norway is big enough for Big Data? Surely Big Data Solutions Director Luis Campos from Oracle PT will tell us.
  19. Software projects are difficult, get off with a good start and a good data model in this presentation with Heli (the world has only one Heli like Heli, so first name is OK, we think).
  20. Do you like Scottish mood and complicated humour? You’ll be able to read 10053 trace files backwards before you understand all his jokes, which make his presentations never boring. Meet OUGN best supporter and a long-time friend, Doug Burns.
  21. Do you usually wait three years after release of R2 before you upgrade? You will have no reason to wait after you’ve got all the details in the Hitchhiker’s Guide to  Oracle Database Upgrades
  22. With 12c PL/SQL is stronger than ever, according to Bryn in his presentation P133.
  23. Some worried that Oracle would kill MySQL once they bought it. They were wrong, our MySQL track is full of good content from Oracle’s own developers and users out there. Meet attendees and Oracle staff and ask about MySQL (Care for an meetup btw? We’ve got an open slot for you.)
  24. Has everybody moved to Linux? Check out this presentation on Sparc and LDOMS.
  25. The apps track is back!
  26. You too are late learning APEX? Don’t worry, check out this.
  27. Several managers have signed up. If you manage DBAs and other techies this is your opportunity to see them in their preferred habitat, and you can exchange experience with other managers.
  28. Do you have challenges in your job? Alone with a new design, or maybe you have learned something unusual? Share your experience and discuss it with other techies. Learn what is not in the documentation.
  29. Introverts and control freaks have no better place to socialize then on a user conference. Not tired of discussing RMAN configuration? You will not be alone on this ship.
  30. Have a question for Oracle sale, pre-sale, technologists or someone from the C-level? Oracle Norway, US, DE and more are sending several to support us, and they don’t mind sitting down for a talk, be it in a bar or in the Boardroom. Who knows what can happen in international waters. Just don’t mention licenses on the dance floor, that is bad mojo.
  31. This year we have a new native app for smartphones. It will work even when you are offline. Make your own agenda and share it on Twitter.
  32. One of two frequent feedback from speakers and attendees is that the conference is well organized. Thanks to Color Line and the venue the conference is usually a smooth sailing. Actually we can’t think of any better venue, so we will probably use the same setup for many years.
  33. The other feedback is that the food is quite good. Breakfast, lunch and dinner are served in a restaurant with excellent standard. More info about the ship here.
  34. Being on a ship, people stick around and socialize with other speakers and attendees. Not too big and not to small. This is a fantastic networking opportunity; you’ll always find someone, when you’ve had enough you can always retreat to your cabin or a calm restaurant.
  35. The weather forecast looks quite promising, with sun, no rain and 4º C the Oslofjord will be a nice scenery going and returning.
  36. Tax free...
  37. Though we have wifi in the conference area, you may actually enjoy being offline sometimes. It is a good excuse to not read email and check whatever addiction you have when we are going through Skagerrak.
  38. If you come from abroad and want to know more about the Norwegian market you get to meet a variety of large and small customers.
  39. Actually more attendees from outside Norway has registered than any time before.
  40. Forgot to mention the cloud. Surely, someone will mention it on the conference. There will probably be more clouds in the sessions than in the sky, because we will have no-cloud weather during the conference.
  41. We also have many presentations with tons of no-hype stuff, proven work from experienced people.
  42. Seeing first-time speakers freak out when the ship starts moving is priceless.

If you are still reading this, it may be to late. Ticket sale ends Monday March 31, 09 hs CET. Mail us if you are desperate.


Saturday, January 18, 2014

Kurs med Cary Millsap i Oslo dagen før #OUGN14

Vårens store høydepunkt er OUGN sitt vårseminar. Her er programmet og her kan du melde deg på. Drar du til Oslo for å delta på konferansen anbefaler jeg at du flyr inn dagen før for å bli med på et annet stort høydepunkt, nemlig et dagskurs med Cary Millsap i Oslo.

Dette er et kurs både for utviklere som integrerer mot Oracle database og for Oracle DBA-er. En bedre presentasjon av kurset finner du på påmeldingssiden.

Personlig så ser jeg på det som et skille i karrieren min, mellom før og etter at jeg lærte metodene til Cary. "Tuning" som det ofte kalles er ofte forbundet med myter og råd som baserer seg på prøving og feiling, sjelden effektivt i komplekse systemer. Ved å bruke SQL trace og de riktige verktøyene til å finne frem til hva som tar tid, kommer man raskere frem til målet. Målet er altså å optimalisere responsen til en transaksjon mot databasen, en tung rapport, interaktiv bruk fra en web-side, osv.

Kurset begynner kl 10 og holder til i Bjørvika, det blir dermed mulig å fly inn til Oslo på onsdag morgen før kurset.

Har du spørsmål rundt kurset ta gjerne kontakt på Twitter, email eller telefon. Kurset arrangeres av Method R.


Friday, September 20, 2013

Upcoming conferences

OSL - CPH - SFO. Starting on my route in a few hours. Really look forward to see tweeps and a bunch of people from the worldwide Oracle community. Lots of good presentations of course, but also bloggers' meetup, Oaktable world, RAC Attack, Anchor Steam, and more.

I'll speak at the Bulgarian Oracle User Group conference  22nd to 24th of November. I have heard from many that this is a good conference. First time in Bulgaria means this will be a triple excitement; giving a presentation about something I have thought a lot about, seeing a new country and meeting a possibly different culture.

The UKOUG Tech conference starts the week after. That is another conference I've never been to (strangely enough since it is rather close compared to OOW).

If you happen to be at one of these conferences, please say Hi!


Sunday, September 15, 2013

Finding corrupt XML files on Linux

Here is an easy command to find corrupt XML-files on a Linux box.

A couple of days ago I had a problem with OBIEE and weblogic. Googling and search on My Oracle Support indicated that the failure may be caused by a corrupt XML file. And you know that there are many of them in an OBIEE installation. In fact MOS in Doc 1475146.1 blindly suggests to move a bunch of them to a backup location, because in some cases they had become corrupt for some strange reason. I was not satisfied with just deleting a few random files that have caused problems for others earlier, I wanted to find the file.

I then came up with the idea that I could find the corrupt file by parsing every XML file below a point in the file hierarchy.  On Linux this is quite easy. The command xmlint is possibly not standard on every system, but should be easy to get with yum or similar. In my case the file I was looking for belonged to user weblogic, meaning the corrupt file would reside in a directory called weblogic, hence the command. But you can of course tweak the arguments to the find command to search the files you want:


find . -wholename \*weblogic\*.xml -ls -exec xmlint {} > /dev/null \;


Any normal output will be discarded and only failures sent to stderr (on your screen). A similar command to parse every file with extension xml below $ORACLE_BASE:

find $ORACLE_BASE -name \*.xml -exec xmlint {} > /dev/null \;


If you (re)move the file you usually have to restart stuff with opmnctl.

Sunday, July 7, 2013

Corrupt database blocks III

If you don't know what procrastination is you could start your own blog. Start a post with something like "I worked with ... and decided to write three posts about it". Procrastination is what you experience before the last post is ready; seemingly simple things take forever to get out the door.

This post is important for my own record and shows how to salvage data from a large table with several corrupt blocks. I also had a learning experience I wanted to remember, explaining the redundancy of information here. Previous post was about how to map out tables and the corrupt blocks in a temporary table (that is, not a table created as a global temporary table, but a table you'll drop when you're finished.)

Note 61685.1 on My Oracle Support (MOS) shows how to extract data from a corrupt table. This post builds on some of that. Before you begin; data in corrupt blocks are gone. Unless you have special tools and start reading the data files with a tool like DUDE you won't recover those data. This is about how to recover the data that reside in blocks that have not been marked as corrupt.

One essential function gives you the ROWID from the block number and its file number: DBMS_ROWID.ROWID_CREATE:

select DBMS_ROWID.ROWID_CREATE(1,253071,89,660864,0) from DUAL;

In the example above 1 means extended rowid; no need to change this, 253071 is the DATA_OBJECT_ID from DBA_OBJECTS, 89 is FILE# (file number) from V$DATABASE_BLOCK_CORRUPTION , and 660864 is BLOCK# (block number) from the same view. The last parameter is for the row number in that block, in this case we want the whole block and therefore starts with row 0.

This is an example of the content of V$DATABASE_BLOCK_CORRUPTION:
FILE#     BLOCK#    BLOCKS  CORRUPTION_CHANGE#  CORRUPTION_TYPE
89        661112    8       0                   CORRUPT
89        661048    16      0                   CORRUPT
89        660984    16      0                   CORRUPT
89        660920    16      0                   CORRUPT
89        660864    8       0                   CORRUPT

In previous post I created a table SEGMENTS_WITH_CORRUPTION. The following query gives a report of corrupt segments with an interval by rowid using the function above:

select O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,
DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) ROWID_OF_FIRST_CORR,
  DBMS_ROWID.ROWID_CREATE(1,o.DATA_OBJECT_ID,c.file_id,c.start_corrupt+c.corrupt_blocks,0) ROWID_OF_NEXT_CLEAN
from segments_with_corruption c,DBA_OBJECTS O
where C.OWNER=O.OWNER
and c.segment_name=o.object_name;
An example of output (slightly redacted so I don't reveal the source)
OWNER  OBJECT_NAME  OBJECT_TYPE    ROWID_OF_FIRST_CORR   ROWID_OF_NEXT_CLEAN
LUSER  FUBAR        TABLE          AAA9yPABZAAChZ4AAA    AAA9yPABZAAChaAAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChY4AAA    AAA9yPABZAAChZIAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChX4AAA    AAA9yPABZAAChYIAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChW4AAA    AAA9yPABZAAChXIAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChWAAAA    AAA9yPABZAAChWIAAA

Now, create a new table to hold the good data. Using LUSER.FUBAR as the owner.table_name for the original table:


Create table luser.fubar_recover as
Select * from luser.fubar
Where 1=0;
This means we create an empty table with the same structure, we will fill it with data in a few steps. If you had only a few intervals of corrupt blocks, you can create insert statements like the following in order to retrieve the good data:

Insert into luser.fubar_recover
  select *
  from luser.fubar
  where rowid < ‘AAA9yPABZAAChZ4AAA’;

Insert into luser.fubar_recover
  select *
  from luser.fubar
  where rowid >= ‘AAA9yPABZAAChaAAAA’  
  and rowid < ‘AAA9yPABZAAChY4AAA’;

In the first example you get all the rows in blocks up to the corrupt one. In the second you get all the rows between two corrupt blocks. In my case I had a large table with several stretches of corrupt and good blocks. I did not want to handcraft all these insert statements, I wanted one statement that could retrieve all the good data from the table.

To achieve that I started with a query that generates interval of corrupt blocks in the order as they are laid out in the table. At this point I was only working on one table and one data file as the query below shows.
select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER=’LUSER’ and C.SEGMENT_NAME='FUBAR’
    order by c.start_corrupt
  )
;

The output looked like this:
FIRST_CORRUPT FIRST_CLEAN PREVIOUS_CORRUPT NEXT_CORRUPT RN
AAA9yPABZAAChWAAAA AAA9yPABZAAChWIAAA AAA9yPABZAAChW4AAA 1
AAA9yPABZAAChW4AAA AAA9yPABZAAChXIAAA AAA9yPABZAAChWAAAA AAA9yPABZAAChX4AAA 2
AAA9yPABZAAChX4AAA AAA9yPABZAAChYIAAA AAA9yPABZAAChW4AAA AAA9yPABZAAChY4AAA 3
AAA9yPABZAAChY4AAA AAA9yPABZAAChZIAAA AAA9yPABZAAChX4AAA AAA9yPABZAAChZ4AAA 4
AAA9yPABZAAChZ4AAA AAA9yPABZAAChaAAAA AAA9yPABZAAChY4AAA 5
Oh, well. Layout of the last table could have been better, I experimented with export html-code from SQL Developer that can be pasted into the HTML-tab.  Anyway, it shows stretches of blocks that are corrupt for this file. Now I want to generate WHERE clauses that are used in the select statement that retrieves the good data:


with INTERVALS as (
select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER='LUSER' and C.SEGMENT_NAME='FUBAR'
    order by c.start_corrupt
  )
)  select 'where rowid < ''' || FIRST_CORRUPT || '''' LINE
   from INTERVALS
   where RN=1
   union
   select 'or (rowid >= ''' || FIRST_CLEAN || ''' and rowid < ''' || NEXT_CORRUPT || ''')' LINE
   from INTERVALS
   where RN>1 and NEXT_CORRUPT is not null
   union
   select 'or (rowid >= ''' || FIRST_CLEAN || ''')' LINE
   from INTERVALS
   where NEXT_CORRUPT is null
   order by rn
  ;

This gives the following output:
LINE
where rowid < 'AAA9yPABZAAChWAAAA'
or (rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA')
or (rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA')
or (rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA')
or (rowid >= 'AAA9yPABZAAChaAAAA)

I can now amend the insert statement by using these where clauses at the end of the select statement:
insert   into LUSER.FUBAR_RECOVER
select /*+ ROWID(T) */ *
from LUSER.FUBAR T
where rowid < 'AAA9yPABZAAChWAAAA'
or (rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA')
or (rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA')
or (rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA')
or (rowid >= 'AAA9yPABZAAChaAAAA');


Also note the ROWID hint that tells Oracle how to scan the table. The biggest problem with this query is that it does not work(!) Yes, I'm going to drag you through the learning experience I had when making things automated and slightly more advanced. When executing the SQL above Oracle performs a normal table scan and aborts when it reads the first corrupt block. This can be seen if you look at the execution plan:

explain plan for
select /*+ ROWID(T) */ *
from LUSER.FUBAR T
where rowid < 'AAA9yPABZAAChWAAAA'
or (rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA')
or (rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA')
or (rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA')
or (rowid >= 'AAA9yPABZAAChaAAAA');
select * from table(dbms_xplan.display);

Plan hash value: 3975471795
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |    11M|  2111M|   838K  (1)| 02:47:37 |
|*  1 |  TABLE ACCESS FULL| FUBAR        |    11M|  2111M|   838K  (1)| 02:47:37 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWID<'AAA9yPABZAAChWAAAA' OR ROWID>='AAA9yPABZAAChaAAAA'
              OR ROWID>='AAA9yPABZAAChXIAAA' AND ROWID<'AAA9yPABZAAChX4AAA' OR
              ROWID>='AAA9yPABZAAChYIAAA' AND ROWID<'AAA9yPABZAAChY4AAA' OR
              ROWID>='AAA9yPABZAAChZIAAA' AND ROWID<'AAA9yPABZAAChZ4AAA')
Compare this to the plan for the SQL that was used in the beginning:

explain plan for
select /*+ ROWID(T) */ *
from LUSER.FUBAR T
where rowid >= 'AAA9yPABZAAChaAAAA';
select * from table(dbms_xplan.display);
Plan hash value: 3328636578
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  5445K|  1012M|   836K  (1)| 02:47:18 |
|*  1 |  TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|   836K  (1)| 02:47:18 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access(ROWID>='AAA9yPABZAAChaAAAA')

This one uses TABLE ACCESS BY ROWID, but the one that fails uses normal TABLE SCAN. I therefore changed slightly the SQL to generate where clauses:

with INTERVALS as (
select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER='LUSER' and C.SEGMENT_NAME='FUBAR'
    order by c.start_corrupt
  )
)  select 'select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid < ''' || FIRST_CORRUPT || '''' LINE 
   from INTERVALS
   where RN=1
   union
   select 'union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= ''' || FIRST_CLEAN || ''' and rowid < ''' || NEXT_CORRUPT || '''' LINE
   from INTERVALS
   where RN>1 and NEXT_CORRUPT is not null
   union
   select 'union select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid >= ''' || FIRST_CLEAN || ''';' LINE
   from INTERVALS
   where NEXT_CORRUPT is null
   order by RN
  ;

The resulting SQL used to retrieve good data looks like this:

select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid < 'AAA9yPABZAAChWAAAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid >= 'AAA9yPABZAAChaAAAA';

This one actually runs, but it runs for a long time and it eventually failed later because TEMP ran out of space. The reason for this can also be seen in the execution plan:
Plan hash value: 3469639691
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    11M|  2177M|       |  4680K (78)| 15:36:06 |
|   1 |  SORT UNIQUE                  |              |    11M|  2177M|  2858M|  4680K (78)| 15:36:06 |
|   2 |   UNION-ALL                   |              |       |       |       |            |          |
|*  3 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|       |   836K  (1)| 02:47:18 |
|*  4 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|       |   836K  (1)| 02:47:16 |
|*  5 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|       |   836K  (1)| 02:47:16 |
|*  6 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|       |   836K  (1)| 02:47:16 |
|*  7 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|       |   836K  (1)| 02:47:18 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(ROWID<'AAA9yPABZAAChWAAAA')
   4 - access(ROWID>='AAA9yPABZAAChXIAAA' AND ROWID<'AAA9yPABZAAChX4AAA')
   5 - access(ROWID>='AAA9yPABZAAChYIAAA' AND ROWID<'AAA9yPABZAAChY4AAA')
   6 - access(ROWID>='AAA9yPABZAAChZIAAA' AND ROWID<'AAA9yPABZAAChZ4AAA')
   7 - access(ROWID>='AAA9yPABZAAChaAAAA')
 

Look at line 1 and 2 in the plan. I forgot to use UNION ALL instead of UNION and Oracle then goes on to sort this stuff in order to return distinct rows.  The table had over 100M rows and sorting this filled the TEMP tablespace completely. Since I know I want all rows, I changed it to UNION ALL (this is finally a good one you can use):

select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER='LUSER' and C.SEGMENT_NAME='FUBAR'
    order by c.start_corrupt
  )
)  select 'select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid < ''' || FIRST_CORRUPT || '''' LINE 
   from INTERVALS
   where RN=1
   union 
   select 'union all select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= ''' || FIRST_CLEAN || ''' and rowid < ''' || NEXT_CORRUPT || '''' LINE
   from INTERVALS
   where RN>1 and NEXT_CORRUPT is not null
   union all
   select 'union all select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid >= ''' || FIRST_CLEAN || ''';' LINE
   from INTERVALS
   where NEXT_CORRUPT is null
   order by RN
  ;
The new execution plan looks like this:
Plan hash value: 439408122
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    11M|  2177M|  4181K (81)| 13:56:23 |
|   1 |  UNION-ALL                   |              |       |       |            |          |
|*  2 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|   836K  (1)| 02:47:18 |
|*  3 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|   836K  (1)| 02:47:16 |
|*  4 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|   836K  (1)| 02:47:16 |
|*  5 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|   836K  (1)| 02:47:16 |
|*  6 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|   836K  (1)| 02:47:18 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(ROWID<'AAA9yPABZAAChWAAAA')
   3 - access(ROWID>='AAA9yPABZAAChXIAAA' AND ROWID<'AAA9yPABZAAChX4AAA')
   4 - access(ROWID>='AAA9yPABZAAChYIAAA' AND ROWID<'AAA9yPABZAAChY4AAA')
   5 - access(ROWID>='AAA9yPABZAAChZIAAA' AND ROWID<'AAA9yPABZAAChZ4AAA')
   6 - access(ROWID>='AAA9yPABZAAChaAAAA')
 
To wrap it up:

If you have a large table with several corrupt blocks and many good ones, you can save the good data by creating a table that maps the bad blocks and then create a select statements that retrieve the rows from all the good blocks.

Best is of course to get your backup and recover routines in order, combine it with a decent fault management so you'll discover the corrupt blocks at an early stage, thereby making it possible to recover the blocks before they have been aged out of your backup system.

If corrupt blocks happens again and again you should see if it happens only with very few tables or many. If the corruption is restricted to few out of many tables it may indicate that it has something to do with how these tables are created and used (thinking about NOLOGGING operations here). In case of frequent and seemingly random corruption I would start thinking about the underlying storage.

Tuesday, May 21, 2013

Download from OTN slow? Check DNS

Sometimes download from Oracle Technology Network (OTN) is really slow, but it may not be Oracle's fault. Oracle is using Akamai Technologies  to distribute the software. You can see this if you do an nslookup on download.oracle.com and see it resolves (finally) to a host under akamai.net.

They have servers all over the world and when things work as they are supposed to the box on your end should connect to a host physically near you and not on another continent. If your download is really slow find the IP address that your DNS resolves for download.oracle.com (or the host you are downloading from) and then use a geo-lookup site to see where it is located.

Just now the DNS server for my local ISP (get.no) resolved download.oracle.com to two servers in California:

[root@rio ~]# nslookup download.oracle.com
Server:         84.208.20.110
Address:        84.208.20.110#53

Non-authoritative answer:
download.oracle.com     canonical name = download.oracle.com.edgesuite.net.
download.oracle.com.edgesuite.net       canonical name = a1961.d.akamai.net.
Name:   a1961.d.akamai.net
Address: 64.209.77.10
Name:   a1961.d.akamai.net
Address: 64.209.77.18

I used www.infosniper.net to find where this host was located. Me being in Oslo this was obviously not optimal. The only DNS server I can remember is 8.8.8.8 which is one of Google's public DNS servers. I decided to change /etc/resolv.conf to use 8.8.8.8 as the primary DNS server, and download.oracle.com now resolves to two servers in Germany:
[root@rio ~]# nslookup download.oracle.com
Server:         8.8.8.8
Address:        8.8.8.8#53

Non-authoritative answer:
download.oracle.com     canonical name = download.oracle.com.edgesuite.net.
download.oracle.com.edgesuite.net       canonical name = a1961.d.akamai.net.
a1961.d.akamai.net      canonical name = a1961.d.akamai.net.0.1.cn.akamaitech.net.
Name:   a1961.d.akamai.net.0.1.cn.akamaitech.net
Address: 46.33.72.121
Name:   a1961.d.akamai.net.0.1.cn.akamaitech.net
Address: 46.33.72.129

Here is the link if you like maps... I then flushed DNS in Firefox, restarted the download, and confirmed with netstat -an that the connection now went to a server in Germany. The download went much faster, well, at least for a while... YMMV.

Actually I thought that using Google's DNS server could give a suboptimal response if Google's DNS server was located somewhere far away (like in USA), but clearly they have some magic here that returns an IP address better than the DNS server of my local ISP.

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.