Re: Corrupt datafile of TEMPORARY tablespace

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sat, 25 May 2002 08:47:58 +1000
Message-ID: <acmg1p$egj$1@lust.ihug.co.nz>

Eeeek! Run for the hills!

Lesson 1: how are your servers so easily switched off? Sort it.
Lesson 2: why are you allowing datafiles to autoextend? Sort it.
Lesson 3: why on Earth are you poking around with the internals of binary

data files? Stop it immediately.

All you have to do is to try and startup the database. When it falls over, it presumably will report a problem with 'file X' (a file number)... at which point you issue the command 'alter database datafile X offline', followed by a 'alter database open'.

Now you have the database open, you just 'drop tablespace temp', go back to hard disk and remove the data file(s) that was/were associated with that tablespace, and then back in SQL Plus, create a new proper temporary tablespace (which, with your version, should be using tempfiles, not datafiles... ie, the command should be 'create temporary tablespace temp tempfile 'path/filename' size 100M autoextend off' (pick an appropriate size and stick to it).

Then, you dispose of that hex editor as fast as you can, and you go away and learn something about basic backup and recovery techniques (in which the use of hex editors is considered something of a luxury).

Never, ever play around with any of the components of an Oracle database (controlfile, datafile, redo log file) in the way you've described here. Doing so will royally stuff everything. And next time it won't be a disposable datafile (like TEMP is), at which point.... 'Bye-bye data!'.

Regards
HJR "Neki Lik" <boris_at_illustro.hr> wrote in message news:aclv3d$dosu$1_at_as201.hinet.hr...
> Hi all,
>
> in one of our databases (Oracle 8.1.7.0.0. SE / WinNT) an user accidentaly
> swiched the server off while it was doing a big SQL query (and nothing
> else!).
> Now, database cannot start because temp datafile isn't as big as Oracle
> expect it would be. Autoexpand was on (on that datafile) and probably the
> datafile was expanding while user switched off the server.
>
> I supose that information in temporary datafile isn't important at all for
> the rest of the data (another tablespaces).
>
> My questions are next:
>
> 1. Is there any utility that can fix header of bad datafile?
> 2. Can I in any way put some other temp datafile in place of this one
> (created using exact same installation of Oracle, but with other SID, and
in
> the same tablespace)?
> 3. What and where should I change in header in that "replacement" datafile
> (using hex editor)?
> I tried to do this, and done some experimenting, and manage to realize
that
> among other things, header contains information of tablespace in which
> datafile belongs, SID of the database and, of course, block cheksums.
> Now, when I change those info (tblspace name and SID) dbverify reports
that
> cheksum is invalid and compute the cheksum (I manage to find where it
rests
> in file), but I can't change it since the checksum itself is used in
> calculation of cheksum (am I right?). Help.
> 4. Or there is another easy way to do it, and I'm just not aware of it?
>
> Thanks in advance...
>
>
>
Received on Fri May 24 2002 - 17:47:58 CDT