The very first thing I'd try is copying the surviving control file then restarting the DB - the loss of the controlfile is what caused the DB to crash, and if the redo log group happened to be INACTIVE at the time, then I'm pretty much OK. I'd find this out from the alert log. So long as there is a disk in the right place, I can issue CLEAR LOGFILE and they will just be recreated.
If not then we have a problem - the SCN in the controlfile is ahead of the DBFs and we've no way to get there. We have no choice but to perform a point-in-time-recovery, and we have to discard those DBFs as well. In RMAN, restore database (assuming controlfile autobackup), recover until the highest SCN in the archived redo logs, then OPEN RESETLOGS. Since I have the cumulative incremental, I don't need the differential incremental (I'm not sure why you'd mix the two types? Also why if we had several disks for redo why we'd put an entire group on one disk rather than multiplexing all groups).
(There is probably a much better answer involving flashback, but I have to confess that I have only used explicitly created flash restore points so far, never run through a DR scenario).
N.B. This has been editied since its original posting.
You may want to look at duplicating the database to a new name provided you have all the required backups and archivelogs to restore to the required point in time.
I am assuming that ASM is involved here, if not you might need to look at the duplicate command to see how to rename the files as it's done automatically by ASM.
If your using standard os files rather than ASM then you might need to include this in your duplicate statement once you've connected to the atarget and auxiliary databases (see below);
run
{
SET NEWNAME FOR DATAFILE 1 TO '/oradata/instance/file1.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oradata/instance/file1.dbf';
<repeat for all the files in your Database>
DUPLICATE TARGET DATABASE to newdb;
}
Now the theory should be;
Create a new pfile (from the existing database and rename the various references to the database), making sure you change any log destinations and the directories exist
Start the new instance in a no mount state
Then start
rman
Connect target sys@instance
Connect auxiliary /
Once the two connections are up.
The duplicate command to restore to a point in time should be as follows
DUPLICATE TARGET DATABASE TO newdb UNTIL TIME TO_DATE('20/07/2011 19:45:00','DD/MM/YYYY HH24:MI:SS')
-- This should be prior to when you created the Test table so the database will be restored upto that point in time. It theory at least.
another option i do find useful is to resize the logfile and groups
DUPLICATE TARGET DATABASE TO newdb
LOGFILE
GROUP 1 ('/oradata/instance/redo01a.log',
'/oradata/instance/redo01b.log') SIZE 200K REUSE,
GROUP 2 ('/oradata/instance/redo02a.log',
'/oradata/instance/redo02b.log') SIZE 200K REUSE;
or with ASM something like
DUPLICATE TARGET DATABASE TO newdb
LOGFILE
GROUP 1 ('+DATA',
'+FRA') SIZE 200K REUSE,
GROUP 2 ('+DATA',
'+FRA') SIZE 200K REUSE;
There are several options you can also specify, I believe one is to an scn, sequence or until a date time. You might need to do a to_date convertion for the last option. putting these together should help you get what you're after.
Best Answer
Presumably there was data in them at one point, which has since been deleted.
If the corrupted blocks are in free space, there is no need to take any action. Oracle will reformat the block when it attempts to use the space. So you can ignore the alerts.
If you want to clear the corrupt blocks to prevent more alerts, create a nologging table in the relevant tablespace. Insert a lot of rows until the corrupt blocks disappear, then drop the table.