Re: A new form of ORA-1555
I'm not using AUM on my production databases because they're still 8.1.7. To tell the truth, I haven't seen any major patches for AUM in the 9iR2. Well, when Oxford goes to 9.2, I guess AUM is an option. I'm still paranoid about the new features. I believe that progress does bad things to the lambs and ruins our milk. On 2003.06.06 23:49 Rachel Carmichael wrote: we are using AUM pretty successfully for our production systems. Interestingly enough the one problem we did have was on a test box, and we couldn't repeat it --- Daniel W. Fink [EMAIL PROTECTED] wrote: Mladen, I could not agree more! I seriously pondered not posting this information at all. FBQ is a nice feature, but I would not depend upon it. I'm a conservative and somewhat paranoid DBA and I would not recommend AUM for production systems, though certain very knowledgable and respected members of this list disagree with me. We each have our reasons for our positions and we are both right. -- Daniel W. Fink http://www.optimaldba.com Gogala, Mladen wrote: Well, I've read a lot of that on this list (this is not the first time FBQ is being discussed) but as a conservative and somewhat paranoid DBA, I don't want to try anything that isn't supported with a very new feature like FBQ. The experience taught me a lesson about ora-600 and alike. I really wouldn't want to depend on a feature that is very, very new. I'd much rather use logminer instead. That's just me. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A new form of ORA-1555
A classic case when AUM does not help prevent 1555 errors ! Query duration is MAXQUERYLENreported inv$undostat view. SCN could be the 'as of SCN' when the query started (not sure, as I could never get my small tests to failwith 1555 when using AUM). What is also interesting is the SMON activity of offlining undo segments. That very well could be the reason for this error. In this case adjusting UNDO_RETENTION MAXQUERYLEN is one option. The other being, correcting this long running query. BTW.. Raj I exchanged e-mails earlier regarding this, as I just wanted to confirm that he was using Auto Undo Dmgt feature ;) He informed me that v$undostat view did report a non-zero count in the SSOLDERRCNT column as expected... so that column works okay. Cheers! - Kirti "Jamadagni, Rajendra" [EMAIL PROTECTED] wrote: A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:"SYS_B_00") G_1, ROUND(G_2/:"SYS_B_01") G_2, ROUND(G_3/:"SYS_B_02") G_3, ROUND(G_4/:"SYS_B_03") G_4, R OUND(G_5/:"SYS_B_04") G_5, ROUND(A_1/:"SYS_B_05") A_1, ROUND(A_2/:"SYS_B_06") A_2, ROUND(A_3/:"SYS_B_07") A_3, ROUND(A_4 /:"SYS_B_08") A_4, ROUND(A_5/:"SYS_B_09") A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:"SYS_B_10",NVL((DECODE(QTR,:"SYS_B_11",NVL(CURR_GOAL,:"SYS_B_12"))),:"SYS_B_13"),:"SYS_B_14") G_1,DECO DE(YEAR,:"SYS_B_15",NVL((DECODE(QTR,:"SYS_B_16",NVL(CURR_GOAL,:"SYS_B_17"))),:"SYS_B_18"),:"SYS_B_19") G_2,DECODE(YEAR,: "SYS_B_20",NVL((DECODE(QTR,:"SYS_B_21",NVL(CURR_GOAL,:"SYS_B_22"))),:"SYS_B_23"),:"SYS_B_24") G_3,DECODE(YEAR,:"SYS_B_25 ",NVL((DECODE(QTR,:"SYS_B_26",NVL(CURR_GOAL,:"SYS_B_27"))),:"SYS_B_28"),:"SYS_B_29") G_4,DECODE(YEAR,:"SYS_B_30",NVL((DE CODE(QTR,:"SYS_B_31",NVL(CURR_GOAL,:"SYS_B_32"))),:"SYS_B_33"),:"SYS_B_34") G_5,DECODE(YEAR,:"SYS_B_35",NVL((DECODE(QTR, :"SYS_B_36",NVL(CURR_ACCRUAL,:"SYS_B_37"))),:"SYS_B_38"),:"SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! * Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: A new form of ORA-1555
we ended up with an ORA-600 after lots of 'SMON offlining undo segment messages in the alert log. Happened only once, Support asked us to set an event and trap it when/if it happens again I'm beginning to think maybe I should have stuck with rollback segments --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: A classic case when AUM does not help prevent 1555 errors ! Query duration is MAXQUERYLEN reported in v$undostat view. SCN could be the 'as of SCN' when the query started (not sure, as I could never get my small tests to fail with 1555 when using AUM). What is also interesting is the SMON activity of offlining undo segments. That very well could be the reason for this error. In this case adjusting UNDO_RETENTION MAXQUERYLEN is one option. The other being, correcting this long running query. BTW.. Raj I exchanged e-mails earlier regarding this, as I just wanted to confirm that he was using Auto Undo Dmgt feature ;) He informed me that v$undostat view did report a non-zero count in the SSOLDERRCNT column as expected... so that column works okay. Cheers! - Kirti Jamadagni, Rajendra [EMAIL PROTECTED] wrote: A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:SYS_B_00) G_1, ROUND(G_2/:SYS_B_01) G_2, ROUND(G_3/:SYS_B_02) G_3, ROUND(G_4/:SYS_B_03) G_4, R OUND(G_5/:SYS_B_04) G_5, ROUND(A_1/:SYS_B_05) A_1, ROUND(A_2/:SYS_B_06) A_2, ROUND(A_3/:SYS_B_07) A_3, ROUND(A_4 /:SYS_B_08) A_4, ROUND(A_5/:SYS_B_09) A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:SYS_B_10,NVL((DECODE(QTR,:SYS_B_11,NVL(CURR_GOAL,:SYS_B_12))),:SYS_B_13),:SYS_B_14) G_1,DECO DE(YEAR,:SYS_B_15,NVL((DECODE(QTR,:SYS_B_16,NVL(CURR_GOAL,:SYS_B_17))),:SYS_B_18),:SYS_B_19) G_2,DECODE(YEAR,: SYS_B_20,NVL((DECODE(QTR,:SYS_B_21,NVL(CURR_GOAL,:SYS_B_22))),:SYS_B_23),:SYS_B_24) G_3,DECODE(YEAR,:SYS_B_25 ,NVL((DECODE(QTR,:SYS_B_26,NVL(CURR_GOAL,:SYS_B_27))),:SYS_B_28),:SYS_B_29) G_4,DECODE(YEAR,:SYS_B_30,NVL((DE CODE(QTR,:SYS_B_31,NVL(CURR_GOAL,:SYS_B_32))),:SYS_B_33),:SYS_B_34) G_5,DECODE(YEAR,:SYS_B_35,NVL((DECODE(QTR, :SYS_B_36,NVL(CURR_ACCRUAL,:SYS_B_37))),:SYS_B_38),:SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! * - Do you Yahoo!? Free online calendar with sync to Outlook(TM). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A new form of ORA-1555
Title: A new form of ORA-1555 It might have something to do with setting the table to do a Flashback query. Maybe Dan will know. Ruth - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Friday, June 06, 2003 10:00 AM Subject: A new form of ORA-1555 A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:"SYS_B_00") G_1, ROUND(G_2/:"SYS_B_01") G_2, ROUND(G_3/:"SYS_B_02") G_3, ROUND(G_4/:"SYS_B_03") G_4, R OUND(G_5/:"SYS_B_04") G_5, ROUND(A_1/:"SYS_B_05") A_1, ROUND(A_2/:"SYS_B_06") A_2, ROUND(A_3/:"SYS_B_07") A_3, ROUND(A_4 /:"SYS_B_08") A_4, ROUND(A_5/:"SYS_B_09") A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:"SYS_B_10",NVL((DECODE(QTR,:"SYS_B_11",NVL(CURR_GOAL,:"SYS_B_12"))),:"SYS_B_13"),:"SYS_B_14") G_1,DECO DE(YEAR,:"SYS_B_15",NVL((DECODE(QTR,:"SYS_B_16",NVL(CURR_GOAL,:"SYS_B_17"))),:"SYS_B_18"),:"SYS_B_19") G_2,DECODE(YEAR,: "SYS_B_20",NVL((DECODE(QTR,:"SYS_B_21",NVL(CURR_GOAL,:"SYS_B_22"))),:"SYS_B_23"),:"SYS_B_24") G_3,DECODE(YEAR,:"SYS_B_25 ",NVL((DECODE(QTR,:"SYS_B_26",NVL(CURR_GOAL,:"SYS_B_27"))),:"SYS_B_28"),:"SYS_B_29") G_4,DECODE(YEAR,:"SYS_B_30",NVL((DE CODE(QTR,:"SYS_B_31",NVL(CURR_GOAL,:"SYS_B_32"))),:"SYS_B_33"),:"SYS_B_34") G_5,DECODE(YEAR,:"SYS_B_35",NVL((DECODE(QTR, :"SYS_B_36",NVL(CURR_ACCRUAL,:"SYS_B_37"))),:"SYS_B_38"),:"SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
Re: A new form of ORA-1555
You do not have to specifically enable a table for flashback query. In 9.2 (Kirti, please correct me if I am wrong), it is automatically enabled, even if you are not using AUM. I believe that this is a new error message format for 9. The solution that is commonly quoted is to increase your undo_retention to cover the amount of time needed to complete the query. However, this is not a guarantee that the query will actually work. If space is needed, unexpired extents will be reused. I think you may be hitting a situation similar to what Rachel encountered several weeks ago. It appears that a large number (at least 104) undo segments have been created. SMON is now offlining them in the hopes of reclaiming space. This brings up a situation posed recently and it was not a condition I had tested. If a large number of segments is created, are they ever dropped or do they continue to exist and consume space (at least 2 extents/128k) in the undo tablespace until it is dropped and a new one created? I THINK that the answer is that once created an undo segment will exist until the tablespace is dropped as there is no 'drop undo segment' command with aum. -- Daniel W. Fink http://www.optimaldba.com Ruth Gramolini wrote: A new form of ORA-1555 It might have something to do with setting the table to do a Flashback query. Maybe Dan will know. Ruth - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Friday, June 06, 2003 10:00 AM Subject: A new form of ORA-1555 A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (QueryDuration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:"SYS_B_00") G_1, ROUND(G_2/:"SYS_B_01") G_2,ROUND(G_3/:"SYS_B_02") G_3, ROUND(G_4/:"SYS_B_03") G_4, R OUND(G_5/:"SYS_B_04") G_5,ROUND(A_1/:"SYS_B_05") A_1, ROUND(A_2/:"SYS_B_06") A_2, ROUND(A_3/:"SYS_B_07")A_3, ROUND(A_4 /:"SYS_B_08") A_4, ROUND(A_5/:"SYS_B_09") A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECTDECODE(YEAR,:"SYS_B_10",NVL((DECODE(QTR,:"SYS_B_11",NVL(CURR_GOAL,:"SYS_B_12"))),:"SYS_B_13"),:"SYS_B_14") G_1,DECO DE(YEAR,:"SYS_B_15",NVL((DECODE(QTR,:"SYS_B_16",NVL(CURR_GOAL,:"SYS_B_17"))),:"SYS_B_18"),:"SYS_B_19") G_2,DECODE(YEAR,: "SYS_B_20",NVL((DECODE(QTR,:"SYS_B_21",NVL(CURR_GOAL,:"SYS_B_22"))),:"SYS_B_23"),:"SYS_B_24") G_3,DECODE(YEAR,:"SYS_B_25 ",NVL((DECODE(QTR,:"SYS_B_26",NVL(CURR_GOAL,:"SYS_B_27"))),:"SYS_B_28"),:"SYS_B_29") G_4,DECODE(YEAR,:"SYS_B_30",NVL((DE CODE(QTR,:"SYS_B_31",NVL(CURR_GOAL,:"SYS_B_32"))),:"SYS_B_33"),:"SYS_B_34") G_5,DECODE(YEAR,:"SYS_B_35",NVL((DECODE(QTR, :"SYS_B_36",NVL(CURR_ACCRUAL,:"SYS_B_37"))),:"SYS_B_38"),:"SYS_B ~ But I havn't figured out why query durationand SCN is shown any ideas? ... what is the significance? Oracle 9202,RAC TIA Raj Rajendra dot Jamadagni at nospamespn dotcom All Views expressed in thisemail are strictly personal. QOTD:Any clod can have facts, having an opinion is an art !
RE: A new form of ORA-1555
Flashback query is enabled by setting the UNDO_RETENTION parameter to the desired number of seconds. Table V$UNDOSTAT contains the column MAXQUERYLEN which gives the max time that you can use to dynamically set UNDO_RETENTION. You also have to enable flashback for session using DBMS_FLASHBACK. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message-From: Daniel W. Fink [mailto:[EMAIL PROTECTED]Sent: Friday, June 06, 2003 5:01 PMTo: Multiple recipients of list ORACLE-LSubject: Re: A new form of ORA-1555You do not have to specifically enable a table for flashback query. In 9.2 (Kirti, please correct me if I am wrong), it is automatically enabled, even if you are not using AUM.I believe that this is a new error message format for 9. The solution that is commonly quoted is to increase your undo_retention to cover the amount of time needed to complete the query. However, this is not a guarantee that the query will actually work. If space is needed, unexpired extents will be reused. I think you may be hitting a situation similar to what Rachel encountered several weeks ago. It appears that a large number (at least 104) undo segments have been created. SMON is now offlining them in the hopes of reclaiming space. This brings up a situation posed recently and it was not a condition I had tested. If a large number of segments is created, are they ever dropped or do they continue to exist and consume space (at least 2 extents/128k) in the undo tablespace until it is dropped and a new one created? I THINK that the answer is that once created an undo segment will exist until the tablespace is dropped as there is no 'drop undo segment' command with aum. -- Daniel W. Fink http://www.optimaldba.comRuth Gramolini wrote: It might have something to do with setting the table to do a Flashback query. Maybe Dan will know. Ruth - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Friday, June 06, 2003 10:00 AM Subject: A new form of ORA-1555 A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:"SYS_B_00") G_1, ROUND(G_2/:"SYS_B_01") G_2, ROUND(G_3/:"SYS_B_02") G_3, ROUND(G_4/:"SYS_B_03") G_4, R OUND(G_5/:"SYS_B_04") G_5, ROUND(A_1/:"SYS_B_05") A_1, ROUND(A_2/:"SYS_B_06") A_2, ROUND(A_3/:"SYS_B_07") A_3, ROUND(A_4 /:"SYS_B_08") A_4, ROUND(A_5/:"SYS_B_09") A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:"SYS_B_10",NVL((DECODE(QTR,:"SYS_B_11",NVL(CURR_GOAL,:"SYS_B_12"))),:"SYS_B_13"),:"SYS_B_14") G_1,DECO DE(YEAR,:"SYS_B_15",NVL((DECODE(QTR,:"SYS_B_16",NVL(CURR_GOAL,:"SYS_B_17"))),:"SYS_B_18"),:"SYS_B_19") G_2,DECODE(YEAR,: "SYS_B_20",NVL((DECODE(QTR,:"SYS_B_21",NVL(CURR_GOAL,:"SYS_B_22"))),:"SYS_B_23"),:"SYS_B_24") G_3,DECODE(YEAR,:"SYS_B_25 ",NVL((DECODE(QTR,:"SYS_B_26",NVL(CURR_GOAL,:"SYS_B_27"))),:"SYS_B_28"),:"SYS_B_29") G_4,DECODE(YEAR,:"SYS_B_30",NVL((DE CODE(QTR,:"SYS_B_31",NVL(CURR_GOAL,:"SYS_B_32"))),:"SYS_B_33"),:"SYS_B_34") G_5,DECODE(YEAR,:"SYS_B_35",NVL((DECODE(QTR, :"SYS_B_36",NVL(CURR_ACCRUAL,:"SYS_B_37"))),:"SYS_B_38"),:"SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
Re: A new form of ORA-1555
well it's been a few weeks and ours are still there, offline so I guess they don't get dropped. --- Daniel W. Fink [EMAIL PROTECTED] wrote: You do not have to specifically enable a table for flashback query. In 9.2 (Kirti, please correct me if I am wrong), it is automatically enabled, even if you are not using AUM. I believe that this is a new error message format for 9. The solution that is commonly quoted is to increase your undo_retention to cover the amount of time needed to complete the query. However, this is not a guarantee that the query will actually work. If space is needed, unexpired extents will be reused. I think you may be hitting a situation similar to what Rachel encountered several weeks ago. It appears that a large number (at least 104) undo segments have been created. SMON is now offlining them in the hopes of reclaiming space. This brings up a situation posed recently and it was not a condition I had tested. If a large number of segments is created, are they ever dropped or do they continue to exist and consume space (at least 2 extents/128k) in the undo tablespace until it is dropped and a new one created? I THINK that the answer is that once created an undo segment will exist until the tablespace is dropped as there is no 'drop undo segment' command with aum. -- Daniel W. Fink http://www.optimaldba.com Ruth Gramolini wrote: It might have something to do with setting the table to do a Flashback query. Maybe Dan will know. Ruth - Original Message - From: Jamadagni, Rajendra mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 10:00 AM Subject: A new form of ORA-1555 A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:SYS_B_00) G_1, ROUND(G_2/:SYS_B_01) G_2, ROUND(G_3/:SYS_B_02) G_3, ROUND(G_4/:SYS_B_03) G_4, R OUND(G_5/:SYS_B_04) G_5, ROUND(A_1/:SYS_B_05) A_1, ROUND(A_2/:SYS_B_06) A_2, ROUND(A_3/:SYS_B_07) A_3, ROUND(A_4 /:SYS_B_08) A_4, ROUND(A_5/:SYS_B_09) A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:SYS_B_10,NVL((DECODE(QTR,:SYS_B_11,NVL(CURR_GOAL,:SYS_B_12))),:SYS_B_13),:SYS_B_14) G_1,DECO DE(YEAR,:SYS_B_15,NVL((DECODE(QTR,:SYS_B_16,NVL(CURR_GOAL,:SYS_B_17))),:SYS_B_18),:SYS_B_19) G_2,DECODE(YEAR,: SYS_B_20,NVL((DECODE(QTR,:SYS_B_21,NVL(CURR_GOAL,:SYS_B_22))),:SYS_B_23),:SYS_B_24) G_3,DECODE(YEAR,:SYS_B_25 ,NVL((DECODE(QTR,:SYS_B_26,NVL(CURR_GOAL,:SYS_B_27))),:SYS_B_28),:SYS_B_29) G_4,DECODE(YEAR,:SYS_B_30,NVL((DE CODE(QTR,:SYS_B_31,NVL(CURR_GOAL,:SYS_B_32))),:SYS_B_33),:SYS_B_34) G_5,DECODE(YEAR,:SYS_B_35,NVL((DECODE(QTR, :SYS_B_36,NVL(CURR_ACCRUAL,:SYS_B_37))),:SYS_B_38),:SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A new form of ORA-1555
Acutally, there nothing at the table level to 'enable' it for the FBQ (in 9i R1 and R2). In 9i R2, they introduced a 'flashback' privilege (object and System level). In 9i R1 one needed exec privilege on a package (dbms_flashback) to use FBQ. SMON offlined Undo Segs because those had been idled, had expired undo_retention period, and current number of concurrent transactions dropped. AUS still left online were due to either 1) they had active transactions or 2) unexpired extents or 3) SESSIONS parameter dectated... Dan, you are correct. After AUS gets created it gets dropped only when the Undo TS is dropped. (Also, switching to a new UNDO TS does not drop old AUS from old unto TS.) Whether it comes on-line or not is solely dependent on the transaction load. And that may create the possibility of getting 'Out of Space' error, in case, if I resized the UNDO TS datafiles to a smaller value ;) - Kirti --- Daniel W. Fink [EMAIL PROTECTED] wrote: You do not have to specifically enable a table for flashback query. In 9.2 (Kirti, please correct me if I am wrong), it is automatically enabled, even if you are not using AUM. I believe that this is a new error message format for 9. The solution that is commonly quoted is to increase your undo_retention to cover the amount of time needed to complete the query. However, this is not a guarantee that the query will actually work. If space is needed, unexpired extents will be reused. I think you may be hitting a situation similar to what Rachel encountered several weeks ago. It appears that a large number (at least 104) undo segments have been created. SMON is now offlining them in the hopes of reclaiming space. This brings up a situation posed recently and it was not a condition I had tested. If a large number of segments is created, are they ever dropped or do they continue to exist and consume space (at least 2 extents/128k) in the undo tablespace until it is dropped and a new one created? I THINK that the answer is that once created an undo segment will exist until the tablespace is dropped as there is no 'drop undo segment' command with aum. -- Daniel W. Fink http://www.optimaldba.com Ruth Gramolini wrote: It might have something to do with setting the table to do a Flashback query. Maybe Dan will know. Ruth - Original Message - From: Jamadagni, Rajendra mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 10:00 AM Subject: A new form of ORA-1555 A sighting in alert log ... SMON offlining US=102 SMON offlining US=104 Fri Jun 6 08:42:06 2003 ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24): Fri Jun 6 08:42:06 2003 SELECT ROUND(G_1/:SYS_B_00) G_1, ROUND(G_2/:SYS_B_01) G_2, ROUND(G_3/:SYS_B_02) G_3, ROUND(G_4/:SYS_B_03) G_4, R OUND(G_5/:SYS_B_04) G_5, ROUND(A_1/:SYS_B_05) A_1, ROUND(A_2/:SYS_B_06) A_2, ROUND(A_3/:SYS_B_07) A_3, ROUND(A_4 /:SYS_B_08) A_4, ROUND(A_5/:SYS_B_09) A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT From (SE LECT DECODE(YEAR,:SYS_B_10,NVL((DECODE(QTR,:SYS_B_11,NVL(CURR_GOAL,:SYS_B_12))),:SYS_B_13),:SYS_B_14) G_1,DECO DE(YEAR,:SYS_B_15,NVL((DECODE(QTR,:SYS_B_16,NVL(CURR_GOAL,:SYS_B_17))),:SYS_B_18),:SYS_B_19) G_2,DECODE(YEAR,: SYS_B_20,NVL((DECODE(QTR,:SYS_B_21,NVL(CURR_GOAL,:SYS_B_22))),:SYS_B_23),:SYS_B_24) G_3,DECODE(YEAR,:SYS_B_25 ,NVL((DECODE(QTR,:SYS_B_26,NVL(CURR_GOAL,:SYS_B_27))),:SYS_B_28),:SYS_B_29) G_4,DECODE(YEAR,:SYS_B_30,NVL((DE CODE(QTR,:SYS_B_31,NVL(CURR_GOAL,:SYS_B_32))),:SYS_B_33),:SYS_B_34) G_5,DECODE(YEAR,:SYS_B_35,NVL((DECODE(QTR, :SYS_B_36,NVL(CURR_ACCRUAL,:SYS_B_37))),:SYS_B_38),:SYS_B ~ But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
Re: A new form of ORA-1555
Mladen, That is a common misconception, one that Oracle wholeheartedly supports. Flashback Query (FBQ) depends upon the smon_scn_time table, which is popluated in 9i, regardless of the undo_management setting. This table is populated regardless of the UNDO_RETENTION parameter. This parameter is the guideline (not absolute) that Oracle uses when deciding which extents to reclaim when it needs space. If Oracle needs space, it may take extents containing committed transaction info that is still within the UNDO_RETENTION time. You can perform FBQ using RBSs, though this is probably not supported by Oracle, so use it at your own peril. The chances of success are diminished because of the difference in undo space management (rbs reuses space more often). I have done this several times, but in a non-production environment. IIRC, v$undostat is not populated unless the undo_management is set to auto. This really does not help in making the transition from rbs to aum. It would be nice to have it populated regardless so that you would have data to use to set the undo tablespace size and the value of UNDO_RETENTION. I did make one mistake. You have to enable flashback for a table or have the flashback_any_table privilege (Thanks, Kirti). Dan Gogala, Mladen wrote: Flashback query is enabled by setting the UNDO_RETENTION parameter to the desired number of seconds. Table V$UNDOSTAT contains the column MAXQUERYLEN which gives the max time that you can use to dynamically set UNDO_RETENTION. You also have to enable flashback for session using DBMS_FLASHBACK. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED]
RE: A new form of ORA-1555
Well, I've read a lot of that on this list (this is not the first time FBQ is being discussed) but as a conservative and somewhat paranoid DBA, I don't want to try anything that isn't supported with a very new feature like FBQ. The experience taught me a lesson about ora-600 and alike. I really wouldn't want to depend on a feature that is very, very new. I'd much rather use logminer instead. That's just me. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message-From: Daniel W. Fink [mailto:[EMAIL PROTECTED]Sent: Friday, June 06, 2003 6:20 PMTo: Multiple recipients of list ORACLE-LSubject: Re: A new form of ORA-1555Mladen, That is a common misconception, one that Oracle wholeheartedly supports. Flashback Query (FBQ) depends upon the smon_scn_time table, which is popluated in 9i, regardless of the undo_management setting. This table is populated regardless of the UNDO_RETENTION parameter. This parameter is the guideline (not absolute) that Oracle uses when deciding which extents to reclaim when it needs space. If Oracle needs space, it may take extents containing committed transaction info that is still within the UNDO_RETENTION time. You can perform FBQ using RBSs, though this is probably not supported by Oracle, so use it at your own peril. The chances of success are diminished because of the difference in undo space management (rbs reuses space more often). I have done this several times, but in a non-production environment. IIRC, v$undostat is not populated unless the undo_management is set to auto. This really does not help in making the transition from rbs to aum. It would be nice to have it populated regardless so that you would have data to use to set the undo tablespace size and the value of UNDO_RETENTION. I did make one mistake. You have to enable flashback for a table or have the flashback_any_table privilege (Thanks, Kirti).DanGogala, Mladen wrote: Flashback query is enabled by setting the UNDO_RETENTION parameter to the desired number of seconds. Table V$UNDOSTAT contains the column MAXQUERYLEN which gives the max time that you can use to dynamically set UNDO_RETENTION. You also have to enable flashback for session using DBMS_FLASHBACK. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED]
Re: A new form of ORA-1555
You can perform FBQ using RBSs, though this is probably not supported by Oracle, so use it at your own peril. The chances of success are diminished because of the difference in undo space management (rbs reuses space more often). I have done this several times, but in a non-production environment. Dan, I'm sure you're aware of how to prevent RBS from ever wrapping. So in theory, using FQB with standard RBS can provide snapshots ( excuse me, 'flashbacks' ) of data for an indefinite period of time. You just need a lot of disk space. :) Jared Daniel W. Fink [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/06/2003 03:20 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: A new form of ORA-1555 Mladen, That is a common misconception, one that Oracle wholeheartedly supports. Flashback Query (FBQ) depends upon the smon_scn_time table, which is popluated in 9i, regardless of the undo_management setting. This table is populated regardless of the UNDO_RETENTION parameter. This parameter is the guideline (not absolute) that Oracle uses when deciding which extents to reclaim when it needs space. If Oracle needs space, it may take extents containing committed transaction info that is still within the UNDO_RETENTION time. You can perform FBQ using RBSs, though this is probably not supported by Oracle, so use it at your own peril. The chances of success are diminished because of the difference in undo space management (rbs reuses space more often). I have done this several times, but in a non-production environment. IIRC, v$undostat is not populated unless the undo_management is set to auto. This really does not help in making the transition from rbs to aum. It would be nice to have it populated regardless so that you would have data to use to set the undo tablespace size and the value of UNDO_RETENTION. I did make one mistake. You have to enable flashback for a table or have the flashback_any_table privilege (Thanks, Kirti). Dan Gogala, Mladen wrote: Flashback query is enabled by setting the UNDO_RETENTION parameter to the desired number of seconds. Table V$UNDOSTAT contains the column MAXQUERYLEN which gives the max time that you can use to dynamically set UNDO_RETENTION. You also have to enable flashback for session using DBMS_FLASHBACK. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A new form of ORA-1555
Mladen, I could not agree more! I seriously pondered not posting this information at all. FBQ is a nice feature, but I would not depend upon it. I'm a conservative and somewhat paranoid DBA and I would not recommend AUM for production systems, though certain very knowledgable and respected members of this list disagree with me. We each have our reasons for our positions and we are both right. -- Daniel W. Fink http://www.optimaldba.com Gogala, Mladen wrote: Well, I've read a lot of that on this list (this is not the first time FBQ is being discussed) but as a conservative and somewhat paranoid DBA, I don't want to try anything that isn't supported with a very new feature like FBQ. The experience taught me a lesson about ora-600 and alike. I really wouldn't want to depend on a feature that is very, very new. I'd much rather use logminer instead. That's just me. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED]
Re: A new form of ORA-1555
we are using AUM pretty successfully for our production systems. Interestingly enough the one problem we did have was on a test box, and we couldn't repeat it --- Daniel W. Fink [EMAIL PROTECTED] wrote: Mladen, I could not agree more! I seriously pondered not posting this information at all. FBQ is a nice feature, but I would not depend upon it. I'm a conservative and somewhat paranoid DBA and I would not recommend AUM for production systems, though certain very knowledgable and respected members of this list disagree with me. We each have our reasons for our positions and we are both right. -- Daniel W. Fink http://www.optimaldba.com Gogala, Mladen wrote: Well, I've read a lot of that on this list (this is not the first time FBQ is being discussed) but as a conservative and somewhat paranoid DBA, I don't want to try anything that isn't supported with a very new feature like FBQ. The experience taught me a lesson about ora-600 and alike. I really wouldn't want to depend on a feature that is very, very new. I'd much rather use logminer instead. That's just me. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A new form of ORA-1555
Oh no, we agree. I wouldn't do automatic undo management, either. V$ROLLSTAT is perfectly good for me. I'll wait for the version 10 to intorduce AUM to my databases. On 2003.06.06 21:54 Daniel W. Fink wrote: Mladen, I could not agree more! I seriously pondered not posting this information at all. FBQ is a nice feature, but I would not depend upon it. I'm a conservative and somewhat paranoid DBA and I would not recommend AUM for production systems, though certain very knowledgable and respected members of this list disagree with me. We each have our reasons for our positions and we are both right. -- Daniel W. Fink http://www.optimaldba.com Gogala, Mladen wrote: Well, I've read a lot of that on this list (this is not the first time FBQ is being discussed) but as a conservative and somewhat paranoid DBA, I don't want to try anything that isn't supported with a very new feature like FBQ. The experience taught me a lesson about ora-600 and alike. I really wouldn't want to depend on a feature that is very, very new. I'd much rather use logminer instead. That's just me. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A new form of ORA-1555
I am using AUM in our Test/Acceptance databases with no problems at all. This month it will be rolled out to a couple of production databases. FBQ is another matter altogether :) MUM (manual undo mgmt) was a depreacted option when 9i R1 came out. I won't be surprised if only AUM would be available from the next release (Oracle10i, OracleX or whatever they call it). We will know for sure in just couple more months ([EMAIL PROTECTED] is in Sept this year :) - Kirti --- Rachel Carmichael [EMAIL PROTECTED] wrote: we are using AUM pretty successfully for our production systems. Interestingly enough the one problem we did have was on a test box, and we couldn't repeat it --- Daniel W. Fink [EMAIL PROTECTED] wrote: Mladen, I could not agree more! I seriously pondered not posting this information at all. FBQ is a nice feature, but I would not depend upon it. I'm a conservative and somewhat paranoid DBA and I would not recommend AUM for production systems, though certain very knowledgable and respected members of this list disagree with me. We each have our reasons for our positions and we are both right. -- Daniel W. Fink http://www.optimaldba.com Gogala, Mladen wrote: Well, I've read a lot of that on this list (this is not the first time FBQ is being discussed) but as a conservative and somewhat paranoid DBA, I don't want to try anything that isn't supported with a very new feature like FBQ. The experience taught me a lesson about ora-600 and alike. I really wouldn't want to depend on a feature that is very, very new. I'd much rather use logminer instead. That's just me. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
