Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> DBA Experiences from the edge - Programmer deleted datafiles he thought wasn't in necessary anymore.
Ok,
I know I said my last post was my last post, and the post before that was my last post, but this; this post is my last post for sure!
Also I know files and permissions to the database should be given sparingly to those who know what they're doing, but this is an exceptional case where the programmer has access to root, the datafiles, and the ability to log into the database as sysdba through DBA group permissions.
As these things happen, I'd like to share my experiences just in case someone else is faced with this situation.
Here are the specifics:
Oracle 8.1.5i (don't laugh) database on a Solaris 2.8 machine with 1 proc
and 3/4 gig memory. This is all esoteric.
The programmer created a new tablespace with new datafiles for his application and determined that the old datafiles were no longer necessary, so he deleted them from the filesystem. This happened about a week ago.
This is just a test database that can be rebuilt if an error occurs so I
don't pay too much attention to it. My main focus is on the production
system.
Today, the programmer decides to restart the Solaris box and to his dismay,
the database wasn't starting.
"I restarted {machinename} and got this error. I don't know what
happened...
"And I want to use the test database to demonstrate something right now,"
cried the programmer (It's always right now, isn't it?).
Upon inspection of the error message and the alert log, I determined the
problem was that some datafiles were missing. The programmer confirmed that
those datafiles were non longer being used, so he deleted them.
With a sigh I went to work, taking time here and there to chide the programmer.
Me: "STARTUP MOUNT" Me: "DROP TABLESPACE ts_name INCLUDING CONTENTS CASCADE CONSTRAINTS;" DB: "Database not open."
"What?!?!" I asked.
Me: "ALTER TABLESAPCE ts_name OFFLINE;"
DB: "Database not open."
I double as an extra programmer, so my mind at that time wasn't fully as a
DBA.
I pondered. "The only way to drop a tablespace is if the database were
open? I guess that makes sense. The DB needs to check to see if the TS is
empty or if there are any constraints against it."
I sent the programmer back to his desk because he was breathing over my
shoulder.
"And I can't take the tablespace offline either, because the DB has to be
open for the same reason."
I didn't have my books with me, so I started intuitively going over the
Oracle architecture.
"What about taking the datafiles offline? 'Can't see why not..."
So that's what I did for all four datafiles that were deleted. Me: "ALTER DATABASE DATAFILE df# OFFLINE;"
Then I opened the database, dropped the suspect tablespaces, bounced the database, made sure that the datafiles *really* weren't being used, then finally removed them with rm.
So what's the moral of this story?
BACKUP, BACKUP, BACKUP (preferably with RMAN and incremental backups + archivelogs) and monitor your databases, even if it's just a test or development database. If I had the database backed up I could have simply issued a series of RMAN commands to restore and recover the missing datafiles and that would be the end of that, until the programmer decided to delete the "unnecessary datafiles" again...
-- Andreas Oracle 9i Certified Professional Oracle 10g Certified Professional Oracle 9i Certified PL/SQL DeveloperReceived on Tue Jun 21 2005 - 18:58:56 CDT
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
--- WARNING: DO NOT REPLY TO THIS EMAIL Reply to me only on this newsgroup