Oracle 26ai Read-Only Database and Standby Reporting

Opening an Oracle AI Database 26ai database read only lets applications query persistent data while preventing ordinary changes to that data in the opened database. This is useful for a planned reporting window, inspection of a prepared database copy, or access to a physical standby. The command is short, but its meaning depends on the database role and the container you are administering. A primary database, a pluggable database, and a physical standby require different operational decisions.

The previous lesson introduced physical standby creation and Data Guard management. This lesson explains how to make data available for queries and how that choice affects redo application. Read-only opening does not create a standby, initiate automatic failover, or configure Fast-Start instance recovery. Decide which database should serve the workload before selecting a startup command.

Choose the scope before changing the mode

Read-only choices and their scope

Choice	Scope	Operational consequence
Primary CDB opened read only	Container database	Applications cannot perform ordinary persistent data changes there.
Application PDB opened read only	Selected PDB	Other PDBs can remain read-write when their CDB permits it.
Read-only session	Current session	Other sessions retain their own access and write capabilities.
Physical standby with apply paused	Standby database	Queries use the applied data; incoming redo may accumulate.
Active Data Guard real-time query	Licensed physical standby	Queries and Redo Apply run together; monitor lag.

For the following whole-database examples, assume a prepared single-instance CDB, an administrative SQL*Plus connection to its root, and appropriate SYSDBA privileges. Confirm the instance environment and service before connecting. These examples illustrate procedures; their outputs are expected values rather than results from a running database.

A database managed by Oracle Clusterware needs its coordinated start, stop, and service procedures. Do not independently stop one RAC instance and assume the whole database has changed mode. Oracle prohibits opening a database read only while another instance has it open read-write. The 26ai ALTER DATABASE reference defines the opening restrictions.

Prepare a primary database for read-only access

Schedule the change with application owners. Identify scheduled jobs, connection pools, batch imports, and clients that expect to write. A reporting application may appear to issue only queries while its login routine updates an audit table or its initialization code creates a working table. Test the actual application path so the change does not produce a stream of unexpected write errors.

Check that the files belong to the intended database, that the parameter file and control files are usable, and that any required recovery has been completed. A restored copy cannot be made consistent simply by requesting a read-only open. If Oracle reports missing files or required recovery, diagnose that state using the appropriate restore and recovery procedure before continuing.

For an already running primary database, inspect its identity and current role:

SELECT name, db_unique_name, database_role, open_mode
FROM v$database;
SHOW CON_NAME

The expected role for this procedure is PRIMARY, and the whole-CDB administration connection should identify CDB$ROOT. When the instance is stopped, verify its identity through the prepared environment and inspect the database after mounting. Do not guess the role from a filename or the service's informal label.

Open the prepared primary CDB read only

If the database is currently open read-write, first stop application activity and perform the planned shutdown:

SHUTDOWN IMMEDIATE;

Wait for completion before starting the next operation. The two-stage procedure then starts the instance and mounts the database without opening it:

STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

The intermediate mounted state provides an opportunity to confirm identity and investigate an unexpected condition before allowing query access. If the database is already mounted and its prerequisites are satisfied, issue only the opening statement. If it is already stopped, omit the initial shutdown.

For a prepared stopped instance, the equivalent single-step SQL*Plus alternative is:

STARTUP OPEN READ ONLY;

Choose one startup route. Do not execute both alternatives consecutively against an already opened database. SQL*Plus handles STARTUP as a client command, while ALTER DATABASE is a SQL statement. Oracle documents the accepted startup options in the 26ai SQL*Plus STARTUP reference.

Verify the database and application container

SELECT database_role, open_mode
FROM v$database;

SELECT name, open_mode
FROM v$pdbs
ORDER BY name;

For the primary CDB example, expect PRIMARY and READ ONLY in V$DATABASE. The correct column is OPEN_MODE. It is not DATABASE_READ_MODE, and the returned mode uses spaces. Oracle's V$DATABASE reference defines the role and mode values.

Inspect the target application PDB separately. A read-only root does not prove that every application PDB is open and reachable. Where the target PDB is mounted and the CDB state allows it, open the named PDB read only using an appropriate root connection. Then check its service and connect as the reporting user. A successful administrator query against the root is not an application acceptance test.

Confirm a representative report returns the intended data. Use an account with the required query privileges and review failures from packages or application startup routines. Read-only mode still permits some state administration and temporary work; it does not mean the Oracle software performs no storage activity. Oracle's CDB administration guide also explains the temporary-tablespace requirements for queries that need sorts.

Return a primary database to read-write operation

End the read-only window deliberately. Notify reporting users, stop new connections through the planned service procedure, and finish or terminate remaining sessions according to the maintenance plan. For this single-instance primary CDB, use an orderly shutdown followed by a mounted startup and an explicit read-write open:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ WRITE;
SELECT database_role, open_mode FROM v$database;

Expect PRIMARY and READ WRITE. Review the application PDBs and services before restarting writers, schedulers, and batch jobs. Record the actual completion time and any errors. This sequence is a primary-database mode change, not a method of promoting a physical standby.

If only one application needs read-only access, a whole-CDB shutdown may be unnecessary. On a read-write primary CDB, an administrator connected to the root can change the named PDB's mode after coordinating its sessions. Here reportpdb is an example PDB name:

ALTER PLUGGABLE DATABASE reportpdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE reportpdb OPEN READ ONLY;
SELECT name, open_mode FROM v$pdbs WHERE name = 'REPORTPDB';

The close operation interrupts that PDB's sessions. Verify the exact name and scope before issuing it. To return the same PDB to normal operation, close it and reopen it read-write when its CDB and role permit that mode. Review restart behavior separately rather than assuming the temporary maintenance choice will persist as intended.

In RAC, PDB mode changes also require instance-aware planning. A statement affecting the current instance does not establish the requested mode everywhere the service might run. The 26ai PDB administration guide explains PDB connections, services, and open-mode changes.

Two narrower Oracle 26ai alternatives

Hybrid read-only mode distinguishes user categories. A mounted PDB can be opened with ALTER PLUGGABLE DATABASE reportpdb OPEN HYBRID READ ONLY;. Local users are read-only while common users can write. Select this mode only when that distinction matches the requirement; a task demanding all application data remain unchanged needs a different choice. See Oracle's HYBRID_READ_ONLY reference.

For a current session, Oracle 26ai provides ALTER SESSION SET READ_ONLY=TRUE;. This limits that session without changing the container's open mode. Other sessions can continue writing. It is useful for session-specific access behavior, but does not freeze a database for everyone or establish the same snapshot across all reports. Oracle documents the scope in the READ_ONLY parameter reference.

A Data Guard physical standby^[1] offers a separate database for reporting. Opening it for queries keeps its role as PHYSICAL STANDBY. It does not make it a primary or remove the need for redo transport and recovery management.

The decisive distinction is whether Redo Apply can run while queries are active. Without an Oracle Active Data Guard entitlement for real-time query, stop apply before opening the physical standby. With the appropriate licensed option, queries can run while apply updates the standby. This condition is stated in Oracle's physical standby management guide.

Transport and apply are separate stages. A standby may receive redo while apply is stopped, but the reports see only changes already applied. Allowing transport to continue therefore preserves incoming recovery material without making the queried data current. Record the data freshness at the start of the reporting window and make that limitation visible to report consumers.

Reporting with apply paused

The following SQL example assumes a configured, mounted, single-instance physical standby under direct SQL administration, with the required files and redo available. If managed recovery is active, stop it before opening:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
SELECT database_role, open_mode FROM v$database;

If apply is already stopped, omit the cancellation command. If the standby instance is stopped, start it mounted before the opening step. Expect PHYSICAL STANDBY and READ ONLY. Inspect the application PDB and reporting service as well.

Keep apply stopped during the unlicensed real-time-query case. Set a reporting-window limit and provide enough space for received redo. Plan for catch-up after reports finish: a long pause can extend the time required to bring the standby current. Treat the pause as a change to recovery readiness, even when the primary application remains available.

To return this directly administered single-instance standby to mounted recovery, end reporting sessions and use:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  DISCONNECT FROM SESSION;

Confirm the mounted standby role, inspect recovery progress, and monitor lag until it meets the operating target. Retaining the physical standby and required redo allows normal catch-up; simply using read-only reporting does not by itself require a new clone.

Licensed real-time query with apply running

For a licensed Active Data Guard deployment, start from the standby that has been opened read only after apply was stopped. Restart apply on that open instance:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  DISCONNECT FROM SESSION;
SELECT database_role, open_mode FROM v$database;

The expected mode becomes READ ONLY WITH APPLY, and the role remains PHYSICAL STANDBY. This avoids a scheduled apply pause, but it does not establish zero lag or a guarantee that every query sees the latest primary commit.

These SQL sequences are alternatives to a broker-managed operating procedure. When Data Guard broker owns the configuration, use its intended-state controls, coordinate opening with those controls, and verify the resulting state through broker and database views. Do not issue competing apply changes independently and assume they will remain in effect.

Check standby freshness throughout the reporting window, not just immediately after startup. Run the following query on the standby:

SELECT name, value, unit, datum_time, time_computed
FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag', 'apply finish time');

Transport lag concerns delivery; apply lag concerns visibility of source changes. Read the timestamps alongside the values. An unchanged DATUM_TIME across observations indicates that fresh source data is not arriving, so a reassuring old lag value can mislead. This view returns no rows on a primary database. See the 26ai metric definitions.

Set a freshness requirement for each workload. A historical monthly report may tolerate a pause that a current inventory dashboard cannot. Also set recovery targets independently: acceptable report age does not establish acceptable data loss or failover time. Alert on stale observations, unavailable destinations, storage pressure, and apply failures.

Budget CPU, memory, disk bandwidth, and temporary space for reporting together with recovery work. Test peak workloads and the return-to-apply period. Reports that compete heavily for resources can slow catch-up or make an emergency role transition harder to complete within the expected time.

Maintain file configuration deliberately. Modern physical standby file management can automate supported primary file changes when configured correctly, so blanket instructions to copy every structural change manually are obsolete. However, path mapping, storage capacity, and changes requiring manual intervention still need review. Oracle's STANDBY_FILE_MANAGEMENT reference warns that automatic file creation can overwrite existing files if destinations are configured incorrectly.

Use role-aware services so reporting clients reconnect to an appropriate database after a planned switchover or incident. Test authentication, PDB availability, representative queries, and connection behavior after the transition. A database that opens successfully is only one part of restoring an application.

Keep backup and restore verification in the recovery plan. Read-only access does not create a backup, validate a backup set, or remove the need for historical recovery points. Likewise, a standby can receive unwanted primary changes. Choose protection against user errors, corruption, and site outages through the combined recovery design rather than treating one open mode as complete protection.

Complete the read-only operation

1. Confirm the database role, administrative container, intended scope, and application service.
2. Coordinate sessions and use the appropriate primary, PDB, or standby procedure.
3. Verify the database mode and the application PDB through database views.
4. For standby reporting, establish apply policy, licensing requirements, freshness targets, and storage capacity.
5. Test application access, monitor the window, and verify the planned return to write access or redo application.

The central skill is matching the requested read-only scope to the database role and recovery policy. Continue to the Fast-Start Fault Recovery conclusion to review how instance recovery, standby protection, and application availability fit together.

[1] Physical standby: a Data Guard replica maintained through redo transport and Redo Apply; opening it for queries does not change its database role.