ORA-01156: recovery or flashback in progress may need access to files
Posted by Mir Sayeed Hassan on July 7th, 2018
ORA-01156: recovery or flashback in progress may need access to files
[oracle@primdb onlinelog]$ !sq sqlplus / as sysdba
SQL> alter database drop standby logfile group 1; alter database drop standby logfile group 1 * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files
Solution:
You need to stop the standby recovery process & change the standby_file_management to “manual” & delete the logfile
SQL> alter database recover managed standby database cancel; Database altered.
SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO
SQL> alter system set standby_file_management=’MANUAL;
SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string MANUAL
Droping/adding the logfile
SQL> alter database drop standby logfile group 1; Database altered.
SQL> alter database drop standby logfile group 2; Database altered.
Incase if not drop change to manual
SQL> alter system set standby_file_management=’MANUAL; then drop it
Note:
You need to enable the above parameter to AUTO as standby_file_management=’AUTO’; after completed the task
Verify
SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u02/oradata/onlinelog/standby07.log /u02/oradata/onlinelog/standby08.log /u02/oradata/onlinelog/standby09.log /u02/oradata/onlinelog/redo4.log /u02/oradata/onlinelog/redo5.log /u02/oradata/onlinelog/redo6.log /u02/oradata/onlinelog/standby10.log 7 rows selected.
Restart the recovery process
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
SQL> @/home/oracle/scripts/stbytest.sql; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 2931 1 1 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 2810 1 796 MRP0 WAIT_FOR_LOG 1 2941 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 1 2941 453822 1 9 rows selected. THREAD# MAX(SEQUENCE#) ---------- -------------- 1 2940
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
==========Hence tested & verified in our production standby database==========