Reply from bmacmillan on Mar 4 at 3:49 PM By the way, I found this.
http://www.akadia.com/services/ora_optimize_undo.h tml
Specifically, this section:
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Again, all in one query:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
When I issue the command, I get
The formatting is a bit off, but I know that the ACTUAL_UNDO_SIZE is, indeed, 393,980 MB and the UNDO_RETENTION is 900, so that means the NEEDED_UNDO_SIZE is 475.828125
Do you think 475.828125 means 475 GB - this is what I'm thinking.
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. __.____._ | In the Spotlight Become a blogger at Toolbox.com and share your expertise with the community. Start today.    _.____.__ |
