Client copy problem with PSAPUNDO tablespace

[Former Member]

Hi,

I am doing remote client copy from production to Quality server.

But it always get stuck up at a certain table PPOIX and table space PSAPUNDO becomes full.

I have kept on increasing it as it gets stuck up while client copying. Its been going from last 2 months.

And it has become more than double the size as it used to be.

And still got full this time while client copying.

The DB parameter UNDO_RETENTION = 12 hours

What could be wrong?

What can I do to solve this problem?

Thanks,

Tajinder

[Former Member · Accepted solution]

Hi

Don't be too hasty in increasing size of PSAPUNDO, analyse the table size in DB02.

Also check SAP note 3155, wich says about retention guarantee.

Regards

Rahul

[Former Member · Accepted solution]

Hi Rahul,

Sorry for the delay..Went to other project. Couldnt reply.

i went through the notes. Instead of patch upgrading, I thought of reducing retention time to 4 hours.

I changed the paramter UNDO_RETENTION to 4 hours in the file init<SID>.ora. Before changing, i stopped the SAP services and listerner too.

But after changing n when i restarted the server, under DB26 the paramter UNDO_RETENTION is still showing 12 hours.

If i click on "profile file", it opens the file and in this file UNDO_RETENTION is 4 hours. But it is not reflecting under DB26 paramters.

When I try to change it from DB26 and when I save it, it gives me error as "Connection name SYSTEMCON not maintained for user SYSTEM in DB <SID>.

We can maintain DB conection, but I think they are for other external Databases.

Kindly help me in this. Am i doing something wrong?

Thanks,

Tajinder

[former_member185954 · Accepted solution]

Hello,

Does your Oracle have a spfile, if yes then you must change the parameter in spfile and recreate the pfile from it and stop/start the database/SAP.

Only then would your parameter reflect.

This should be true for all parameter, however i think UNDO_RETENTION parameter can be changed on the fly at runtime.

ALTER SYSTEM SET UNDO_RETENTION = <time in seconds>

The effect of the UNDO_RETENTION parameter is immediate, but it can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with the "snapshot too old" message.

Regards,

siddhesh

[Former Member · Accepted solution]

Hi,

How can I check about spfile of oracle?

Well, I changed it through "ALTER SYSTEM SET UNDO_RETENTION = <time in secs> command and It changes on the fly at runtime.

Is there any setting i can do so that i am able to change that from DB26 itself?

Thanks,

Tajinder

[former_member185954 · Accepted solution]

Hello,

In your /oracle/<SID>/920_64/dbs directory, if you have the file spfile<SID>.ora you are using a spfile.

to set the parameter in your SPFILE and make it permanent, you need to execute the command:

ALTER SYSTEM SET parameter = value SCOPE=[SPFILE/MEMORY/BOTH];

Go through this link for understanding this concept better.

http://www.oracle-base.com/articles/9i/PersistentInitializationParameters.php

Regards,

Siddhesh

[Former Member]

Hi,

Closing the thread.

Thanks for your support

[former_member185954]

Hello,

Target is your Quality server right ?

Then reduce the DB Retention to 4 hours.

Other option is if your production client is not very big, try the client export option.

Regards,

Siddhesh