on 02-13-2009 3:37 PM
Hello Friends,
I am using SAP R3 4.6C R2 on Oracle 9.2.0.6.0 Database.
I want to Reconfigure All Rollback Segments of PSAPROLL Tablespace.
The Existing Setting of PSAPROLL Tablespace is as followed:
-
Tablespace Size (kb) Free (kb) Used (%) Tab/ind Extents AutoExt (kb) Used (%) Status Backup
PSAPROLL 3,737,592 3,438,592 7 20 290 Off 7 ONLINE NOT ACTIVE
Tablespace Management Allocation Init ext.(Kb) Next ext.(Kb) Min ext. Max ext. Pct-Inc. Bl.Size (Kb)
PSAPROLL LOCAL UNIFORM 1,024 1,024 1 Unlimited 0 8
And the Existing Setting after changing one RollBack Segment is as followed:
-
Data from DBA_SEGMENTS
(unlimited = 1-)
Owner Object Type Tablespace KBytes Blocks Extents MaxExtents Next (K)
SYS PRS_11 ROLLBACK PSAPROLL 102,400 12,800 100 32,765 1,024
SYS PRS_0 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_1 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_2 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_3 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_4 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_5 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_6 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_7 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_8 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_9 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_10 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_12 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_13 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_14 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_15 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_16 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_17 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_18 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
SYS PRS_19 ROLLBACK PSAPROLL 10,240 1,280 10 32,765 1,024
Total 296,960 37,120 290
-
I have reconfigured PRS_11 Rollback Segment as followed as per as Example 1 mentioned in SNOTE 3807: Error Messages Regarding RollBack and Undo Segments
ALTER ROLLBACK SEGMENT PRS_11 OFFLINE;
DROP ROLLBACK SEGMENT PRS_11;
CREATE ROLLBACK SEGMENT PRS_11 TABLESPACE PSAPROLL
STORAGE (INITIAL 10M
NEXT 10M
MINEXTENTS 10
MAXEXTENTS 70
OPTIMAL 100M);
ALTER ROLLBACK SEGMENT PRS_11 ONLINE;
But, after recreation of that PRS_11 Rollback Segment, the Next Extent parameter setting is not changed as expectation.
Owner Object Type Tablespace KBytes Blocks Extents MaxExtents Next (K)
SYS PRS_11 ROLLBACK PSAPROLL 102,400 12,800 100 32,765 *1,024*
What is the reason for this ?
Is there any setting for Table space , so that we can apply the global changes in Next Extent of new Rollback Segments.
The output form dba_rollback_segs is telling the different thing, even after recreation of PRS_11 Rollback Segment.
SQL> select SEGMENT_NAME, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTE
NTS, MAX_EXTENTS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT
------------------------------ ------------------------------ --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
----------- ----------- -----------
SYSTEM SYSTEM 57344
57344 2 505
PRS_11 PSAPROLL 104857600
1048576 1 32765
I am not understanding this situation.
How to resolve this issue ?
I am waiting for your valuable suggestions.
Thanks & Regards,
Bhavik G. Shroff
Everything looks Ok except the parameter extents.What's the out put below
SELECT name,
extends,
wraps,
extents,
shrinks
FROM v$rollstat r,
v$rollname n
WHERE r.usn = n.usn
and r.name = 'PRS_11';
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SELECT name,
extends,
wraps,
shrinks
FROM v$rollstat r,
v$rollname n
WHERE r.usn = n.usn;
run this query and let me know the results
Rgds,
Sk
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
SQL> SELECT name,
ex 2 tends,
w 3 raps,
4 shrinks
F 5 ROM v$rollstat r,
v 6 $rollname n
7 WHERE r.usn = n.usn;
NAME EXTENDS WRAPS SHRINKS
------------------------------ ---------- ---------- ----------
SYSTEM 0 0 0
PRS_11 0 40 0
PRS_0 0 32 0
PRS_1 0 36 0
PRS_2 0 43 0
PRS_3 0 37 0
PRS_4 0 34 0
PRS_5 0 39 0
PRS_6 0 27 0
PRS_7 0 32 0
PRS_8 0 41 0
NAME EXTENDS WRAPS SHRINKS
------------------------------ ---------- ---------- ----------
PRS_9 0 41 0
PRS_10 0 37 0
PRS_12 0 25 0
PRS_13 0 35 0
PRS_14 0 35 0
PRS_15 0 27 0
PRS_16 0 47 0
PRS_17 0 34 0
PRS_18 0 31 0
PRS_19 0 34 0
21 rows selected.
SQL>
Regards
Bhavik G. Shroff
From which table you are trying to view the new data? R u using dba_rollback_segs? It is a static view....it will be updated in the next reboot or the next time when you do the analysis....
Use V$rollstat to see the current view.
Rgds,
SK
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
V$ROLLSTAT View
14.02.2009 Statistics for all online rollback segments - Info 1 13:17:43
Name USN Extents Size (Kb) Written(Kb) Act.trans. Header gets Header waits Optimal size (Kb)
SYSTEM 0 18 1,432 160,546,875 0 2,740 0 0
PRS_0 2 10 10,232 291,473,164 0 54,646 0 10,240
PRS_1 3 10 10,232 311,212,363 0 56,936 0 10,240
PRS_2 4 10 10,232 377,180,879 0 65,764 0 10,240
PRS_3 5 10 10,232 314,701,934 0 54,307 0 10,240
PRS_4 6 10 10,232 28,841,666 0 56,648 0 10,240
PRS_5 7 10 10,232 330,707,637 0 64,872 0 10,240
PRS_6 8 10 10,232 230,982,305 0 50,787 0 10,240
PRS_7 9 10 10,232 28,532,416 0 56,413 0 10,240
PRS_8 10 10 10,232 345,465,273 0 63,842 0 10,240
PRS_9 11 10 10,232 35,021,373 0 52,684 0 10,240
PRS_10 12 10 10,232 320,204,844 0 60,442 0 10,240
PRS_11 1 100 102,392 338,170,918 0 62,152 0 102,400
PRS_12 14 10 10,232 224,758,066 0 50,505 0 10,240
PRS_13 15 10 10,232 289,125,684 0 59,621 0 10,240
PRS_14 16 10 10,232 292,405,742 0 63,152 0 10,240
PRS_15 17 10 10,232 234,707,715 0 50,583 0 10,240
PRS_16 18 10 10,232 398,010,254 0 59,198 0 10,240
PRS_17 19 10 10,232 290,330,625 0 64,216 0 10,240
PRS_18 20 10 10,232 275,655,957 0 55,035 0 10,240
PRS_19 21 10 10,232 308,297,539 0 58,964 0 10,240
See this Entry.
PRS_11 1 *100* 102,392 338,170,918 0 62,152 0 102,400
Regards,
Bhavik G. Shroff
What is the value of the parameter UNDO_MANAGEMENT?? You cannot tune the rollback segments if the database is in automatic mode because Oracle handles the operation of the rollback segments internally.
Change the parameter to MANUAL if you want to tune the Rollback Segment.
Rgds,
SK
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string
SQL>
SQL> select SEGMENT_NAME, TABLESPACE_NAME, STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
PRS_11 PSAPROLL ONLINE
PRS_0 PSAPROLL ONLINE
PRS_1 PSAPROLL ONLINE
PRS_2 PSAPROLL ONLINE
PRS_3 PSAPROLL ONLINE
PRS_4 PSAPROLL ONLINE
PRS_5 PSAPROLL ONLINE
PRS_6 PSAPROLL ONLINE
PRS_7 PSAPROLL ONLINE
PRS_8 PSAPROLL ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
PRS_9 PSAPROLL ONLINE
PRS_10 PSAPROLL ONLINE
PRS_12 PSAPROLL ONLINE
PRS_13 PSAPROLL ONLINE
PRS_14 PSAPROLL ONLINE
PRS_15 PSAPROLL ONLINE
PRS_16 PSAPROLL ONLINE
PRS_17 PSAPROLL ONLINE
PRS_18 PSAPROLL ONLINE
PRS_19 PSAPROLL ONLINE
21 rows selected.
SQL>
Reagards,
Bhavik G. Shroff
PSAPROLL????? This becomes obsolete. Better upgrade your DB to 10g, or atleast upgrade to 9i.
Rgds,
SK
OCP 9i,10g.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I am using SAP R3 4.6C SR2 on Oracle 9i Database and HP_UX 11i Operating System.
I want to adjust Classic Rollback Segments of PSAPROLL, if it is possible.
If the Expected Output is not possible even after this Adjustment, then I will convert PSAPROLL to PSAPUNDO, if it is required.
Any More Suggestions for this situation.
Regards,
Bhavik G. Shroff
First of all, PSAPROLL is ancient technology.
You should convert to PSAPUNDO which will make administration much easier and more efficient. ORA-01555 errors are almost history b moving to PSAPUNDO.
I would highly recommend that you read SAP note 600141 - Oracle9i: Automatic UNDO Management.
note that this also works very well with Oracle 10g.
Still using Oracle 8? then upgrade, Oracle 8 has been out of support for at least 5 years now.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.