分类目录归档：数据库

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

发表于 2012 年 4 月 13 日 由 惜分飞

数据库版本

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 | 标签为 | 一条评论

DB2中schema管理

发表于 2012 年 4 月 12 日 由 惜分飞

0.DB2版本信息

[db2inst1@xifenfei ~]$ db2level
DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09050" 
with level identifier "03010107".
Informational tokens are "DB2 v9.5.0.0", "s071001", "LINUXIA3295", and Fix Pack 
"0".
Product is installed at "/opt/db2/V9.5".

1.显示syscat.schemata视图结构

[db2inst1@xifenfei ~]$ db2 "describe table syscat.schemata"

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMANAME                      SYSIBM    VARCHAR                    128     0 No    
OWNER                           SYSIBM    VARCHAR                    128     0 No    
OWNERTYPE                       SYSIBM    CHARACTER                    1     0 No    
DEFINER                         SYSIBM    VARCHAR                    128     0 No    
DEFINERTYPE                     SYSIBM    CHARACTER                    1     0 No    
CREATE_TIME                     SYSIBM    TIMESTAMP                   10     0 No    
REMARKS                         SYSIBM    VARCHAR                    254     0 Yes

2.查询当前存在schema

[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"

SCHEMANAME                 OWNER                      CREATE_TIME               
------------            ------------                   ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744

  11 record(s) selected.

3.显示创建schema

[db2inst1@xifenfei ~]$  db2 "create schema xifenfei"
DB20000I  The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"

SCHEMANAME                 OWNER                      CREATE_TIME               
------------            ------------                  ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744
XIFENFEI                   DB2INST1                   2012-04-03-12.01.12.724932

  12 record(s) selected.

4.隐式创建schema

[db2inst1@xifenfei ~]$ db2 "create table xff.t_xifenfei(id int,name varchar(100))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"

SCHEMANAME                 OWNER                      CREATE_TIME               
------------            ------------                 ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744
XIFENFEI                   DB2INST1                   2012-04-03-12.01.12.724932
XFF                        SYSIBM                     2012-04-03-12.03.12.581260

  13 record(s) selected.

隐式创建schema的所属用户会是SYSIBM(存放系统数据字典表SCHEMA)

5.删除schema

[db2inst1@xifenfei ~]$ db2 "drop schema xff"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "drop 
schema xff".  Expected tokens may include:  "RESTRICT".  SQLSTATE=42601

[db2inst1@xifenfei ~]$  db2 drop schema xff restrict 
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0478N  DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA" 
cannot be processed because there is an object "XFF.T_XIFENFEI", of type 
"TABLE", which depends on it.  SQLSTATE=42893

[db2inst1@xifenfei ~]$ db2 "drop table xff.t_xifenfei"
DB20000I  The SQL command completed successfully.

[db2inst1@xifenfei ~]$  db2 drop schema xff restrict 
DB20000I  The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"

SCHEMANAME                 OWNER                      CREATE_TIME               
------------            ------------                 ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744
XIFENFEI                   DB2INST1                   2012-04-03-12.01.12.724932

  12 record(s) selected.

删除schema需要使用restrict关键字,而且该schema中无对象存在.

在DB2中的schema的概念和ORACLE中的概念有着本质的区别：在ORACLE中schema和用户是同一个;在DB2中schema不一定是用户,因为db2内部没有用户的概念,连接用户必须是操作系统用户.

发表在 DB2 | 评论关闭

DB2中产生唯一值三种方式

发表于 2012 年 4 月 12 日 由 惜分飞

一.Genearate_unique函数

[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_gu(custno char(13) for bit data,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$  db2 "insert into xifenfei.t_gu values
> (generate_unique(),'www.xifenfei.com'),(generate_unique(),'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_gu"

CUSTNO                        CUSTNAME        
----------------------------- ----------------
x'20120403054630527862000000' www.xifenfei.com
x'20120403054630527940000000' xifenfei        

  2 record(s) selected.

generate_unique是按照国际标准时间(UTC)生成的当前时间戳加上当前数据库分区号，包含13个字节的字符串。如果调整了系统时间可能会出现重复

二.序列(Sequence)

[db2inst1@xifenfei ~]$ db2 "create sequence xifenfei.xff_seq
> as bigint
> start with 1
> increment by 1
> no maxvalue
> cycle
> cache 10"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_seq(xff_id bigint,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_seq values(nextval 
> for xifenfei.xff_seq,'www.xifenfei.com'), (nextval for xifenfei.xff_seq,'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_seq"

XFF_ID               CUSTNAME        
-------------------- ----------------
                   1 www.xifenfei.com
                   2 xifenfei        

  2 record(s) selected.

和ORACLE的sequence基本相同，只是在oracle中是sequence.nextval这里改为了nextnvl for seqence

三.自增字段

--1.generated always as identity方式(不能人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_a(xff_id bigint not null generated always 
> as identity(start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入失败
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(xff_id,custname) values(11,'XIFENFEI')"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0798N  A value cannot be specified for column "XFF_ID" which is defined as 
GENERATED ALWAYS.  SQLSTATE=428C9
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_a"

XFF_ID               CUSTNAME        
-------------------- ----------------
                  10 www.xifenfei.com

  1 record(s) selected.

--1.generated by default as identity方式(可以人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_d(xff_id bigint not null generated by default 
> as identity (start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入成功
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(xff_id,custname) values(11,'XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_d"

XFF_ID               CUSTNAME        
-------------------- ----------------
                  10 www.xifenfei.com
                  11 XIFENFEI        

  2 record(s) selected.

这个功能和sql server/mysql的自增长列很相似，给出了两种方式选择，使得比它们更加灵活

发表在 DB2 | 评论关闭