Decision on File system management in Oracle+SAP

[Former Member]

Hi All,

In my production system we use to have /oracle/SID/sapdata1 and oracle/SID/sapdata2. Initially there was many datafiles assigned to the table sapce PSAPSR3, few as autoextend on and few as autoextend off. As per my understanding DB02 shows you the information just tablespace wise it will report AUTOEXTEND ON as soon as at least one of the datafiles has AUTOEXTEND ON. In PSAPSR3 all the datafile with autoextend ON are from SAPDATA1 which has only 50 GBs left. All the files as Autoextend OFF are from SAPDATA2 which has 900 GBs of sapce left.

Now the question is :

1.Do I need to request for additional space for SAPDATA1 as some of the tablespaces are at the edge of autoextend and that much space is not left in the FS(sapadat1) , then how will they extend? DB growth is 100GB per month.

2.We usually were adding 10 GB of datafile in the tablespace with 30GB as autoextend.

Can we add another datafile from sapdata2 this time with autoextend ON and the rest will be taken care automatically.

Pleae suggest.

Regards,

VIcky

[Reagan · Accepted solution]

Hello

If there is a possibility to extend the sapdata1 file system then I would consider extending that to let the datafiles grow. if there is some restriction to that then you should consider turning off the Auto-Extend mode for all the datafile that reside in the sapdata1 file system.

I would do a calculation for all the datafiles which have not yet reached the MAXSIZE for the amount of space required to reach the MAXSIZE and then get the sapdata1 file system extended with that amount of space. All the subsequent tablespace extensions will be done on the sapdata2 file system.

Just having a well organised SAP/Oracle system.

Regards

RB

[Former Member · Accepted solution]

Hi Regan,Deepak, We have a maintenance window plan and the customer urges not to increase the space for next 3-4 months referring we still have 900 GB in sapdat2. Which means in my scenario. I have to turn off the autoextend for all the datafiles belonging to sapdata1 and then add a datafile from sapdata2 with autoextend ON to survive for next 3-4 months..?? Please help as this is a major concern. So in my scenarios lets say we generally add a datafile of 10GB and autoextend 30 GB(its the practice here and I had recently joined). So if I turn off the autoextend off will it effect in any way?? Also we need to monitor the growth of sapdata1. Regards, Vicky

[former_member188883 · Accepted solution]

Hi Vicky,

Which means in my scenario. I have to turn off the autoextend for all the datafiles belonging to sapdata1 and then add a datafile from sapdata2 with autoextend ON to survive for next 3-4 months..

This looks to be feasible option given with your scenario.

Hope this helps.

Regards,

Deepak Kori

[Reagan · Accepted solution]

If you turn off the autoextend mode OFF then the data file won't grow. When you have the file system sapdata1 extended with the amount of space for the data file to grow upto 30GB then you may switch ON the autoextend mode.

[Former Member · Accepted solution]

Hi Deepak/Regan, Thanks a lot for helping. Any other factor I shall consider. Ok one last concern. If a datafile already in place of size 10GB from sapdata1 with autoextend 30 GB. It might already be using with some part of the autoextend(10 GB + some space from autoextend). So before turning off the autoextend, any extra caution I shall take?? or simply turning off the autoextend of sapdata1 files after adding a datafile from sapdat2 with autoextend on  will automatically take care of files from further extending on sapdata1. I know this is not a great distribution/management(SAP/oracle) but it is like this here and I had recently joined this project.

[Reagan · Accepted solution]

I already gave you the answer in my last reply. When you disable the autoextend off the datafile will stop growing. When you extend a tablespace with a new data file of 10GB, it doesn't not contain any data until the database starts writing data into it. When the data file reaches the 10GB size it will not grow if the autoextend is turned off.

[Former Member · Accepted solution]

Hi Regan, Thats true. My concern was for the files from sapadat1 which has already crossed the 10 GB limit due to autoextend on feature. Lets say it is somewhere between 10-30 GB.So before turning off the autoextend do I have to take care of all such file. Or whatever be the size between 0---30 GB turning off autoextend will prevent files from further growing. And I can continue to survive with sapdata2 with autoextend on feature for couple of months. Thanks for your time & help.

[Reagan · Accepted solution]

See what Oracle says about turning off the autoextend mode.

Changing Datafile Size

Once you issue the autoextend off SQL statement the file won't grow anymore and there is nothing to be done before that.

[Former Member · Accepted solution]

Hi Vicky,

You just use the space from sapdata2 with auto extend ON, as you have to use it for 3 to 4 months then as per DB growth it will consume 300 - 400 GB from sapdata2.

After extending File system of sapdata1 you can move it to sapdata1 with auto extension ON.

[Former Member]

Hi,

Just add the tablespace on /oracle/SID/sapdata2 will be take care automatically.    

[Former Member]

hi Yogesh, You mean add a datafile from sapdata2 with autoextend on..??

[Former Member]

Yes.

[former_member188883]

Hi Vicky,

As you have 100GB/month growth suggestion here would be

1) Add 2 more mount points sapdata3 and sapdata4 with around 1 TB space.

   This is to distribute data across 4 data partitions for better performance

2) As sapdata1 has datafiles with auto extend ON, you need to extend the file system to 500 GB atleast so that whenever data is written on datafiles under sapdata1, it will have space to grow using autoextend feature. Without sufficient disk space it may lead to space problem and transaction may result in dump.

3) No need to change anything on sapdata2 as you already have 900GB free space

Hope this helps.

Regards,

Deepak Kori