Recently , we have run into a problem where a tablespace in one of our UAT database was corrupted. This corruption of
datafile was caused by harddisk failure where tablespace's datafile located. Though it was UAT database, we had to recover the
tablespace as soon as possible so that application users can perforom works on it.
As you may know, the quickest way to recover from this kind of errror in Oracle is to put the tablespace offline, then
restore the tablespace from the latest backup and follow by applying all the archive log since the full backup.

Below test case demonstrate what we have done to try to recover the tablespace:

Got error when we want to query the table in the tablespace "test". The error show we have problem with the datafile..

SQL> select count(*) from t;

select count(*) from t

*

ERROR at line 1:

ORA-01115: IO error reading block from file 6 (block # 17)

ORA-01110: data file 6: '/u01/oradata/WRXUAT/test.dbf'

ORA-27091: unable to queue I/O

ORA-27072: File I/O error

Additional information: 3

Additional information: 12

Since the datafile of tablespace test is corruped, we put it offline so that we can perform recovery.

SQL> alter tablespace test offline immediate;

Tablespace altered.

We now initiate rman, and restore the datafile from the last full database backup.

bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Sep 25 17:34:13 2010

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

connected to target database: WRXUAT (DBID=2511178338)

RMAN> restore tablespace test;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"
RMAN-01007: at line 1 column 20 file: standard input


RMAN> restore tablespace 'test'
2> ;

Starting restore at 25-SEP-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/25/2010 19:55:54
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "test"

RMAN> restore tablespace "test";

Starting restore at 25-SEP-10
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/25/2010 19:56:12
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "test"

From the RMAN errors shown above, it appears the RMAN is unable to restore the tablespace with name "test", no matter we try to add double-quote or single-quote to the tablespace name. Checking the V$RESERVED_WORDS dynamic view we can find that "TEST" is a reserved words from Oracle.

SQL> select * from v$reserved_words where keyword='TEST';

KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
TEST                                    4 N N N N N

In fact, Oracle has already told us to avoid using reserved words as identifiers,e.g. SID,table ,column and tablespace etc. In some cases, e.g. table and column name, we can add double-quote to the reserved words as a workaround. However, this make it difficult to maintain and is prone to errors.