ORA-01555 [message #540707] |
Wed, 25 January 2012 07:26 |
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 |
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 |
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 #540734 is a reply to message #540733] |
Wed, 25 January 2012 08:27 |
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 #540747 is a reply to message #540732] |
Wed, 25 January 2012 09:05 |
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 #540833 is a reply to message #540748] |
Thu, 26 January 2012 02:15 |
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 |
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.
|
|
|