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*.
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.
