Recovery Issues with Read-Only Tablespaces in Oracle 26ai

What Read-Only Status Means for Recovery

A tablespace is a logical storage unit backed by datafiles. Its files contain segments for objects such as tables and indexes. An online read-only tablespace allows queries while preventing ordinary modifications to its data. An offline tablespace is unavailable for normal access. These are different conditions.

A backup taken after the transition to READ ONLY completed can provide a restore-only recovery path when the tablespace has remained read-only and the applicable control-file conditions are satisfied. By contrast, a backup taken during an earlier writable period may need recovery. An older read-only backup also needs recovery if the tablespace subsequently became writable.

Consequently, it is incorrect to say that redo can never be applied to a read-only tablespace's restored files. Recovery depends on the changes needed to bring those files to the required state. The absence of ordinary data changes during one read-only interval does not erase earlier or later recovery requirements.

Identify the Control File You Are Using

The control file records database structure, datafile information, redo information, and recovery metadata. A current control file and an older backup can describe different points in the database's history. Recreating a control file introduces another situation because its initial file inventory comes from the reconstruction statement.

In a multitenant database, the control files serve the CDB. A PDB does not have a separate control file that you recreate independently. Perform database-level control-file work from the CDB root with the required administrative privileges, and identify the owning container before handling a tablespace or datafile.

For an authorized SQL*Plus session connected to the root while the database is mounted or open, begin with:

SELECT name, controlfile_type, open_mode, log_mode
FROM v$database;

SELECT con_id, file#, name, status
FROM v$datafile
ORDER BY con_id, file#;

Compare the results with the recovery plan and known file inventory. A recorded filename does not prove that the operating system or ASM can access the file. Investigate storage errors before replacing or renaming anything.

Using a Backup Control File with Read-Only Tablespaces

Keep SQL*Plus and RMAN Procedures Distinct

In a user-managed SQL*Plus procedure, the following illustrates recovery using an already restored and mounted backup control file:

RECOVER DATABASE USING BACKUP CONTROLFILE;

This command does not restore the control file or datafiles. Required restores, file handling, and recovery prerequisites must already have been addressed. It is SQL*Plus syntax, not an RMAN command.

RMAN uses its own control-file restoration and recovery commands. After recovery with a restored backup control file, the primary database must be opened with RESETLOGS, even when recovery applied all required redo. This differs from ordinary complete recovery using the current control file. See Oracle's RMAN RECOVER reference.

RESETLOGS creates a new database incarnation. It does not reset database SCNs to 1, and it does not automatically make every earlier backup unusable. Future recovery still depends on sufficient backup, redo, and incarnation metadata.

Preparing for Control-File Recreation

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Read-Only Files and MISSING Entries

Section 37.3.2 of the Oracle recovery guide describes special handling of read-only files during control-file recreation. Omit those read-only files from the CREATE CONTROLFILE statement so recovery can skip them. This procedure assumes their recovery requirements have been evaluated; files restored from an earlier writable period can still need recovery.

When Oracle reconciles the control-file inventory with the data dictionary, a file present in the dictionary but omitted from the reconstruction statement can receive a name such as MISSING00005. This is a placeholder in metadata. It does not by itself mean the physical file was deleted.

At the documented stage after the database opens, associate each applicable read-only placeholder with its verified file using ALTER DATABASE RENAME FILE. Before doing so, establish:

• The absolute file number, tablespace, and owning container.
• The exact placeholder name recorded by Oracle, including any path.
• The actual file's identity, location, accessibility, and state history.
• Whether the file is a usable surviving read-only file or needs restoration and recovery.

A metadata rename changes the recorded filename. It does not copy data, repair corruption, or apply redo. Replacing a placeholder with the path of an unrelated file is not recovery. Similarly, bringing a tablespace online cannot compensate for missing changes in a restored file.

If a file is absent or damaged, use the appropriate restore and recovery procedure. If it was intentionally left offline, verify that its recovery and state requirements have been met before restoring access. The special read-only reconstruction procedure should not be generalized to every file whose name begins with MISSING.

How State Changes Affect Backup Usability

Skipping Restoration Is a Separate Decision

RMAN supports RESTORE DATABASE SKIP READONLY to exclude read-only files from a restore operation. That exclusion neither repairs missing files nor guarantees they will be available when applications resume. A staged restoration needs an explicit plan for the excluded files.

Do not add the same clause to RECOVER DATABASE: SKIP READONLY is a RESTORE option, not an equivalent RECOVER option. Consult the RESTORE reference when deciding what to restore. Keep that decision separate from the recovery requirements of each file.

Verify Files, Containers, and Application Access

After recovery, review the recovery output and alert log for unresolved errors. From an authorized CDB-root session at the appropriate mounted or open stage, inspect affected file headers:

SELECT con_id, file#, status, recover, fuzzy, error, name
FROM v$datafile_header
ORDER BY con_id, file#;

Use the results with the known file inventory and completed recovery procedure. Header information alone is not a complete corruption check. Also, V$RECOVER_FILE is unreliable when the control file was restored or recreated, so an empty result is not proof that recovery succeeded.

Once the owning PDB is open, connect to that PDB and check the expected tablespace state. For the example tablespace:

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = 'EMP_HISTORY';

This query describes current status, not every past transition. Verify that the expected CDB and PDBs are open, required files are accessible, and application queries can read the recovered data. Resolve unexpected offline files before declaring the application available.

Finally, update the recovery record with the control file used, restored files, completed recovery, and any incarnation change. Refresh backups as required by the recovery plan and ensure another administrator can identify the files and metadata needed for the next incident.

The next lesson concludes this module.