RelationalDBDesign 





Physical Backups  «Prev  Next»
Lesson 12 "Read-only" tablespace backups
Objective Explain the necessity to backup "read-only" tablespaces.

Necessity to Backup Read-only Tablespace

Using read-only tablespaces eliminates the need to perform frequent backups of large, static portions of the database.
When you create a new tablespace, it is always created by default, as a read-write tablespace. You can change the tablespace to read-only with the READ ONLY option of the ALTER DATABASE command.
All of its associated datafiles will also be rendered read-only. After changing the tablespace to read-only, the DBA should make a backup. When this backup is used in a recovery, Oracle will treat it as being consistent with the most recent whole database backup. Because a read-only tablespace cannot be modified, it does not need repeated backups. If you need to recover a database, you do not need to recover any read-only tablespaces since they could not have been modified; you simply restore them to their original or alternate location.
Making a tablespace read-only does not change its offline or online status. The command ALTER TABLESPACE <tablespacename> OFFLINE | ONLINE functions the same for read-only and read-write tablespaces.

Read only Table Space Backup
Read only Table Space Backup

You may change the status of a tablespace from read-write to read-only by using the ALTER TABLESPACE command.
SVRMGR> ALTER TABLESPACE user_data READ ONLY;

After this command is issued, a checkpoint is performed for all datafiles associated with the tablespace. The file headers are then frozen with the current SCN (System Change Number ) and the DBWR(Database Writer) process writes only to datafiles whose tablespaces are in the read-write mode. Normal checkpoints occur on these files. The DBA can make an operating system backup of all the files associated with the read-only tablespace.


  1. System change number: System change number is a clock value for the Oracle database that describes a committed version of the database. It functions as a timestamp that helps to ensure transaction consistency.
  2. Database Writer: The Database Writer is responsible for writing modified blocks from the database buffer cache back to the database files.

Guidelines for read-only tablespace backup

  1. Only one backup is necessary after a tablespace is altered to read-only because no writes will be possible from that time forward. The only time the files will need to be recovered is if they become damaged.
  2. When you change the status of a tablespace from read-only back to read-write, this allows the DBWR to write to the tablespace files. Because the files can now be written to, you must perform normal scheduled backups for all the datafiles associated with that tablespace.

When you use the ALTER TABLESPACE command to change a tablespace to read-only, the control file is updated. It is important that the control file accurately identify read-only tablespaces when performing a recovery. If not, you will have to re-create the control file.
The next lesson concludes this module.

Read Only Backups - Quiz

Click the Quiz link below to review your understanding of more database backup concepts.
Read Only Backups - Quiz