Home » RDBMS Server » Server Administration » ORA-01555 (oracle,9208)
ORA-01555 [message #540707] Wed, 25 January 2012 07:26 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I am getting below ORA-01555 error in alert log everyday.

ORA-01555 caused by SQL statement below (SCN: 0x09ad.86a4562a):
Sat Jan 21 08:39:45 2012
SELECT (NVL(MAX(BLOCK_ID + BLOCKS ),0) * :b1 ) / 1024    
FROM DBA_EXTENTS  WHERE TABLESPACE_NAME = :b2  AND FILE_ID = :b3


I cannot able to find the sql_id here. So how can I find from which process or session this query is firing? before increasing the undo size, I need to analyze as why it is occuring? can you pls help me on this?

undo_management                      string      MANUAL
undo_retention                       integer     900
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string

[Updated on: Wed, 25 January 2012 07:53] by Moderator

Report message to a moderator

Re: ORA-01555 [message #540712 is a reply to message #540707] Wed, 25 January 2012 07:35 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
You are using manual rollback segments. To fix this issue, convert to using automatic undo management.
Re: ORA-01555 [message #540732 is a reply to message #540712] Wed, 25 January 2012 08:22 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
can you pls confirm changing the undo_management from MANUAL to AUTO is only enough to avoid this error in future?
Re: ORA-01555 [message #540733 is a reply to message #540732] Wed, 25 January 2012 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No one can confirm this.
It is the FIRST MANDATORY step.

Regards
Michel
Re: ORA-01555 [message #540734 is a reply to message #540733] Wed, 25 January 2012 08:27 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Thanks Michel. First I will change this. then let us see.

I have noted the steps to change the undo_management from MANUAL to AUTO.Can you pls crosscheck this and confirm from your end?

Step 1. create undo tablespace

Step 2. 
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS;
ALTER SYSTEM SET UNDO_RETENTION=900;
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Step 3:
Restart the database.


Also I saw the below parameters related to rollback segments. whether I need to remove or disable this? is there any impact of using this parameter when we have the auto undo management?

SQL> show parameter rollback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
max_rollback_segments                integer     122
rollback_segments                    string
transactions_per_rollback_segment    integer     5
SQL>
SQL> show parameter transaction

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
transaction_auditing                 boolean     TRUE
transactions                         integer     610
transactions_per_rollback_segment    integer     5

[Updated on: Wed, 25 January 2012 08:28]

Report message to a moderator

Re: ORA-01555 [message #540738 is a reply to message #540734] Wed, 25 January 2012 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use SCOPE in all ALTER SYSTEM statements in step 1.
You can ignore the other parameters.

Regards
Michel
Re: ORA-01555 [message #540747 is a reply to message #540732] Wed, 25 January 2012 09:05 Go to previous messageGo to next message
abhi_sri
Messages: 20
Registered: September 2010
Location: India
Junior Member
Can you please confirm your undo tablespace name? Looks there is not seperate undo tablespace. Better create a seperate undo tablespace and make undo management Auto. Because system tablespace is currently using to store undo data and it is not having enough space.That is why transactions overwrites the undo data & you are getting this error.
Re: ORA-01555 [message #540748 is a reply to message #540747] Wed, 25 January 2012 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Because system tablespace is currently using to store undo data and it is not having enough space.
>That is why transactions overwrites the undo data & you are getting this error.
WRONG!
Re: ORA-01555 [message #540833 is a reply to message #540748] Thu, 26 January 2012 02:15 Go to previous messageGo to next message
abhi_sri
Messages: 20
Registered: September 2010
Location: India
Junior Member
It would be great if you give RIGHT! It will deffinatly helpfull for me. As far as I know If there is no undo tablespace specified then System tablespace stores undo data.
Re: ORA-01555 [message #540835 is a reply to message #540833] Thu, 26 January 2012 02:25 Go to previous message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Quote:
If there is no undo tablespace specified then System tablespace stores undo data.
Automatic undo management uses undo segments, which reside in an undo tablespace. Manual undo management uses rollback segments, which reside in the tablespace(s) in which you created them. You are thinking of the one default rollback segment in the system tablespace, which should never be used once the database is created.
But why ask? Rollback segments are (or should be) ancient history.
Previous Topic: reducing tablespace size ???
Next Topic: ORA-00600: internal error code, arguments: [kcbchg1_11]
Goto Forum:
  


Current Time: Fri Nov 29 04:46:58 CST 2024