Lesson 7	Recovery issues related to read-only tablespaces
Objective	Describe recovery issues related to read-only tablespace

Recovery issues related to Read-only Tablespaces

After a media failure, you must perform media recovery with the option: USING BACKUP CONTROLFILE. Some of your datafiles belong to the read-only tablespace. During the process, you get error messages saying that redo information cannot be applied to the read-only datafiles and the recovery session terminated unsuccessfully.
Recovering a database with read-only tablespaces via the USING BACKUP CONTROLFILE option can be a tricky process. Before you start the recovery, you have to get rid of the error messages by taking the read-only tablespaces offline. Then, you have to make sure you are using the correct version of the backup control file.

Using Correct Backup control file

It is important to make sure you are using the appropriate backup control file. It makes a big difference if a tablespace is read-only or read-write. At the end of recovery, if a tablespace is read-only, you need to use the backup control file from a time when the tablespace was read-only. If the tablespace is read-write, make sure you use the backup control file with this information. If there is no appropriate version of the control file, you have to recreate it.

Recreating Control File

If you have to recreate a control file for a database with a read-only tablespace, there are special procedures to follow. You can find these special procedures in a trace file if you issue the ALTER DATABASE BACKUP CONTROLFILE TO TRACE; command.
The output below is a trace file for the database ORCL with a read-only tablespace STUDENT. In this trace file, notice the DATAFILE clause. This clause does not include the read-only tablespace STUDENT. At the end of the trace file, the read-only tablespace STUDENT gets its name back and is put back online.

Dump file C:\orant\RDBMS80\trace\ORA00214.TRC
Thu Mar 09 15:14:45 2000
ORACLE V8.0.5.0.0 - Production vsnsta=0
vsnsql=c vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.1.0 - Production
Windows NT V4.0, OS V5.101, CPU type 586
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 11

pid: d6


*** SESSION ID:(10.1) 2000.03.09.15.14.45.241
*** 2000.03.09.15.14.45.241
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1630
LOGFILE
GROUP 1 'C:\ORANT\DATABASE\LOG4ORCL.ORA'  SIZE 1M,
GROUP 2 'C:\ORANT\DATABASE\LOG3ORCL.ORA'  SIZE 1M,
GROUP 3 'C:\ORANT\DATABASE\LOG2ORCL.ORA'  SIZE 1M,
GROUP 4 'C:\ORANT\DATABASE\LOG1ORCL.ORA'  SIZE 1M
DATAFILE
'C:\ORANT\DATABASE\SYS1ORCL.ORA',
'C:\ORANT\DATABASE\USR1ORCL.ORA',
'C:\ORANT\DATABASE\RBS1ORCL.ORA',
'C:\ORANT\DATABASE\TMP1ORCL.ORA',
'C:\ORANT\DATABASE\USER_TEMP.ORA',
'C:\ORANT\DATABASE\OEM.ORA';
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Files in read only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00005'
TO 'C:\ORANT\DATABASE\STUDENT.ORA';
# Online the files in read only tablespaces.
ALTER TABLESPACE "STUDENT" ONLINE;

Recreating the control file does, however, affect the recovery of a read-write tablespace which was once read-only. If you make the tablespace read-write in the control file, the recovery process can no longer determine when the tablespace was switched from read-only. Therefore, the backup made when the tablespace was still read-only becomes invalid and recovery from this backup is impossible.
To avoid problems, always perform a backup immediately after you change a tablespace from read-only to read-write. The next lesson concludes this module.