SQL>
startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF'
Cause :-
I checked the following location( C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU) and found out that the file PRABHU01.dbf was not present.
Solution :-
There are two solutions to the above problem.
1. If data in this file is not that much important that remove this file information from database and open your database.
2. If data in this file is important and you have backup available for this file and if your database is in archivelog mode than you can recover the data in this file upto the last commit.But if database is in NOARCHIVELOG mode then restoration of data possible only upto the last backup.
Solution 1 :- Make the file Offline and then open your database after that drop that tablespace.
SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF'
Cause :-
I checked the following location( C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU) and found out that the file PRABHU01.dbf was not present.
Solution :-
There are two solutions to the above problem.
1. If data in this file is not that much important that remove this file information from database and open your database.
2. If data in this file is important and you have backup available for this file and if your database is in archivelog mode than you can recover the data in this file upto the last commit.But if database is in NOARCHIVELOG mode then restoration of data possible only upto the last backup.
Solution 1 :- Make the file Offline and then open your database after that drop that tablespace.
SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;
FILE# FILE_NAME
TABLESPACE_NAME
STATUS
----------
-----------------------------------------------------------------------------------
-----------------------------------------
------------------
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSTEM01.DBF
SYSTEM
SYSTEM
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\UNDOTBS01.DBF
UNDOTBS1
ONLINE
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSAUX01.DBF
SYSAUX
ONLINE
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\USERS01.DBF
USERS
ONLINE
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF
PRABHU
ONLINE
SQL> drop tablespace PRABHU including contents and datafiles;
drop tablespace PRABHU including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF'
offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME
,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;
FILE# FILE_NAME
TABLESPACE_NAME
STATUS
----------
-----------------------------------------------------------------------------------
-----------------------------------------
------------------
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSTEM01.DBF
SYSTEM
SYSTEM
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\UNDOTBS01.DBF
UNDOTBS1
ONLINE
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSAUX01.DBF
SYSAUX
ONLINE
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\USERS01.DBF
USERS
ONLINE
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF
PRABHU
OFFLINE
SQL> drop tablespace PRABHU including contents and datafiles;
Tablespace dropped.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
Solution 2 :- Restore and recover the lost file through RMAN
C:\Documents and Settings\prabhu.subburaj>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 26
12:43:18 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRABHU (DBID=2171747492, not open)
RMAN> list backup of datafile 5;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 152.00K DISK
00:00:23 26-FEB-13
BP Key: 19 Status: AVAILABLE
Compressed: YES Tag: TAG20130226T123606
Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 1028343 26-FEB-13
C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF
RMAN> restore datafile 5 preview;
Starting restore at 26-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 152.00K DISK
00:00:23 26-FEB-13
BP Key: 19 Status: AVAILABLE
Compressed: YES Tag: TAG20130226T123606
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 1028343 26-FEB-13
C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------ ---------------
26 2.50K DISK
00:00:01 26-FEB-13
BP Key: 26 Status: AVAILABLE
Compressed: YES Tag: TAG20130226T123711
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_ANNNN_TAG20130226T123711_8LRQS0JY_.BKP
List of Archived Logs in backup set 26
Thrd Seq Low SCN Low Time Next
SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 20 1028338
26-FEB-13 1028364 26-FEB-13
Media recovery start SCN is 1028343
Recovery must be done beyond SCN 1028343 to clear data files fuzziness
Finished restore at 26-FEB-13
RMAN> restore datafile 5;
Starting restore at 26-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
tag=TAG20130226T123606
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 26-FEB-13
RMAN> recover datafile 5 ;
Starting recover at 26-FEB-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:01:02
Finished recover at 26-FEB-13
RMAN> exit
Recovery Manager complete.
C:\Documents and Settings\prabhu.subburaj>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 12:46:12 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
Database altered.
SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME
,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;
FILE# FILE_NAME
TABLESPACE_NAME
STATUS
----------
-----------------------------------------------------------------------------------
-----------------------------------------
------------------
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSTEM01.DBF
SYSTEM
SYSTEM
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\UNDOTBS01.DBF
UNDOTBS1
ONLINE
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSAUX01.DBF
SYSAUX
ONLINE
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\USERS01.DBF
USERS
ONLINE
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF
PRABHU
ONLINE
NOTE :- If database is in NOARCHIVELOG mode and you want to
restore and recover your lost file, then after restoration of file from backup
if recovery is possible through the redo logs currently available then you can
open your database but if while in the process of recovery it asks for archive
logs then you cannot open your database, you have to restore your
full database from the backup to open your database. For Recovery upto last
commit keep your database in ARCHIVELOG mode.
I hope this article helped you.
No comments:
Post a Comment