Oracle ORA-16003 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-16003: Standby Database Is Restricted to Read-Only Access

ORA-16003 occurs when a write operation (DML or DDL) is attempted on an Oracle Data Guard standby database. Standby databases are designed to receive and apply redo data from the primary, making them inherently read-only by architecture. Any attempt to modify data directly on the standby violates Data Guard's synchronization model and is immediately blocked by Oracle.

Top 3 Causes

1. Directly Executing DML/DDL on the Standby

The most common cause is a developer or application accidentally connecting to the standby and running INSERT, UPDATE, DELETE, or DDL statements.

-- First, always verify which database you are connected to
SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE
FROM V$DATABASE;

-- Example output indicating a standby:
-- NAME  DB_UNIQUE_NAME  DATABASE_ROLE     OPEN_MODE
-- ORCL  ORCL_STB        PHYSICAL STANDBY  READ ONLY WITH APPLY

-- Any DML attempt on a standby will produce ORA-16003:
-- INSERT INTO employees VALUES (9999, 'Test', 'IT', SYSDATE);
-- ERROR at line 1: ORA-16003: standby database is restricted to read-only access

2. Application Not Distinguishing Primary from Standby

When connection pools or load balancers route write requests to both primary and standby endpoints without role awareness, write requests inevitably land on the standby.

-- Check role-based services configured in the environment
SELECT NAME, NETWORK_NAME, ROLE
FROM DBA_SERVICES
WHERE ROLE IN ('PRIMARY', 'PHYSICAL_STANDBY');

-- Verify active sessions on standby hitting write operations
SELECT s.SID, s.USERNAME, s.PROGRAM, q.SQL_TEXT, q.COMMAND_TYPE
FROM V$SESSION s
JOIN V$SQL q ON s.SQL_ID = q.SQL_ID
WHERE q.COMMAND_TYPE IN (2, 6, 7)  -- 2=INSERT, 6=UPDATE, 7=DELETE
AND s.STATUS = 'ACTIVE';

3. Stale Connections After Switchover or Failover

After a role transition (switchover or failover), applications that still hold connections to the old primary — now a standby — will fail with ORA-16003 when they attempt writes.

-- Check switchover readiness on the current primary
SELECT SWITCHOVER_STATUS, DATABASE_ROLE FROM V$DATABASE;

-- Perform a controlled switchover (run on current primary)
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- Complete switchover on the new primary (old standby)
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE OPEN;

Quick Fix Solutions

Step 1 — Confirm the database role before doing anything else:

SELECT DATABASE_ROLE, OPEN_MODE, DB_UNIQUE_NAME FROM V$DATABASE;

Step 2 — Redirect your writes to the correct primary:

-- Connect to primary using the correct TNS alias or service
-- Example using SQLPlus:
-- sqlplus user/password@PRIMARY_WRITE_SERVICE

-- Verify the target is truly the primary
SELECT DATABASE_ROLE FROM V$DATABASE;
-- Expected: PRIMARY

Step 3 — Create and use role-based services to prevent future misrouting:

-- Create a write service tied to the PRIMARY role
BEGIN
  DBMS_SERVICE.CREATE_SERVICE(
    service_name => 'APP_WRITE',
    network_name => 'APP_WRITE'
  );
END;
/

EXEC DBMS_SERVICE.START_SERVICE('APP_WRITE');

-- Create a read-only service for the standby
BEGIN
  DBMS_SERVICE.CREATE_SERVICE(
    service_name => 'APP_READ',
    network_name => 'APP_READ'
  );
END;
/

Prevention Tips

Use Role-Based Services with Data Guard Broker
Configure PRIMARY and PHYSICAL_STANDBY role-based services so that during any switchover or failover, Oracle automatically starts the correct service on the correct node. Configure your application's write connection pool to use only the PRIMARY service endpoint, and route read-only queries to the PHYSICAL_STANDBY service. Enable Fast Start Failover (FSFO) combined with Oracle Notification Service (ONS) for seamless automatic reconnection after role transitions.
Implement a Post-Switchover Runbook
Every role transition must be followed by a standardized checklist: update TNS aliases, refresh JDBC connection strings, recreate DB links pointing to the new primary, and validate all batch jobs target the correct database. Automate this validation with a monitoring script that queries V$DATABASE.DATABASE_ROLE across all nodes and alerts on any unexpected role state. This eliminates the human error factor that causes most ORA-16003 occurrences after planned maintenance.

Related Errors

ORA-16000 — Database opened in read-only mode; often appears alongside ORA-16003.
ORA-01109 — Database not open; occurs when the standby is in mount state.
ORA-16401 — DML blocked by Data Guard Broker policy on a standby database.
ORA-16826 — Fast Start Failover configuration mismatch; commonly co-occurs with ORA-16003 during failover scenarios.

📖 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.

DEV Community