redo log corrupted

[Hunan_1]

I have a redo log corrupted, How can I start to solve the problem.

any help would be really appreciated,

[Pete Randall]

What do you mean by a "redo log"?

Pete

[Ian Lochray]

I presume you are talking about an Oracle redo log. If so, how did the corruption manifest itself and what error message(s) did Oracle report?

[Hunan_1]

yes, I'm talking about oracle redo log,
I have 4 groups mirrored everyone of them in different disks.

At certain moment, the oracle's log shows that there was an inconsistency in one redo log.

Then, I shutdown the database, after when it was been restarted with the resetlogs option, the following message was shown.

alter database open resetlog
*
ORA-01194: El fichero 1 necesita mas recuperacion para ser consistente.
ORA-01110: Fichero de datos 1 :"/opt/oracle8/...."

It's not matter if I lost the data, I would be really pleased if someone could help me to put it working again.

Thanks

[Stan_17]

Hunan,

presuming you lost the current redolog, then this is what i would do,

1. restore all datafiles from recent backup

2. mount the db

3. do a cancel based recovery (recover database until cancel)

4. open the db with resetlogs (alter database open resetlogs)

5. take an immediate backup of the database (hotbackup), as your previous backup becomes useless due to step #4.

hth,
Stan

[Hunan_1]

The problem is that we I don't have any backup.
So the only I want to save is the database structure, no matter if the stored data is lost.

thanks

[Stan_17]

Oops, no backups is always a problem. okay tell me on what datafile your getting the error, is it temp, system or rbs or data...?

do you have any export dumps ?

Stan

[Hunan_1]

That datafile it's attached to the tablespace system.

can I edit the init.ora and use this?

_allow_resetlogs_corruption=TRUE

Could it be an option to get back my database?

[Stan_17]

Since the datafile being reported belongs to system tbs and you have no backups..then i would suggest to use the undocumented parameter _allow_resetlogs_corruption=TRUE. only trump card that you have now.

so go ahead, set this parameter in init.ora file and bounce the db.

immediately, take an export dump of the entire database or at least the schemas that your concerned of and rebuild the database.

[Brian Crabtree]

This command should work:

alter database clear logfile '';

You might have to do:

alter database clear unarchived logfile '';

The last option that you could try, is to copy the valid member from the group over the corrupted member from the group, and bring the database up. This should allow the database to come up as well.

Brian

[Stan_17]

hi brian,

hunan, could get lucky with clear logfile thing, however, the last option you suggested, oracle automatically does that for you. if one log member is corrupted for some darn reasons, then it automatically reads/writes the other members within the group which is the whole point of multiplexing redologs.

correct me if i'm wrong.

[Hunan_1]

I've tried to clear the redo log, but didn't work either.

Thanks

[Brian Crabtree]

Stan,

Not quite. In an online situtation, Oracle will not hang saying that a redo-log is corrupted, but will mark it corrupt and put errors in the alertlog. On a startup however, it checks the logs, and verifies that they are valid before opening.

This has been true in my experiance. I might go try it out on a test system to see if this is still true.

Brian

[Indira Aramandla]

You could try this.

1.First, try to clean up any client connections by killing them at the operating system level. PMON becomes stuck trying to clean up one of these processes. You have to kill the instance at the operating system level.

2. You cannot open the database as you again receive the same errors on the corrupted redo log. You must bring the database to the mount stage and then clear the logfile with: ALTER DATABASE CLEAR UNARCHIVED LOGFILE ''

3. You are then able to open the database and do a clean shutdown.

4. At this point, you should take a backup and now fix the issue that caused the curruption (like hardware failure....etc)

Reasons (refer here)
The redo log header became corrupt due to hardware failure. As the archiver was unable to archive that log and processing continued, all redo logs were filled and not archived. Thus a log switch could not occur. Oracle attempted to terminate the instance but was unable due to some client processes still connected. After the instance is killed, it cannot be restarted until the corrupted redo log has been cleared. Clearing the redo log with 'UNARCHIVED' allows you to reuse that redo log. Refer to the SQL Reference Guide under the ALTER DATABASE statement for CLEAR LOGFILE options.

[Stan_17]

Hi Brian,

I agree that oracle will not hang if one of redolog member gets corrupted. however, lgwr moves on to next member in the redolog group. besides it writes errors on to alert.log and trace files...so the chances of introducing the same corruption at the same point in redolog is minimal.

similarly, when arch kicks in to archive, it reads the redolog member and if it encounters corruption, automatically it moves on to the next member in group and so on...

same happens on instance recoveries too.

Stan

[Yogeeraj_1]

hello,

please clarify whether your database is still up or is down.

If the later, unfortunately you have very few options for recovery.

regards
Yogeeraj

[PAVIC Thierry]

Hi, Do you have all your files except the redolog file ?

Do yo use the arcive log mode?

Is it possible to open database with nomount option ?

[Hunan_1]

Hi, I'll do my last try today.

After adding the line _allow_resetlogs_corruption=TRUE to init{SID}.ora, mount the database and run an sql script to recover all the datafiles(alter database recover datafile '...'), open the database. It seemed everything was ok, but it crashed after some minutes, the messages shown made reference to some blocks corrupted in the SYSTEM datafile.

Now the database is down, The database is not set to archive log mode, it's possible to startup the database with nomount option.

thanks all for your helping