Re: Questions about using BIGFILE vs SMALLFILE

  Sandra,      

I have found bigfile being used in lots of projects   

at various clients and have found it useful for those   

environments, but, not sure if it is an industry   

standard or not..      

Anyway, the answer to whether bigfile is worth it   

for your project/database environment at this stage   

 could be determined after considering at least   

 some additional details about your db environment(s) :      

1. Size of database(s) being possibly considered

for bigfile..      

2. Type of databases (olap/oltp) & no. of datafiles   

and any concerning db file limits in control file..      

3. DB performance levels (current & desired)..      

4. Percentage of data growth & weekly operational activities related to data file additions..      

5. Backup window, storage server characteristics   

and network bandwidth for backup (for both prod   

 and lower environments).         

 6. Type of operating system & ability to handle   

 large o.s files..         

7. Any RTO that need to be factored in & risk   

level tolerance levels when single big datafile   

 restore doesn’t go as planned & if there are   

 false starts.      

8. Current tablespace layout limitations say -   

due to being based on companies’ LOB (lines   

 of business) that architecturally rule out   

 move towards a tablespace.         

btw - AFAIK, database can contain both smallfile   

and bigfile tablespaces. So, it shouldn’t be an   

either or proposition..   

 Regards,
Rajeev                        

>
> On Feb 16, 2024 at 7:42 PM, <Mladen Gogala (mailto:gogala.mladen_at_gmail.com)> wrote:
>
>
>
> On Fri, 2024-02-16 at 15:48 -0700, Sandra Becker wrote:
>
> >
> > OS - RHEL6
> > Oracle 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.
> >
> >
> >
> > Does BIGFILE support BLOB, CLOB and Geometry (spatial) data? We have a lot of spatial data as well as BLOBs and CLOBs.
> >
> > Will moving tablespaces from smallfile to bigfile require downtime?
> >
> > 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?
> >
> > 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.
> >
> >

>

>

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