|
Master Note: Troubleshooting guide for Automatic Undo Management (Doc ID 1579081.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.1.0.1 [Release 9.2 to 12.1]
Information in this document applies to any platform.
PURPOSE
This is a Master Note for troubleshooting the various issues reported on Undo Management. This document provides a brief explanation for the various issues and the list of diagnostic information to be collected before raising a Service Request with Oracle Support.
這是一個(gè)主要說(shuō)明，用于對(duì)Undo Management中報(bào)告的各種問(wèn)題進(jìn)行故障排除。本文檔簡(jiǎn)要說(shuō)明了各種問(wèn)題，并在通過(guò)Oracle Support提出服務(wù)請(qǐng)求之前要收集的診斷信息列表。
TROUBLESHOOTING STEPS
There are various Undo related issues reported. Refer : Assistant: Get Assistance to Understand and Solve Oracle Undo Management issues (Doc ID 1575667.2)
報(bào)告了各種與Undo相關(guān)的問(wèn)題。請(qǐng)參閱：Assistant: Get Assistance to Understand and Solve Oracle Undo Management issues (Doc ID 1575667.2)
1- Please provide the following diagnostic information if the issue persists: 如果問(wèn)題仍然存在，請(qǐng)?zhí)峁┮韵略\斷信息：
a. Undo parameters Undo參數(shù)
select nam.ksppinm NAME, val.KSPPSTVL VALUE
from x$ksppi nam, x$ksppsv val
where nam.indx = val.indx
and (nam.ksppinm like '%undo%' or
nam.ksppinm in ('_first_spare_parameter', '_smu_debug_mode'))
order by 1;
示例：
col NAME for a35
col VALUE for a50
select nam.ksppinm NAME, val.KSPPSTVL VALUE
from x$ksppi nam, x$ksppsv val
where nam.indx = val.indx
and (nam.ksppinm like '%undo%' or
nam.ksppinm in ('_first_spare_parameter', '_smu_debug_mode'))
order by 1;
NAME VALUE
----------------------------------- --------------------------------------------------
_collect_undo_stats TRUE
_disable_undo_tablespace_alerts FALSE
_enable_default_undo_threshold TRUE
_first_spare_parameter
_flush_undo_after_tx_recovery TRUE
_gc_undo_affinity TRUE
_gc_undo_block_disk_reads TRUE
_highthreshold_undoretention 4294967294
_in_memory_undo TRUE
_lm_spare_undo 0
_optimizer_undo_changes FALSE
_optimizer_undo_cost_change 11.2.0.4
_smon_undo_seg_rescan_limit 10
_smu_debug_mode 0
_undo_autotune TRUE
_undo_block_compression TRUE
_undo_debug_mode 0
_undo_debug_usage 0
_verify_undo_quota FALSE
undo_management AUTO
undo_retention 900
undo_tablespace UNDOTBS1
22 rows selected.
b. What are the various statuses for Undo Extents? Undo Extents的各種狀態(tài)是什么？
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
示例：
SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
STATUS MB COUNT(*)
--------- ---------- ----------
ACTIVE 2162.25 408
EXPIRED 18233.75 16146
UNEXPIRED 27083.25 21389
c. Tuned Retention 調(diào)整保留
SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT) FROM V$UNDOSTAT;
SELECT BEGIN_TIME, END_TIME, TUNED_UNDORETENTION, MAXQUERYLEN, MAXQUERYID, NOSPACEERRCNT, EXPSTEALCNT, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT;
示例：
SQL> SELECT BEGIN_TIME, END_TIME, TUNED_UNDORETENTION, MAXQUERYLEN, MAXQUERYID, NOSPACEERRCNT, EXPSTEALCNT, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT;
BEGIN_TIME END_TIME TUNED_UNDORETENTION MAXQUERYLEN MAXQUERYID NOSPACEERRCNT EXPSTEALCNT UNDOBLKS TXNCOUNT
----------------- ----------------- ------------------- ----------- ------------- ------------- ----------- ---------- ----------
20191129 15:22:20 20191129 15:27:42 1420 699 0rc4km05kgzb9 0 0 0 4
20191129 15:12:20 20191129 15:22:20 1118 397 0rc4km05kgzb9 0 0 1 83
20191129 15:02:20 20191129 15:12:20 1717 997 0rc4km05kgzb9 0 0 0 13
... ...
20191129 09:42:20 20191129 09:52:20 1795 953 0rc4km05kgzb9 0 3 129 654
20191129 09:32:20 20191129 09:42:20 1190 348 0rc4km05kgzb9 0 10 5446 540
36 rows selected.
d. The size details and auto-extend setting for the UNDO Tablespace UNDO表空間的大小詳細(xì)信息和自動(dòng)擴(kuò)展設(shè)置
COL AUTOEXTENSIBLE FORMAT A14
SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='&UNDOTBS';
示例：
SQL> COL AUTOEXTENSIBLE FORMAT A14
SQL> SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='&UNDOTBS';
Enter value for undotbs: UNDOTBS1
FILE_ID BYTES (MB) MAXBYTES (MB) AUTOEXTENSIBLE
---------- ---------- ------------- --------------
3 70 32767.9844 YES
e. Upload the alert log file from the startup.
2- Provide the query outputs from the "diagnostic information" section of Doc ID 1579081.1"
提供來(lái)自Doc ID 1579081.1的"diagnostic information"部分的查詢(xún)輸出
|
