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

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

I was trying to add a standby logfile to standby database when I got the error ORA-01156: recovery or flashback in progress may need access to files

SQL> alter database add standby logfile '/u01/oradata/voice/lf/stdb_redo3.log' size 4m;
alter database add standby logfile '/u01/oradata/voice/lf/stdb_redo3.log' size 4m
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

To know what caused ORA-01156, I used oerr.

SQL> !oerr ora 1156
01156, 00000, "recovery or flashback in progress may need access to files"
// *Cause: Either media recovery, instance recovery, or flashback was
//         in progress. The recovery or flashback in progress may need
//         the files to which this operation is being applied.
// *Action: Wait for recovery or flashback to complete.

Though there is no file by name '/u01/oradata/voice/lf/stdb_redo3.log', oerr reported that recovery or flashback needs to access the files.

To add the standby logfile, the managed recovery has to be cancelled.

SQL> alter database recover managed standby database cancel;

Database altered.

the parameter standby_file_management is still set to AUTO

SQL> sho parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

Added the standby logfile

SQL> alter database add standby logfile '/u01/oradata/voice/lf/stdb_redo3.log' size 4m;

Database altered.

SQL> alter database add standby logfile '/u01/oradata/voice/lf/stdb_redo4.log' size 4m;

Database altered.
and then the recovery has to started again...
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.
SQL> select group#, type, member from v$logfile order by type;

GROUP# TYPE    MEMBER
------ ------- -------------------------------------------
     1 ONLINE  /u01/oradata/voice/lf/redo1.log
     2 ONLINE  /u01/oradata/voice/lf/redo2.log
     6 ONLINE  /u01/oradata/voice/lf/redo3.log
     9 ONLINE  /u01/oradata/voice/lf/redo9b.log
     9 ONLINE  /u01/oradata/voice/lf/redo9.log
     5 ONLINE  /u01/oradata/voice/lf/redo5.log
     7 STANDBY /u01/oradata/voice/lf/stdb_redo4.log
     3 STANDBY /u01/oradata/voice/lf/stdb_redo3.log
     4 STANDBY /u01/oradata/voice/lf/stdb_redo2.log

9 rows selected.
Comments