Home » RDBMS Server » Server Administration » SMON: Parallel transaction recovery tried (Oracle 11.1.0.6 on Solaris 10)
SMON: Parallel transaction recovery tried [message #515114] Fri, 08 July 2011 02:28 Go to next message
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?
Re: SMON: Parallel transaction recovery tried [message #515120 is a reply to message #515114] Fri, 08 July 2011 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59196
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

Re: SMON: Parallel transaction recovery tried [message #515123 is a reply to message #515114] Fri, 08 July 2011 02:57 Go to previous messageGo to next message
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
Re: SMON: Parallel transaction recovery tried [message #515126 is a reply to message #515123] Fri, 08 July 2011 03:11 Go to previous messageGo to next message
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
Re: SMON: Parallel transaction recovery tried [message #515130 is a reply to message #515126] Fri, 08 July 2011 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59196
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
Re: SMON: Parallel transaction recovery tried [message #515233 is a reply to message #515126] Fri, 08 July 2011 23:16 Go to previous messageGo to next message
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
Re: SMON: Parallel transaction recovery tried [message #515259 is a reply to message #515233] Sat, 09 July 2011 09:42 Go to previous messageGo to next message
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
Re: SMON: Parallel transaction recovery tried [message #515341 is a reply to message #515259] Sun, 10 July 2011 21:27 Go to previous messageGo to next message
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
Re: SMON: Parallel transaction recovery tried [message #515342 is a reply to message #515341] Sun, 10 July 2011 21:45 Go to previous messageGo to next message
BlackSwan
Messages: 22804
Registered: January 2009
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.
Re: SMON: Parallel transaction recovery tried [message #515356 is a reply to message #515342] Mon, 11 July 2011 01:37 Go to previous messageGo to next message
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.
Re: SMON: Parallel transaction recovery tried [message #517220 is a reply to message #515356] Sun, 24 July 2011 22:21 Go to previous message
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.
Previous Topic: Free space in standby database
Next Topic: Add tnsentry to TNSNAMES.ORA
Goto Forum:
  


Current Time: Tue Sep 23 10:01:26 CDT 2014

Total time taken to generate the page: 0.09642 seconds