Database open fails after online backup recovery

[Former Member]

Hi Friends

We are trying to set up an additional server using the online backup of our DEV server. We have been following SAP Note 549828 for the same. Having restored the online backup, the open database failed.

To resolve the same, in accordance to SAP Note 549828, we created a backup control file with success using the command

create controlfile reuse set database DEV resetlogs noarchivelog

However on issuance of the command

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

we run into an error as to

ORA-00279: change 794638222 generated at 10/25/2007 12:43:20 needed for thread 1

ORA-00289: suggestion : /oracle/DEV/oraarch/DEVarch1_9766.dbf

ORA-00280: change 794638222 for thread 1 is in sequence #9766

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/oracle/DEV/oraarch/DEVarch1_9766.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/DEV/sapdata1/system_1/system.data1'

We even manually copied the file system.data1 from the source to the target server but to no avail.

Also the SQL command

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE

displays a different change# for system.data1 while it is showing same number for all other datafiles.

Please advice at the earliest as we are struck. Points awaiting their master.

Regards

Lokesh Gupta

[former_member204746 · Accepted solution]

oh man...

first, do not copy system.data1 from other system... not a good idea... your problem is that you need to papply some redo logs to make datafile consistent.

second, you restored from an online backup, you need offline redologs and the first that is needed is file oracle/DEV/oraarch/DEVarch1_9766.dbf

so, go get that file on source system or on backup and place it at oracle/DEV/oraarch

then, try your command again until you at least pass the date of end of backup... so, you may need more than 1 redolog file to complete the recovery.

[Former Member]

The solutions given have been very close and we were able to open the database after copying certain other offline redo logs. However, the question which still rose was that all the offline redo logs that were present at the time of backup were restored and no others were left. As this was a system copy, i could get the next few offline redo logs from source server. However, in case of crash, this wud not help. However, i guess in such situation, one can execute command ALTER DATABASE OPEN UNTIL CANCEL USING BACKUP CONTROLFILE and give cancel after the last redo log file has reached, which in my opinion should work.

Anyways, Thanks a ton guys. Keep up the good work.

[former_member204746]

I have hit the exact problem with a non-SAP Oracle database yesterday.

an great article explain why:

http://www.ardentperf.com/2007/05/09/controlfile-recovery-requires-resetlogs/

basically, your control files are not in-sync with redo logs becasue you also renamed the SID.

the solution I found:

SQL> <b>recover database using backup controlfile;</b>

ORA-00279: change 783890 generated at 05/09/2007 13:11:59 needed for thread 1

ORA-00289: suggestion :

/u04/oracle/product/10.2.0/db_1/dbs/arch1_6_621627183.dbf

ORA-00280: change 783890 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

<b>/oracle/QAS/mirrlogA/LOG_G11M1.DBF</b>

Log applied.

Media recovery complete.

if this does not work, try all online redologs 1 by one until you find the one that was needed.

then,:

alter database open resetlogs;

[Former Member]

Some inputs addition to Erics comments

The problem is you dont have archives ie Offline redo logfiles in correct location.

/oracle/DEV/oraarch/DEVarch1_9766.dbf Here DEVarch1_9766 is archive file which is misssing from location /oracle/DEV/oraarch. To recover DB you need Archives generated during hot backup.

Generally these steps willl give you desired result.

select * from v$logfile;

We normally switch the log files to the number of log groups that exist

alter system switch logfile;

create a Backup directory to hold our hot back datafiles and Archives

When the backup is complete check the backup location to see if all the files are available. We could now either FTP the same to the other system or copy over these files to another location in case of cloning on the same system.

Copy over all the files to their respective filesystems and directories and then edit the file that was created using the backup controlfile to trace. Copy that file to the remote system and edit it accordingly.

check that all the files are in the right location and edit that information in the control file

Once the controlfile is run successfully and you get the statement processed, we can start applying the archive logs that we have moved to the archive log destination directory as per the init<sid>.ora file.

do a recover of the database to its consistent state

recover database using backup controlfile until cancel;

The create control file command only changes the structure of the database and the SID name, the header of the datafiles still hold all the required information. The above command would ask you to input the archive log file names one by one to do recovery or you could choose the AUTO option. Once the recovery process is complete, open the database with the resetlogs option

Regards

Vinod