Necessity to Backup Read-only Tablespace

As an Oracle Database Administrator (DBA), it is imperative to understand the importance of backing up read-only tablespaces in Oracle 13c. Despite the non-changing nature of data within read-only tablespaces, there are compelling reasons to include them in your backup strategy:

1. Recovery Consistency: Even though a read-only tablespace does not undergo modifications, it is a part of the database that must be consistent with the rest of the data during recovery. In a scenario where a database must be recovered to a specific point in time, all tablespaces, including read-only, need to be restored to ensure the database is consistent. Failing to back up a read-only tablespace might prevent a complete recovery.
2. Datafile Corruption: Read-only tablespaces are not immune to physical corruption. Issues such as disk failures, accidental file deletions, or other hardware-related problems can cause corruption. Backups are essential for restoring datafiles should they become corrupt.
3. Database Duplication: If there is a need to duplicate the database, for instance, for testing or development purposes, having a backup of read-only tablespaces is essential. Without the backup, you cannot create a fully functional duplicate as the read-only data will be missing.
4. Transportation and Migration: Read-only tablespaces are often used to transport data between databases or as part of data migration strategies. A consistent backup of these tablespaces is necessary to facilitate the transport process and ensure that the tablespaces can be attached to the target database without issues.
5. Standby Databases: In an environment with standby databases, such as those configured for Data Guard, it is important to back up read-only tablespaces. These backups are necessary to maintain synchronization with the primary database, allowing for a failover or switch-over without data inconsistency.
6. Architectural Compliance: Regulatory compliance and internal data governance policies may mandate the backing up of all database components, regardless of their state. This includes read-only tablespaces to ensure that there is a recovery point available for every aspect of the database.
7. Full Database Restore: In the event of a full database restore, the absence of backups for read-only tablespaces would result in an incomplete restoration, potentially violating data integrity and compliance requirements.
8. Longevity and Immutability: Although the data in a read-only tablespace does not change, the importance of that data may persist over time. Backups serve as a long-term preservation mechanism to safeguard against unforeseen events.

In conclusion, the practice of backing up read-only tablespaces in Oracle 13c is a judicious element of a comprehensive backup strategy, ensuring data protection, recovery consistency, and adherence to best practices and policies in database administration.

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.

SQL> ALTER TABLESPACE RO_SAMPLE READ ONLY;

SQL> 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^[1] ) and the DBWR(Database Writer ^[2] ) 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. 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.

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

[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.