I am playing wiht Oracle 9i DB wir RH 7.2 and while testing recovery procedure I have taken an online backup (using trace file) and data file backup. I was aborting and starting the db again to check the point in time recovery. However I got the following error and I could not open the daabase at all.
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/opt/oracle/product/9.0.1/oradata/kendb/system01.dbf'
I searched the help and founfd that the datafile has to be restored from recent backups. I did that as I had already taken online backup (I dont have any old backup). I copied the system01.dbf to the original database and then and again started the database and got the same error again.
I did try the incomplete recovery but it is not working as database cannot be opened at all. What are various ways to recover it?
If I need to recreate the entire database again then how to do it?
The problem is you have opened the database with resetlogs and don't have a backup of the control file. Assuming you took a backup after you opened with resetlogs and you're in archivelog mode, you can recover by recreating your control file using the output in the trace directory.
Thanks a lot. It worked. I modified the create controlfile code on trace file with RESETLOGS in it and controlfile ws created successfully.
However the next command 'RECOVER DATABASE' failed as it wanted the option 'USING BACKUP CONTROLFILE' . So I used the same option in following way
'RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE'
But this also didnt work and I got the following error
ORA-00279: change 7174247 generated at 08/12/2002 22:56:36 needed for thread 1
ORA-00289: suggestion : /usr/local/localamol/oradata/archivelog/_20.arc
ORA-00280: change 7174247 for thread 1 is in sequence #20
I did give the file name but it didnt work and again gave the same error with new file name. So Again I shutdown the database completely and just started in normal mode. It asked for the NORESETLOGS or RESETLOGS. And then I opened using RESETLOGS and it worked.
The DB is working fine now. But I would like to know why the problem occurred while RECOVER DATABASE command was used.