This discussion is archived
6 Replies Latest reply: Feb 12, 2013 7:27 AM by Aman.... RSS

Few general questions.

AnkitV Newbie
Currently Being Moderated
Hi

I have below doubts/questions.

1) Its said that DBWn doesn't write to data files in real-time for performance reasons. The changes remain in buffer cache before being written to data files via DBWn.
When I COMMIT a change, and within a SECOND, my colleague sitting thousands of miles away from me fire a SELECT to check the modified data, he does see the new
modified data. Does it mean that DBWn wrote to data file within that SECOND or is it read consistency in action ?

2) In our production database, several temporary tables are dropped and re-created everyday. As recyclebin feature is enabled, they will certainly appear in UNDO tablespace.
My questions is, should there be a periodic job (weekly/fortnightly/monthly) which issues 'purge recyclebin' command to empty out the recyclebin OR oracle takes care of this
itself ?

3) I have a local database (Oracle 10gR2) on my laptop using windows 7. In what case do I need to use oradim utility in windows ? I mean when I start windows,
ORCLService automatically starts. When I shut down system, it goes off. So do I really need to use oradim here to start/stop ORCL database ?

4) Also, as I said, when windows shutdown, ORCL goes off, is it equivalent to instance failure (or shutdown abort) in database terms ?
Should I manually stop the ORCLService everytime before windows shutdown ?

Thanks.
  • 1. Re: Few general questions.
    Padma.... Newbie
    Currently Being Moderated
    Hi,
    You asked
    1)My questions is, should there be a periodic job (weekly/fortnightly/monthly) which issues 'purge recyclebin' command to empty out the recyclebin OR oracle takes care of this itself ?

    and the answer is the space reclamation of the recycle bin objects is automatically done when the requirement comes.

    2)Also, as I said, when windows shutdown, ORCL goes off, is it equivalent to instance failure (or shutdown abort) in database terms ?
    Should I manually stop the ORCLService everytime before windows shutdown ?

    and the answer is it is equivalent to instance failure so better manual stop of the service before shutdown

    Thanks
    Padma...
  • 2. Re: Few general questions.
    John Stegeman Oracle ACE
    Currently Being Moderated
    1). Your colleague a thousand miles away can still read from the buffer cache.

    2). Why not use permanent tables instead? The Recycle Bin does not use UNDO either. Oracle will take care of reclaiming space from the recycle bin when it needs to, however, so you don't necessarily need to do anything about this.

    3). No you don't - the Windows Service does that for you

    4). http://docs.oracle.com/cd/B13789_01/win.101/b10113/admin.htm#i1006074
  • 3. Re: Few general questions.
    AnkitV Newbie
    Currently Being Moderated
    Thanks for the answer John..

    Can you please tell more about first question.
    1) Does it mean that DBWn wrote to data file within that SECOND or is it read consistency in action ?

    You said "he can read from the buffer cache" the new value. He will read from buffer cache because that value in buffer cache is marked committed, is it ?
    If it was not committed at the time he issued query, he would have read old value from UNDO, and that would have been read consistency. Am I correct ?

    Thanks,
  • 4. Re: Few general questions.
    John Stegeman Oracle ACE
    Currently Being Moderated
    The data in the buffer cache isn't marked as committed directly, but you can conceptually think of it that way, yes.

    Your understanding is fundamentally correct.
  • 5. Re: Few general questions.
    EdStevens Guru
    Currently Being Moderated
    AnkitV wrote:
    Hi
    <snip>
    4) Also, as I said, when windows shutdown, ORCL goes off, is it equivalent to instance failure (or shutdown abort) in database terms ?
    Should I manually stop the ORCLService everytime before windows shutdown ?

    Thanks.
    Take a look at the alert log. See if there is a 'crash recovery' operation as part of the automatic startup. Then you will have discovered for yourself the answer to the above.
  • 6. Re: Few general questions.
    Aman.... Oracle ACE
    Currently Being Moderated
    AnkitV wrote:
    Hi

    I have below doubts/questions.

    1) Its said that DBWn doesn't write to data files in real-time for performance reasons. The changes remain in buffer cache before being written to data files via DBWn.
    When I COMMIT a change, and within a SECOND, my colleague sitting thousands of miles away from me fire a SELECT to check the modified data, he does see the new
    modified data. Does it mean that DBWn wrote to data file within that SECOND or is it read consistency in action ?
    Well, you have pretty much gotten the answer already but just to echo, DBWR writes only when there is a CHECKPOINT call done. And with COmmit, there is no such thing that happens. Your colleague, doesn't matter where he is sitting, is getting the data from the buffer cache only because, with the change, the actual buffer gets modified. Normally, what's told in the DBA classes that Oracle would keep both the old and the new images at the same time in the buffer cache when an update happens which is partially true. Oracle does brings in the undo image but only when there is a requirement of it, for read consistancy purpose. It's not going to do it just iike that. So if you have done a change, rather a small change, it's going to effect the actual copy of the buffer in the buffer cache and with the transaction being committed, it's that buffer only which is marked as committed with the actual data being updated with the Commit SCN. So you and your colleague are going to see that very same buffer only being given for the select purpose.

    HTH
    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points