Oracle 9.2 ORA-01122 problem with UNDOTBS01.DBF

Question

Something went wrong with our backup. What I did

shutting down db

recover the backup

restart db

after that I got this error

ORA-01122: Datenbank-Datei 2 bringt Fehler bei Verifizierungspruefung
ORA-01110: Datendatei 2: 'D:\ORACLE\ORA92ABO\ABO\UNDOTBS01.DBF'
ORA-01207: Datei neuer als Kontrolldatei - alte Kontrolldatei

the controlfile is at C:\oracle ... and the database files are at d:\oracle\ora92abo...

My guess is that the backupprogramm between backing up the d:\oracle files and the c:\controlfile the database restarts. so there is a moment between the backups where the database is running.

Guess thats bad.

I googled that the UNDOTBS01.DBF has something to do with cloning, with we don't use/need at the moment.

EDIT: Details to the backup method

step 1: shutdown via

spool d:\oracle\01shutdon.log
connect / AS SYSDBA
shutdown immediate
exit

step2: datatransfer

backup the database folder with syncback to NAS backup the controlfile with xcopy to NAS

step3: restart

spool d:\oracle\02startup.log
connect / AS SYSDBA
startup
exit

Answer 1

OK here is what to do. Obviously substitute in your own values here.

1. delete the UNDOTBS DBF from your disk. You still have a backup so it's OK.
2. sqlplus / as sysdba
3. startup
4. It will complain about the missing DBF, don't worry
5. alter system set undo_management = manual scope=spfile;
6. shutdown and startup again
7. alter database datafile 'D:\ORACLE\ORA92ABO\ABO\UNDOTBS01.DBF' offline drop;
8. alter database open;
9. drop tablespace undotbs;
10. Recreate the UNDO tablespace. You DO need it.
11. shutdown and startup again

You really really need to get hold of and read some RMAN documentation...

Answer 2

Firstly - when you did the restore, did you also restore the controlfile? I know it's obvious but check anyway.

In Oracle, there is a thing call the SCN, system control number. Every time you do a COMMIT, the SCN is advanced by 1. The SCN for the database is held in the header of each DBF and in the controlfile. What this error means is that your UNDO tablespace (cloning??? no it's to do with rolling back - undoing - a transaction) datafiles have a higher SCN than the control file. That is to say, the controlfile is older than the DBFs.

How exactly have you been doing the backups? Is it with RMAN? Or have you been copying the DBFs with the database running...? The good news is that if you have a good backup, there will be no in-flight transactions in there that you need to worry about.

It may be possible for you to workaround this by one of two means. Firstly, you could just keep on trying to start it - every start attempt increases the SCN by 1, so eventually the controlfile will pass the DBF. This may take a while. Or you can try recreate the controlfile. Find another Oracle, one that works, and do ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'c:\temp\mycontrolfile.ctl'; Edit that file with the DB name and the new locations of your DBFs, startup and run that script and it will generate a new controlfile. Then you should be able to to an OPEN RESETLOGS.

HOWEVER if your backup is bad there is nothing you can do.