Online Redo Logs on Physical Standby (Metalink Doc ID 740675.1) states that if you encounter an ORA-01623 when trying to drop a redo log ON THE STANDBY that the only way to drop the group (While it is still a standby) is to recreate the standby control file. This is an can be an invasive process and I would not recommend it unless you are already planning on recreating the standby as a whole.
According to Oracle there is no other way around this due to Oracle Bug(s)
Bug 16091872 – ORA-01623 / ORA-01624 / ORA-01567 dropping ORL on a STANDBY database (Metalink Doc ID 16091872.8)
This situation occurred to a client recently and thought I would share my solution…
Putting together what I know about flashback on 11.2 I began to wonder why we couldn’t just set a restore point (snapshot standby), Activate the standby, switch and drop the log effected then flashback.
Lets see if that works…
Also as part of this process I will go through the adding dropping of the redo and standby redo to resize. If you are not interested in that bit you may want to scroll down to the flashback portion.
The Test Environment
I have a primary database on vmguest01 (DB Unique Name admin)
it has 4 log groups 2 members each and standby redo logs for role reversals
SQL> select INSTANCE_NAME, INSTANCE_ROLE, DATABASE_STATUS from v$instance;
INSTANCE_NAME INSTANCE_ROLE DATABASE_STATUS
---------------- ------------------ -----------------
admin1 PRIMARY_INSTANCE ACTIVE
SQL> select name, DB_UNIQUE_NAME, DATABASE_ROLE, PRIMARY_DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE PRIMARY_DB_UNIQUE_NAME
--------- ------------------------------ ---------------- ------------------------------
ADMIN admin PRIMARY
SQL>
set lines 160 pages 100
col member format a55
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$log a, v$logfile b
where a.group#=b.group#
union
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$standby_log a, v$logfile b
where a.group#=b.group#
order by 1,2,3;
GROUP# THREAD# TYPE STATUS MEMBER BYTES/1024/1024
---------- ---------- ------- ---------------- ------------------------------------------------------- ---------------
1 1 ONLINE INACTIVE +DATA_DG1/admin/onlinelog/group_1.357.963145563 128
1 1 ONLINE INACTIVE +FLASH_DG1/admin/onlinelog/group_1.272.963145563 128
2 1 ONLINE INACTIVE +DATA_DG1/admin/onlinelog/group_2.358.963145563 128
2 1 ONLINE INACTIVE +FLASH_DG1/admin/onlinelog/group_2.273.963145563 128
3 1 ONLINE INACTIVE +DATA_DG1/admin/onlinelog/group_3.359.963145565 128
3 1 ONLINE INACTIVE +FLASH_DG1/admin/onlinelog/group_3.274.963145565 128
4 1 ONLINE CURRENT +DATA_DG1/admin/onlinelog/group_4.360.963145565 128
4 1 ONLINE CURRENT +FLASH_DG1/admin/onlinelog/group_4.275.963145565 128
5 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_5.363.1057837191 128
5 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_5.302.1057837191 128
6 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_6.364.1057837199 128
6 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_6.303.1057837199 128
7 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_7.365.1057837199 128
7 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_7.304.1057837201 128
8 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_8.366.1057837201 128
8 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_8.305.1057837201 128
9 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_9.367.1057837205 128
9 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_9.306.1057837205 128
I have a standby database on vmguest02 (DB Unique Name admsb)
it has matching (unused) redo logs and matching active standby logfiles.
SQL> select INSTANCE_NAME, INSTANCE_ROLE, DATABASE_STATUS from v$instance;
INSTANCE_NAME INSTANCE_ROLE DATABASE_STATUS
---------------- ------------------ -----------------
admsb1 PRIMARY_INSTANCE ACTIVE
SQL> select name, DB_UNIQUE_NAME, DATABASE_ROLE, PRIMARY_DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE PRIMARY_DB_UNIQUE_NAME
--------- ------------------------------ ---------------- ------------------------------
ADMIN admsb PHYSICAL STANDBY admin
SQL>
set lines 160 pages 100
col member format a55
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$log a, v$logfile b
where a.group#=b.group#
union
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$standby_log a, v$logfile b
where a.group#=b.group#
order by 1,2,3;
GROUP# THREAD# TYPE STATUS MEMBER BYTES/1024/1024
---------- ---------- ------- ---------------- ------------------------------------------------------- ---------------
1 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_1.351.1057835973 128
1 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_1.274.1057835973 128
2 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_2.356.1057835975 128
2 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_2.273.1057835977 128
3 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_3.355.1057835979 128
3 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_3.272.1057835981 128
4 1 ONLINE CURRENT +DATA_DG1/admsb/onlinelog/group_4.352.1057835983 128
4 1 ONLINE CURRENT +FLASH_DG1/admsb/onlinelog/group_4.289.1057835985 128
5 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_5.353.1057837249 128
5 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_5.303.1057837251 128
6 1 STANDBY ACTIVE +DATA_DG1/admsb/onlinelog/group_6.354.1057837251 128
6 1 STANDBY ACTIVE +FLASH_DG1/admsb/onlinelog/group_6.304.1057837251 128
7 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_7.363.1057837251 128
7 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_7.305.1057837251 128
8 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_8.364.1057837253 128
8 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_8.306.1057837253 128
9 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_9.365.1057837255 128
9 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_9.307.1057837257 128
18 rows selected.
So first we will reproduce the bug while resizing the redo logs.
Probably best to stop managed recovery for the duration…
On the standby drop and recreate the standby redo logs
SQL> select name, DB_UNIQUE_NAME, DATABASE_ROLE, PRIMARY_DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE PRIMARY_DB_UNIQUE_NAME
--------- ------------------------------ ---------------- ------------------------------
ADMIN admsb PHYSICAL STANDBY admin
SQL> recover managed standby database cancel;
canceled managed recovery.
SQL> alter system set standby_file_management=MANUAL;
System altered.
-- add new (larger standby logs)
-- note you must clear the "active" standby logs or you will get the error below:
alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 5;
alter database drop standby logfile group 5
*
ERROR at line 1:
ORA-00261: log 5 of thread 1 is being archived or modified
ORA-00312: online log 5 thread 1: '+DATA_DG1/admsb/onlinelog/group_5.353.1057837249'
ORA-00312: online log 5 thread 1: '+FLASH_DG1/admsb/onlinelog/group_5.303.1057837251'
SQL> alter database clear logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 8;
Database altered.
SQL> alter database add standby logfile thread 1 group 8 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 9;
Database altered.
SQL> alter database add standby logfile thread 1 group 9 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> set lines 160 pages 100
col member format a55
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$log a, v$logfile b
where a.group#=b.group#
union
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$standby_log a, v$logfile b
where a.group#=b.group#
order by 1,2,3;SQL> SQL> 2 3 4 5 6 7 8
GROUP# THREAD# TYPE STATUS MEMBER BYTES/1024/1024
---------- ---------- ------- ---------------- ------------------------------------------------------- ---------------
1 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_1.351.1057835973 128
1 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_1.274.1057835973 128
2 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_2.356.1057835975 128
2 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_2.273.1057835977 128
3 1 ONLINE CURRENT +DATA_DG1/admsb/onlinelog/group_3.355.1057835979 128
3 1 ONLINE CURRENT +FLASH_DG1/admsb/onlinelog/group_3.272.1057835981 128
4 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_4.352.1057835983 128
4 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_4.289.1057835985 128
5 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_5.366.1057845069 256
5 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_5.322.1057845071 256
6 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_6.354.1057845103 256
6 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_6.304.1057845103 256
7 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_7.363.1057845115 256
7 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_7.305.1057845117 256
8 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_8.364.1057845129 256
8 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_8.323.1057845131 256
9 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_9.365.1057845141 256
9 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_9.307.1057845141 256
18 rows selected.
Now on the primary resize redo logs:
SQL> select DB_UNIQUE_NAME, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ----------------
admin PRIMARY
-- Note you cannot drop the current log file so if you get the following error move past that group and come back to it
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance admin1 (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '+DATA_DG1/admin/onlinelog/group_1.357.963145563'
ORA-00312: online log 1 thread 1: '+FLASH_DG1/admin/onlinelog/group_1.272.963145563'
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile thread 1 group 2 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile thread 1 group 3 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database add logfile thread 1 group 4 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile thread 1 group 1 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
Since we are not using the standby logs on the primary (unless a role reversal occurs) we can just drop and recreate those now as well.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 8;
Database altered.
SQL> alter database add standby logfile thread 1 group 8 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop standby logfile group 9;
Database altered.
SQL> alter database add standby logfile thread 1 group 9 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> set lines 160 pages 100
col member format a55
SQL> SQL> select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$log a, v$logfile b
where a.group#=b.group#
union
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$standby_log a, v$logfile b
2 3 4 5 6 7 where a.group#=b.group#
order by 1,2,3; 8
GROUP# THREAD# TYPE STATUS MEMBER BYTES/1024/1024
---------- ---------- ------- ---------------- ------------------------------------------------------- ---------------
1 1 ONLINE UNUSED +DATA_DG1/admin/onlinelog/group_1.357.1057845873 256
1 1 ONLINE UNUSED +FLASH_DG1/admin/onlinelog/group_1.272.1057845875 256
2 1 ONLINE CURRENT +DATA_DG1/admin/onlinelog/group_2.358.1057845663 256
2 1 ONLINE CURRENT +FLASH_DG1/admin/onlinelog/group_2.273.1057845665 256
3 1 ONLINE UNUSED +DATA_DG1/admin/onlinelog/group_3.359.1057845679 256
3 1 ONLINE UNUSED +FLASH_DG1/admin/onlinelog/group_3.274.1057845681 256
4 1 ONLINE UNUSED +DATA_DG1/admin/onlinelog/group_4.360.1057845857 256
4 1 ONLINE UNUSED +FLASH_DG1/admin/onlinelog/group_4.275.1057845859 256
5 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_5.363.1057846165 256
5 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_5.302.1057846165 256
6 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_6.364.1057846187 256
6 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_6.303.1057846187 256
7 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_7.365.1057846205 256
7 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_7.304.1057846205 256
8 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_8.366.1057846221 256
8 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_8.305.1057846221 256
9 1 STANDBY UNASSIGNED +DATA_DG1/admin/onlinelog/group_9.367.1057846233 256
9 1 STANDBY UNASSIGNED +FLASH_DG1/admin/onlinelog/group_9.306.1057846233 256
18 rows selected.
So now the primary looks good so back to the standby (and our Bug)
SQL> set lines 160 pages 100
col member format a55
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$log a, v$logfile b
where a.group#=b.group#
SQL> SQL> 2 3 4 union
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$standby_log a, v$logfile b
where a.group#=b.group#
order by 1,2,3; 5 6 7 8
GROUP# THREAD# TYPE STATUS MEMBER BYTES/1024/1024
---------- ---------- ------- ---------------- ------------------------------------------------------- ---------------
1 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_1.351.1057835973 128
1 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_1.274.1057835973 128
2 1 ONLINE CURRENT +DATA_DG1/admsb/onlinelog/group_2.356.1057835975 128
2 1 ONLINE CURRENT +FLASH_DG1/admsb/onlinelog/group_2.273.1057835977 128
3 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_3.355.1057835979 128
3 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_3.272.1057835981 128
4 1 ONLINE CLEARING +DATA_DG1/admsb/onlinelog/group_4.352.1057835983 128
4 1 ONLINE CLEARING +FLASH_DG1/admsb/onlinelog/group_4.289.1057835985 128
5 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_5.366.1057845069 256
5 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_5.322.1057845071 256
6 1 STANDBY ACTIVE +DATA_DG1/admsb/onlinelog/group_6.354.1057845103 256
6 1 STANDBY ACTIVE +FLASH_DG1/admsb/onlinelog/group_6.304.1057845103 256
7 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_7.363.1057845115 256
7 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_7.305.1057845117 256
8 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_8.364.1057845129 256
8 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_8.323.1057845131 256
9 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_9.365.1057845141 256
9 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_9.307.1057845141 256
18 rows selected.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.
SQL> alter system set standby_file_management=MANUAL;
System altered.
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance admsb1 (thread 1)
ORA-00312: online log 1 thread 1: '+DATA_DG1/admsb/onlinelog/group_1.351.1057835973'
ORA-00312: online log 1 thread 1: '+FLASH_DG1/admsb/onlinelog/group_1.274.1057835973'
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
Wait a minute!!! log group 1 wasn’t even the active log? Well, now we are stuck.. Cant switch a log because it is a standby database. Switching the logs in the primary are no help either as the problem resides in the standby’s control file.
So will flashback / snapshot standby save us? Lets find out:
SQL> select name, DB_UNIQUE_NAME, DATABASE_ROLE, PRIMARY_DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE PRIMARY_DB_UNIQUE_NAME
--------- ------------------------------ ---------------- ------------------------------
ADMIN admsb PHYSICAL STANDBY admin
SQL> alter database convert to snapshot standby;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name, DB_UNIQUE_NAME, DATABASE_ROLE, PRIMARY_DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE PRIMARY_DB_UNIQUE_NAME
--------- ------------------------------ ---------------- ------------------------------
ADMIN admsb SNAPSHOT STANDBY admin
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile thread 1 group 2 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile thread 1 group 3 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database add logfile thread 1 group 4 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile thread 1 group 1 ('+DATA_DG1','+FLASH_DG1') size 256M;
Database altered.
SQL> set lines 160 pages 100
SQL> col member format a55
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$log a, v$logfile b
where a.group#=b.group#
SQL> 2 3 4 union
select a.group#, a.thread#, b.type, a.status, b.member, bytes/1024/1024
from v$standby_log a, v$logfile b
where a.group#=b.group#
order by 1,2,3; 5 6 7 8
GROUP# THREAD# TYPE STATUS MEMBER BYTES/1024/1024
---------- ---------- ------- ---------------- ------------------------------------------------------- ---------------
1 1 ONLINE INACTIVE +DATA_DG1/admsb/onlinelog/group_1.351.1057849131 256
1 1 ONLINE INACTIVE +FLASH_DG1/admsb/onlinelog/group_1.274.1057849133 256
2 1 ONLINE UNUSED +DATA_DG1/admsb/onlinelog/group_2.356.1057849271 256
2 1 ONLINE UNUSED +FLASH_DG1/admsb/onlinelog/group_2.337.1057849271 256
3 1 ONLINE UNUSED +DATA_DG1/admsb/onlinelog/group_3.355.1057849331 256
3 1 ONLINE UNUSED +FLASH_DG1/admsb/onlinelog/group_3.338.1057849333 256
4 1 ONLINE CURRENT +DATA_DG1/admsb/onlinelog/group_4.352.1057849175 256
4 1 ONLINE CURRENT +FLASH_DG1/admsb/onlinelog/group_4.289.1057849175 256
5 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_5.366.1057845069 256
5 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_5.322.1057845071 256
6 1 STANDBY ACTIVE +DATA_DG1/admsb/onlinelog/group_6.354.1057845103 256
6 1 STANDBY ACTIVE +FLASH_DG1/admsb/onlinelog/group_6.304.1057845103 256
7 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_7.363.1057845115 256
7 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_7.305.1057845117 256
8 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_8.364.1057845129 256
8 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_8.323.1057845131 256
9 1 STANDBY UNASSIGNED +DATA_DG1/admsb/onlinelog/group_9.365.1057845141 256
9 1 STANDBY UNASSIGNED +FLASH_DG1/admsb/onlinelog/group_9.307.1057845141 256
18 rows selected.
SQL> -- so far so good.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2722615296 bytes
Fixed Size 2256272 bytes
Variable Size 553648752 bytes
Database Buffers 2147483648 bytes
Redo Buffers 19226624 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2722615296 bytes
Fixed Size 2256272 bytes
Variable Size 553648752 bytes
Database Buffers 2147483648 bytes
Redo Buffers 19226624 bytes
SQL> alter database mount standby database;
Database altered.
SQL> select name, DB_UNIQUE_NAME, DATABASE_ROLE, PRIMARY_DB_UNIQUE_NAME from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE PRIMARY_DB_UNIQUE_NAME
--------- ------------------------------ ---------------- ------------------------------
ADMIN admsb PHYSICAL STANDBY admin
SQL> alter system set standby_file_management=auto;
System altered.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> exit
[oracle@vmguest02]$ tail -f alert_admsb1.log
Media Recovery Log +FLASH_DG1/admsb/archivelog/2020_11_30/thread_1_seq_64.325.1057845451
Media Recovery Log +FLASH_DG1/admsb/archivelog/2020_11_30/thread_1_seq_65.326.1057845833
Media Recovery Log +FLASH_DG1/admsb/archivelog/2020_11_30/thread_1_seq_66.329.1057847627
Media Recovery Log +FLASH_DG1/admsb/archivelog/2020_11_30/thread_1_seq_67.272.1057849327
Media Recovery Log +FLASH_DG1/admsb/archivelog/2020_11_30/thread_1_seq_68.339.1057849427
Media Recovery Log +FLASH_DG1/admsb/archivelog/2020_11_30/thread_1_seq_69.333.1057849559
Media Recovery Waiting for thread 1 sequence 70 (in transit)
Recovery of Online Redo Log: Thread 1 Group 6 Seq 70 Reading mem 0
Mem# 0: +DATA_DG1/admsb/onlinelog/group_6.354.1057845103
Mem# 1: +FLASH_DG1/admsb/onlinelog/group_6.304.1057845103
So if you find yourself stuck in a situation where you have encountered Bug 16091872 You can resolve by using snapshot standby to open the database read-write make your redo changes to the control file then flash back to the point where managed recovery was stopped. Since redo is only recorded in the control file of the standby the redo changes will be preserved.