Re: Terminate rollback?
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: brian.s.wisniewski@xxxxxxxxxxxx
- Date: Thu, 2 Apr 2009 21:04:28 -0500
If you can't restart the instance, you can still perform this using
following method:
oradebug setospid <smonpid>
oradebug event 10513 trace name context forever, level 2
<find the process and kill OS process (not just alter system kill)>
you should be able to drop the table now..
Enable smon recovery again.
oradebug event 10513 trace name context off
I haven't tested this method though :-(. But, this method should work too..
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
On Thu, Apr 2, 2009 at 6:25 PM, Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx
> wrote:
> Brian
> You may be able to use this method:
>
> 1. Kill the instance with shutdown abort.
> 2. Set event 10513 to disable smon transaction rollback. Since the
> instance is dead, transaction must be rolled back by smon. 10513 disable
> smon rollback.
> startup nomount;
> alter system set events '10513 trace name context forever, level 2'
> scope=memory;
> alter database mount;
> alter database open;
> 3. After opening database, drop the table..
> drop table t1;
> 4. Now, make sure event is off.. This is a very *critical* step.
> alter system set events '10513 trace name context off';
> or just restart instance..
> show parameter event
>
> I just tested this in 11g and should work in 10g too..Search for smon 10513
> in metalink and you can read more information about this event.
>
> Good luck!
>
> PS: I wouldn't try this method in production though..
>
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
>
> Original message:
> =============
>
> On Thu, Apr 2, 2009 at 11:29 AM, Brian S Wisniewski <
> brian.s.wisniewski@xxxxxxxxxxxx> wrote:
> 10.2.0.4 DEVELOPMENT database
>
> Is there any way (oracle events) to stop a rollback that is in-flight? I
> want to drop the object that the rollback is occurring against and start
> over. The rollback that is in-flight is going to run for hours and I'd
> rather not wait, if possible. This is against a development database so I'm
> not concerned about using undocumented processes if anyone has one
> available. I looked through the oracle events but didn't find one that
> stood out.
>
>> Thanks - Brian
>>
>> ------------------------------
>>
>
>
Other related posts:
