Reply from bmacmillan on Jun 6 at 10:08 AM Hello, I'm still struggling with the PSAPUNDO tablespace.
Here's the pertinent data:
Our SAP environment is EHP7 for SAP ERP 6.0.
Our Oracle version is 11.2.0.3.0.
Our DB size is 2.7 GB. Free space 1 GB.
Our PSAPUNDO tablespace now has 20 datafiles.
The size of PSAPUNDO is 645 GB.
The 1st 4 datafiles were created by our consultant when we went live with ECC 6.0, in April, 2014. The total size, at that point, was 116 GB, and it remained that size until November 2015. From this point on, I have been adding datafiles to keep it from running out of space.
It should be noted that these 4 datafiles were created using the autoextend ON option.
When I started adding datafiles, I was not aware of this requirement, and did not specify. All of the datafiles, however show as Autoextend YES in DB02, as does the tablespace.
From what I have read, the PSAPUNDO tablespace should be relatively maintenance free, and you should not have to add datafiles to it, because the data gets over-written, based on the parameters:
Our undo_retention is set to 3600.
The Iselect max(maxquerylen) from v$undostat query returns a value of 3448, so I think I have this set correctly.
I have also read that the PSAPUNDO tablespace is, typically, between 5-10% of the total size of the database? Ours is way larger than that, obviously.
Please let mw know hat other information I can provide that will help. I'm sure I must be doing something wrong!
Thanks,
Bruce
| | | ---------------Original Message---------------
From: bmacmillan
Sent: Thursday, March 03, 2016 9:27 AM
Subject: Proper Usage/Configuration Of PSAPUNDO
Good morning,
I hope someone can help me with some advice.
A while back, we started having issues in SAP, and traced it back to the PSAPUNDO tablespace being full.
Since then, we have been monitoring it, and adding datafiles as needed,
I now have a bit more time to look into this properly, and I discovered that when the PSAPUNDO tablespace was initially created (when we went live with ECC 6.0), the consultant created the datafiles using the "autoextend" option.
See below:
CREATE UNDO TABLESPACE PSAPUNDO DATAFILE '/oracle/PRD/sapdata1/undo_1/undo.data1' SIZE 20000M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 32700M
Sat Apr 12 17:21:40 2014
[21306] Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE PSAPUNDO DATAFILE '/oracle/PRD/sapdata1/undo_1/undo.data1' SIZE 20000M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 32700M
ALTER TABLESPACE PSAPUNDO ADD DATAFILE
'/oracle/PRD/sapdata2/undo_2/undo.data2' SIZE 32000M AUTOEXTEND ON NEXT 20M MAXSIZE 32700M,
'/oracle/PRD/sapdata3/undo_3/undo.data3' SIZE 32000M AUTOEXTEND ON NEXT 20M MAXSIZE 32700M,
'/oracle/PRD/sapdata4/undo_4/undo.data4' SIZE 32000M AUTOEXTEND ON NEXT 20M MAXSIZE 32700M
Now, when we first experienced the issues, we added new datafiles without using the "autoextend" option (we didn't know any better), but when I added the last 4, I started using the option again:
My question is - what, exactly, does the autoextend option do for us?
I was under the impression that this would ensure that datafiles would get added automatically, as required. But, if that is the case, why did we all of a sudden start to need to add datafiles, given the manner in which our consultant created the initial datafiles within PSAPUNDO?
Note that we have never needed to add datafiles, manually, to any of the other tablespaces - they have been added automatically, as we'd expect.
Please explain, if you can. Or, let me know if you need more information.
Thanks,
Bruce
. |
| Reply to this email to post your response. __.____._ |    _.____.__ |
