Coskan’s Approach to Oracle

January 29, 2007

Changing UNDO_RETENTION on RAC (10.1.0.X)

Filed under: RAC — coskan @ 12:13 pm

4 months ago while we were archiving our database (2 node RAC) we got continuous Ora-1555 error for archiving queries.

I decided to update undo_retention parameter with command

alter system set undo_retention=36000 scope=both;

than DB hanged. One node was accepting “sqlplus / as sysdba” while

other node (the node i alter the DB ) wasn’t.

srvctl stop database -d DB_NAME

srvctl stop instance -d DB_NAME -i INSTANCE_NAME

commands did not work for the unresponding node.

I took the system state dumps from the working node and killed the other nodes processes from OS.

After CRS restart everthing worked fine.

I did not understand why till the respond from Metalink Tar.

This was related with the bug https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=4220405

It says

ALTER SYSTEM SET UNDO_RETENTION=<N> HANGS RAC INSTANCES which has been closed as base bug 3023661)
Bug 4220405 ALTER SYSTEM SET UNDO_RETENTION=<N> HANGS RAC INSTANCES which has been closed as base bug 3023661)
The problem is caused by deadlock between CKPT and PZ99 slave. The internal algorithm to query the
current value of undo_retention of each instance and modify it has problem. It does unnecessary gv$ query and
lob$ update when spfile is used. It is coding problem. The bug has been fixed in 10.2 and it is not backportable to 10.1.0.x due to code structure change.

The Usage of UNDO_RETENTION is below

alter system set undo_retention=1800 sid=’RAC1′;
alter system set undo_retention=1800 sid=’RAC2′;

Be carefull while changing undo_retention on RAC

 
About these ads

4 Comments »

  1. I run into same bug under 9.2.0.5.0.

    The workaround and correct syntax mentioned by Coskan helped to overcome the situation.

    Many thanks for sharing you knowledge and experiences

    Cheers
    Alf

    Comment by Alf — July 16, 2007 @ 5:15 am

  2. Thanks it is a good article and this is realy help . . .

    Comment by Endera — November 23, 2008 @ 5:06 pm

  3. Tomorrow as weekend activity i need to change the undo for some specific job run , good article , will consider your note.
    Hope this bug is removed on 11g.?

    Comment by kns — January 27, 2012 @ 4:30 pm

  4. To apply on all nodes use the below command.
    alter system set undo_retention=2000 sid=’*';

    Thanks.

    Comment by Arundas — June 5, 2012 @ 8:54 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 205 other followers

%d bloggers like this: