Resolving ORA-01153 in Oracle Data Guard: Managed Standby Recovery Troubleshooting (Real-Time DBA Scenario)

Introduction

In an Oracle Data Guard environment, maintaining a healthy standby database is critical for disaster recovery and high availability. One of the common issues DBAs encounter during managed recovery operations is:

ORA-01153: an incompatible media recovery is active

This blog explains a real-time troubleshooting scenario, the root cause, and the correct approach to resolve this issue.

Environment Details

Oracle Version: 19c (19.25)
Database Role: Physical Standby
Host: livedbcs-dr
Database Name: CDBlive

Understanding the Problem

In a physical standby database, redo logs from the primary are applied using the background process called:

MRP (Managed Recovery Process)

ORA-01153 occurs when:

A recovery session is already active
Previous recovery was not properly terminated
Multiple recovery commands are executed

Step-by-Step Troubleshooting

1. Connect to Database

[opc@LIVEdbcs-dr ~]$ sudo su - oracle
Last login: Fri Apr 3 17:47:02 IST 2026
[oracle@livedbcs-dr ~]$ sqlplus "/as sysdba"

2. Verify Host and Database Status

SQL> !hostname
livedbcs-dr

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

CDBlive MOUNTED

Database is in MOUNTED mode (expected for standby)

3. Attempt to Start Managed Recovery

SQL> alter database recover managed standby database disconnect from session;

ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Root Cause Analysis

This error indicates that Oracle detects an already active or inconsistent recovery session.

To verify recovery processes:

SQL> select process, status, thread#, sequence# from v$managed_standby;

Interpretation

MRP0 → Managed Recovery Process
APPLYING_LOG → Recovery active
WAIT_FOR_LOG → Waiting for logs
IDLE → Session exists but not active

Even if recovery appears stopped, Oracle may still consider it active internally.

Resolution Steps

Step 1: Cancel Existing Recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 2: Restart Managed Recovery

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Step 3: Monitor Log Apply Status

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


Thread Last Sequence Received Last Sequence Applied Difference


     1                2032364               2032343         21


Thread Last Sequence Received Last Sequence Applied Difference


     1                2032366               2032348         18

Logs are being applied and lag is reducing

Note on Database Restart

In some cases, DBAs may perform:

SQL> shut immediate;
SQL> startup mount;

However, this is not always required.

Restart should only be done if recovery state is stuck
In most cases, cancel + restart recovery is sufficient

Common Causes of ORA-01153

MRP already running
Incomplete recovery cancellation
Multiple recovery sessions triggered
Automation scripts issuing duplicate commands
Session disconnection without cleanup

Best Practices

1. Always Check Recovery Status Before Starting

SQL> select process, status from v$managed_standby;

2. Avoid Multiple Recovery Commands

Ensure scripts do not start recovery repeatedly

3. Monitor Log Apply Lag

Use scripts like:

SQL>SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

4. Automate Alerts

Monitor:

Apply lag
Recovery stopped status

5. Verify Database Mode