分类目录归档:系统层级

Oracle增加表空间步骤

本文只介绍新增文件的方式(其他方式类似可自行研究):

1、通过命令:show parameter db_block_size,来查询当前数据库单个文件的最大size,如果是8192(8K),最大单个文件为32G,如果是16384(16K),最大单个文件为64G。

2、通过以下命令查询当前系统对应需要增加数据文件的表空间信息。

SELECT Upper(F.TABLESPACE_NAME) “表空间名”,
D.TOT_GROOTTE_MB “表空间大小(M)”,
D.TOT_GROOTTE_MB – F.TOTAL_BYTES “已使用空间(M)”,
To_char(Round((D.TOT_GROOTTE_MB – F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
‘990.99’) || ‘%’ “使用比”,
F.TOTAL_BYTES “空闲空间(M)”,
F.MAX_BYTES “最大块(M)”
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

3、通过以下命令查询表空间对应单个文件的信息(主要是保证新增的数据文件跟原有文件保存信息一致,如保证单个文件一致)。

SELECT “File Name”, “Tablespace”, “Status”, “Size (MB)”, “Used (MB)”, “Used (Proportion)”, “Used (%)”, “Auto Extend” FROM(
select * from (
SELECT /*+ all_rows use_concat */
‘SQLDEV:LINK{#;#}’||USER||’#;#DATAFILE#;#’||ddf.file_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Name”,
ddf.tablespace_name as “Tablespace”,
ddf.online_status as “Status”,
TO_CHAR(NVL(ddf.bytes / 1024 / 1024, 0), ‘99999990.000’) as “Size (MB)”,
TO_CHAR(DECODE(NVL(u.bytes/1024/1024, 0), 0, NVL((ddf.bytes – NVL(s.bytes, 0))/1024/1024, 0), NVL(u.bytes/1024/1024, 0)), ‘99999999.999’) as “Used (MB)”,
CASE
when ddf.online_status = ‘OFFLINE’ then
‘OFFLINE’
when ddf.online_status = ‘RECOVER’ then
‘RECOVER’
else
‘SQLDEV:GAUGE:0:100:0:0:’|| TRIM(TO_CHAR(DECODE((NVL(u.bytes, 0) / ddf.bytes * 100), 0, NVL((ddf.bytes – NVL(s.bytes, 0)) / ddf.bytes * 100, 0), (NVL(u.bytes, 0) / ddf.bytes * 100)), ‘990’))
end as “Used (Proportion)”,
TO_CHAR(DECODE((NVL(u.bytes, 0) / ddf.bytes * 100), 0, NVL((ddf.bytes – NVL(s.bytes, 0)) / ddf.bytes * 100, 0), (NVL(u.bytes, 0) / ddf.bytes * 100)), ‘990.99’) as “Used (%)”,
ddf.autoextensible as “Auto Extend”
FROM
sys.dba_data_files ddf,
(
SELECT
file_id,
SUM(bytes) bytes
FROM
sys.dba_free_space GROUP BY file_id
) s,
(
SELECT
file_id,
SUM(bytes) bytes
FROM
sys.dba_undo_extents
WHERE
status <> ‘EXPIRED’
GROUP BY file_id
) u
WHERE
(ddf.file_id = s.file_id(+) and ddf.file_id=u.file_id(+))
UNION
SELECT
‘SQLDEV:LINK{#;#}’||USER||’#;#DATAFILE#;#’||v.name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Name”,
dtf.tablespace_name as “Tablespace”,
dtf.status as “Status”,
TO_CHAR(NVL(dtf.bytes / 1024 / 1024, 0), ‘99999990.000’) as “Size (MB)”,
TO_CHAR(NVL(t.bytes_used/1024/1024, 0), ‘99999990.000’) as “Used (MB)”,
CASE
when dtf.status = ‘OFFLINE’ then
‘OFFLINE’
else
‘SQLDEV:GAUGE:0:100:0:0:’|| TRIM(TO_CHAR(NVL(t.bytes_used / dtf.bytes * 100, 0), ‘990.99’))
end as “Used (Proportion)”,
TO_CHAR(NVL(t.bytes_used / dtf.bytes * 100, 0), ‘990’) as “Used (%)”,
dtf.autoextensible as “Auto Extend”
FROM
sys.dba_temp_files dtf,
sys.v_$tempfile v,
v$temp_extent_pool t
WHERE
(dtf.file_name = v.name or dtf.file_id = v.file#)
and dtf.file_id = t.file_id(+)
ORDER BY 1
) sub1 order by 2 asc
)

4、新增文件命令。

alter tablespace APPS_TS_TX_DATA add datafile ‘/u01/DEV/db/data/a_txn_data07.dbf’ size 10000m;

ORA-20100 文件 通过 FND_FILE 创建失败解决方案

克隆了一个EBS 11i的环境,提交客户同步请求的时候出现了错误提示是:
原因:由于 ORA-20100: 为 FND_FILE 创建文件 10034190.tmp 失败。
我的服务器中有2个ERP环境,参数APPSLPTMP指定为默认的/usr/tmp。然后, 在网上找了一些相关资料,说不同的环境APPSLPTMP和数据库的utl_file_dir参数不能指定相同的目录,于是做了修改,修改命令如下:
# mkdir -p /usr/opt/tmp       创建目录
# chmod -R a+rw /usr/opt  赋予该目录读写权限
# su – orauat                               切换致数据库用户
$ export APPSLPTMP=/usr/opt/tmp   修改环境变量
$ cd $ORACLE_HOME/dbs
$ vi init<SID>.ora                   编辑数据库初始化文件,修改参数:utl_file_dir
utl_file_dir = /usr/opt/tmp,……..<SID>_prod
并且重新启动的环境。在数据库执行函数:FND_FILE_OUT_LINE()提示成功。
这个错误很常见:

1.查看$APPLPTMP系统环境变量的值,一般是/usr/tmp,需要保证该文件夹是存在的;

2.查看utl_file_dir数据库参数,其第一个值也应该为/usr/tmp;
select* from v$parameter t whee t.name=’utl_file_dir’
3.查看该文件夹的权限,该文件夹必须为应用用户和数据库用户都具有读写权限;
4.通过exec FND_FILE.PUT_LINE(FND_FILE.LOG, ‘THIS IS A TEST’);进行测试;
5.如果仍然还有问题,请查看你的服务器上面是不是有多套ERP环境,如果有多个的话两个$APPLPTMP文件同时写会冲突当一台服务器上运行了多套环境时,不能使用/usr/tmp作为$APPLPTMP,须定义成各自的目录。且该目录须在数据库参数utl_file_dir中。

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