recover oracle 9i db without backup


I have an oracle 9i database running at home under RHEL 3.
Somehow, one of the redo logs got  corrupted, and now the database will not open properly.
BTW, I have no backups.

I logged in as sysdba and did a 'recover database until cancel'.  I then got the following message:
"ORA-01589: must use RESETLOGS or NORESETLOGS option for database open"

So then I did: "alter database open resetlogs;" and got the following message:
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/icat/system01.dbf'

I tried the two steps above after raking around the internet, but as you can probably tell, I'm not
really sure what I am doing.  Does any one know how I can recover the database?

If this is not possible, then how do I get the database back into a working state so that I can recreate
my schema's from scratch.

Many thanks.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jrb1senior developerCommented:
Are you in archivelog mode?  If you lose a redo log again, don't recover the rest of the database.  That is recovering the database from the archive logs.  Instead, you'll probably want to reset the redo log through something like:


i think u can perform a time based recovery

do the following

1)Back up the database as a precaution and correct any media failures.

2)Restore backup control files (if necessary) and backup datafiles.

3)Perform media recovery on the restored backup using the RECOVER DATABASE statement with the UNTIL TIME option.

To restore the files necessary for time-based recovery and bring them online:

(1)If the current control files do not match the physical structure of the database at the intended time of recovery, restore a backup control file that reflects the database's physical file structure at the point at which incomplete media recovery should finish. To determine which control file backup to use:

Review the list of files that corresponds to the current control file and each control file backup to determine the correct control file to use.

If necessary, replace all current control files of the database with the correct control file backup.

Alternatively, create a new control file to replace the missing one.

(2)Restore backups of all the datafiles of the database. All backups used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to January 2 at 2:00 p.m., then restore all datafiles with backups completed before this time. Follow these guidelines:

1)if You do not have a backup of a datafile--Create an empty replacement file, which can be recovered

2)if A datafile was added after the intended time of recovery --- Do not restore a backup of this file, since it will no longer be used for the database after recovery completes

3)The hardware problem causing the failure has been solved and all datafiles can be restored to their default locations ----Restore the files and skip Step 5 of this procedure

4)A hardware problem persists--Restore damaged datafiles to an alternative storage device

(3)Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

% sqlplus sys/change_on_install@prod1

(4)Start a new instance and mount the database:


(5)If one or more damaged datafiles were restored to alternative locations in Step 2, indicate the new locations of these files to the control file of the associated database. For example, enter:

ALTER DATABASE RENAME FILE "/oracle/dbs/df2.f" TO "/oracle/newloc/df2.f";

(6)Obtain the names of all datafiles requiring recovery by:

Checking the list of datafiles that normally accompanies the control file being used.

Querying the V$DATAFILE view.

(7)Make sure that all datafiles of the database are online. All datafiles of the database must be online unless an offline tablespace was taken offline normally. For example, to guarantee that a datafile named user1 (a fully specified filename) is online, enter the following statement:


If a backup of the control file is being used with this incomplete recovery (that is, a control file backup or re-created control file was restored), indicate this in the dialog box or command used to start recovery. If a specified datafile is already online, Oracle ignores the statement.

To perform time-based recovery:

Issue the RECOVER DATABASE UNTIL TIME statement to begin time-based recovery. The time is always specified using the following format, delimited by single quotation marks: 'YYYY-MM-DD:HH24:MI:SS'. The following statement recovers the database up to a specified time using a control file backup:


Apply the necessary redo log files to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup, you must supply names of online logs.

Apply redo log files until the last required redo log file has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.



01194, 00000, "file %s needs more recovery to be consistent"

Cause:  An incomplete recovery session was started, but an insufficient
         number of logs were applied to make the file consistent. The
         reported file was not closed cleanly when it was last opened by
         the database. It must be recovered to a time when it was not being
         updated. The most likely cause of this error is forgetting to
         restore the file from a backup before doing incomplete recovery.
Action: Either apply more logs until the file is consistent or restore
          the file from an older backup and repeat recovery.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

do you find some information in the alert log file and trace files?If there is, please post it.If  a redo log file is corruptedd it depends on the type of  the file.If it was not the current redo log file ,so a checkpoint already occured and you will not loose information, just drop it and create a new one(search for more detailed info in the Oracle documentation about the exactly steps). If the current redo log file was corrupted ,and as you said you are not running in archive log , I think that you will lose info, the most reacent info , which is hold in that redo log.This situation is also complicated and you have to investigate it deeper.Oracle doesn't reccomend doing a  point in time  recovery if you haven't enough experience. There is a view which can show you info about redo logs , se Oracle Administrator's Guide in the Documentation for more info , not sure , something like v$log_files.Check it! It's all written in the docs.
jrb1senior developerCommented:
Any luck?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
u9707118Author Commented:
Sorry guys, I haven't had a chance yet to try any of your suggestions.  I am away tomorrow (for a couple of weeks).
I will try them out as soon as I get back.

u9707118Author Commented:
Sorry for taking so long...
I still have not found the time to look at the problem and cannot guarantee when I will be able to look at it.
For now I have just split the points.

When I do get time to sort it out, if any of the above answers do not solve the problem....I'll just post a new question.

Thanks to all those who tried to help out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.