RE: REDO LOG Concepts (intentional or stupid authoritative destruction of a database)

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