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]$
No comments:
Post a Comment