Workaround of ora-00312 and ora-00313 error :-
---------------------------------------------
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: '/ORADB/APP/ORACLE/ORADATA/ORCL/REDO02.LOG'
---------------------------------------------
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: '/ORADB/APP/ORACLE/ORADATA/ORCL/REDO02.LOG'
----------------------
Alert.log file :- ----------------------
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oradb/app/oracle/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
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 '/ORADB/APP/ORACLE/ORADATA/ORCL/REDO01M2.LOG' ;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/ORADB/APP/ORACLE/ORADATA/ORCL/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: /ORADB/APP/ORACLE/ORADATA/ORCL/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
}
--------------------
{ 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: /ORADB/APP/ORACLE/ORADATA/ORCL/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
'/ORADB/APP/ORACLE/ORADATA/ORCL/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: '/ORADB/APP/ORACLE/ORADATA/ORCL/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: '/ORADB/APP/ORACLE/ORADATA/ORCL/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: '/ORADB/APP/ORACLE/ORADATA/ORCL/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: '/ORADB/APP/ORACLE/ORADATA/ORCL/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: '/ORADB/APP/ORACLE/ORADATA/ORCL/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>
---------------------------------------------------------------------------------------------------------------------
(4)-----------when status of redolog file is CURRENT and ACTIVE and ACTIVE and INACTIVE -----------
---------------------------------------------------------------------------------------------------------------------
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) ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
(4)-----------when status of redolog file is CURRENT and ACTIVE and ACTIVE and INACTIVE -----------
---------------------------------------------------------------------------------------------------------------------
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) ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
4) ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED 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: '/oradb/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
5) recover database until cancel.
6) alter database open resetlogs .
7) 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
6) alter database open resetlogs .
7) 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
SQL> select group#,thread#,status from v$log
2 /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 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
SQL>
SQL> alter system switch logfile ;
System altered.
….
…..
SQL>
SQL>
SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> alter system switch logfile;
## Not switching a log On hang state ctrl+c
SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
----------------------LINUX rm –rf *.log
SQL> startup mount .
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED 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: '/oradb/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
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: '/ORADB/APP/ORACLE/ORADATA/ORCL/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: '/ORADB/APP/ORACLE/ORADATA/ORCL/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>
-------------------------------------------------------------------------------------------------------------------
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
---------------------------------------------------------------------------------------------------------------------
2.
---------------------------------------------------------------------------------------------------------------------
shutdown immediate
startup mount
alter database open;
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradb/app/oracle/oradata/orcl/redo02.log'
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
alter database open;
----------------------------------------------------------------------------------------------------------------------
3.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.
Shut down the database with this SQLPLUS command:
shutdown abort
ABORT is needed because the database system cannot perform a proper shutdown, due to the damaged group.
2. Find out which file is missing, and check the ALERT and trace files for the reason why the redo log files were lost.
3. Mount the database with these SQLPLUS commands:
connect / as sysdba
startup mount
4. If you were running the database in ARCHIVELOG mode and archiving of the damaged online redo log group was not complete, you have to temporarily switch to NOARCHIVELOG mode before deleting the defective group, because otherwise the system does not let you delete the files:
alter database noarchivelog;
5. Delete the damaged online redo log files in one of the following ways:
As a group:
alter database drop logfile group <group number>;
As individual files:
alter database drop logfile member '<file name>' [,'<file name>'];
6. Create the new online redo log files (to replace the damaged ones, which you just deleted):
alter database add logfile '<file name>'[,'<file name>'] to group <group number>;
7. If the database was set to NOARCHIVELOG mode during these actions, change it back to ARCHIVELOG mode:
alter database archivelog;
8. If you were running the database in ARCHIVELOG mode, and the archiving of the online redo log group was not complete at the moment the problem occurred, it is essential that you now perform a backup of the entire database. If you do not, the offline redo log chain has a gap, which means that – in the event of another media error – only an incomplete recovery is possible.
----------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment