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

Backing up "read-only" tablespaces in Oracle 19c is necessary for several reasons, even though their contents do not change. Here’s why:
  1. Protection Against Data Loss
    • Even though the data in read-only tablespaces doesn't change, it's still possible to lose that data due to hardware failures, accidental deletions, or corruption. A backup ensures you can restore the tablespace and prevent permanent data loss.
  2. Database Recovery
    • In Oracle, during a recovery scenario, all tablespaces (including read-only ones) must be available and synchronized to a consistent point. If a read-only tablespace is missing or corrupted, recovery operations might fail, or the database might not open properly.
  3. Initial Backup for Restoration
    • When a tablespace is marked as read-only, you must back it up at least once after the change. This is because subsequent backups of the database won't include it unless explicitly specified. Without this initial backup, recovery processes would not have a valid baseline to restore the read-only data.
  4. Data Archival
    • Read-only tablespaces often store historical or archival data. Backups serve as an additional layer of security to preserve this critical information for compliance, auditing, or reporting purposes.
  5. Media Management
    • If you use RMAN (Recovery Manager), Oracle does not back up read-only tablespaces unless you explicitly include them. This feature allows you to optimize backup strategies, but it also means you need to take responsibility for creating and retaining an initial backup for those tablespaces.
  6. Avoid Data Reconstruction
    • If a read-only tablespace is lost and there’s no backup, reconstructing the data might be complex, time-consuming, or even impossible, depending on the data's nature and source.

Best Practices:
  • Initial Backup: Perform a full backup of the read-only tablespace immediately after marking it read-only.
  • Retention Policy: Retain backups of read-only tablespaces as long as they are needed for recovery purposes.
  • Regular Verification: Periodically verify the integrity of the backup to ensure it can be used in a recovery scenario.

Although the data in read-only tablespaces doesn't change, backing them up ensures the continuity and reliability of the database. This practice safeguards against unexpected incidents that could compromise the integrity or availability of the database.

Using read-only Tablespaces

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.
ALTER TABLESPACE
The image illustrates a read-only tablespace backup process in Oracle, and the components shown are essential elements of a database backup strategy. Here's a breakdown of the components:
  1. Control Files:
    • These files store metadata about the database, such as the structure, datafile locations, and log file information. They are crucial for starting the database and performing recovery operations.
    • In the image, control files are labeled with version or SCN (System Change Number) 73, indicating consistency at this point in time.
  2. Online Redo Logs:
    • Redo logs record all changes made to the database. Even though read-only tablespaces do not change, redo logs ensure recoverability of other parts of the database.
    • The version 74 suggests that the redo logs may have progressed further than the snapshot SCN of the control files, which is typical in an ongoing database.
  3. Data Files:
    • Data files are the physical storage files that contain the database's actual data. This includes all tablespaces, such as the system and user-defined tablespaces.
    • The data files are consistent with SCN 73, matching the control file version, which ensures recoverability in case of a restore.
  4. RO_Sample (Read-Only Tablespace):
    • This represents a specific tablespace marked as "read-only." Its data does not change, but it must still be backed up after being set to read-only to ensure it is available for recovery.
    • SCN 73 shows that the read-only tablespace is consistent with the control files and data files.
  5. System Tablespace:
    • The system tablespace contains essential database metadata and core dictionary information. It is always required for database operation and must be part of any backup process.
    • It is consistent with SCN 73, like the control files and data files.

Key Observations:
  • SCN Consistency: The majority of the components (control files, data files, and tablespaces) are at the same SCN 73, ensuring a recoverable state for the backup.
  • Progressing Redo Logs: The redo logs show a slightly advanced SCN (74), reflecting ongoing changes in the database that occur during the backup process. These logs ensure that all changes up to the current SCN can be recovered.

Backup Strategy:
  • After marking a tablespace as read-only, it is backed up alongside the control files, data files, and system tablespace. Redo logs are continuously backed up to ensure recovery up to the most recent state.
  • This image emphasizes the need to capture all essential components, even for read-only tablespaces, as they are critical for database integrity and recovery. 
SQL> ALTER TABLESPACE RO_SAMPLE READ ONLY;

Make a Physical Backup
The diagram illustrates the key components and process involved in a physical backup of an Oracle database, specifically highlighting the System Change Number (SCN) and its role in maintaining consistency across the backup. Here's an analysis of the components and process:
  1. Control Files:
    • Represented on the left with an SCN value of 73.
    • The control file stores metadata about the database, including the structure, data files, and log files.
    • During a backup, it ensures the database can track changes and maintain consistency.
  2. Online Redo Logs:
    • Positioned next to the control files with an SCN value of 74.
    • These logs capture all changes made to the database in real time.
    • Essential for recovery, they allow replaying transactions to restore the database to a consistent state.
  3. System Tablespace (File Header Frozen in Current SCN):
    • The system tablespace has an SCN value of 73 at the moment the backup is initiated.
    • The "file header is frozen" indicates that the backup captures the consistent state of the database as of this SCN.
    • This frozen state ensures transactional consistency.
  4. Data Files:
    • These are the primary storage for database data.
    • Initially at an SCN of 74, the SCN changes over time as transactions occur, moving to 80.
    • This change indicates that new transactions occurred after the initial backup point.
  5. Backup Process:
    • The backup involves taking a physical copy of the database files, starting with the system tablespace and data files.
    • The SCN values play a critical role in identifying a consistent point in time for recovery.
    • Even as data files are updated (SCN 80), the backup ensures a snapshot of the database is captured as of SCN 73 for recovery purposes.

Purpose:
  • This backup strategy ensures that the database can be restored to a consistent state, even if transactions continue during the backup process.
  • SCN is a vital marker for transactional consistency and recovery.
RO_SAMPLE: Make a Physical Backup - File Header is frozen in the current SCN.

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

Checkpoint is performed

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.

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

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

SEMrush Software