Mir Sayeed Hassan – Oracle Blog

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==========