---------------------------------------------
shutdown immediate the database and deleted one of the online redo logs via OS command, in this case there are only 2 groups with 1 log member in each. When you try to open the database you receive the following errors:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO02.LOG'
Solution of these error:-
---------------------------------
ORA-00312 and ORA-00313 occurred when a redo log file is missing by any type of media failure.
If a media failure has affected the online redo logs of a database, then the appropriate recovery Solution depends on the following:
1) The configuration of the online redo log:
i) multiplexed
ii) non-multiplexed
2) The status and types of online redo log files affected by the media failure:
SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT
4 1 INACTIVE
and 5) unarchived
so i define it in three parts
(1)----- When deleted log file is multiplexed OR Mirrored
(2)-----when status of redolog file is INACTIVE (has been archived and not archived)
(3)-----when status of redolog file is CURRENT and ACTIVE
--------------------------------------------------------------
(1)----- When deleted log file is multiplexed OR Mirrored------
--------------------------------------------------------------
If the online redo log of a database is multiplexed, means if at least
one member of each online redo log group is not affected by the media
failure, then the database continues functioning as normal, but error
messages are written to the log writer trace file and the alert_SID.log
of the database.
Then copy & paste the one existing member, and rename it to corrupted log file name.
or
drop the damaged member and add a new member by using following command .
SQL> ALTER DATABASE DROP LOGFILE MEMBER 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01M2.LOG' ;
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01M3.LOG' TO GROUP 2;
live example:-
-----------------
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 2 16:37:58 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> column member format a30
SQL>
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
After shutdown, here i delete a member of redolog group 1 , which is multiplexed .
then database is open normaly ,but a error is generated in alert log file. see bellow
alert log contents
--------------------
{ Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun Oct 02 17:29:05 2011
ALTER DATABASE OPEN
Sun Oct 02 17:29:06 2011
Errors in file g:\oracle\product\10.2.0\admin\halimdb\bdump\halimdb_lgwr_1064.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Sun Oct 02 17:29:06 2011
Thread 1 opened at log sequence 7
Current log# 4 seq# 7 mem# 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO04.LOG
Successful open of redo thread 1
Sun Oct 02 17:29:06 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Oct 02 17:29:06 2011
SMON: enabling cache recovery
Sun Oct 02 17:29:07 2011
Successfully onlined Undo Tablespace 1.
Sun Oct 02 17:29:07 2011
SMON: enabling tx recovery
Sun Oct 02 17:29:07 2011
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=15, OS id=2488
Sun Oct 02 17:29:11 2011
Completed: ALTER DATABASE OPEN
Sun Oct 02 17:29:11 2011
}
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;
GROUP# STATUS MEMBER
---------- ------- ------------------------------
1 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO01.LOG
1 INVALID G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO01M2.LOG
2 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO02.LOG
3 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO03.LOG
4 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO04.LOG
SQL>
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> ALTER DATABASE DROP LOGFILE MEMBER
'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01M2.LOG' ;
Database altered.
SQL>
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
SQL>
-------------------------------------------------------------------------
(2)-------------------when status of redolog file is INACTIVE------------
-------------------------------------------------------------------------
Mount the database and check v$log to see if the deleted log is INACTIVE .
If the log is INACTIVE, simply Clear the log group (in the mount state of the DB)
(alter database clear logfile group 2) ;
or
drop the log group
(alter database drop logfile group 2);
If there are only 2 log groups then it will be necessary to add another group before dropping this one.
[Note :-
1) To clear an inactive, online redo log group that has been archived
simple apply.....(in mount state of DB)
(alter database clear logfile group 2) ;
and
2) To clear an inactive, online redo log group that has not been archived
Clear the log using the UNARCHIVED keyword. For example,
ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;
and Immediately back up the whole database.
so that you have a backup you can use for complete recovery without relying
on the cleared log group.
and backup control file also using
ALTER DATABASE BACKUP CONTROLFILE TO 'D:/oracle/control_file_bk/con1.f';
]
Live example:-
---------------
SQL> select group#,thread#,status from v$log
2 /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 1 CURRENT
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
[after shutdown the database, here i delete "REDO02.LOG" manually via OS command, which is INACTIVE]
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 192940932 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO02.LOG'
SQL>
SQL>
SQL>
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;
GROUP# STATUS MEMBER
---------- ------- ------------------------------
1 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO01.LOG
2 STALE G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO02.LOG
3 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO03.LOG
4 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO04.LOG
SQL>
SQL>
SQL>
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO02.LOG'
SQL>
SQL>
SQL>
SQL> alter database clear logfile group 2 ;
Database altered.
SQL>
SQL>
SQL>
SQL> alter database open ;
Database altered.
SQL>
SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL>
--------------------------------------------------------------------------
(3)-----------when status of redolog file is CURRENT and ACTIVE-----------
--------------------------------------------------------------------------
First Mount the database and check v$log to see if the deleted log is current;
If the log is current they should simply perform fake recovery and then
open resetlogs the Backup full database immediately .
below are the steps.......
1) connect as sysdba like..
SQL> conn / as sysdba
2) startup mount .
3) recover database until cancel.
4) alter database open resetlogs .
5) Backup the Database (full).
Live example:-
---------------
SQL> ed
Wrote file afiedt.buf
1 SELECT group# "GROUP", status, MEMBER, TYPE, is_recovery_dest_file
2 FROM v$logfile
3* ORDER BY 1, 2
SQL> /
GROUP STATUS MEMBER TYPE IS_
---------- ------- ------------------------------ ------- ---
1 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO01.LOG
2 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO02.LOG
3 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO03.LOG
4 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO04.LOG
SQL> select group#,thread#,status from v$log
2 /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 1 INACTIVE
SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> select group#,thread#,status from v$log
2 /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 1 INACTIVE
SQL>
SQL> alter system switch logfile ;
System altered.
SQL>
SQL>
SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT
4 1 INACTIVE
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
[after shutdown the database, here i delete "REDO03.LOG" manually via OS command, which is CURRENT ]
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 197135236 bytes
Database Buffers 406847488 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO03.LOG'
SQL>
SQL> alter database clear logfile group 3 ;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>
SQL> alter database clear logfile group 3 ;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>
SQL>
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> recover database until cancel;
Media recovery complete.
SQL>
SQL>
SQL> alter database open
2 /
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL>
No comments:
Post a Comment