Tech babble was a blog I began loooong back and then abandoned it in favor of blogger. here is the old link, in case you wanna take a look! http://o3.indiatimes.com/techbabble
Saturday, February 19, 2005
Learning Experience from a recovery scenario……
In the recent past, I found myself in a recovery scenario… Recoveries, although few and far between in todays day and age of excellent hardware and support services, definitely test the mettle of your backup as well as your DBA 🙂
There was a database running n version 8i Rel 3 (817) on a UNIX box, where a 30 + gig tablespace was to be dropped and the space was to be reused for another database/tablespace on the same box. And accidentally, two tablespaces instead of 1 got dropped!
The drop command took ages to execute. It was run almost 3-4 times and then cancelled after letting it execute for more than 10+ hrs.
Lesson learnt: If a drop tablespace command takes time, allow it to take whatever sweet time it wants to, until it says something to you …in the alert.log.
After cancelling the command for the 5th time, we brought the tablespace offline and ran an OFFLINE DROP on each of the associated datafiles. Once done, we deleted the physical files were deleted.
Now the tablespace that was not supposed to be dropped also was forcefully murdered by bringing it offline; running offline drop and then physically removing files. When this accident was realized, we attempted restoring the files back to original location (of the 2nd tablespace) but we were unsuccessful. The reason was that there was no space on the server. But the obvious question that came to mind was that if a file was removed and is now being restored back, then it should atleast have that much space…
lesson learnt: Oracle does not release space back to the OS even when you run an “rm” command at the OS level. So we had to bringdown oracle and re-attempt restore, which happend now painlessly.
Once the restore was over we tried to recover the tablespace and the recovered successfully by applying the archivelogs.
……to be continued — (i am also in the process of upgrading local MS office software, which is asking me to shut this browser) 😦
Monday 21 Feb, 2005 – PART II (Now I am back at my desk and all the upgrades are over, so I can continue where I left off last week.)
Now were back to getting the original tablespace out of the way. The current situation was that the it was OFFLINE and the datafiles were “OFFLINE DROPPED“ and physically deleted. Now the surprising part was that the file listing still showed in the controlfile of the database and were in “recover“ mode.
we again tried the drop command.
Lesson Learnt: when a drop tablespace command is issued, Oracle clears entries in the SYS.FET$ table first and since during the growth of that tablespace, extents would have been allocated left right and centre, clearing up of the SYS.FET$ is what takes all the time. Once this is cleared, only then the controlfile is updated with the removal information and then tablespace is removed. This took a long time and finally happend.
This whole excersise was a very exiting one because almost all the people were used to this command executing quickly, at max taking a couple of hours. But this time it took a lot longer than that…
But finally the tablespace was dropped and recreated with a smaller size. and everybody breathed a sigh of relief….