OraFAQ Forum: Server Administration » SMON: Parallel transaction recovery tried

Home » RDBMS Server » Server Administration » SMON: Parallel transaction recovery tried (Oracle 11.1.0.6 on Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SMON: Parallel transaction recovery tried [message #515114]

Fri, 08 July 2011 02:28

luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam

Member

I found the "SMON: Parallel transaction recovery tried" error message in the alert log once per day at 19:23 more or less everyday. Every time this message occurs, it generate a 7.2GB dump file named "core" in $ORACLE_HOME/dbs.

Here is part of the alert log

Tue Jul 05 19:18:45 2011
LNS: Standby redo logfile selected for thread 1 sequence 537178 for destination
LOG_ARCHIVE_DEST_2
Tue Jul 05 19:24:18 2011
SMON: Parallel transaction recovery tried
Tue Jul 05 19:25:54 2011
Thread 1 advanced to log sequence 537179

Please tell me how can I find out what transactions have caused the problem?

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515120 is a reply to message #515114]

Fri, 08 July 2011 02:46

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You get this message when SMON failed to spawn a slave process to parallel recover transactions.
This is related to FAST_START_PARALLEL_ROLLBACK

Check its value and maybe set it to FALSE to disable parallelism.

Regards
Michel

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515123 is a reply to message #515114]

Fri, 08 July 2011 02:57

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

More important than disabling Parallel Rollback, you need to know which transactions are these. Why at the same time every day ? There's obviously a pattern.

Hemant K Chitale

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515126 is a reply to message #515123]

Fri, 08 July 2011 03:11

luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam

Member

hkchital wrote on Fri, 08 July 2011 14:57

More important than disabling Parallel Rollback, you need to know which transactions are these. Why at the same time every day ? There's obviously a pattern.

Hemant K Chitale

That's why I didn't disable it. I've checked all the JOB and there is no failed one. Also no specific job run at that time. The database is up and functioning properly during the file dump.
I'd like to find out what transactions happened at that time.

Here is the result from V$FAST_START_TRANSACTIONS

SQL> select * from V$FAST_START_TRANSACTIONS order by seq;
USN                    SLT                    SEQ                    STATE            UNDOBLOCKSDONE         UNDOBLOCKSTOTAL        PID                    CPUTIME                PARENTUSN              PARENTSLT              PARENTSEQ              XID              PXID             RCVSERVERS             
---------------------- ---------------------- ---------------------- ---------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------- ---------------- ---------------------- 
13                     32                     218729                 RECOVERED        25                     25                                            16                                                                                          000D002000035669                  1                      
13                     28                     223713                 RECOVERED        25                     25                                            15                                                                                          000D001C000369E1                  1                      
12                     21                     225187                 RECOVERED        26                     26                                            15                                                                                          000C001500036FA3                  1                      
13                     16                     226262                 RECOVERED        25                     25                                            15                                                                                          000D0010000373D6                  1                      
13                     30                     227470                 RECOVERED        26                     26                                            15                                                                                          000D001E0003788E                  1                      
12                     5                      228357                 RECOVERED        25                     25                                            16                                                                                          000C000500037C05                  1                      
12                     8                      238298                 RECOVERED        26                     26                                            15                                                                                          000C00080003A2DA                  1                      
1                      12                     403744                 RECOVERED        26                     26                                            15                                                                                          0001000C00062920                  1                      
2                      25                     405537                 RECOVERED        26                     26                                            15                                                                                          0002001900063021                  1                      
1                      4                      413404                 RECOVERED        26                     26                                            15                                                                                          0001000400064EDC                  1                      
2                      12                     418444                 RECOVERED        26                     26                                            15                                                                                          0002000C0006628C                  1                      
3                      3                      477242                 RECOVERED        25                     25                                            14                                                                                          000300030007483A                  1                      
11                     27                     482027                 RECOVERED        26                     26                                            15                                                                                          000B001B00075AEB                  1                      
11                     0                      486482                 RECOVERED        25                     25                                            16                                                                                          000B000000076C52                  1                      
4                      3                      499622                 RECOVERED        26                     26                                            15                                                                                          0004000300079FA6                  1                      
4                      17                     501039                 RECOVERED        26                     26                                            16                                                                                          000400110007A52F                  1                      
11                     12                     509640                 RECOVERED        26                     26                                            14                                                                                          000B000C0007C6C8                  1                      
4                      21                     516266                 RECOVERED        26                     26                                            15                                                                                          000400150007E0AA                  1                      
4                      13                     524878                 RECOVERED        26                     26                                            14                                                                                          0004000D0008024E                  1                      
5                      22                     531978                 RECOVERED        26                     26                                            15                                                                                          0005001600081E0A                  1                      
5                      32                     536570                 RECOVERED        26                     26                                            15                                                                                          0005002000082FFA                  1                      
7                      1                      541052                 RECOVERED        26                     26                                            16                                                                                          000700010008417C                  1                      
6                      4                      544031                 RECOVERED        26                     26                                            15                                                                                          0006000400084D1F                  1                      
7                      5                      548371                 RECOVERED        26                     26                                            16                                                                                          0007000500085E13                  1                      
6                      9                      551061                 RECOVERED        26                     26                                            15                                                                                          0006000900086895                  1                      
6                      0                      551739                 RECOVERED        26                     26                                            18                                                                                          0006000000086B3B                  1                      
6                      18                     555448                 RECOVERED        26                     26                                            16                                                                                          00060012000879B8                  1                      
7                      19                     557241                 RECOVERED        25                     25                                            15                                                                                          00070013000880B9                  1                      
7                      13                     567726                 RECOVERED        26                     26                                            17                                                                                          0007000D0008A9AE                  1                      
7                      7                      567879                 RECOVERED        25                     25                                            15                                                                                          000700070008AA47                  1                      
8                      18                     572563                 RECOVERED        26                     26                                            16                                                                                          000800120008BC93                  1                      
8                      31                     580521                 RECOVERED        26                     26                                            15                                                                                          0008001F0008DBA9                  1                      
8                      16                     580692                 RECOVERED        26                     26                                            14                                                                                          000800100008DC54                  1                      
8                      0                      587516                 RECOVERED        25                     25                                            16                                                                                          000800000008F6FC                  1                      
9                      18                     602361                 RECOVERED        26                     26                                            15                                                                                          00090012000930F9                  1                      
9                      20                     610094                 RECOVERED        26                     26                                            18                                                                                          0009001400094F2E                  1                      
10                     11                     622684                 RECOVERED        25                     25                                            15                                                                                          000A000B0009805C                  1                      
10                     23                     622711                 RECOVERED        26                     26                                            15                                                                                          000A001700098077                  1                      
10                     18                     623217                 RECOVERED        26                     26                                            15                                                                                          000A001200098271                  1                      
10                     4                      631597                 RECOVERED        26                     26                                            15                                                                                          000A00040009A32D                  1                      
10                     16                     643422                 RECOVERED        25                     25                                            14                                                                                          000A00100009D15E                  1                      
10                     20                     655780                 RECOVERED        26                     26                                            15                                                                                          000A0014000A01A4                  1                      

42 rows selected

Thanks

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515130 is a reply to message #515126]

Fri, 08 July 2011 03:17

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515233 is a reply to message #515126]

Fri, 08 July 2011 23:16

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

That looks like a very large number of transactions that failed at the same time.
Could it be an application server (running multiple database sessions) being killed/rebooted ?

Hemant K Chitale

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515259 is a reply to message #515233]

Sat, 09 July 2011 09:42

luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam

Member

Hi Hemant,

I think they are cumulative failed transactions but don't know exactly when did each of them happen. Also not know how to find the command they executed.

Could it be an application server (running multiple database sessions) being killed/rebooted ?
> nothing was killed, all the servers keep working okay

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515341 is a reply to message #515259]

Sun, 10 July 2011 21:27

luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam

Member

Can you experts tell me how to find out the transactions that caused the problem? Any suggestion is highly appreciated. Thanks

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515342 is a reply to message #515341]

Sun, 10 July 2011 21:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Tue Jul 05 19:24:18 2011
>SMON: Parallel transaction recovery tried
>Tue Jul 05 19:25:54 2011
>Thread 1 advanced to log sequence 537179

in alert_SID.log file the timestamp get written & then on next line the details are written
in a span of under 100 seconds, the SMON error was written & then the REDO logfile switched.

I would run DBMS_LOGMNR to see what DML was prior to Tue Jul 05 19:24:18 2011.
I suspect/hope that by identifying the active DML, the possible suspects should be greatly reduced.
Especially intersecting the DML with objects which have PARALLEL/DEGREE > 1.

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #515356 is a reply to message #515342]

Mon, 11 July 2011 01:37

luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam

Member

Thanks BlackSwan for your response, I'm going to try with DBMS_LOGMNR as your advised.

Report message to a moderator

Re: SMON: Parallel transaction recovery tried [message #517220 is a reply to message #515356]

Sun, 24 July 2011 22:21

luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam

Member

It has been 2 weeks since I started to analyse the transactions using Log Miner, and notice that there is always an update statement which raises the rollback

SQL> select  to_char(lc.timestamp,'HH24:MI:SS') "time",
  2          lc.machine_name,
  3          lc.username,
  4          lc.operation,
  5          lc.rollback,
  6          lc.xid,
  7          lc.seg_owner, lc.seg_name,
  8          lc.sql_redo
  9  from    v$logmnr_contents lc
 10  where   lc.xid in (select xid from V$FAST_START_TRANSACTIONS)
 11    and   operation != 'INTERNAL'
 12  order by lc.timestamp desc;

time       MACHINE_NA USERNAME   OPERATION   ROLLBACK XID                SEG_OWNER  SEG_NAME        
---------- ---------- ---------- ---------- --------- ------------------ ---------- --------------- 
SQL_REDO                                                                                            
----------------------------------------------------------------------------------------------------
19:24:01   UNKNOWN    UNKNOWN    ROLLBACK           0 0008000E00093046                              
rollback;                                                        

19:24:01   UNKNOWN    UNKNOWN    DELETE             1 0008000E00093046   UNKNOWN    OBJ# 70561      
delete from "UNKNOWN"."OBJ# 70561" where ROWID = 'AAAqwsAF7AACMq0AAD';                              

19:23:40   UNKNOWN    UNKNOWN    UPDATE             1 0008000E00093046   UNKNOWN    OBJ# 70561      
update "UNKNOWN"."OBJ# 70561" set "COL 35" = HEXTORAW('00540001010c008000010000000100001bbbcd07000a4
090000420000000') where ROWID = 'AAAqwsAF7AACMq0AAD';

The alert message "SMON: Parallel transaction recovery tried" occurs at 19:23:56. I guess that the UPDATE statement started at 19:23:40 caused the problem
"OBJ# 70561" sure is the object ID, but "COL 35" seems not to be the column ID. I had logged all the statements which update the column ID 35, result is no one touch that column.

Please can anyone tell me how to find out the name of "COL 35" in the above UPDATE statement?

Thanks very much for your help.

Report message to a moderator

Previous Topic:	Free space in standby database
Next Topic:	Add tnsentry to TNSNAMES.ORA

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 11:18:48 CDT 2024