FAQ
Hi all,

When we shutdown Oracle instance with shutdown immediate mode. The Oracle
will wait until all rollback completed.

The challenge is, how we can login to the instance to check the progress of
rolling back, assume we are given sysdba privilege.

Our attempt:
Login / as sysdba unable to query v$transaction. as shutdown in progress not
allow query.
Why Oracle allow to log in as sysdba while nothing can be done in the so
call session.

Thanks for sharing your ideas.

Search Discussions

  • Sinardy Xing at Jun 27, 2007 at 2:58 am
    Hi all,

    I would like to rephrase my question, how do we monitor the progress of
    shutting down immediate mode.

    Thanks.
    On 6/27/07, Sinardy Xing wrote:

    Hi all,

    When we shutdown Oracle instance with shutdown immediate mode. The Oracle
    will wait until all rollback completed.

    The challenge is, how we can login to the instance to check the progress
    of rolling back, assume we are given sysdba privilege.

    Our attempt:
    Login / as sysdba unable to query v$transaction. as shutdown in progress
    not allow query.
    Why Oracle allow to log in as sysdba while nothing can be done in the so
    call session.


    Thanks for sharing your ideas.

    --
    regards,
    Santos
    --
    http://www.freelists.org/webpage/oracle-l
  • Kerber, Andrew W. at Jun 27, 2007 at 1:00 pm
    The simplest method on unix is to tail -f the alert log.



    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Sinardy Xing
    Sent: Tuesday, June 26, 2007 9:58 PM
    To: oracle-l
    Subject: Re: Query Database while shutdown in progress



    Hi all,

    I would like to rephrase my question, how do we monitor the progress of
    shutting down immediate mode.

    Thanks.

    On 6/27/07, Sinardy Xing wrote:

    Hi all,

    When we shutdown Oracle instance with shutdown immediate mode. The
    Oracle will wait until all rollback completed.

    The challenge is, how we can login to the instance to check the progress
    of rolling back, assume we are given sysdba privilege.

    Our attempt:
    Login / as sysdba unable to query v$transaction. as shutdown in progress
    not allow query.
    Why Oracle allow to log in as sysdba while nothing can be done in the so
    call session.

    Thanks for sharing your ideas.

    --
    regards,
    Santos

    ------------------------------------------------------------------------------
    NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

    ==============================================================================

    --
    http://www.freelists.org/webpage/oracle-l
  • Hemant K Chitale at Jun 27, 2007 at 3:52 pm
    Your only option would be to open a seperate pre-connected session
    before issuing
    the "SHUTDOWN IMMEDIATE" and then use that pre-connected sesssion to query
    V$TRANSACTION etc.
    At 10:58 AM Wednesday, Sinardy Xing wrote:
    Hi all,

    I would like to rephrase my question, how do we monitor the progress
    of shutting down immediate mode.


    Thanks.



    On 6/27/07, Sinardy Xing
    <oracle.rdbms_at_gmail.com> wrote:
    Hi all,

    When we shutdown Oracle instance with shutdown immediate mode. The
    Oracle will wait until all rollback completed.

    The challenge is, how we can login to the instance to check the
    progress of rolling back, assume we are given sysdba privilege.

    Our attempt:
    Login / as sysdba unable to query v$transaction. as shutdown in
    progress not allow query.
    Why Oracle allow to log in as sysdba while nothing can be done in
    the so call session.


    Thanks for sharing your ideas.

    --
    regards,
    Santos


    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
    and
    http://hemantscribbles.blogspot.com
    and
    http://hemantoracledba.blogspot.com

    "First they ignore you, then they laugh at you, then they fight you,
    then you win" !"
    Mohandas Gandhi Quotes
    : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
  • Jared Still at Jun 27, 2007 at 4:26 pm

    On 6/26/07, Sinardy Xing wrote:
    Hi all,

    When we shutdown Oracle instance with shutdown immediate mode. The Oracle
    will wait until all rollback completed.

    The challenge is, how we can login to the instance to check the progress
    of rolling back, assume we are given sysdba privilege.
    You could always do a startup force.

    This is the same (I think it is anyway) as a shutdown abort/startup.

    The difference is that the rollback takes place in the background after the
    database has opened.

    Long ago this was somewhat problematic as the rollback would be serialized.
    eg. if your rollback would have been in parallel normally, the startup force
    method would
    cause the rollback to be done in a single process, taking much longer to
    complete.

    I haven't looked at that in some time, so I don't know how it behaves now.
    If might be worth
    some reading and experimentation.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Andrew Kerber at Jun 27, 2007 at 5:16 pm
    I dont think a startup force is a good idea. Last time I checked that did a
    shutdown abort followed by a startup.
    On 6/27/07, Jared Still wrote:

    On 6/26/07, Sinardy Xing wrote:

    Hi all,

    When we shutdown Oracle instance with shutdown immediate mode. The
    Oracle will wait until all rollback completed.

    The challenge is, how we can login to the instance to check the progress
    of rolling back, assume we are given sysdba privilege.
    You could always do a startup force.

    This is the same (I think it is anyway) as a shutdown abort/startup.

    The difference is that the rollback takes place in the background after
    the database has opened.

    Long ago this was somewhat problematic as the rollback would be
    serialized.
    eg. if your rollback would have been in parallel normally, the startup
    force method would
    cause the rollback to be done in a single process, taking much longer to
    complete.

    I haven't looked at that in some time, so I don't know how it behaves now.
    If might be worth
    some reading and experimentation.


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 27, 2007 at 5:19 pm
    On 6/27/07, Andrew Kerber wrote:

    >
    I dont think a startup force is a good idea. Last time I checked that did
    a shutdown abort followed by a startup.
    Same old debate.

    What's wrong with shutdown abort?

    Sometimes it is necessary.

    In flight transactions get rolled back either way.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Andrew Kerber at Jun 27, 2007 at 5:43 pm
    Sometime shutdown abort is necessary. However, its not recommended. I
    would rather not risk having to recover a database w/o a good reason. If
    all that is needed is to monitor a shutdown immediate. there are a lot of
    ways to handle that. If nothing else, you can tail the alert log, and see
    the id thats keep the instance form shutting down, then kill that session.
    That would be much safer than an abnormal termination of the instance.
    On 6/27/07, Jared Still wrote:
    On 6/27/07, Andrew Kerber wrote:

    I dont think a startup force is a good idea. Last time I checked that
    did a shutdown abort followed by a startup.

    Same old debate.

    What's wrong with shutdown abort?

    Sometimes it is necessary.

    In flight transactions get rolled back either way.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 27, 2007 at 5:55 pm

    On 6/27/07, Andrew Kerber wrote:
    Sometime shutdown abort is necessary. However, its not recommended. I
    would rather not risk having to recover a database w/o a good reason. If
    all that is needed is to monitor a shutdown immediate. there are a lot of
    ways to handle that. If nothing else, you can tail the alert log, and see
    the id thats keep the instance form shutting down, then kill that session.
    That would be much safer than an abnormal termination of the instance.
    Why is shutdown immediate safer than shutdown abort?

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 27, 2007 at 7:00 pm
    On 6/27/07, Andrew Kerber wrote:
    Sometime shutdown abort is necessary. However, its not recommended. I
    would rather not risk having to recover a database w/o a good reason. If
    all that is needed is to monitor a shutdown immediate. there are a lot of
    ways to handle that. If nothing else, you can tail the alert log, and see
    the id thats keep the instance form shutting down, then kill that session.
    That would be much safer than an abnormal termination of the instance.

    I agree with Jared, whats up with this? Yes, a shutdown abort assures that
    you will be doing crash/instance recovery, but so what? If you are truly
    concerned about that, do a checkpoint before you do the shutdown abort
    (assuming that does not take forever). In what cases will a shutdown require
    media recovery? I know of none unless you are planning on rm'ing a bunch of
    datafiles afterwards.

    One of the beauties of Oracle, and a fact that seems little known, is the
    rule with regards to checkpoints and redo logs. Everyone knows that a redo
    log can not be reused in ARCHIVELOG mode until the archived redo log is
    created. What seems less known is the rule that an online redo log can not
    be reused until all dirty blocks associated with that online redo log have
    been checkpointed and written to the datafiles. Thus, shutdown abort should
    NEVER lead to a media recovery.

    I will say, as a point of caution, that back in the 7.x days, there was a
    bug that I discovered that would cause you to have to do media recovery
    after a shutdown abort. So if you are running 7.x then be careful about a
    shutdown abort.

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1
  • Andrew Kerber at Jun 27, 2007 at 7:12 pm
    The last time I checked, admittedly in the 8.1.7 world, oracle stated that
    you could end up with file corruption on a shutdown abort (note that is
    could, not would, I never had it happen), If they have changed this and now
    say that a shutdown abort is as safe as a shutdown immediate, I have not
    heard it. Does anyone have a statement from oracle to that effect?

    What happens if you have datafiles in backup mode and you try and do a
    startup force?
    On 6/27/07, Robert Freeman wrote:
    On 6/27/07, Andrew Kerber wrote:

    Sometime shutdown abort is necessary. However, its not recommended. I
    would rather not risk having to recover a database w/o a good reason. If
    all that is needed is to monitor a shutdown immediate. there are a lot of
    ways to handle that. If nothing else, you can tail the alert log, and see
    the id thats keep the instance form shutting down, then kill that session.
    That would be much safer than an abnormal termination of the instance.
    I agree with Jared, whats up with this? Yes, a shutdown abort assures that
    you will be doing crash/instance recovery, but so what? If you are truly
    concerned about that, do a checkpoint before you do the shutdown abort
    (assuming that does not take forever). In what cases will a shutdown require
    media recovery? I know of none unless you are planning on rm'ing a bunch of
    datafiles afterwards.

    One of the beauties of Oracle, and a fact that seems little known, is the
    rule with regards to checkpoints and redo logs. Everyone knows that a redo
    log can not be reused in ARCHIVELOG mode until the archived redo log is
    created. What seems less known is the rule that an online redo log can not
    be reused until all dirty blocks associated with that online redo log have
    been checkpointed and written to the datafiles. Thus, shutdown abort should
    NEVER lead to a media recovery.

    I will say, as a point of caution, that back in the 7.x days, there was a
    bug that I discovered that would cause you to have to do media recovery
    after a shutdown abort. So if you are running 7.x then be careful about a
    shutdown abort.

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com
    !
    Sig V1.1


    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Joel.Patterson_at_crowley.com at Jun 27, 2007 at 7:42 pm
    I am in the shutdown immediate camp. Note dbshut/dbstart has abort...
    but then again I think that was for sudden server shutdowns (who would
    do that)?



    However, (Not from Oracle), I have heard oracle has gotten a lot better
    with abort - as in not as dangerous or fearsome. I just like
    synchronizing all the file headers etc.... Does killing the session stop
    the rollback? If not, then monitoring the alert log for the offending
    user is nice but doesn't save very many minutes.



    Ever see a session take awhile to kill?.... rolling back, no?



    The moral might be -- if you are desperate -- Most of the time, the
    shutdown abort will shutdown now (immediately), and the fast rollback,
    roll forward on startup in the background will still allow you to work
    whereas no one can work during an hour shutdown immediate rollback.



    However, the disclaimer is to get some solid background on whether you
    would implement such a procedure as Standard. Most databases don't
    have to shutdown very often anymore... some maintenance is about it.









    Joel Patterson
    Database Administrator
    joel.patterson_at_crowley.com
    x72546
    904 727-2546

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andrew Kerber
    Sent: Wednesday, June 27, 2007 3:13 PM
    To: robertgfreeman_at_yahoo.com
    Cc: oracle-l
    Subject: Re: Query Database while shutdown in progress



    The last time I checked, admittedly in the 8.1.7 world, oracle stated
    that you could end up with file corruption on a shutdown abort (note
    that is could, not would, I never had it happen), If they have changed
    this and now say that a shutdown abort is as safe as a shutdown
    immediate, I have not heard it. Does anyone have a statement from
    oracle to that effect?

    What happens if you have datafiles in backup mode and you try and do a
    startup force?

    On 6/27/07, Robert Freeman wrote:

    On 6/27/07, Andrew Kerber < andrew.kerber_at_gmail.com
    wrote:
    Sometime shutdown abort is necessary. However, its not
    recommended. I would rather not risk having to recover a database w/o a
    good reason. If all that is needed is to monitor a shutdown immediate.
    there are a lot of ways to handle that. If nothing else, you can tail
    the alert log, and see the id thats keep the instance form shutting
    down, then kill that session. That would be much safer than an abnormal
    termination of the instance.



    I agree with Jared, whats up with this? Yes, a shutdown abort assures
    that you will be doing crash/instance recovery, but so what? If you are
    truly concerned about that, do a checkpoint before you do the shutdown
    abort (assuming that does not take forever). In what cases will a
    shutdown require media recovery? I know of none unless you are planning
    on rm'ing a bunch of datafiles afterwards.



    One of the beauties of Oracle, and a fact that seems little known, is
    the rule with regards to checkpoints and redo logs. Everyone knows that
    a redo log can not be reused in ARCHIVELOG mode until the archived redo
    log is created. What seems less known is the rule that an online redo
    log can not be reused until all dirty blocks associated with that online
    redo log have been checkpointed and written to the datafiles. Thus,
    shutdown abort should NEVER lead to a media recovery.



    I will say, as a point of caution, that back in the 7.x days, there was
    a bug that I discovered that would cause you to have to do media
    recovery after a shutdown abort. So if you are running 7.x then be
    careful about a shutdown abort.



    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on
    Amazon.com!
    Sig V1.1



    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 27, 2007 at 8:45 pm

    On 6/27/07, Joel.Patterson_at_crowley.com wrote:
    However, (Not from Oracle), I have heard oracle has gotten a lot better
    with abort � as in not as dangerous or fearsome. I just like synchronizing
    all the file headers etc�. Does killing the session stop the rollback? If
    not, then monitoring the alert log for the offending user is nice but
    doesn't save very many minutes.
    See http://www.speakeasy.org/~jwilton/oracle/shutdown-abort-bad.html

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 27, 2007 at 8:55 pm
    And there is a *very* good point made in that page. If you work in an
    environment where 9 nines is the target, shutdown abort might well be the
    only way to achieve that when you are needing to shutdown the database. A
    shutdown immediate can take FOREVER to run in a number of different
    situations.

    Besides.... if the database isn't going to recover from a shutdown abort, it
    isn't going to recover from having the power pulled on it. Either way, it's
    a BUG, a BAD BAD BUG and should be corrected post-haste.

    One should never make an operational decision (as this is in my opinion)
    without all the facts. This is a discussion that has come up more times that
    I can count. I'm fairly certain you will not be able to find me a recognized
    Oracle expert that agrees with the opinion "Don't do a shutdown abort". I
    believe (and if you all know otherwise let me know) that at various times
    I've seen threads from Tom Kyte, Cary Millsap, Jonathan Lewis and other
    folks well versed on Oracle on this topic... Nay, even should I say experts,
    going back several years.

    Me, I'm just a spurt. No Ex about me.

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Jared Still
    Sent: Wednesday, June 27, 2007 2:46 PM
    To: Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; robertgfreeman_at_yahoo.com;
    oracle-l_at_freelists.org
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Joel.Patterson_at_crowley.com wrote:
    However, (Not from Oracle), I have heard oracle has gotten a lot better
    with abort – as in not as dangerous or fearsome. I just like synchronizing
    all the file headers etc…. Does killing the session stop the rollback? If
    not, then monitoring the alert log for the offending user is nice but
    doesn't save very many minutes.

    See http://www.speakeasy.org/~jwilton/oracle/shutdown-abort-bad.html

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • MacGregor, Ian A. at Jun 27, 2007 at 10:15 pm
    You won't make 9 nines with shutdown abort

    Seconds in a year

    select 365 *24 *60 * 60 from dual;

    365*24*60*60

    31536000

    Amount of downtime allowed for 9 nines

    1* select 31536000 - (31536000 * .999999999) from dual
    SQL> /

    31536000-(31536000*.999999999)

    .031536

    And remember it's supposed to include scheudled maintenance down times.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Robert Freeman
    Sent: Wednesday, June 27, 2007 1:55 PM
    To: Jared Still; Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    And there is a *very* good point made in that page. If you work in an environment where 9 nines is the target, shutdown a
    --
    http://www.freelists.org/webpage/oracle-l
  • Randy Johnson at Jun 28, 2007 at 1:22 am
    Ever hear of RAC?;-)


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of MacGregor, Ian A.
    Sent: Wednesday, June 27, 2007 5:16 PM
    To: robertgfreeman_at_yahoo.com; Jared Still; Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    You won't make 9 nines with shutdown abort

    Seconds in a year

    select 365 *24 *60 * 60 from dual;

    365*24*60*60

    31536000

    Amount of downtime allowed for 9 nines

    1* select 31536000 - (31536000 * .999999999) from dual
    SQL> /

    31536000-(31536000*.999999999)

    .031536

    And remember it's supposed to include scheudled maintenance down times.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robert Freeman
    Sent: Wednesday, June 27, 2007 1:55 PM
    To: Jared Still; Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    And there is a *very* good point made in that page. If you work in an
    environment where 9 nines is the target, shutdown a
    --
    http://www.freelists.org/webpage/oracle-l

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM


    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM


    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 28, 2007 at 1:27 am
    And I really meant 5 nines, not 9.... :-) Of course, that gives me all of
    about 31 seconds...

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Randy Johnson
    Sent: Wednesday, June 27, 2007 7:22 PM
    To: ian_at_slac.stanford.edu; robertgfreeman_at_yahoo.com; 'Jared Still';
    Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    Ever hear of RAC?;-)

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of MacGregor, Ian A.
    Sent: Wednesday, June 27, 2007 5:16 PM
    To: robertgfreeman_at_yahoo.com; Jared Still; Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    You won't make 9 nines with shutdown abort

    Seconds in a year

    select 365 *24 *60 * 60 from dual;

    365*24*60*60

    31536000

    Amount of downtime allowed for 9 nines

    1* select 31536000 - (31536000 * .999999999) from dual
    SQL> /

    31536000-(31536000*.999999999)

    .031536

    And remember it's supposed to include scheudled maintenance down times.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robert Freeman
    Sent: Wednesday, June 27, 2007 1:55 PM
    To: Jared Still; Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    And there is a *very* good point made in that page. If you work in an
    environment where 9 nines is the target, shutdown a
    --
    http://www.freelists.org/webpage/oracle-l

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    --
    http://www.freelists.org/webpage/oracle-l
  • Sinardy Xing at Jun 28, 2007 at 3:06 am
    Hi All,

    I have an idea to achieve the objective need your comments,

    Objective: To report time taken for shutdown immediate to complete,
    while doing rollbacks
    Material to use: DB trigger with before shutdown event, utl output

    CREATE OR REPLACE TRIGGER before_shutdown BEFORE SHUTDOWN ON DATABASE
    BEGIN

    my new challenge is to write this trigger, (oh man... I forgot most of the
    syntax already)
    convert the calculation base on note 117316.1 point 9 with utl output ?
    The report say shutdown in progress and the is used_ublk * db_block_size to
    rollback, base on 1 minute 20 blocks this shutdown progress is estimated to
    required 3 hours to complete.

    END;

    /

    According to Note 117316.1 Point 9
    select used_ublk from v$transaction

    If there is a value there, this is the number of undo blocks used by
    the transaction. Wait one minute and again select "used_ublk" from
    "v$transaction" where ADDR=; .
    Note the value. If it is decreasing, a rollback is occuring and based
    on the difference between these values, you can "guesstimate" the time
    required to complete the rollback. For example, if the first query
    returns a value of 80000 and the second one returns 70000, it
    took 1 minute to rollback 10000 blocks. Based on this number,
    you can guestimate the time to complete the rollback. In this
    case, it would be 7 minutes.

    Will this work ? :)

    Thanks for your feedback
  • Oracle-l-bounce_at_freelists.org at Jun 28, 2007 at 1:31 pm
    Taking the sentence literately puts perspective on it, and as well
    humor.

    But what else will a DBA do when confronted with numbers.

    Keep grinning.

    Joel Patterson
    Database Administrator
    joel.patterson_at_crowley.com
    x72546
    904 727-2546

    -----Original Message-----
    From: MacGregor, Ian A.
    Sent: Wednesday, June 27, 2007 6:16 PM
    To: robertgfreeman_at_yahoo.com; Jared Still; Patterson, Joel
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    You won't make 9 nines with shutdown abort

    Seconds in a year

    select 365 *24 *60 * 60 from dual;

    365*24*60*60

    31536000

    Amount of downtime allowed for 9 nines

    1* select 31536000 - (31536000 * .999999999) from dual
    SQL> /

    31536000-(31536000*.999999999)

    .031536

    And remember it's supposed to include scheudled maintenance down times.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robert Freeman
    Sent: Wednesday, June 27, 2007 1:55 PM
    To: Jared Still; Joel.Patterson_at_crowley.com
    Cc: andrew.kerber_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Query Database while shutdown in progress

    And there is a *very* good point made in that page. If you work in an
    environment where 9 nines is the target, shutdown a

    --
    http://www.freelists.org/webpage/oracle-l
  • Joel.Patterson_at_crowley.com at Jun 28, 2007 at 1:28 pm
    Good page. Just don't like being adamant if I am not very sure.



    Joel Patterson
    Database Administrator
    joel.patterson_at_crowley.com
    x72546
    904 727-2546

    From: Jared Still
    Sent: Wednesday, June 27, 2007 4:46 PM
    To: Patterson, Joel
    Cc: andrew.kerber_at_gmail.com; robertgfreeman_at_yahoo.com;
    oracle-l_at_freelists.org
    Subject: Re: Query Database while shutdown in progress



    On 6/27/07, Joel.Patterson_at_crowley.com
    wrote:

    However, (Not from Oracle), I have heard oracle has gotten a lot
    better with abort - as in not as dangerous or fearsome. I just like
    synchronizing all the file headers etc.... Does killing the session stop
    the rollback? If not, then monitoring the alert log for the offending
    user is nice but doesn't save very many minutes.

    See http://www.speakeasy.org/~jwilton/oracle/shutdown-abort-bad.html

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Jun 27, 2007 at 7:55 pm
    In all the docs I read for 10g (read: certification prep guides),
    shutdown abort is still treated like a leper. I'm pretty surprised by
    this thread so far.

    Don.

    On 6/27/07, Andrew Kerber wrote:

    >
    The last time I checked, admittedly in the 8.1.7 world, oracle stated that
    you could end up with file corruption on a shutdown abort (note that is
    could, not would, I never had it happen), If they have changed this and now
    say that a shutdown abort is as safe as a shutdown immediate, I have not
    heard it. Does anyone have a statement from oracle to that effect?
    --
    Don Seiler
    oracle blog: http://ora.seiler.us
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l
  • Elliott, Patrick at Jun 27, 2007 at 8:19 pm
    I do SHUTDOWN ABORT all the time, and I haven't seen corruption caused
    by it since probably Oracle 7. It is best to issue the "ALTER SYSTEM
    CHECKPOINT" command first, but this may not be possible if the database
    is hung. I have seen many situations where a SHUTDOWN IMMEDIATE simply
    would not complete.

    Pat

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Don Seiler
    Sent: Wednesday, June 27, 2007 2:56 PM
    To: oracle-l
    Subject: Re: Query Database while shutdown in progress

    In all the docs I read for 10g (read: certification prep guides),
    shutdown abort is still treated like a leper. I'm pretty surprised by
    this thread so far.

    Don.

    On 6/27/07, Andrew Kerber wrote:

    >
    The last time I checked, admittedly in the 8.1.7 world, oracle stated
    that you could end up with file corruption on a shutdown abort (note
    that is could, not would, I never had it happen), If they have
    changed this and now say that a shutdown abort is as safe as a
    shutdown immediate, I have not heard it. Does anyone have a statement
    from oracle to that effect?

    --
    Don Seiler
    oracle blog: http://ora.seiler.us
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l

    ___________________________________________________________________________________________________
    CONFIDENTIALITY AND PRIVACY NOTICE
    Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.

    To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Sinardy Xing at Jun 28, 2007 at 3:19 am
    I know 1 team that manage sun system they shutdown the system using "sync;
    sync; init 6;" and already have been like that for few years oracle is
    invisible to them. Power Ranger :)
    On 6/28/07, Elliott, Patrick wrote:


    I do SHUTDOWN ABORT all the time, and I haven't seen corruption caused
    by it since probably Oracle 7. It is best to issue the "ALTER SYSTEM
    CHECKPOINT" command first, but this may not be possible if the database
    is hung. I have seen many situations where a SHUTDOWN IMMEDIATE simply
    would not complete.


    Pat

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Don Seiler
    Sent: Wednesday, June 27, 2007 2:56 PM
    To: oracle-l
    Subject: Re: Query Database while shutdown in progress

    In all the docs I read for 10g (read: certification prep guides),
    shutdown abort is still treated like a leper. I'm pretty surprised by
    this thread so far.

    Don.
    On 6/27/07, Andrew Kerber wrote:

    The last time I checked, admittedly in the 8.1.7 world, oracle stated
    that you could end up with file corruption on a shutdown abort (note
    that is could, not would, I never had it happen), If they have
    changed this and now say that a shutdown abort is as safe as a
    shutdown immediate, I have not heard it. Does anyone have a statement
    from oracle to that effect?

    --
    Don Seiler
    oracle blog: http://ora.seiler.us
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l




    ___________________________________________________________________________________________________
    CONFIDENTIALITY AND PRIVACY NOTICE
    Information transmitted by this email is proprietary to Medtronic and is
    intended for use only by the individual or entity to which it is addressed,
    and may contain information that is private, privileged, confidential or
    exempt from disclosure under applicable law. If you are not the intended
    recipient or it appears that this mail has been forwarded to you without
    proper authority, you are notified that any use or dissemination of this
    information in any manner is strictly prohibited. In such cases, please
    delete this mail from your records.

    To view this notice in other languages you can either select the following
    link or manually copy and paste the link into the address bar of a web
    browser: http://emaildisclaimer.medtronic.com
    --
    http://www.freelists.org/webpage/oracle-l

    --
    regards,

    Sinardy

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Haddon at Jun 28, 2007 at 4:12 am
    an init 6 is the same as a reboot,.. it will execute the K scripts in
    each level from 6 down to 1 and then reboot. If the K##oracle scripts
    have been created in levels 3, or 4 like they should have the database
    will be shutdown cleanly anyway.

    Mike

    Sinardy Xing wrote:
    I know 1 team that manage sun system they shutdown the system using
    "sync; sync; init 6;" and already have been like that for few years
    oracle is invisible to them. Power Ranger :)
    --
    http://www.freelists.org/webpage/oracle-l
  • Andrew Kerber at Jun 28, 2007 at 12:05 pm
    Actually, I wasnt saying dont ever do a shutdown abort. My point is you
    shouldnt do it unless you have to. LIke, when the database is hanging.
    Shutdown immediate works perfectly well almost all the time. Like I said,
    if Oracle says a shutdown abort is now as safe as a shutdown immediate, I
    havent heard it.
    On 6/27/07, Michael Haddon wrote:

    an init 6 is the same as a reboot,.. it will execute the K scripts in
    each level from 6 down to 1 and then reboot. If the K##oracle scripts
    have been created in levels 3, or 4 like they should have the database
    will be shutdown cleanly anyway.

    Mike

    Sinardy Xing wrote:
    I know 1 team that manage sun system they shutdown the system using
    "sync; sync; init 6;" and already have been like that for few years
    oracle is invisible to them. Power Ranger :)

    --
    http://www.freelists.org/webpage/oracle-l

    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Joel.Patterson_at_crowley.com at Jun 28, 2007 at 1:44 pm
    Note that without modifying those K scripts, oracle is doing a shutdown
    abort.

    (I have always modified them for various reasons. Presently we don't
    use them but I modified them drastically to fit my circomstances -- e.g.
    sysadmin can shut down everything without calling me).

    Joel Patterson
    Database Administrator
    joel.patterson_at_crowley.com
    x72546
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Michael Haddon
    Sent: Thursday, June 28, 2007 12:12 AM
    To: Oracle-L_at_Freelists
    Subject: Re: Query Database while shutdown in progress

    an init 6 is the same as a reboot,.. it will execute the K scripts in
    each level from 6 down to 1 and then reboot. If the K##oracle scripts
    have been created in levels 3, or 4 like they should have the database
    will be shutdown cleanly anyway.

    Mike

    Sinardy Xing wrote:
    I know 1 team that manage sun system they shutdown the system using
    "sync; sync; init 6;" and already have been like that for few years
    oracle is invisible to them. Power Ranger :)
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 27, 2007 at 8:42 pm

    On 6/27/07, Andrew Kerber wrote:

    What happens if you have datafiles in backup mode and you try and do a
    startup force?
    select

    'alter database datafile ' || '''' || f.name || '''' || ' end
    backup;'
    from v$datafile f, v$backup b
    where b.status = 'ACTIVE'
    and f.file# = b.file#
    /

    run the generated SQL.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 27, 2007 at 8:48 pm
    Or in 10g you just do an alter database end backup... So, if that
    possibility exists make your startup script:

    startup mount;
    alter database end backup;
    alter database open;

    Viola, no problems!!

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Jared Still
    Sent: Wednesday, June 27, 2007 2:43 PM
    To: andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; oracle-l
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Andrew Kerber wrote:

    What happens if you have datafiles in backup mode and you try and do a
    startup force?

    select

    'alter database datafile ' || '''' || f.name || '''' || ' end
    backup;'
    from v$datafile f, v$backup b
    where b.status = 'ACTIVE'
    and f.file# = b.file#
    /

    run the generated SQL.

    --

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Randy Johnson at Jun 28, 2007 at 1:18 am
    Then the database will say these files require media recovery. Then you, the
    expert DBA that you are, will do an "alter database datafile {filename} end
    backup;" followed by an "alter database open;" and all will be well. :-)


    My general approach to bouncing a database that typically takes a long time
    to shutdown immediate is:

    shutdown abort
    startup restrict
    shutdown immediate

    BTW, unless I'm mistaken, checkpoints occur automatically at the least every
    3 seconds.


    -Randy




    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still
    Sent: Wednesday, June 27, 2007 3:43 PM
    To: andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; oracle-l
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Andrew Kerber "mailto:andrew.kerber_at_gmail.com"andrew.kerber_at_gmail.com> wrote:

    What happens if you have datafiles in backup mode and you try and do a
    startup force?



    select

    'alter database datafile ' || '''' || HYPERLINK
    "http://f.name"f.name || '''' || ' end backup;'
    from v$datafile f, v$backup b
    where b.status = 'ACTIVE'
    and f.file# = b.file#
    /

    run the generated SQL.

    --

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 28, 2007 at 1:24 am
    Of course, there is a checkpoint, and then there is a checkpoint. :-) Crash
    recovery is all about when did the checkpoint complete, not when did it
    begin, correct? :-)

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Randy Johnson
    Sent: Wednesday, June 27, 2007 7:19 PM
    To: jkstill_at_gmail.com; andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; 'oracle-l'
    Subject: RE: Query Database while shutdown in progress

    Then the database will say these files require media recovery. Then you,
    the expert DBA that you are, will do an "alter database datafile {filename}
    end backup;" followed by an "alter database open;" and all will be well.
    :-)

    My general approach to bouncing a database that typically takes a long
    time to shutdown immediate is:

    shutdown abort
    startup restrict
    shutdown immediate

    BTW, unless I'm mistaken, checkpoints occur automatically at the least
    every 3 seconds.

    -Randy

    --
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still
    Sent: Wednesday, June 27, 2007 3:43 PM
    To: andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; oracle-l
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Andrew Kerber wrote:

    What happens if you have datafiles in backup mode and you try and do a
    startup force?

    select

    'alter database datafile ' || '''' || f.name || '''' || ' end
    backup;'
    from v$datafile f, v$backup b
    where b.status = 'ACTIVE'
    and f.file# = b.file#
    /

    run the generated SQL.

    --

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    --
    http://www.freelists.org/webpage/oracle-l
  • Randy Johnson at Jun 28, 2007 at 2:35 am
    Good point.




    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robert Freeman
    Sent: Wednesday, June 27, 2007 8:25 PM
    To: randyjo_at_sbcglobal.net; jkstill_at_gmail.com; andrew.kerber_at_gmail.com
    Cc: 'oracle-l'
    Subject: RE: Query Database while shutdown in progress

    Of course, there is a checkpoint, and then there is a checkpoint. :-) Crash
    recovery is all about when did the checkpoint complete, not when did it
    begin, correct? :-)


    RF


    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Randy Johnson
    Sent: Wednesday, June 27, 2007 7:19 PM
    To: jkstill_at_gmail.com; andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; 'oracle-l'
    Subject: RE: Query Database while shutdown in progress

    Then the database will say these files require media recovery. Then you, the
    expert DBA that you are, will do an "alter database datafile {filename} end
    backup;" followed by an "alter database open;" and all will be well. :-)


    My general approach to bouncing a database that typically takes a long time
    to shutdown immediate is:

    shutdown abort
    startup restrict
    shutdown immediate

    BTW, unless I'm mistaken, checkpoints occur automatically at the least every
    3 seconds.


    -Randy




    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still
    Sent: Wednesday, June 27, 2007 3:43 PM
    To: andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; oracle-l
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Andrew Kerber "mailto:andrew.kerber_at_gmail.com"andrew.kerber_at_gmail.com> wrote:

    What happens if you have datafiles in backup mode and you try and do a
    startup force?



    select

    'alter database datafile ' || '''' || HYPERLINK
    "http://f.name"f.name || '''' || ' end backup;'
    from v$datafile f, v$backup b
    where b.status = 'ACTIVE'
    and f.file# = b.file#
    /

    run the generated SQL.

    --

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/873 - Release Date: 6/26/2007
    11:54 PM

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 28, 2007 at 6:46 pm

    On 6/27/07, Randy Johnson wrote:
    BTW, unless I'm mistaken, checkpoints occur automatically at the least
    every 3 seconds.
    Gee, I hope not.

    LGWR on the other hand... :)

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Randy Johnson at Jun 28, 2007 at 11:29 pm
    Okay, I stand corrected. That's what happens when you reply from memory.


    My bad.



    CHECKPOINT TUNING AND ERROR HANDLING



    What is a Checkpoint?

    A Checkpoint is a database event which synchronizes the modified data
    blocks in memory with the datafiles on disk. It offers Oracle the means for
    ensuring the consistency of data modified by transactions. The mechanism of
    writing modified blocks on disk in Oracle is not synchronized with the
    commit of the corresponding transactions.


    A checkpoint has two purposes: (1) to establish data consistency, and
    (2) enable faster database recovery. How is recovery faster? Because all
    database changes up to the checkpoint have been recorded in the datafiles,
    making it unnecessary to apply redo log entries prior to the checkpoint. The
    checkpoint must ensure that all the modified buffers in the cache are really
    written to the corresponding datafiles to avoid the loss of data

    which may occur with a crash (instance or disk failure).


    Oracle writes the dirty buffers to disk only on certain conditions:


    A shadow process must scan more than one-quarter of the
    db_block_buffer
    parameter.
    Every three seconds. <----<<
    When a checkpoint is produced.

    A checkpoint is realized on five types of events:


    At each switch of the redo log files.
    When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
    When the size in bytes corresponding to :
    (LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
    is written on the current redo log file.
    Directly by the ALTER SYSTEM SWITCH LOGFILE command.
    Directly with the ALTER SYSTEM CHECKPOINT command.

    During a checkpoint the following occurs:

    The database writer (DBWR) writes all modified database
    blocks in the buffer cache back to datafiles,
    Log writer (LGWR) updates both the controlfile and
    the datafiles to indicate when the last checkpoint
    occurred (SCN)

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still
    Sent: Thursday, June 28, 2007 1:46 PM
    To: randyjo_at_sbcglobal.net
    Cc: oracle-l
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Randy Johnson wrote:



    BTW, unless I'm mistaken, checkpoints occur automatically at the least every
    3 seconds.


    Gee, I hope not.

    LGWR on the other hand... :)

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/876 - Release Date: 6/28/2007
    10:56 AM

    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/876 - Release Date: 6/28/2007
    10:56 AM

    --
    http://www.freelists.org/webpage/oracle-l
  • Joel.Patterson_at_crowley.com at Jun 29, 2007 at 11:31 am
    OK, I give... bad morning for a Friday. I'm reading that you said
    every 3 seconds... then you reply back apologizing for replying from
    memory and accompany that with documentation that says every 3 seconds?
    - and use an arrow to highlight the line.





    I definitely cannot walk away missing this, so I apologize if my brain
    hasn't kicked in yet... got a few things happening already this morning.



    Joel Patterson
    Database Administrator
    joel.patterson_at_crowley.com
    x72546
    904 727-2546

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Randy Johnson
    Sent: Thursday, June 28, 2007 7:30 PM
    To: 'oracle-l'
    Subject: RE: Query Database while shutdown in progress



    Okay, I stand corrected. That's what happens when you reply from memory.



    My bad.





    CHECKPOINT TUNING AND ERROR HANDLING



    What is a Checkpoint?

    A Checkpoint is a database event which synchronizes the modified data
    blocks in memory with the datafiles on disk. It offers Oracle the means
    for ensuring the consistency of data modified by transactions. The
    mechanism of writing modified blocks on disk in Oracle is not
    synchronized with the commit of the corresponding transactions.



    A checkpoint has two purposes: (1) to establish data consistency,
    and (2) enable faster database recovery. How is recovery faster?
    Because all database changes up to the checkpoint have been recorded in
    the datafiles, making it unnecessary to apply redo log entries prior to
    the checkpoint. The checkpoint must ensure that all the modified buffers
    in the cache are really written to the corresponding datafiles to avoid
    the loss of data

    which may occur with a crash (instance or disk failure).


    Oracle writes the dirty buffers to disk only on certain conditions:



    A shadow process must scan more than one-quarter of the
    db_block_buffer
    parameter.
    Every three seconds. <----<<
    When a checkpoint is produced.

    A checkpoint is realized on five types of events:



    At each switch of the redo log files.
    When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
    When the size in bytes corresponding to :
    (LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
    is written on the current redo log file.
    Directly by the ALTER SYSTEM SWITCH LOGFILE command.
    Directly with the ALTER SYSTEM CHECKPOINT command.

    During a checkpoint the following occurs:

    The database writer (DBWR) writes all modified database
    blocks in the buffer cache back to datafiles,
    Log writer (LGWR) updates both the controlfile and
    the datafiles to indicate when the last checkpoint
    occurred (SCN)

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still
    Sent: Thursday, June 28, 2007 1:46 PM
    To: randyjo_at_sbcglobal.net
    Cc: oracle-l
    Subject: Re: Query Database while shutdown in progress



    On 6/27/07, Randy Johnson wrote:



    BTW, unless I'm mistaken, checkpoints occur automatically at the least
    every 3 seconds.



    Gee, I hope not.

    LGWR on the other hand... :)

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/876 - Release Date:
    6/28/2007 10:56 AM

    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.476 / Virus Database: 269.9.10/876 - Release Date:
    6/28/2007 10:56 AM

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Haddon at Jun 28, 2007 at 1:20 am
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    I have seen file corruption after a shutdown abort. Granted it was in
    early 8i but it "could" happen. Just think what an abort
    actually is, the processes are killed and all shared memory segments
    and semaphores are released. If a write operation or long running
    checkpoint is in progress you stand a very good chance of file
    corruption.
    Test it out, create a situation that would require some length of time
    to perform a checkpoint, or a rollback and shutdown abort (or crash,
    cause thats what it is) the database.
    Just my .02
    Mike Haddon
    The last time I checked, admittedly in the 8.1.7 world, oracle
    stated that you could end up with file corruption on a shutdown abort
    (note that is could, not would, I never had it happen),  If they
    have changed this and now say that a shutdown abort is as safe as a
    shutdown immediate, I have not heard it.  Does anyone have a
    statement from oracle to that effect?

    What happens if you have datafiles in backup mode and you try and do a
    startup force?


    I agree with
    Jared, whats up with this? Yes, a shutdown abort assures that you will
    be doing crash/instance recovery, but so what? If you are truly
    concerned about that, do a checkpoint before you do the shutdown abort
    (assuming that does not take forever). In what cases will a shutdown
    require media recovery? I know of none unless you are planning on
    rm'ing a bunch of datafiles afterwards.

    One of the
    beauties of Oracle, and a fact that seems little known, is the rule
    with regards to checkpoints and redo logs. Everyone knows that a redo
    log can not be reused in ARCHIVELOG mode until the archived redo log is
    created. What seems less known is the rule that an online redo log can
    not be reused until all dirty blocks associated with that online redo
    log have been checkpointed and written to the datafiles. Thus, shutdown
    abort should NEVER lead to a media recovery.

    'If at first you dont succeed, dont take up skydiving.'
  • JApplewhite_at_austinisd.org at Jun 27, 2007 at 8:28 pm
    Startup Force IS, actually, recommended. Check out the 9i RMan demo file
    case1.rcv in $ORACLE_HOME/rdbms/demo. Also, in your 10g docs, check out
    Backup and Recovery Advanced User's Guide
    ...6 Making Backups with RMAN: Advanced Topics
    ......Backing Up in NOARCHIVELOG Mode: Example

    In both cases Oracle recommends a Startup Force / Shutdown Immediate /
    Startup Mount sequence to get your NoArchivelog database ready for its
    RMan backup.

    Works for us in all our Dev and Test DB RMan backups.

    Jack C. Applewhite - Database Administrator
    Austin (Texas) Independent School District
    512.414.9715 (wk) / 512.935.5929 (pager)

    I'll just sit back in the shade while everyone gets laid.
    That's what I call Intelligent Design. -- God ("Origin of Species": Chris
    Smither)

    "Andrew Kerber"
    Sent by: oracle-l-bounce_at_freelists.org
    06/27/2007 01:31 PM
    Please respond to
    andrew.kerber_at_gmail.com

    To
    "Jared Still"
    cc
    oracle.rdbms_at_gmail.com, oracle-l
    Subject
    Re: Query Database while shutdown in progress

    Sometime shutdown abort is necessary. However, its not recommended. I
    would rather not risk having to recover a database w/o a good reason. If
    all that is needed is to monitor a shutdown immediate. there are a lot of
    ways to handle that. If nothing else, you can tail the alert log, and
    see the id thats keep the instance form shutting down, then kill that
    session. That would be much safer than an abnormal termination of the
    instance.

    On 6/27/07, Jared Still wrote:
    On 6/27/07, Andrew Kerber < andrew.kerber_at_gmail.com> wrote:
    I dont think a startup force is a good idea. Last time I checked that did
    a shutdown abort followed by a startup.

    Same old debate.

    What's wrong with shutdown abort?

    Sometimes it is necessary.

    In flight transactions get rolled back either way.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Baumgartel, Paul at Jun 27, 2007 at 9:24 pm
    Robert,


    Who's "Viola"?;-)


    Paul Baumgartel
    CREDIT SUISSE

    Information Technology
    Securities Processing Databases Americas
    One Madison Avenue
    New York, NY 10010
    USA

    Phone 212.538.1143
    paul.baumgartel_at_credit-suisse.com
    www.credit-suisse.com



    From: oracle-l-bounce_at_freelists.org On Behalf Of Robert Freeman
    Sent: Wednesday, June 27, 2007 4:48 PM
    To: Jared Still; andrew.kerber_at_gmail.com
    Cc: oracle-l
    Subject: RE: Query Database while shutdown in progress

    Or in 10g you just do an alter database end backup... So, if that possibility exists make your startup script:


    startup mount;
    alter database end backup;
    alter database open;


    Viola, no problems!!

    RF



    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Jared Still
    Sent: Wednesday, June 27, 2007 2:43 PM
    To: andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; oracle-l
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Andrew Kerber > wrote:

    What happens if you have datafiles in backup mode and you try and do a startup force?



    select

    'alter database datafile ' || '''' || f.name <http://f.name> || '''' || ' end backup;'
    from v$datafile f, v$backup b
    where b.status = 'ACTIVE'
    and f.file# = b.file#
    /

    run the generated SQL.

    --

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    ==============================================================================
    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
    ==============================================================================

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 28, 2007 at 1:18 am
    BLAH.... Voila!!

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Baumgartel, Paul
    Sent: Wednesday, June 27, 2007 3:24 PM
    To: 'robertgfreeman_at_yahoo.com'
    Cc: oracle-l
    Subject: RE: Query Database while shutdown in progress

    Robert,

    Who's "Viola"?;-)

    Paul Baumgartel
    CREDIT SUISSE

    Information Technology
    Securities Processing Databases Americas
    One Madison Avenue
    New York, NY 10010
    USA

    Phone 212.538.1143
    paul.baumgartel_at_credit-suisse.com
    www.credit-suisse.com

    --
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robert Freeman
    Sent: Wednesday, June 27, 2007 4:48 PM
    To: Jared Still; andrew.kerber_at_gmail.com
    Cc: oracle-l
    Subject: RE: Query Database while shutdown in progress

    Or in 10g you just do an alter database end backup... So, if that
    possibility exists make your startup script:

    startup mount;
    alter database end backup;
    alter database open;

    Viola, no problems!!

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on
    Amazon.com!
    Sig V1.1

    -----Original Message-----
    From: Jared Still
    Sent: Wednesday, June 27, 2007 2:43 PM
    To: andrew.kerber_at_gmail.com
    Cc: robertgfreeman_at_yahoo.com; oracle-l
    Subject: Re: Query Database while shutdown in progress

    On 6/27/07, Andrew Kerber wrote:

    What happens if you have datafiles in backup mode and you try and do a
    startup force?

    select

    'alter database datafile ' || '''' || f.name || '''' || ' end
    backup;'

    from v$datafile f, v$backup b
    where b.status = 'ACTIVE'
    and f.file# = b.file#
    /

    run the generated SQL.

    --

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    ==
    Please access the attached hyperlink for an important electronic
    communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    ==

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

People

Translate

site design / logo © 2022 Grokbase