Re: Query Database while shutdown in progress

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=<value from TADDR in v$session>; .
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

Other related posts: