Re: BIG File Tablespaces

yes, as specified in my message, the difference is a BIGFILE tablespace can only have 1 datafile, not a number thereof.  So in reality a BIGFILE is more limited by the Operating system and mount point than anything else inside the database.  The issue becomes how long to you want your backup software to take backing up that datafile, especially if it's sending that backup somewhere else in the world like a cloud server is another geographical location.  Having a database or tablespace in backup mode for days on end is not conducive to a quick recovery when crap hits the fan.  I had a database (version 8.2 pre RMAN days) that went into hot backup, the backup agent failed telling my sysadmin it had failed, but no one told me and the database remained in backup mode for days until the OS was shutdown for maintenance.  Surprise on database restart when we had to recover the db including restoring a pile of redo logs from tape.  What should have been a 5 minute restore ended up being an all nighter with 4 assembly lines of robots waiting.

Experience counts and it is sometimes painful.

On 2/20/2024 13:29, Sayan Malakshinov wrote:
> Hi RIchard,
>
> Each Oracle datafile can contain maximum /4194303 (4 Million) data
> blocks/.
>
>
> I'm not a fan of BIGFILE tablespaces too, but this part is not
> relevant for them.
> According to
> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/physical-database-limits.html
>
> A bigfile tablespace contains only one datafile or tempfile, which can
> contain up to approximately 4 billion ( 2^32  ) blocks. The maximum
> size of the single datafile or tempfile is 128 terabytes (TB) for a
> tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
>
> So they have the same limit of the number of datablocks:
> 2^32  for BIGFILE TS vs 2^22  * 1023 (max number of datafiles for TS =
> 2^10 ) for Small size TS.
>
>
> On Tue, Feb 20, 2024 at 6:12 PM richard goulet <rjgoulet_at_comcast.net>
> wrote:
>
> Each Oracle datafile can contain maximum /4194303 (4 Million) data
> blocks/. So maximum file size is 4194303 multiplied by the
> database block size.
>
> So, the maxsize of a bigfile tablepsace = 1*block_size*/4194303
> and would be restricted by the max file size of the mount point
> and operating system./
>
> /Now take into account the amount of time and other restrictions
> of the backup solution your using and I think you've a very BAD idea./
>
> /I've been through occurrences where a mount point gets hammered
> by IO, corrupted by other happenings on the system and even
> dropped by an OS admin.  Having multiple datafiles across multiple
> mounts is the best option.  The smaller the pieces you have to
> recover the faster it gets done.  And yes, I've been criticized by
> many a "guru" who likes take advantage of the latest and "best"
> practices over the years, but as my managers always observed when
> those practices resulted in disaster I got things back up quickly./
>
> /KISS: Keep It Simple, Stupid.
> /
>
> /
> /
>
> /Richard Goulet, MSGT USAF(ret)
> Previous Senior Oracle DBA and OCP
> /
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE
> http://orasql.org

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