Wednesday, May 7, 2025

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

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

-bash: oraenv: No such file or directory

-bash: oraenv: No such file or directory What Are Root.sh And OrainstRoot.sh Scripts In A Standalone RDBMS Installation? (Doc ID 1493121.1) ...