Recover from a missing data file

This document will detail two recovery scenarios dealing with a missing datafile using a RMAN disk based backup. If you need a document for configuring RMAN see the post Setting up RMAN for backup and recovery using Database Control

Scenario:

You receive an email from a user reporting the error below when attempting to create a table in their default tablespace.

SQL> create table test as select * from user_objects;
create table test as select * from user_objects
                                   *
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u02/app/oracle/oradata/orcl/test.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL>

You look on the file system and you see that the file test.dbf is indeed no longer present.

[oracle@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/
arch    ctl            redo          system01.dbf  undotbs01.dbf
backup  example01.dbf  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@ora1 ~]$

Action:

Identify the tablespace in which the missing data file is a member.

SQL> select tablespace_name from dba_data_files where file_id = 6;

TABLESPACE_NAME
------------------------------
TEST

SQL>

In this scenario the database does not need to be shutdown in order to recover the missing data file, however, the tablespace TEST does need to be taken offline.

SQL> alter tablespace test offline immediate;

Tablespace altered.

SQL>

We now connect to the database through RMAN and restore the missing data file.

[oracle@ora1 ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 20 11:59:53 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1239150297)

RMAN> restore datafile 6;

Starting restore at 20-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK

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 00006 to /u02/app/oracle/oradata/orcl/test.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1
channel ORA_DISK_1: piece handle=/u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1 tag=TAG20100420T091928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-APR-10

RMAN> 

At this point data file 6 has been recovered now we perform recovery on the file.

RMAN> recover datafile 6;

Starting recover at 20-APR-10
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 20-APR-10

RMAN>

All that is left is to bring the tablespace TEST back online.

SQL> alter tablespace test online;

Tablespace altered.

SQL>

With recovery complete the user is now able to create tables in their default tablespace.

SQL> create table test as select * from user_objects;

Table created.

SQL> select tablespace_name from user_tables where table_name ='TEST';

TABLESPACE_NAME
------------------------------
TEST

SQL> 

Scenario:

You attempt to start the database and see the following error:

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u02/app/oracle/oradata/orcl/test.dbf'


SQL>

You check the location and verify that the file is indeed missing.

ACTION:

The missing datafile will need to be restored from a backup. Currently the database is mounted which is required for recovery so we can just go straight into RMAN.

After connecting we restore datafile 6.

[oracle@ora1 ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 20 15:17:14 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1239150297, not open)

RMAN> restore datafile 6;

Starting restore at 20-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK

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 00006 to /u02/app/oracle/oradata/orcl/test.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1
channel ORA_DISK_1: piece handle=/u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1 tag=TAG20100420T091928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-APR-10

RMAN>

Next we recover datafile 6.

RMAN> recover datafile 6;

Starting recover at 20-APR-10
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 20-APR-10

RMAN>

And finally we open the database.

RMAN> alter database open;

database opened

RMAN> 

4 thoughts on “Recover from a missing data file”

  1. I have a script that I use to run my rmans from. I manually move and delete the rman files, but I set an expire time in rman. Should I let rman delete the files? I do one day for a full backup and then I do incremental backups the rest of the week, so should I set me expire time for 7 days and just issue deleted expired backups, while doing my full and incremental backups?

  2. If you move the RMAN backups and delete them outside of RMAN through OS commands you could run into some problems later as RMAN still thinks that backups are on the backup device.

    Before coming up with a policy to remove the backups it would be wise to discuss with the data owners what their expectations are concerning the backups. These discussions will help you determine how to configure RMAN to handle old backups. Unfortunately the owners will most likely not be versed in Oracle terminology so you will need to be.

    Oracle allows two ways to identify which backups to delete and you can only specify one or the other. One method is backup redundancy which stats how many backups you want to keep. For example if you set the redundancy to 7 after the 8th backup the 1st backup is eligible for deletion. Note that this number has no correlation to the day. If you take 7 backups in 1 day and 8th the next, the 1st back is still eligible for deletion.

    The other method is based on a recovery window. If you set a recovery window of 7 days after the 8th day the backups taken the 1st day are eligible for deletion. The recovery window is based on days. Days will most likely be the terminology used when discussing backups with the data owners.

    Once you discuss the backups with owners of the data and you have a plan to go forward make the appropriate changes in RMAN.

    For example if the owners want the last 7 backups issue a command like below in RMAN while connected to the databases.

    CONFIGURE RETENTION POLICY TO REDUNDANCY 7;

    If the owners want the last 7 days issue this command.

    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

    Next you should clean up the old backups and records associated with them using crosscheck followed by a delete expired.

  3. Thanks for the nice post.

    I done with everything but at the last tablespace SA(in my case) back online.

    I am getting this error. can you please help out here.

    SQL> alter tablespace SA online;
    alter tablespace SA online
    *
    ERROR at line 1:
    ORA-01113: file 6 needs media recovery
    ORA-01110: data file 6: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/tbs_data.dat’

    Thanks
    gajanan

  4. SQL> select count (*) from M_LIC_SB;
    ERROR:
    ORA-12801: error signaled in parallel query server P002
    ORA-01116: error in opening database file 45
    ORA-01110: data file 45: ‘/home/bkp_kndnew/exp_custom20.dbf’
    ORA-07368: sfofi: open error, unable to open database file.
    SVR4 Error: 2: No such file or directory

    have not taken the backup of this file now how i can recover this file

Leave a Reply

Your email address will not be published. Required fields are marked *