RE: System tablespace datafile lost - NO archivelog - Recoveryoptions...???

@bartthebear  

Sorry, Bart san, I'm not ready yet to commit sepukku. It wasn't I who did the dead, but trying to preserve a customers honour ;-)  

@tim  

there is always a solution, and otherwise one should start building one ;-)  

@Kurt / Carel Jan  

Thanks very much for the fast / personal help. I still have to investigate what this client, who came for help, has left on backup tapes etc. The client thought he still had a backup of 2001 (corrupted system is of 2003), so maybe the combination of the 2001 system tablespace and the rest of 2003 can result in a restorable system (with maybe not so much loss of data).  

Anyway I should have known (CALL ORA600 :-).  

AND I should not have forgotten...maybe googling a little bit more and it would have been appeared to me again, but how many times does one encounter (should one encounter) this kind of situation.  

Apparently the customer had no backup and garbled up the system tablespace. After detecting a harddisk corruption they applied an OS verify util that came up with 3 neat files that once where part of the system tablespace datafile, and then they noticed that they hadn't a backup.  

@Kurt  

Kurt, Thanks. I must investigate a little bit more what options I still have AND how far the customer wants to go regarding restoring the data. If it is as hopeless as the customer describes and they are willing to restore the data, then be assured I will contact you.  

As said, thanks !

From: Kurt Van Meerbeeck [mailto:kurtvm_at_pandora.be] Sent: Mon 9-10-2006 21:06
To: cjpengel.dbalert_at_xs4all.nl; tim_at_evdbt.com; Marco Gralike Cc: oracle-l_at_freelists.org
Subject: Re: System tablespace datafile lost - NO archivelog - Recoveryoptions...???

>
>
> >His tool (I've seen it working) parsed data files 'guessing' table layouts.
> This system ended up with 15000 tables, each exported in a dump file, each
> table numbered from 1..15000. A bunch of Baan consultants >started (re)naming
> the tables based on the column type/order, and contents. After 2 weeks pretty
> much most of the data was there again.

For the record - the db had 1500 tables (not 15000) ;-) - but nevertheless - it was 68Gb of raw data without a SYSTEM tablespace. Apparently - Baan dba's/consultants are used to working with flatfiles. So just by looking at the flatfiles they knew immediate which flatfile was which table. They even made me alter my code to produce a particular date format so they could run it through their BAAN loader directly. After 2 days they had their critical parts back up and running - after 2 weeks - the complete db was loaded.

Cheers,
Kurt

At 20:51 9/10/2006 +0200, Carel-Jan Engel wrote:
>
> Marco,
>
> Go to <http://www.ora600.nl <http://www.ora600.nl/> >www.ora600.nl
> This is the site of Kurt van Meerbeeck. This guy has done great jobs, and it
> is not all that expensive (a lot cheaper than DUL, and you can DIY).
>
> He was one of the teacher/presenters at the Miracle Downtime High Performance
> Bootcamp in June. Tuomas Pystynen was another one, and I happened to be the
> 3rd. Kurt sat next to me in my car for 16 hours driving to and from
> Copenhagen, so we had some time to chat.
>
> One of his most remarkable stories was a Baan database. After a major crash
> it appeared tapes were never aged out, and a lot of tapes appeared
> unreadable. They managed to get all datafiles collected from a series of
> tapes spanning a month, except SYSTEM. Not that much of a consistent backup.
>
> His tool (I've seen it working) parsed data files 'guessing' table layouts.
> This system ended up with 15000 tables, each exported in a dump file, each
> table numbered from 1..15000. A bunch of Baan consultants started (re)naming
> the tables based on the column type/order, and contents. After 2 weeks pretty
> much most of the data was there again.
>
> Even better, if you have a system tablespace of say, a development clone, it
> can use that one to determine the layout and will pretty much be able to
> restore the whole lot.
>
> HTH, Best regards,
>
> Carel-Jan Engel
>
> ===
> If you think education is expensive, try ignorance. (Derek Bok)
> ===
>
>
>
> On Mon, 2006-10-09 at 10:13 -0600, Tim Gorman wrote:
>>
>> It's kind of like voting for president in North Korea. Only one choice --
>> under any circumstances -- when in NOARCHIVELOG mode: full database restore
>> from cold backup. There are no options for partial recovery or any recovery
>> at all. NOARCHIVELOG mode = full database restore, no recovery, period.
>> This scenario is no exception.
>>
>> So, vote for Kim Jong Il...
>>
>>
>>
>> Marco Gralike wrote:
>>>
>>> Got a nice one. What are my options?
>>>
>>> Situation.
>>>
>>> Old Oracle 7.3 database on Novell Netware 4.11 server, which is NOT in
>>> archivemode AND lost the system tablespace datafile...
>>>
>>> I know off / and performed, in my days, nice tricky anc challenging
>>> recoveries with hidden "corruption allowed" etc parameters, but uptill now
>>> I always had a system tablespace datafile to work with...
>>>
>>> Has someone ideas?
>>>
>>>
>>>
>>> Thanks
>>>
>>>
>>> Marco
>>
>>
>>
>>
>> --
>> Tim Gorman
>> consultant - Evergreen Database Technologies, Inc.
>>
>> website = <http://www.evdbt.com <http://www.evdbt.com/> >http://www.evdbt.com <http://www.evdbt.com/>
>> email = <mailto:tim_at_evdbt.com>tim_at_evdbt.com
>> mobile = +1-303-885-4526
>> fax = +1-303-484-3608
>> --
>> <http://www.freelists.org/webpage/oracle-l>http://www.freelists.org/webpa
>> ge/oracle-l
>

--
Kurt Van Meerbeeck
kurtvm_at_pandora.be
kurt_van_meerbeeck_at_axi.be
dude_at_ora600.org
http://www.ora600.org <http://www.ora600.org/> 

Imagination is more important than knowledge... (A.Einstein)

You can have many different jobs and still be lazy... (H.Simpson)








--
http://www.freelists.org/webpage/oracle-l