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

Issue:

Standby redo log file creation on Standby database or DR database is failing 

Error Message: 

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

Cause: 

MRP process is running on standby database which is preventing to create standby redo logs

Fix:

Stop MPR and create standby redo logs and start the MRP process

Error Logs and commands output:

1. Standby redo log file creation on Standby database or DR database is failing 

[oracle@oraclelab3 2025_05_07]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:25:58 2025

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> set pages 1000

SQL> set lines 1000

SQL> col DBID for a10

SQL> select * from v$standby_log;

no rows selected

SQL>

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M

*

ERROR at line 1:

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

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M

*

ERROR at line 1:

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

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M

*

ERROR at line 1:

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

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M

*

ERROR at line 1:

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

SQL>

2. MRP process was up and running which is preventing us to create a standby redo logs on the standby database. Stop the MPR and create the standby redo logs

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M;

Database altered.

SQL> set pages 1000

SQL> set lines 1000

SQL> col DBID for a10

SQL> select * from v$standby_log;

    GROUP# DBID          THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAS T_CHANGE# LAST_TIME     CON_ID

---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- --- --------- --------- ----------

         4 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

         5 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

         6 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

         7 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

SQL>

3. Start the MPR process once the after the standby redo log files are created successfully

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

Database altered.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab3 2025_05_07]$