Re: Questions about using BIGFILE vs SMALLFILE

On Fri, 2024-02-16 at 15:48 -0700, Sandra Becker wrote:
> OS - RHEL6Oracle EE - 12.2
>
> We have a new DBA that is insisting using BIGFILE has been the industry
> standard for the past 10 years.  Not something I was aware of since my
> current and last 3 employers chose not to use it.  As team lead, I'm
> trying to find more information to decide if it's really worth the effort
> to move all our tablespaces to BIGFILE format before I make any
> recommendations to my management.
>
> First some background on our environment:   
> * We have an incredibly bad database design that was done some 20 years
> ago and management will not approve a redesign.  
> * We do use ASM, which does simplify datafile management for us.
> * About 80 percent of the application tables belong to a single schema. 
> Roughly 90 percent of all application tables reside in the same
> tablespace.  
> * Thankfully, most indexes are in a separate tablespace.   
> * Many of the tables have a BLOB, CLOB or spatial data, sometimes all
> three in the same table.
> * We are in the middle of a project to rewrite applications and move all
> data off of on-prem Oracle databases to AWS hosted postgres databases.
> * New management has expressed the need to accelerate the move off of
> Oracle  (still expect it to take another 2-3 years even if accelerated).
> - There will be no more upgrades or patches to the Oracle environment. 
> (We also no longer have Oracle Support.  Financial decision.).
> * Any changes cannot incur downtime for the production databases.
> * Moving to BIGFILE also assumes we'll actually get new ASM storage, not
> a
> given due to some budget cuts.
> I have some questions regarding what's involved and supported.
> 1. Does BIGFILE support BLOB, CLOB and Geometry (spatial) data?  We have
> a lot of spatial data as well as BLOBs and CLOBs.
> 2. Will moving tablespaces from smallfile to bigfile require downtime?
> 3. None of our tablespaces are over 175 datafiles currently, most are
> under 20 with minimal growth expected.  Would we really gain anything
> by making the change?
> 4. Would BIGFILE on ASM really be any better than our current SMALLFILE
> on ASM given the project to move off of Oracle completely?
> Since the major projects are to get us off of Oracle sooner rather than
> later, is this really where we should be focusing our time and energy? 
> My inclination is to say no, the team should be focusing on learning to
> administer and monitor the AWS postgres, as well as the MSSQL
> servers/databases that will not be migrated to postgres.  I've been
> reading some articles, but wanted to get the feedback from people who
> have done the move from smallfile to bigfile.
>
> Thank  you for your feedback.
>
> --
> Sandy B.
>

Hi Sandy,
I wouldn't quite agree that BIGFILE tablespaces are industry standard. I did run into them few times in my life, but none of my employers were using them consistently. The problems that I have run into with the BFT (BIGFILE tablespaces, not to be confused with the BFG from the game of Doom) are that it is practically impossible to move them to different storage and that it's rather hard to back them up and restore them. RMAN can do BFT in parallel, but backing up a 5 TB tablespace is rather slow and problematic. I have also run into a bug, albeit on Oracle 11g, where file rebalancing on ASM took a significant amount of time with BFT. What does your colleague say, what would be the advantages of using BFT? Your colleague should subscribe to this list and argue his point. Have a good weekend!

-- 
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com


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