recover oracle 9i db without backup

Posted on 2005-03-28
Medium Priority
Last Modified: 2007-12-19

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.

Question by:u9707118
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 25

Assisted Solution

jrb1 earned 800 total points
ID: 13648036
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:


Assisted Solution

helpneed earned 800 total points
ID: 13649647

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.



Assisted Solution

helpneed earned 800 total points
ID: 13649663

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.

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Assisted Solution

adrian_ang earned 400 total points
ID: 13650877
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.
LVL 25

Accepted Solution

jrb1 earned 800 total points
ID: 13827170
Any luck?

Author Comment

ID: 13849791
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.


Author Comment

ID: 14428970
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.

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question