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

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

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...

  • at command 5 i get the error that the database is not opened. ORA-01109 – wurlog Oct 31 '10 at 22:07
  • Well, it's not opened, because of step 4. But it should still let you alter the SPFILE. Then you can continue with step 6. – Gaius Oct 31 '10 at 22:12
  • i think step 9 should be '9. drop tablespace undotsbs1;' right? – wurlog Nov 1 '10 at 15:09
  • Yep sorry, typo! – Gaius Nov 1 '10 at 16:15

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.

  • do i need the undo tablespace? if the rest of the database is valid i wont need it. how can i get rid of it? – wurlog Oct 31 '10 at 20:33
  • When you say copying, what exactly do you do? This is important. Are you just copying the files? Is the database running while you copy the files? If so, have you set HOTBACKUP mode? Are you also backing up the archivelogs? – Gaius Oct 31 '10 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.