Skip to content

How to recover datafile not backed up

by Mohamed Azar on January 3, 2011

Recently i just added one datafile for a tablespace and later, had a media failure occur before I backuped recently datafile.In this case , How do we recover.

We have previous Good Database backup.

Redo files needed upto Datafiles created.

If you have a current control file, we can restore , recover datafile & tablespace or database level.

Now See Below Example :

before I just backuped database with archivelogs.

Now I’m go to Add datafile to a existing tablespace.

SQL> alter tablespace testtbs add datafile 'D:\BACKUP\RESTORENEW\TESTTBS02.dbf' SIZE 50M;

Tablespace altered. 

SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS01.DBF
D:\BACKUP\RESTORENEW\TESTTBS02.DBF

Now I moved Recently added DBF file to other location

d:\backup\restorenew>move testtbs02.dbf d:\backup
        1 file(s) moved.

d:\backup\restorenew>

Shutdown Database & Startup

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: 'D:\BACKUP\RESTORENEW\TESTTBS02.DBF'

Now Recover tablespace using RMAN

RMAN> restore tablespace testtbs;

Starting restore at 03-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK

creating datafile file number=12 name=D:\BACKUP\RESTORENEW\TESTTBS02.DBF
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 00009 to D:\BACKUP\RESTORENEW\TESTTBS01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP tag=TAG201101
03T122037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-JAN-11

RMAN> recover tablespace testtbs;

Starting recover at 03-JAN-11
using channel ORA_DISK_1

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

Finished recover at 03-JAN-11

RMAN> alter database open;

database opened

Let me check datafile

SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS01.DBF
D:\BACKUP\RESTORENEW\TESTTBS02.DBF

Using A backup Control file

If we’re using a backup control file that has no information about the datafile, then we must restore and recover at the database level.

I just added another datafile also.

SQL> alter tablespace testtbs add datafile 'D:\BACKUP\RESTORENEW\TESTTBS03.dbf' SIZE 50M;

Tablespace altered.

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                331350480 bytes
Database Buffers             197132288 bytes
Redo Buffers                   5804032 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 03-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

recovery area destination: C:\app\oracle\mazar\flash_recovery_area
database name (or database unique name) used for search: AZARDB
channel ORA_DISK_1: AUTOBACKUP C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20110103
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP

channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
output file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL
Finished restore at 03-JAN-11

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 03-JAN-11
Starting implicit crosscheck backup at 03-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 03-JAN-11

Starting implicit crosscheck copy at 03-JAN-11
using channel ORA_DISK_1
Crosschecked 17 objects
Finished implicit crosscheck copy at 03-JAN-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_29_6L2X3V22_.ARC
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_29_6L2XL3M4_.ARC
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_26\O1_MF_S_738773797_6KGCCPPK_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_26\O1_MF_S_738777392_6KGGW2R7_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_N_738851503_6KJQ80HG_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_N_738851640_6KJY64RH_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_S_738813797_6KHLFP6B_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP

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 00001 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\BACKUP\ADDME01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\BACKUP\RCRMAN01.DBF
channel ORA_DISK_1: restoring datafile 00007 to D:\BACKUP\USERSTBS01.DBF
channel ORA_DISK_1: restoring datafile 00008 to D:\BACKUP\UNDOTBS02.DBF
channel ORA_DISK_1: restoring datafile 00009 to D:\BACKUP\RESTORENEW\TESTTBS01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\BACKUP\TESTDB001.DBF
channel ORA_DISK_1: restoring datafile 00011 to D:\BACKUP\TESTTB1.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP tag=TAG201101
03T122037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:05
Finished restore at 03-JAN-11

RMAN> recover database;

Starting recover at 03-JAN-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 34 is already on disk as file C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_34_6L35K2PO_
.ARC
archived log for thread 1 with sequence 35 is already on disk as file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG
archived log file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_34_6L35K2PO_.ARC thread=1 sequence=34
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
creating datafile file number=12 name=D:\BACKUP\RESTORENEW\TESTTBS02.DBF
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
creating datafile file number=13 name=D:\BACKUP\RESTORENEW\TESTTBS03.DBF
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
media recovery complete, elapsed time: 00:00:06
Finished recover at 03-JAN-11

RMAN> alter database open resetlogs;

database opened
SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS02.DBF
D:\BACKUP\RESTORENEW\TESTTBS03.DBF
D:\BACKUP\RESTORENEW\TESTTBS01.DBF
From: Backup & Recovery
←
→
3 Comments Post a comment
  1. Tariq farooq #

    This is an AWESOME article! Keep up the GREAT work!!!

    January 3, 2011
    Reply
    • azardba #

      Thank you Mr.Tariq Farooq. You’re Welcome 🙂

      January 3, 2011
      Reply
  2. android data #

    Hi
    Wicked post! For Data Recovery and Backup, check out:
    http://bit.ly/X3wPr

    February 11, 2013
    Reply

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s

%d bloggers like this: