Re: Problems with a datafile

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Fri, 29 Oct 1999 12:58:22 -0700
Message-ID: <3819FC5D.CBCD5326@vnl.nl>

"Jesus M. Lopez" wrote:

> Really, I have two similar tablespaces. To one of them I have added two
> datafiles (2000Mb each) without problems. With the other one I had the
> problem I have described and I have a datafile of 65Mb that I cannot
> enlarge. So, I don't think it is a problem of maxextents.
>
> I have put the datafile online, but now I cannot open the database, I get
> the following messages when I try to open it:
>
> ORA-01190: control file or data file 8 is from before the last RESETLOGS
> ORA-01110: data file 8: '/baandata/dat2/datbaanIV03.dbf'
>
> So, I don't know what to do with this file.
>
> Any idea is welcome.
> Thanks
>
> Jesus M. Lopez
>
> junlei_at_my-deja.com wrote:
>
> > The reason you can't make it bigger is probably due to the size of the
> > tablespace. Make sure your tablespace has its maxextents set to more
> > than what your datafiles need.
> >
> > Also, did you set autoextent on and maxextents for your datafile?
> >
> > Junlei
> >
> > In article <38187A0B.976EFB7_at_es.ibm.com>,
> > "Jesus M. Lopez" <lopez_at_es.ibm.com> wrote:
> > > First of all, my knowledge about ORACLE is not very deep.
> > >
> > > By mistake I added a datafile of 16Mb to a tablespace. My intention
> > was
> > > to add a datafile of 2000Mb.
> > >
> > > I tried to resize the file, but I couldn't resize it bigger than 65Mb.
> > >
> > > Then, I looked for a way to delete the file in order to recreate it
> > with
> > > the right size.
> > >
> > > Well, now I have the file with status offline. I suppose that the file
> > > doesn't have any data because I have other two files of 2000Mb each
> > and
> > > the volume of data is about 2400Mb.
> > >
> > > Please, help!
> > >
> > > My first intention is to get a file of 2000Mb instead the actual of
> > > 65Mb.
> > > But, if I should live with the small file forever, my question is
> > how
> > > can I bring it online, I'm running then database in NOARCHIVE mode.
> > >
> > > My environment is:
> > > - AIX 4.3
> > > - ORACLE 7.3.4
> > >
> > > Thanks in advance.
> > > Jesus M. Lopez
> > >
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

What was the error when you tried to enlarge? I suppose you did:
alter database datafile '/baandata/dat2/datbaanIV03.dbf' resize 2000M; and then what? You are sure /baandata/dat2 has enough space left?

BTW the assumption there will be no data in this particular datafile is extremely dangerous! There is no garantuee that oracle will not have used it. There is a way to find out by inspecting rowid's, but putting that you have only 2400M used and over 4G available is not the way.

Looking at your current error logs, it looks like you copied a previous version datbaanIV03.dbf back? You will have to do the resetlogs again, as you cannot afford to drop the original datafile.

One tip, tho: I would switch to archivelog mode asap. I assume this BaanIV shop cannot afford to loose 24hrs of work. --
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle

Work:                                Home:
----------------------------------   ----------------------------
Inter Access V&L                     Hunzestraat 4
Palatijn 3, 7521 PN Enschede         7555 WB Hengelo
PoBox 545, 7500 AM Enschede          (31)074-2425046

053-4341500 Received on Fri Oct 29 1999 - 14:58:22 CDT