Lesson 6	Recovery of a read-only tablespace
Objective	Describe recovery scenarios for a read-only tablespace.

Recovery of Read-Only Tablespace

If a read-only tablespace is destroyed due to media failure, the recovery process is dependent on how current the tablespace backups are. The following image demonstrates three recovery scenarios for a read-only tablespace.

employee-history-database — Employee History Database

The following diagram lists possible solutions to recover the tablespace given different scenarios.

Note: Always make a backup of the tablespace after you switch the tablespace from read-write to read-only or vice versa. If the media recovery is permanent, you can restore the read-only tablespace to an alternative location using the

ALTER DATABASE RENAME FILE <old_filename> 
TO <new_filename>; command.

Suppose you made a backup of a tablespace named STUDENT after you altered it to read-only. At a later time, you alter it to read-write again, but you forget to make a backup of it. Now a media failure has damaged this tablespace. What are you going to do? The following simulation demonstrates how to recover a read-only tablespace in this situation.

After a media failure, you try to start the system. Type |||(S1)STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA|||(S0) at the prompt and press Enter
STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA
The error message shows that the datafile C:\ORANT\DATABASE\STUDENT.ORA is damaged. You know this file belongs to the STUDENT tablespace, which was read-only, and you have a backup of the tablespace. However, you remember that you had one of your junior DBAs change the tablespace to read-write a couple of days ago and it is not included in your routine backup strategy. As a result, you have to use the backup when the tablespace STUDENT was read-only to perform the recovery. In order to make the database available, you must take the damaged tablespace STUDENT offline.
Type ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE; at the prompt and press Enter.
ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE;
To start the database, type |||(S1)ALTER DATABASE OPEN;|||(S0) at the prompt and press Enter.

ALTER DATABASE OPEN;
Before restoring the backup STUDENT, you have to access the operating system. Type HOST at the prompt and press Enter.
HOST

To restore the backup STUDENT, type

COPY C:\ORANT\DATABASEX\ORCLBACKUP\STUDENT_R.ORA 
C:\ORANT\DATABASE\STUDENT.ORA

at the prompt and press Enter.

Recover Read Only Tablespace

SVRMGR> STARTUP OPEN PFILE=C:\ORNAT\DATABASE\INITORCL.ORA 
ORACLE instance started.
Total System Global Area                         11710464 bytes
Fixed Size                                          49152 bytes
Variable Size                                    11177984 bytes
Database Buffers                                   409600 bytes
Redo Buffers                                        73728 bytes
Database mounted.
ORA-01157: cannot identify data file 5 - file not found
ORA-01110: data file 5: 'C:\ORANT\DATABASE\STUDENT.ORA'
SVRMGR> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE; 
Statement processed.
SVRMGR> ALTER DATABASE OPEN;
Statement processed.
SVRMGR> HOST
Microsoft(R) Windows NT(TM)
(C) Copyright 1985-1996 Microsoft Corp.
C:\orant> COPY C:\ORANT\DATABASEx\ORCLBACKUP\STUDENT_R.ORA C:\ORANT\DATABASE\STUDENT.ORA
C:\ORANT\DATABASEx\ORCLBACKUP\STUDENT_R.ORA
1 file(s) copied.
C:\orant> EXIT
SVRMGR> RECOVER AUTOMATIC DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA';
Statement processed.
SVRMGR> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' ONLINE; 
Statement processed.
SVRMGR>

To view the lines for the whole process in the simulation, click the View Code button.
The next lesson investigates recovery issues related to the read-only tablespace.