Procedure to delete and create PSAPUNDO

[Former Member]

Hi!

After client copy we have PSAPUNDO tablespace with size of 44 GB.

The "undo_retention" Parameter was set to value "0".

Question:

What is the technical procedure to decrease the size of PSAPUNDO (to delete and to create this tablespace)?

Thank you!

[former_member524429]

Dear Holger,

Please refer Note 600141 - Oracle9i: Automatic UNDO Management to get more information.

Recommendation for the Undo retention time:

-> 12h as the start value

-> As an indicator, the longest running SAP ABAP report can be used, if this is known.

-> You should not select an undo_retention parameter that is too small, otherwise, 'ORA-01555' will appear more regularly than in manual Undo Management mode.

-> Ideally, you should select undo_retention at a size that is large enough for the Undo tablespace to be used correctly (approximately 80%). If the undo_retention is too small, space remains unused in the Undo tablespace and is consequently wasted. However, if the undo_retention you have selected is too large, this means you want to have an undo_retention that you would not be able to keep within the specified Undo tablespace size due to the given change rate in the system. If you had created the Undo tablespace with AUTOEXTEND ON, the data file would now increase.

-> However, if a Transaction requires space for undo data, but no more space is available in the undo tablespace (and the undo tablespace cannot expand automatically), undo data whose undo retention has not yet expired are also overwritten. Therefore, the program does not conform to the specified undo retention in this case. (Remark: Oracle9i attempts to conform to the undo retention that you specify, but cannot guarantee it. It is only possibly to guarantee conformity to the specified undo retention period as of Oracle Release 10g. Also refer to Metalink Note 269814.1.)

-> If there are indications in the alert log that suggest that the Undo retention could not be retained, then the Undo tablespace must be increased if several ORA-01555 errors have occurred simultaneously. However, if this has not occurred together with ORA-01555, this means that the undo_retention could not be adhered in certain, rare circumstances only (for example a heavy workload). Therefore, the size of the Undo tablespace would mostly be correct.

undo_retention can be changed dynamically (therefore, changes become effective immediately). To retain a particular URT ('Undo Retention Time'), the Undo tablespace must be at least large enough to retain the changes to active transactions. If the Undo tablespace is too small, Undo information is overwritten (Data consistency precedes read consistency). This will most likely result in the appearance of "snapshot too old" messages.

[Former Member]

Hi,

When I recreate PSAPUNDO, should I create it with retention guarantee?

eg:

CREATE UNDO TABLESPACE PSAPUNDO1 DATAFILE

'/u05/xxxxx/undotbs2.dbf' SIZE 8192M RETENTION GUARANTEE;

Assistance would be much appreciated. I am going to recreate the PSAPUNDO tablespace at the same time as changing oracle undo_management  parameter  to MANUAL. This is oracle 11g.

Thanks

[stefan_koehler]

Hi Willem,

> When I recreate PSAPUNDO, should I create it with retention guarantee?

Depends on what you want to achieve. Guaranteed retention for long running DMLs (like SELECTs) or guaranteed data manipulation DMLs (like DELETE, UPDATE, etc.).

One of both may fail if the data is overwritten or the space is used up. It depends on what you want.

Regards

Stefan

[Former Member]

Dear Stefan,

Thank you for the response.

I meant to say in my previous post that I'm changing the undo_management parameter from MANUAL to AUTOMATIC. I found quite a few of our 11.2.0.3 databases that are still using MANUAL undo management.  I will be following the guide in Note 600141 to do this.

I was referring to the SAP recommended default for the UNDO tablespace, whether to create it with retention guarantee or not? 

I found oracle notes about "retention guarantee" that mention: "This option is disabled by default. Enabling retention guarantee can cause multiple DML operations to fail. Use with caution".   With this in mind it seems I should rather not add it by default?

I also notice a few databases contain all the PSAPROLL, PSAPROLLBIG and PSAPUNDO tablespaces.  In these databases only the segmens for PSAPROLL are ONLINE, and for the rest are offline.  Should I follow roughly the same SAP note to move to PSAPUNDO tablespace and remove PSAPROLL and PSAPROLLBIG tablespaces?  I noticed in the guide that PSAPROLL are the old format of PSAPUNDO.

Response will be appreciated.

Thanks and Regards,

Willem

[stefan_koehler]

Hi Willem,

> With this in mind it seems I should rather not add it by default?

Once again - it depends on what you want to achieve. If you do not enable the guaranteed retention, it is possible that long running queries will fail with "ORA-01555 snapshot too old", even if the retention period is not reached (because of the undo data is overwritten by DMLs). The retention is guaranteed, if you enable

guaranteed undo retention and so the queries will not fail with an ORA-01555 within this period, but maybe DMLs will fail, if the space is used up. So you have to choose between the two impacts.

> Should I follow roughly the same SAP note to move to PSAPUNDO tablespace and remove PSAPROLL and PSAPROLLBIG tablespaces?

There is no need for that (rollback segment) tablespaces anymore, if you switch to "undo_management = AUTOMATIC"

Regards

Stefan

[Former Member]

Hi Stefan,

Thank you for advise.

Kind Regards,

Willem

[former_member524429]

Dear Holger,

PSAPUNDO is used to ensure data consistency and read consistency in an Oracle database system.

One can decrease the size of exisitng Tablespace by resizing its Data files using BRTOOLS.

Here is the Procedure to resize the Datafile of a Tablespace:

1.Analyze what datafiles should be resized according to the table extension (please check space needed and available via DB13, DB02, ST06)

Decreasing the size of datafile of PSAPUNDO tablespace may lead to data/read inconsistency. Perform the following activity in offline database mode.

2.Go to server login with <sid>adm

3.Run brtools

4.Go to space management (option number 2)

5.Go to alter datafile (option number 5)

6.Check the option, if itu2019s ok wih the default setting type c then enter to continue to next brspace process

7.Go to resize data file (option number 5)

8.All data files will be appeared enter to scroll down choose the datafile which will be resized

9.On the option 7 (New data file size in MB (size) ......... []) please enter the datafile new size in MB based on your requirement.

10.Continue

11.Donu2019t forget to choose exit after finish

Now,,,,,

Question:
What is the technical procedure to decrease the size of PSAPUNDO (to delete and to create this tablespace)?

Please refer this helpful Metalink [431652.1 --- How to Change the Existing Undo Tablespace to a New Undo Tablespace|https://metalink.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocDsrc=KB&bmDocTitle=How%20to%20Change%20the%20Existing%20Undo%20Tablespace%20to%20a%20New%20Undo%20Tablespace&viewingMode=1143&bmDocID=431652.1&from=BOOKMARK&bmDocType=HOWTO))]

You can access Oracle Metalink by following this SAP [Note 758563 - Oracle Metalink access for SAP customers|https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=758563]

For information regarding undo_retention, please follow this [SAP Note 1035137 - Oracle Database 10g: Automatic Undo Retention|https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1035137]

Regards,

Bhavik G. Shroff

[Former Member]

Hi!

Many thanks for your response.

The problem seems to be that I have a tablespace PSAPUNDO that have 2 datafiles (UNDO.DATA1: 20 GB and UNDO.DATA2: 24 GB).

So i think it is not possible to decrease the size of these datafiles, because they have reached the maximum size.

The quesiton:

- How can I delete and create PSAPUNDO tablespace without lose any data in PSAPUNDO?

When I execute

>DROP TABLESPACE PSAPUNDO INCLUDING CONTENTS AND DATAFILES ;

then the current data un PSAPUNDO will be lost?

[Former Member]

If you delete the data files then of course you will also delete its content. But if you wish to hang on that data within the tablespace - why delete it in the first place or attempt to decrease the size? It seems as if you need it with its current size.

[former_member524429]

Dear Holger,

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails.

The quesiton:

How can I delete and create PSAPUNDO tablespace without lose any data in PSAPUNDO?

Do the following Online tasks:

1. Create a new PSAPUNDO_2 Tablespace with the new data files of your required size. The size of the PSAPUNDO type tablespace should be determined by kiping in my the long running SAP transactions in your system and the UNDO retention period.

2. Switching to new Tablespace PSAPUNDO_2 by changing UNDO_TABLESPACE initialization parameter.

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = PSAPUNDO_2;

Assuming PSAPUNDO_1 is the current undo tablespace, after this command successfully executes, the instance uses PSAPUNDO_2 in place of PSAPUNDO_1 as its undo tablespace.

When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace (PSAPUNDO_2).

The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.

An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode.

3. Drop that Old PSAPUNDO_1 Tablespace, when it becomes offline automatically.

Regards

Bhavik G. Shroff

[Former Member]

Hi!

Thank you very much!

One last question:

can I execute all the steps in ONLINE-mode of my Oracle Database or should I shutdown the database first?

Do all the open transactions move with this commando from one tablespace to another or do I need to waint with my undo_retention time?

> ALTER SYSTEM SET UNDO_TABLESPACE = PSAPUNDO_2;

[stefan_koehler]

Hello,

sometimes i wonder why asking all these questions that were answered in my first reply.

I think i should also start copy & paste all the documentation and become a hero )

Regards

Stefan

[former_member524429]

Dear Holger,

can I execute all the steps in ONLINE-mode of my Oracle Database or should I shutdown the database first?

Yes you can do it online, as you will create different undo tablespace parallel with your existing undo tablespace.

Do all the open transactions move with this commando from one tablespace to another or do I need to waint with my undo_retention time?
ALTER SYSTEM SET UNDO_TABLESPACE = PSAPUNDO_2;

All The transactions which are started after the execution of the command ALTER SYSTEM SET UNDO_TABLESPACE = PSAPUNDO_2 will refer to PSAPUNDO_2 instead of old undo tablespace. The old Transactions/unfinished transactions will refer to that Old Undo Tablespace.

Regards,

Bhavik G. Shroff

[Former Member]

Have you tried to use brtools -> Space management -> Alter data file -> Turn on and maintain autoextend and then downsizing the PSAPUNDO datafiles as far as possible or needed?

Kind regards.

[stefan_koehler]

Hello Holger,

please check my reply in this thread:

Regards

Stefan