Home » RDBMS Server » Server Administration » Urgent - Probs with Rollack-Segment
Urgent - Probs with Rollack-Segment [message #121634] Tue, 31 May 2005 04:42 Go to next message
marrec
Messages: 35
Registered: May 2005
Location: Mühlheim, Germany
Member
Hi there,

i have a prob with one of my rollback-segments.
this is a big segment, which is used for some special transactions.

Yesterday a transaction failed.
We tried to set the rbs offline. After this the database was very slow, and some employees could'nt work.


the alert.log file said:
SMON: about to recover undo segment xy
SMON: mark undo segment xy as available
.
.
.
these two lines were repeated thousand times and more in the alert.log.
the rbs had the state "Partly Available".
After setting the state back to online, the message disappered.
Now we tried to set the rbs offline to drop and recreate it, but the state is now "pending offline".
In v$rollstat i can see that one transaction is active, but i can't see any transactions in v$transaction.
In v$rollstat the column writes is = 0 !!!

we use oracle 8.1.7 on win200 advanced server

i'd really appreciate any helps,
thx so far
marrec

Report message to a moderator

icon6.gif  Re: Urgent - Probs with Rollack-Segment [message #121638 is a reply to message #121634] Tue, 31 May 2005 05:23 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member
Did you used followin to assign the transaction:

SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;

Run this quiery and see if there are any active transaction on that RBS?

SELECT name, xacts "ACTIVE TRANSACTIONS"
FROM v$rollname, v$rollstat
WHERE status = 'PENDING OFFLINE'
AND v$rollname.usn = v$rollstat.usn;

Are u gettin ORA-1555?

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121639 is a reply to message #121638] Tue, 31 May 2005 05:38 Go to previous messageGo to next message
marrec
Messages: 35
Registered: May 2005
Location: Mühlheim, Germany
Member
hi,

i'm not getting an error.
oracle just says, that there is one active transaction in this rbs.
nobody used the 'set transaction' command.
and in v$transaction there's no transaction at all.
this one transaction seems to me like a "ghost transaction".

in v$rollstat col writes is 0, so the transaction didn't write anything into the rbs.

thx,
marrec

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121664 is a reply to message #121634] Tue, 31 May 2005 06:56 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member
How many RBS you have & what of what size?

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121668 is a reply to message #121664] Tue, 31 May 2005 07:08 Go to previous messageGo to next message
marrec
Messages: 35
Registered: May 2005
Location: Mühlheim, Germany
Member
all in all we've got 23 rbs.

but 15 of them are offline.
remain 7 online (one is system rbs) and 1 (the infamous one) is pending offline.

select usn, rssize / (1024*1024) as "size" --MB
from v$rollstat

USN|size
0 | 0,3828125
2 | 138,078125
3 | 138,078125
4 | 138,078125
5 | 138,078125
6 | 138,078125
7 | 138,078125
22|1315,65625 (pending offline)

usn 0 is system rbs, usn 22 is the big one, which causes the problems.

thx

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121673 is a reply to message #121634] Tue, 31 May 2005 07:31 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member
-- You can Query the V$ROLLSTAT (column WRITES) to find out if there are any UNDO being generated for this session. If the session is active and doing the update, then there will be some undo being generated.

-- The view V$SESSION_LONGOPS will also show the progress of the session. Query this view for the session executing the update statement and this will show if there are any progress being made.

-- Check the V$SESSION_WAIT view to find out if the session is waiting on any particular resource.

select event,p1,p2,p3,seconds_in_wait from v$session_wait where sid = SID and wait_time = 0;

-- The V$SESSTAT will also help in identifying the progress made by the session. Run the Query given below and check if the value is increasing or not.

select st.name,sess.value from v$statname st,v$sesstat sess where st.statistic#=sess.statistic# and st.name = 'redo blocks written' and sess.sid=SID of hanging session;

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121682 is a reply to message #121673] Tue, 31 May 2005 08:43 Go to previous messageGo to next message
marrec
Messages: 35
Registered: May 2005
Location: Mühlheim, Germany
Member
i don't know which session causes the problem, because v$transaction is empty, so i can't join it.

v$rollstat "writes" is 0 !
(the transaction has been running for hours now and there's no undo generated)

in v$session_longops is no record which has column sofar < totalwork --> so every work should be done

In v$session_wait are a lot of session waiting for something:
SQL*Net message from client has state "WAITED UNKNOWN TIME"
rdbms ipc message,
pmon_timer,
buffer_busy_waits,
db file sequential read,
Wait for stopper event to be increased,
PX Deq: Txn Recovery Start


select st.name,sess.value from v$statname st,v$sesstat sess where st.statistic#=sess.statistic# and st.name = 'redo blocks written' and sess.sid=SID

there are 34 rows with
redo blocks written 0

and the last one has:
redo blocks written 31341470
this session is from a background process.


thx
marrec

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121690 is a reply to message #121682] Tue, 31 May 2005 09:08 Go to previous messageGo to next message
marrec
Messages: 35
Registered: May 2005
Location: Mühlheim, Germany
Member
there's one session, which is a system background process, that has:
"Wait for stopper event to be increased"
in v$session_wait - event.

this session has also a TX lock in v$locks. (and some ps locks)

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121854 is a reply to message #121690] Wed, 01 June 2005 08:27 Go to previous messageGo to next message
marrec
Messages: 35
Registered: May 2005
Location: Mühlheim, Germany
Member
hi,

finally (when all the others went home) i set the rollback-segment online and did a 'shutdown immediate'. then i started the database again and set the rollback-segment offline again.
After a restart the rbs was offline and the database is working fine again.

thx,
marrec

Report message to a moderator

Re: Urgent - Probs with Rollack-Segment [message #121960 is a reply to message #121634] Thu, 02 June 2005 00:30 Go to previous message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member
Yeah, I was havin an idea the process is hanged some where, and a clean shutdown will solve this problem...

Well good the DB is back in good health Smile

Have a nice day.

[Updated on: Thu, 02 June 2005 00:34]

Report message to a moderator

Previous Topic: ora-01555
Next Topic: compatible parameter 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 10 07:46:27 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.