Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

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