DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01123 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01123: Cannot Start Online Backup; Media Recovery Not Enabled

ORA-01123 occurs when you attempt to perform an online (hot) backup on an Oracle database that is running in NOARCHIVELOG mode. Online backups require media recovery to be enabled, which means the database must be operating in ARCHIVELOG mode. Without archived redo logs, Oracle cannot guarantee data consistency during a live backup, so the operation is immediately rejected.

Top 3 Causes

1. Database Running in NOARCHIVELOG Mode

This is the root cause in nearly all cases. When a database is in NOARCHIVELOG mode, Oracle does not archive redo log files, making media recovery impossible.

-- Check current archive log mode
SELECT NAME, LOG_MODE
FROM   V$DATABASE;

-- If result shows NOARCHIVELOG, you must switch modes
-- Expected output for healthy setup: LOG_MODE = 'ARCHIVELOG'
Enter fullscreen mode Exit fullscreen mode

2. Incorrect Target Instance in Backup Scripts

In multi-instance or RAC environments, backup scripts may inadvertently connect to an instance that is in NOARCHIVELOG mode, triggering ORA-01123.

-- Verify instance and archive mode together
SELECT I.INSTANCE_NAME,
       I.HOST_NAME,
       D.LOG_MODE
FROM   V$INSTANCE I,
       V$DATABASE D;

-- Confirm archiving is actively working
SELECT DEST_ID, STATUS, TARGET, ARCHIVER
FROM   V$ARCHIVE_DEST
WHERE  STATUS = 'VALID';
Enter fullscreen mode Exit fullscreen mode

3. Incomplete ARCHIVELOG Mode Switch

A DBA may have issued the ALTER DATABASE ARCHIVELOG command but failed to restart the database in MOUNT state first, leaving the database still in NOARCHIVELOG mode.

-- Verify if archiving is actually enabled post-change
SELECT LOG_MODE, ARCHIVER
FROM   V$DATABASE, V$INSTANCE;

-- Check archive log generation to confirm mode is active
SELECT COUNT(*) AS ARCHIVE_COUNT
FROM   V$ARCHIVED_LOG
WHERE  FIRST_TIME >= SYSDATE - 1/24;
-- Count of 0 on an active database may indicate archiving is not working
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Follow these steps to resolve ORA-01123 immediately:

-- Step 1: Shut down the database cleanly
SHUTDOWN IMMEDIATE;

-- Step 2: Start in MOUNT mode (do not open)
STARTUP MOUNT;

-- Step 3: Enable ARCHIVELOG mode
ALTER DATABASE ARCHIVELOG;

-- Step 4: Open the database
ALTER DATABASE OPEN;

-- Step 5: Confirm the change
SELECT NAME, LOG_MODE
FROM   V$DATABASE;
-- LOG_MODE should now show 'ARCHIVELOG'

-- Step 6: Set archive destination (recommended)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/arch' SCOPE=BOTH;

-- Step 7: Test online backup
ALTER TABLESPACE USERS BEGIN BACKUP;
-- Perform OS-level file copy here
ALTER TABLESPACE USERS END BACKUP;
Enter fullscreen mode Exit fullscreen mode

Note: Always use RMAN for production backups instead of manual BEGIN/END BACKUP commands. RMAN handles archiving coordination automatically.

-- Recommended: RMAN full online backup
-- Run from RMAN prompt:
-- BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-- Verify no tablespace is stuck in backup mode
SELECT FILE#, STATUS, CHANGE#
FROM   V$BACKUP
WHERE  STATUS = 'ACTIVE';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Include archive mode verification in all deployment checklists. Before any production deployment or database cloning, always query V$DATABASE to confirm LOG_MODE = 'ARCHIVELOG'. Automate this check in your monitoring scripts to alert DBAs immediately if NOARCHIVELOG mode is detected.

  2. Standardize on RMAN for all backup operations. RMAN proactively validates prerequisites including archive mode before executing any backup job, reducing human error. Schedule regular BACKUP VALIDATE DATABASE runs and monitor V$RMAN_BACKUP_JOB_DETAILS to maintain a healthy backup history.

-- Monitor backup job health
SELECT SESSION_KEY,
       INPUT_TYPE,
       STATUS,
       START_TIME,
       END_TIME,
       ELAPSED_SECONDS
FROM   V$RMAN_BACKUP_JOB_DETAILS
ORDER  BY START_TIME DESC
FETCH FIRST 10 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01145 – Raised when an offline immediate operation is denied in NOARCHIVELOG mode; closely related to archive configuration issues.
  • ORA-00257 – Archiver error due to full archive log destination disk; commonly encountered after switching to ARCHIVELOG mode without adequate disk planning.
  • ORA-01152 – File was not restored from a sufficiently old backup; appears in media recovery chains alongside ORA-01123.

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