Few days back a semi-DBA(semi cause he looks more into application but side-by-side does some DBA work also) came rushing, saying he is getting “ORA-19528” when trying to drop the standby redolog files .The database was single instance with datafiles and redo logfiles on ASM and was used as the capture database for CDC.Google didn’t provide much information and metalink shows its a bug.As per Metalink :-
“This error is due to the strangely named (non-existent) standby logfile that was carried over during the upgrade, which belongs to the standby database.”
Metalink provides a workaround –  “Recreate the Controlfile “
So lets starts:-

SQL> select group# from v$logfile where TYPE='STANDBY';
 GROUP#
----------
 4
 5
 6
 7
 8
 9
SQL> select GROUP#,STATUS,MEMBER from v$logfile where TYPE='STANDBY';
 GROUP#     STATUS  MEMBER
---------- ------- ------------------------------------------------------------
 4                  +DATA1/stg/onlinelog/group_4.289.710747981
 5                  +DATA1/stg/onlinelog/group_5.290.710747983
 6         INVALID  +DATA1
 7                  +DATA1/stg/onlinelog/group_7.292.710747985
 8                  +DATA1/stg/onlinelog/group_8.293.710747987
 9                  +DATA1/stg/onlinelog/group_9.294.710747991

Something is wrong with “6”.Its invalid.Invalid status means the file is not accessible.When asked the semi-DBA he has no idea!!!!As i had thought!!!
When checked on the ASM (+DATA1/stg/onlinelog/) location atleast 20 “group_6.xxx.xxxxxxxxx’ files were present.As the status was “INVALID” removed these ~ 20 files using rm command in asmcmd.
Lets drop the logfile group

SQL>  alter database drop standby logfile group 6;
 alter database drop standby logfile group 6
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files

As the status was showing INVALID, this error might be correct.Lets try dropping some other group of standby type.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files

Ohhhhhh….still error persists !!!!!!! So, lets create a new controlfile without the standby logfiles.Take backup of the current controlfile :-

sql> alter database backup controlfile to trace;

The above command  backups up the controlfile in human readable format in UDUMP.Open the tracefile,edit it and save it as “.sql” file.The edited version  looked like

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "STG" NORESETLOGS  ARCHIVELOG
 MAXLOGFILES 10
 MAXLOGMEMBERS 5
 MAXDATAFILES 500
 MAXINSTANCES 1
 MAXLOGHISTORY 292
LOGFILE
 GROUP 1 '+DATA1/stg/onlinelog/group_1.274.710735591'  SIZE 256M,
 GROUP 2 '+DATA1/stg/onlinelog/group_2.275.710735593'  SIZE 256M,
 GROUP 3 '+DATA1/stg/onlinelog/group_3.276.710735593'  SIZE 256M
DATAFILE
 '+DATA1/stg/datafile/system.277.710735595',
 '+DATA1/stg/datafile/undo_tbs.278.710735599',
 '+DATA1/stg/datafile/sysaux.279.710735601',
 '+DATA1/stg/datafile/undo_tbs.284.710735787',
 '+DATA1/stg/datafile/undo_tbs.285.710735787',
 '+DATA1/stg/datafile/ic_tbs.286.710735843',
 '+DATA1/stg/datafile/cdc_pub.287.710735861',
 '+DATA1/stg/datafile/stg.288.710743737'
CHARACTER SET AL32UTF8
;

Standby logfile group entries are also present in the trace file (between the LOGFILE and DATAFILE entries but  hashed):-

-- STANDBY LOGFILE
  --   GROUP 4 '+DATA1/ichadstg/onlinelog/group_4.289.710747981'  SIZE 256M,
  --   GROUP 5 '+DATA1/ichadstg/onlinelog/group_5.290.710747983'  SIZE 256M,
  --   GROUP 6 '+DATA1'  SIZE 256M,
  --   GROUP 7 '+DATA1/ichadstg/onlinelog/group_7.292.710747985'  SIZE 256M,
  --   GROUP 8 '+DATA1/ichadstg/onlinelog/group_8.293.710747987'  SIZE 256M,
  --   GROUP 9 '+DATA1/ichadstg/onlinelog/group_9.294.710747991'  SIZE 256M

Few lines below in the trace file, command for adding the standby logfiles is present :-

----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '+DATA1/stg/onlinelog/group_4.289.710747981' SIZE 256M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '+DATA1/stg/onlinelog/group_5.290.710747983' SIZE 256M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '+DATA1' SIZE 256M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '+DATA1/stg/onlinelog/group_7.292.710747985' SIZE 256M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '+DATA1/stg/onlinelog/group_8.293.710747987' SIZE 256M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '+DATA1/stg/onlinelog/group_9.294.710747991' SIZE 256M REUSE;

The controfiles were present on the server disk and not on ASM, so changed the location to ASM in control_file parameter

SQL> alter system set control_files='+DATA1','+DATA1' scope=spfile;
System altered.

Shutdown the database, as connect as sysdba to the ideal instance

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-bash-3.00$ sqlplus / as sysdba
Connected to an idle instance.
SQL>  @create_control.sql  --> The controlfile had the STARTUP NOMOUNT
Control file created.

Open the database

SQL> alter database open;
Database altered.

Check the controlfile and the logfile

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA1/stg/controlfile/current.339.711854167
+DATA1/stg/controlfile/current.338.711854167
SQL> select group#,member from v$logfile;
    GROUP#    MEMBER
----------  --------------------------------------------------------------------------------
         2   +DATA1/stg/onlinelog/group_2.275.710735593
         1   +DATA1/stg/onlinelog/group_1.274.710735591
         3   +DATA1/stg/onlinelog/group_3.276.710735593
SQL> select group#,member from v$logfile where type='STANDBY';
no rows selected

Removed the standby logfiles from ASM using rm command in ASMCMD and then added the standby logfiles to the database.The command in the backup tracefile can be used.

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4 '+ARCH1' SIZE 256M;
Database altered.

Created all the 6 standby logfiles.Tried to drop the standby logfile and was successful now.But, still don’t know why this error came,was some changes done from their side or was it due to some other reason……..