Christian is of course correct that an extra logical member would force the
idiot or saboteur to delete a second file.

The question posed, however, is NP-incomplete. Any preventative measure
suggested can ultimately be defeated by a priviledged account when the point
is attempting to destroy a database. Concoct a defense and tell me what it
is and I posit an account with authority to defeat it.

Why not just delete all the backups and all the online datafiles? Reductio
absurdum.

Now perhaps the OP is inclined to focus on the assurance that recoverability
is assured as long as the commit reaches the online redo log.

That is of course subject to the assurance that the log continues to exist
long enough for it to be archived or for the transactional changes
represented to be written to the database files. That realization caused
Oracle to create the option of multiple members in redo log groups (before
duplexing and multiplexing spinning rust was routinely available) as part of
the ongoing campaign in conjunction with the VLDB to plug all single points
of failure in the early to mid 1990's.

No computer system can ever be secured against destruction by illicit or
stupid actions from a priviledged account; that single point of failure
remains and cannot be completely cured.

Additional layers can be implemented against accidental attempts:

1) "rm" in the path of priviledged accounts can refuse to function on files
with open links (preferably reporting them, which should slap even a
nincompoop awake when the link holder is LGWR)
2) "rm" in the path of priviledged accounts can refuse to function on files
containing the name fraction "redo" or "log".
3) Much more baroque attachments can be made to the "rm" command including
perling it up to actually query all the running databases on the machine to
verify that the file target is not an unarchived redo log member. At some
point this gets paranoid even for a DBA.

And of course you have to provide an alternate name to actually whack such
files from priviledged accounts, and if it becomes the default command used
routinely the purpose is defeated. Likewise if someone escalates to the
"i_really_mean_it_whack_the_file" command after rm warns them, ... well you
know. (Notice that the excessively long command name, which I usually abhor,
is a good idea in this case to help prevent it from becoming routine).

As I have written: The problem is NP-incomplete. Oracle is very, very
recoverable, but nothing can ultimately succeed past "delete whatever you
like while I'm running."

Regards,

mwf

-----Original Message-----
<snip>

> I've run into this situation before as well. It happens,
> people accidentally delete files.
>
> What would be interesting to research, is if you still
> can somehow recover those files.

Mhmm... Among other things it's exactly for such situations that we
should put multiple members in a group. If you have another member, just
do a "alter system switch logfile" and copy it where it has been
deleted...


Cheers,
Chris
<snip>



--
http://www.freelists.org/webpage/oracle-l