Need help regarding PSAPUNDO and PSAPTEMP in context of client copy

[Former Member]

Hi,

I am trying to take a remote copy ( prof = sap_all ) from production system to quality.

The whole database size is nearly 80 GB ( we have one integration testing client on production system, means it also contribues to this 80 GB ).

I had created a tablespace PSAPROLLBIG of 2GB for client copy purpose. But it gave an error ORA 1562 - failed to extend rollback seg PRS_BIG

So I increased the size to 4 GB and got the same error again.

Next I implemented oracle AUM with 5GB space allocated to PSAPUNDO. and got error ORA-30036: unable to extend segment in undo tablespace.

Next I tried to change the undo_retention parameter from 12h ( SAP suggested ) to 2h and increased PSAPUNDO by 5GB more.

Now this time I am getting error for PSAPTEMP.

Current size of PSAPTEMP is 358 MB

On this background I want to get some clarification on following.

1. what will be the difference in use of PSAPUNDO and PSAPTEMP while copy is running. ( i.e. what will be entered in PSAPUNDO and what will be filled in PSAPTEMP.)

2. the target client already has a copy taken 1 month before. so I think while importing it first delete existing data and then copies the new one.

So If I first delete the target client and then take import on it; will it have advantage in regards of getiing UNDO or TEMP segments getting filled ?

Please gie your suggestions. and any more suggestion to help me getting the copy done successfully. Should I have to take into account anything else before starting import.

( Please let me know if I am not clear to describe the problem ).

Thanks.

---Shamish.

[Former Member · Accepted solution]

Hi Shamish

Actually you were on the right track, i think you just have to increase PSAPTEMP a bit and you will be fine. 358 MB seems just too small, i suggest you increase it to at least 2GB.

1. what will be the difference in use of PSAPUNDO and PSAPTEMP while copy is running. ( i.e. what will be entered in PSAPUNDO and what will be filled in PSAPTEMP.)

PSAPTEMP: is needed for large sort operations, as mentioned when you have a select with an ORDER BY clause, or if you do join two tables. During the client copy some sorting might be needed for special tables (cluster tables) where data is stored sorted.

PSAPUNDO: undo space is only needed for DML (data manipulation), if data is changed undo is generated. This obviously is heavily the case during a client copy.

2. the target client already has a copy taken 1 month before. so I think while importing it first delete existing data and then copies the new one.

So If I first delete the target client and then take import on it; will it have advantage in regards of getiing UNDO or TEMP segments getting filled ?

Deleting the client first might help for the undo problem, but you already solved that. I cannot imagine, it will help for the PSAPTEMP issue. As i said i would just increase PSAPTEMP and restart the copy.

One more add: if you are doing the client copy with parallel processes, this will influence your requirements on temp and undo space, because of the concurrently running processes.

Best regards

Michael

[Former Member · Accepted solution]

Hi Michael,

Thanks. The problem got solved. I was going for the option for increasing PSAPTEMP only. But wanted to clear my concepts before doing the same blindly.

Thanks for your explanation. ( and sorry for late reply )

One more thing I want to know is....

Will it make any adverse effect if I make the undo_retention parameter as 1 hour or less.

---Shamish

[Former Member · Accepted solution]

Will it make any adverse effect if I make the undo_retention parameter as 1 hour or less.

No, it won't help you prevent an ORA-1562. This simply means you used up all your undo space (and didn't commit to free a segment). undo_retention might prevent a ORA-1555 SNAPSHOT TOO OLD, but if you are using all your undo space, blocks will be overwritten even if undo_retention cannot be hold.

Here is the official docu: [undo_retention|http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams221.htm#sthref898]

It is now possible to guarantee the retention on an undo tablespace, but this is not commonly used in SAP systems:

SQL> select RETENTION from dba_tablespaces where tablespace_name = 'PSAPUNDO';

RETENTION

___________

NOGUARANTEE

Regards, Michael

[Former Member]

Hi Sharmish,

Check in ST04, under Sorts--> Disk, whether the value is high during the client copy, because this gives number of sorts stored in the temporary segments, which is nothing but located in PSAPTEMP. Also check in DB02 for PSAPTEMP usage.

Sorting operation occurs when SQL statements uses ORDER BY operations during their execution.

With regards

Sudha

[Former Member]

Hi,

Check the required space using Test run, also check that required space is available or not from OS level.

Switch on the auto-extent option for all the data files of both the tablespaces using brtools.

Hope this help.

Regards,

Rajesh