SQL) set lines 1000;
SQL) set pagesize 80;
SQL) select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
-----------------------------------------------------------------------------
1 /oracle/SRD/origlogA/log_g11m1.dbf
1 INVALID /oracle/SRD/mirrlogA/log_g11m2.dbf
2 /oracle/SRD/origlogB/log_g12m1.dbf
2 INVALID /oracle/SRD/mirrlogB/log_g12m2.dbf
3 /oracle/SRD/origlogA/log_g13m1.dbf
3 INVALID /oracle/SRD/mirrlogA/log_g13m2.dbf
4 /oracle/SRD/origlogB/log_g14m1.dbf
4 INVALID /oracle/SRD/mirrlogB/log_g14m2.dbf
The above is the system status we can find with command. Invalid is coming due to that the mirrlog doesn't
exist, there is no file at that location.
We may follow the below procedure:
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g11m2.dbf';
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g11m2.dbf' to group 1;
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g12m2.dbf';
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g12m2.dbf' to group 2;
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g13m2.dbf';
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g13m2.dbf' to group 3;
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g14m2.dbf';
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g14m2.dbf' to group 4;
If it stills shows invalid under status then give the below command :
alter system switch logfile;
SQL) select group#, status, member from v$logfile;
GROUP# STATUS MEMBER
----------------------------------------------------------------------------
1 /oracle/SRD/origlogA/log_g11m1.dbf
2 /oracle/SRD/origlogB/log_g12m1.dbf
2 INVALID /oracle/SRD/mirrlogB/log_g12m2.dbf
3 /oracle/SRD/origlogA/log_g13m1.dbf
3 INVALID /oracle/SRD/mirrlogA/log_g13m2.dbf
4 /oracle/SRD/origlogB/log_g14m1.dbf
4 INVALID /oracle/SRD/mirrlogB/log_g14m2.dbf
The invalid status will disappear.
For more info; please read SAP Note 491160