Oracle instance status: OPEN, MOUNTED, RESTRICTED, and detecting a real outage

An Oracle instance that reports STATUS = OPEN and DATABASE_STATUS = ACTIVE is not necessarily serving production traffic. Several intermediate and degraded states look healthy to a naive health check while blocking user work: restricted mode left on after maintenance, a quiesce in progress, a resumable operation suspended on a space error, or a shutdown pending behind stuck sessions. Equally common is the opposite failure: a physical standby correctly reports READ ONLY WITH APPLY and gets paged as “down” because the check assumed READ WRITE.

This guide covers the columns that determine availability in production: V$INSTANCE (STATUS, DATABASE_STATUS, ACTIVE_STATE, LOGINS, SHUTDOWN_PENDING) and V$DATABASE (OPEN_MODE, DATABASE_ROLE). It also covers the per-service check in V$ACTIVE_SERVICES, because an instance can be fully open with the production service missing or blocked and still produce a real application outage.

The expected healthy state for a primary is OPEN, ACTIVE, NORMAL, ALLOWED, READ WRITE, DATABASE_ROLE = PRIMARY, with the expected services registered and not blocked. Anything else is either a real incident or a documented maintenance state that should be silenced.

What this means

Oracle exposes instance availability through several columns that together describe a state machine, not a single boolean. The minimal triad is:

• V$INSTANCE.STATUS - the lifecycle phase: STARTED (NOMOUNT), MOUNTED (control file open, datafiles closed), OPEN (datafiles open, users can be admitted), OPEN MIGRATE (opened with UPGRADE or DOWNGRADE).
• V$INSTANCE.DATABASE_STATUS - runtime condition on top of STATUS: ACTIVE (serving), SUSPENDED (a resumable operation is blocked on a space error), INSTANCE RECOVERY.
• V$INSTANCE.ACTIVE_STATE - quiesce state: NORMAL, QUIESCING (a QUIESCE RESTRICTED is waiting for non-DBA sessions to drain), QUIESCED.

To this triad you must add:

• V$INSTANCE.LOGINS - ALLOWED or RESTRICTED (only DBA connections admitted).
• V$INSTANCE.SHUTDOWN_PENDING - YES means a SHUTDOWN has been issued and the instance is draining.
• V$DATABASE.OPEN_MODE - MOUNTED, READ WRITE, READ ONLY, READ ONLY WITH APPLY.
• V$DATABASE.DATABASE_ROLE - PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY, SNAPSHOT STANDBY, FAR SYNC.

The only combination that is unambiguously “production primary serving work” is STATUS = OPEN, DATABASE_STATUS = ACTIVE, ACTIVE_STATE = NORMAL, LOGINS = ALLOWED, OPEN_MODE = READ WRITE, DATABASE_ROLE = PRIMARY, SHUTDOWN_PENDING = NO. Everything else is either a standby (where READ ONLY or READ ONLY WITH APPLY is correct), a maintenance state, or a real outage.

A single-column check on STATUS will miss most of the interesting failures. An instance with STATUS = OPEN and ACTIVE_STATE = QUIESCING is not serving normal users. An instance with STATUS = OPEN and LOGINS = RESTRICTED will accept only DBA connections and reject every application login. An instance with STATUS = OPEN and DATABASE_STATUS = SUSPENDED is wedged on a resumable space error and will not progress until the space condition is resolved.

Common causes

Quick checks

These are all read-only and safe to run during an incident.

-- Check instance state
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS, ACTIVE_STATE,
       LOGINS, SHUTDOWN_PENDING
FROM V$INSTANCE;

-- Check database open mode and role
SELECT NAME, OPEN_MODE, DATABASE_ROLE, PROTECTION_MODE,
       SWITCHOVER_STATUS
FROM V$DATABASE;

-- Verify the production services are registered and not blocked
SELECT NAME, NETWORK_NAME, BLOCKED
FROM V$ACTIVE_SERVICES
ORDER BY NAME;

-- Resumable operations currently suspended
SELECT USER_ID, SESSION_ID, NAME, STATUS, ERROR_MSG
FROM DBA_RESUMABLE;

# Listener-side view of registered services
lsnrctl status LISTENER

# Full admission path test (listener + service + handler + instance)
# Do not embed real credentials in the command line in production; use a wallet.
echo "exit" | sqlplus -L user/pass@//host:1521/service_name

# Tail the alert log for startup, quiesce, suspend, or shutdown messages
adrci exec="show alert -tail 100"

How to diagnose it

1. Run the V$INSTANCE and V$DATABASE checks first. Compare against the expected state for this database’s role. A primary must be OPEN/ACTIVE/READ WRITE; a physical standby is healthy at READ ONLY WITH APPLY.
2. If STATUS is not OPEN, stop. The instance is in a startup phase or never opened. Pull the alert log and look for ORA- errors around the most recent startup, then resolve the startup blocker before chasing anything else.
3. If STATUS = OPEN but DATABASE_STATUS = SUSPENDED, a resumable operation is blocked on space. Query DBA_RESUMABLE for the offender. Either add space, fix the quota, or abort the specific resumable session. Do not restart the instance; the suspend is doing its job and a restart loses the in-flight work.
4. If STATUS = OPEN but ACTIVE_STATE is QUIESCING or QUIESCED, someone issued ALTER SYSTEM QUIESCE RESTRICTED. Confirm with the change log. If it is not intentional, ALTER SYSTEM UNQUIESCE reverses it. Note that QUIESCING can persist for a long time if a long-running non-DBA session refuses to become inactive.
5. If STATUS = OPEN but LOGINS = RESTRICTED, only DBAs can connect. This is the classic “restricted mode left on after patching” failure. Confirm with V$INSTANCE.LOGINS and the maintenance log. ALTER SYSTEM DISABLE RESTRICTED SESSION reverses it without a restart.
6. If STATUS = OPEN, LOGINS = ALLOWED, and the state is otherwise normal, but applications still cannot connect, check V$ACTIVE_SERVICES. An instance can be fully open with the production service not registered, blocked, or running on a different instance in RAC.
7. If SHUTDOWN_PENDING = YES, a SHUTDOWN is in progress. Decide whether to let it finish or to cancel. Verify whether the shutdown is the planned one before doing anything disruptive.
8. Confirm the role. A database that reports DATABASE_ROLE = PHYSICAL STANDBY and OPEN_MODE = READ ONLY WITH APPLY is a healthy standby and must not page. A database that unexpectedly reports a standby role on what should be the primary is a switchover or failover event and needs a separate incident response.

The state machine below summarises the decision path.

flowchart TD
    A[STATUS column] -->|OPEN| B[OPEN_MODE column]
    A -->|STARTED or MOUNTED| Z[Startup phase: check alert log]
    B -->|READ WRITE| C[DATABASE_STATUS]
    B -->|READ ONLY or READ ONLY WITH APPLY| S[Check DATABASE_ROLE: standby is normal]
    C -->|ACTIVE| D[ACTIVE_STATE and LOGINS]
    C -->|SUSPENDED| R[Resumable space error: DBA_RESUMABLE]
    D -->|NORMAL and ALLOWED| E[Check V$ACTIVE_SERVICES]
    D -->|QUIESCING or QUIESCED| Q[ALTER SYSTEM UNQUIESCE]
    D -->|RESTRICTED| L[ALTER SYSTEM DISABLE RESTRICTED SESSION]
    E -->|Expected service present, not blocked| OK[Healthy primary]
    E -->|Missing or blocked| SV[Service-level outage]

Metrics and signals to monitor

A composite health check for a primary should require all of: STATUS = OPEN, DATABASE_STATUS = ACTIVE, ACTIVE_STATE = NORMAL, LOGINS = ALLOWED, OPEN_MODE = READ WRITE, DATABASE_ROLE = PRIMARY, SHUTDOWN_PENDING = NO, and the expected service registered and not blocked. For a physical standby, replace READ WRITE with READ ONLY WITH APPLY and do not alert on the difference.

Fixes

Restricted mode left on after maintenance

Symptom: LOGINS = RESTRICTED, application logins fail with ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege.

-- Reverse restricted mode without a restart
ALTER SYSTEM DISABLE RESTRICTED SESSION;

Then verify V$INSTANCE.LOGINS = ALLOWED and confirm with a real application connection. Investigate how the restricted session was enabled. Patching and some maintenance scripts start the instance in restricted mode and are supposed to disable it at the end; if they do not, file a runbook change so the disable step is explicit and verified.

For patching specifically, check the patch/RU runbook: some startup and migration procedures deliberately enable restricted mode and must leave it on until post-startup validation finishes. Do not disable RESTRICTED merely because it is set; verify that the maintenance procedure has completed first.

Quiesce in progress

Symptom: ACTIVE_STATE = QUIESCING or QUIESCED, non-DBA sessions cannot start new transactions.

-- Reverse an unintentional quiesce
ALTER SYSTEM UNQUIESCE;

QUIESCING does not become QUIESCED until all non-DBA sessions become inactive. A single long-running OLTP session can hold the quiesce in QUIESCING indefinitely. Identify the holding sessions before unquiescing if the quiesce was intentional; otherwise unquiesce and investigate who issued it.

Resumable operation suspended

Symptom: DATABASE_STATUS = SUSPENDED, one or more sessions are stuck mid-statement with no error returned.

-- Identify the suspended operation
SELECT USER_ID, SESSION_ID, NAME, STATUS, ERROR_MSG, TIMEOUT
FROM DBA_RESUMABLE;

Resolve the underlying condition (add space to the tablespace, raise the quota, fix the datafile). The suspended operation resumes automatically once the condition clears. Do not restart the instance; that loses in-flight work and the suspend is protecting correctness. If the operation must be aborted, kill the specific session, not the instance.

Shutdown pending

Symptom: SHUTDOWN_PENDING = YES, no new sessions admitted, existing sessions draining.

Decide whether the shutdown is the planned one. If yes, let it finish and verify the restart. If no, the only reliable way to cancel a SHUTDOWN is to kill the session that issued it before the shutdown reaches a phase where it cannot be interrupted. Once the shutdown is far enough along, the only path forward is to let it complete and restart the instance.

Database mounted but never opened

Symptom: STATUS = MOUNTED, OPEN_MODE = MOUNTED. The instance started and mounted the control file but never opened the datafiles.

Pull the alert log around the most recent startup. Common causes are an interrupted STARTUP (operator hit Ctrl-C), a missing or inaccessible datafile, or a DBA who intentionally left the database mounted for recovery. If the cause is resolved:

-- Only after the startup blocker is resolved and you understand why the
-- database was left mounted
ALTER DATABASE OPEN;

If the database was intentionally left mounted for a recovery operation, that is a maintenance state and should be silenced, not paged.

Service missing or blocked on an open instance

Symptom: instance is OPEN/ACTIVE/READ WRITE but the application cannot connect; lsnrctl status shows the production service missing or BLOCKED.

SELECT NAME, NETWORK_NAME, BLOCKED
FROM V$ACTIVE_SERVICES
ORDER BY NAME;

If the expected service is missing, LREG has not registered it with the listener. This can happen when the instance is under heavy load and LREG is delayed. Check whether the service was stopped intentionally, then force re-registration:

ALTER SYSTEM REGISTER;

In RAC, verify the service is supposed to run on this instance and review the service placement policy. A service marked BLOCKED = YES is accepting no new connections at all, which is distinct from restricted mode (restricted mode filters logins; a blocked service refuses them outright).

Prevention

• Make the composite state check the primary availability signal. Treat any deviation from OPEN/ACTIVE/NORMAL/ALLOWED/READ WRITE/PRIMARY as a page on a primary, and make the check role-aware so standbys do not page on READ ONLY.
• Make restricted mode a first-class signal. The number of post-maintenance outages caused by LOGINS = RESTRICTED left on after patching is large enough to deserve its own alert.
• Make service presence a first-class signal. The single most common “instance is up but the application is down” pattern is a missing or blocked service. Checking V$ACTIVE_SERVICES against an expected list catches it.
• Add the alert log to the check set. QUIESCE, SUSPENDED, SHUTDOWN, and restricted session changes all leave traces. The alert log is the universal correlation anchor.
• Run a write-path synthetic. A read-only SELECT 1 FROM DUAL can pass while the database is hung. A small INSERT/COMMIT/DELETE/COMMIT against a health check table exercises LGWR and catches hangs that pass every read-only check.
• Document maintenance states in the alerting system. Silence them explicitly instead of relying on operators to ignore pages.

How Netdata helps

• The Oracle collector surfaces V$INSTANCE.STATUS, DATABASE_STATUS, ACTIVE_STATE, LOGINS, and SHUTDOWN_PENDING as discrete dimensions, so the composite state check is a single alert condition rather than a bespoke script.
• V$DATABASE.OPEN_MODE and DATABASE_ROLE are reported alongside instance state, which lets you write role-aware alerts that do not page on a healthy READ ONLY WITH APPLY standby.
• Per-second collection catches short-lived state transitions that 60-second polling misses, including the window where a quiesce is in progress or a shutdown is pending.
• ML anomaly detection on session count, TPS, and active sessions flags the “instance is OPEN but no work is flowing” pattern without requiring a static threshold per workload.
• Correlating instance state with listener status, V$ACTIVE_SERVICES, alert log patterns, and TPS in a single view shortens the time between “page received” and “is this a real outage or a maintenance state.”
• Composite alerts can require the expected service to be present and not blocked on top of the instance state, eliminating the “up but unserviced” false negative.

The Oracle Database monitoring solution surfaces these signals with per-second collection and anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle ‘buffer busy waits’: hot blocks, sequence headers, and index leaf splits
• Oracle buffer cache hit ratio: the most misused metric in Oracle monitoring
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ‘cursor: pin S wait on X’: mutex contention on hot cursors
• Oracle ‘db file scattered read’: multiblock reads, full scans, and plan regressions
• Oracle ‘db file sequential read’: single-block index reads and buffer cache misses
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks