Consolidation of sapdata filesystems into /oracle/SID

[Former Member]

Hi:

We currently have 6 sapdata filesystems in our Oracle database as shown below. Each of these is 500GB large. Since only about 15% to 20% of the overall space is being used, our storage engineers are recommending that we get rid of the 6 sapdata filesystems and expand the /oracle/SID filesystem. Then we can just leave the sapdata directories and copy our datafiles there. This way, we will end up with one very large filesystem for all our Oracle datafiles. These are the current filesystems that we have:

/oracle/SID

/oracle/SID/102_64

/oracle/SID/mirrlogA

/oracle/SID/mirrlogB

/oracle/SID/oraarch

/oracle/SID/origlogA

/oracle/SID/origlogB

/oracle/SID/sapreorg

/oracle/SID/sapbkup

/oracle/SID/sapdata1

/oracle/SID/sapdata2

/oracle/SID/sapdata3

/oracle/SID/sapdata4

/oracle/SID/sapdata5

/oracle/SID/sapdata6

They will only be consolidating the sapdata filesystems into the filesystem /oracle/SID. All other filesystems will remain the way they are. I really don't know why they are adamant about having only 1 filesystem instead of recreating the 6 sapdatas we have but making them much smaller.

Does anyone see any issues with this from a performance, management, future upgrade, etc standpoint.

Thank you

[ashish_vikas · Accepted solution]

Hello Bayo,

Ya, you can create only one FS for oracle but is not recommended.

However you can keep only one SAPDATA<n> directory if all datafiles can be created in this FS. But in your case you allready have 6 sapdata directories and i gues they have different filesystem as well.

To overcome this, now you manually need to move your datafiles to any one sapdata1 directories..and then deleted that datafile in other sapdatan directory. But make sure you move datafiles in correct process not by simply copying and delete them as you have control file who keep entry of this. You can use brtools ofcourse. Also you need oracle downtime for this activity.

thanks

Ashish

[Former Member · Accepted solution]

Please can you explain why only one Oracle filesystem is not recommended. I would appreciate any supporting links if you have any too.

Thank you

[stefan_koehler · Accepted solution]

Hello Bayo,

as i already told you it has something to do with the ways how oracle accesses (async, direct i/o, etc.) the data and redolog files.

If you have just one file system (/oracle/<SID>) for all oracle files, all of them (including binaries, etc.) will be accessed with the mount option for /oracle/<SID> outside of the oracle process responsibility (oracle database instance). This will be problematic in cases of direct i/o or concurrent i/o. (for example see sapnote #948294, that i already mentioned)

Oracle and HP have also published a nice paper about that (with Oracle 9i, but for that topic it doesn't matter):

http://www.oracle.com/technology/deploy/performance/pdf/TWP_Oracle_HP_files.pdf

And a general doc about the i/o options:

http://www.rampant-books.com/t_oracle_direct_i_o.htm

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan:

Thank you for the explanation and supporting documents.

Regards

Bayo

[Former Member · Accepted solution]

Additional to the mount options, it is a VERY bad idea to combine for example the arch destination (/oracle/SID/oraarch) with directories like sapdata or saptrace/sapreorg in one filesystem.

If user trace files fill up the filesystem, then you will suffer an archiver stuck for no real reason. And then people spend lots of time trying to find the root cause of it...

Best regards, Michael

[anindya_bose]

I guess 64 bit unix or solaris...please confirm

you should consider the following when determining the number of datafiles:

1.Performance should be with a small number of big datafiles rather than a large number of small datafiles.

2.Operating systems often impose a limit on the number of files a process can open simultaneously. Oracle's DBW0 process can open all online datafiles. Oracle is also capable of treating open file descriptors as a cache, automatically closing files when the number of open file descriptors reaches the operating system-defined limit. Oracle allows more datafiles in the database than the operating system-defined limit; this can have a negative performance impact

But still I would go for more number of datafiles. Think about a situation when one of the data files got corrupted, then instead of recovering one particular small file virtually you have to restore and recover the whole database. You can keep the database open with other datafile functioning if no one is acccesing the corrupted data file.

If you are copying or moving some datafiles to another space for any reason , then a big data file can cause problem in few OS.

One doubt I have here, as we know "a datafile can be a part of one and only tablespace" how can we have 4-5 tablspace in one datafile?

One error reported with DB02 upto BASIS 640 is described in https://service.sap.com/sap/support/notes/682382

So, in my opinion we should use medium sized datafile. In our system we have 60 datafiles of size 10 G

[Former Member]

Anindya

This issue relates to filesystem and not datafiles.

Thank you

[stefan_koehler]

Hello,

@Anindya

> 1.Performance should be with a small number of big datafiles rather than a large number of small datafiles.

What? You need to explain this ..

> One doubt I have here, as we know "a datafile can be a part of one and only tablespace" how can we have 4-5 tablspace in one datafile?

What do you mean with this? Where is it mentioned that we have a data file in more than one tablespace?

@Bayo

> So just using one sapdata<X> should be ok? Could we have any issues during SAP upgrades as a result of using only 1

Everything will be fine with one sapdata<X> file system. (also during upgrades, etc.)

Regards

Stefan

[anindya_bose]

@stefan.. First time I got the question wrong. Later on Bayo explain the situation..

> 1.Performance should be with a small number of big datafiles rather than a large number of small datafiles.

it should be read like "Performance should be good with a small number of big datafiles rather than a large number of small datafiles. ".. again typo. reasons of this I explained

Edited by: Anindya Bose on Aug 9, 2009 3:57 PM

[stefan_koehler]

Hi Bayo,

> I really don't know why they are adamant about having only 1 filesystem instead of recreating the 6 sapdatas we have but making them much smaller.

I also wonder why the os admin doesn't shrink the file systems and return the unused space. Unfortunately you don't tell us the OS platform that you are using.

> Does anyone see any issues with this from a performance, management, future upgrade, etc standpoint.

I would recommend to keep one sapdata<X> file system. Why? Just think about the ways how oracle accces the data files (concurrent i/o, async i/o, etc.). If you just have on /oracle/SID file system any other files under this file system will also be accessed with this option, if you mount it with the corresponding i/o option.

Oracle/SAP recommends just to mount the sapdata<X>, online/offline redolog file systems with this option. (for good reasons - see sapnote #948294). So please keep one /oracle/<SID>/sapdata<X> file system.

Regards

Stefan

[Former Member]

Hi Stefan:

We use HP-UX 11.31. Shrinking the filesystems is an option but we keep seeing in a lot of places that it is not recommended. We don't want to corupt any data in the process of shrinking. I don't know if anyone can confirm that shrinking filesystems on HP-UX 11.31 is safe.

So just using one sapdata<X> should be ok? Could we have any issues during SAP upgrades as a result of using only 1. Just looking to make sure that changing the physical architecture does not alter anything in the future.

Regards

Bayo Odutola