DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-16000 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-16000: Database Open for Read-Only Access

ORA-16000 is an Oracle error that occurs when a user attempts a write operation (INSERT, UPDATE, DELETE, DDL) on a database that has been opened in read-only mode. This is most commonly encountered in Oracle Data Guard environments when developers or applications accidentally connect to a Physical Standby database instead of the Primary. Understanding why this happens and how to resolve it quickly is essential for any Oracle DBA or developer.

Top 3 Causes

1. Accidentally Connected to a Data Guard Standby Database

This is the number one cause in production environments. Physical Standby databases in a Data Guard configuration are opened as READ ONLY or READ ONLY WITH APPLY (Active Data Guard). Any write attempt results in ORA-16000.

-- Check if you're connected to a Standby
SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE
FROM V$DATABASE;

-- Example output indicating Standby:
-- DATABASE_ROLE    : PHYSICAL STANDBY
-- OPEN_MODE       : READ ONLY WITH APPLY
Enter fullscreen mode Exit fullscreen mode

Fix: Disconnect and reconnect using the Primary database connection string (TNS alias).

2. Database Explicitly Opened in READ ONLY Mode

A DBA may have intentionally opened the database in read-only mode for auditing, data verification, or snapshot analysis using ALTER DATABASE OPEN READ ONLY.

-- Check current open mode
SELECT OPEN_MODE FROM V$DATABASE;

-- To switch back to READ WRITE:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ WRITE;

-- Verify the change
SELECT NAME, OPEN_MODE FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

⚠️ Warning: Never run this on a Standby database — it will break Data Guard synchronization.

3. PDB (Pluggable Database) Opened as READ ONLY

In Oracle 12c+ Multitenant environments, an individual PDB can be in READ ONLY mode even when the CDB is fully open for read-write operations.

-- Check all PDB open modes from CDB$ROOT
SELECT CON_ID, NAME, OPEN_MODE
FROM V$PDBS
ORDER BY CON_ID;

-- Reopen specific PDB as READ WRITE
ALTER PLUGGABLE DATABASE your_pdb_name CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE your_pdb_name OPEN READ WRITE;

-- Persist the state across restarts
ALTER PLUGGABLE DATABASE your_pdb_name SAVE STATE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use this diagnostic script first whenever ORA-16000 is encountered:

-- Universal ORA-16000 diagnostic query
SELECT
    d.NAME              AS db_name,
    d.DATABASE_ROLE     AS role,
    d.OPEN_MODE         AS open_mode,
    d.PROTECTION_MODE   AS protection_mode,
    i.HOST_NAME         AS host
FROM V$DATABASE d, V$INSTANCE i;
Enter fullscreen mode Exit fullscreen mode
Scenario Fix
Connected to Standby Reconnect to Primary DB
DB opened READ ONLY Restart with OPEN READ WRITE
PDB is READ ONLY ALTER PLUGGABLE DATABASE ... OPEN READ WRITE

Prevention Tips

1. Standardize TNS Naming for Primary vs. Standby

Use clear, distinct service names such as MYDB_PRW (read-write, Primary only) and MYDB_RO (read-only, Standby). Create a dedicated service on the Primary that fails over automatically with Data Guard Broker so applications always connect to the correct database.

-- Create a Primary-only write service
EXEC DBMS_SERVICE.CREATE_SERVICE('MYDB_WRITE', 'MYDB_WRITE');
EXEC DBMS_SERVICE.START_SERVICE('MYDB_WRITE');
Enter fullscreen mode Exit fullscreen mode

2. Automate Open Mode Monitoring

Schedule a monitoring job to alert DBAs immediately if the database open mode changes unexpectedly from READ WRITE.

-- Use this in a scheduled monitoring script or OEM custom metric
SELECT
    CASE WHEN OPEN_MODE != 'READ WRITE'
         THEN 'ALERT: DB is ' || OPEN_MODE
         ELSE 'OK'
    END AS status,
    NAME, OPEN_MODE, DATABASE_ROLE
FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

Related Oracle Errors

  • ORA-16004 — Backup database requires recovery; common in Data Guard setups alongside ORA-16000.
  • ORA-01219 — Database not open; can appear during incorrect open mode transitions.
  • ORA-01552 — Cannot use system rollback segment on read-only database.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)