Freespace in Tablespaces

[Former Member]

Dear all,

We are getting red alert message on Database administration in EWA report ,

Kindly check below logs :

We checked your system for tablespaces that may overflow in the near future.

10.2.1 Tablespaces - Autoextend Limit

Tablespace Size (KB) Free (KB) Avg. Chg/Week (KB) %-Used AutoExt %-Used after AutoExt

PSAPSR3 200028160 316992 5557700 99 204.800.000 98

PSAPSR3700 72826880 3591936 8429 95 174.080.000 40

SYSAUX 430080 31616 12596 92 10.240.000 4

You have set the 'autoextend' option for some tablespaces to a limited value. More than 95% of the limited size is already used.

Recommendation: Increase the values for the limits as soon as possible, or use SAPDBA to add new data files to the tablespaces of the table above.

Implementation: To determine the size required, consider the tablespaces' history of storage usage. Once you have added a data file, back up the extended tablespace. This ensures that the new state of the database can be recovered.

Kindly advise to resovle the above issues ..

[former_member524429 · Accepted solution]

Hi,

With Auto Extend enabled Tablespace/Datafile, you can ignore such utilization info.(98% or 99%).

I would recommend you to manage PSAPSR3* Tablespace manually based on your Database growth rate (daily/weekly/monthly DB growth based).

If you want to maintain all your tablespace with Auto Extend enabled, then make sure that you should have enough space available on filesystem.

PSAPSR3 200028160 316992 5557700 99 204.800.000 98

With the above detail, the space increment parameter for this tablespace is not set enough. Because even after extend with the next incremental space size, the tablespace will still have 98% utilized (which is above threshould value. you can ignore this with Auto Extend Maintenance). Moreover yout Table space's weekly grow is around 5GB.

You have set the 'autoextend' option for some tablespaces to a limited value. More than 95% of the limited size is already used.

You can reconfigure (provide higher limit value) increment parameter "File increment size in MB" of Auto Extend maintenance feature for the target tablespace/datafile so that it can extend with sufficient space within threshold space limit.

Regards,

Bhavik G. Shroff

[Former Member · Accepted solution]

Dear Shroff,

Kindly check the below file system for Database .

/dev/prddata1lv 120.00 64.72 55.28 54% /oracle/IRP/sapdata1

/dev/prddata2lv 120.00 68.96 51.04 58% /oracle/IRP/sapdata2

/dev/prddata3lv 120.00 74.36 45.64 62% /oracle/IRP/sapdata3

/dev/prddata4lv 120.00 67.81 52.19 57% /oracle/IRP/sapdata4

Kindly advise what i have to do to resolve the above issues .

[Former Member · Accepted solution]

Dear Shroff,

Pls check the show table space details through BRtools

BR1001I BRSPACE 7.00 (49)

BR1002I Start of BRSPACE processing: seerdask.dbw 2010-11-29 11.30.06

BR0101I Parameters

Name Value

oracle_sid IRP

oracle_home /oracle/IRP/102_64

oracle_profile /oracle/IRP/102_64/dbs/initIRP.ora

sapdata_home /oracle/IRP

sap_profile /oracle/IRP/102_64/dbs/initIRP.sap

space_function dbshow

space_copy_dir /oracle/IRP/sapreorg

scroll_lines 20

system_info orairp/orairp PRODORADB AIX 3 5 00018B6AD400

oracle_info IRP 10.2.0.4.0 8192 51738 758307642 PRODORADB

sap_info 700 SAPSR3 0002LK0003IRP0011D11604089540015Mainte

nance_ORA

make_info rs6000_64 OCI_102 Aug 2 2010

command_line /usr/sap/IRP/SYS/exe/run/brspace -p initIRP.sap -

s 20 -l E -U -f dbshow -c tsinfo

BR0280I BRSPACE time stamp: 2010-11-29 11.30.11

BR1009I Name of database instance: IRP

BR1010I BRSPACE action ID: seerdask

BR1011I BRSPACE function ID: dbw

BR1012I BRSPACE function: dbshow

BR1036I Class of information to be shown: tsinfo

BR0280I BRSPACE time stamp: 2010-11-29 11.30.14

BR0659I List menu 259 + you can select one or more entries

-

---

List of database tablespaces

Pos. Tablespace Type Status ExtMan. SegMan. Backup Files/AuExt.

Total[KB] Used[%] Free[KB] MaxSize[KB] ExtSize[KB] FreeExt. Lar

gest[KB]

1 - PSAPSR3 DATA ONLINE LOCAL AUTO NO 20/20

200192000 99.84 327168 204800000 4608000 21 79

8720:655360:614400:593920:593920+

2 - PSAPSR3700 DATA ONLINE LOCAL AUTO NO 17/17

72826880 95.07 3591936 174080000 101253120 17 612

3520:6123520:6103040:6082560:6082560+

3 - PSAPSR3USR DATA ONLINE LOCAL AUTO NO 1/1

20480 90.00 2048 10240000 10219520 1 1021

9520+:2048:0:0:0

4 - PSAPTEMP TEMP ONLINE LOCAL MANUAL NO 1/1

4362240 0.00 4362240 10240000 5877760 0 587

7760+:0:0:0:0

5 - PSAPUNDO UNDO ONLINE LOCAL MANUAL NO 1/1

10240000 0.16 10223552 10240000 0 585 280

1664:1372160:1024128:851968:474112

6 - SYSAUX DATA ONLINE LOCAL AUTO NO 1/1

430080 94.23 24832 10240000 9809920 22 980

9920+:17344:1728:704:576

7 - SYSTEM DATA ONLINE LOCAL MANUAL NO 1/1

901120 99.38 5568 10240000 9338880 3 933

8880+:5056:448:64:0

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-

---

BR0662I Enter your selection:

Kindly advise for resolving the above issue .

[former_member524429 · Accepted solution]

Hi,

For PSAPSR3700 and SYSAUX , the settings are looking normal based on their growth rate.

But,

1 - PSAPSR3 DATA ONLINE LOCAL AUTO NO 20/20
200192000 99.84 327168 204800000 4608000 21 79

With the above setting info, the next Extend size of PSAPSR3 is around 4GB which is less than the weekly growth rate (5.5 GB) of PSAPSR3 table space. Provide large Ext. Incr. size according to weekly growth rate (brttols -> space management -> Altering a Tablespace..).Treat it as warning only. With Auto Extend tablespace maintenance the space will be extended On demand within the allocated disk space for PSAPSR3 table space.

As the weekly growth rate of SYSSUX and PSAPSR3700 Tablespace are not so high , you can Turn off Auto Extend settings and do manual space management for them in your case.

Regards,

Bhavik G. Shroff

[Former Member]

Hi,

You can set auto extend for some perticular tablespaces which are increasing fastly using brtools or sapdba, In your system some tablespaces are more than 95 % , check them using db02. For immediate solution, you need to add data file using sapdba or brtools in that perticular tablespaces manualy.

Regards,

Kiran