Skip to content

How to Resolve ORA-01156: recovery or flashback in progress may need access to files

  • by

ORA-01156

Tried to add a standby log file to a database, but it failed with ORA-01156.

SQL> alter database add standby logfile thread 1 group 108 '+DATA/ORCLCDB/redo108a.log' size 2048m;
alter database add standby logfile thread 1 group 108 '+DATA/ORCLCDB/redo108a.log' size 2048m
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

ORA-01156 means that any redo log file cannot be added or modified at this moment, because recovery or flashback is in progress. Most likely, the error happens in the standby database due to Managed Recovery Process (MRP) is working on applying changes from the primary database.

Solution

We should stop the running MRP first.

1. Stop MRP

Without Broker

SQL> alter database recover managed standby database cancel;

Database altered.

With Broker

If you were using data guard broker.

DGMGRL> edit database ORCLSTB set state='APPLY-OFF';
Succeeded.

You may also fully stop data guard service if you were in maintenance.

2. Check MRP

To make sure that MRP has gone, we can query V$MANAGED_STANDBY.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby where process like '%MRP%';

no rows selected

3. Add Logfile

Now we can add the standby redo log file safely.

SQL> alter database add standby logfile thread 1 group 108 '+DATA/ORCLCDB/redo108a.log' size 2048m;

Database altered.

We solved it.

Leave a Reply

Your email address will not be published.