|
|
Oracle 2011. 8. 11. 15:24
버그 속성
| 유형 |
B - Defect |
제품 버전에서 수정됨 |
- (수정된 버전 없음) |
| 중요도 |
2 - Severe Loss of Service |
제품 버전 |
10.2.0.2 |
| 상태 |
44 - Not Feasible to fix, to Filer |
플랫폼 |
46 - Linux x86 |
| 생성 날짜 |
02-Apr-2007 |
플랫폼 버전 |
LINUX AS4.0 |
| 업데이트 날짜 |
06-Aug-2008 |
기본 버그 |
- |
| 데이터베이스 버전 |
10.2.0.2 |
|
|
| 영향을 받는 플랫폼 |
Generic |
|
|
| 제품 소스 |
Oracle |
|
|
 관련 제품
| 라인 |
Oracle Database Products |
제품군 |
Oracle Database |
| 영역 |
Oracle Database |
제품 |
5 - Oracle Server - Enterprise Edition |
Hdr: 5969934 10.2.0.2 RDBMS 10.2.0.2 DATA PUMP EXP PRODID-5 PORTID-46
Abstract: EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK
*** 04/02/07 10:22 pm ***
TAR:
----
PROBLEM:
--------
1. Clear description of the problem encountered:
EXPDP client gets UDE-00008 and ORA-31626 while the server side export is
OK.
The export log from the server side looks OK.
// client console log
------------------------------
UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-6512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-6512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-6512: at line 1
------------------------------
2. Pertinent configuration information (MTS/OPS/distributed/etc)
2-node RAC
3. Indication of the frequency and predictability of the problem
100% reproducible at the customer's site.
4. Sequence of events leading to the problem
Please see attached trace files.
DIAGNOSTIC ANALYSIS:
--------------------
No error in alert log on both nodes.
The following is the output of expdp with trace=EB0300.
Deleting queue from the master table fails, same as BUG 5663241.
// expdep command line
expdp \'sys/XXX as sysdba\' trace=EB0300
DUMPFILE=work_app_dmp:20070330_expdp_err_test.dmp
FULL=y LOGFILE=work_app_dmp:20070330_expdp_err_test.log
// trace of Master Control Process
------------------------------
KUPM: 17:54:40.520: Closing job....
KUPM: 17:54:40.520: Final state in Close_job is: COMPLETED
KUPM: 17:54:40.520: dropping master since job never started
KUPM: 17:54:40.520: keep_master_flag = FALSE
KUPM: 17:54:40.520: Delete files = FALSE
KUPM: 17:54:40.520: File subsystem shutdown
KUPM: 17:54:40.520: In set_longops
KUPM: 17:54:40.521: Work so far is: 66.80495548248291015625
KUPM: 17:54:40.521: Master delete flag is: TRUE
KUPV: 17:54:40.521: Delete request for job: SYS.SYS_EXPORT_FULL_01
KUPV: 17:54:40.535: Deleting FT job entry
KUPV: 17:54:40.535: Deleting queues
KUPC: 17:54:40.966: Deleted queue SYS.KUPC$C_1_20070330174720.
KUPC: 17:54:40.968: Error Code: -24018
KUPC: 17:54:40.968: Error Text: deleteQueue: ORA-24018: STOP_QUEUE on
SYS.KUPC$S_1_20070330174720 failed, outstanding transactions found
KUPV: 17:54:40.968: Delete request for MT: SYS.SYS_EXPORT_FULL_01
*** 17:54:51.426
KUPM: 17:54:51.426: Fixed views cleaned up
KUPM: 17:54:51.427: Log file is closed.
KUPV: 17:54:51.427: Detach request
KUPV: 17:54:51.434: Deleting FT att entry
------------------------------
Importing a table (TABLES=test_tbl_2) with the dump file
(20070330_expdp_err_test.dmp)
is successfully done, which means that the export is OK regarding the table
(test_tbl_2).
// impdp command line
impdp \'sys/XXX as sysdba\' DUMPFILE=work_app_dmp:20070330_expdp_err_test.dmp
TABLES=test_tbl_2 LOGFILE=work_app_dmp:20070330_impdp_test.log
WORKAROUND:
-----------
None.
RELATED BUGS:
-------------
BUG 5416274
BUG 5663241 (duplicate bug of BUG 5416274)
REPRODUCIBILITY:
----------------
reproduced twice at customer's site.
CT says every time expdp was done, this phenomenon occured.
TEST CASE:
----------
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
I will be loading the following files:
dbram01_dm00_19485.trc
dbram01_dw01_19569.trc
expdp.txt
imp.txt
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 04/02/07 10:26 pm ***
*** 04/02/07 10:44 pm ***
*** 04/02/07 10:45 pm ***
*** 04/10/07 01:09 am ***
*** 04/10/07 01:09 am ***
*** 04/10/07 01:20 am ***
*** 04/10/07 01:20 am *** (CHG: Sta->11)
*** 04/10/07 01:20 am ***
*** 04/10/07 01:20 am ***
*** 05/06/07 10:57 pm ***
*** 05/22/07 05:02 pm ***
*** 05/31/07 11:52 pm ***
*** 06/13/07 06:39 pm ***
*** 06/18/07 02:38 am ***
*** 06/18/07 02:38 am ***
*** 06/18/07 02:38 am ***
*** 06/20/07 03:41 am ***
*** 06/20/07 03:50 am ***
*** 07/16/07 06:48 pm ***
*** 07/24/07 06:48 pm ***
*** 08/02/07 09:40 pm ***
*** 08/21/07 06:45 pm ***
*** 09/25/07 07:46 pm ***
*** 10/15/07 06:39 pm ***
*** 11/02/07 05:06 pm ***
*** 11/15/07 01:48 am ***
*** 03/06/08 09:52 pm ***
*** 03/20/08 08:17 am ***
*** 03/20/08 08:17 am ***
*** 05/13/08 11:26 am ***
*** 07/08/08 06:35 am ***
*** 08/06/08 08:35 am ***
*** 08/06/08 09:21 am *** (CHG: Sta->44)
*** 08/06/08 09:21 am ***
In the 10.2.0.2 release, there were a number of problems that caused the
expdp and impdp clients to exit prematurely, interpreting a nonfatal error as
a fatal one, giving the appearance that the job had failed when it hadn't. In
fact, inspection of the log file, if one was specified for the job, showed
that the job ran successfully to completion. Often a trace file written by
one of the Data Pump processes would provide more detail on the error that
had been misinterpreted as a fatal one. Many of these errors involved the
queues used for communication between the Data Pump processes, but there were
other issues as well.
With each subsequent release, these problems have been addressed, and the
client has become more robust and rarely, if ever, runs into situations like
this. However, this is the result of dozens of bug fixes in subsequent
releases, some in Data Pump and some in supporting layers. It's impossible to
know, at this point, what combination of bug fixes would address this
specific failure, and even if that was possible, it wouldn't address other
possible failures that look very similar on the client side.
Relying on information in the log file is one way to verify that the job
actually completed successfully. Problems like this one became much more
intermittent by the 10.2.0.4 and 10.2.0.5 releases and are rarely, if ever,
seen in 11.1 or later.
출처 : Oracle Support
Oracle 2011. 8. 11. 15:19
| 수정 날짜 29-APR-2010 유형 PROBLEM 상태 PUBLISHED |
|
Applies to:Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
SymptomsDatapump export from the client side completes with errors. Example:
expdp system/password full=y dumpfile=full_exp.dmp logfile=full_explog
...
. . exported "CALYPSO"."KICKOFF_CFG" 0 KB 0 rows
. . exported "CALYPSO"."LE_AGR_CHILD" 0 KB 0 rows
. . exported "CALYPSO"."LE_ATTR_CODE" 0 KB 0 rows
UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1
However, reviewing the logfile shows that the "job successfully completed"
CauseThis issue has been discussed in Bug 5969934 EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK
SolutionThe expdp client makes calls to DBMS_DATAPUMP package to start and monitor export job. Once the export job is underway, the client just monitors the job status by issuing DBMS_DATAPUMP.GET_STAUS.
Therefore, if the export logfile says "job successfully completed", the dump file generated by the job should be fine.
You can simply ignore the errors, since the dump file is still valid for an import.
In the 10.2.0.2 release, there were a number of problems that caused the expdp and impdp clients to exit prematurely, interpreting a nonfatal error as a fatal one, giving the appearance that the job had failed when it hadn't. In fact, inspection of the log file, if one was specified for the job, showed that the job ran successfully to completion. Often a trace file written by one of the Data Pump processes would provide more detail on the error that had been misinterpreted as a fatal one. Many of these errors involved the queues used for communication between the Data Pump processes, but there were other issues as well.
.
With each subsequent release, these problems have been addressed, and the client has become more robust and rarely, if ever, runs into situations like this. However, this is the result of many bug fixes in subsequent releases, some in Data Pump and some in supporting layers. It's impossible to know, at this point, what combination of bug fixes would address this specific failure, and even if that was possible, it wouldn't address other possible failures that look very similar on the client side.
.
Relying on information in the log file is one way to verify that the job actually completed successfully. Problems like this one became much more intermittent by the 10.2.0.4 release and are rarely, if ever, seen in 11.1 or later.
ReferencesBUG:5969934 - EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK
|
관련 자료
|
출처 : Oracle Support
Data Pump에 생각보다 많은 Bug들이 있다. 하지만, 성능상으로는 이런 Bug들을 감수할만큼 효율적이니 잘 피해서 사용해보자.
Oracle 2011. 8. 11. 11:43
버그 속성
| 유형 |
B - Defect |
제품 버전에서 수정됨 |
11.1 |
| 중요도 |
2 - Severe Loss of Service |
제품 버전 |
10.2.0.2.0 |
| 상태 |
80 - Development to Q/A |
플랫폼 |
212 - IBM AIX on POWER Systems (64-bit) |
| 생성 날짜 |
12-Jul-2006 |
플랫폼 버전 |
- |
| 업데이트 날짜 |
16-Mar-2011 |
기본 버그 |
- |
| 데이터베이스 버전 |
10.2.0.2.0 |
|
|
| 영향을 받는 플랫폼 |
Generic |
|
|
| 제품 소스 |
Oracle |
|
|
관련 제품
| 라인 |
Oracle Database Products |
제품군 |
Oracle Database |
| 영역 |
Oracle Database |
제품 |
5 - Oracle Server - Enterprise Edition |
Hdr: 5387030 10.2.0.2.0 RDBMS 10.2.0.2.0 TXN MGMT LOCAL PRODID-5 PORTID-212
Abstract: AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
*** 07/12/06 08:56 am REQUEST TEXT ***
So when:
a) not using GUARANTEED retention at undo tablespace level and
b) not using auto-extensible data files (fixed sized data file). then the
tuned_undoretention stays high to satisfy the maxquerylen at that time and
causes out of undo space error. The tuned_undoretention value did not return
to
a lower value even after 36 hours of quiet time (not a single query was
submitted).
Then, we changed undo tablespace's data files to be auto_extensible, but did
not allow it to actually extend any further. The max size was set to the
current allocated size.
With this change the tuned_undoretention returned to the set value for und
o_retention parameter immediately after the long running query completed.
This is same behavior we have seen in 10g R1 without the need for
auto_extensible data files.
And that is the difference in 10g R2 from R1. This change forces the bogus
use
of auto_extensible option.
I will upload a text file for you to review in support of what I mentioned
here.
*** 07/12/06 08:56 am BUSINESS NEED ***
It is causes confusion and misleading to customer needs.
So, this workaround is not a big deal, but 10g R2 is not backward compatible
to 10g R1 in this respect. And is not clearly documented as such. If there is
no chance to change this to match what 10g R1did, then I think a Metalink
Note to that effect will clear things up for all who may run into this
issue.
*** 07/12/06 08:57 am RESPONSE ***
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_TBS
SQL>
SQL> alter session set nls_date_format='mm/dd/yy hh24:mi:ss';
Session altered.
SQL> set lines 132
SQL> get f1.sql
1 select begin_time
2 , end_time
3 , undoblks
4 , tuned_undoretention
5 , maxquerylen
6 , maxqueryid
7* from v$undostat
SQL> /
BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
MAXQUERYLEN MAXQUERYID
----------------- ----------------- ---------- -------------------
----------- -------------
07/05/06 14:32:23 07/05/06 14:37:25 1 900
0
07/05/06 14:22:23 07/05/06 14:32:23 3 900
0
07/05/06 14:12:23 07/05/06 14:22:23 4 900
0
07/05/06 14:02:23 07/05/06 14:12:23 2 900
0
07/05/06 13:52:23 07/05/06 14:02:23 50 900
0
07/05/06 13:42:23 07/05/06 13:52:23 5 900
0
07/05/06 13:32:23 07/05/06 13:42:23 2 900
0
07/05/06 13:22:23 07/05/06 13:32:23 4 900
0
07/05/06 13:12:23 07/05/06 13:22:23 6 900
0
07/05/06 13:02:23 07/05/06 13:12:23 0 900
0
07/05/06 12:52:23 07/05/06 13:02:23 63 900
0
BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
MAXQUERYLEN MAXQUERYID
----------------- ----------------- ---------- -------------------
----------- -------------
07/05/06 12:42:23 07/05/06 12:52:23 265 1800
962 271b023vduant
07/05/06 12:32:23 07/05/06 12:42:23 449 1201
362 271b023vduant
07/05/06 12:22:23 07/05/06 12:32:23 404 2948
2108 271b023vduant
07/05/06 12:12:23 07/05/06 12:22:23 372 2348
1506 271b023vduant
07/05/06 12:02:23 07/05/06 12:12:23 353 1747
906 271b023vduant
07/05/06 11:52:23 07/05/06 12:02:23 513 1421
582 271b023vduant
07/05/06 11:42:23 07/05/06 11:52:23 169 1005
164 fby3wmzn3hzvw
07/05/06 11:32:23 07/05/06 11:42:23 3 900
0
07/05/06 11:22:23 07/05/06 11:32:23 2 900
0
07/05/06 11:12:23 07/05/06 11:22:23 11 900
0
07/05/06 11:02:23 07/05/06 11:12:23 4 900
0
*** 07/12/06 08:58 am RESPONSE ***
SQL> get f2.sql
1 select tablespace_name
2 , retention
3 from dba_tablespaces
4* where tablespace_name = 'UNDO_TBS'
SQL> /
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDO_TBS NOGUARANTEE
SQL> col file_name for a45
SQL>
SQL> get f3.sql
SQL> get f3.sql
1 select file_name
2 , autoextensible
3 , bytes/1048576 MB
4 , increment_by
5 , maxbytes/1048576 Max_MB
6 from dba_data_files
7* where tablespace_name = 'UNDO_TBS'
SQL> /
FILE_NAME AUT MB INCREMENT_BY
MAX_MB
--------------------------------------------- --- ---------- ------------
----------
/u10/oradata/VZCT/undo_tbs_02.dbf YES 3000 128
3000
/u11/oradata/VZCT/undo_tbs_01.dbf YES 2000 128
2000
/u13/oradata/VZCT/undo_tbs_03.dbf YES 1000 128
1000
*** 07/26/06 02:54 pm *** (CHG: Sta->11 Asg->NEW OWNER)
*** 07/27/06 12:09 pm ***
*** 07/28/06 02:22 pm ***
*** 07/28/06 02:22 pm ***
*** 08/16/06 10:17 am ***
*** 08/16/06 02:36 pm *** ESCALATED
*** 08/16/06 02:36 pm ***
*** 08/21/06 10:24 am ***
*** 08/21/06 02:17 pm ***
*** 08/21/06 02:17 pm ***
*** 08/21/06 04:07 pm *** (CHG: Sta->30)
*** 08/21/06 04:07 pm ***
*** 09/18/06 09:12 am *** (CHG: Sta->16)
*** 09/18/06 09:12 am ***
*** 09/18/06 09:49 am *** (CHG: Sta->11)
*** 09/18/06 01:15 pm *** (CHG: Sta->30)
*** 09/18/06 01:15 pm ***
*** 09/19/06 08:13 am *** (CHG: Sta->16)
*** 09/19/06 08:13 am ***
*** 09/19/06 10:56 am *** (CHG: Sta->30)
*** 09/19/06 10:56 am ***
*** 09/25/06 08:25 am *** (CHG: Sta->11)
*** 09/25/06 08:25 am ***
*** 09/26/06 06:30 am ***
*** 09/26/06 02:33 pm ***
*** 09/26/06 02:39 pm ***
*** 09/26/06 02:43 pm *** (CHG: Sta->30)
*** 09/26/06 02:43 pm ***
*** 09/28/06 04:12 pm ***
TAR: 5337754.992
----
PROBLEM:
--------
1) Unused Undo segments are not being release, originally the database was
running on 10.1 without problem, but after upgrade to release 10.2.0.2 the
problem started.
2) UNDO_RETENTION parameter was correctly set = 900
3) Undo Tablespace was fixed sized, in other word the Undo tablespace was not
enabled with Autoextensible option for the associated datafiles.
4) Also, the Undo tablespace was never enabled with the guaranty retention
option.
5) So, after upgrade to 10.2 the problem is the TUNED_UNDORETENTION stayed
very high causing UNDO_TABLESPACE run out of disk space and it is not
releasing the unused undo segments.
6) The Oracle Undo Advisor is always asking to increase the Undo tablespace
(e.g. 17032 MB) which is not acceptable.
7) TUNED_UNDORETENTION column (v$undostat) is always increasing and never
returning back to 900 as on 10gR1:
=============================================================
SQL> get f1.sql
1 select begin_time, end_time, undoblks, tuned_undoretention, maxquerylen,
maxqueryid
2* from v$undostat
SQL> /
BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
MAXQUERYLEN MAXQUERYID
----------------- ----------------- ---------- -------------------
----------- -------------
04/15/06 12:37:38 04/15/06 12:40:00 1443 61233
36031 gf37j3d3q2h15
04/15/06 12:27:38 04/15/06 12:37:38 6285 61261
36031 gf37j3d3q2h15
04/15/06 12:17:38 04/15/06 12:27:38 5450 60206
35430 gf37j3d3q2h15
04/15/06 12:07:38 04/15/06 12:17:38 6348 60110
34830 gf37j3d3q2h15
04/15/06 11:57:38 04/15/06 12:07:38 5005 60094
34229 gf37j3d3q2h15
04/15/06 11:47:38 04/15/06 11:57:38 5440 59954
33628 gf37j3d3q2h15
04/15/06 11:37:38 04/15/06 11:47:38 6662 58994
33028 gf37j3d3q2h15
04/15/06 11:27:38 04/15/06 11:37:38 5197 58995
32427 gf37j3d3q2h15
04/15/06 11:17:38 04/15/06 11:27:38 5597 59184
31826 gf37j3d3q2h15
04/15/06 11:07:38 04/15/06 11:17:38 6035 59943
31225 gf37j3d3q2h15
04/15/06 06:47:38 04/15/06 06:57:38 5185 56068
15607 gf37j3d3q2h15
04/15/06 06:37:38 04/15/06 06:47:38 5002 54669
15007 gf37j3d3q2h15
04/15/06 06:27:38 04/15/06 06:37:38 4905 54491
14406 gf37j3d3q2h15
04/13/06 21:57:38 04/13/06 22:07:38 3474 345600
213 0mdbrtbvdd9gu
04/13/06 21:47:38 04/13/06 21:57:38 25 345600
0
04/13/06 21:37:38 04/13/06 21:47:38 22 345600
0
04/13/06 21:27:38 04/13/06 21:37:38 96 345600
0
236 rows selected.
=============================================================
*** 09/28/06 04:12 pm ***
DIAGNOSTIC ANALYSIS:
--------------------
1) This behavior was diagnosed through the v$undostat view (OWC session)
2) Please check the undo_retention_too_much.txt log file.
WORKAROUND:
-----------
None.
RELATED BUGS:
-------------
None
REPRODUCIBILITY:
----------------
It is always reproducible in the customers environment.
TEST CASE:
----------
None
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
Analyst: Esteban Bernal
AIM: ebernalus
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 09/28/06 04:13 pm *** (CHG: Sta->16)
*** 09/28/06 04:19 pm ***
*** 09/28/06 04:31 pm *** (CHG: Sta->11)
*** 09/28/06 08:10 pm *** (CHG: Sta->30)
*** 09/28/06 08:10 pm ***
*** 09/29/06 06:34 am *** (CHG: Sta->11)
*** 09/29/06 06:34 am ***
*** 09/29/06 05:05 pm *** (CHG: Sta->30)
*** 09/29/06 05:05 pm ***
*** 10/02/06 02:21 pm *** (CHG: Sta->11)
*** 10/02/06 02:21 pm ***
*** 10/02/06 04:29 pm *** (CHG: Sta->30)
*** 10/02/06 04:29 pm ***
*** 10/03/06 10:59 am *** (CHG: Sta->11)
*** 10/03/06 10:59 am ***
*** 10/03/06 11:19 am ***
*** 10/03/06 12:27 pm ***
*** 10/03/06 01:52 pm ***
*** 10/03/06 01:52 pm ***
*** 10/03/06 04:00 pm ***
*** 10/06/06 11:56 am ***
*** 10/06/06 12:13 pm ***
*** 10/06/06 12:13 pm ***
*** 10/09/06 06:11 am ***
*** 10/10/06 08:42 am ***
*** 10/12/06 06:04 am ***
*** 10/16/06 11:15 am ***
*** 10/18/06 12:01 pm ***
*** 10/23/06 07:59 am ***
*** 10/23/06 10:58 am ***
*** 10/26/06 04:43 pm ***
*** 10/26/06 06:28 pm ***
*** 11/01/06 12:19 pm ***
*** 11/09/06 03:54 pm ***
*** 11/13/06 03:52 pm ***
*** 11/14/06 11:19 am ***
*** 11/15/06 08:43 am ***
*** 11/15/06 08:43 am ***
*** 11/17/06 02:57 pm *** (CHG: Sta->30)
*** 11/17/06 02:57 pm ***
*** 11/17/06 04:09 pm ***
*** 11/27/06 11:04 am ***
*** 11/27/06 11:05 am *** (CHG: Sta->11)
*** 11/27/06 11:05 am ***
*** 11/29/06 09:45 am ***
*** 11/30/06 06:52 am ***
*** 11/30/06 06:56 am ***
*** 11/30/06 07:36 am ***
*** 11/30/06 12:36 pm ***
*** 12/04/06 03:26 pm ***
*** 12/04/06 05:21 pm ***
*** 12/05/06 02:15 pm ***
*** 12/05/06 02:16 pm *** (CHG: Sta->30)
*** 12/05/06 02:49 pm ***
*** 12/05/06 02:50 pm *** (CHG: Sta->11)
*** 12/05/06 02:50 pm ***
*** 12/05/06 03:52 pm *** (CHG: Sta->30)
*** 12/05/06 03:52 pm ***
*** 12/06/06 07:35 am *** (CHG: Sta->11)
*** 12/06/06 07:35 am ***
*** 12/06/06 08:25 am *** (CHG: Sta->30)
*** 12/06/06 08:25 am ***
*** 12/06/06 03:12 pm ***
*** 12/06/06 03:15 pm *** (CHG: Sta->11)
*** 12/06/06 03:15 pm ***
*** 12/06/06 04:09 pm ***
*** 12/06/06 04:34 pm ***
*** 12/07/06 10:05 am ***
*** 12/07/06 05:47 pm *** (CHG: Sta->30)
*** 12/07/06 05:47 pm ***
*** 12/08/06 07:22 am *** (CHG: Sta->11)
*** 12/08/06 07:23 am ***
*** 12/08/06 10:20 am *** (CHG: Sta->30)
*** 12/08/06 10:20 am ***
*** 12/08/06 12:09 pm *** (CHG: Sta->11)
*** 12/08/06 12:09 pm ***
*** 12/11/06 04:29 pm ***
*** 12/12/06 07:22 am ***
*** 12/14/06 11:37 am ***
*** 12/21/06 04:56 pm ***
*** 12/22/06 04:06 pm ***
*** 01/02/07 06:18 am ***
*** 01/02/07 04:25 pm ***
*** 01/03/07 03:48 pm ***
*** 01/03/07 04:53 pm ***
*** 01/04/07 04:22 pm ***
*** 01/08/07 06:49 am ***
*** 01/11/07 04:01 pm ***
*** 01/12/07 12:28 pm ***
*** 01/12/07 11:02 pm ***
REDISCOVERY INFORMATION:
Look for:
- System Managed Undo
undo_management=auto in init.ora
- Fixed size undo tablespace
select autoextensible from dba_data_files
where tablespace_name='<current_undo_ts>'
returns "No" for all the undo ts datafiles.
- The undo tablespace is already sized such that
it always has more than enough space to store
all the undo generated within the undo_retention
time, and the in-use undo space never exceeds the
undo tablespace warning alert threshold (see
below for the query to show the thresholds).
- The tablespace threshold alerts recommend that
the DBA add more space to the undo tablespace:
select creation_time, metric_value, message_type,
reason, suggested_action
from dba_outstanding_alerts
where object_name='<current_undo_ts>';
returns a suggested action of:
"Add space to the tablespace"
Or, this recommendation has been reported in the
past but the condition has now cleared:
select creation_time, metric_value, message_type,
reason, suggested_action, resolution
from dba_alert_history
where object_name='<current_undo_ts>';
- The undo tablespace in-use space exceeded the
warning alert threshold at some point in time:
To see the warning alert percentage threshold:
select object_type, object_name,
warning_value, critical_value
from dba_thresholds
where object_type='TABLESPACE';
To see the (current) undo tablespace percent of
space in-use:
select
((select (nvl(sum(bytes),0))
from dba_undo_extents
where tablespace_name='<current_undo_ts>'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name='<current_undo_ts>')
"PCT_INUSE"
from dual;
WORKAROUND:
There are 3 possible alternate workarounds (any one of
these should resolve the problem of the alerts triggering
unnecessarily):
1. Set the autoextend and maxsize attribute of each datafile
in the undo ts so it is autoextensible and its maxsize is
equal to its current size so the undo tablespace now has
the autoextend attribute but does not autoend:
alter database datafile '<datafile_flename>'
autoextend on maxsize <current_size>;
With this setting, v$undostat.tuned_undoretention is not
calculated based on a percentage of the undo tablespace
size, instead v$undostat.tuned_undoretention is set to
the maximum of:
maxquerylen secs + 300
undo_retention specified in init.ora
2. Set the following hidden parameter:
in init.ora: _smu_debug_mode=33554432
or use: alter system set "_smu_debug_mode" = 33554432;
With this setting, v$undostat.tuned_undoretention is not
calculated based on a percentage of the fixed size undo
tablespace, instead v$undostat.tuned_undoretention is
set to the maximum of:
maxquerylen secs + 300
undo_retention specified in init.ora
3. Set the following hidden parameter:
in init.ora: _undo_autotune = false
or use: alter system set "_undo_autotune" = false;
With this setting, v$undostat (and therefore
v$undostat.tuned_undoretention) is not maintained and
and the undo_retention used is the one specified in
init.ora
RELEASE NOTES:
]]In 10.2, for a non-autoextend SMU undo tablespace which
]]is already adequately sized to handle the transaction
]]workload for the specified undo_retention time, the
]]tablespace threshold alert mechanism may still recommend
]]that the DBA add more space to the undo tablespace.
*** 01/12/07 11:20 pm ***
*** 01/12/07 11:20 pm *** (CHG: Sta->80)
*** 01/15/07 03:53 am ***
*** 01/16/07 04:21 am ***
*** 01/16/07 06:28 am ***
*** 01/16/07 08:24 am ***
*** 01/16/07 11:18 am ***
*** 01/16/07 02:06 pm ***
*** 01/16/07 02:07 pm ***
*** 01/16/07 02:07 pm ***
*** 01/16/07 03:56 pm ***
*** 01/16/07 10:24 pm ***
*** 01/17/07 06:42 am ***
*** 01/17/07 08:45 am ***
*** 01/17/07 11:22 am ***
*** 01/17/07 07:17 pm ***
*** 01/17/07 07:19 pm ***
*** 01/19/07 11:25 am ***
*** 01/19/07 12:40 pm ***
*** 01/19/07 02:10 pm ***
*** 01/23/07 07:50 am ***
*** 01/23/07 01:01 pm *** (CHG: Sta->16)
*** 01/23/07 01:01 pm ***
*** 01/23/07 04:36 pm *** (CHG: Sta->30)
*** 01/23/07 04:36 pm ***
*** 01/24/07 08:07 am ***
*** 01/24/07 08:57 am ***
*** 01/24/07 08:58 am *** (CHG: Sta->11)
*** 01/24/07 08:58 am ***
*** 01/24/07 02:21 pm ***
*** 01/24/07 03:32 pm ***
*** 01/25/07 02:44 pm ***
*** 01/25/07 02:44 pm *** (CHG: Sta->80)
*** 01/25/07 02:44 pm ***
*** 02/20/07 05:57 am ***
*** 02/21/07 02:55 am ***
*** 02/23/07 05:04 am ***
*** 03/05/07 07:18 am ***
*** 03/05/07 07:23 am ***
*** 03/06/07 12:13 am ***
*** 03/12/07 07:14 am ***
*** 04/11/07 08:11 pm ***
*** 04/11/07 08:13 pm ***
*** 04/13/07 04:19 am ***
*** 04/13/07 04:20 am ***
*** 04/13/07 04:23 am ***
*** 04/26/07 05:03 pm ***
*** 04/26/07 05:30 pm ***
*** 05/02/07 08:14 am ***
*** 05/03/07 12:40 am ***
*** 05/03/07 12:42 am ***
*** 07/05/07 04:19 pm ***
*** 07/06/07 05:24 pm ***
*** 07/12/07 05:21 pm ***
*** 09/14/07 02:22 pm ***
*** 09/17/07 11:45 pm ***
*** 09/17/07 11:47 pm ***
*** 09/18/07 10:29 am ***
*** 09/28/07 04:02 pm ***
*** 10/09/07 03:41 pm ***
*** 10/09/07 03:43 pm ***
*** 10/18/07 02:12 pm ***
*** 10/18/07 10:10 pm ***
*** 10/18/07 10:12 pm ***
*** 11/21/07 08:54 am ***
*** 11/21/07 01:01 pm ***
*** 11/21/07 01:01 pm ***
*** 11/30/07 02:59 am ***
*** 11/30/07 12:09 pm ***
*** 12/02/07 12:50 am ***
*** 12/11/07 05:30 am ***
*** 12/11/07 09:25 am ***
*** 02/10/08 10:04 pm ***
*** 04/08/08 12:55 pm ***
*** 04/09/08 12:51 am ***
*** 04/17/08 03:49 am ***
*** 04/17/08 03:49 am ***
*** 04/17/08 04:06 am ***
*** 04/17/08 04:06 am ***
*** 04/17/08 10:57 pm ***
*** 04/17/08 11:08 pm ***
*** 04/17/08 11:08 pm ***
*** 04/18/08 03:55 am ***
*** 04/18/08 03:55 am ***
*** 04/18/08 07:55 am ***
*** 04/18/08 02:01 pm ***
*** 04/18/08 02:01 pm ***
*** 04/18/08 02:33 pm ***
*** 04/21/08 12:58 am ***
*** 04/24/08 04:40 am ***
*** 04/24/08 04:49 am ***
*** 04/24/08 04:54 am ***
*** 04/28/08 05:53 am ***
*** 04/28/08 11:43 am ***
*** 04/28/08 12:32 pm *** (CHG: Sta->11)
*** 04/28/08 12:32 pm ***
*** 04/28/08 01:13 pm ***
*** 04/28/08 01:13 pm *** (CHG: Sta->80)
*** 04/28/08 01:13 pm ***
*** 04/28/08 11:03 pm ***
*** 05/05/08 06:32 am ***
*** 05/19/08 03:41 am ***
*** 05/19/08 06:47 am ***
*** 05/19/08 06:47 am ***
*** 05/21/08 11:44 pm ***
*** 05/22/08 10:41 am ***
*** 05/22/08 06:58 pm ***
*** 05/22/08 06:58 pm ***
*** 06/12/08 05:16 am ***
64-bit) for base bug 7145055
*** 06/12/08 06:35 am ***
*** 06/12/08 06:39 am ***
bug 7145055
*** 06/12/08 06:43 am ***
*** 06/12/08 06:46 am ***
base bug 7145055
*** 07/21/08 01:36 am ***
base bug 7117498
*** 07/25/08 04:54 pm ***
*** 08/08/08 02:17 am ***
base bug 7285182
*** 08/11/08 12:47 pm ***
*** 08/19/08 07:34 am ***
*** 08/20/08 06:16 pm ***
*** 08/22/08 06:39 am ***
7342997
*** 08/29/08 02:40 pm ***
*** 09/18/08 04:37 am ***
*** 10/02/08 11:57 pm ***
*** 12/03/08 01:24 am ***
bug 5471994 bug 4367986 bug 6085625 bug 5648872 bug 5863277 bug 5177766
bug 5254759 bug 6452485
*** 01/15/09 06:03 am ***
*** 01/27/09 11:23 pm ***
*** 02/25/09 10:59 pm ***
*** 03/24/09 02:03 am ***
bug 5476873 bug 5599293 bug 5922239
*** 03/27/09 12:19 pm ***
*** 04/09/09 03:23 am ***
*** 04/21/09 08:57 pm ***
*** 04/21/09 08:57 pm ***
*** 04/21/09 08:57 pm ***
*** 04/21/09 08:58 pm ***
*** 04/21/09 08:58 pm ***
*** 06/02/09 09:08 am ***
bug 5476873 bug 5599293 bug 5922239 bug 5081798 bug 5057695
*** 06/23/09 04:27 am ***
bug 5476873 bug 5599293 bug 5922239 bug 5010657 bug 6859515
*** 07/09/09 09:16 am ***
*** 09/08/09 01:46 am ***
*** 01/28/10 09:21 am ***
*** 01/28/10 01:21 pm ***
*** 01/29/10 08:31 am ***
*** 01/29/10 08:32 am ***
*** 01/30/10 01:58 am ***
*** 02/03/10 03:09 pm ***
*** 02/03/10 03:09 pm ***
*** 04/02/10 08:30 am ***
*** 07/01/10 05:38 pm ***
*** 08/30/10 03:03 pm ***
*** 09/15/10 01:08 am ***
*** 11/09/10 07:39 am ***
*** 12/15/10 02:05 pm ***
*** 12/19/10 11:21 pm ***
*** 12/20/10 03:34 am ***
*** 12/21/10 10:47 pm ***
*** 12/21/10 10:48 pm ***
*** 12/29/10 07:14 am ***
*** 01/03/11 10:53 pm ***
*** 01/04/11 01:08 am ***
*** 01/11/11 12:19 am ***
*** 01/26/11 02:47 am ***
*** 03/03/11 09:15 am ***
*** 03/16/11 05:26 pm ***
|
