How to restore and recover tablespace?

Steps if you need to restore all datafiles in a tablespace is corrupted so you would need to restore the table space. The following example shows the steps

— Shows cannot restore the tablespace yet till the tablespace is online
RMAN> run {
2> restore tablespace ts_something;
3> }

Starting restore at 27-NOV-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/TEST/test_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/product/10.2.0/db_1/dbs/1nlu32je_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/27/2010 20:33:45
ORA-19870: error reading backup piece /u01/oracle/product/10.2.0/db_1/dbs/1nlu32je_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 4

— tablespace cannot be made offline as it will try to flush all blocks in the datafiles but as the datafile is corrupted.
SQL> alter tablespace ts_something offline;
alter tablespace ts_something offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oradata/TEST/test_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

— So using table space offline immediate Oracle will not check if the file exists and does not perform checkpoint and we need to do media recovery on the table space
SQL> alter tablespace ts_something offline immediate;

Tablespace altered.

— Display datafile status
SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = 'TS_SOMETHING'
and t.ts# = d.ts#;

NAME STATUS
—————————————————————— ——-
/u01/oradata/TEST/test_01.dbf RECOVER

— restore the table space
RMAN> run {
2> restore tablespace ts_something;
3> }

Starting restore at 27-NOV-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/TEST/test_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/product/10.2.0/db_1/dbs/1nlu32je_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/product/10.2.0/db_1/dbs/1nlu32je_1_1 tag=TAG20101127T202206
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 27-NOV-10

— table space has be restored but not recovered so shows it cannot be made online till table space is recovered
SQL> alter tablespace ts_something online;
alter tablespace ts_something online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/oradata/TEST/test_01.dbf'

— recover table space
SQL> recover tablespace ts_something;
Media recovery complete.

— Tablespace can be made online
SQL> alter tablespace ts_something online;

Tablespace altered.

Shows data file status
SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = 'TS_SOMETHING'
and t.ts# = d.ts#;

NAME STATUS
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— ——-
/u01/oradata/TEST/test_01.dbf ONLINE

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s