Re: Database Recovery failing

pmahale_at_yahoo.com (Prasad Mahale) wrote in message news:<44a6df49.0305201255.13076694_at_posting.google.com>...
> Hi,
> I am doing an OCP and as a practice, I am trying to backup and recover
> the database. Here is what i did -
> 1) I made the database as noarchivelog and created few tables and
> loaded with the data.
> 2) Later i shutdown the database and copied only the USER_DATA.DBF
> datafile to some other backup directory (Yes, only the datafile and
> not the controlfile).
> 3) Started the database and deleted all the rows from the table.
> 4) Switched the logs 5/6 times to erase the log entries.
> 5) Shutdown the database and restored datafile from the backup
> directory.
> 6) Startup nomount
> 7) recover database
> 8) see below
>
> SQL> alter database open
> ERROR at line 1:
> ORA-01589: must use RESETLOGS or NORESETLOGS option for database
> open
>
> SQL> alter database open resetlogs;
> alter database open resetlogs
> *
> ERROR at line 1:
> ORA-01152: file 1 was not restored from a sufficiently old backup
> ORA-01110: data file 1: 'C:\ORACLE\ORADATA\MYDB9I\SYSTEM01.DBF'
>
> SQL> alter database open noresetlogs;
> alter database open noresetlogs
> *
> ERROR at line 1:
> ORA-01588: must use RESETLOGS option for database open
>
>
> Looks like my controlfile and datafile are out of sync. Am I doing
> something silly? WHat do i need to do?
>
> Thanks in advance.

Prasad, since your db is in noarchivelog mode that means to make a proper backup you must shut the database down and copy the database and control files using an OS copy utility.

This also means in the event of media failure that your only recovery option is to recover ALL database data files so that the entire database is at one point in time. When you restore all database files then you need to reset the online redo logs since they no longer apply.

This is exactly what the error messages you are getting are telling you. Looking at the Database Error Message manual descriptions for these errors should clue you in to this, at least if you consider them in relation to what I have said.

There is a Backup and Recovery Manual whose first three or four chapters should give you a pretty good idea of what is necessary to do in order to successfully backup and recover your database.

HTH -- Mark D Powell -- Received on Wed May 21 2003 - 16:10:50 CEST