28 | 03 | 2024
Latest Articles
Popular Articles

RMAN

Recover Offline Datafile

User Rating:  / 0
PoorBest 

This morning I lost a lot of time in the traffic jams. There were a lot of additional traffic jams because of heavy rain, wind and the ever going on road works. I got a bit depressed since these days the news is always about increasing government deficits, raising unemployment.


I came rather late in our office and checked my email. Since we setup Oracle Enterpise Manager 10G we get emails whenever there is an alert with one of the 500 databases for which we are responsable. We receive also emails when backup jobs are succesful or not. But today among the many hundreds emails our GRID sent to me, I discover this one.

 

Target Name=PALLADIUM

Target Type=Database Instance
Host=PALLADIUM01.our-company.com
Metric=datafiles_need_recovery
Metric Value=1
Timestamp=Jun 10, 2009 2:29:32 AM EDT
Severity=Critical
Message=Number of datafiles need media recovery is 1.

Notification Rule Name=OUR_DB_PROD_NOTIFICATION_RULE
Notification Rule Owner=OUR_SYSMAN
Notification Count=1

 

This is an interesting one. A datafile went offline at least this is what the GRID claims. That' s indeed a critical alert. Offline datafiles need recovery and we need archived redo logs ... otherwise we are in trouble. Let' s login with a terminal server session.

 

C:\Documents and Settings\myself>SET ORACLE_SID=PALLADIUM
C:\Documents and Settings\myself>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 10 03:00:59 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the OLAP and Data Mining options
SQL> select log_mode from v$database;
LOG_MODE


ARCHIVELOG


SQL> select * from v$recover_file;
     FILE# ONLINE  ONLINE_


ERROR                                                                CHANGE#


TIME


        14 OFFLINE OFFLINE
                                                                  8.8166E+12
10-JUN-09


SQL> exit  

OK, we have indeed an offline datafile and the database operates in archive log mode. Since I know we have a general rule to configure an RMAN recovery window of 3 days I should be able to recover this datafile easily.

It was indeed easy.
 

C:\oracle\product\10.2.0\db_1\BIN>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jun 10 03:02:49 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: PALLADIUM (DBID=1420861292)
RMAN> recover datafile 14;
Starting recover at 10-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=132 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 10-JUN-09
RMAN> sql "alter database datafile 14 online";
sql statement: alter database datafile 14 online


 
It is very wise to look at the alert file when these kind of events occur.
 


Wed Jun 10 02:25:04 2009
KCF: write/open error block=0x76f99 online=1
     file=14 D:\ORACLE\ORADATA\PALLADIUM\ED_PM_08.DBF
     error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 14: D:\ORACLE\ORADATA\PALLADIUM\ED_PM_08.DBF
Wed Jun 10 03:03:06 2009
alter database recover datafile list clear
Wed Jun 10 03:03:06 2009
Completed: alter database recover datafile list clear
Wed Jun 10 03:03:06 2009
alter database recover if needed
 datafile 14

Media Recovery Start
 parallel recovery started with 3 processes
Wed Jun 10 03:03:07 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 34680 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PALLADIUM\REDO03.LOG
  Mem# 1 errs 0: G:\ORACLE\ORADATA\PALLADIUM\REDO03A.LOG
Completed: alter database recover if needed
 datafile 14
Wed Jun 10 03:03:23 2009
alter database datafile 14 online
Wed Jun 10 03:03:23 2009
Completed: alter database datafile 14 online

 

I did a good job and it was really very easy. What have we learnt : "USE RMAN"