test recovery tablespace command

RMAN> sql 'alter tablespace tts1 offline';

sql statement: alter tablespace tts1 offline

RMAN> sql 'alter tablespace tts1 online';

sql statement: alter tablespace tts1 online

RMAN> sql 'alter tablespace tts1 offline immediate';

sql statement: alter tablespace tts1 offline immediate

RMAN> sql 'alter tablespace tts1 online';

sql statement: alter tablespace tts1 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/28/2013 14:02:55
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace tts1 online
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u02/oradata/TTS/tts1_01.bdf'

RMAN> recover tablespace tts1 ;

Starting recover at Mar 28 2013 14:03:11
using channel ORA_DISK_1

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

Finished recover at Mar 28 2013 14:03:12

RMAN> sql 'alter tablespace tts1 online';

sql statement: alter tablespace tts1 online


A more likely scenario in real world is particular disk/filesystem is not available, thus we need to relocate those affected data files.

1. backup the tablespace TTS1

RMAN> report schema;

Report of database schema for database with db_unique_name TTS

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u02/oradata/TTS/system01.dbf
2    600      SYSAUX               ***     /u02/oradata/TTS/sysaux01.dbf
3    370      UNDOTBS1             ***     /u02/oradata/TTS/undotbs01.dbf
4    5        USERS                ***     /u02/oradata/TTS/users01.dbf
5    20       TTS1                 ***     /u02/oradata/TTS/tts1_01.bdf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/TTS/temp01.dbf



RMAN> backup tablespace tts1;

Starting backup at Mar 28 2013 14:29:58
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/oradata/TTS/tts1_01.bdf
channel ORA_DISK_1: starting piece 1 at Mar 28 2013 14:29:59
channel ORA_DISK_1: finished piece 1 at Mar 28 2013 14:30:00
piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG201303                             28T142958_8o7rv78f_.bkp tag=TAG20130328T142958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at Mar 28 2013 14:30:00

Starting Control File and SPFILE Autobackup at Mar 28 2013 14:30:00
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_03_28/o1_mf_s_811261800_8o                             7rv8gp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at Mar 28 2013 14:30:01



RMAN> list backup of tablespace tts1;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
24      Incr 0  204.51M    DISK        00:01:20     Mar 27 2013 15:27:08
        BP Key: 24   Status: AVAILABLE  Compressed: YES  Tag: TAG20130327T152547
        Piece Name: /u02/oradata/TTS/FRA/TTS/backupset/2013_03_27/o1_mf_nnnd0_TAG20130327T152547_8o57qx5l_.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    0  Incr 1021037    Mar 27 2013 15:25:49 /u02/oradata/TTS/tts1_01.bdf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
28      Incr 1  312.00K    DISK        00:00:31     Mar 27 2013 15:32:29
        BP Key: 28   Status: AVAILABLE  Compressed: YES  Tag: TAG20130327T153155
        Piece Name: /u02/oradata/TTS/FRA/TTS/backupset/2013_03_27/o1_mf_nnnd1_TAG20130327T153155_8o583gsl_.bkp
  List of Datafiles in backup set 28
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    1  Incr 1021321    Mar 27 2013 15:31:58 /u02/oradata/TTS/tts1_01.bdf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
33      Full    2.05M      DISK        00:00:01     Mar 28 2013 14:30:00
        BP Key: 33   Status: AVAILABLE  Compressed: YES  Tag: TAG20130328T142958
        Piece Name: /u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T142958_8o7rv78f_.bkp
  List of Datafiles in backup set 33
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5       Full 1101322    Mar 28 2013 14:29:59 /u02/oradata/TTS/tts1_01.bdf

2. Simulate the disk is gone.

RMAN> sql 'alter tablespace tts1 offline immediate';

sql statement: alter tablespace tts1 offline immediate

RMAN> set newname for datafile '/u02/oradata/TTS/tts1_01.bdf' to '/home/oracle/tts1_01.dbf';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03031: this option of set command needs to be used inside a run block

RMAN> run {
2> set newname for datafile '/u02/oradata/TTS/tts1_01.bdf' to '/home/oracle/tts1_01.dbf';
3> restore tablespace tts1;
4> switch datafile all;
5> recover tablespace tts1;
6> sql 'alter tablespace tts1 online ' ;
7> }

executing command: SET NEWNAME

Starting restore at Mar 28 2013 14:33:37
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 00005 to /home/oracle/tts1_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T142958_8o7rv78f_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T142958_8o7rv78f_.bkp tag=TAG20130328T142958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at Mar 28 2013 14:33:38

datafile 5 switched to datafile copy
input datafile copy RECID=24 STAMP=811262018 file name=/home/oracle/tts1_01.dbf

Starting recover at Mar 28 2013 14:33:38
using channel ORA_DISK_1

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

Finished recover at Mar 28 2013 14:33:38

sql statement: alter tablespace tts1 online

RMAN> report schema;

Report of database schema for database with db_unique_name TTS

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u02/oradata/TTS/system01.dbf
2    600      SYSAUX               ***     /u02/oradata/TTS/sysaux01.dbf
3    370      UNDOTBS1             ***     /u02/oradata/TTS/undotbs01.dbf
4    5        USERS                ***     /u02/oradata/TTS/users01.dbf
5    20       TTS1                 ***     /home/oracle/tts1_01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/TTS/temp01.dbf


3. after disaster,  put the datafile to original location;


RMAN> backup as copy datafile 5 format '/u02/oradata/TTS/tts1_01.bdf';

Starting backup at Mar 28 2013 14:37:41
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/tts1_01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2013 14:37:42
ORA-19504: failed to create file "/u02/oradata/TTS/tts1_01.bdf"
ORA-27038: created file already exists
Additional information: 1

RMAN> host ' rm /u02/oradata/TTS/tts1_01.bdf ';

host command complete

RMAN> backup as copy datafile 5 format '/u02/oradata/TTS/tts1_01.bdf';

Starting backup at Mar 28 2013 14:37:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/tts1_01.dbf
output file name=/u02/oradata/TTS/tts1_01.bdf tag=TAG20130328T143759 RECID=26 STAMP=811262280
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at Mar 28 2013 14:38:00

Starting Control File and SPFILE Autobackup at Mar 28 2013 14:38:00
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_03_28/o1_mf_s_811262280_8o7sb92t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at Mar 28 2013 14:38:01

RMAN> sql 'alter database datafile 5 offline ' ;

sql statement: alter database datafile 5 offline


RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "/u02/oradata/TTS/tts1_01.bdf"

RMAN> sql 'alter database datafile 5 online ' ;

sql statement: alter database datafile 5 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/28/2013 14:40:30
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 5 online
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u02/oradata/TTS/tts1_01.bdf'

RMAN> recover datafile 5  ;

Starting recover at Mar 28 2013 14:40:47
using channel ORA_DISK_1

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

Finished recover at Mar 28 2013 14:40:47

RMAN> sql 'alter database datafile 5 online ' ;

sql statement: alter database datafile 5 online

RMAN> report schema;

Report of database schema for database with db_unique_name TTS

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u02/oradata/TTS/system01.dbf
2    600      SYSAUX               ***     /u02/oradata/TTS/sysaux01.dbf
3    370      UNDOTBS1             ***     /u02/oradata/TTS/undotbs01.dbf
4    5        USERS                ***     /u02/oradata/TTS/users01.dbf
5    20       TTS1                 ***     /u02/oradata/TTS/tts1_01.bdf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/TTS/temp01.dbf

RMAN> exit


Recovery Manager complete.

4. Verification

orarac2poc:TTS:/home/oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 28 14:42:10 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SYS@TTS> select table_name ,owner from dba_TABLEs where tablespace_name='TTS1';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T01                            TRANP
T02                            TRANP
IDX03                          TRANP

SYS@TTS> select count(*) from tranp.t01;

  COUNT(*)
----------
     65949