3.5. Recovering Read-Only Tablespaces
A read-only tablespace is a tablespace that contains static information. This means that in most cases, no media recovery is required. This type of read-only tablespace recovery is of a non-critical nature.
There are only a few times when media recovery is required with a read-only tablespace. The first is when the last backup of the read-only tablespace was taken when the tablespace was read-write and the tablespace was made read-only afterward. The second is when the tablespace was read-only when the last backup was taken, then was made read-write in between, and then was made read-only again. These two scenarios are really saying that the checkpoint process that updates the SCNs for the control file and file headers will need to be synchronized by the recovery process of applying log files. These two methods are the same as any other tablespace recovery. Read-only recovery is described in Table 3.1.
Because the most common method of read-only tablespace recovery is that no media recovery is needed, we will walk through an example of this approach. This is non-critical recovery method. Let's look at recovering a read-only tablespace in this manner.
First, make the tablespace USERS read-only and force this statement to archive log by switching through the redo logs:
SQL> alter tablespace users read only; Tablespace altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
We are using a Windows environment for our example. If we were using Unix, the command is cp to copy the file. Next, shut down the database ORA101T and make a cold backup in the C:oracleoradataackupora101t directory for restore purposes:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> host Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:Documents and Settingsdstuns> cd c:oracleoradataackupora101t c:oracleoradataackupora101t> copy c:oracleoradataora101t*
Start up the database to see the error:
SQL> startup ORACLE instance started. Total System Global Area 88080384 bytes Fixed Size 787588 bytes Variable Size 78642044 bytes Database Buffers 8388608 bytes Redo Buffers 262144 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: 'C:ORACLEORADATAORA101TUSERS01.DBF'
Shut down the database and do a restore of the users01.dbf read-only tablespaces datafile:
SQL> shutdown immediate ORA-01109: database not open Database dismounted.
ORACLE instance shut down. SQL> host Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:Documents and Settingsdstuns> cd c:oracleoradataora101t C:oracleoradataora101t> copy c:oracleoradataackupora101tusers01.dbf
users01.dbf C:oracleoradataora101t> exit
Finally, start the database ORA101T without issue because the user01.dbf has been restored:
SQL> startup ORACLE instance started. Total System Global Area 88080384 bytes Fixed Size 787588 bytes Variable Size 78642044 bytes Database Buffers 8388608 bytes Redo Buffers 262144 bytes Database mounted. Database opened. SQL>
As you can see, the USERS01.DBF datafile for the read-only tablespaces only needs to be restored. No recovery is required because there are no changes in the datafile. Essentially this means that the USERS01.DBF datafile's header does not change when database checkpointing updates SCN values in the normal read-write datafiles. So the USERS tablespace is static after it becomes read-only.