Re: Db crashed with no backups :confused:

From: SEJ <sejprv_at_hotmail.com>
Date: Sun, 1 Feb 2009 11:09:04 +0100
Message-ID: <498574e7$0$90272$14726298_at_news.sunsite.dk>

"GS" <gs_at_gs.com> skrev i en meddelelse news:%PKgl.8139$PH1.1582_at_edtnps82...

> dbagtcol wrote:
>> On Jan 30, 5:32 pm, dbagtcol <cx4gt..._at_gmail.com> wrote:

>>> On Jan 30, 5:17 pm, gazzag <gar..._at_jamms.org> wrote:
>>>
>>>
>>>
>>>
>>>
>>>> On 30 Jan, 12:10, dbagtcol <cx4gt..._at_gmail.com> wrote:
>>>>> On Jan 30, 4:49 pm, gazzag <gar..._at_jamms.org> wrote:
>>>>>> On 30 Jan, 11:09, dbagtcol <cx4gt..._at_gmail.com> wrote:
>>>>>>> Hi all
>>>>>>> We've just had a scenario here; one of our dev db has just crashed
>>>>>>> due
>>>>>>> to unavoidable power failure. We've had no backup for this since the
>>>>>>> beginning. I could recover redo logfiles; I couldn't RECOVER
>>>>>>> datafiles
>>>>>>> though. I am trying out CREATE CONTROLFILE REUSE DATABASE orcl
>>>>>>> RESETLOGS NOARCHIVELOG option, provided I don't have backups for
>>>>>>> control files, redo logfile, datafiles, spfiles or anything. I am on
>>>>>>> RHEL 4, Oracle 10gR2.
>>>>>>> Please suggest.
>>>>>> Stop what you're doing now! Shut down the instance, if it's open,
>>>>>> and
>>>>>> take a cold (file system) backup. When you've done that, we can go
>>>>>> from there.
>>>>>> HTH
>>>>>> -g
>>>>> I'd appreciate if you could elaborate. I've already taken cold back up
>>>>> for all datafiles.
>>>>> Thanks
>>>> 1. Have you taken backups of the control file and SPFILE too?
>>>> 2. Is your database in archivelog mode?
>>>> 3. Why are you trying to recreate the control file?
>>>> 4. What happens when you issue a STARTUP?
>>>> HTH
>>>> -g- Hide quoted text -
>>>> - Show quoted text -
>>> 1. I have backups for them.
>>> 2. No.
>>> 3. Seems like control files, logfiles and datafiles are corrupt, due
>>> to power failures.
>>> 4. It gives:
>>> ORACLE instance started.
>>>
>>> Total System Global Area 2147483648 bytes
>>> Fixed Size 1268508 bytes
>>> Variable Size 402654436 bytes
>>> Database Buffers 1728053248 bytes
>>> Redo Buffers 15507456 bytes
>>> Database mounted.
>>> ORA-01589: must use RESETLOGS or NORESETLOGS option for database open-
>>> Hide quoted text -
>>>
>>> - Show quoted text -

>>
>> And when I try to open it with resetlogs option,
>> SQL>  alter database open resetlogs;
>>  alter database open resetlogs
>> *
>> ERROR at line 1:
>> ORA-01194: file 1 needs more recovery to be consistent
>> ORA-01110: data file 1: '/u01/app/oracle/oradata/d2he/system01.dbf'
>
> Umm, have you tried:
>
>  "recover datafile '/u01/app/oracle/oradata/d2he/system01.dbf';"?
>
> IIRC, I have had crashes due to power outages and had to do this to each 
> datafile before the instance would open.
>
>
>

Hi

In two cases where a customer had no backup and their DB crashed I have used the parameter below, If any prober recovery fails you can get the database open but it wil not be consistent, try google this "_allow_resetlogs_corruption"

regards SEJ Received on Sun Feb 01 2009 - 04:09:04 CST