« February 2007 | Main | October 2007 »
March 23, 2007
Performing Tablespace Recovery on 10gR2
Yesterday and today I've been working on the intricacies of restoring a tablespace. This is an interesting concept in that it should be possible to recover a messed up tablespace without bringing the database down. In other words, someone could seriously mess up their own area (tablespace) and we could recover it for them without disturbing anyone else. This would be a key skill for effective management of our new production database.
Like most things Oracle, this isn't as easy as it sounds. Every time I would take a tablespace offline and try a recovery on it, the datafile for that tablespace would also require recovery. I'm not yet sure why this is happening, but once the datafile was recovered, then sure enough, I could recover and open the tablespace to the current time.
Another thing, I had to recover dropped tables using flashback. They didn't come back with the tablespace recovery to a prior point in time. This must be tied to the required recovery of the datafile.
Bottom line is that I was able to recover just one tablespace. Using flashback, I could replace any dropped tables as well. This is yet another benefit of going to 10gR2 as our production database.
Posted by rossm at 10:53 AM | Comments (0)
March 7, 2007
Update on 10gR2
Well, I did come back from Hawaii, although it sure was a fun trip. Snorkeling was really fun, and we saw lots of fish and turtles. Very nice!
No moss has grown on me, though, since my return. I've torn right in to the 10gR2 upgrade again. Travis created a new snapshot of our "TENG" test database. I dropped a couple tables, then Travis replaced the existing db with his snapshot. To make a long story short, the database came back up with the dropped tables restored.
I ran in to one interesting problem, and a lot of uninteresting ones, too. The db was throwing an ORA-01102 error on startup. It wouldn't mount the db. An excellent article in MetaLink (Note:1034037.6) explained how to look for existing memory segments and semaphores. I followed the directions for removing the old segments and semaphores and the db started right up.
What this all means is that we are in a position to restore TENG using one of Travis' snapshots. The db will just open once the snapshot has been applied. So, if we destroy the db in testing, it can be replaced in a matter of a few minutes - no need to completely reinstall. This is not a complete restore and recovery process - yet - because we will lose all data from the time of the snapshot. OK for the test db, not good enough for production. The next thing to do is to begin work on a good backup and recovery strategy. Also, we need to get some version of 9i-AERO on our test box.
Posted by rossm at 2:07 PM | Comments (0)