Lesson 7	Recovery issues related to read-only tablespaces
Objective	Explain how read-only tablespaces affect database recovery and how to handle them properly.

Recovery Issues with Read-only Tablespaces

A tablespace is a logical storage unit in an Oracle database that holds data files, such as tables and indexes. When recovering an Oracle database, special care is required if it contains read-only tablespaces, which are tablespaces that cannot be modified and thus do not generate redo (records of changes made to the database). Redo information cannot be applied to these files, which can cause recovery to terminate with errors if not handled correctly.

Handling Read-only Tablespaces During Recovery

Take read-only tablespaces offline before starting recovery. This prevents errors when applying redo. For example, use the following command before recovery:
```
ALTER TABLESPACE  OFFLINE;
    
```
Use the correct backup control file. A control file is a small binary file that stores metadata about the database, including the status of tablespaces (read-only or read-write). Ensure the backup control file reflects the tablespace’s status at the time of the backup.

Using the Correct Backup Control File

If a tablespace was read-only at the time of backup, use a backup control file that records this state. If it was read-write, use a control file that includes this information. To recover the database using a backup control file, execute:


RECOVER DATABASE USING BACKUP CONTROLFILE;

If no suitable backup control file exists, you must recreate the control file.

Recreating the Control File

A control file can be recreated using:


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This generates a trace script with SQL commands to rebuild the control file. During recreation, read-only tablespaces may be omitted from the new control file, causing their data files to appear as `MISSING00005` (or similar) because the control file no longer references them. To resolve this, rename the file to its correct location and bring the tablespace online:


ALTER DATABASE RENAME FILE 'MISSING00005' TO 'C:\ORANT\DATABASE\STUDENT.ORA';
ALTER TABLESPACE STUDENT ONLINE;

Impact of Mode Changes

If a tablespace is changed from read-only to read-write, the recovery process may not know when this change occurred. Backups taken before the switch can become unusable because they lack the redo information needed for the read-write state.

Best Practice

Always perform a new backup immediately after changing a tablespace from read-only to read-write to ensure recovery compatibility.
Keep multiple versions of the control file to ensure compatibility with different tablespace states.

The next lesson concludes this module.