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.