Recovering datafile not backed up using RMAN

(1)  For test case, We are creating a new tablespace namely tbs2 and is not backed up

RMAN> sql "create tablespace tbs2 datafile ''+DATA'' size 50m";

sql statement: create tablespace tbs2 datafile ''+DATA'' size 50m

RMAN> report schema;

Report of database schema for database with db_unique_name CVRMAN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     +DATA/cvrman/datafile/system.256.847354793
2    640      SYSAUX               ***     +DATA/cvrman/datafile/sysaux.257.847354793
3    50       UNDOTBS1             ***     +DATA/cvrman/datafile/undotbs1.318.848201233
4    5        USERS                ***     +DATA/cvrman/datafile/users.259.847354795
5    345      EXAMPLE              ***     +DATA/cvrman/datafile/example_new.dbf
6    10       TBS1                 ***     +DATA/cvrman/datafile/tbs1_new.dbf
7    50       TBS2                 ***     +DATA/cvrman/datafile/tbs2.328.848467411

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    21       TEMP                 32767       +DATA/cvrman/tempfile/temp.268.847355331

RMAN> list backup of tablespace tbs2;

specification does not match any backup in the repository

2. Manually removed the datafile of tablespace tbs2 and validating the same tablespace for errors and listing the failure details

RMAN> validate tablespace tbs2;

Starting validate at 25-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=59 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=60 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=30 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=54 device type=DISK
RMAN-06169: could not read file header for datafile 7 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 05/25/2014 05:43:16
RMAN-06056: could not access datafile 7
RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
11374      HIGH     OPEN      25-MAY-14     Tablespace 8: 'TBS2' is offline
2348       HIGH     OPEN      25-MAY-14     One or more non-system datafiles are missing
2342       HIGH     OPEN      21-MAY-14     One or more non-system datafiles are offline

RMAN> list failure 2348
2> ;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2348       HIGH     OPEN      25-MAY-14     One or more non-system datafiles are

RMAN> list failure 2342 detail
2> ;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2342       HIGH     OPEN      21-MAY-14     One or more non-system datafiles are
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 2342
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  11365      HIGH     OPEN      25-MAY-14     Datafile 7: '+DATA/cvrman/datafile
    Impact: Some objects in tablespace TBS2 might be unavailable

RMAN>  list failure 2348 detail
2> ;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2348       HIGH     OPEN      25-MAY-14     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 2348
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  11371      HIGH     OPEN      25-MAY-14     Datafile 7: '+DATA/cvrman/datafile/tbs2.328.848467411' is missing
    Impact: Some objects in tablespace TBS2 might be unavailable

(3) Checking the repository for backup of tablespace tbs2 to restore

RMAN> list backup of tablespace tbs2;
specification does not match any backup in the repository

(4)Intrestingly, after issuing the restore command for tablespace tbs2, the missing datafiles for tbs2 is created.

RMAN> restore tablespace tbs2;

Starting restore at 25-MAY-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

creating datafile file number=7 name=+DATA/cvrman/datafile/tbs2.328.848467411
restore not done; all files read only, offline, or already restored
Finished restore at 25-MAY-14

(5) Recover the tablespace tbs2

RMAN> recover tablespace tbs2;

Starting recover at 25-MAY-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

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

Finished recover at 25-MAY-14

(6) Bringing back the tablespace tbs2 to online

RMAN> sql "alter tablespace tbs2 online";

sql statement: alter tablespace tbs2 online

Switch datafile to non-default location using RMAN

1.Script to switch the datafile to non-default location
cat rman_switch.cmd

run
{
sql "alter database datafile 6 offline";
set newname for datafile 6 to '+DATA/cvrman/datafile/tbs1_new.dbf';
restore datafile 6;
switch datafile 6;
recover datafile 6;
sql "alter database datafile 6 online";
}

2. Output

$ rman target="/"

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 22 08:05:30 2014

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

connected to target database: CVRMAN (DBID=657566608)

RMAN> @ rman_switch.cmd

RMAN> run
2> {
3> sql "alter database datafile 6 offline";
4> set newname for datafile 6 to '+DATA/cvrman/datafile/tbs1_new.dbf';
5> restore datafile 6;
6> switch datafile 6;
7> recover datafile 6;
8> sql "alter database datafile 6 online";
9> }
using target database control file instead of recovery catalog
sql statement: alter database datafile 6 offline

executing command: SET NEWNAME

Starting restore at 22-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=24 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=51 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=49 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=50 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 +DATA/cvrman/datafile/tbs1_new.dbf
channel ORA_DISK_1: reading from backup piece +DATA/cvrman/backupset/2014_05_22/nnndf0_tag20140522t051830_0.287.848207921
channel ORA_DISK_1: piece handle=+DATA/cvrman/backupset/2014_05_22/nnndf0_tag20140522t051830_0.287.848207921 tag=TAG20140522T051830
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-MAY-14

datafile 6 switched to datafile copy
input datafile copy RECID=82 STAMP=848217961 file name=+DATA/cvrman/datafile/tbs1_new.dbf

Starting recover at 22-MAY-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

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

Finished recover at 22-MAY-14

sql statement: alter database datafile 6 online

RMAN> **end-of-file**

RMAN> report schema;

Report of database schema for database with db_unique_name CVRMAN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     +DATA/cvrman/datafile/system.256.847354793
2    640      SYSAUX               ***     +DATA/cvrman/datafile/sysaux.257.847354793
3    50       UNDOTBS1             ***     +DATA/cvrman/datafile/undotbs1.318.848201233
4    5        USERS                ***     +DATA/cvrman/datafile/users.259.847354795
5    345      EXAMPLE              ***     +DATA/cvrman/datafile/example.308.847525493
6    10       TBS1                 ***     +DATA/cvrman/datafile/tbs1_new.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/cvrman/tempfile/temp.268.847355331

Recover datafile from image copy

Unable to bring the tablespace to online, needs media recovery
==========================================

RMAN> sql "alter tablespace example online";

sql statement: alter tablespace example online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/14/2014 07:49:56
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace example online
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DATA/cvrman/datafile/example.dbf'


1. List failure from the RMAN prompt, (get the failure details using list failure command in the rman prompt)

RMAN> list failure 2348 detail;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2348       HIGH     OPEN      14-MAY-14     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 2348
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  2351       HIGH     OPEN      14-MAY-14     Datafile 5: '+DATA/cvrman/datafile/example.dbf' is missing
    Impact: Some objects in tablespace EXAMPLE might be unavailable


3. Check for the copy of tablespace example

RMAN> list copy of tablespace example;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
30      5    A 14-MAY-14       1064695    14-MAY-14
        Name: +DATA/cvrman/datafile/example.308.847525493
        Tag: TAG20140514T074452


4.switching the datafile to copy

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "+DATA/cvrman/datafile/example.308.847525493"

5.Recover the tablespace example

RMAN> recover tablespace example;

Starting recover at 14-MAY-14
using channel ORA_DISK_1

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

Finished recover at 14-MAY-14


6.Bring the tablespace back to online
RMAN> sql "alter tablespace example online";

sql statement: alter tablespace example online