PSAPUNDO tablespace interfereing with SAP database backup

[Former Member]

My UNDO tablespace has grown (currently 9GB) and as a result I'm getting an error when attempting backup. Resulting error is shown below

BR0204I Percentage done: 94.11%, estimated end time: 4:20

BR0001I ***********************************************___

BR0202I Saving /oracle/DEV/sapdata3/undo_1/undo.data1

BR0203I to /dev/rmt/0mn ...

BR0278E Command output of 'LANG=C cd /oracle/DEV/sapdata3/undo_1 && echo undo.data1 /usr/sap/DEV/SYS/exe/run/brtools -f detach LANG=C cpio -ovB -Hodc > /dev/rmt/0mn':

cpio: cpio: undo.data1: too large to archive in current mode

BR0240E Size 5120 of saved/restored data is different from the size 9227476992 of file/save set /oracle/DEV/sapdata3/undo_1/undo.data1

10 blocks

1 error(s)

BR0280I BRBACKUP time stamp: 2009-11-17 03.54.28

BR0279E Return code from 'LANG=C cd /oracle/DEV/sapdata3/undo_1 && echo undo.data1 /usr/sap/DEV/SYS/exe/run/brtools -f detach LANG=C cpio -ovB -Hodc > /dev/rmt/0mn': 1

BR0222E Copying /oracle/DEV/sapdata3/undo_1/undo.data1 to/from /dev/rmt/0mn failed due to previous errors

BR0280I BRBACKUP time stamp: 2009-11-17 03.54.31

BR0304I Starting and opening database instance DEV ...

BR0280I BRBACKUP time stamp: 2009-11-17 03.54.50

BR0305I Start and open of database instance DEV successful

BR0056I End of database backup: bebxtins.aft 2009-11-17 03.54.28

BR0280I BRBACKUP time stamp: 2009-11-17 03.54.52

BR0054I BRBACKUP terminated with errors

I've tried resizing the datafile in order to reduce the size but Oracle error 03297

03297, 00000, "file contains used data beyond requested RESIZE value"

*Cause: Some portion of the file in the region to be trimmed is

currently in use by a database object

*Action: Drop or move segments containing extents in this region prior to

resizing the file, or choose a resize value such that only free

space is in the trimmed.

kindly advice on the way forward

[Former Member · Accepted solution]

Hello,

The likely cause is that cpio cannot handle output files greater than 2 Gigabytes. To get round this you have to use dd instead of cpio. In the initDEV.sap profile, set following parameter:

tape_copy_cmd = dd

This will address the backup issue but of course it isn't going to make your PSAPUNDO smaller. If you want to reduce the PSAPUNDO, then let us know and I'll provide you with the procedure.

Regards,

Mark

[Former Member · Accepted solution]

Thanks alot Mark

I've made the change & I will see how the backup goes this evening

Space is a constraint in my server & I would wish to reduce the size of the datafile

Kindly provide me with the procedure

Regards

[Former Member · Accepted solution]

Hi Andale,

OK, here goes. For this procedure you will have to take the SAP system down because PSAPUNDO is dropped and recreated, which is not possible if there are active transactions.

Log in to the database server as user oradev

sqlplus / as sysdba

-- Stop/restart to make sure there is no more activity
shutdown immediate 

-- Create a temporary undo tablespace
create undo tablespace PSAPUNDO2  datafile '/oracle/DEV/sapdata3/undo2.data1' size 1G;

-- Make this the default undo tablespace
alter system set undo_tablespace=PSAPUNDO2 scope=memory;

-- Drop PSAPUNDO
drop tablespace PSAPUNDO including contents and datafiles;

-- Recreate PSAPUNDO, e.g. with a single datafile of 2 GB
create undo tablespace PSAPUNDO datafile '/oracle/DEV/sapdata3/undo_1/undo.data1' size 2G;

-- Make PSAPUNDO again the default undo tablespace
alter system set undo_tablespace=PSAPUNDO scope=both;

-- Get rid of the temporary undo tablespace
drop tablespace PSAPUNDO2 including contents and datafiles;

-- Just another stop/start to end cleanly
shutdown
startup

Good luck,

Mark

[Former Member · Accepted solution]

Thanks a lot

This is very useful

regards