Recovery from missing or corrupted datafile
Scenario: (When you take cold backup means consistent backup)
If your database is running in archive log mode and you take cold backup means consistent backup (all data file, control file and redo log file) in every Sunday and Monday to Saturday you take only backup of archive log file.
Recovery from missing or corrupted datafile(s):
Case 1:
You have Sunday cold backup and you have also Monday to Wednesday archive log file backup. Suppose any data file corrupt or missed on Thursday, how will you recover database up to Wednesday.
*
When u start database by using startup command system show following error:
SQL> startup
ORACLE instance started.
Total System Global Area 122755896 bytes
Fixed Size 453432 bytes
Variable Size 67108864 bytes
Database Buffers 54525952 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘C:OORADATASYSTEM.DBF’
* Read DBWR trace file or alert log file and find details of missing data files. Restore missing files from backup storage area by using OS Copy command and try to open database by using alter database open command.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘C:OORADATASYSTEM.DBF’
(Error means data file 1 needs media recovery)
*
Recover database by using following syntax:
SQL> recover datafile 1;
ORA-00279: change 222132 generated at 06/02/2006 10:41:42 needed for thread 1
ORA-00289: suggestion : C:OADMINARCHARC00100052
ORA-00280: change 222132 for thread 1 is in sequence #52
Note : Specify log: {
AUTO
Log applied.
Media recovery complete.
*
Open databae:
SQL> alter database open;
Your database recovered up to Wednesday.
Responses