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……..
— 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