Oracle – 第21页 – Database SOS

ORACLE最大可以存储多少数据量

今天有朋友在群里面讨论oracle数据库最大可以存储的数据大小,下面根据官方文档提供的相关限制,大概估算出来oracle数据库最多可以存储的数据量

Physical Database Limits(11.2)

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 2²² – 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of25,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 2²² or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 2³² ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2²²) blocks.
External Tables file	Maximum size	Dependent on the operating system. An external table can be composed of multiple files.

通过这里的相关限制可以大概的技术出来oracle数据库在传统数据文件和大数据文件情况下最大大小分别是:

传统数据文件(Smallfile)

32*1024(数据块大小)* (2²²–1)(一个数据文件的数据块数)*65533

(数据库中最多数据文件个数)= 9006784790495232(byte)/1024/1024/1024/1024=8191.6P
如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2047.9P
大数据文件(Bigfile)

32*1024(数据块大小)* (2³²– 1)(一个数据文件的数据块数)*65533

(数据库中最多数据文件个数) = 9222949822242324480 (byte)/1024/1024/1024/1024 =8589541374P
如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2147385343.5P
参考:http://docs.oracle.com/cd/E11882_01/server.112/e25513/limits002.htm

kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)

alert日志错误kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)
该错误是出现在win 2008 + 11.2.0.1的数据库中

Sun Jun 03 01:00:05 2012
kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DFA, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DF0, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DF0, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
kewastUnPackStats(): bad magic 1 (0x0000000036407DD8, 0)
Sun Jun 03 02:00:18 2012
kewastUnPackStats(): bad magic 1 (0x0000000035387DD4, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DD4, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
kewastUnPackStats(): bad magic 1 (0x0000000035387DDC, 0)
Sun Jun 03 03:00:32 2012
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DD4, 0)
kewastUnPackStats(): bad magic 1 (0x000000001D8F7DDC, 0)

错误原因:
出现该问题是 Bug:8967729/unpublished bug 8730312
Active Session History中某些数据(the moduile, action program, etc. information)未被正确存储,然后awr程序在整点(默认配置)的时候访问V$ACTIVE_SESSION_HISTORY 视图失败,从而出现了该错误.出现该问题可能导致ash和awr使用异常或不准确

问题解决:
1.在Oracle12c release and the 11.2.0.2 and higher patchsets中被修复
2.打上one-off Patch:8730312(one-off补丁没有经过oracle的严格测试,在打该补丁前可能需要进行测试)

系统中数据文件第一个数据块和oracle 中第一个数据块关系

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：系统中数据文件第一个数据块和oracle 中第一个数据块关系

数据文件第一个数据块到底有没有纳入数据块的数据块计算中,也就是我们通常所说的rdba(file#,block),是否真的是从数据文件的第一个数据块开始计算的？下面通过实验验证
相关信息和准备工作

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-05-29 19:39:48
SQL> select name,block_size from v$datafile where file#=9;
NAME                                                         BLOCK_SIZE
------------------------------------------------------------ ----------
/u01/oracle/oradata/xifenfei/users01.dbf                           8192
--dd出来数据文件第一和第二个数据块
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.01 bs=8192 count=1
1+0 records in
1+0 records out
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.02 bs=8192 count=1 skip=1
1+0 records in
1+0 records out
[oracle@xifenfei ~]$ ll user.*
-rw-r--r--  1 oracle oinstall 8192 May 26 04:43 user.01
-rw-r--r--  1 oracle oinstall 8192 May 26 04:44 user.02

bbed验证

[oracle@xifenfei ~]$ bbed password=blockedit blocksize=8192  listfile=/home/oracle/bbed_new.file mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Sat May 26 04:56:49 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/xifenfei/users01.dbf                             0
     2  /home/oracle/user.01                                                 0
     3  /home/oracle/user.02                                                 0
--users01.dbf(完整数据文件,第一个数据块)
BBED> set file 1
        FILE#           1
BBED> set block 1
        BLOCK#          1
BBED> d /v count 128
 File: /u01/oracle/oradata/xifenfei/users01.dbf (1)
 Block: 1       Offsets:    0 to  127  Dba:0x00400001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........
 <16 bytes per line>
--直接设置file 2错误(后续提供其他方法)
BBED> set file 2
BBED-00307: incorrect blocksize (8192) or truncated file
--查看users01.dbf(第二个数据块)
BBED> set file 3
        FILE#           3
BBED> set block 1
        BLOCK#          1
BBED> d /v count 128
 File: /home/oracle/user.02 (3)
 Block: 1       Offsets:    0 to  127  Dba:0x00c00001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........
 <16 bytes per line>
--查看users01.dbf(真正第一个数据块)
BBED> set filename 'user.01'
        FILENAME        user.01
BBED> d /v count 128
 File: user.01 (0)
 Block: 1       Offsets:    0 to  127  Dba:0x00000000
-------------------------------------------------------
 00020000 00200000 800c0000 5d5c5b5a l ..... ......]\[Z
 00000000 86280000 00000000 00000000 l .....(..........
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 <16 bytes per line>

通过这个对比可以知道:当我们直接使用bbed查看数据块内容的时候,自动屏蔽了数据文件上真正的第一个数据块.其实block 1是数据文件上的第二个数据块

hexdump验证

--users01.dbf(完整文件)
[oracle@xifenfei ~]$ hexdump -C /u01/oracle/oradata/xifenfei/users01.dbf|head -20
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00002010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00002020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00002030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00002040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00002070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00002080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00002090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000020a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000020b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000020c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000020f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00002100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
--users01.dbf(第一个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.01
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000
--users01.dbf(第二个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.02|head -20
00000000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00000010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00000020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00000030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00000060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00000070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00000080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00000090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000000a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000000b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000000c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000000f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00000100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000110  00 00 00 00 00 00 00 00  09 00 00 00 00 00 00 00  |................|
00000120  00 00 00 00 f9 e9 be 2e  00 00 00 00 00 00 00 00  |................|
00000130  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

通过hexdump对三个文件的对比可以知道users01.dbf的头两个数据文件确实是由第一和第二个数据块组成.然后结合上面bbed dump出来的结果.可以再次证明数据文件第一个数据块,不能被bbed识别(从第二个数据文件开始)

实验总结
我们的数据文件其实是从文件的第二个数据块开始记录起(该数据块为block 1).也就是说系统的数据块和oracle中的rdba标示的数据块不是一致.而是系统数据块比oracle数据块多1.
因这个原因解释了以前的一个疑问:Oracle数据文件大小的限制为什么指定数据文件最大值为(2^22-1*block_size),而不是根据rowid的2^22*block_size
关于类此问题在windows验证请见:在UltraEdit中定位数据文件内容

拷贝windows中datafile header方法(ocopy)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：拷贝windows中datafile header方法(ocopy)

在很多时候,我们需要对数据文件的头部进行分析,但是因为人不在本地,数据文件本身很大,网络又不好.这个时候我们可能要求对方传过来文件文件的头部几M即可.在unix/linux中可以使用dd实现该需求;在win中可以使用ocopy实现该需求.dd实现请参考:dd操作数据文件;这里讲win下面实现方法:
ocopy语法

D:\>ocopy
OCOPY v2.0 - Copyright 1989-1993 Oracle Corp.  All rights reserved.
Usage:
    ocopy from_file [to_file [a | size_1 [size_n]]]
    ocopy -b from_file to_drive
    ocopy -r from_drive to_dir

ocopy拷贝数据文件header

D:\>ocopy  E:\oracle\oradata\xifenfei\SYSAUX01.DBF d:\sysaux.dbf 20480 1
D:\SYSAUX.DBF
OCOPY - Write error.
--忽略(未找到原因)
D:\>dir sysaux*
 驱动器 D 中的卷没有标签。
 卷的序列号是 000B-FBCB
 D:\ 的目录
2012/05/07  22:28             1,024 SYSAUX.DB2
2012/05/07  22:28             1,024 SYSAUX.DB3
2012/05/07  22:28             1,024 SYSAUX.DB4
2012/05/07  22:28             1,024 SYSAUX.DB5
2012/05/07  22:28             1,024 SYSAUX.DB6
2012/05/07  22:28             1,024 SYSAUX.DB7
2012/05/07  22:28             1,024 SYSAUX.DB8
2012/05/07  22:28             1,024 SYSAUX.DB9
2012/05/07  22:28        20,971,520 SYSAUX.DBF
               9 个文件     20,979,712 字节
               0 个目录 28,771,282,944 可用字节
--SYSAUX.DBF是我们需要的文件

上传到linux中bbed验证

[oracle@xifenfei ~]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri May 25 08:31:12 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/home/oracle/sysaux.dbf'
        FILENAME        /home/oracle/sysaux.dbf
BBED> set blocksize 8192
        BLOCKSIZE       8192
--从win中拷贝的数据库,第一个block非bbed有效块
BBED> set block 2
        BLOCK#          2
BBED> map
 File: /home/oracle/sysaux.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 360 bytes                    @0
 ub4 tailchk                                @8188
BBED> map /v
 File: /home/oracle/sysaux.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 360 bytes                    @0
    struct kcvfhbfh, 20 bytes               @0
    struct kcvfhhdr, 76 bytes               @20
    ub4 kcvfhrdb                            @96
    struct kcvfhcrs, 8 bytes                @100
    ub4 kcvfhcrt                            @108
    ub4 kcvfhrlc                            @112
    struct kcvfhrls, 8 bytes                @116
    ub4 kcvfhbti                            @124
    struct kcvfhbsc, 8 bytes                @128
    ub2 kcvfhbth                            @136
    ub2 kcvfhsta                            @138
    struct kcvfhckp, 36 bytes               @140
    ub4 kcvfhcpc                            @176
    ub4 kcvfhrts                            @180
    ub4 kcvfhccc                            @184
    struct kcvfhbcp, 36 bytes               @188
    ub4 kcvfhbhz                            @224
    struct kcvfhxcd, 16 bytes               @228
    word kcvfhtsn                           @244
    ub2 kcvfhtln                            @248
    text kcvfhtnm[30]                       @250
    ub4 kcvfhrfn                            @280
    struct kcvfhrfs, 8 bytes                @284
    ub4 kcvfhrft                            @292
    struct kcvfhafs, 8 bytes                @296
    ub4 kcvfhbbc                            @304
    ub4 kcvfhncb                            @308
    ub4 kcvfhmcb                            @312
    ub4 kcvfhlcb                            @316
    ub4 kcvfhbcs                            @320
    ub2 kcvfhofb                            @324
    ub2 kcvfhnfb                            @326
    ub4 kcvfhprc                            @328
    struct kcvfhprs, 8 bytes                @332
    struct kcvfhprfs, 8 bytes               @340
    ub4 kcvfhtrt                            @356
 ub4 tailchk                                @8188
--数据块拷贝出来正常

sql_id和hash value的部分转换

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：sql_id和hash value的部分转换

从oracle 10g开始引进了sql_id，在老版本的oralce中,要表明一条sql,一般使用hash value，而在10g及其以后版本中一般建议使用sql_id,从9i的sp和10g的awr中也可以看出.对于Library Cache对象,Oracle使用MD5算法进行哈希,生成一个128位的Hash Value,其中低32位作为HASH VALUE显示,SQL_ID则取了后64位.既然hash value和sql_id之前存在着这样的关系,那么我们就可以通过函数实现两者的部分转换(因为最终取值长度不同,所以不能完全转换)
1.查询sql_id和hash value

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
  2  "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-05-26 01:05:39
SQL> select sql_id,hash_value from v$sql where sql_text like
  2  'select * from dual';
SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

2.oracle自带函数转换sql_id to hash value

SQL> select dbms_utility.SQLID_TO_SQLHASH('a5ks9fhw2v9s1') hash_value FROM DUAL;
HASH_VALUE
----------
 942515969

3.自己编写函数sql_id to hash value

SQL> CREATE OR REPLACE FUNCTION sql_id_2_hash_value (sql_id VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     l_output   NUMBER := 0;
  5  BEGIN
  6         SELECT TRUNC (
  7                   MOD (
  8                      SUM (
  9                         (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz',
 10                                 SUBSTR (LOWER (TRIM (sql_id)), LEVEL, 1))
 11                          - 1)
 12                         * POWER (32, LENGTH (TRIM (sql_id)) - LEVEL)),
 13                      POWER (2, 32)))
 14           INTO l_output
 15           FROM DUAL
 16     CONNECT BY LEVEL <= LENGTH (TRIM (sql_id));
 17     RETURN l_output;
 18  END;
 19  /
函数已创建。
SQL> select sql_id_2_hash_value('a5ks9fhw2v9s1') hash_value FROM DUAL;
HASH_VALUE
----------
 942515969

4.hash value 转换为部分 sql_id

SQL> CREATE OR REPLACE FUNCTION hash_value_2_sql_id (p_hash_value NUMBER)
  2     RETURN VARCHAR2
  3  IS
  4     l_output   VARCHAR2 (8) := '';
  5  BEGIN
  6     FOR i
  7        IN (    SELECT SUBSTR (
  8                          '0123456789abcdfghjkmnpqrstuvwxyz',
  9                          1
 10                          + FLOOR (
 11                               MOD (p_hash_value / (POWER (32, LEVEL - 1)), 32)),
 12                          1)
 13                          sqlidchar
 14                  FROM DUAL
 15            CONNECT BY LEVEL <= LN (p_hash_value) / LN (32)
 16              ORDER BY LEVEL DESC)
 17     LOOP
 18        l_output := l_output || i.sqlidchar;
 19     END LOOP;
 20
 21     RETURN l_output;
 22  END;
 23  /
函数已创建。
SQL> select hash_value_2_sql_id(942515969) from dual;
HASH_VALUE_2_SQL_ID(942515969)
--------------------------------------------------------
2v9s1

参考:http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/

找出 alter system kill session 'sid,serial#' kill 掉的数据库会话对应进程

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：找出 alter system kill session 'sid,serial#' kill 掉的数据库会话对应进程

当我们使用alter system kill session ‘sid,serial#’ 在数据库中kill掉某个会话的时候,如果你观察仔细会发现v$session.paddr发生了改变,从而是的不能直接通过关联v$process.add找出spid,然后进行其他操作.本文提供三种方法找该种情况下spid的方法.
数据库版本

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

会话1

SQL> select sid, SERIAL#,paddr from v$session where
  2  sid=(select sid from v$mystat where rownum=1);
       SID    SERIAL# PADDR
---------- ---------- --------
       133         53 35FE16F4

会话2

SQL> select sid, SERIAL#,paddr from v$session where
  2  sid=(select sid from v$mystat where rownum=1);
       SID    SERIAL# PADDR
---------- ---------- --------
       143         21 35FE2D3C

会话3

SQL> alter system kill session '133,53';
System altered.
SQL> alter system kill session '143,21';
System altered.
SQL> select sid, SERIAL#,paddr,status from v$session where sid in(133,143);
       SID    SERIAL# PADDR    STATUS
---------- ---------- -------- ----------------
       133         53 3547A3F4 KILLED
       143         21 3547A3F4 KILLED

证明alter system kill session后,v$session中的paddr发生了改变,这个时候如果需要找出原来的spid,不能使用v$session.paddr和v$process.addr关联获得

找出kill掉的spid方法1

SQL> select spid, program from v$process
  2      where program!= 'PSEUDO'
  3      and addr not in (select paddr from v$session)
  4      and addr not in (select paddr from v$bgprocess)
  5      and addr not in (select paddr from v$shared_server);
SPID                                             PROGRAM
------------------------------------------------ ------------------------------
14260                                            oracle@xifenfei (L001)
14256                                            oracle@xifenfei (L000)
15300                                            oracle@xifenfei (TNS V1-V3)
14179                                            oracle@xifenfei (D000)
15318                                            oracle@xifenfei (TNS V1-V3)
14252                                            oracle@xifenfei (N000)
SQL> !ps -ef|grep 15300|grep -v grep
oracle   15300 14052  0 03:22 ?        00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
SQL> !ps -ef|grep 15318|grep -v grep
oracle   15318 15315  0 03:22 ?        00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

找出kill 掉的spid 方法2

SQL> SELECT s.username,s.status,
  2  x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
  3  decode(bitand (x.ksuprflg,2),0,null,1)
  4  FROM x$ksupr x,v$session s
  5  WHERE s.paddr(+)=x.addr
  6  and bitand(ksspaflg,1)!=0;
USERNAME   STATUS           ADDR       KSLLAPSC   KSLLAPSN KSLLASPO                   KSLLID1R KSLL DE
---------- ---------------- -------- ---------- ---------- ------------------------ ---------- ---- --
           ACTIVE           35FD5990          6         26 14121                             0      1
           ACTIVE           35FD6FD8          1         69 14055                             0      1
           ACTIVE           35FD8620          1         69 14055                             0      1
           ACTIVE           35FD9C68          1         69 14055                             0      1
           ACTIVE           35FDB2B0          8         27 15300                             0      1
           ACTIVE           35FDC8F8         12         36 15300                             0      1
           ACTIVE           35FDDF40          1         69 14055                             0      1
           ACTIVE           35FDF588          1         69 14055                             0      1
           ACTIVE           35FE3860          7         26 14236                             0      1
           ACTIVE           35FE4EA8          1         69 14224                             0      1
           ACTIVE           35FE64F0         63          2 14311                           377 EV   1
           ACTIVE           35FEA7C8          3         26 14155                           258 EV   1
           ACTIVE           35FE9180         59          2 14248                           378 EV   1
           ACTIVE           35FE9CA4         12          2 14603                             0      1
           ACTIVE           35FD64B4          1         69 14055                             0      1
           ACTIVE           35FD7AFC          2         27 14055                             0      1
           ACTIVE           35FD9144          2         27 15300                             0      1
           ACTIVE           35FDA78C          3         26 14171                             0      1
           ACTIVE           35FDBDD4         17          2 15255                             0      1
           ACTIVE           35FDD41C         22         26 14155                             0      1
           ACTIVE           35FDEA64         52         26 14155                             0      1
           ACTIVE           35FE4384          1         69 14224                             0      1
           ACTIVE           35FE59CC          1         69 14224                             0      1
           ACTIVE           35FEB2EC          2          2 14248                             0      1
           ACTIVE           35FEC934         11         26 14121                             0      1
SYS        ACTIVE           35FEF5C4          4         16 14117                             0
                            35FE0BD0          1         69 14055                             0
                            35FE865C          1         69 14117                             0
                            35FE7B38          1         69 14117                             0
                            35FE16F4          1         26 14155                             0
                            35FD4E6C          0          0                                   0
                            35FE00AC          2        279 14117                             0
                            35FE2D3C          0          0                                   0
                            35FE7014          2        335 14117                             0
--挑选username和status为null的会话
SQL> select spid,program from v$process where addr in (
  2  '35FE0BD0',
  3  '35FE865C',
  4  '35FE7B38',
  5  '35FE16F4',
  6  '35FD4E6C',
  7  '35FE00AC',
  8  '35FE2D3C',
  9  '35FE7014'
 10  );
SPID                                             PROGRAM
------------------------------------------------ ------------------------------
                                                 PSEUDO
14179                                            oracle@xifenfei (D000)
14183                                            oracle@xifenfei (S000)
15300                                            oracle@xifenfei (TNS V1-V3)
15318                                            oracle@xifenfei (TNS V1-V3)
14252                                            oracle@xifenfei (N000)
14256                                            oracle@xifenfei (L000)
14260                                            oracle@xifenfei (L001)
8 rows selected.
--同样可以发现spid 15300和15318的进程已经在数据库中被kill掉

找出kill掉的spid方法3(11g特有)

SQL> select  spid,program  from v$process where addr in
  2  (select creator_addr from v$session where sid in(133,143));
SPID                                             PROGRAM
------------------------------------------------ ------------------------------
15300                                            oracle@xifenfei (TNS V1-V3)
15318                                            oracle@xifenfei (TNS V1-V3)

找出kill掉的spid方法4(11g特有)

SQL> select * from V$DETACHED_SESSION;
      INDX PG_NAME                                                             SID    SERIAL#        PID
---------- ------------------------------------------------------------ ---------- ---------- ----------
         0 DEFAULT                                                             143         21         21
         1 DEFAULT                                                             133         53         19
SQL> select spid,program from v$process where pid in(21,19);
SPID                                             PROGRAM
------------------------------------------------ ------------------------------
15300                                            oracle@xifenfei (TNS V1-V3)
15318                                            oracle@xifenfei (TNS V1-V3)

Process OS id : xxxxx alive after kill

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Process OS id : xxxxx alive after kill

Process OS id : xxxxx alive after kill警告

Mon May 21 04:55:06 2012
Shutting down instance (immediate)
License high water mark = 373
Mon May 21 04:55:06 2012
Stopping Job queue slave processes
Mon May 21 04:55:06 2012
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Mon May 21 04:55:14 2012
Process OS id : 9922 alive after kill
Errors in file
Mon May 21 04:55:16 2012
Process OS id : 8159 alive after kill
Errors in file /oracle/admin/resultdb/udump/resultdb_ora_14639.trc
Mon May 21 04:55:17 2012
Process OS id : 8285 alive after kill
Errors in file /oracle/admin/resultdb/udump/resultdb_ora_14639.trc
Mon May 21 04:55:33 2012
ALTER DATABASE CLOSE NORMAL

错误原因

On some platforms it takes some time to kill processes--AIX being one of those platforms
There have been previous reports of shutdown taking time on AIX and after all Oracle waits were taken out of
the picture it was determined to be due to the way the kill command is implemented on that platform.
Bug 4931101 ERRORS IN ALERT LOG DURING SHUTDOWN

处理建议

Ignore the error  as all processes will be closed and shutdown will complete successfully.

补充说明
本次出问题的数据库是运行在 linux 平台上的 10.2.0.3

主键表插入数据不提交,外键表插入数据被阻塞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：主键表插入数据不提交,外键表插入数据被阻塞

有客户和我说:他在含主外键的表中实验发现,在主表数据未提交,然后在外键表插入该数据数据时,出现外键表hang住现象.我开始以为是不同的会话,根据oracle数据库的一致性原则,应该新会话在外键表中不能知道这个记录的存在,直接报错.
可是我实验结果证明:外键表会被阻塞.分析原因如下：
模拟环境

SQL> create table t_p(id number primary key,name varchar2(100));
Table created.
SQL> create table t_f(fid number primary key,pid number, foreign key(pid) references  t_p(id));
Table created.
--会话1
SQL> insert into t_p values(1,'xifenfei');
1 row created.
SQL> commit;
Commit complete.
--会话2
SQL> insert into t_f values(1,1);
1 row created.
SQL> commit;
--会话1
SQL> insert into t_p values(2,'XIFENFEI');
1 row created.
--会话2
SQL> insert into t_f values(2,2);
--hang住

通过实验发现,当主键数据没有提交,然后在外键表中插入该数据外键数据时,该条记录会处于hang住状态(等待),那是什么原因导致了这个等待呢？对会话2做一个10046的trace,发现如下

*** 2012-05-17 17:25:41.757
WAIT #3065187488: nam='enq: TX - row lock contention' ela= 27002895 name|mode=1415053316 usn<<16
| slot=262151 sequence=588 obj#=-1 tim=1337246741756917
EXEC #3065187488:c=4000,e=27004456,p=0,cr=2,cu=14,mis=0,r=0,dep=0,og=1,
plh=0,tim=1337246741757690
ERROR #3065187488:err=1013 tim=1337246741757751
STAT #3065187488 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL
(cr=0 pr=0 pw=0 time=12 us)'
WAIT #3065187488: nam='SQL*Net break/reset to client' ela= 581 driver
id=1650815232 break?=0 p3=0 obj#=-1 tim=1337246741782587
WAIT #3065187488: nam='SQL*Net message to client' ela= 2 driver id=1650815232
#bytes=1 p3=0 obj#=-1 tim=1337246741782668

通过这个trace发现,是因为TX锁导致了外键表上的插入操作被阻塞.出现该问题的原因
有两种可能:1.两次插入(主键表和外键表分别插入)在主键表上有不兼容锁;2.外键表上有不兼容性锁.

使用oradebug跟踪会话

oradebug setmypid
--EVENT 10704跟踪锁的使用情况
oradebug EVENT 10704 trace name context forever,level 10
--插入数据操作
oradebug EVENT 10704 trace name context off
oradebug TRACEFILE_NAME

跟踪主键表插入数据

*** 2012-05-17 19:05:52.410
ksqgtl *** TM-00012892-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x343c0e54, ktcdix=2147483647, topxcb=0x343c0e54
        ktcipt(topxcb)=0x0
*** 2012-05-17 19:05:52.411
ksucti: init txn DID from session DID
ksqgtl:
        ksqlkdid: 0001-0013-0000000F
*** 2012-05-17 19:05:52.429
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0013-0000000F
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0013-0000000F
ksqgtl: RETURNS 0
*** 2012-05-17 19:05:52.430
ksqgtl *** TM-00012894-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x343c0e54, ktcdix=2147483647, topxcb=0x343c0e54
        ktcipt(topxcb)=0x0
*** 2012-05-17 19:05:52.430
ksucti: init session DID from txn DID:
ksqgtl:
        ksqlkdid: 0001-0013-0000000F
*** 2012-05-17 19:05:52.430
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0013-0000000F
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0013-0000000F
ksqgtl: RETURNS 0
*** 2012-05-17 19:05:52.431
ksqgtl *** TX-00050019-00000307 mode=6 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x343c0e54, ktcdix=2147483647, topxcb=0x343c0e54
        ktcipt(topxcb)=0x0
*** 2012-05-17 19:05:52.431
ksucti: init session DID from txn DID:
ksqgtl:
        ksqlkdid: 0001-0013-0000000F
*** 2012-05-17 19:05:52.431
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0013-0000000F
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0013-0000000F
ksqgtl: RETURNS 0
SQL> SELECT TO_NUMBER(12892,'xxxxxxx') from dual;
TO_NUMBER(12892,'XXXXXXX')
--------------------------
                     75922
SQL> SELECT TO_NUMBER(12894,'xxxxxxx') from dual;
TO_NUMBER(12894,'XXXXXXX')
--------------------------
                     75924
SQL> select object_name from dba_objects where object_id in(75922,75924);
OBJECT_NAM
----------
T_P
T_F

通过锁使用情况跟踪可以知道,在主键表插入一条记录时,先在主键表获得TM锁,然后外键表获得TM锁,最后主键表获得TX MODE=6的锁。

跟踪外键表插入数据

*** 2012-05-17 19:49:24.912
ksqgtl *** TM-00012892-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8
        ktcipt(topxcb)=0x0
*** 2012-05-17 19:49:24.912
ksucti: init txn DID from session DID
ksqgtl:
        ksqlkdid: 0001-0015-00000064
*** 2012-05-17 19:49:24.913
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0015-00000064
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0015-00000064
ksqgtl: RETURNS 0
*** 2012-05-17 19:49:24.913
ksqgtl *** TM-00012894-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8
        ktcipt(topxcb)=0x0
*** 2012-05-17 19:49:24.913
ksucti: init session DID from txn DID:
ksqgtl:
        ksqlkdid: 0001-0015-00000064
*** 2012-05-17 19:49:24.913
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0015-00000064
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0015-00000064
ksqgtl: RETURNS 0
*** 2012-05-17 19:49:24.913
ksqgtl *** TX-0002001f-0000034a mode=6 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8
        ktcipt(topxcb)=0x0
*** 2012-05-17 19:49:24.913
ksucti: init session DID from txn DID:
ksqgtl:
        ksqlkdid: 0001-0015-00000064
*** 2012-05-17 19:49:24.914
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0015-00000064
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0015-00000064
ksqgtl: RETURNS 0
*** 2012-05-17 19:49:24.914
ksqgtl *** TX-00050019-00000307 mode=4 flags=0x10021 timeout=21474836 ***
ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8
        ktcipt(topxcb)=0x0
*** 2012-05-17 19:49:24.914
ksucti: init session DID from txn DID:
ksqgtl:
        ksqlkdid: 0001-0015-00000064
*** 2012-05-17 19:49:24.914
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0015-00000064
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0015-00000064
*** 2012-05-17 19:49:24.914
ksqcmi: TX,50019,307 mode=4 timeout=21474836

从这里可以发现:先在主键表和外键表上加上TM锁,然后外键表获得TX MODE=6的锁(这边成功,因为该表上未有其他级别不兼容锁),再需要在主键表上获得TX MODE=4(表结构共享锁+所有记录共享锁),但是这个时候,发现该锁上已经在主键表插入数据未提交的时候,已经含有了TX MODE=6的锁,从而使得TX MODE=4无法获得,从而使得外键表插入数据处于阻塞状态.

ORA-07445 [ACCESS_VIOLATION] [UNABLE_TO_READ] []

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-07445 [ACCESS_VIOLATION] [UNABLE_TO_READ] []

alert中发现ORA-07445错误
ORA-07445: exception encountered: core dump [PC:0x7FFF65D0] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFF] [PC:0x7FFF65D0] [UNABLE_TO_READ] []错误,导致数据库down掉

Mon May 14 14:34:34 2012
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFF] [PC:0x7FFF65D0, {empty}]
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p001_1280.trc:
ORA-07445: exception encountered: core dump [PC:0x7FFF65D0] [ACCESS_VIOLATION]
[ADDR:0xFFFFFFFF] [PC:0x7FFF65D0] [UNABLE_TO_READ] []
Mon May 14 14:34:35 2012
Trace dumping is performing id=[cdmp_20120514143435]
Mon May 14 14:35:10 2012
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFF] [PC:0x7FFF65D0, {empty}]
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_1072.trc  (incident=164712):
ORA-07445: exception encountered: core dump [PC:0x7FFF65D0] [ACCESS_VIOLATION]
[ADDR:0xFFFFFFFF] [PC:0x7FFF65D0] [UNABLE_TO_READ] []
ORA-12080: Buffer cache miss for IOQ batching
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_164712\orcl_smon_1072_i164712.trc

分析trace文件

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows Server 2003 Version V5.2 Service Pack 2
CPU                 : 8 - type 586, 4 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:4892M/8189M, Ph+PgF:5638M/9795M, VA:925M/4095M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 12
Windows thread id: 1072, image: ORACLE.EXE (SMON)
--以上信息得出操作系统和数据库版本2003 sp2+oracle11g(11.1.0.6 32位)
Dump continued from file: d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_1072.trc
ORA-07445: exception encountered: core dump [PC:0x7FFF65D0] [ACCESS_VIOLATION]
[ADDR:0xFFFFFFFF] [PC:0x7FFF65D0] [UNABLE_TO_READ] []
ORA-12080: Buffer cache miss for IOQ batching
========= Dump for incident 164712 (ORA 7445 [PC:0x7FFF65D0]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFF] [PC:0x7FFF65D0, {empty}]
--这里的ORA-07445 [ACCESS_VIOLATION][UNABLE_TO_READ]根据经验结合这里的32位的环境,
--怀疑是sga使用的内存太多,ORACLE数据库不能读SGA相关内存导致
--在trace中找出相关参数配置.
[0004]: processes=300
[0004]: sessions=335
[0004]: __shared_pool_size=1124073472
[0004]: __large_pool_size=8388608
[0004]: __java_pool_size=16777216
[0004]: __streams_pool_size=251658240
[0004]: streams_pool_size=251658240
[0004]: sga_target=0
[0004]: __sga_target=1887436800
[0004]: memory_target=3145728000
[0004]: memory_max_target=4722786304
[0004]: db_block_size=8192
[0004]: __db_cache_size=478150656
[0004]: __shared_io_pool_size=0
[0004]: compatible=11.1.0.0.0
[0004]: log_buffer=8851456
[0004]: __pga_aggregate_target=780140544
--这里可以看到sga_target分配了内存为1887436800=1.7578125G
--pga_aggregate_target分配了780140544=0.7265625G
--两者内存之和大于2G,超过了32位ORACLE默认限制

查询MOS发现[1341681.1]
该错误原因

This is a resource issue (memory in particular). 32-bit windows systems,
are limited to 2GB of addressable memory so if you are on this platform
it's likely you are simply exceeding the capabilities of the 32bit operating system.

解决建议

First recommendation :
If you have not already done so, add the /3GB switch to your boot.ini file and reboot the server. The
boot.ini will be located in the root directory on the drive where windows is installed. The switch, /3GB,
is placed at the end of the line that executes the WinNT loading process.
This will allow applications such as oracle access to 3Gb or memory instead of 2Gb.
Example:
[operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00" /3GB
 Second recommendation :
You do not want to increase memory target. If anything, this should be decreased.
You are limited to under 2GB of addressable memory on 32bit windows (the limit is actually about 1.85GB).
This is for both SGA and PGA memory for all instances; you have to reduce the SGA size for the instance.
The recommendation is to reduce sga_target, memory_target, and memory_max_target.

ORACLE在AIX中产生SOFTWARE PROGRAM ABNORMALLY TERMINATED警告原因

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE在AIX中产生SOFTWARE PROGRAM ABNORMALLY TERMINATED警告原因

数据库中发现如下错误
该错误的解决方案:ORA-07445[dbgrmqmqpk_query_pick_key()+0f88]

Dump file /oracle/diag/rdbms/sgerp5/sgerp5/incident/incdir_579300/sgerp5_m000_7602504_i579300.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/db_1
System name:    AIX
Node name:  sgerp5
Release:    1
Version:    6
Machine:    00C8F0564C00
Instance name: sgerp5
Redo thread mounted by this instance: 1
Oracle process number: 138
Unix process pid: 7602504, image: oracle@sgerp5 (m000)
*** 2012-05-11 03:52:35.200
*** SESSION ID:(752.5029) 2012-05-11 03:52:35.200
*** CLIENT ID:() 2012-05-11 03:52:35.200
*** SERVICE NAME:(SYS$BACKGROUND) 2012-05-11 03:52:35.200
*** MODULE NAME:(MMON_SLAVE) 2012-05-11 03:52:35.200
*** ACTION NAME:(Auto-Purge Slave Action) 2012-05-11 03:52:35.200
Dump continued from file: /oracle/diag/rdbms/sgerp5/sgerp5/trace/sgerp5_m000_7602504.trc
ORA-07445: exception encountered: core dump [dbgrmqmqpk_query_pick_key()+0f88]
[SIGSEGV] [ADDR:0xB38F0000000049][PC:0x100213C08] [Address not mapped to object] []

errpt错误说明
在产生7445错误的同时观察aix系统错误日志发现SOFTWARE PROGRAM ABNORMALLY TERMINATED错误

sgerp5_[oracle]-->errpt -aj A924A5FC
---------------------------------------------------------------------------
LABEL:          CORE_DUMP
IDENTIFIER:     A924A5FC
Date/Time:       Fri May 11 03:52:55 BEIST 2012
Sequence Number: 471
Machine Id:      00C8F0564C00
Node Id:         sgerp5
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSPROC
Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED
Probable Causes
SOFTWARE PROGRAM
User Causes
USER GENERATED SIGNAL
        Recommended Actions
        CORRECT THEN RETRY
Failure Causes
SOFTWARE PROGRAM
        Recommended Actions
        RERUN THE APPLICATION PROGRAM
        IF PROBLEM PERSISTS THEN DO THE FOLLOWING
        CONTACT APPROPRIATE SERVICE REPRESENTATIVE
Detail Data
SIGNAL NUMBER
           6
USER'S PROCESS ID:
               7602504
FILE SYSTEM SERIAL NUMBER
          14
INODE NUMBER
           0      367648
CORE FILE NAME
/oracle/diag/rdbms/sgerp5/sgerp5/cdump/core_7602504/core
PROGRAM NAME
oracle
STACK EXECUTION DISABLED
           0
COME FROM ADDRESS REGISTER
sskgmcrea 0
PROCESSOR ID
  hw_fru_id: 1
  hw_cpu_id: 2
ADDITIONAL INFORMATION
skgdbgcra 224
??
ksdbgcra 3D0
ssexhd 978
??
Symptom Data
REPORTABLE
1
INTERNAL ERROR
SYMPTOM CODE
PCSS/SPI2 FLDS/oracle SIG/6 FLDS/skgdbgcra VALU/224

错误原因

This error is logged when a software program abnormally ends and causes a core dump. Users might
not be exiting applications correctly, the system might have been shut down while users were
working in application, or the user's terminal might have locked up and the application stopped
1)这里也就是说如果oracle进程在aix机器上异常终止,并且产生了一个core dump文件,
  就会出现SOFTWARE PROGRAM ABNORMALLY TERMINATED警告信息
2)用户登录系统没有正常退出,而系统被关闭
3)用户强制终止一个一个lock,而导致进程停止

本次AIX日志警告原因:由于进程7602504异常终止(ORA-07445错误)并且产生了 /oracle/diag/rdbms/sgerp5/sgerp5/cdump/core_7602504/core dump 文件,从而有了AIX中的SOFTWARE PROGRAM ABNORMALLY TERMINATED警告信息

分类目录归档：Oracle