cancel
Showing results for 
Search instead for 
Did you mean: 

ROLLBACK Segment Reconfiguration does not give some Effects for PSAPROLL

former_member524429
Active Contributor
0 Kudos

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

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Kudos

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';

former_member524429
Active Contributor
0 Kudos

Hi,

SQL> select name, extends, wraps, extents, shrinks from v$rollstat r, v$rollname
 n where r.usn=n.usn and name='PRS_11';

NAME                              EXTENDS      WRAPS    EXTENTS    SHRINKS
------------------------------ ---------- ---------- ---------- ----------
PRS_11                                  0         40        100          0

SQL>

Regards,

Bhavik G. Shroff

Former Member
0 Kudos

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

former_member524429
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

former_member524429
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

former_member524429
Active Contributor
0 Kudos

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

Former Member
0 Kudos

PSAPROLL????? This becomes obsolete. Better upgrade your DB to 10g, or atleast upgrade to 9i.

Rgds,

SK

OCP 9i,10g.

former_member524429
Active Contributor
0 Kudos

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

former_member204746
Active Contributor
0 Kudos

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.