Recover the Datafile without any prior backup

Today I planned to test “Recover the datafile without prior backup”

Currently there is no failure in database. I shutdown the database & removed the below datafile in OS level “D:\ORACLE\APP\ORADATA\CHE\TEST.DBF”.

Note:
1.Database must be in archive log mode.
2.We need all the archive log files from lost datafile created.

1.Login into che database

C:\>set oracle_sid=CHE

SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 19 14:21:03 2013

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 226495588 bytes
Database Buffers 301989888 bytes
Redo Buffers 5828608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: 'D:\ORACLE\APP\ORADATA\CHE\TEST.DBF'

SQL> alter database datafile 9 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

FILE# ERROR TIME
---------- ----------------------------------- ---------
9 FILE NOT FOUND

SQL> select file#,status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 RECOVER

9 rows selected.

We need to recover the datafile 9.

SQL> alter database create datafile 'D:\ORACLE\APP\ORADATA\CHE\TEST.DBF';

Database altered.

2.Recover the datafile without backup
C:\>set oracle_sid=che

RMAN> connect target /

Recovery Manager: Release 11.1.0.7.0 - Production on Thu May 19 13:45:17 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to target database: CHE (DBID=3460503574)

RMAN> recover datafile 9;

Starting recover at 19-MAY-133
Using channel ORA_DISK_1

Starting media recovery

archived log for thread 1 with sequence 30 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_30_5Z7CTYPG_.ARC
archived log for thread 1 with sequence 31 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_31_5Z7CV1QG_.ARC
archived log for thread 1 with sequence 32 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_32_5Z7CV7OZ_.ARC
archived log for thread 1 with sequence 33 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_33_5Z7CV9KL_.ARC
archived log for thread 1 with sequence 34 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_34_5Z7D4FOH_.ARC
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
19\O1_MF_1_30_5Z7CTYPG_.ARC thread=1 sequence=30
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
19\O1_MF_1_31_5Z7CV1QG_.ARC thread=1 sequence=31
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
19\O1_MF_1_32_5Z7CV7OZ_.ARC thread=1 sequence=32
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-MAY-13

3. Verify the database

SQL> alter database datafile 9 online;

Database altered.

SQL> select file#,error,time from v$recover_file;

no rows selected

SQL> select file#, status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE

Is it Possible to 'DROP' a Datafile from a Tablespace?

In fact Oracle does not provide an interface for dropping datafiles like you could drop a schema and its object in case of table, a view, a user, etc. Once you add a datafile in the tablespace then the datafile cannot be removed but in some case you need do it then you can perform some work to find closer results.

How to Deal different scenario Need to remove Datafile:

Select file_name, tablespace_name from dba_data_files where tablespace_name ='SDH_TIMS_DBF';

FILE_NAME                                 TABLESPACE_NAME

------------------------------------      ---------------

D:\ORACLE\ORADATA\MANOJ\SDH_TIMS01.DBF   SDH_TIMS_DBF

D:\ORACLE\ORADATA\MANOJ\SDH_TIMS02.DBF   SDH_TIMS_DBF

If the datafile you want to remove is the only datafile in that tablespace then simply drop the entire tablespace:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;‎

Note: Before performing certain operations such as taking tablespaces/datafiles offline, and trying to drop them, ensure you have a full backup.

The DROP TABLESPACE command removes the tablespace, the datafiles, and its contents from data dictionary.  Oracle will no longer have access to ANY object that was contained in this tablespace.  The physical datafile must then be removed using an operating system command (Oracle NEVER physically removes any datafiles). 

If you have more than one datafile in the tablespace and you want to keep the objects of first datafile then you must export all the objects you want to keep then Drop the tablespace.

Select owner,segment_name,segment_type from dba_segments where tablespace_name='<name ‎of tablespace>'‎

Note: Make sure you specify the tablespace name in capital letters.

OWNER	SEGMENT_NAME	SEGMENT_TYPE
TIMS	GEN_BUYER_OPEN_BALANCE	TABLE
TIMS	GEN_BUYER_PROFILE	TABLE
TIMS	GEN_BUYER_STATEMENT	TABLE
TIMS	GEN_COMPANY_QUANTITY_TYPE	TABLE
TIMS	GEN_CONTRACT_PROFILE	TABLE
TIMS	GEN_CONTRACT_WH_LOCATIONS	TABLE
TIMS	GEN_DEPOSIT_CU	TABLE
TIMS	GEN_DEPOSIT_INSTALLMENT	TABLE
TIMS	STK_ITEM_STATEMENT	TABLE PARTITION
TIMS	USR_SMAN_SALESMAN_FK_I	INDEX
TIMS	AG_DTL_PK	INDEX
TIMS	AG_DTL_AGING_FK_I	INDEX

Now Re-create the tablespace with the desired datafiles then import the objects into that tablespace. 

If you just added the datafile and Oracle has not yet allocated any space within this datafile, then you can resize to make the datafile smaller than 5 Oracle blocks.  If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.

ALTER DATABASE DATAFILE <filename> RESIZE;

Here we are not including the OFFLINE DROP command because is not meant to allow you to remove a datafile.

ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP;

ALTER DATABASE DATAFILE <datafile name> OFFLINE;  --in archivelog mod

What the above command really means is that you are offlining the datafile with the intention of dropping the tablespace. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace.  This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup. The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile...

Oracle Data Recovery Advisor 11g new feature

Data recovery advisor is one of the oracle 11g new features.

The Data Recovery Advisor tool can assist with diagnosing media failures, making recommendations and providing the RMAN commands to resolve the problem.

Data Recovery advisor we have used three commands.

1.List failure
2.Advice failure
3.Repair failure

How it works?

1.List failure: 

 V$diag_info)àThe Data Recovery Advisor stores its information outside the database in the Automatic Diagnostic Repository (ADR -

If you find any problem in database and Data recovery advisor is not reporting about
failure, you can proactively validate the database health check running the below RMAN command.

RMAN> validate database;

RMAN> list failure;

If there is any failure in database, it will be recorded the information in ADR. Once information recorded into ADR, we can use the advice failure & repair failure.

2.Advice failure: 

It gives advice how to recover the problems detected by data recovery advisor.

It provides Manual checklist option & Automated repair options depend upon our problems.

Manual checklist option: Advice to manually handle the issue.

Automated repair options: Provides the repair script for a problem

RMAN> advice failure;

3.Repair failure: 

While we execute the below command, it will recover the problem automatically.
No need any manual work.

RMAN> repair failure;

I planned to test the below scenario in my test server.

I will remove a datafile in OS level & test how data recovery advisor works?

Environment Details:

Operating system: Windows XP service pack 2

Database Version: 11.1.0.7 (32 bit)

Source database name: che

1. Login into che database

C:\>set oracle_sid=che

RMAN> connect target /
Recovery Manager: Release 11.1.0.7.0 - Production on Thu May 19 13:45:17 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> list failure;

No failures found that match specification

RMAN> backup validate check logical database;

Starting backup at 19-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\ORACLE\APP\ORADATA\CHE\SYSTEM01.DBF
input datafile file number=00002 name=D:\ORACLE\APP\ORADATA\CHE\SYSAUX01.DBF
input datafile file number=00005 name=D:\ORACLE\APP\ORADATA\CHE\EXAMPLE01.DBF
input datafile file number=00006 name=D:\ORACLE\APP\ORADATA\CHE\RAJA_DATA01.DBF
input datafile file number=00008 name=D:\ORACLE\APP\ORADATA\CHE\TEST_DATA01.DBF
input datafile file number=00003 name=D:\ORACLE\APP\ORADATA\CHE\UNDOTBS01.DBF
input datafile file number=00007 name=D:\ORACLE\APP\ORADATA\CHE\RAJA_INDX01.DBF
input datafile file number=00009 name=D:\ORACLE\APP\ORADATA\CHE\TEST_INDX01.DBF
input datafile file number=00004 name=D:\ORACLE\APP\ORADATA\CHE\USERS01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12076 90880 1240542
File Name: D:\ORACLE\APP\ORADATA\CHE\SYSTEM01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 64448
Index 0 11475
Other 0 2881

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 29439 79440 1240540
File Name: D:\ORACLE\APP\ORADATA\CHE\SYSAUX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 12041
Index 0 10715
Other 0 27245

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 121 7040 1240542
File Name: D:\ORACLE\APP\ORADATA\CHE\UNDOTBS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 6919

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 264 640 1005190
File Name: D:\ORACLE\APP\ORADATA\CHE\USERS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 33
Other 0 252

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1728 12800 894302
File Name: D:\ORACLE\APP\ORADATA\CHE\EXAMPLE01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4452
Index 0 1272
Other 0 5348

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 11596 12800 935985
File Name: D:\ORACLE\APP\ORADATA\CHE\RAJA_DATA01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1057
Index 0 0
Other 0 147

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 6107 6400 935999
File Name: D:\ORACLE\APP\ORADATA\CHE\RAJA_INDX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 160
Other 0 133

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 11628 12800 928348
File Name: D:\ORACLE\APP\ORADATA\CHE\TEST_DATA01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1019
Index 0 0
Other 0 153

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 6107 6400 928453
File Name: D:\ORACLE\APP\ORADATA\CHE\TEST_INDX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 154
Other 0 139

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished backup at 19-MAY-13


2. Remove the datafile in OS level

Before the datafile, please make sure prior backup is available.
Currently there is no failure in database. I shutdown the database & removed the below datafile in OS level “D:\ORACLE\APP\ORADATA\CHE\RAJA_INDX01.DBF”.

3. Startup the database

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 226495588 bytes
Database Buffers 301989888 bytes
Redo Buffers 5828608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: 'D:\ORACLE\APP\ORADATA\CHE\RAJA_INDX01.DBF'

SQL> alter database datafile 7 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

FILE# ERROR TIME
---------- ----------------------------------- ---------
7 FILE NOT FOUND

SQL> select file#,status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 RECOVER
8 ONLINE
9 ONLINE

9 rows selected.

We need to recover the datafile 7.

4. Recover the datafile using Data recover advisor

C:\>set oracle_sid=che

RMAN> connect target /

Recovery Manager: Release 11.1.0.7.0 - Production on Thu May 19 13:45:17 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to target database: CHE (DBID=3460503574)

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
322 HIGH OPEN 19-MAY-13 one or more non-system datafiles are corrupt

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
322 HIGH OPEN 19-MAY-13 One or more non-system datafiles are corrupt

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If a standby database is available, then consider a Data Guard switchover or failover

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 7
Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\oracle\app\diag\rdbms\che\che\hm\reco_948713839.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\oracle\app\diag\rdbms\che\che\hm\reco_948713839.hm


contents of repair script:
# restore and recover datafile
sql 'alter database datafile 7 offline';
restore datafile 7;
recover datafile 7;
sql 'alter database datafile 7 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: alter database datafile 7 offline

Starting restore at 19-MAY-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to D:\ORACLE\APP\ORADATA\CHE\RAJA_I
NDX01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\APP\FLASH_RECOVERY_AREA\
CHE\BACKUPSET\2013_05_18\O1_MF_NNNDF_TAG20130518T103629_5Z482Q6K_.BKP
channel ORA_DISK_1: piece handle=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\BACKUPSET
\2013_05_18\O1_MF_NNNDF_TAG20130518T103629_5Z482Q6K_.BKP tag=TAG20130518T103629
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 19-MAY-13

Starting recover at 19-MAY-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 18 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_18\O1_MF_1_18_5Z4867P5_.ARC
archived log for thread 1 with sequence 19 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_18\O1_MF_1_19_5Z486CGC_.ARC
archived log for thread 1 with sequence 20 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_18\O1_MF_1_20_5Z49DVKR_.ARC
archived log for thread 1 with sequence 21 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_18\O1_MF_1_21_5Z49SJJ9_.ARC
archived log for thread 1 with sequence 22 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_18\O1_MF_1_22_5Z4BRQQN_.ARC
archived log for thread 1 with sequence 23 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_18\O1_MF_1_23_5Z4BRRW0_.ARC
archived log for thread 1 with sequence 24 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_24_5Z6RQ7YR_.ARC
archived log for thread 1 with sequence 25 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_25_5Z6SJZDP_.ARC
archived log for thread 1 with sequence 26 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_26_5Z724OK2_.ARC
archived log for thread 1 with sequence 27 is already on disk as file D:\ORACLE\
APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_19\O1_MF_1_27_5Z75LR6T_.ARC
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
18\O1_MF_1_18_5Z4867P5_.ARC thread=1 sequence=18
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
18\O1_MF_1_19_5Z486CGC_.ARC thread=1 sequence=19
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
18\O1_MF_1_20_5Z49DVKR_.ARC thread=1 sequence=20
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
18\O1_MF_1_21_5Z49SJJ9_.ARC thread=1 sequence=21
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
18\O1_MF_1_22_5Z4BRQQN_.ARC thread=1 sequence=22
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
18\O1_MF_1_23_5Z4BRRW0_.ARC thread=1 sequence=23
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
19\O1_MF_1_24_5Z6RQ7YR_.ARC thread=1 sequence=24
archived log file name=D:\ORACLE\APP\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2013_05_
19\O1_MF_1_25_5Z6SJZDP_.ARC thread=1 sequence=25
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-MAY-13

sql statement: alter database datafile 7 online
repair failure complete

5. Verify the database

SQL> select file#,error,time from v$recover_file;

no rows selected

SQL> select file#,status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE

9 rows selected.