Re: DBA Experiences from the edge - Programmer deleted datafiles he thought wasn't in necessary anymore.

Andreas Sheriff wrote:
> 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 Developer
>
>
> "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

First thought, isn't this what test systems are all about? Letting someone learn, have some fun, wake up some brain cells, get the blood pressure pumping.

But this becomes a little old when the other thing you noted was "they want the system back available as soon as possible".

If it is at all important for the developers or qa people, it needs to be backed up with some periodic frequency and then (if you let people hack away between backups) with the disclaimer that you might have to recover it back to the last available backup. Received on Tue Jun 21 2005 - 19:17:45 CDT