DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01557 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01557: Cannot Create Rollback Segment, Tablespace Must Be Online

ORA-01557 is an Oracle database error that occurs when attempting to create or bring online a rollback segment whose associated tablespace is in an OFFLINE or inaccessible state. This error is most commonly encountered during database startup or when manually managing rollback segments under UNDO_MANAGEMENT=MANUAL configuration. Understanding the root cause quickly is critical, as this error can prevent your database from fully opening.

Top 3 Causes and Fixes

Cause 1: Tablespace Is in OFFLINE State

The most common cause. If the tablespace holding the rollback segment was taken offline for maintenance and not brought back online before a restart, this error will trigger at startup.

Diagnose and Fix:

-- Check tablespace status
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE contents IN ('UNDO', 'PERMANENT');

-- Check rollback segment location
SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs;

-- Bring the tablespace online
ALTER TABLESPACE RBS_TS ONLINE;

-- If the DB is in MOUNT state, check datafile status first
SELECT file#, name, status
FROM v$datafile;

-- Bring datafile online from MOUNT state
ALTER DATABASE DATAFILE '/oracle/oradata/RBS01.DBF' ONLINE;
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Missing or Corrupt Datafile

If the physical datafile belonging to the rollback segment tablespace has been deleted or corrupted at the OS level, Oracle cannot bring the tablespace online, triggering ORA-01557.

Diagnose and Fix:

-- Check for datafile issues from MOUNT state
SELECT file#, name, status
FROM v$datafile
WHERE status != 'ONLINE';

-- Restore and recover using RMAN
-- Run inside RMAN console:
RESTORE DATAFILE '/oracle/oradata/RBS01.DBF';
RECOVER DATAFILE '/oracle/oradata/RBS01.DBF';

-- After recovery, bring online
ALTER DATABASE DATAFILE '/oracle/oradata/RBS01.DBF' ONLINE;
ALTER TABLESPACE RBS_TS ONLINE;
ALTER DATABASE OPEN;

-- If rollback segment data is expendable, drop and recreate
ALTER ROLLBACK SEGMENT RBS1 OFFLINE;
DROP ROLLBACK SEGMENT RBS1;

CREATE ROLLBACK SEGMENT RBS1
  TABLESPACE RBS_TS
  STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 2 MAXEXTENTS UNLIMITED);

ALTER ROLLBACK SEGMENT RBS1 ONLINE;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Incorrect ROLLBACK_SEGMENTS Parameter

If the ROLLBACK_SEGMENTS parameter in your init.ora or spfile references a segment name that no longer exists or belongs to a different tablespace, Oracle will fail to create it at startup.

Diagnose and Fix:

-- Check current parameter value
SHOW PARAMETER rollback_segments;

-- Update spfile with correct rollback segment names
ALTER SYSTEM SET ROLLBACK_SEGMENTS = ('RBS1', 'RBS2') SCOPE=SPFILE;

-- Better yet, migrate to Automatic Undo Management (Oracle 9i+)
ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1 SCOPE=SPFILE;

-- Verify undo tablespace after restart
SELECT tablespace_name, status, contents
FROM dba_tablespaces
WHERE contents = 'UNDO';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Switch to Automatic Undo Management (AUM): On Oracle 9i and above, set UNDO_MANAGEMENT=AUTO to eliminate manual rollback segment management entirely. This removes the primary source of ORA-01557 errors.

  • Pre-startup validation checklist: Before restarting a database, always verify that all tablespaces are online, all datafiles exist at the OS level, and your parameter file references valid, existing rollback segments. A simple shell script or SQL check can save significant downtime.

-- Quick pre-startup health check
SELECT d.name AS datafile,
       d.status,
       t.name AS tablespace
FROM v$datafile d
JOIN v$tablespace t ON d.ts# = t.ts#
WHERE d.status != 'ONLINE'
ORDER BY t.name;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01534 Rollback segment not found — often accompanies ORA-01557 when parameter names are wrong
ORA-01545 Rollback segment unavailable — similar context to ORA-01557
ORA-30012 AUM undo tablespace offline — the AUM equivalent of ORA-01557

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