New data files not being utilized fully...

[Former Member]

Hi Experts,

Recently we have added few data files in a new directory as the old directory has reached the used % as 90% using df -h command.

/oracle/SID/sapdata1 214G(USED)   21G(AVAIL)  92%(USED%)         OLD Dir

/oracle/PRD/sapdata5 34G (USED) 201G(AVAIL) 15%(USED%)          NEW Dir

It is seen that the used % is increasing not only in the new directory but also in the old directory (now 92%).

We are unable to find the reason for used % increasing in old directory...

Please help.

Our OS system is x86_64 GNU/Linux

SAP ECC 6.0

ORACLE 10.2.0.4.0

Regards

Imaan

Message was edited by: imaan shaikh

[Former Member]

Hi Imaan,

I guess there are datafiles on sapdata1 FS which belong to TEMPORARY TABLESPACE.

Check the growth of the datafiles residing on sapdata1 using dba_hist_seg_stat and v$datafile.

Sometimes, temp datafiles cause this situation.

Br,

Venky

[Former Member]

Hi Imaan,

If you are still worried about the filling degree of sapdata1, then findout how many datafiles are on sapdata1 and check if they have autoext is set to ON. Switch it off using the following command.

ALTER DATABASE DATAFILE 'ABSOLUTE_PATH_OF_DATAFILE' AUTOEXTEND OFF;

This should avoid sapdata1 FS 100% full situation.

Br,

Venky

[Former Member]

Hi Experts,

Thanks for your inputs.

Venkatesh - we have set AUTO EXTEND OFF for the newly added table spaces.

My requirement is to make the below point clear:

1. We add a datafile for a table space, say for PSAP<SID> with 5 GB of space. Now the space is given from say, sapdata2. And the newly added file is <sid>_<nn> with 5GB.

2. This 5 GB is allocated to sid_nn from sapdata2. Before addition sapdata1 was at 90%. [new datafile added to sapdata2].

3. Now when i look at df -H, even the sapdata1 has moved from 90% to 92%. [note: AUTO EXTENDED was OFF for last added datafile in sapdata1]

So, my question is : why there is increase of 2% on sapdata1? there are no addition of files or any changes to file system done manually.

Please suggest.

Regards,

Imaan

[Former Member]

Hello Imaan,

So, my question is : why there is increase of 2% on sapdata1? there are no addition of files or any changes to file system done manually.

- If you have created data file for tablespace called XYZ in sapdata2, other tablespaces in the database will still use space from sapdata1 if those data files are not part of new sapdata2.

Hope it answered your question.

Thanks,

Siva Kumar

[Former Member]

Hi,

You have to check how they are mounted or shared.

By default table-space is shared across datafiles in Oracle DB so,it logical that olddata file will also grown in parallel..

Once it reaches 100% ,it will stop writing under OLD datafile which as expected and wont impact your system performance.

Hope this helps.

Regards,

Ashutosh

[Former Member]

Hello imaan,

You can find out the tablespaces and alter those table spaces to inculde new sapdata file now.

After that it will point to only new sapdata directories.

Regards,

Amit Barnawal

[former_member188883]

Hi Imaan,

When user performs a transaction in SAP system, data gets stored in the object "Table". These objects may reside in a particular datafile or across datafiles. When the growth in these table object increase..respective datafiles will also grow.

Impact of this growth will be seen in occupancy of disk space using df -k.

Regards,

Deepak Kori

[Former Member]

Hi

Growth of individual datafiles depend up on the tablespace linked to it. So, based on the db growth and individual TS growth, your data file grows.

~rajesh

[Former Member]

Hi,

This is how the oracle DB architecure designed.

Data blocks --> Extents --> Segments --> Tablespace (Logical Structure)

Each tablespace will have "n" number of datafiles associated to it which will be residing under your SAPDATA Filesystem (/oracle/SID/sapdatan)..

Even though you have added new SAPDATA filesystem, you might have extended only few tablespace's by adding new data files to it. So the growth on the new SAPDATA file system will be based on the tablespace growth that associated with corresponding data files resides in new File system. All other tablespace that has not having data files on newly created File system will continue to grow on the existing File system itself.

Don't worry too much about this. Just monitor your tablespace used_% and when any of your tablespace reaches above 85%, then add new data file to it under newly created SAPDATA File system. I hope it make you sense.

Please read some basics of oracle db administration and that will give you fair idea on this.

Best Regards,

Vasanth G