Data File recovery is normal scenario of Dba Activities. Many a times it happens that we take any Data File Offline and when I try to makeit Online we get following error:
ALTER DATABASE DATAFILE 4 ONLINE;
Error starting at line 27 in command:
ALTER DATABASE DATAFILE 4 ONLINE
Error report:
SQL Error: ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF'
ORA-01200: actual file size of 640 is smaller than correct size of 960 blocks
01122. 00000 - "database file %s failed verification check"
*Cause: The information in this file is inconsistent with information
from the control file. See accompanying message for reason.
*Action: Make certain that the db files and control files are the correct
files for this database.
In above scenario , My Database is in Archive log and it is 10.2 release. Problem is once I take any DBF offline and try to make it Online again, It asks for Media Recovery. The reason for asking MR is the change in SCN. SCN is changed between time of Offline and time to take DBF Online again. And once the SCN is changed, to bring effected DBF we need to provide Archived Changed Vector and any changes residing in Online REDO log files.
However! In this case nothing to worry about. Because I have my database backup intact. All I need to do is Restore and Recover Data file number 4.
Before we proceed further take Datafile 4 Offline and remove it from HDD or take it to another part of HDD.
Although! Datafile number 4 was offline, I could not cut/paste it from its location to any other location. If we miss to do this, than while restoring the DBF, we get “DBF already in use error and Restore operation fails”. In case you face error on deleting it from OS level than you have to take DB down for just to remover DBF from its default location. Once we are done with these steps then remaining part of Restore/Recover is just the same.
RMAN> RUN
2> {
3> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT 'D:\BKUP\DG_FILES\SEED_%U.BKP';
4> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
5> RESTORE DATAFILE 4;
6> }
allocated channel: CH1
channel CH1: sid=137 devtype=DISK
sql statement: ALTER DATABASE DATAFILE 4 OFFLINE
Starting restore at 01-AUG-11
channel CH1: starting datafile backupset restore
channel CH1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF
channel CH1: reading from backup piece D:\BKUP\DG_FILES\SEED_DG_1DMIB1F7_1_1.BKP
channel CH1: restored backup piece 1
piece handle=D:\BKUP\DG_FILES\SEED_DG_1DMIB1F7_1_1.BKP tag=TAG20110725T141711
channel CH1: restore complete, elapsed time: 00:00:04
Finished restore at 01-AUG-11
released channel: CH1
RMAN> Recover datafile 4;
Starting recover at 01-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=135 devtype=DISK
starting media recovery
archive log thread 1 sequence 13 is already on disk as file D:\BKUP\SEED\SEED_ARC_00013_0756558498_001.ARC
archive log thread 1 sequence 14 is already on disk as file D:\BKUP\SEED\SEED_ARC_00014_0756558498_001.ARC
archive log thread 1 sequence 15 is already on disk as file D:\BKUP\SEED\SEED_ARC_00015_0756558498_001.ARC
archive log thread 1 sequence 16 is already on disk as file D:\BKUP\SEED\SEED_ARC_00016_0756558498_001.ARC
archive log thread 1 sequence 17 is already on disk as file D:\BKUP\SEED\SEED_ARC_00017_0756558498_001.ARC
archive log thread 1 sequence 18 is already on disk as file D:\BKUP\SEED\SEED_ARC_00018_0756558498_001.ARC
archive log thread 1 sequence 19 is already on disk as file D:\BKUP\SEED\SEED_ARC_00019_0756558498_001.ARC
archive log thread 1 sequence 20 is already on disk as file D:\BKUP\SEED\SEED_ARC_00020_0756558498_001.ARC
archive log thread 1 sequence 21 is already on disk as file D:\BKUP\SEED\SEED_ARC_00021_0756558498_001.ARC
archive log thread 1 sequence 22 is already on disk as file D:\BKUP\SEED\SEED_ARC_00022_0756558498_001.ARC
archive log thread 1 sequence 23 is already on disk as file D:\BKUP\SEED\SEED_ARC_00023_0756558498_001.ARC
archive log thread 1 sequence 24 is already on disk as file D:\BKUP\SEED\SEED_ARC_00024_0756558498_001.ARC
archive log thread 1 sequence 25 is already on disk as file D:\BKUP\SEED\SEED_ARC_00025_0756558498_001.ARC
archive log thread 1 sequence 26 is already on disk as file D:\BKUP\SEED\SEED_ARC_00026_0756558498_001.ARC
archive log thread 1 sequence 27 is already on disk as file D:\BKUP\SEED\SEED_ARC_00027_0756558498_001.ARC
archive log thread 1 sequence 28 is already on disk as file D:\BKUP\SEED\SEED_ARC_00028_0756558498_001.ARC
archive log thread 1 sequence 29 is already on disk as file D:\BKUP\SEED\SEED_ARC_00029_0756558498_001.ARC
archive log thread 1 sequence 30 is already on disk as file D:\BKUP\SEED\SEED_ARC_00030_0756558498_001.ARC
archive log thread 1 sequence 31 is already on disk as file D:\BKUP\SEED\SEED_ARC_00031_0756558498_001.ARC
archive log thread 1 sequence 32 is already on disk as file D:\BKUP\SEED\SEED_ARC_00032_0756558498_001.ARC
archive log thread 1 sequence 33 is already on disk as file D:\BKUP\SEED\SEED_ARC_00033_0756558498_001.ARC
archive log thread 1 sequence 34 is already on disk as file D:\BKUP\SEED\SEED_ARC_00034_0756558498_001.ARC
archive log thread 1 sequence 35 is already on disk as file D:\BKUP\SEED\SEED_ARC_00035_0756558498_001.ARC
archive log thread 1 sequence 36 is already on disk as file D:\BKUP\SEED\SEED_ARC_00036_0756558498_001.ARC
archive log filename=D:\BKUP\SEED\SEED_ARC_00013_0756558498_001.ARC thread=1 sequence=13
archive log filename=D:\BKUP\SEED\SEED_ARC_00014_0756558498_001.ARC thread=1 sequence=14
archive log filename=D:\BKUP\SEED\SEED_ARC_00015_0756558498_001.ARC thread=1 sequence=15
archive log filename=D:\BKUP\SEED\SEED_ARC_00016_0756558498_001.ARC thread=1 sequence=16
archive log filename=D:\BKUP\SEED\SEED_ARC_00017_0756558498_001.ARC thread=1 sequence=17
archive log filename=D:\BKUP\SEED\SEED_ARC_00018_0756558498_001.ARC thread=1 sequence=18
archive log filename=D:\BKUP\SEED\SEED_ARC_00019_0756558498_001.ARC thread=1 sequence=19
archive log filename=D:\BKUP\SEED\SEED_ARC_00020_0756558498_001.ARC thread=1 sequence=20
archive log filename=D:\BKUP\SEED\SEED_ARC_00021_0756558498_001.ARC thread=1 sequence=21
archive log filename=D:\BKUP\SEED\SEED_ARC_00022_0756558498_001.ARC thread=1 sequence=22
archive log filename=D:\BKUP\SEED\SEED_ARC_00023_0756558498_001.ARC thread=1 sequence=23
archive log filename=D:\BKUP\SEED\SEED_ARC_00024_0756558498_001.ARC thread=1 sequence=24
archive log filename=D:\BKUP\SEED\SEED_ARC_00025_0756558498_001.ARC thread=1 sequence=25
archive log filename=D:\BKUP\SEED\SEED_ARC_00026_0756558498_001.ARC thread=1 sequence=26
archive log filename=D:\BKUP\SEED\SEED_ARC_00027_0756558498_001.ARC thread=1 sequence=27
archive log filename=D:\BKUP\SEED\SEED_ARC_00028_0756558498_001.ARC thread=1 sequence=28
archive log filename=D:\BKUP\SEED\SEED_ARC_00029_0756558498_001.ARC thread=1 sequence=29
archive log filename=D:\BKUP\SEED\SEED_ARC_00030_0756558498_001.ARC thread=1 sequence=30
archive log filename=D:\BKUP\SEED\SEED_ARC_00031_0756558498_001.ARC thread=1 sequence=31
archive log filename=D:\BKUP\SEED\SEED_ARC_00032_0756558498_001.ARC thread=1 sequence=32
archive log filename=D:\BKUP\SEED\SEED_ARC_00033_0756558498_001.ARC thread=1 sequence=33
archive log filename=D:\BKUP\SEED\SEED_ARC_00034_0756558498_001.ARC thread=1 sequence=34
media recovery complete, elapsed time: 00:00:14
Finished recover at 01-AUG-11
RMAN>
Alter database data ile 4 online;
After this just take backup of Data File or if possible full database backup.
No comments:
Post a Comment