Google it ....

Showing posts with label ORA-01194. Show all posts
Showing posts with label ORA-01194. Show all posts

Wednesday, January 29, 2014

WARNING! Recovering data file from a fuzzy .. file ORA-01194: file needs more recovery to be consistent

Oracle Release - 10.2.0.5.0,  platform - HP-UX (64-bit)

problem - Disk damaged on physical standby and dozens of data files were lost.  Instance was down and archive log gap presented between standby and primary databases.

Instead of restore from backup we just copied (fast way) online data files from primary and started applying missing logs.

At the start of apply process we saw warnings as follow in the standby alert log telling that we are supplying incorrect files.
WARNING! Recovering data file 291 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 529 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 530 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command. ...

Later, after all logs applied successfully we tried to open standby in read only mode:
SQL> alter database open read only ;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01194: file 291 needs more recovery to be consistent
ORA-01110: data file 291 ...

Although all logs applied and dbs are synchronized there was a problem with the data file in our warning list - 291.
Of course it indicates that files should have been transferred in backup mode.

Apparently, we found all relevant tablespaces and put them into backup mode:
ALTER TABLESPACE TS1 BEGIN BACKUP;
ALETR TABLESPACE TS2 BEGIN BACKUP;

As transfer finished we took them out from backup mode.
ALTER TABLESPACE TS1 END BACKUP;
ALETR TABLESPACE TS2 END BACKUP;

It is all, after applying all logs we could successfully open standby in read only mode.

Thanks,