RE: Increase size of data files and rollback segments

Yechiel,

Thanks for pointing out the potential issues with autoextending onto an exact 4 Gb boundary (as I forgot to include them).

Reading the bug you referenced (at http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=BUG&p_id=1668488) it says:
"...
Does not reproduce on 8.1.7 Sun Solaris. ...
Rediscovery Information: Resize a datafile file to [exactly] 4GB, 8G, 12G, 16G, etc. After resizing a datafile to 4G, alter system checkpoint was failing with ORA-27069 ...
This fix is in 8.1.7.4.1 and 9.0.1.4.0 but not in 9.2.0.2.1"

I am on 8.1.7.4.5 and so am not affected by this issue - I can't remember the version the original poster was on.

Anyone using 817x may also want to check note 120607.1 (http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=120607.1) titled "Support Status and Alerts for Oracle8i Release 3 (8.1.7.X)" for information on this and other issues.

This refers you to Note 148894.1 (http://metalink.oracle.com/metalink/plsql/showdoc?db=Not&id=148894.1) titled "ALERT: Problems with Datafile AUTOEXTEND/RESIZE on Oracle8i on NT/2000 Platforms"

This contains the information "... A fix is now available in 8.1.7.1.4 <Bug.1823173> and will be included in 8.1.7.3 <BUG.1794199> ..."

For general Oracle information on 2Gb files see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=62427.1&p_database_id=NOT titled "2Gb or not 2Gb - File limits in Oracle". This contains links to other notes with Port specific information.

I hope this helps.

Regards,
Bruce Reardon

-----Original Message-----
Sent: Wednesday, 11 December 2002 11:19 PM

Hello Jeremiah

I did some research on metalink and it says:

1. Do not use datafiles more then 4GB on NT systems.
2. There is a bug on NT, W2K that if you resize datafile (direct command or autoextend) to 4GB boundary, i.e 4,8,12, then there are two conditions: 2.1) No archive log - database crashes but you can start again and resize the datafiles. 2.2) Archive log - restore and regenerate to a point in time prior before the resize. This bug was reported also on 9.2.

The work around is to resize to 4.1, 8.1 GB datafiles.

Bug number 1668488.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 11, 2002 12:54 PM

> On Wed, 11 Dec 2002, Yechiel Adar wrote:
>
> > Do not allow your datafile to autoextend across 4GB boundary.
> > There is a bug that cause this datafile to be unusable.
>
> I think the 4Gb limit is confined to a handfull of older operating
> system versions or older Oracle versions. For about the last five
> years I have been accustomed to creating 16Gb datafiles with no
> problem.
>
> Imagine trying to build a 5 terabyte data warehouse out of 1900Mb
> datafiles! It would require 2760 datafiles!
>
> Can anyone confirm that this is no longer a problem after certain
> versions of O/S and Oracle?
>
> Note another mean bug (8.1.6.2 / HP-UX 64-bit) where Oracle lets you
> specify no size for a datafile, then adds it to the controlfile /data
> dictionary in a way that makes it look like it has a ton (like a
> terabyte) of free space. The datafile can't be resized or offline
> dropped, and the tablespace must be dropped and recreated (unless you
> get the patch). Let one segment extend into there and watch the
> ORA-600s.
>
> --
> Jeremiah Wilton
> http://www.speakeasy.net/~jwilton
>
> > ----- Original Message -----
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, December 11, 2002 2:46 AM
> >
> >
> > > As a start, look up the following commands in the SQL Reference guide:
> > >
> > > alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' autoextend
on
> > maxsize 20480M;
> > > Read up on the implications of autoextend and whether you want it
> > >
> > > alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' resize
10240M;
> > >
> > > For rollback datafile - same as any other datafile, eg:
> > > alter database datafile 'C:\ORADATA\fred\ROLLBACK1.DBF' autoextend on
> > maxsize 10240M;
> > >
> > > You may then need to add another rollback segment or increase the max
> > extents of an existing one. eg to add another rollback segment:
> > > CREATE PUBLIC ROLLBACK SEGMENT r09_big
> > > TABLESPACE rollback
> > > STORAGE
> > > ( minextents 20
> > > INITIAL 10M
> > > NEXT 10M
> > > MAXEXTENTS UNLIMITED)
> > >
> > > (you would then need to bring this online - eg "alter rollback segment
> > r09_big online;")
> > >
> > >
> > > For tempfile (ie LMT temporary tablespaces):
> > > alter database tempfile 'C:\ORADATA\fred\TEMP1.dbf' autoextend on
maxsize
> > 10240M;
> > >
> > > Remember - don't use the exact sizes I've shown - alter to suit your
case
> > (these were part of a huge load into a new test system)
> > >
> > >
> > > Hope this helps (and willing to learn if some of the above could be
> > improved).
> > >
> > > Regards,
> > > Bruce

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).