分类目录归档:ORA-xxxxx

ORA-01075: you are currently logged on

rm删除文件后alert中出现错误

Mon Apr 16 21:36:59 2012
Errors in file /home/oracle/oracle/admin/XGS/bdump/xgs_j000_1349.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

数据库进程还在运行

oracle     779     1  0 21:21 ?        00:00:01 ora_pmon_XGS
oracle     781     1  0 21:21 ?        00:00:10 ora_psp0_XGS
oracle     783     1  0 21:21 ?        00:00:00 ora_mman_XGS
oracle     785     1  0 21:21 ?        00:00:00 ora_dbw0_XGS
oracle     787     1  0 21:21 ?        00:00:00 ora_lgwr_XGS
oracle     789     1  0 21:21 ?        00:00:00 ora_ckpt_XGS
oracle     791     1  0 21:21 ?        00:00:00 ora_smon_XGS
oracle     793     1  0 21:21 ?        00:00:00 ora_reco_XGS
oracle     795     1  0 21:21 ?        00:00:00 ora_cjq0_XGS
oracle     797     1  0 21:21 ?        00:00:01 ora_mmon_XGS
oracle     799     1  0 21:21 ?        00:00:00 ora_mmnl_XGS
oracle     801     1  0 21:21 ?        00:00:00 ora_d000_XGS
oracle     803     1  0 21:21 ?        00:00:00 ora_s000_XGS

尝试登陆数据库

[oracle@dbtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 16 21:40:06 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01075: you are currently logged on


Enter user-name: sys
Enter password: 
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/oracle/oradata/XGS/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

问题原因

Internal triggers are trying to fire but one or more datafiles for the SYSAUX tablespace is offline, 
this is preventing the database from allowing new connections.

NOTE: At this point, you cannot connect to verify the status in V$DATAFILE, 
but you may find an indication of the offline datafile(s) in the alert.log file.

For example:
In one case, a media problem occurred which made disks unavailable.
This caused several files to be taken offline automatically including a SYSAUX datafile.

解决方法
kill进程,重启数据库到mount状态,然后根据特定情况恢复数据库或者online相关文件

发表在 ORA-xxxxx | 标签为 | 一条评论

记录一次ORA-00600[kdsgrp1]分析

数据库版本

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 IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

找出报错对象

--方法1
*** SESSION ID:(795.16405) 2012-04-05 09:36:11.958
            row 080095ee.26 continuation at
            file# 32 block# 38382 slot 39 not found
**************************************************
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 39 ..... nrows: 19
**************************************************

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 32
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 32
Enter value for block_id: 38382
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 38382 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
AHV8
TBL_IVR_LOG
TABLE PARTITION    CSS_PARTITION                  IVR_LOG_2012_MONTH04

--方法2
*** 2012-04-05 09:36:11.965
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO TBL_CONTACT_INFO_FAILED_TMP 
select * from TBL_IVR_LOG

SO: 70000017f954f50, type: 4, owner: 70000017f65a840, flag: INIT/-/-/0x00
(session) sid: 795 trans: 70000017464a1e8, creator: 70000017f65a840, flag: (40110041) USR/- BSY/-/-/-/-/-
              DID: 0002-0067-000305BD, short-term DID: 0002-0067-000305BE
              txn branch: 0
              oct: 2, prv: 0, sql: 70000015180ee98, psql: 700000180d67550, user: 49/AHV8
service name: SYS$USERS
O/S info: user: oracle10, term: UNKNOWN, ospid: 12976218, machine: zwq_kfdb2
              program: oracle@zwq_kfdb2 (J002)
last wait for 'db file sequential read' blocking sess=0x0 seq=226 wait_time=17071 seconds since wait started=1
                file#=20, block#=95ee, blocks=1

--方法3
Block header dump:  0x080095ee
 Object id on Block? Y
 seg/obj: 0x11eeb  csc: 0x6f2.848e814  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x7c09c89 ver: 0x01 opc: 0
     inc: 0  exflg: 0


SQL> select to_number('11eeb','xxxxxxxx') from dual;

TO_NUMBER('11EEB','XXXXXXXX')
-----------------------------
                        73451

SQL> select owner,object_name,subobject_name,object_type from dba_objects where data_object_id='73451';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                 OBJECT_TYPE
------------------------------ -------------------
AHV8
TBL_IVR_LOG
IVR_LOG_2012_MONTH04           TABLE PARTITION

验证是否真的坏块

SQL> select name from v$datafile where file#=32;

NAME
------------------------------------------------------
/dev/rdb1_data27

[zwq_kfdb2:/home/oraeye]dbv file='/dev/rdb1_data27' blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Apr 13 15:33:10 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /dev/rdb1_data27
  

DBVERIFY - Verification complete

Total Pages Examined         : 1048448
Total Pages Processed (Data) : 947357
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4756
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 96335
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 297329920 (1778.297329920)

SQL> select count(*) from AHV8.TBL_IVR_LOG partition(IVR_LOG_2012_MONTH04);

  COUNT(*)
----------
   8798030

总结:很明显这次出现这个问题,因为内存中出现坏块导致,经过一段时间buffer cache中的坏块内容已经被老化,所以现在不能重现(甚至不用做任何操作)。如果内存中出现了坏块,而且还没有被老化掉,可以刷新data buffer;如果是数据块出现坏块,根据实际情况决定处理

发表在 ORA-xxxxx | 标签为 | 一条评论

ORA-00600[729]分析和处理方法

alert中ORA-00600[729]

Fri Apr  6 04:30:04 2012
Errors in file /oracle9/app/admin/crm/udump/crm2_ora_2548236.trc:
ORA-00600: internal error code, arguments: [729], [1067976], [space leak], [], [], [], [], []

a. the first bracketed number [729] is the common argument for space leak problems.
b. the second number [1067976] is the number of bytes leaked by the error.
c. the third argument is always [space leak].

分析trace文件

*** 2012-04-06 04:30:04.656
*** SESSION ID:(1361.35607) 2012-04-06 04:30:04.648
******** ERROR: UGA memory leak detected 1067976 ********
******************************************************
HEAP DUMP heap name="session heap"  desc=1103a05f0

a. the memory was leaked from the UGA area
b. the amount leaked is reported again in the text (1067976 bytes).
c. the above few lines describe this dump as SESSION HEAP with the descriptor 0x1103a05f0.

计算泄露内存大小

******************************************************
HEAP DUMP heap name="session heap"  desc=1103a05f0
 extent sz=0xff80 alt=32767 het=32767 rec=0 flg=3 opc=3
 parent=110009628 owner=700000c3b6f5620 nex=0 xsz=0xff80
EXTENT 0 addr=1107dbf50
  Chunk        1107dbf60 sz=    65392    free      "               "
EXTENT 1 addr=1107cbf50
  Chunk        1107cbf60 sz=    65392    free      "               "
EXTENT 2 addr=110541da0
  Chunk        110541db0 sz=    61312    free      "               "
EXTENT 3 addr=11062ae88
  Chunk        11062ae98 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 4 addr=1105dae88
  Chunk        1105dae98 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 5 addr=110550d48
  Chunk        110550d58 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 6 addr=110500d48
  Chunk        110500d58 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 7 addr=1104e1df0
  Chunk        1104e1e00 sz=      200    perm      "perm           "  alo=200
  Chunk        1104e1ec8 sz=    65192    free      "               "
EXTENT 8 addr=1104c1df0
  Chunk        1104c1e00 sz=    40720    perm      "perm           "  alo=40720
  Chunk        1104cbd10 sz=       56    free      "               "
  Chunk        1104cbd48 sz=      408    freeable  "kcbl_structure_"
  Chunk        1104cbee0 sz=     6952    free      "               "
  Chunk        1104cda08 sz=     2424    freeable  "kllcqc:kllcqslt"
  Chunk        1104ce380 sz=    14832    free      "               "
EXTENT 9 addr=1104d1df0
  Chunk        1104d1e00 sz=    65392    free      "               "
EXTENT 10 addr=1104b1df0
  Chunk        1104b1e00 sz=      544    free      "               "
  Chunk        1104b2020 sz=       88    freeable  "kllcqc:kllcq   "
  Chunk        1104b2078 sz=    64760    free      "               "
EXTENT 11 addr=110427390
  Chunk        1104273a0 sz=    65392    free      "               "
EXTENT 12 addr=110417390
  Chunk        1104173a0 sz=    65392    free      "               "
EXTENT 13 addr=110407390
  Chunk        1104073a0 sz=    65392    free      "               "
EXTENT 14 addr=1103f7390
  Chunk        1103f73a0 sz=    65392    free      "               "
EXTENT 15 addr=1103e7390
  Chunk        1103e73a0 sz=    65392    free      "               "
EXTENT 16 addr=1103d7390
  Chunk        1103d73a0 sz=    65392    free      "               "
EXTENT 17 addr=1103c7390
  Chunk        1103c73a0 sz=      408    free      "               "
  Chunk        1103c7538 sz=     2232    perm      "perm           "  alo=2232
  Chunk        1103c7df0 sz=    62752    free      "               "
EXTENT 18 addr=1103b7390
  Chunk        1103b73a0 sz=    65392    free      "               "
EXTENT 19 addr=110370080
  Chunk        110370090 sz=     2008    perm      "perm           "  alo=2008
  Chunk        110370868 sz=    63384    free      "               "
EXTENT 20 addr=110360098
  Chunk        1103600a8 sz=    20424    perm      "perm           "  alo=20424
  Chunk        110365070 sz=    44944    free      "               "
Total heap size    =  2172616
FREE LISTS:
 Bucket 0 size=56
  Chunk        1104cbd10 sz=       56    free      "               "
 Bucket 1 size=88
 Bucket 2 size=152
 Bucket 3 size=168
 Bucket 4 size=280
  Chunk        1103c73a0 sz=      408    free      "               "
 Bucket 5 size=432
 Bucket 6 size=536
  Chunk        1104b1e00 sz=      544    free      "               "
 Bucket 7 size=1048
 Bucket 8 size=2072
 Bucket 9 size=4120
  Chunk        1104cbee0 sz=     6952    free      "               "
 Bucket 10 size=8216
  Chunk        1104ce380 sz=    14832    free      "               "
 Bucket 11 size=16408
 Bucket 12 size=32792
  Chunk        110365070 sz=    44944    free      "               "
  Chunk        110370868 sz=    63384    free      "               "
  Chunk        1104d1e00 sz=    65392    free      "               "
  Chunk        1103b73a0 sz=    65392    free      "               "
  Chunk        1103c7df0 sz=    62752    free      "               "
  Chunk        1103d73a0 sz=    65392    free      "               "
  Chunk        1103f73a0 sz=    65392    free      "               "
  Chunk        1104073a0 sz=    65392    free      "               "
  Chunk        1104b2078 sz=    64760    free      "               "
  Chunk        1103e73a0 sz=    65392    free      "               "
  Chunk        1104e1ec8 sz=    65192    free      "               "
  Chunk        1104273a0 sz=    65392    free      "               "
  Chunk        1104173a0 sz=    65392    free      "               "
  Chunk        1107cbf60 sz=    65392    free      "               "
  Chunk        110541db0 sz=    61312    free      "               "
  Chunk        1107dbf60 sz=    65392    free      "               "
 Bucket 13 size=65560
 Bucket 14 size=131096
 Bucket 15 size=262168
 Bucket 16 size=524312
 Bucket 17 size=2097176
Total free space   =  1039056
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
  Chunk        1104e1e00 sz=      200    perm      "perm           "  alo=200
  Chunk        1104c1e00 sz=    40720    perm      "perm           "  alo=40720
  Chunk        1103c7538 sz=     2232    perm      "perm           "  alo=2232
  Chunk        110370090 sz=     2008    perm      "perm           "  alo=2008
  Chunk        1103600a8 sz=    20424    perm      "perm           "  alo=20424
Permanent space    =    65584
******************************************************

FREEABLE and RECREATABLE chunks总和等于1067976 byte(leaked memory)

会话状态分析

*** 2012-04-06 04:30:04.658
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [729], [1067976], [space leak], [], [], [], [], []
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014             
kgeriv+0118          bl       _ptrgl               
kgesiv+0080          bl       kgeriv               000000001 ? 000000002 ?
                                                   1100610D0 ? 000000000 ?
                                                   00000000A ?
ksesic2+005c         bl       kgesiv               FFFFFFFFFFF9320 ? 1101FAF78 ?
                                                   110006308 ? 1103A0818 ?
                                                   000000009 ?
ksmuhe+026c          bl       ksesic2              2D9000002D9 ? 000000000 ?
                                                   000104BC8 ? 000000001 ?
                                                   00000000A ? 103164968 ?
                                                   12E0BE826D694B2F ?
                                                   000000000 ?
ksmugf+0214          bl       ksmuhe               110002A20 ? 110061238 ?
                                                   000000009 ? 102975DE8 ?
ksuxds+170c          bl       ksmugf               000000000 ? 020000000 ?
                                                   1029754D0 ?
ksudel+006c          bl       ksuxds               700000C3B6F5620 ? 100000001 ?
opilof+03dc          bl       01FD427C             <--表示logoff
opiodr+08cc          bl       _ptrgl               
ttcpip+0cc4          bl       _ptrgl               
opitsk+0d60          bl       ttcpip               11000CF90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl               
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF7B0 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF7B0 ?
main+0138            bl       01FD3A28             
__start+0098         bl       main                 000000000 ? 000000000 ?
 
--------------------- Binary Stack Dump ---------------------
………………
 ----------------------------------------
    SO: 700000c3b6f5620, type: 4, owner: 700000c3c987a18, flag: INIT/-/-/0x00
--flag: (41) USR/- BSY/-/-/DEL/-/- shows that the session has been deleted
    (session) trans: 0, creator: 700000c3c987a18, flag: (41) USR/- BSY/-/-/DEL/-/- 
              DID: 0002-0927-01D67CAD, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 700000caf2c0e30, psql: 700000caf2c0e30, user: 52/MONITOR
    O/S info: user: oracrm, term: , ospid: 1490968, machine: zwq_crm2
              program: exp@zwq_crm2 (TNS V1-V3)
    last wait for 'SQL*Net message from client' blocking sess=0x0 seq=59222 wait_time=1537
                driver id=54435000, #bytes=1, =0
    temporary object counter: 0
    ----------------------------------------

a.在logoff的时候发生UGA中的session heap发生内存泄露
b.该进程是一个exp导出数据库程序,并且该程序已经被释放

出现ORA-00600[729]原因

Memory leak problems generally occur when Oracle is trying to free memory allocated to a process. 
The memory leak dump is generally discovered during session logoff, 
when Oracle frees the heaps that are allocated for the user process. 

When a user connects to Oracle, a user process is created and at that time the heap is allocated. 
Every process will have its own memory heap.

The memory is organized in to heaps and every heap consists of one or more extents. 
Each extent contains a series of contiguous memory chunks, and these chunks can be 
either FREE or ALLOCATED. The Generic Heap Manager takes care of allocating and deallocating
 the memory chunks, with the help of FREE LISTS and LRU LISTS. 

Chunk types are as follows:

1. FREE 
2. FREEABLE 
3. RECREATABLE 
4. PERMANENT 
5. FREEABLE WITH MARK 

It is not mandatory that each extent contain only one type of chunk. 
Extents can contain various types of chunks. When processes require memory chunks, 
they are allocated as needed. Oracle keeps track of the amount of memory allocated for the process internally.

When the process terminates, all of the memory that has been allocated for the process is automatically released. 
When the memory is released the allocated heaps are freed. Generally, 
when the heap is freed the only chunks that the process should identify 
as allocated are the PERMANENT chunks and FREE chunks on the freelist. 
If the process finds there are still FREEABLE or RECREATABLE chunks remaining, 
then the process has not properly deallocated the memory. 
This situation is considered a space leak. 

ORA-00600[729]处理方案

1. If there are no other errors reported at the same time, 
this may be a case where the error was a rare occurrence and can be safely ignored. 
As a rule of thumb, leaks less than 90,000 bytes in size are considered to be of low significance. 
The solution in this case is to set event 10262 (see below). 

a. Set the following event in init.ora parameter file. 
   This example disables reporting for space leaks less than 90000 bytes:
event = "10262 trace name context forever, level 90000"

b. Stop and restart the database.

If the level is set to 1, space leak checking is disabled. 
This is not advised because large memory leaks will be missed.

If the event is set to a value greater than 1, 
any space leak up to the number specified in the event is ignored.

2. Is the leak in the SGA? The alert.log should be reviewed for additional 
errors such as ORA-4030 and ORA-4031 to ensure there are no additional 
problems with the shared pool or operating system memory. 

3. Does the error reproduce with a given task? If so, this is 
a case that should be investigated further because the leak could be a known bug. 
See Note 31056.1 ORA-600 [729] UGA Space Leak for a list of known bugs and fixes.

参考:Understanding and Diagnosing ORA-600 [729] Space Leak Errors [ID 403584.1]

发表在 ORA-xxxxx | 标签为 | 一条评论