Partitioning of data in Oracle8 will help minimise the outage caused due to media loss. Partitions can be placed in different tablespaces with datafiles for the tablespaces residing on different disks. For example if a media failure affects the datafiles belonging to one tablespace ( and in turn a partitioned table ), the remaining partitions on other tablespaces can still be accessed. The undamaged partitions can still be queried so that the impact of the media failure is limited.

To simulate this scenario we create a table with 2 partitions, p1 and p2. Partition p1 is located in the USERS tablespace while partition p2 is in the TOOLS tablespace . Media failure on one of the disks causes the datafile on the USERS tablespace to be lost. But the table can still be accessed as the other partition p2 is still available.

SVRMGR> select * from xyz;

COL1

----------

ORA-00376: file 5 cannot be read at this time

ORA-01110: data file 5: '/sp105db04/ORACLE/rman/users01.dbf'

SQL> select * from xyz partition (p1);

select * from xyz partition (p1)

*

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/sp105db04/ORACLE/rman/users02.dbf'

SQL> select * from xyz partition (p2);

COL1

----------

100

200

300

450

4 rows selected.

Restore the datafiles which were lost ( i.e files belonging to the USERS tablespace)

SVRMGR> recover tablespace users;

SVRMGR> alter database datafile '/sp105db04/ORACLE/rman/users01.dbf' online;

Statement processed.

SVRMGR> alter database datafile '/sp105db04/ORACLE/rman/users02.dbf' online;

Statement processed.

SQL> select * from xyz partition (p1);

COL1

----------

29

50

75

80

95

SQL> select * from xyz partition (p2);

COL1

----------

100

200

300

450