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

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

标题:记录一次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-00600[729]分析和处理方法

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

标题: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-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated

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

标题:ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

出现如下错误(ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated)

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_bill_1
Release:        1
Version:        6
Machine:        00F64FF34C00
Instance name: bill1
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 46531060, image: oracle@zwq_bill_1 (J000)
*** SESSION ID:(218.47085) 2012-04-02 19:30:45.561
*** 2012-04-02 19:30:45.561
ORA-12012: error on auto execute of job 1
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1

这个是oracle的一个Bug 2784796,提供解决方法有
1.run the statspack at level 0
2.restart the instance
3.set cursor sharing to exact (probably not feasible)
4.禁用主键,创建合适非唯一index

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY
CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

5.修改STATS$V_$SQLXS视图
分析思路如下:
1)根据主键冲突找到主键包含列(spctab.sql)

create table          STATS$SQL_SUMMARY
(snap_id              number(6)        not null
,dbid                 number           not null
,instance_number      number           not null
,text_subset          varchar2(31)     not null
,sql_text             varchar2(1000)
,sharable_mem         number
,sorts                number
,module               varchar2(64)
,loaded_versions      number
,fetches              number
,executions           number
,loads                number
,invalidations        number
,parse_calls          number
,disk_reads           number
,buffer_gets          number
,rows_processed       number
,command_type         number
,address              raw(8)
,hash_value           number
,version_count        number
,cpu_time             number
,elapsed_time         number
,outline_sid          number
,outline_category     varchar2(64)
,child_latch          number
--注意下面5列构成主键
,constraint STATS$SQL_SUMMARY_PK primary key
    (snap_id, dbid, instance_number, hash_value, text_subset)
 using index tablespace &&tablespace_name
   storage (initial 1m next 1m pctincrease 0)
,constraint STATS$SQL_SUMMARY_FK foreign key (snap_id, dbid, instance_number)
                references STATS$SNAPSHOT on delete cascade
)tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;

2)找到该表插入数据(spcpkg.sql)

insert into stats$sql_summary
            ( snap_id
            , dbid
            , instance_number
            , text_subset
            , sharable_mem
            , sorts
            , module
            , loaded_versions
            , fetches
            , executions
            , loads
            , invalidations
            , parse_calls
            , disk_reads
            , buffer_gets
            , rows_processed
            , command_type
            , address
            , hash_value
            , version_count
            , cpu_time
            , elapsed_time
            , outline_sid
            , outline_category
            , child_latch
            )
       select l_snap_id
            , p_dbid
            , p_instance_number
            , substrb(sql_text,1,31)
            , sharable_mem
            , sorts
            , module
            , loaded_versions
            , fetches
            , executions
            , loads
            , invalidations
            , parse_calls
            , disk_reads
            , buffer_gets
            , rows_processed
            , command_type
            , address
            , hash_value
            , version_count
            , cpu_time
            , elapsed_time
            , outline_sid
            , outline_category
            , child_latch
         from stats$v$sqlxs
        where is_obsolete = 'N'
          and (   buffer_gets   > l_buffer_gets_th
               or disk_reads    > l_disk_reads_th
               or parse_calls   > l_parse_calls_th
               or executions    > l_executions_th
               or sharable_mem  > l_sharable_mem_th
               or version_count > l_version_count_th
              );

3)找出stats$v$sqlxs对象(spcusr.sql)

create or replace view STATS$V_$SQLXS as
select max(sql_text)        sql_text
     , sum(sharable_mem)    sharable_mem
     , sum(sorts)           sorts
     , min(module)          module
     , sum(loaded_versions) loaded_versions
     , sum(fetches)         fetches
     , sum(executions)      executions
     , sum(loads)           loads
     , sum(invalidations)   invalidations
     , sum(parse_calls)     parse_calls
     , sum(disk_reads)      disk_reads
     , sum(buffer_gets)     buffer_gets
     , sum(rows_processed)  rows_processed
     , max(command_type)    command_type
     , address              address
     , hash_value           hash_value
     , count(1)             version_count
     , sum(cpu_time)        cpu_time
     , sum(elapsed_time)    elapsed_time
     , max(outline_sid)     outline_sid
     , max(outline_category) outline_category
     , max(is_obsolete)     is_obsolete
     , max(child_latch)     child_latch
  from v$sql
 group by hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

4)通过这里可以看出,要是的STATS$SQL_SUMMARY主键不重复,只要是的STATS$V_$SQLXS查询出来的记录唯一,所以解决方案就是在STATS$V_$SQLXS视图中增加下列条件,确保查询出来的记录唯一,从而不会发生主键冲突

where
( plan_hash_value > 0
or executions > 0
or parse_calls > 0
or disk_reads > 0
or buffer_gets > 0
)

该bug在10g中修复,对于不能及时升级的数据库,建议采用第五种方法解决问题,比较治标治本,对业务基本上无影响

linux中不能ping通hostname可能存在问题

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

标题:linux中不能ping通hostname可能存在问题

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

不能ping通hostname

[oracle@xifenfei ~]$ hostname
xifenfei
[oracle@xifenfei ~]$ ping xifenfei
ping: unknown host xifenfei
[oracle@xifenfei ~]$ nslookup
> xifenfei
;; connection timed out; no servers could be reached
> exit
[oracle@xifenfei ~]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1    xifenfei1 localhost.localdomain localhost

10G中存在问题

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 3 01:54:22 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
--sqlplus连接非常慢
SQL> startup
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
--ORA-00600[keltnfy-ldmInit]错误

11G中存在问题

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 3 02:02:29 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))'
--因为不能ping通xifenfei,提示LOCAL_LISTENER错误
--修改pfile文件
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))'
--启动数据库
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             306186760 bytes
Database Buffers           54525952 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
[oracle@xifenfei ~]$ ps -ef|grep ora_
oracle    5960     1  0 02:09 ?        00:00:00 ora_pmon_ora11g
oracle    5964     1  0 02:09 ?        00:00:00 ora_psp0_ora11g
oracle    5970     1  1 02:09 ?        00:00:00 ora_vktm_ora11g
oracle    5976     1  0 02:09 ?        00:00:00 ora_gen0_ora11g
oracle    5980     1  0 02:09 ?        00:00:00 ora_diag_ora11g
oracle    5984     1  0 02:09 ?        00:00:00 ora_dbrm_ora11g
oracle    5988     1  0 02:09 ?        00:00:00 ora_dia0_ora11g
oracle    5992     1  0 02:09 ?        00:00:00 ora_mman_ora11g
oracle    5996     1  0 02:09 ?        00:00:00 ora_dbw0_ora11g
oracle    6000     1  0 02:09 ?        00:00:00 ora_lgwr_ora11g
oracle    6004     1  0 02:09 ?        00:00:00 ora_ckpt_ora11g
oracle    6008     1  0 02:09 ?        00:00:00 ora_smon_ora11g
oracle    6012     1  0 02:09 ?        00:00:00 ora_reco_ora11g
oracle    6016     1  0 02:09 ?        00:00:00 ora_mmon_ora11g
oracle    6020     1  0 02:09 ?        00:00:00 ora_mmnl_ora11g
oracle    6028     1  0 02:09 ?        00:00:00 ora_s000_ora11g
oracle    6055     1  0 02:10 ?        00:00:00 ora_p000_ora11g
oracle    6059     1  0 02:10 ?        00:00:00 ora_p001_ora11g
oracle    6063     1  0 02:10 ?        00:00:00 ora_arc0_ora11g
oracle    6069     1  0 02:10 ?        00:00:00 ora_arc1_ora11g
oracle    6073     1  0 02:10 ?        00:00:00 ora_arc2_ora11g
oracle    6077     1  0 02:10 ?        00:00:00 ora_arc3_ora11g
oracle    6081     1  0 02:10 ?        00:00:00 ora_qmnc_ora11g
oracle    6089     1  0 02:10 ?        00:00:00 ora_q000_ora11g
oracle    6093     1  0 02:10 ?        00:00:00 ora_q001_ora11g
oracle    6141     1  0 02:11 ?        00:00:00 ora_d000_ora11g
oracle    6145     1  2 02:11 ?        00:00:00 ora_cjq0_ora11g
--数据库启动正常
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 3 02:10:37 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected.
SQL> show parameter local_listener;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
--sqlplus不能操作,而且sqlplus登录非常慢

监听异常

--监听配置文件
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
--启动监听
[oracle@xifenfei ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-APR-2012 02:19:52
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Starting /u01/oracle/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/oracle/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1522)))
--一直处于等待状态
--客户端登录
C:\Users\XIFENFEI>sqlplus sys/xifenfei@192.168.1.10:1522/ora11g
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 4月 3 12:48:15 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
--一直hang住
[root@xifenfei admin]# netstat -an|grep 1522
tcp        0      0 192.168.1.10:1522           0.0.0.0:*                   LISTEN
tcp      260      0 192.168.1.10:1522           192.168.1.1:51977           ESTABLISHED
tcp        0      0 192.168.1.10:24317          192.168.1.10:1522           ESTABLISHED
tcp      198      0 192.168.1.10:1522           192.168.1.10:24317          ESTABLISHED
--这里显示已经连接

建议:在修改主机名时要慎重,修改的时候要确保/etc/hosts和/etc/sysconfig/network文件都被正确修改

永久表空间出现临时段不能扩展原因探讨

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

标题:永久表空间出现临时段不能扩展原因探讨

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库版本

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

创建5M测试表空间

SQL> CREATE TABLESPACE T_1652 DATAFILE '/tmp/t_1652_01.dbf' size 5M
  2  AUTOEXTEND OFF LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  3  SEGMENT SPACE MANAGEMENT AUTO blocksize 8192;
Tablespace created.

测试CTAS

SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;
create table CHF.T_XIFENFEI TABLESPACE T_1652 as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3000;
Table created.

测试CREATE INDEX

SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
    5242880          0 /tmp/t_1652_01.dbf           5242880
SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=2000;
Table created.
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
    5242880    1048576 /tmp/t_1652_01.dbf           4194304
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 10M;
Database altered.
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
Index created.

测试MOVE

SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;
Table created.
SQL> alter table chf.t_xifenfei move;
alter table chf.t_xifenfei move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
   10485760    4194304 /tmp/t_1652_01.dbf           6291456
SQL>  ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 15M;
Database altered.
SQL> alter table chf.t_xifenfei move;
Table altered.

这里可以发现CTAS,CREATE INDEX,MOVE操作都有个共同点:需要一次性创建一个较大SEGMENT,但是这个SEGMENT的创建过程是在数据库中逐渐实现(非初始化指定大小)。
也就是说,ORACLE对这些对象的处理方法是:对于这样的segment先当作临时段处理,当处理完成后,再把这些在永久表空间中的临时段转换为永久段;所以当这些永久表空间中的临时段在扩展的时候,遇到该永久表空间不足,而该段目前还是临时段(在永久表空间中的临时段),就出现了ORA-01652提示一个永久表空间unable to extend temp segment

表空间online出现ORA-00600[kcbz_check_objd_typ]处理过程

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

标题:表空间online出现ORA-00600[kcbz_check_objd_typ]处理过程

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

online表空间出现ORA-00600[kcbz_check_objd_typ]

Fri Mar 30 14:09:24 2012
alter tablespace xff offline
Fri Mar 30 14:09:28 2012
Completed: alter tablespace xff offline
Fri Mar 30 17:49:59 2012
alter tablespace xff rename datafile '/oradataa/xifenfei.dbf' to '/oradatab/xifenfei_bak.dbf'
Fri Mar 30 17:50:03 2012
Completed: alter tablespace xff rename datafile '/oradataa/xifenfei.dbf' to '/oradatab/xifenfei_bak.dbf'
Fri Mar 30 17:50:03 2012
alter tablespace coweb_bak_new online
Fri Mar 30 17:53:08 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_21275.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []

分析trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/ora10/product
System name:	Linux
Node name:	FH-DB-01
Release:	2.6.18-92.el5
Version:	#1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:	x86_64
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 182
Unix process pid: 21275, image: oracle@FH-DB-01 (TNS V1-V3)
*** 2012-03-30 17:50:16.469
*** ACTION NAME:() 2012-03-30 17:50:15.939
*** MODULE NAME:(sqlplus@FH-DB-01 (TNS V1-V3)) 2012-03-30 17:50:15.939
*** SERVICE NAME:(SYS$USERS) 2012-03-30 17:50:15.939
*** SESSION ID:(921.23041) 2012-03-30 17:50:15.939
*** SESSION ID:(921.23041) 2012-03-30 17:50:15.939
OBJD MISMATCH typ=6, seg.obj=-2, diskobj=294051, dsflg=0, dsobj=294044, tid=294044, cls=1
Input data (nil), 0, 0
Formatted dump of block:
buffer tsn: 10 rdba: 0x0435c094 (1024/70631572)
scn: 0x0b2d.2b4f8874 seq: 0x01 flg: 0x04 tail: 0x88740601
frmt: 0x02 chkval: 0x5626 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000006DBB2000 to 0x000000006DBB4000
06DBB2000 0000A206 0435C094 2B4F8874 04010B2D  [......5.t.O+-...]
06DBB2010 00005626 00000002 00047CA3 2B4F8874  [&V.......|..t.O+]
06DBB2020 00000B2D 00320002 0435C091 00000000  [-.....2...5.....]
06DBB2030 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
06DBB2060 00000000 02800000 00000000 00240000  [..............$.]
06DBB2070 1F3C1F60 00000000 00000000 00000000  [`.<.............]
06DBB2080 00000000 00001F60 00001F5C 00000000  [....`...\.......]
06DBB2090 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
06DBB3FF0 00000000 00000000 00000000 88740601  [..............t.]
Block header dump:  0x0435c094
 Object id on Block? Y
 seg/obj: 0x47ca3  csc: 0xb2d.2b4f8874  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x435c091 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

这里可以得出几个结论:
1)版本平台信息
2)通过下面信息都是知道,data_object_id=294044的对象出现异常(seg.obj=-2)

OBJD MISMATCH typ=6, seg.obj=-2, diskobj=294051, dsflg=0, dsobj=294044, tid=294044, cls=1
Block header dump:  0x0435c094
 Object id on Block? Y
 seg/obj: 0x47ca3  csc: 0xb2d.2b4f8874  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x435c091 ver: 0x01 opc: 0
     inc: 0  exflg: 0

3)结合dba_objects视图查询出来是xifenfei_index 索引出现异常

问题原因猜测
因为没有找到权威解释,对于这里在online表空间的时候出现ORA-00600[kcbz_check_objd_typ]错误,个人的猜测可能是在online的时候,验证数据字典中关于该表空间中对象的相关记录和该表空间总的存储数据比较,然后发现不匹配,所以出现了该错误。

处理方法
虽然该表空间离线,但是我们可以使用drop操作直接删除数据字典中index的记录,然后再online表空间,这样可以绕过数据字典和表空间中的存储比较。当表空间online成功后,然后再创建index

ASMM表空间强制终止DML操作导致ORA-600 [ktspfupdst-1]

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

标题:ASMM表空间强制终止DML操作导致ORA-600 [ktspfupdst-1]

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

发现错误ORA-00600 [ktspfupdst-1],trace关键内容如下

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_crm1
Release:        3
Version:        5
Machine:        00C420B44C00
Instance name: crm1
Redo thread mounted by this instance: 1
Oracle process number: 389
Unix process pid: 1896900, image: oracle@zwq_crm1 (TNS V1-V3)
----------------------------------------------
*** 2012-03-31 02:50:48.509
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktspfupdst-1], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
Current SQL statement for this session:
INSERT INTO XIFENFEI
SELECT * FROM T_XFF
----- 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
kgerinv+00e8         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              000000001 ? 000000000 ?
                                                   000000005 ? 000000001 ?
                                                   000000001 ?
ktspfupdst+0540      bl       kgeasnmierr          110006308 ? 1103994E8 ?
                                                   102A9239C ? 000000000 ?
                                                   000000005 ? 000000010 ?
                                                   000000020 ? 000000006 ?
ktspstchg+00e4       bl       ktspfupdst           000000060 ? 300000004 ?
                                                   FFFFFFFFFFF6E48 ?
                                                   50601CE000000ED ?
                                                   3B401B34C5D02F2A ?
                                                   B92000004000020 ?
kdoiur+062c          bl       ktspstchg            000000000 ? 700000C39D779E8 ?
                                                   000000000 ?
kcoubk+00e4          bl       _ptrgl
ktundo+0988          bl       kcoubk               1010CCD80 ? FFFFFFFFFFF76C0 ?
                                                   100ED51C0 ? FFFFFFFFFFF7150 ?
                                                   1101FAF78 ? 1102567C0 ?
                                                   700000C396A1300 ? 000000002 ?
ktubko+03bc          bl       ktundo               1840DFB30 ?
                                                   3B401B3400000002 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFF85D8 ?
                                                   700000C80A1E880 ? 2FFFF8540 ?
                                                   FFFFFFFFFFF8780 ?
ktuabt+0638          bl       ktubko               DF000000DF ?
                                                   FFFFFFFFFFF8690 ? 000000000 ?
                                                   FFFFFFFFFFF85D8 ? 102973880 ?
                                                   700000C844FA418 ?
ktcrab+02b4          bl       ktuabt               700000C80A1E840 ? 200017CD8 ?
ktcrsp+026c          bl       ktcrab               100F698E4 ? 000000001 ?
ksures+0074          bl       ktcrsp               700000C844FA448 ?
opiexe+3380          bl       01FD4138
opiall0+102c         bl       opiexe               400000000 ? 110002A48 ?
                                                   FFFFFFFFFFFA0A0 ?
kpoal8+0a78          bl       opiall0              5EFFFFBED4 ? 22103A43F8 ?
                                                   FFFFFFFFFFFA5B8 ? 000000000 ?
                                                   FFFFFFFFFFFA508 ? 1103A4B00 ?
                                                   6FF00000738 ?
                                                   24000000007FFF ?
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 ---------------------

这里可以得到信息如下:
1)系统平台aix 5.3,数据库版本9.2.0.8 rac
2)这个错误可能和一个insert select操作,然后取消有关系

表空间管理方式

SQL> SELECT tablespace_name, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces
  2  where tablespace_name=
  3  (select tablespace_name from dba_tables where table_name='XIFENFEI');
TABLESPACE_NAME                SEGMEN
------------------------------ ------
CUSTSERV                       AUTO

查看MOS发现和[ID 388599.1]相符
错误原因:

1. An insert or update on a table causes the addition of a new extent
   and the operation is cancelled.
2. The segment uses Automatic Segment Space Management (ASSM).
3. The call stack in the associated trace file resembles:
    ktspfupdst ktspstchg kdoiur kcoubk ktundo ktubko ktuabt ktcrab ktcrsp

解决方法:
官方给出方案就是升级到新版本,不过可以采取一个比较折中的处理方案,先想办法导出或者备份该对象数据,然后trunate或者重建表和相关index操作,解决该问题

ORA-27103 when Memory target parameter is set to more than 3 GB(11.1.0.7)

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

标题:ORA-27103 when Memory target parameter is set to more than 3 GB(11.1.0.7)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

朋友在数据库软件从11.1.0.6升级到11.1.0.7后,发现数据库无法打开,不能继续下一步升级
数据库启动

SQL> startup upgrade
ORA-03113: end-of-file on communication channel

alert日志

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilecenterdb.ora
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  memory_target            = 12864M
  control_files            = "/u01/app/oracle/oradata/centerdb/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 2G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=centerdbXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/centerdb/adump"
  audit_trail              = "DB"
  db_name                  = "centerdb"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
Thu Mar 29 15:47:06 2012
PMON started with pid=2, OS id=16324
Thu Mar 29 15:47:06 2012
VKTM started with pid=3, OS id=16326 at elevated priority
VKTM running at (20)ms precision
Thu Mar 29 15:47:06 2012
DIAG started with pid=4, OS id=16330
Thu Mar 29 15:47:06 2012
DBRM started with pid=5, OS id=16332
Thu Mar 29 15:47:06 2012
PSP0 started with pid=6, OS id=16334
Thu Mar 29 15:47:06 2012
DIA0 started with pid=7, OS id=16336
Thu Mar 29 15:47:06 2012
MMAN started with pid=8, OS id=16338
Thu Mar 29 15:47:06 2012
DBW0 started with pid=9, OS id=16340
Thu Mar 29 15:47:06 2012
DBW1 started with pid=10, OS id=16342
Thu Mar 29 15:47:06 2012
DBW2 started with pid=11, OS id=16344
Thu Mar 29 15:47:06 2012
DBW3 started with pid=12, OS id=16346
Thu Mar 29 15:47:06 2012
DBW4 started with pid=13, OS id=16348
Thu Mar 29 15:47:06 2012
DBW5 started with pid=14, OS id=16350
Thu Mar 29 15:47:06 2012
LGWR started with pid=15, OS id=16352
Thu Mar 29 15:47:06 2012
CKPT started with pid=16, OS id=16354
Thu Mar 29 15:47:06 2012
SMON started with pid=17, OS id=16356
Thu Mar 29 15:47:06 2012
RECO started with pid=18, OS id=16358
Thu Mar 29 15:47:06 2012
MMON started with pid=19, OS id=16360
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Mar 29 15:47:06 2012
MMNL started with pid=20, OS id=16362
starting up 1 shared server(s) ...
Errors in file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc:
ORA-27103: internal error
Additional information: -1
Additional information: 1
MMAN (ospid: 16338): terminating the instance due to error 27103
Instance terminated by MMAN, pid = 16338

这里可以发现memory_target在12g以上

trace文件内容

[oracle@fcdb trace]$ more /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Trace file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      fcdb
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: centerdb
Redo thread mounted by this instance: 0 <none>
Oracle process number: 8
Unix process pid: 16338, image: oracle@fcdb (MMAN)
*** 2012-03-29 15:47:06.865
*** SESSION ID:(1648.1) 2012-03-29 15:47:06.865
*** CLIENT ID:() 2012-03-29 15:47:06.865
*** SERVICE NAME:() 2012-03-29 15:47:06.865
*** MODULE NAME:() 2012-03-29 15:47:06.865
*** ACTION NAME:() 2012-03-29 15:47:06.865
error 27103 detected in background process
ORA-27103: internal error
Additional information: -1
Additional information: 1
*** 2012-03-29 15:47:06.865
MMAN (ospid: 16338): terminating the instance due to error 27103

结合alert和trace文件查询MOS,发现ORA-27103 when Memory target parameter is set to more than 3 GB [ID 743012.1]描述相符,是由于Bug:7272646引起.
鉴于朋友的数据库还升级过程中,所以给出的处理建议是先把memory_target改为2.8G,执行完升级操作,然后打上Patch:7272646
同时官方还给出了另一种解决方案:设置SHMMAX小于4G,个人不推荐;如果系统内存比较大,会出现多个内存段,影响系统性能

模拟ORA-04043并解决

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

标题:模拟ORA-04043并解决

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

创建两张模拟表

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> create table sys_xifenfei as
  2  select * from dba_tables;
Table created.
SQL> create table chf.chf_xifenfei as
  2  select * from dba_tables;
Table created.

启动数据库到mount状态查询表

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist
SQL> desc sys_xifenfei
ERROR:
ORA-04043: object sys_xifenfei does not exist
SQL> desc chf.chf_xifenfei
ERROR:
ORA-04043: object chf.chf_xifenfei does not exist

打开数据库查询

SQL> alter database open;
Database altered.
SQL> select count(*) from sys_xifenfei;
select count(*) from sys_xifenfei
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from chf.chf_xifenfei;
  COUNT(*)
----------
       868
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

解决问题

SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from dba_tables;
  COUNT(*)
----------
       869
SQL> select count(*) from sys_xifenfei;
  COUNT(*)
----------
       867

MOS解释
ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]

Available workarounds are:
1) Don't describe the dba_* views at mount stage.
OR
2) If you issue DESC of any DBA_*views at mount stage,
    then shutdown and restart the DB instance.
OR
3) Flush the shared pool.
SQL> Alter system flush shared_pool;
and then reissue the failing command.

在10g中open库后提示也为类此ORA-04043: object dba_tables does not exist

ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

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

标题:ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

SCN说明
1、Oracle的SCN在每秒16384次commit的情况下可以维持534年,每秒16384次commit是Oracle早先认为的任何系统的极限commit强度;
2、Oracle里SCN的起点是1988年1月1日;
3、_minimum_giga_scn=n的含义是把SCN往前推进到nG,但请注意,只有在SCN小于nG的时候才会用到这个隐含参数,反之则Oracle会置这个隐含参数于不顾。

求模拟_minimum_giga_scn值
这里通过时间差,大概的模拟_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(300>290)

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2012-03-18 04:27:50
SQL> select months_between(sysdate,to_date('19880101','yyyymmdd')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('19880101','YYYYMMDD'))
------------------------------------------------------
                                             290.55443
SQL> select 16384*60*60*24*31*300/(1024*1024*1024) SCN from dual;
       SCN
----------
12260.7422

启动数据库测试

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--------------------------------
*._minimum_giga_scn=12260
--------------------------------
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

分析ORA-01052

SQL> !oerr ora 1052
01052, 00000, "required destination LOG_ARCHIVE_DUPLEX_DEST is not specified"
// *Cause:  A valid destination for parameter LOG_ARCHIVE_DUPLEX_DEST was not
//          specified when parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was set to
//          two.
// *Action: Either specify a value for parameter LOG_ARCHIVE_DUPLEX_DEST, or
//          reduce the value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to one.
SQL> show parameter LOG_ARCHIVE_DUPLEX_DEST;
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_duplex_dest              string
SQL> show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_min_succeed_dest         integer    1

这里可以看出,不是以为我上面的两个参数设置错误导致ORA-01052,而是因为推进scn过大导致ORA-01052的错误

减小_minimum_giga_scn测试
这里选择_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(280<290) [sql] SQL> select 16384*60*60*24*31*280/(1024*1024*1024) SCN from dual; SCN ---------- 11443.3594 SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. ----------------------------- *._minimum_giga_scn=11443 ----------------------------- SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 236000356 bytes Fixed Size 451684 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> select to_char(dbms_flashback.get_system_change_number(), 2 '9999999999999999') from dual; TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_ ---------------------------------- 12286827692251 SQL> select dbms_flashback.get_system_change_number()/(1024*1024*1024) from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()/(1024*1024*1024) ---------------------------------------------------------- 11443 [/sql] 通过试验可以发现,在我们的数据库遇到异常,需要恢复通过推进scn来恢复的时候,不是推进的越大越好;如果推进的太大可能导致ORA-01052错误,一般建议是比当前不一致的scn稍微大一点 参考:http://www.dbsnake.net/two-scn-internal-points.html