YEEEEEEEEEESSSSSSS sigh

I did it! Finished Tom Kytes book in the allotted time. It was really hard, but also very nice to have done. I guess it's a tiny bit what those maniac running a marathon, or doing the ironman feels like ;-) Don't get me wrong I loved the book, so much awesome insight to issues I know will be looking at in the future and think, good thing I read that book!

Now I move on to Lex de Haan's Mastering SQL and SQL*PLUS.

Hey so when I am done I am both an Expert and Master!!! Yeah right!!
I am looking foward to this book, probably mostly because it has hands on excersises :-) at the end of each chapter.

A short boost

Read chapters 13 and 14. Chapter 13 was about partitioning, this was quite interesting, especially because I had heard a lot about it, but never seen it in action. Lots of great concepts. Have to go back later and get a better understanding of how indexes interact with partion_key_columns and other columns. I didn't completely grasp all that.
Chapter 14 was another dull chapter, parallel execution. Again nothing wrong with the information, it just didn't grab me, not a lot I took with me from that chapter. Struggled hard to get it done, I procrastinated quite a bit on this chapter. Don't know if it is an effect from having spend 3 weeks on this book with intense reading and a monstrous amount of new information, tips and tricks, concepts and right and wrong way to do this. My head might just need something more simple for a short while.

On a completely different subject. Noticed today that my Miracle notepad is not made for writing on both sides, it is completely blank, only lines on the front of the paper. Weird. HE! and that made me remember that when I worked for Maersk Data, they made the business card so that you could not see what you wrote on the backside. They did this because it was not considered "good behavior" to write on the back of a business card... Wonder if Miracle has the same perception on the notes you take???

I am struggling

Wow the book has just taken an ugly turn. Chapter 10 100 pages of information about tables, yikes. The information is great but damned there's lots of it, and having to read in one day is just not that easy. Also has a long description of nested tables and the kicker is, don't use it you have other and in most cases better options. That's like watching a Mchael Mann movie and then have to leave 15 minutes before the ending. Anyways, long chapter 11 about indexes, very much insight in this chapter, so it was a bit easier to read. Chapter 12 almost had me broken. To me that chapter was jus dull, it was 70 pages about the 22 SQL types that is the baseline for most other database types. Maybe it was just because I was struggling for time and sleep and other stuff, but I really didn't feel this chapter gave nearly as much to build on as the other chapters. Best thing I got out of it was how DATE is stored, and how not to use to_char in a comparison. Other than that I felt it should have been a referencial appendix. I am at the point right now where I am looking forward to be done with the book.

Undo the redo or redo the undo!

Very interesting chapter 9 in Tom Kytes book. I knew about the undo and redo feature, but there is definately some issues that are very confusing about this stuff. See my previous post for an example of that. In actuality, my question was about redo for undo in regards for a temporary table, this I got an answer on after a lot of debate during the nerds about recovery issues with redo and undo. This answer also had a challenge, but unfortunately I accidentally deleted the mail so I don't have the answer nor the challenge. However, I have requested a copy from the writer.

Another interesting issue was Delayed Block Cleanout causing an ORA-01555: Snapshot too old.
I was send to look at this excellent metalink note: 40689.1

It really spells out the issue.

EDIT:
I got the mail, the challenge was to describe why Delete generates more redo than an insert in a global temporary table. This is very nicely explained in this chapter. In short the reason is that GTT does not generate redo, the only redo that is made is the undo blocks that when they change will be added to the redo. So GTT only generates undo. Important fact to remember here!
So the asnwer is: Since an Insert hardly creates any undo, but lots of redo, the GTT has very little redo for this operation. (mostly stuff like adding a new extent to the segment).
Since a Delete has lots of Undo, and little redo, then a GTT will have lots of redo (as a result of the undo amount), in fact the redo-size for both permanent and GTT is very much alike due to the fact that the delete is using mostly undo, and hardly any redo ( besides the undo-redo) and thus must be similar in redo-size.

The challenge!

Hm, made the "mistake" to ask the nerds of Miracle for clarification on the undo-redo subject. That ended up in a challenge for me to clarify to prove I had understood the concept of hteir clarification.

Loosely translated, here's the question:

If I have a transaction that takes 2 hours to complete and during this execution, there has been 5 log switches (there is only 2 log groups, and 5 minutes before the end of the transaction the power shuts off. How much redo and undo will be read from both the rollforward and the rollback, and where are they taken from, and under what assumptions regarding "enough space".

It took me some time to figure this out, but here's my answer: (it might not be a 100 % correct in a complete technical view, but it is only to catch the main picture)

I have acouple of assumptions to the question:
1)The transactiondoesn't have commits
2)the database is in Archivelog mode

First and foremost, the rollforward action that the automatic instance recovery will do, is only using online redo logs, thus, it will only read the undo blocks back to the undo segment which has a higher SCN than the last checkpoint.
A log switch forces a checkpoint, thus, all the uncommitted data would have been written to the datafiles. This means that at the checkpoint time undo is written to the datafiles, and the changes are also written to the datafile the object belongs to, though with the information in the blockheader that these changes are not committed.
When rollforward has replayed the "lost" undo segments, the database can be opened and SMON will start to rollback the blocks that needs to be rolled back.
The transaction has been declared dead and all undo made by the transaction need to be rolled back. It is not necessarily only SMON that does this a "user process" could reach the block first and it will then do the rollback needed to get the correct version of the block

The space issue shouldn't be a problem since before the crash there was no problems handling the undo in the tablespace, otherwise an error would have appeared.

The Archived logs with the uncommitted data can be used to recreate the undo segments if a datfile should crash before the next backup is taken. Though, one might think that it would be a good idea to take a backup after the instance recovery has synchronized the datafiles?

I've have received an answer that my picture of this is according to the person asking the question. Since this person is an OCM I take that for a good omen!!!

Visit to Miracle Breweries

This past monday a couple of new hires, like myself, was invited to visit our adminsitration office for some practical information about the company. This also included a tour of the brewery. He He, a company with their own microbrewery, how can you turn down a joboffer from such a company? Gotta love it. Anyway, it was quite interesting and hopefully I will be invited back when they are brewing a new bacth.

Fresh Meat to the MEEP blog

Hello,

My name is Lars and I started with the MEEP Program feb. 1st. I have worked with Oracle products for more than 6 years. Turned DBA last year. In december I passed the Oracle Certified Associate exam, so now I have a peace of paper that says I can study and understand Oracle concepts. :-) So I hope this knowledge will help me through MEEP.



I started with Tom Kytes book: Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

This far I have read 9 chapters, which has covered the basic architecture of databases, such as memory structures, data files, how the database ensures read and write consistency, especially how Oracle does this, locking mechanisms both in objects and Oracles own belly (latches), the different background processes, how transactions work, and the current chapter is redo and undo.



Most of this is not new to me, but the thing that is absolutely awesome about this book is the real life situation, consideration and the no-nos. Which, to me, just takes the understanding of the architecture to a different level.

F.ex. Tom writes about transactions, and how putting in a null in the exception area of a PL/SQL code completely changes the outcome of the transaction since the statement (the procedure, doesn't raise an error) I have seen this many places, and now I start to understand some of the weird errors that I saw in a previous work.


Seems I really grasped (for a newbie) the idea of how Oracle keeps read-consistency. I wrote an email to all the autistic nerds and gave my explanation of how it works internally, and I got an approval of my thought proces from the almighty M2, one of the few who is an OCM (Oracle Certified Master).

The Nose Job Part Two

The wonderfully insane company that I work for, Miracle, has done it again.
This time Morten Egan does a complete Oracle installation on a SAN with his NOSE while wearing a straitjacket. It´s quite amazing stuff.
The whole story started when IBM wanted to be payed for 50 manhours for doing a fairly simple Oracle installation. More details in the videos.
Enjoy...

The Nose Job Part Two http://www.youtube.com/watch?v=SNW-7jCBWsw
You can find Part One right here: http://www.youtube.com/watch?v=CHzV4LZnvHc

Jan

I survived MEEP....

My 90 days in Hell are over. I did my exam yesterday.

The concept of the exam was simple:
First I had to do a Presentation about the education, about my experiences, mistakes made along the way, Pro´s, Con´s etc.
After doing this presentation, I was to answer questions about Oracle by the audience.
The whole session took about 2 hours.

My Presentations contained the following points:

1. Introduction - MEEP, Inspired by the Royal Danish Special Forces training method.
2. Introduction of Me and my Background
3. My experiences with Miracle and My Colleagues
4. The course of MEEP, The Books, The Plan etc.
5. The Learning Method, How I learned the Stuff, Mistakes made along the way etc.
6. MEEP v.2.0 - The next batch (In Progress)
7. Questions

The Questions, of course, were the most nerve-wracking part of the Session.
And I was, I must admit, on thin ice during some of the questions. This was primarily due to my frayed nerves, since I was able to answer many of the same questions the day before, where I went through the Oracle Concepts Guide with my Mentor, Lenn Mikkelsen.

Lenn asked me questions about all the topics, and even though I could´nt answer all of them, he told me that my Knowledge Level was good enough to do the Exam. Lenn helped me a lot through the last few days. Thank You, Lenn.
For those of you who don´t know Lenn, I can tell you that he has over twenty years of experience and is extremely skilled.

Anyway, I got through the questions and then it was jugdement time. Every person in the Audience was asked if they thought I passed or not and they had to substantiate their statement. All of them gave me the thumps up, and Mogens said: 'Welcome To Miracle'.

So what now? I will now start working in a more 'normal' fashion, by taking my place in the Miracle group, that do Online Checks.
I´m already responsible for 3 Customers. That said, I am still going to read books and develop my skills. For example, I want to improve my SQL and possibly even learn some PL/SQL. A little further ahead, I would like to learn SQL Server too.

As for this blog, I´m turning it over to the new MEEP batch; Lars, Martin, Flemming and Morten. Good Luck to you Guys...
In fact, one of them has already started his own blog, meandmeep.blogspot.com. Check it out!

Jan