Re: Query Database while shutdown in progress
- From: "Sinardy Xing" <oracle.rdbms@xxxxxxxxx>
- To: randyjo@xxxxxxxxxxxxx
- Date: Thu, 28 Jun 2007 11:06:34 +0800
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:
