所有由小潘发布的文章

喜欢分享的小潘.

AUM 常用分析/诊断脚本 (文档 ID 1526122.1)

AUM 常用分析/诊断脚本 (文档 ID 1526122.1)

Oracle Database – Enterprise Edition – 版本 10.1.0.5 到 11.1.0.6 [发行版 10.1 到 11.1]

本文档所含信息适用于所有平台

用途

本文旨在规范对用以诊断和分析 ORA-1555 错误的脚本的使用。本文适用于所有数据库管理员及 Oracle Support 分析人员。

要求

以下脚本可在 SQL*Plus 或 iSQL*Plus 中运行。很多脚本都要求拥有数据库的 DBA 权限。

单击 此处 [修订时间:2011 年 5 月 12 日] 下载本文中讨论的脚本。

配置

查看每个脚本的备注以判定对于特定配置/应用程序环境是否提示有所更改。

说明

对于以下脚本,需要使用能够访问 DBA* 和 V$ 表的数据库管理用户。默认情况下,这些脚本登录形式为:

$ sqlplus /nolog

connect / as sysdba

这些脚本应该不会影响数据库性能,可以多次运行。

警告

此示例代码只为教育目的,Oracle Support不提供技术支持。它已经过内部测试,然而我们无法确保它在任何环境中都能成功使用。请您在使用之前先在测试环境中运行。

示例代码

Oracle 发布的用于调查 ORA-1555 错误的脚本每一版本都有所不同。这些脚本只适用于自动 UNDO 管理 (AUM) 配置的环境。

脚本文件以本文档附件的形式提供。注意:在本文档中执行剪切粘贴操作时,这些脚本可能出现格式问题。因此,脚本都附在文档中,可下载使用。本文将讨论每个脚本的优势/作用,并提供示例输出。

AUM 配置和 ORA-1555 全面分析

  1. 配置:

UndoDatafiles.sql — spool 输出到位于默认目录位置的文件 undodatafiles.out 中。

UndoParameters.sql — spool 输出到位于默认目录位置的文件 undoparameters.out 中。

UndoUsage.sql — spool 输出到位于默认目录位置的文件 undousage.out 中。

  1. 当前未提交的事务:

CurrentActivity.sql — spool 输出到位于默认目录位置的文件 undoactivity.out 中。

  1. 历史 UNDO 信息:

UndoHistoryInfo.sql — spool 输出到位于默认目录位置的 undohistory.out 中。

UndoStatistics.sql — spool 输出到位于默认目录位置的 undostatistics.out 中。您可修改此报告以显示适当的分析时间范围。默认情况下,查看最后两天的 V$UNDOSTAT 数据。在 V$UNDOSTAT 视图中,数据会保留七天。

  1. 等待/锁定分析:

UndoPressure.sql — spool 输出到位于默认目录位置的 undopressure.out 中。

  1. 调查 LOB 问题:

LobData.sql — spool 输出到位于默认目录位置的 lobdata.out 中。

示例输出

  1. 配置

示例 undodatafiles.out

############## RUNTIME ##############

Run Time

—————–

05-Aug-2009 08:53

############## DATAFILES ##############

Aut

TBSP Name                   File #   Bytes Alloc (MB) Max Bytes Used (MB) (MB)     Ext

—————————— —— ————————- ———————————— ——

SMALLUNDO                  3                              200                                     200     YES

查看配置数据。AUTOEXTEND 是否打开?如果 UNDO 表空间配置为随着空间需求自动增长,这会对数据库造成影响,数据库可能不会重新使用超过Retention设置的过期 Undo extent,以减少发生 ORA-1555 的几率。表空间进而会随着新的需求增长。

示例 undoparameters.out

############## RUNTIME ##############Run Time

—————–

05-Aug-2009 08:56

############## PARAMETERS ##############

Instance #  Parameter                              Session Value          Instance Value

————– ———————————– ————————- ————————-

1                 _smu_debug_mode                              33554432                  33554432

1                _undo_autotune                                         TRUE                        TRUE

1                undo_management                                    AUTO                       AUTO

1                undo_retention                                                900                             900

1                undo_tablespace                        SMALLUNDO          SMALLUNDO

查看影响 Undo Retention规则的参数设置。

‘_smu_debug_mode’=33554432 会强制让自动优化程序基于系统中运行时间最长的 SQL 的执行时间来计算自动的 undo retention。在默认情况下,自动调整后的保留时间会增长到很长的时间段,空间压力将成为 Undo 表空间中的重大问题。

‘_undo_autotune’=false 是一些 AUM bug 的权宜方法,但这会对分析产生重大影响。V$UNDOSTAT 中不会再进一步跟踪其他数据,显式指定的的 UNDO_RETENTION 设置是影响 undo Retention处理的关键。

示例 undousage.out

############## RUNTIME ##############

Run Time

————————–

05-Aug-2009 08:58

############## IN USE Undo Data ##############

PCT_INUSE

—————-

23.625

TABLESPACE_NAME    EXTENT_MAN    ALLOCATIO      SEGMEN      RETENTION

——————————— ———————— ———————- —————- —————–

SMALLUNDO                  LOCAL                    SYSTEM             MANUAL    NOGUARANTEE

Sum of Free

—————-

65,536

Total Bytes

—————-

209,715,200

############## UNDO SEGMENTS ##############

Status              Total Extents

—————— —————–

UNEXPIRED                    21

EXPIRED                        807

ACTIVE                          195

————-

sum                               1,023

 

Status               Total Segments

——————– ——————-

ONLINE                                  11

————-

sum                                          11

  1. 当前未提交的事务

示例 undoactivity.out

############## RUNTIME ##############

Run Time

—————–

19-Aug-2009 09:43

############## Current Uncommitted Transactions ##############

Started    User     Undo Segment Name            File #       Block #       Status         KBytes     Rows

———— ——— ————————————- ———— ————– ————– ————- ———-

08/19/09 KEN      _SYSSMU8_1245875459$                  3            9735     ACTIVE       48,664   614,178

09:43:02

查看未提交的事务。该事务有多大?什么用户在处理该事务?随着时间的推移,其是否显示为未提交?这在预期之内吗?在此事务之前开始的任何长时间运行的查询、或在此事务之前使用闪回功能都必须创建此数据的旧“副本”。

  1. 历史 UNDO 信息

示例 – undohistory.out

############## RUNTIME ##############

Run Time

—————–

05-Aug-2009 09:08

############## HISTORICAL DATA ##############

Max Concurrent

Last 7 Days

——————–

5

Max Concurrent

Since Startup

———————–

5

1555 Errors

—————

0

Undo Space Errors

————————-

0

############## CURRENT STATUS OF SEGMENTS ##############

############## SNAPSHOT IN TIME INFO ##############

##############(SHOWS CURRENT UNDO ACTIVITY)##############

Segment Name                      Active Bytes     Unexpired Bytes Expired Bytes

———————————– ——————— ———————- ——————–

_SYSSMU10_1245875459$                           0             1,114,112                 65,536

_SYSSMU1_1245875459$                             0             3,211,264          75,497,472

_SYSSMU2_1245875459$                             0                196,608                 65,536

_SYSSMU3_1245875459$                             0             1,507,328          55,115,776

_SYSSMU4_1245875459$             43,253,760                           0                          0

_SYSSMU5_1245875459$                             0             1,048,576          19,922,944

_SYSSMU6_1245875459$                             0                327,680                          0

_SYSSMU7_1245875459$                             0             1,114,112                 65,536

_SYSSMU8_1245875459$                             0                458,752            4,849,664

_SYSSMU9_1245875459$                             0             1,179,648                 65,536

10 rows selected.

############## UNDO SPACE USAGE ##############

Segment#      Shrinks     Avg Shrink Size

—————– ————- ———————–

0                0                              0

1                5                2,424,832

2                5                1,402,470

3                6                2,457,600

4                2                  425,984

5                4                1,638,400

6                4                1,523,712

7                2                1,048,576

8                5                2,031,616

9                1                2,621,440

10                2                1,114,112

11 rows selected.

了解并发性信息。有多少并发性事务相互重叠?如果您不断看到高并发的未提交事务,是否自动调整的 retention 正在正确处理工作负载?对于当前未提交的工作,您还可以检查运行时的段活动情况。同时查看 UNDO 改动的信息。这些段的工作负载是否平衡?收缩是否均匀地分布在段中?是否有任何段承受的压力大于其他段?

示例 undostatistics.out

############## RUNTIME ##############

Run Time

—————–

05-09:08

############## Historical V$UNDOSTAT (Last 2 Days) ##############

Query

Maximum                                               Undo     # of                                                     Tuned Ret

Date/Time Minutes   SqlID                 TBS        Blocks    Trans   # of Unexpired    # of Expired Minutes

————- ————- ——————– ———– ——— ———- ——————— —————- —————

03-09:15                 14 0rc4km05kgzb9           14          39       160                        312           25,024                  29

03-09:25                   4 0rc4km05kgzb9           14          36       220                        312           25,024                  43

03-09:35                 14 0rc4km05kgzb9           14         327      200                            8           25,024                  43

03-09:45                   4 0rc4km05kgzb9           14           20      202                        464           24,896                  29

. . .

05-08:37                   1 0rc4km05kgzb9           14           22      195                           80          25,344                  15

05-08:47                12 0rc4km05kgzb9            14           35      216                           48          25,376                  15

05-08:57                  2 0rc4km05kgzb9            14           33      183                           56          25,368                  15

 

284 rows selected.

############## RECENT MISSES FOR UNDO (Last 2 Days) ##############

no rows selected

no rows selected

############## AUTO-TUNING TUNE-DOWN DATA ##############

######## ROLLBACK DATA (Since Startup) ##############

Name                                                                                                        Counters

————————————————————————————- ————

user rollbacks                                                                                                 4,959

transaction tables consistent reads – undo records applied                          3

transaction tables consistent read rollbacks                                                    0

data blocks consistent reads – undo records applied                          300,730

rollbacks only – consistent read gets                                                       11,384

cleanouts and rollbacks – consistent read gets                                             39

rollback changes – undo records applied                                                18,529

transaction rollbacks                                                                                       190

total number of undo segments dropped                                                         0

tune down retentions in space pressure                                                           0

global undo segment hints helped                                                                     1

global undo segment hints were stale                                                               0

local undo segment hints helped                                                                       0

local undo segment hints were stale                                                                  0

undo segment header was pinned                                                             90,532

IMU CR rollbacks                                                                                           6,183

SMON posted for undo segment recovery                                                       0

SMON posted for undo segment shrink                                                            0

18 rows selected.

############## Long Running Query History ##############

 

Date                    SQL ID                Runaway SQL ID                          Space Issues

——————– ———————- —————————————– ————————————————

02-19:05              0rc4km05kgzb9                                                          Max Tuned Down – Not Auto-Tuning

02-19:15              0rc4km05kgzb9                                                          Reached Best Retention

02-19:25              0rc4km05kgzb9                                                          Reached Best Retention

02-19:35              0rc4km05kgzb9                                                          Reached Best Retention

02-19:45              0rc4km05kgzb9                                                          Reached Best Retention

############## Details on Long Run Queries ##############

SQL ID                 SQL Text                                                                                             Last Load                  Elapsed Days

———————- ——————————————————————————— ————————– ——————

0rc4km05kgzb9    select 1 from obj$ where name=’DBA_QUEUE_SCHEDULES’  2009-08-04/13:30:06                     19

查看报告中在设定时间内收集的关于 undo 活动的数据(默认为 2 天)。

第二部分将显示在 V$UNDOSTAT 中的七天或在实例生命周期中,查询持续时间大于调整后的Retention时间的情况。

是否有大量的“调低”相关活动?“调低”是自动调整 AUM 的一种功能,将会收缩保留时间以减少 UNDO 空间压力。这可指向尚未引发 ORA-30036 错误的空间问题。

最后调查长时运行查询数据。这些可能是我们预期内的,但也有助于指出意外的查询活动。

  1. 等待/锁定分析

示例 undopressure.out

############## RUNTIME ##############

Run Time

—————–

05-08:58

############## WAITS FOR UNDO (Since Startup) ##############

Cummalitve

Instance# Enq Total Requests   Total Waits       Successes           Failures             Time

————- —— ——————– —————- ———————— ————— ——————

1                 HW                  2,104                     0                       2,104                    0                       0

1                  US                        58                     0                            58                    0                       0

############## LOCKS FOR UNDO ##############

no rows selected

############## TUNED RETENTION HISTORY (Last 2 Days) ##############

############## LOWEST AND HIGHEST DATA ##############

END_TIME TUNED_UNDORETENTION

—————– ————————————–

05-08:58                                                      900

05-08:57                                                      900

05-08:37                                                      900

05-07:17                                                      900

05-04:17                                                      900

05-03:57                                                      900

05-03:37                                                      900

05-02:57                                                      900

05-02:37                                                      900

05-02:17                                                      900

05-01:17                                                      900

11 rows selected.

END_TIME TUNED_UNDORETENTION

—————– ————————————-

04-17:57                                                   2227

############## CURRENT TRANSACTIONS ##############

START_DATE  START_SCN   STATUS            SQL Code

——————— —————— —————- —————————————-

05-08:58               53717782         ACTIVE       update abc_tmp set edition_name=”

CURRENT_SCN

———————

53734654

############## WHO’S STEALING WHAT? (Last 2 Days) ##############

UnexStolen ExStolen UnexReuse ExReuse

————— ———— ————— ———–

0              22                   0              0

0              12                   0              0

查看等待和锁定信息。高等待和性能问题可能与已知的 UNDO 性能 bug 匹配。同时查看高、低调整后的Retention信息。在此报告中,您是否发现被盗 extent 的证据?未过期 extent 是否被盗?

  1. 调查 LOB 问题

示例 lobdata.out

Table               Column                                                Tablespace       PCTVersion %   Retention

—————— ———————————————- ——————– ——————– ————-

CTEST             DATA_OBJECT                                TB1                                                          900

PAA_TEST    RESPONDER_COMMENT              TB1                                                          900

EMP_O           PICTURE                                             USERS                                      10

EMP_O           RESUME                                             USERS                                      10

TEST               COMMENTS                                      TB1                                                          900

5 rows selected.

如果定期更新 LOB 数据,LOB 对象上发生 ORA-1555 就可能是预期内的。PCTVersion 默认为 10%,如果您持续对 LOB 数据进行了更改,那么这个此值通常需要调高很多。有时 100%(保留所有更改)还不足以适应工作负载。常规的 ORA-1555 诊断/分析对与 LOB 相关的 ORA-1555 错误是没有用的。LOB 产生的 UNDO 不是使用 UNDO 表空间中的 extent,而是保留在 LOB 表空间中。

其他参考:
https://wenku.baidu.com/view/47b01e6ab84ae45c3b358ca8.html

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams222.htm#REFRN10225

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=341463354078393&parent=DOCUMENT&sourceId=1307334.1&id=1555.1&_afrWindowMode=0&_adf.ctrl-state=wgqt71fpl_126

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=341423607535564&id=1307334.1&_afrWindowMode=0&_adf.ctrl-state=wgqt71fpl_77

Oracle 标准服务 – 数据库技术支持通讯 – 2015年11月版,57期 (文档 ID 2088997.1)

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=wgqt71fpl_299&_afrLoop=344990618186201#title1

关于ORACLE的v$process 和v$session 到达最大连接限制的问题

ORA-12520:TNS:监听程序无法为请求的服务器类型找到可用的处理程序

oracle这个错误的意思是  数据库的连接数达到最大值限制。

一、关于v$process v$session的基本知识
Oracle数据库中Session和Connection的区别。
在Oracle的官方文档上,对Session和Connection是这样解释的:
  Connection: Communicate pathway between a client process and an Oracle database instance.

连接:一个客户端进程和Oracle数据库实例之间的通信链路。

Session: A logical entity in the database instance memory that represnts the state of a current user login to a database. A single connection can have 0, 1 or more sessions established on it.
会话:用于展示当前登录到数据库用户的状态的数据库实例内存中的一个逻辑实体。一个单独的连接可以有0,1,或者更多的会话。

Connection并不是直接建立在用户进程和数据库实例之间的。而是在用户进程和Server Process(服务器进程)之间的,因此有一个Connection就一定会有一个用户进程和一个服务器进程。但不一定会存在Session。比如,如果需要将东西从A运到B,Connection可以看成是一座“桥”,而卡车把东西从A运到B后并返回A,这就是Session。所以,只要不断开连接,随时都可以在这个连接上创建出会话。

二、查看v$process v$session的基本信息
查询资源限制的视图的语法:

select * from v$resource_limit;

select * from v$process;

select * from v$session;

select * from v$session t where t.STATUS=’ACTIVE”;

process和session参数最大值估算方法
select round(sum(pga_used_mem)/1024/1024,0) total_used_M, round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,
round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M, round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process;

三、释放资源
在sqlnet.ora文件中设置expire_time 参数。
可以使用EXPIRE_TIME参数间歇检查异常session并释放process。
官方说明:SQLNET.EXPIRE_TIME
Purpose
Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
Limitations on using this terminated connection detection feature are:
It is not allowed on bequeathed connections.
Though very small, a probe packet generates additional traffic that may downgrade network performance.
Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Recommended Value
10
Example
SQLNET.EXPIRE_TIME=10

这里设置是10分钟,每10分钟Oracle会确认所有session客户端连接是否正常,对于不正常的session,oracle会清理process。

同时,扩大最大连接数,同时Session自动跟着扩大。

–修改最大连接数:

alter system set processes = 500 scope = spfile;

重启数据库:

cmd :sqlplus sys/passw@databasename as sysdba

然后输入以下命令

shutdown immediate;——关闭数据库,大概需要一个小时

startup;–重启数据库,大概几分钟。

重启之后:

select count(*) from v$process    ——从298变成132

select count(*) from v$session    ——从104变成38

但是,在ArcGIS Portal 里面打开一个页面,调用了SDE图层,

上面的连接数字都增加,那么就算设置为最大500,也支撑不来多少用户使用的!!!!????

–查看当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||’s’,b.sql_text,machine

from v$session a, v$sqlarea b

where a.sql_address =b.address order by cpu_time;

——在ArcGIS Portal 里面打开一个页面,调用了SDE图层,通过以上语句,可以看到SDE用户的连接信息。

就算关闭了浏览器,一段时间依然可以看到这些信息,半个小时后,SDE的连接才消失。

——查询数据库有没有死锁,no row selected 说明没有死锁。

select * from v$locked_object

Oracle R12.2补丁步骤

–应用层应用补丁
–1、补丁准备阶段(需run模式下),合适之前补丁是否正常并将文件清理干净,同时同步FS1和FS2
$ adop phase=prepare

–2、补丁应用阶段
$ adop phase=apply patches=xxxxxx

–3、确定阶段(编译无效对象,在此前阶段可通过abort回退)
$ adop phase=finalize

–4、切换阶段(关闭并发管理器和应用,置EBS系统不可用,并完成转化)
$ adop phase=cutover

–5、清理阶段(run模式下,删除代码和数据,如未运行或者下次prepare阶段自动运行)
$ adop phase=cleanup

参考文件:

How To Run Adop Like – Adpatch – With Option Apply=No? (文档 ID 2067371.1)
ADOP phase=prepare Errors With patch does not exists (文档 ID 1941327.1)

Oracle DB scn情况查询

–1、查询当前scn情况

select version,
       date_time,
       dbms_flashback.get_system_change_number current_scn,
       indicator
  from (select version,
               to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
               ((((((to_number(to_char(sysdate, 'YYYY')) – 1988) * 12 * 31 * 24 * 60 * 60) +
               ((to_number(to_char(sysdate, 'MM')) – 1) * 31 * 24 * 60 * 60) +
               (((to_number(to_char(sysdate, 'DD')) – 1)) * 24 * 60 * 60) +
               (to_number(to_char(sysdate, 'HH24')) * 60 * 60) +
               (to_number(to_char(sysdate, 'MI')) * 60) +
               (to_number(to_char(sysdate, 'SS')))) * (16 * 1024)) –
               SYS.dbms_flashback.get_system_change_number) /
               (16 * 1024 * 60 * 60 * 24)) indicator
          from v$instance);

–2、查询距离上限天数的历史变化情况

SELECT tim,
       gscn,
       round(rate),
       round((chk16kscn – gscn) / 24 / 3600 / 16 / 1024, 1) "HEADROOM"
  FROM (SELECT tim,
               gscn,
               rate,
               ((((to_number(to_char(tim, 'YYYY')) – 1988) * 12 * 31 * 24 * 60 * 60) +
               ((to_number(to_char(tim, 'MM')) – 1) * 31 * 24 * 60 * 60) +
               (((to_number(to_char(tim, 'DD')) – 1)) * 24 * 60 * 60) +
               (to_number(to_char(tim, 'HH24')) * 60 * 60) +
               (to_number(to_char(tim, 'MI')) * 60) +
               (to_number(to_char(tim, 'SS')))) * (16 * 1024)) chk16kscn
          FROM (SELECT first_time tim,
                       first_change# gscn,
                       ((next_change# – first_change#) /
                       ((next_time – first_time) * 24 * 60 * 60)) rate
                  FROM v$archived_log
                 WHERE (next_time > first_time)))
 ORDER BY 1, 2;

可参考以下链接:

https://www.cnblogs.com/likingzi/p/6420927.html

其他:此脚本来做patch 13498243