topic Re: redo log corrupted in Operating System - HP-UX

redo log corrupted

Hunan_1 — Wed, 19 Feb 2003 13:22:47 GMT

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

any help would be really appreciated,

Re: redo log corrupted

Pete Randall — Wed, 19 Feb 2003 13:23:33 GMT

What do you mean by a "redo log"?

Pete

Re: redo log corrupted

Ian Lochray — Wed, 19 Feb 2003 14:52:55 GMT

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?

Re: redo log corrupted

Hunan_1 — Wed, 19 Feb 2003 17:39:58 GMT

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

Re: redo log corrupted

Stan_17 — Wed, 19 Feb 2003 18:37:58 GMT

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

Re: redo log corrupted

Hunan_1 — Wed, 19 Feb 2003 19:22:11 GMT

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

Re: redo log corrupted

Stan_17 — Wed, 19 Feb 2003 19:41:47 GMT

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

Re: redo log corrupted

Hunan_1 — Wed, 19 Feb 2003 20:14:12 GMT

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?

Re: redo log corrupted

Stan_17 — Wed, 19 Feb 2003 20:23:58 GMT

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.

Re: redo log corrupted

Brian Crabtree — Wed, 19 Feb 2003 20:39:24 GMT

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

Re: redo log corrupted

Stan_17 — Wed, 19 Feb 2003 21:17:51 GMT

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.

Re: redo log corrupted

Hunan_1 — Wed, 19 Feb 2003 21:38:16 GMT

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

Thanks

Re: redo log corrupted

Brian Crabtree — Thu, 20 Feb 2003 01:43:05 GMT

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

Re: redo log corrupted

Indira Aramandla — Thu, 20 Feb 2003 04:58:40 GMT

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.

Re: redo log corrupted

Stan_17 — Thu, 20 Feb 2003 05:46:03 GMT

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

Re: redo log corrupted

Yogeeraj_1 — Thu, 20 Feb 2003 06:13:10 GMT

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

Re: redo log corrupted

PAVIC Thierry — Thu, 20 Feb 2003 08:56:23 GMT

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 ?

Re: redo log corrupted

Hunan_1 — Thu, 20 Feb 2003 17:15:25 GMT

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