Find in this Blog

Sunday, June 8, 2014

ORA-00362: member is required to form a valid logfile in group 1

SQL> SELECT GROUP#,STATUS,TYPE FROM V$LOGFILE
  2  ;

    GROUP# STATUS  TYPE
---------- ------- -------
         3 STALE   ONLINE
         3 STALE   ONLINE
         2 STALE   ONLINE
         2 STALE   ONLINE
         1 STALE   ONLINE
         1 INVALID ONLINE
         4 STALE   ONLINE
         4 STALE   ONLINE
         3 INVALID ONLINE

9 rows selected.

SQL> SELECT GROUP#,STATUS,TYPE FROM V$LOGFILE WHERE STATUS='INVALIDE'
  2  ;

no rows selected

SQL> SELECT GROUP#,STATUS,TYPE FROM V$LOGFILE WHERE STATUS='INVALID'
  2  ;

    GROUP# STATUS  TYPE
---------- ------- -------
         1 INVALID ONLINE
         3 INVALID ONLINE

SQL> SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE WHERE STATUS='INVALID'
  2  ;

    GROUP# STATUS
---------- -------
MEMBER
---------------------------------------------------------------------------

         1 INVALID
F:\ORACLE\SOL\MIRRLOGA\LOG_G11M2.DBF

         3 INVALID
G:\ORACLE\SOL\ORIGLOGA\LOG_G11M5


SQL> SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE WHERE STATUS='INVALID'
  2  ;

    GROUP# STATUS
---------- -------
MEMBER
---------------------------------------------------------------------------

         1 INVALID
F:\ORACLE\SOL\MIRRLOGA\LOG_G11M2.DBF

         3 INVALID
G:\ORACLE\SOL\ORIGLOGA\LOG_G11M5


SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open





SQL> alter database drop LOGFILE MEMBER'F:\ORACLE\SOL\MIRRLOGA\LOG_G11M2.DB
alter database drop LOGFILE MEMBER'F:\ORACLE\SOL\MIRRLOGA\LOG_G11M2.DBF'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 1

Solution  (for my refrence)

Create the control file  using trace file (alter database backup controllfile to trace;)

remove the invalid control file and group from the trace file.

and re create the control on  nomount mode.

SQL> @C:\ctrl.sql
control file created.

then execute the 

SQL> recover database;
media recovered successfully
SQL> Alter database open;
database opened;




select group#,member from v$logfile where status='INVALID';

select Group#,status,type from v$logfile;





No comments:

Post a Comment

Ask Your Questions ?#