Home » RDBMS Server » Backup & Recovery » Log file Corrupted (Oracle 10g)
Log file Corrupted [message #460422] Fri, 11 June 2010 03:15 Go to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

Hi Friends,

I lost my redolog files. How can i restore those files. I don't have backup.
Re: Log file Corrupted [message #460424 is a reply to message #460422] Fri, 11 June 2010 03:20 Go to previous messageGo to next message
Frank Naude
Messages: 4581
Registered: April 1998
Senior Member
So, let me try to understand this. You don't have a single DB backup but you want to restore?
Re: Log file Corrupted [message #460426 is a reply to message #460424] Fri, 11 June 2010 03:23 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

I don't have backup. I think one redo001.log is corrupted
Re: Log file Corrupted [message #460427 is a reply to message #460426] Fri, 11 June 2010 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Have you another member of the same group?
Post the result of:
select * from v$log;
select * from v$logfile;

Regards
Michel
Re: Log file Corrupted [message #460428 is a reply to message #460427] Fri, 11 June 2010 03:30 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

Yes Michel I've another member in another group.
Re: Log file Corrupted [message #460430 is a reply to message #460428] Fri, 11 June 2010 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Not in another group, in the same group.
The idea is if one member of the group is corrupted another may not.
Please post the result of the queries I provided.
Please post the abstract of alert.log showing the corruption.
What is the current state of your database/instance?

Regards
Michel
Re: Log file Corrupted [message #460431 is a reply to message #460430] Fri, 11 June 2010 03:56 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

Michel,

I've one member in each group. Now db is in mount stage.

Queries O/p

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- ---------------------- -------------------------
1 1 70 52428800 1 NO CURRENT 2825881 11-JUN-10
2 1 68 52428800 1 YES INACTIVE 2785626 11-JUN-10
3 1 69 52428800 1 YES INACTIVE 2805013 11-JUN-10

3 rows selected

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------------------- ------- ------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------ ---------------------
3 ONLINE D:\APP\ORACLE\ORADATA\ORCL\REDO03.LOG NO
2 ONLINE D:\APP\ORACLE\ORADATA\ORCL\REDO02.LOG NO
1 ONLINE D:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG NO
4 STANDBY D:\APP\ORACLE\ORADATA\ORCL\REDO04A.LOG NO
5 STANDBY D:\APP\ORACLE\ORADATA\ORCL\REDO05A.LOG NO
6 STANDBY D:\APP\ORACLE\ORADATA\ORCL\REDO06A.LOG NO
7 STANDBY D:\APP\ORACLE\ORADATA\ORCL\REDO07A.LOG NO

7 rows selected
Re: Log file Corrupted [message #460433 is a reply to message #460431] Fri, 11 June 2010 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So you have only one member per group. It does not seem there is any problem.
What is in your alert.log file?
Post what you can see if you "alter database open;".

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Log file Corrupted [message #460437 is a reply to message #460433] Fri, 11 June 2010 04:39 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

This is error from startup

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1334380 bytes
Variable Size             507511700 bytes
Database Buffers           20971520 bytes
Redo Buffers                5844992 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG'
Re: Log file Corrupted [message #460440 is a reply to message #460437] Fri, 11 June 2010 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And what is in the alert.log from the last "alter database mount" line till now?
Also post the result of:
DIR D:\APP\ORACLE\ORADATA\ORCL

Regards
Michel

[Updated on: Fri, 11 June 2010 04:53]

Report message to a moderator

Re: Log file Corrupted [message #460446 is a reply to message #460440] Fri, 11 June 2010 05:04 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

Last Lines from alert log files

<msg time='2010-06-11T15:01:42.875+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:2986:2780954927' client_id='' type='NOTIFICATION'
 group='admin_ddl' level='16' host_id='JNET62'
 host_addr='192.168.1.93' module='sqlplus.exe' pid='4572'>
 <txt>ORA-313 signalled during: ALTER DATABASE OPEN...
 </txt>
</msg>
<msg time='2010-06-11T15:01:43.564+05:30' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='JNET62' host_addr='192.168.1.93' module='MMON_SLAVE'
 pid='5732'>
 <txt>Errors in file d:\app\oracle\diag\rdbms\orcl\orcl\trace\orcl_m000_5732.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: &apos;D:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG&apos;
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
 </txt>
</msg>
<msg time='2010-06-11T15:01:46.853+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='dbkh_generate_new_failure_alert:2592:3311820762' client_id='' type='ERROR'
 group='DRA' level='1' host_id='JNET62'
 host_addr='192.168.1.93' module='MMON_SLAVE' pid='5732'>
 <txt>Checker run found 2 new persistent data failures
 </txt>
</msg>
<msg time='2010-06-11T15:16:35.532+05:30' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='JNET62' host_addr='192.168.1.93' module=''
 pid='2868'>
 <txt>db_recovery_file_dest_size of 2048 MB is 64.71% used. This is a
 </txt>
</msg>
<msg time='2010-06-11T15:16:35.532+05:30' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='JNET62' host_addr='192.168.1.93' module=''
 pid='2868'>
 <txt>user-specified limit on the amount of space that will be used by this
 </txt>
</msg>
<msg time='2010-06-11T15:16:35.532+05:30' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='JNET62' host_addr='192.168.1.93' module=''
 pid='2868'>
 <txt>database for recovery-related files, and does not reflect the amount of
 </txt>
</msg>
<msg time='2010-06-11T15:16:35.532+05:30' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='JNET62' host_addr='192.168.1.93' module=''
 pid='2868'>
 <txt>space available in the underlying filesystem or ASM diskgroup.
 </txt>
</msg>

Re: Log file Corrupted [message #460455 is a reply to message #460446] Fri, 11 June 2010 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Also post the result of:
DIR D:\APP\ORACLE\ORADATA\ORCL
Re: Log file Corrupted [message #460465 is a reply to message #460455] Fri, 11 June 2010 06:26 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

The following files are available in that folder

CONTROL01.CTL
CONTROL02.CTL
CONTROL03.CTL

REDO02.LOG
REDO03.LOG
REDO04A.LOG
REDO05A.LOG
REDO06A.LOG
REDO07A.LOG

SYSAUX01.DBF
SYSTEM01.DBF
TEMP01.DBF
UNDOTBS01.DBF
USERS01.DBF
DMTBLSP01.DBF
RCVCAT01.DBF
Re: Log file Corrupted [message #460469 is a reply to message #460465] Fri, 11 June 2010 06:39 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

Michel, In that folder "REO01.LOG" is not available. I don't have any backup. What I've to do. Any possibilities are there to get that file.
Re: Log file Corrupted [message #460473 is a reply to message #460469] Fri, 11 June 2010 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In mount state, execute:
alter database clear logfile 'D:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG';
and try to open teh database.
Tell us what happens, depending on the cases the solution is different.

Regards
Michel
Re: Log file Corrupted [message #460477 is a reply to message #460473] Fri, 11 June 2010 07:06 Go to previous messageGo to next message
kk2205
Messages: 13
Registered: March 2009
Location: hyderabad
Junior Member

Hi Michel,

My problem is solved. Thanks for your cooperation.

bye
Re: Log file Corrupted [message #460493 is a reply to message #460477] Fri, 11 June 2010 08:33 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for the feedback.
Was it solved with what I said or in other way?

Regards
Michel
Previous Topic: insufficient privileges while connecting sys user
Next Topic: recover from rman
Goto Forum:
  


Current Time: Thu Nov 21 21:48:12 CST 2024