shutdown abort risk

[BGiulio]

Hello crew,

I'm facing a problem with a process hanging on an Orcle 8.01 db: I killed the process from server command line but in V$SESSION this is still living even if marked for kill. This is since 2 days ago and it seems not to stop; the problem is that such process is consuming db performance, resulting in a slowing down of the activities.
I try the alter system kill session command, but I have there another problem:
in the V$SESSION table, the process is reported having its SID but the SESSION number is changing countinuously and it is not possible to pass this value to the above command.
I tried as a last resource the SHUTDOWN IMMEDIATE but it hangs: after nearby 6 hours I stopped it because necessity of dbase availability.
The only idea I have is to try shutdown abort but my scare is about the event of no remounting of the database: does anyone have an insights about this?

thanks already!!!

[A. Clay Stephenson · Accepted solution]

There are, of course, no absolute certainties in this situation but if you must shutdown, you must. I can tell you that I have never had a problem bringing an Oracle 7 and higher database back up after a shutdown abort. The recovery should be automatic. You should take steps to make certain that the database is as inactive as possible.

It is also possible that a shutdown immediate will leave shared memory segments allocated. You may need to identify those and remove them manually via ipcrm.

[Leif Halvarsson_2]

Hi
I have used shutdown abort several times without problems. Somtimes a Oracle server has stopped due to power failure (with no Oracle shutdown a all) and started again witout problem. It seems as the risk is not so big but Oracle dont recommend using this in other then emergency cases.

[A. Clay Stephenson]

Oops .... I'm an idiot. I should have said:
It is also possible that a shutdown ABORT will leave shared memory segments allocated. You may need to identify those and remove them manually via ipcrm.

[T G Manikandan]

Hello,
When you do a
shutdown end

1.The uncommited transactions are not rolled back.

2.The next startup will require some instance recovery i.e

The SCN is not synchronized between the control and the datafiles.
This instance recovery will be taken care by Oracle itself.

Thanks

[Brian Crabtree]

Performing a 'shutdown end' is not inherantly dangerous.

I have seen this situation before. What I have done in the past, is to 'alter system enable restricted session' for a short period, and kill the session. It should stop it from hopping serial numbers.

Brian

[Duncan Edmonstone]

If you *do* issue a shutdown end , always immediately follow it with:

startup restrict ;
shutdown normal ;

This will give the database a chance to carry out full instance recovery without any users getting access. In the case of large data warehouses this could take a while to roll back uncommitted transactions (how long is a piece of string!)

HTH

Duncan

[Aashish Raj]

Hi,

The serial# is changing because PMON process is doing the process recovery.
If your DB is 8.0.x , then you can increase the value of cleanup_rollback_entries init.ora parameter , which will speed up the transaction recovery by SMON at next startup after shutodwn abort.

Thanks
AR