System datafile lost:-- In this scenario,Hardware failure (Media failure) causes loss of System01.dbf file. And when DBA tries to start the database gets following error:
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 176163716 bytes
Database Buffers 427819008 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF'
Now! To see any message/note regarding this we can check DBWR trace file located at
D:\oracle\product\10.2.0\admin\seed\bdump location. The naming convention of trace file is like
SID_DBWR_<srno>.trc. This trace file contains following message:
Dump file d:\oracle\product\10.2.0\admin\seed\bdump\seed_dbw0_1100.trc
Dump file d:\oracle\product\10.2.0\admin\seed\bdump\seed_dbw0_1100.trc
Tue Nov 15 15:43:10 2011
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 2 - type 586, 1 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:552M/2037M, Ph+PgF:1134M/3935M, VA:1302M/2047M
Instance name: seed
Redo thread mounted by this instance: 1
Oracle process number: 5
Windows thread id: 1100, image: ORACLE.EXE (DBW0)
*** SERVICE NAME:() 2011-11-15 15:43:10.613
*** SESSION ID:(167.1) 2011-11-15 15:43:10.613
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Recovery step in this scenario are:
1:- Take DBF offline;
2:- Restore DBF from Backup;
3:- Recover datafile;
4:- Take DBF online.
Before we proceed with Recovery, We check the existence of backup of Datafile 1(System01.dbf).
C:\ rman target / catalog rman_user/rmRecovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 15 16:56:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SEED (DBID=848131167, not open)
connected to recovery catalog databasean_user@seed_82
RMAN> list backup of datafile 1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1248 Full 134.63M DISK 00:00:41 15-NOV-11
BP Key: 1249 Status: AVAILABLE Compressed: YES Tag: FULL_DB_BKUP
Piece Name: D:\ORACLE\RMAN_BKUP\SEED\SEED_%R_01MRNTAO.BKP
List of Datafiles in backup set 1248
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1150785 15-NOV-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF
Now! We can see that this particular backupset can be used for Recovery of System data file.
Step1:- Take System01.dbf (1) offline
RMAN> Sql "Alter Database Datafile 1 Offline";
sql statement: Alter Database Datafile 1 Offline
Step 2:- Restore Datafile 1 from backup
RMAN> Restore datafile 1;
Starting restore at 15-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\RMAN_BKUP\SEED\SEED_%R_01MRNTAO.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\RMAN_BKUP\SEED\SEED_%R_01MRNTAO.BKP tag=FULL_DB_BKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 15-NOV-11
Step 3:- Recover the datafile
RMAN> Recover datafile 1;
Starting recover at 15-NOV-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-NOV-11
Step 4:- Take datafile online
RMAN> Sql "Alter database datafile 1 online";
sql statement: Alter database datafile 1 online
Note:- The timing of this recovery varies and depends upon the DB size,Number of Archive REDO log to apply.
Any comment/Suggestion will be welcome.
No comments:
Post a Comment