incomplete tablespace recovery (Oracle 9i, Win) ...

[sysadv]

Hi,

I'd like to recover an older tablespace (without touching any redolog). The following procedure seems to be appropriate:

1. alter tablespace xxx offline;
2. recover tablespace xxx until cancel;
2b. Canceling immediately.
3. alter tablespace xxx online;

There is no need to mention, that it doesn't work, isn't it? "Recover tablespace" doesn't seem to feel familiar with an option like "until cancel". Does anybody have an idea, how to do it right?

Your sincerely Sysadv

 

[SantaMufasa]

Sysadv,

To clarify, Oracle's "Recover tablespace...until cancel" means that you can recover a tablespace, one redo log at a time, until you type in the response, "CANCEL", at the recovery process's prompt.

So, as an example, let's presume that I need to recover a table that I incorrectly DROPped at 13:21 yesterday. Let's also presume that I have checked my archive redo log files and determined that log switch #7234 occurred at 13:18 yesterday.

I restore my backup copy of my tablespace data files, I do a "STARTUP MOUNT", then a "RECOVER TABLESPACE YADA UNTIL CANCEL". Oracle begins the recovery process. After Oracle applies each archive redo log file, the recovery process pauses and asks whether I wish to continue (<RET>) or CANCEL. I respond with an [Enter] key to continue applying my redo log files until the recovery process applies archive redo log file #7234. At that point, instead of responding with an [Enter], I respond with the word, "CANCEL[Enter]", which tells the recovery process to stop at that point.

I then issue the command to reset my log files, and an "ALTER DATABASE OPEN;". I confirm that my missing table now exists again. I export the table. (And then, if I choose to do so, I can restore an up-to-the-minute-current copy of my database, and import the previously missing table from the export of that table that I created moments ago.)

There may be many different scenarios that justify a "CANCEL-based" recovery, but the above example is one of those scenarios that may give you a vision for how/why that type of recovery might be useful.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sysadv]

Hi SantaMufasa,

much thanks for your response. Your scenario sounds quite familiar to me. And if you consider your table being deleted shortly after the backup was made ... then you might come to the conclusion of restoring your tablespace-files only. Not redoing later transactions.

My case is similar. I'm in trouble restoring a tablespace without any later redologs available. But it's ok. The data within the tablespace seems to be proper for the moment. But I can't convince "oracle" to accept the tablespace only.

Bye Sysadv

 

[SantaMufasa]

Then, similar to my scenario, above, you many need to do a CANCEL-based recovery for the entire database (to restore what you need), export the recovered item, then blow away the partially recovered database, then do an up-to-the-minute-current recovery, followed by an import of the recovered item.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sysadv]

Hi,

much thanks for your responses. Perhaps I should give you some more details about my current problem. I recently got in trouble with a buggy sql-script. One tablespace was influenced, that means every single bit of data has been erased. The oracle-instance was working and still available (open). We are running an online-backup and an oracle-export every day. Unfortunately no archived redolog file is available, neither at the server nor at the backup tapes. I do only have the chance to restore, control files and every dbf necessary for every single day of the last 4 weeks ... but no archived redolog. That usually ends up in the following state, oracle wants to recover the system tablespace. By the way, it would be of help anyway to recover the whole instance, but ... read yourself. The process is as follows: recreation of the database via dbca, recovering the old pwdsid.ora, the control files and the system- and non-system-dbfs. "startup mount" works fine, "alter database open resetlogs" results in the wish to recover the tablespace system01.dbf. If I could assist with archive redolog-files, there would be no problem. Thus I try to "recover database using backup controlfile until CANCEL". Having in mind to cancel the recovery, before it could actually begin. But it doesn't help. In addition I unfortunately had to figure out, that every export-dump (stored at the backup tapes) is faulty, that means incomplete. Neither a partial import nor a full import is possible.

In my view we've got one possible solution. Recovering the working instance (with an emptied tablespace), reintegrating an older version of the tablespace ... but how?

Bye sysadv

 

[karluk]

You are extremely close to being unrecoverable. There is no way to do a recovery from an online backup without at least one archive log file. That's because the online backup allows updates to happen while the data files are being backed up. That means that the data files aren't in a consistent state when you restore them and try to open the database. You will always get the "datafile 1 needs media recovery" error.

As it happens, I once was in the exact situation you find yourself in. (It's a long story.) Oracle support told me to add the undocumented parameter "_allow_resetlogs_corruption=true" to my init.ora file, and then try opening the database with the resetlogs option. That doesn't always work, but if it does, you should then immediately take an export of your data and import it to a new database. Oracle won't provide ongoing support for a database which has been opened with the _allow_resetlogs_corruption parameter. It is strictly a last gasp try at getting your data back before giving up and moving to Siberia.