Saturday, February 7, 2009

Oracle: Types of database failures

There are different reasons for a database to fail .Some of them may result a partial failure ,some may become complete disaster.

Oracle database is enhanced with different types of recovery solutions for different types of failures.Some failure types can be recovered without dba's interaction. means oracle server will take care . on the other hands ,some requires serious dba's interaction. that's what a dba is paid for.............

let us check the database failure types:

1) Statement level failure
2) Network failure
3) User process failure
4) Instance failure
5) Media failure
6) Users issuing unwanted sql statements

a brief discussion:

1) Statement level failure:

a) the statement is not in proper syntax
solution: check the syntax and correct it

b) The user executing a statement does not have proper privilege to do so. for example:
select * from a.test;
here user is trying to access a table named test from a user's schema . if you do not have select privilege on test table , the statement can fail;
solution: give proper privillege to the user.

c) A huge insert or update which requires more space in destination tablespace to complete , compare to how much space already available.
solution: increase the size of tablespace/datafile/autoextend the tablespace or datafile

d)
A statement can fail because of programmatic logical errors
solution: refer to development team for correction

e) trying to enter data to an oracle table violating the integrity of a table. means you are trying to enter invalid data
solution: check for the constraints enabled on that table and correct the data as it allows

2) network failure
May happen because of network interface card(NIC) failure or network connection (physical or logical) failure or oracle net listener failure
solution: configure multiple nic cards on your database server.
have backup physical links from the database server.
you may have backup listener also

3) user process failure:
user process may fail because of an abnormal termination of user process(shutdown of user's machine, failure of running oracle client software , user may disconnect by clicking on cross sign to close an applications.
solution: does not require dba interaction as oracle has background process called pmon to release the resources,locks held by that session and rollback the uncommitted changes by that session
4) instance failure:
a) hardware failure
b) power failure
c) background process like pmon shuts down the database because of an error situation
solution:
check the alert log file, trace files generated by different background processes,
start the database by using startup command from sql*plus prompt
while the database startup process, oracle will automatically perform instance recovery
(known as crash recovery also) , that is to put the database in consistant state
by performing roll forward the committed changes and roll back uncommitted changes
so , no interaction required from dba

5)media failure

any failure of reading from or writing to disks are known as media failure Normally this is what a dba is paid for .So it requires a complete dba interaction. this may happen because of hard disk crash,disk controller fails, a head crash of hard disk modify,delete the oracle files (datafiles,control files,redo files) manually or by any other applications then oracle.

solution: There are different solutions for these depending on what type of media failure are in picture. you may need to restore your complete database, or only control files or datafiles or redo files , may be archived log also.

NOTE: This also depends on how is your backup and recovery strategy to recover from a
disaster like this.and how quickly you can do the same


6) users issuing unwanted sql statements:

users may drop a table unwantedly
users can modify(insert/delete/update) data inside a table unwantedly or may need a
rollback after commit
solution: use oracle flashback feature to restore a table to a previous point in time or
to restore the dropped table
You can use log miner tool to determine the changes
If the changes are not yet committed then you can issue simple rollback command
to rollback the changes

Thanks,
have a nice day...........
Our greatest glory is not in never falling but in rising every time we fall.
Confucius

19 comments:

Anonymous said...

I've been browsing online more than 2 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all website owners and bloggers made good content as you did, the web will be a lot more useful than ever before.

Also visit my webpage ... diet that works

Unknown said...

new line smile
Such a very informatics post, as I have browsed the majority of your posts. But I found this post is almost certainly where I got the most useful information for my research. It is really very interesting to read this particular post. I just would like to say thanks for your great efforts.

Anonymous said...

Great one particular Mehdi....at any time since I came to understand about SL about 2 many
years in the past, I have not looked again at something else.
Now granted I have not been consistent over these
2 many years but SL may be the only factor I did when i did
it. I just never could get the best time of
working day nailed down to exactly where it benefited me one
of the most. Now I've, and I am viewing gains just over the previous thirty day period of Constant instruction. I hold telling myself to be client along with the benefits WILL come. I'm gradually acquiring
my nourishment in verify, but it really
is much better than it had been per month in the past.
Many thanks Mehdi for this program!

Also visit my site ... mike chang monster mass free

Anonymous said...

Even though I find some of these things to be
annoying, they never impact my everyday lifestyle and if it
gets towards the stage which i never desire to see any
of it anymore then Facebook has several features to make these folks disappear from your feed.



My webpage; how to get women

Anonymous said...

I've tried a few of these and have had some very good results. Shared

Also visit my page; cvs teeth whitening pen

Anonymous said...

Its like you read my mind! You seem to know so
much about this, like you wrote the book in it or something.
I think that you could do with some pics to drive the message home a
little bit, but other than that, this is excellent blog.
An excellent read. I'll definitely be back.

Feel free to surf to my site customer reviews on vigrx plus

Anonymous said...

These "sheds" are wonderful. But you could have problems getting your grown kids to move
out if you experienced these kinds of accommodations on web site.
lol.

Feel free to visit my site; arrow 10ft x 14ft lindale shed reviews

Anonymous said...

Also in Russia, I was stunned by the quantity of people
(mainly female) who experienced their phones hanging around their necks on a lanyard (admittedly these were not smart phones five several
years ago in Russia).

Also visit my web blog; superman stamina video

Anonymous said...

Helpful information. Lucky me I found your site by chance,
and I am shocked why this twist of fate didn't happened earlier! I bookmarked it.

Feel free to surf to my web-site office furniture workstation malaysia

Anonymous said...

Helpful information. Lucky me I found your site by chance, and I am shocked why this twist of fate didn't happened earlier! I bookmarked it.

My weblog - office furniture workstation malaysia

Anonymous said...

Of course, you can't discover without having order initial. . That is another piece on the schooling puzzle. ? It's
frequent perception... but some of these men and women with PHDs
often overlook this.

Feel free to visit my webpage; attract women with this easy body language trick

Anonymous said...

Thanks Uncinus' for your information. Much appreciated. I recognize where the majority of these people are comming from even so, in terms of conspiracy theories as well as a basic mistrust in the energy elite and our own federal government. You'd have to be in severe denial
or blind by decision not to see this country is in critical problems, that
we are lied to by mainstreem media and our personal elected officers on a variety of troubles.
The blood will flow within the streets prior to its over.


My web site :: 60 minute stamina interactive training videos free download

Anonymous said...

I truly love to consume wholesome and I genuinely want to assist individuals that are obesity.
I may possibly only be 12 many years previous but even at this age I truly feel that if I speak out on the planet I maybe in a position
to help out inside the world.

Feel free to visit my webpage :: $10 gift subscription to healthy cooking taste Of home magazine

Anonymous said...

Just saw the update. In the event you never mind my asking, when
in the working day did you do your exercise and your very first feeding?
And how lengthy did you wait until you had the sirloin/potato food?


Also visit my site: How to eat healthy food

Anonymous said...

No way! Significant?

Here is my website; six pack shortcuts mike chang complete workout free download

Anonymous said...

I cannot truly identify with most of these,
even though I gave birth 3 months in the past. I hated my
1st (and only) pair of maternity jeans. They never did fit me proper.
I did a house delivery, so I never had an ultrasound or drank
the horrible koolaid. I put collectively a child registry just so I'd possess a checklist of factors to conserve up for. I never actually gave it to anybody and by no means experienced a child shower. I by no means did uncover a comfy place or comfortable pillows. And that i tried to not get my hopes up with the constructive pregnancy check given that the final one particular finished in miscarriage.But I am able to recognize using the vomiting, require to pee, as well as the elastic hair tie.

Here is my webpage 2gts dvds

Anonymous said...

Great article! That is the kind of info that are supposed to be shared
around the web. Disgrace on the seek engines for no longer positioning
this put up upper! Come on over and discuss with my site .
Thanks =)

My web blog - CSR Racing cheats; mrcg.evertidegames.com,

Unknown said...

Do you have a spam problem on this website; I also am a blogger,
and I was curious about your circumstance; many of us have designed some nice
techniques and we are seeking to swap techniques with other people, be sure to send me an email if intrigued.


See my video about a Tax Lawyer
Tax Attorney

Anonymous said...

Hi, I do believe this is a great web site. I stumbledupon it
;) I will return once again since I book marked it. Money and freedom
is the greatest way to change, may you be rich
and continue to guide other people.

my web-site - Dui attorneys Columbia South Carolina