How to monitor Tablespace Growth on AIX

[Former Member]

Hi Friends,

What is the correct way of analysing Tablespace growth in SAP ECC6.0 with Oracle 10g on AIX 5.3.

Below i have shown the tablespace of my QAC system

<b>

Filesystem GB blocks Used Free %Used Mounted on

/dev/qacdata1lv 35.00 29.53 5.47 85% /oracle/IRQ/sapdata1

/dev/qacdata2lv 35.00 25.00 10.00 72% /oracle/IRQ/sapdata2

/dev/qacdata3lv 35.00 32.70 2.30 94% /oracle/IRQ/sapdata3

/dev/qacdata4lv 35.00 29.15 5.85 84% /oracle/IRQ/sapdata4</b>

1. Since <b>sapdata3 is 94%</b> full above, should i increase the size of this mount point above?

Beacuse as its 94% adding datafile wouldnt make any sense, i think in this case the mount point should

be allocated more space, am i right (please correct me)

2. By looking at the Tablespace, do you think that i have suffiecient space for client copy, I have to perform

Production client copy onto Quality client.?

If you think that some tablespaces are 94% full above then should i first increase the mount point size

and then perform the client copy (please guide me as how should i go about it)

Regards

Aayush

Edited by: Ayush Johri on Jan 15, 2009 11:13 AM

[Former Member · Accepted solution]

Hi,

As part of system monitoring activity, keep tablespace monitoring also in the checklist.

Keep a benchmark for tablespaces for about 80%, if it reaches the stage extend tablespace immediately.

For client copy you need space in your system, best way is to extend the mount point first then start the client copy. otherwise client copy will get failed.

Hope it helps

Regards

KT

[Former Member]

Hai,

Filesystem monitoring can be done from ST06, no need to go to OS level.

You are right, first add space to the filesystem sapdata3 and then extend the tablespaces residing in the filesystem.

In your second question you have not shown the Tablespace statistics, anyway if the Tablespaces are 94% then it is better to extend by adding datafile if the Tablespace are AUTOEXTEND OFF, if they have ON option then extending the filesystem will help, i.e., extending the filesystem in which the Tablesapce is residing.

In PRODUCTION systems keeping the Tablespaces in AUTOEXTEND OFF is recommended, but this will need administration effort.

Regards,

Yoganand.V

[Former Member]

Hi Yoganand,

I appreciate your crisp and clear reply.

Here i would like to confirm few things

> As you said in my case i have to increase the size of the mount point, as sapdata3 mount point is 94% full, so i will do this.

> After that i dont think i shouldn't add the data file manually since all my taplespaces are with AUTOEXTEND ON, and i am not planning to make it OFF, since sap recommends to keep it with ON status. With ON the benefit is datafile would be added automatically as and when tablespace becomes full (am i right please correct) ?

> If you could through more light as why you suggested me to keep Tablespaces in AUTOEXTEND OFF in Production system, since by default its in ON status. I would like to know your perspective on this..

Regards

Aayush

[Former Member]

Hai,

AUTO EXTEND ON:

With this option, the data file is extended automatically as the data grows. However, the entire disk can still overflow. Therefore, be sure to regularly monitor space on the disk volume.

Use this method when you have enough space on the disk volume and the tablespace is not expected to grow too rapidly.

So if you are sure that the Tablespace will not grow rapidly then you can use. If you are going to do client copy then it is necessary to keep some good amount of free space in the filesystem (for ex., sapdata1,2,3,etc.,), because you have said that you are going to keep AUTO EXTEND ON.

With AUTO EXTEND ON option the datafiles are not added automatically but the exsisting one grows until the max extent is reached or until the filesystem over flows.

Keeping AUTO EXTEND OFF makes your tablespace to grow to the size of the data file you add. But you have to keep monitoring them every now and them to see whether they need to be extended or not.

But this is the work of any Basis admin, that is to monitor the systems.

This option is more or less vice versa of AUTO EXTEND ON option, in ON option you need to keep monitoring the Filesystem to prevent overflow of the filesystem.

Regards,

Yoganand.V

[Former Member]

HI Yoganand,

After looking into DB02, My Production server tablespace SYSTEM is 99% used.

Below i have shown the complete Tablespaces from my PRD server.

I would appreciate your suggestions as to what actions should i undertake immediately.

<b>tablespace Size(MB) Free(MB) Used(%) </b>

PSAPSR3 40,000.00 3,871.94 90

PSAPSR3700 71,120.00 4,390.88 94

PSAPSR3USR 20.00 18.13 9

PSAPTEMP 2,000.00 2,000.00 0

PSAPUNDO 8,860.00 8,649.81 2

SYSAUX 260.00 22.56 91

<b>SYSTEM 860.00 11.69 99</b>

As far as my File system space is concerned on AIX level(shown below), in all (sapdata1,2,3,4) i have 30% Free space, so i suppose as such there is no need to increase the File System SIze.

Since i have 30% of space left in each sapdata filesystem, do you think that i manually go and datafile in SYSTEM tablespace, as its AUTOEXTEND ON. cant it automatically allocate the space as its AUTOEXTEND ON.

<b>Filesystem GB blocks Used Free %Used Mounted on</b>

/dev/prddata1lv 60.00 27.23 32.77 46% /oracle/IRP/sapdata1

/dev/prddata2lv 60.00 26.37 33.63 44% /oracle/IRP/sapdata2

/dev/prddata3lv 60.00 34.48 25.52 58% /oracle/IRP/sapdata3

/dev/prddata4lv 60.00 30.67 29.33 52% /oracle/IRP/sapdata4

Regards

Ayush

[Former Member]

Hai,

Yes, you need not to worry about SYSTEM tablespace, it will extend itself.

Its is always better to keep an eye on the Filesystems if you have Tablespaces with AUTOEXTEND ON.

Regards,

Yoganand.V

[Former Member]

Hi Yoganand,

i m new to basis our system is on AIX. can u tell be how to check the file systems(sapdata1,2,3 etc).

can u give the commands in steps wise.

Regards,

Sameer

[Former Member]

Hello Sameer

df -k /oracle/SID/sapdata*

or df -kg /oracle/SID/sapdata*

it will list all filesystems ending with sapdata* folders in Kilobytes or GBs

for eg.

Filesystem GB blocks Used Free %Used Mounted on

/dev/qacdata1lv 35.00 29.53 5.47 85% /oracle/IRQ/sapdata1

/dev/qacdata2lv 35.00 25.00 10.00 72% /oracle/IRQ/sapdata2

/dev/qacdata3lv 35.00 32.70 2.30 94% /oracle/IRQ/sapdata3

/dev/qacdata4lv 35.00 29.15 5.85 84% /oracle/IRQ/sapdata4

second column: size in GBs

third column: tells the used space in GB

fourth : free space in GBs

fifth: so %used tells that 85% used out of 35GB

sixth: each sapdata* filesystem is having different mount point

you can always refer man help for unix commands

http://www.ss64.com/bash/df.html

thanks

Bhudev