Re: Tablespace - BIGFILE versus SMALLFILE

From: ddf <oratune_at_msn.com>
Date: Wed, 24 Sep 2014 10:21:17 -0700 (PDT)
Message-ID: <351dc5cf-5dec-41c5-a739-4bfb72d52d53_at_googlegroups.com>



On Tuesday, September 23, 2014 3:31:10 PM UTC-6, jimmy..._at_gmail.com wrote:
> We are using NetApp for our storage. OS nfs, not Direct NFS.
>
>
>
> OS: OEL 5.6
>
> DB: Oracle 11gR2
>
>
>
> Currently, we are using SMALLFILE. Here are the details for the non-partitioned table:
>
>
>
> NUM_ROWS : 2442000193
>
> SIZE_GB : 70.47
>
> BLOCKS : 8602880
>
>
>
> We have 3 data files, each is set to max_size of 25GB.
>
>
>
>
>
> I have been tasked with partitioning the table and moving the database to our new hardware.
>
>
>
> I'm recommending switching to BIGFILE. With an 8k data block size this data file can have a max size of 32TB.
>
>
>
> My question is what are the advantages/disadvantages of SMALLFILE/BIGFILE?
>
>
>
> Any known issues with BIGFILE with NetApp (NFS)?

The big issue is with backup/recovery and RMAN. Yes, BIGFILE tablespaces can make space management much easier for the DBA until backup time, when such large files can take considerably longer to backup.

Of course you need a file system that can contain a single 32TB file (should the tablespace reach that size) for your 8k block size database. If you use larger block sizes (up to 32K) the maximum size reaches 128TB.

SMALLFILE tablespaces (the default) are restricted to files no larger than 32GB (for an 8k block size) and the total number that is set by the db_files parameter. The maximum is operating system dependent; for Linux systems the typical hard limit is 64K. Since db_files is usually 1024 or less there shouldn't be an issue. Unfortunately to increase the db_files value you need to alter the system with scope=spfile then restart the database:

alter system set db_files=2048 scope=spfile; shutdown immediate
startup

This presumes you are using an spfile for startup; if not then the pfile needs to be edited and the database restarted.

It's up to you which path you take; there is good and bad in both, so weigh your options carefully and consider the backup window when choosing between SMALLFILE and BIGFILE. This is a good situation for a test or sandbox database to test how long backups take with SMALLFILE tablespaces and then with BIGFILE tablespaces.

David Fitzjarrell Received on Wed Sep 24 2014 - 19:21:17 CEST

Original text of this message