Home » Other » General » What happens when one of the dbw0 processes fail for whaterver reason?
What happens when one of the dbw0 processes fail for whaterver reason? [message #494729] Wed, 16 February 2011 16:38 Go to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Oracle allows a maximum of 20 dbw (database writer) processes. What happens to the database when one of them fails ?
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #494743 is a reply to message #494729] Wed, 16 February 2011 19:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>What happens to the database when one of them fails ?
Tested, robust & stable software does not automagically "fail".
If/when such a failure occurs, then you need to submit a Bug Report to identify the problem so it can be corrected so it does not occur again.

EXACTLY what is the point of this question?

Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #494750 is a reply to message #494743] Wed, 16 February 2011 23:11 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Not sure what is the guidline for asking a question.

This question was asked of me in an interview. I wanted to know what savy dba have to say. It did not go well for me.
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #494905 is a reply to message #494750] Thu, 17 February 2011 08:27 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then this paragraph would probably be a good start.

(Also have a loot at the picture above the paragraph)

What will happen if what is described there does not happen any more?

[Updated on: Thu, 17 February 2011 08:29]

Report message to a moderator

Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #495131 is a reply to message #494729] Thu, 17 February 2011 14:18 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
My line of thinking is that the dbw0 that has failed will simply not be able to right any blocks to a datafile. Not sure if this would constitute a media failure but I imagine that the other dbw0 processes will continue to write to the datafiles. As long as there is at least one dbw0 running the system would not fail.


I honestly did not have a straight answer for this but I have not heard from anyone who does. If that asshole who interviewed me finds this, I would like him to share his experience.
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #495354 is a reply to message #495131] Fri, 18 February 2011 06:39 Go to previous messageGo to next message
John Watson
Messages: 8593
Registered: January 2010
Location: Global Village
Senior Member
I have to say that seems seems like a perfectly reasonable interview question to me. Any competent DBA will know what happens if a background process fails. I won't tell you the answer: you can find out by doing the test. Start up an instance, and kill the dbw0 process. The result will be obvious! Full details will be in your alert log.
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #495858 is a reply to message #495354] Tue, 22 February 2011 20:17 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Here is what I found.

After killing the dbw0 process I did the following update and got the error.


>update emp set sal = sal * 1.1;


ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 14285
Session ID: 170 Serial number: 7


/* in alert_log */

Tue Feb 22 20:49:31 2011
Completed: ALTER DATABASE OPEN
Tue Feb 22 20:52:46 2011
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_14165.trc:
ORA-00471: DBWR process terminated with error
PMON (ospid: 14165): terminating the instance due to error 471
Instance terminated by PMON, pid = 14165


/* in orcl_pmon_14165.trc */

Background process DBW0 found dead
Oracle pid = 10
OS pid (from detached process) = 14183
OS pid (from process state) = 14183
dtp = 0x60017660, proc = 0xc1335a10
Dump of memory from 0x0000000060017660 to 0x00000000600176C0
...
...
...
error 471 detected in background process
ORA-00471: DBWR process terminated with error

*** 2011-02-22 20:52:46.245
PMON (ospid: 14165): terminating the instance due to error 471



This means that the process monitor will detect that the dbw0 is missing
and shut down the instance. This answers my question as to what happens
when a single dbw0 process is on the machine. I do not have the means to
test what happens in case one of many dbw0 fails. If someone can try this
I will be greatful to learn.
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #495872 is a reply to message #495858] Tue, 22 February 2011 23:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why can't you test with mutliple writers when you can with one?

Regards
Michel
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #495899 is a reply to message #495858] Wed, 23 February 2011 02:41 Go to previous messageGo to next message
John Watson
Messages: 8593
Registered: January 2010
Location: Global Village
Senior Member
Add more database writers by adjusting the db_writer_processes parameter (it is a static parameter).
You'll find that some other background processes can be killed, but most will result in the instance terminating. I've never made a comprehensive list. This is why I love Oracle: a simple test that takes two minutes can save hours of speculation. When you aren't sure of something, try it out.
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #496402 is a reply to message #495899] Sun, 27 February 2011 19:26 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I was hesitant to alter the parameter because I thought it was dependent on the number of cpu. Anyway I decided to go boldly
where I have never been and added another db_writer process and when I killed one the entire instance terminated. So the simple
answer is that when one db_writer process goes down the instance will be terminated. Thanks to all especially JW .
Re: What happens when one of the dbw0 processes fail for whaterver reason? [message #496417 is a reply to message #496402] Mon, 28 February 2011 01:47 Go to previous message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
As you could see, the best way to get a reliable answer is to make the test.

Regards
Michel
Previous Topic: Which parts most delay occur in online application?
Next Topic: Newbie looking out for enterprise application integration tools
Goto Forum:
  


Current Time: Wed Sep 22 22:43:18 CDT 2021