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;
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 ?#