Recovery of Non-system Datafile without backup using rman in 11gR2

Assumption :-

For tablespace RECOVERY one datafile is backedup using rman and other datafile is not backedup.

1.. Create tablespace called RECOVERY with one datafile recovery01.dbf



2.. Take full backup of Database using rman

Now again add a new datafile as recovery02.dbf to RECOVERY Tablespace that you have created.


3.. For testing purpose create a Table called DEMO in RECOVERY tablespace and perform some DML operation and commit.



4.. Now navigate to the location of where RECOVERY tablespace was created and remove both the datafile including newly created.


5.. Now once you know that datafile of RECOVERY is removed as process of recovery try to bring it offline and you will get error as follows.


5.1.. Now bring RECOVERY tablespace offline with immediate option and check status.


Note :- Reasons to make tablespace offline:-

1.. To make a portion of the database unavailable while allowing normal access to the remainder of the database

2.. To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)

3.. To make an application and its group of tables temporarily unavailable while updating or maintaining the application

4.. To rename or relocate tablespace datafiles.

Following tablespace cannot be taken offline:


2.. The undo tablespace

3.. Temporary tablespaces

NORMAL:- A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error and when you specify OFFLINE NORMAL, the database takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default.

TEMPORARY:- A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY, the database takes offline the datafiles that are not already offline, checkpointing them as it does so.If no files are offline, but you use the temporary clause, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.

IMMEDIATE:- A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.

5.2.. Datafile recovery01.dbf was created when tablespace RECOVERY was created and backedup also but other datafile recovery02.dbf was created later and is not backed up.


5.3.. Now connect with rman and check datafiles are backed up or not using file_id

From given below output datafile 3 is backed up associated with tablespace RECOVERY.

209.pngBut for datafile 6 was not backedup associated with tablespace RECOVERY.210.png

6.. While restoring tablespace RECOVERY from rman backup. Restore process will create datafile 6 which was not backed up . i.e rman full backup was taken before datafile was 6 added and restore process will restore datafile 3 from full rman backup.


6.1 Recovering tablespace RECOVERY.


Note :- Given below syntax can be also used.

restore datafile ‘location of datafile’

recover datafile ‘location of datafile’

7.. Bring the tablespace RECOVERY online and check status of tablespace.


7.1..  Using sql check whether datafile 3 and 6 are created for tablespace RECOVERY or not.


Now using OS commands.


7.2.. Query the table which was created for tablespace RECOVERY.


Happy Learning !!!!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s