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

CauseWhat it looks likeFirst thing to check
Restricted mode left on after maintenanceSTATUS = OPEN, LOGINS = RESTRICTED, applications get ORA-01035SELECT LOGINS FROM V$INSTANCE; review the maintenance runbook
Quiesce in progressACTIVE_STATE = QUIESCING, non-DBA sessions drain slowlySELECT ACTIVE_STATE FROM V$INSTANCE; find who issued ALTER SYSTEM QUIESCE RESTRICTED
Resumable space errorDATABASE_STATUS = SUSPENDED, some sessions stuck mid-statementSELECT * FROM DBA_RESUMABLE
Shutdown pendingSHUTDOWN_PENDING = YES, no new work admittedCheck who issued SHUTDOWN and whether sessions are draining
Database never openedSTATUS = MOUNTED, OPEN_MODE = MOUNTEDAlert log for startup errors
Primary accidentally on standby roleDATABASE_ROLE = PHYSICAL STANDBY, OPEN_MODE = READ ONLYRole transition history, Data Guard broker
Service missing or blockedInstance fully open, application cannot connectV$ACTIVE_SERVICES for expected service names
Standby paged as downOPEN_MODE = READ ONLY WITH APPLY, alert fired expecting READ WRITEAlert threshold should be role-aware

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

SignalWhy it mattersWarning sign
V$INSTANCE.STATUSConfirms the instance lifecycle phaseAnything other than OPEN in production
V$INSTANCE.DATABASE_STATUSCatches suspended state from resumable errorsSUSPENDED instead of ACTIVE
V$INSTANCE.ACTIVE_STATECatches a quiesce that has not completedQUIESCING or QUIESCED
V$INSTANCE.LOGINSCatches restricted modeRESTRICTED instead of ALLOWED
V$INSTANCE.SHUTDOWN_PENDINGCatches a shutdown that has not drainedYES on a production primary
V$DATABASE.OPEN_MODECatches a database that was mounted but never openedMOUNTED on a primary
V$DATABASE.DATABASE_ROLEDisambiguates primary vs standby expectationsUnexpected role after a switchover
V$ACTIVE_SERVICES per serviceCatches service-level outage on an open instanceExpected service missing or BLOCKED = YES
Listener status and service registrationCatches the case where the instance is up but the listener cannot routelsnrctl status shows no services or all BLOCKED
TPS and active session countCorroborates that work is actually flowingSTATUS = OPEN but TPS near zero with load present

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.

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.