DB02 showing 80% full data where as full file system is empty

[Former Member]

hi all,

In DB02 transaction of my BW production server i'm getting following entry on Database tab:

Name DEFAULT

DB system: ORA

Size: 45,83 GB Total size: 156,25 GB

Free Size: 9,16 GB Total free size: 119,58 GB

Used: 80 % Total used: 23 %

where as in file system i have hardly any space filled.

Now what does this 80% used signifies?

Where does my data goes while data loading? Does it goes in this module? if so then during next dataload my database will be full?

kindly help.

Regards,

Priya

[Former Member · Accepted solution]

Hi, are you see warnings in Db13 t-code --> check database ?

Explination of your "sizes" -->

Size: 45,83 GB v --> the size is currently allocated in file system

Total size: 156,25 GB the size is MAX calculeted (Your DB can grow up tothis size in file system) because the AUTOEXTEND are turned ON.

Free Size: 9,16 GB --> really free space in DB now

Total free size: 119,58 GB Free size --> total free autoextendable size ...

Used: 80 % --> 45,83 DB, free 9,16 (20%) --> used 80% (36,67)

Total used: 23 % --> Total (inc AUTOEXTEND) size are 156,25 (100%) --> used 36,67 == 23%

Regards.

[Former Member · Accepted solution]

Hi Sergo,

In DB13 check database i am getting finished with warnings:

I'm getting following warnings:

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/F0PA_C01

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/FZEMPTRSFR

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/FZEMP_ATTR

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/FZEMP_CNT

BR0280I BRCONNECT time stamp: 2010-06-20 03.31.23

BR0972I Checking database operations...

BR0973W Database operation alert - level: WARNING, operation: Archive log backup, condition: Operation too old or has not been run in clean-up period

BR0973W Database operation alert - level: WARNING, operation: Complete database backup, condition: Operation too old or has not been run in clean-up period

BR0280I BRCONNECT time stamp: 2010-06-20 03.31.23

BR0974I Checking database messages in /oracle/IBP/saptrace/background/alert_IBP.log ...

BR0976W Database message alert - level: WARNING, line: 581158, time: 2010-06-14 00.30.50, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581176, time: 2010-06-14 00.30.57, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581194, time: 2010-06-14 00.31.09, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581212, time: 2010-06-14 00.31.21, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581230, time: 2010-06-14 00.31.34, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581248, time: 2010-06-14 00.31.45, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581266, time: 2010-06-14 00.31.58, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581647, time: 2010-06-15 00.30.40, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581658, time: 2010-06-15 00.30.52, message:

Checkpoint not complete

BR0976W Database message alert - level: WARNING, line: 581676, time: 2010-06-15 00.31.04, message:

Checkpoint not complete

BR0280I BRCONNECT time stamp: 2010-06-20 03.31.24

BR0977I Checking database profile...

BR0978W Database profile alert - level: WARNING, parameter: LOG_BUFFER, value: 14242816 (>< 4096,512 KB)

BR0280I BRCONNECT time stamp: 2010-06-20 03.31.24

BR0980I Number of changed database profile parameters: 0

Since my current database size is 80% full i would have to expand its size. Can you please guide me how to increase size of my database?

Thanks & Regards,

Priya

[Former Member · Accepted solution]

Now i can't understand your situation, what size File system's you have (where your Oracle files are placed) ?

Try to check in DB02 --> tablespaces --> Overview how your autoextend's are allocated for tablespaces, check (compare) the

SIZE(MB) and Total SIZE(MB) ((or check the "sum of all free fragments")), check all tablespaces have the autoextend to YES ?

Regards.

[Former Member · Accepted solution]

hi this is the details. all tablespaces are Autoextend set as yes. few of the table spaces are 100% full though. My question is do i need to increase the table isze or will it get expanded automatically as it is auto extend enable? If i have to do it manually, then can you please tell me how to do it?

Tablespace name Size(MB) Free(MB) Used(%) Autoextend Total size(MB) Total free space(MB) Total used (%) #Files #Segments #Extents Status Contents

PSAPSR3 24660.00 13.06 100 YES 40000.00 15353.06 62 4 50421 74674 ONLINE PERMANENT

PSAPSR3700 9560.00 11.13 100 YES 40000.00 30451.13 24 4 767 5130 ONLINE PERMANENT

PSAPSR3DB 5000.00 2748.88 45 YES 30000.00 27748.88 8 3 2106 3883 ONLINE PERMANENT

PSAPSR3USR 20.00 19.31 3 YES 10000.00 9999.31 0 1 10 10 ONLINE PERMANENT

PSAPTEMP 2980.00 2978.00 0 YES 10000.00 9998.00 0 1 0 0 ONLINE TEMPORARY

PSAPUNDO 4340.00 2936.44 32 YES 10000.00 8596.44 14 1 12 320 ONLINE UNDO

SYSAUX 200.00 37.13 81 YES 10000.00 9837.13 2 1 986 1900 ONLINE PERMANENT

SYSTEM 470.00 16.13 97 YES 10000.00 9546.13 5 1 1204 2364 ONLINE PERMANENT

Regards,

Priya

[Former Member · Accepted solution]

Ok, but you not answer on my question

what size File system's you have (where your Oracle files are placed) ?

Now i can see what all tablspaces are have autoextend YES --> all tablspaces are OK (And even more than necessary)

except the PSAPSR3 tablspace ... Usually this TB are the biggest in DB and growing rapidly . Try to check the history of changing this tbs. and check what time you have enough the remaining 15Gb.

If need you can add more data files using BRTOOLS (BRSPACE) -->

http://help.sap.com/erp2005_ehp_04/helpdata/EN/30/3d333b5f3ad646e10000000a114084/frameset.htm

Regards.

[Former Member · Accepted solution]

hi sergo,

following is the file system:

/dev/oraclelv 1.50 0.00 1.50 1% /oracle

/dev/clientlv 1.00 0.14 0.86 15% /oracle/client

/dev/stage_102_64lv 10.00 4.95 5.05 50% /oracle/stage/102_64

/dev/sap_ibplv 10.00 5.44 4.56 55% /usr/sap/IBP

/dev/sapmntlv 5.00 2.54 2.46 51% /sapmnt/IBP

/dev/oracle_ibplv 4.00 0.12 3.88 4% /oracle/IBP

/dev/ibp_102_64lv 10.00 3.59 6.41 36% /oracle/IBP/102_64

/dev/sapreorglv 2.00 0.00 2.00 1% /oracle/sapreorg

/dev/origlogAlv 1.00 0.11 0.89 11% /oracle/IBP/origlogA

/dev/origlogBlv 1.00 0.11 0.89 11% /oracle/IBP/origlogB

/dev/mirrlogAlv 1.00 0.10 0.90 10% /oracle/IBP/mirrlogA

/dev/mirrlogBlv 1.00 0.10 0.90 10% /oracle/IBP/mirrlogB

/dev/sapdata1lv 89.00 7.82 81.18 9% /oracle/IBP/sapdata1

/dev/sapdata2lv 89.00 24.39 64.61 28% /oracle/IBP/sapdata2

/dev/sapdata3lv 89.00 9.35 79.65 11% /oracle/IBP/sapdata3

/dev/sapdata4lv 89.00 4.92 84.08 6% /oracle/IBP/sapdata4

/dev/oraarchlv 44.00 16.64 27.36 38% /oracle/IBP/oraarch

so should i increase the tablespace PSAPSR3.

Try to check the history of changing this tbs. and check what time you have enough the remaining 15Gb.
If need you can add more data files using BRTOOLS (BRSPACE) -->

i couldn't understand this. what should i do.

Regards,

Priya

Also the tablespace PSAPSR3700 is 100% full. Edited by: Priya1234 on Jun 24, 2010 9:42 AM

[Former Member · Accepted solution]

i couldn't understand this. what should i do.
Also the tablespace PSAPSR3700 is 100% full

Yes i see PSAPSR3700 is 100%, but you have autoextend up to 40GB for this tbs, actually only 9,5 GB is used and the remaining 30 GB enough for you for a very long time.

About PSAPSR3, go to DB02 --> tablespaces --> detailed analysis --> Tablspace name = PSAPSR3 , press enter.

This will open window with selected tablsps --> doublclick on this. You will see "Main data" --> locate to "History tab" and check how quickly your

tbs is growing (per day, week . or month). After you can define at what time you have enough your remaining 15 GB for this tbs.

Regards.

[Former Member · Accepted solution]

i cant see any data in the history or any other tab.

Regards,

Priya

[Former Member · Accepted solution]

This will open window with selected tablsps --> doublclick on this

Are you do it ?

[Former Member · Accepted solution]

ok i got it..

following is the details: for daily analysis:

Date Tablespace name Size(MB) Chg.size(MB) Free(MB) Chg. #extents/day Increment by Chg. used (%) Autoextend User blocks Chg.tot.size (MB) Total free(MB) Chg. total free (MB) Total used(%) Chg. tot. used(%) #Files Chg.#Files #Segments Chg. # segments #Extents Chg. # extents

6/24/2010 PSAPSR3 24660.00 460.00 13.06 -0.88 100 0 YES 40000.00 0.00 15353.06 -460.88 62 2 4 0 50421 0 74674 102

6/23/2010 PSAPSR3 24200.00 320.00 13.94 -4.62 100 0 YES 40000.00 0.00 15813.94 -324.62 60 0 4 0 50421 0 74572 114

6/22/2010 PSAPSR3 23880.00 400.00 18.56 -2.00 100 0 YES 40000.00 0.00 16138.56 -402.00 60 1 4 0 50421 0 74458 62

6/21/2010 PSAPSR3 23480.00 360.00 20.56 -4.07 100 0 YES 40000.00 0.00 16540.56 -364.07 59 1 4 0 50421 0 74396 138

6/20/2010 PSAPSR3 23120.00 300.00 24.63 11.57 100 0 YES 40000.00 0.00 16904.63 -288.43 58 1 4 0 50421 0 74258 96

6/19/2010 PSAPSR3 22820.00 60.00 13.06 -11.63 100 0 YES 40000.00 0.00 17193.06 -71.63 57 0 4 0 50421 3 74162 112

6/18/2010 PSAPSR3 22760.00 80.00 24.69 8.63 100 0 YES 40000.00 0.00 17264.69 -71.37 57 0 4 0 50418 0 74050 66

6/17/2010 PSAPSR3 22680.00 60.00 16.06 4.68 100 0 YES 40000.00 0.00 17336.06 -55.32 57 0 4 0 50418 0 73984 67

6/16/2010 PSAPSR3 22620.00 60.00 11.38 -9.43 100 0 YES 40000.00 0.00 17391.38 -69.43 57 1 4 0 50418 0 73917 72

6/15/2010 PSAPSR3 22560.00 60.00 20.81 0.68 100 0 YES 40000.00 0.00 17460.81 -59.32 56 0 4 0 50418 0 73845 63

6/14/2010 PSAPSR3 22500.00 60.00 20.13 -2.43 100 0 YES 40000.00 0.00 17520.13 -62.43 56 0 4 0 50418 0 73782 65

6/13/2010 PSAPSR3 22440.00 80.00 22.56 10.56 100 0 YES 40000.00 0.00 17582.56 -69.44 56 0 4 0 50418 2 73717 100

6/12/2010 PSAPSR3 22360.00 280.00 12.00 0.06 100 0 YES 40000.00 0.00 17652.00 -279.94 56 1 4 0 50416 0 73617 92

6/11/2010 PSAPSR3 22080.00 340.00 11.94 -5.81 100 0 YES 40000.00 0.00 17931.94 -345.81 55 1 4 0 50416 3 73525 180

6/10/2010 PSAPSR3 21740.00 380.00 17.75 -10.06 100 0 YES 40000.00 0.00 18277.75 -390.06 54 1 4 0 50413 0 73345 297

6/9/2010 PSAPSR3 21360.00 380.00 27.81 3.12 100 0 YES 40000.00 0.00 18667.81 -376.88 53 1 4 0 50413 0 73048 150

6/8/2010 PSAPSR3 20980.00 380.00 24.69 2.25 100 0 YES 40000.00 0.00 19044.69 -377.75 52 1 4 0 50413 0 72898 148

6/7/2010 PSAPSR3 20600.00 400.00 22.44 16.56 100 0 YES 40000.00 0.00 19422.44 -383.44 51 1 4 0 50413 0 72750 180

6/6/2010 PSAPSR3 20200.00 340.00 5.88 -12.50 100 0 YES 40000.00 0.00 19805.88 -352.50 50 0 4 0 50413 0 72570 194

6/5/2010 PSAPSR3 19860.00 360.00 18.38 1.57 100 0 YES 40000.00 0.00 20158.38 -358.43 50 1 4 0 50413 0 72376 252

6/4/2010 PSAPSR3 19500.00 380.00 16.81 -0.88 100 0 YES 40000.00 0.00 20516.81 -380.88 49 1 4 0 50413 0 72124 322

6/3/2010 PSAPSR3 19120.00 180.00 17.69 6.00 100 0 YES 40000.00 0.00 20897.69 -174.00 48 1 4 0 50413 0 71802 251

6/2/2010 PSAPSR3 18940.00 8780.00 11.69 8.69 100 0 YES 40000.00 0.00 21071.69 -8771.31 47 22 4 0 50413 6294 71551 14269

For weekly:

Date Tablespace name Size(MB) Chg.size(MB) Free(MB) Chg.free(MB) Increment by Chg. used(%) Autoextend User blocks Chg.tot.size (MB) Total free(MB) Chg. total free(MB) Total used(%) Chg.tot.used(%) #Files Chg.#Files #Segments Chg. # segments #Extents Chg. # extents

6/21/2010 PSAPSR3 23480.00 980.00 20.56 0.43 100 0 YES 40000.00 0.00 16540.56 -979.57 59 3 4 0 50421 3 74396 614

6/14/2010 PSAPSR3 22500.00 1900.00 20.13 -2.31 100 0 YES 40000.00 0.00 17520.13 -1902.31 56 5 4 0 50418 5 73782 1032

6/7/2010 PSAPSR3 20600.00 1660.00 22.44 10.75 100 0 YES 40000.00 0.00 19422.44 -1649.25 51 4 4 0 50413 0 72750 1199

6/2/2010 PSAPSR3 18940.00 8780.00 11.69 8.69 100 0 YES 40000.00 0.00 21071.69 -8771.31 47 22 4 0 50413 6294 71551 14269

3/17/2010 PSAPSR3 10160.00 0.00 3.00 0.00 100 0 YES 40000.00 0.00 29843.00 0.00 25 0 4 0 44119 0 57282 0

For monthly only two eneteries:

Date Tablespace name Size(MB) Chg.size(MB) Free(MB) Chg. #extents/day Increment by Chg. used (%) Autoextend User blocks Chg.tot.size (MB) Total free(MB) Chg. total free (MB) Total used(%) Chg.used(%) #Files Chg.#Files #Segments Chg. # segments #Extents Chg. # extents

6/2/2010 PSAPSR3 18940.00 8780.00 11.69 8.69 100 0 YES 40000.00 0.00 21071.69 -8771.31 47 22 4 0 50413 6294 71551 14269

3/17/2010 PSAPSR3 10160.00 0.00 3.00 0.00 100 0 YES 40000.00 0.00 29843.00 0.00 25 0 4 0 44119 0 57282 0

Regards,

Priya

[Former Member · Accepted solution]

also how can i check wat will be the maximum tablespace size upto which i can increase is size?

[Former Member · Accepted solution]

I'm starting to tire) ...

DB02 --tablspaces --> overwiev --> shows for your PSAPSR3 (TOTAL SIZE) = 40 GB i'm right ? Allocated are 24460 -->

Sum of all fre fragments --> 15353 this size are currently free with the approach.

Per your history you will use your PSAPSR3 last month rather actively, your DB grow from

06.02.2010 = 18940

06.21.2010 = 23480

Somewhere about 7Gb per month ...

This means with the approach your Remaining 15GB enough for 2 months, but you can add more datafiles In order to not think about it. Regards.