Patchwork Psychology

A developer asked me the other day, “Do we have backups of the [insert website name here] database? I accidentally issued an SQL UPDATE statement without a WHERE clause and overwrote a bunch of data.”
“And you didn’t make an export before you started working on it, did you?”, I replied, admonishingly. The answer was obvious.

Well of course we back the database up. We back up the transaction logs and the compressed tablespace files. But it won’t be as simple as “rolling back” his changes. Maybe it could be, but I don’t know how to do it–I’m a sysadmin, not a DBA. The only thing that we have available is a complete cold backup of all of the tablespace files and related things–basically whatever our DBA (who is working at a client right now) said we needed to do a full restoration of the database. My hope is that I can take the tablespace datafile of the particular schema and pop it in.

Ok, so first I SSH to the database server I’m going to restore on and get my restore file copied there. I su - to my dba account and sqlplus /nolog, then connect / as sysdba. Now I run the SQL that should take the affected schema offline:

ALTER TABLESPACE [ts_name] OFFLINE IMMEDIATE;

Now I quite sqlplus and copy my restore file in, after decompressing it and making a backup copy of the original file. The next step is to recover the tablespace:

RECOVER TABLESPACE [ts_name];

Unfortunately, I get this error:

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 58: '/u02/oradata/[db_name]/[ts_name].dbf'
ORA-01122: database file 58 failed verification check
ORA-01110: data file 58: '/u02/oradata/[db_name]/[ts_name].dbf'
ORA-01251: Unknown File Header Version read for file number 58

So I give up and email the DBA. She says she doesn’t think it’s possible to restore one tablespace to a time earlier than the rest of the database. A little more research shows this to be accurate. Without some significant hacking, there are only two ways available to me (now): either find an export of the data or restore the entire database to a new database and export the data. There may have been other options like flashback if the mistake had been handled within minutes of occurring.

Let’s hope the bolloxed data isn’t important, eh?