(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
