Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, February 25, 2013

RMAN: Recovery of missing datafile that is never backed up

Oracle Database 11gR2 on Windows 7
DB in archivelog mode

Assuming that already a tablespace "TS1" is existing with one datafile, if not you can use the statements below.
 create tablespace ts1 datafile 'C:\app\Inam\oradata\orcl\ts01.dbf' size 10m reuse;

1-   Add the new datafile in the tablespace
SQL> alter tablespace ts1 add datafile 'C:\app\Inam\oradata\orcl\tsnew.dbf' size 10m;

Tablespace altered.

Do some activity
SQL> create table ali.newtable as select * from dba_objects;

Table created.

2- Now shutdown instance and then delete the datafile "tsnew.dbf" for which we don't have any backup. After this mount the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2262048 bytes
Variable Size             624954336 bytes
Database Buffers          436207616 bytes
Redo Buffers                5513216 bytes
Database mounted.

3- The missing datafile is the only datafile that needs recovery. In this case you do not have to restore anything. Archivelogs are restored automatically by RMAN as they are needed for the recover command.

C:\Windows\system32>rman target /
Recovery Manager: Release - Production on Mon Feb 25 10:20:31 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1336013077, not open)

RMAN> run {
2>         allocate channel c1 type disk;
3>         sql "alter database create datafile
4>             ''C:\app\Inam\oradata\orcl\TSNEW.dbf''" ;
5>         recover database;
6>         sql "alter database open";
7>         release channel c1;
8>     }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=156 device type=DISK
sql statement: alter database create datafile             ''C:\app\Inam\oradata\
Starting recover at 25-FEB-13
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-FEB-13
sql statement: alter database open
released channel: c1


Ref: 130724.1

No comments: