Monthly Archives: 十二月 2011

恢复被rm意外删除数据文件

Posted on by

一.模拟数据文件删除

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 31 22:00:52 2011

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

--数据库版本
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

--所有数据文件
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ora11g/system01.dbf
/opt/oracle/oradata/ora11g/sysaux01.dbf
/opt/oracle/oradata/ora11g/undotbs01.dbf
/opt/oracle/oradata/ora11g/users01.dbf
/opt/oracle/oradata/ora11g/example01.dbf

--删除example01.dbf数据文件
SQL> !rm /opt/oracle/oradata/ora11g/example01.dbf

SQL> !ls -l /opt/oracle/oradata/ora11g/example01.dbf
ls: /opt/oracle/oradata/ora11g/example01.dbf: 没有那个文件或目录

--因为数据文件被删除，创建表失败
SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;
as select * from dba_tables
                 *
ERROR at line 2:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

二.找回数据文件

--查找dbw进程spid
[oracle@node1 ~]$ ps -ef|grep dbw|grep -v grep
oracle   18387     1  0 Dec22 ?        00:00:12 ora_dbw0_ora11g

--查看该进程所有文件句柄
[oracle@node1 ~]$ ll /proc/18387/fd
总计 0
lr-x------ 1 oracle oinstall 64 12-31 22:03 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 12-31 22:03 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 10 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 12 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 13 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 14 -> /proc/18387/fd
lr-x------ 1 oracle oinstall 64 12-31 22:03 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 16 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 17 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lrwx------ 1 oracle oinstall 64 12-31 22:03 18 -> /opt/oracle/product/11.2.0/db_1/dbs/lkORA11G
lr-x------ 1 oracle oinstall 64 12-31 22:03 19 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
l-wx------ 1 oracle oinstall 64 12-31 22:03 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 20 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 21 -> socket:[441562]
lrwx------ 1 oracle oinstall 64 12-31 22:03 256 -> /opt/oracle/oradata/ora11g/control01.ctl
lrwx------ 1 oracle oinstall 64 12-31 22:03 257 -> /opt/oracle/oradata/ora11g/system01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 258 -> /opt/oracle/oradata/ora11g/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 259 -> /opt/oracle/oradata/ora11g/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 260 -> /opt/oracle/oradata/ora11g/users01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 261 -> /opt/oracle/oradata/ora11g/example01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 12-31 22:03 262 -> /opt/oracle/oradata/ora11g/temp01.dbf
lr-x------ 1 oracle oinstall 64 12-31 22:03 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 4 -> /dev/null
lrwx------ 1 oracle oinstall 64 12-31 22:03 5 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 7 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 8 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 9 -> /dev/null
--通过句柄恢复数据文件[被删除数据文件会被标示(deleted)]
[oracle@node1 ~]$ cp /proc/18387/fd/261 /opt/oracle/oradata/ora11g/example01.dbf
--确认该数据文件已经恢复成功
[oracle@node1 ~]$ ll /opt/oracle/oradata/ora11g/example01.dbf
-rw-r----- 1 oracle oinstall 362422272 12-31 22:05 /opt/oracle/oradata/ora11g/example01.dbf

三.数据文件online

SQL> alter database datafile 5 offline;
 
Database altered.
 
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
 
Database altered.

SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;

Table created.

四.补充说明
在意外使用os命令删除掉数据文件时，千万不要慌张重启数据库或者操作系统，可以通过dbwn进程相关句柄找回数据文件

在UltraEdit中定位数据文件内容

Posted on by

一、定位数据块
1.bbed查看数据块

--第一个数据块
BBED> set block 1
        BLOCK#          1
BBED> dump count 16
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:    0 to   15           Dba:0x00000000
------------------------------------------------------------------------
 0ba20000 01004001 00000000 00000104 

 <32 bytes per line>

BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 0ba20000 01004001 00000000 00000104 387a0000 00000000 0003200b 74684acd 
 43484600 00000000 7d4a0000 00320000 00200000 05000300 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 d28a0900 00000000 1ab5d72c f7b4d72c c5320900 00000000 00000000 

 <32 bytes per line>

--第二个数据块

BBED> set block 2
        BLOCK#          2

BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 2                Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 1da20000 02004001 08dee400 00000204 f6b80000 05000000 08000000 00320000 
 09000000 50000000 feff3f00 07000000 00320000 7f000000 fa010000 07dee400 
 00000000 00000000 00000000 00000000 b1220000 08000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

--第100个数据块
BBED> set block 100
        BLOCK#          100

BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 100              Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 06a20000 64004001 343f0900 00000104 a38d0000 02002000 e1c90000 343f0900 
 0000e81f 021f3200 61004001 00000000 00000000 00000000 00000000 00000000 
 00000000 05000900 2c010000 52008000 c8005900 00800000 a3350900 00000000 
 00000000 00008001 00000000 6b00fa00 5e1a6419 00000000 00000000 00000000 

 <32 bytes per line>

2.UltraEdit查看数据块

--第0个数据块
--(为了便于和bbed的block一致，称为0比较合适，因为这个块在bbed中看不到)
00000000h: 00 A2 00 00 00 00 C0 FF 00 00 00 00 00 00 00 00 ; .?...?........
00000010h: 66 C8 00 00 00 20 00 00 00 32 00 00 7D 7C 7B 7A ; f?.. ...2..}|{z
00000020h: A0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; 爜..............
00000030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
……………………………………………………
00001fa0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fb0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fc0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fd0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fe0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................  
--第一个数据块（bbed中的block 1）
00002000h: 0B A2 00 00 01 00 40 01 00 00 00 00 00 00 01 04 ; .?...@.........
00002010h: 38 7A 00 00 00 00 00 00 00 03 20 0B 74 68 4A CD ; 8z........ .thJ?
……………………………………………………………
000021c0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
000021d0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第二个数据块(bbed中的block 2)
00004000h: 1D A2 00 00 02 00 40 01 08 DE E4 00 00 00 02 04 ; .?...@..掬.....
00004010h: F6 B8 00 00 05 00 00 00 08 00 00 00 00 32 00 00 ; 龈...........2..
00004020h: 09 00 00 00 50 00 00 00 FE FF 3F 00 07 00 00 00 ; ....P...??.....
00004030h: 00 32 00 00 7F 00 00 00 FA 01 00 00 07 DE E4 00 ; .2.....?...掬.
00004040h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第100个数据块(bbed中的block 100)
000c8000h: 06 A2 00 00 64 00 40 01 34 3F 09 00 00 00 01 04 ; .?.d.@.4?......
000c8010h: A3 8D 00 00 02 00 20 00 E1 C9 00 00 34 3F 09 00 ; .... .嵘..4?..
000c8020h: 00 00 E8 1F 02 1F 32 00 61 00 40 01 00 00 00 00 ; ..?..2.a.@.....
000c8030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
000c8040h: 00 00 00 00 05 00 09 00 2C 01 00 00 52 00 80 00 ; ........,...R.€.
000c8050h: C8 00 59 00 00 80 00 00 A3 35 09 00 00 00 00 00 ; ?Y..€..?......
000c8060h: 00 00 00 00 00 00 80 01 00 00 00 00 6B 00 FA 00 ; ......€.....k.?
000c8070h: 5E 1A 64 19 00 00 00 00 00 00 00 00 00 00 00 00 ; ^.d.............

3.结论:因为2000(16进制)=8192(10进制),而每行又是16，所以UltraEdit定位块的规则是
SELECT to_char(‘2*块数’,’xxxxxxx’) xff FROM dual;(xff*1000)

SQL> SELECT TO_number('2000','xxxxxx') FROM dual;

TO_NUMBER('2000','XXXXXX')
--------------------------
                      8192

二、UltraEdit定位块内内容
1.bbed查看内容

--第一块偏移量为484数据
BBED> set block 1
        BLOCK#          1

BBED> set offset 484
        OFFSET          484

BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:  484 to  611           Dba:0x00000000
------------------------------------------------------------------------
 c052e700 00000000 ec82ba2d 01000000 2e060000 39020000 10008984 02000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

--第一块偏移量为8188数据
BBED> dump offset 8188 count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010b0000 

 <32 bytes per line>

2.UltraEdit中内容

--offset 484(kscnbas)
000021e0h: 00 00 00 00 C0 52 E7 00 00 00 00 00 EC 82 BA 2D ; ....繰?....靷?    
000021f0h: 01 00 00 00 2E 06 00 00 39 02 00 00 10 00 89 84 ; ........9.....墑
00002200h: 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................

--offset 8188(tailchk) 
00003ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 01 0B 00 00 ; ................

3.结论:10进制的便宜量转换为16进制，然后对照你块开始行号+偏移量(16进制),得到对应偏移量开始位置

SQL> SELECT to_char('484','xxxxxxx') FROM dual;

TO_CHAR('484','X
----------------
     1e4

SQL> SELECT to_char('8188','xxxxxxx') FROM dual;

TO_CHAR('8188','
----------------
    1ffc

三.补充说明
1.本实验是拷贝linux下11g数据文件到win上使用UltraEdit操作得出
2.使用UltraEdit,需要熟悉对一些关键数据的偏移量比较清楚

记一次含AND-EQUAL执行计划调优

Posted on by

1.数据库版本

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

2.发现含有AND-EQUAL执行计划

SQL> SELECT 
  2  COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
  3    FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
  4  WHERE  (TABXNPRESM0_.COMPANY_ID = 346240)
  5     AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
  6     AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
  7     AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
  8     AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
  9     AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
 10     AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=40)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
   3    2       NESTED LOOPS (Cost=11 Card=1 Bytes=40)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=8 Card=1 Bytes=25)
   5    4           AND-EQUAL
   6    5             INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_2' (NON-UNIQUE)
   7    5             INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_1' (NON-UNIQUE) (Cost=4 Card=638)
   8    3         INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     272188  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3.关于AND-EQUAL解释
If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.
Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.
大概的意思是当where条件后面含有多个列的单列索引时(不超过5个)，会先得到每个条件的rowid，然后这些rowid进行merges，得到一个rowid的结果集，最后根据这些rowid取表中记录。

4.表/列/索引相关信息

--index和列信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
  2    FROM USER_IND_COLUMNS
  3   WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
IDX_XN_PRESMS_1                TAB_XN_PRESMS                  COMPANY_ID
IDX_XN_PRESMS_2                TAB_XN_PRESMS                  EMPLOYEE_ID
IDX_XN_PRESMS_3                TAB_XN_PRESMS                  PRE_TIME
PK_TAB_XN_PRESMS               TAB_XN_PRESMS                  PRE_ID
IDX_XN_PRESMS_4                TAB_XN_PRESMS                  SEND_TYPE
IDX_XN_PRESMS_DETAIL           TAB_XN_PRESMS_DETAIL           PRE_ID
IDX_XN_PRESMS_DETAIL_2         TAB_XN_PRESMS_DETAIL           SEND_TIME
PK_TAB_XN_PRESMS_DETAIL        TAB_XN_PRESMS_DETAIL           DETAIL_ID
 
8 rows selected

--index的统计信息
SQL> SELECT TABLE_NAME,
  2         INDEX_NAME,
  3         TO_CHAR(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss')
  4    FROM USER_INDEXES
  5   WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
 
TABLE_NAME                     INDEX_NAME                     TO_CHAR(LAST_ANALYZED,'YYYY-MM
------------------------------ ------------------------------ ------------------------------
TAB_XN_PRESMS                  IDX_XN_PRESMS_1                2011-12-29 09:25:32
TAB_XN_PRESMS                  IDX_XN_PRESMS_2                2011-12-29 09:25:35
TAB_XN_PRESMS                  IDX_XN_PRESMS_3                2011-12-29 09:25:39
TAB_XN_PRESMS                  IDX_XN_PRESMS_4                2011-12-29 09:25:21
TAB_XN_PRESMS_DETAIL           IDX_XN_PRESMS_DETAIL           2011-12-29 09:20:03
TAB_XN_PRESMS_DETAIL           IDX_XN_PRESMS_DETAIL_2         2011-12-29 09:20:01
TAB_XN_PRESMS                  PK_TAB_XN_PRESMS               2011-12-29 09:25:46
TAB_XN_PRESMS_DETAIL           PK_TAB_XN_PRESMS_DETAIL        2011-12-29 09:20:02
 
8 rows selected

--列的唯一度情况
SQL>  SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
  2     FROM USER_TAB_COLS
  3    WHERE (TABLE_NAME, COLUMN_NAME) IN
  4          (SELECT TABLE_NAME, COLUMN_NAME
  5             FROM USER_IND_COLUMNS
  6            WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL'))
  7            ORDER BY table_name,NUM_DISTINCT DESC;
 
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
TAB_XN_PRESMS                  PRE_ID                              1999270
TAB_XN_PRESMS                  PRE_TIME                            1342594
TAB_XN_PRESMS                  EMPLOYEE_ID                           10676
TAB_XN_PRESMS                  COMPANY_ID                             3136
TAB_XN_PRESMS                  SEND_TYPE                                10
TAB_XN_PRESMS_DETAIL           DETAIL_ID                           3863184
TAB_XN_PRESMS_DETAIL           PRE_ID                              1996872
TAB_XN_PRESMS_DETAIL           SEND_TIME                            437526
 
8 rows selected

通过这些信息可以得出：
1)统计信息是最新收集过的
2)因为有多个单列index，数据库为了使得cost最小，可能选择了不合适的index(IDX_XN_PRESMS_2[EMPLOYEE_ID]/IDX_XN_PRESMS_1[COMPANY_ID]),使得出现AND-EQUAL，从而逻辑读偏高。对于这个sql，应该使用唯一度比较高的IDX_XN_PRESMS_3[PRE_TIME]
3)也可以通过修改index，实现程序高效，但是考虑到会影响启动程序，在没有十足的把握之前遵守hint优先原则

5.增加hint提示

SQL> SELECT /*+ index(TABXNPRESM0_ IDX_XN_PRESMS_3) */
  2  COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
  3    FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
  4  WHERE  (TABXNPRESM0_.COMPANY_ID = 346240)
  5     AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
  6     AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
  7     AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
  8     AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
  9     AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
 10     AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=641 Card=1 Bytes=40)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
   3    2       NESTED LOOPS (Cost=641 Card=1 Bytes=40)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=638 Card=1 Bytes=25)
   5    4           INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_3' (NON-UNIQUE) (Cost=63 Card=22286)
   6    3         INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1422  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

从这里可以看出，程序的逻辑读下降了很多(从272188下降到1422)，得到了优化效果，提高了程序执行效率
结合上篇:BITMAP CONVERSION FROM ROWIDS,总结一个经验，如果同时使用到了一个表的多个index，效率一般情况下不会太高。同时也给各位提个醒，index并非越多越好，有时候会适得其反(建立index时需要考虑情况)

清理表部分数据方法

Posted on by

最近两天，开发要求，清理掉某些表的部分数据，因为不能停业务，不能采用cast+rman方式实现.只能自己写脚本删除，在这些删除数据中，经过总结，共有三种类型:
1.删除单个表数据
删除a表以dealdate为范围的部分数据

DECLARE 
  P_SQL   VARCHAR2(300) := 
  'DELETE FROM a WHERE dealdate<to_date('||''''||
'2010-11-01 00:00:00'||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';
  P_COUNT NUMBER := 1000;
BEGIN
  WHILE 1 = 1 LOOP
    EXECUTE IMMEDIATE P_SQL || ' and rownum <= :nu'
      USING P_COUNT;
    IF SQL%NOTFOUND THEN
      EXIT;
    END IF;
    COMMIT;
  END LOOP;
  COMMIT;
END;

2.删除两个关联表数据
tab_a,tab_b两个表通过共有的PRE_ID列关联，然后按照tab_a.ACCEPT_TIME列为条件删除两个表中数据,tab_b表中数据比tab_a多很多

DECLARE
  CURSOR CUR IS
    SELECT B.ROWID BID, A.ROWID AID
      FROM tab_a A, tab_b B
     WHERE A.PRE_ID = B.PRE_ID
       AND A.ACCEPT_TIME <
           TO_DATE('2010-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
     ORDER BY B.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW IN CUR LOOP
    DELETE FROM tab_a WHERE ROWID = ROW.AID;
    DELETE FROM tab_b WHERE ROWID = ROW.BID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

3.删除某个表中重复列
删除tab_a表中的COMPANY_ID/PY_DES/PY_DES_Q/PY_TYPE/RELATE_ID列重复数据

DECLARE
  CURSOR CUR IS
    SELECT A.ROWID AID
      FROM tab_a A
     WHERE ROWID NOT IN (SELECT MAX(B.ROWID)
                           FROM tab_a B
                          WHERE A.COMPANY_ID = B.COMPANY_ID
                            AND A.PY_DES = B.PY_DES
                            AND A.PY_DES_Q = B.PY_DES_Q
                            AND A.PY_TYPE = B.PY_TYPE
                            AND A.RELATE_ID = B.RELATE_ID)
     ORDER BY A.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW IN CUR LOOP
    DELETE FROM tab_a WHERE ROWID = ROW.AID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

ORA-07445[kslgetl()+120]/ORA-00108错误解决

Posted on by

一.数据库版本

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

二.alert中发现ORA-07445[kslgetl()+120]/ORA-00108错误

Mon Dec 19 09:19:42 2011
found dead dispatcher 'D000', pid = (13, 1)
Mon Dec 19 09:19:42 2011
dispatcher 'D000' encountered error getting listening address
Mon Dec 19 09:19:42 2011
Errors in file /opt/oracle/admin/gaxt/bdump/gaxt_ora_16297.trc:
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Mon Dec 19 09:19:45 2011
found dead dispatcher 'D000', pid = (21, 2)
Mon Dec 19 09:19:45 2011
dispatcher 'D000' encountered error getting listening address
Mon Dec 19 09:19:45 2011
Errors in file /opt/oracle/admin/gaxt/bdump/gaxt_ora_16299.trc:
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously

三.trace文件信息

Oracle process number: 15
Unix process pid: 10607, image: oracle@gongantest (D000)

Warning: keltnfy call to ldmInit failed with error 46
*** 2011-12-19 19:21:40.100
network error encountered getting listening address:
  NS Primary Error: TNS-12533: TNS:illegal ADDRESS parameters
  NS Secondary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00503: Illegal ADDRESS parameters
OPIRIP: Uncaught error 108. Error stack:
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x208, PC: [0x7a06b8, kslgetl()+120]
*** 2011-12-19 19:21:40.107
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Current SQL information unavailable - no session.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   2B91BE8F7D50 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   2B91BE8F7D50 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
ssexhd()+629         call     ksedmp()             000000003 ? 000000001 ?
                                                   2B91BE8F7D50 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
__restore_rt()+0     call     ssexhd()             00000000B ? 2B91BE8F8D70 ?
                                                   2B91BE8F8C40 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
kslgetl()+120        signal   __restore_rt()       0600E7560 ? 0000000E8 ?
                                                   09AAE5728 ? 0000009A9 ?
                                                   000003980 ? 09AAE5740 ?
ksfglt()+108         call     kslgetl()            0600E7560 ? 000000001 ?
                                                   09AAE5728 ? 0000009A9 ?
                                                   000003980 ? 09AAE5740 ?

四.在MOS上找到相关文章[ID 1298804.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7 - Release: 11.1 to 11.1
Information in this document applies to any platform.
Symptoms

The following errors are seen in the trace file written by an ORA-7445 [kslgetl]:

network error encountered getting listening address:
NS Primary Error: TNS-12533: TNS:illegal ADDRESS parameters
NS Secondary Error: TNS-12560: TNS:protocol adapter error
NT Generic Error: TNS-00503: Illegal ADDRESS parameters
OPIRIP: Uncaught error 108. Error stack:
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x130, PC: [0x82f09dc, kslgetl()+80]

The trace file indicates that there is no session:

Current SQL information unavailable - no session.

The Call Stack Trace in the ORA-7445 trace file contains a function list similar to:

 kslgetl <- PGOSF57_ksfglt
<- kghfre <- kmnsbf <- nsbfr <- nsiofrrg <- nsiocancel
<- nsopen_shutitdown <- nsclose <- nsgblclose <- nsgblTRMHelper <- nsgblRealTerm
<- nlstdstp <- npinlt <- ksuabt <- opidrv <- sou2o
<- opimai_real <- main <- libc_start_main

Cause

The trace file first reports: Warning: keltnfy call to ldmInit failed with error 46 

The ORA-7445 is not the starting point here. This exception is just a spin-off from ORA-180 and it is possible that different internal errors may be seen, such as ORA-600 [504], depending on what is happening when the ORA-180 is encountered.

The cause for the ORA-180 is related to the inital message at the beginning of the trace file: "keltnfy call to ldmInit failed with error 46" and this is followed by: "network error encountered getting listening address:" 

The error code (here: 46) is the key for solving the issue.
This warning says that ldmInit() returned error 46 which is LDMERR_HOST_NOT_FOUND (host not found).

This error is returned if the OS call gethostbyname() fails with an error. So these appears to be a network specific issue.
Solution

1) Check permission on /etc/hosts

$ ls -l /etc/hosts -rw-r--r-- 2 root root 194 Oct 17 2006 /etc/hosts


Check if /etc/hosts file is correctly configured


<ip address> <fully qualified hostname> <simple or short hostname> <alias, if applicable> ( all of this on one line ).


2) Check the hostname:

$ hostname
$ ping `hostname`

Make sure you are able to ping the hostname


3) Check if /etc/nodename is correctly configured
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.


$ nslookup <shortname> $ nslookup <long name> $ nslookup <ip address>

The forward and reverse lookup should succeed and return consistent address/info.


4) Check nsswitch.conf


$ more nsswitch.confhosts: files dnsMake sure host lookup is also done through the /etc/hosts file and not just dns. It is recommended that FILES come first before DNS.


Also, check the resolv.conf. This makes sure that the DNS is working properly.

在这里看到虽然数据库的版本不一样，alert和trace中的错误不完全一致，但是很相似。是由于主机名不能被正常访问导致，所以尝试这从主机名相关部分着手解决。

五.查看主机名相关信息，解决问题

[oracle@gongantest ~]$  ls -l /etc/hosts
-rw-r--r-- 2 root root 176 Dec 16 13:43 /etc/hosts
[oracle@gongantest ~]$ hostname
gongantest
[oracle@gongantest ~]$ ping gongantest
ping: unknown host gongantest
[oracle@gongantest ~]$ more  /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1        localhost.localdomain localhost
#192.168.11.60    gongantest
--发现改主机名的dns被注释掉
[oracle@gongantest ~]$ ping gongantest
PING gongantest (192.168.11.60) 56(84) bytes of data.
From 192.168.9.66 icmp_seq=2 Destination Host Unreachable
From 192.168.9.66 icmp_seq=3 Destination Host Unreachable
From 192.168.9.66 icmp_seq=4 Destination Host Unreachable

--- gongantest ping statistics ---
5 packets transmitted, 0 received, +3 errors, 100% packet loss, time 3999ms, pipe 3
--除掉注释，测试不通
[root@gongantest ~]# ifconfig
eth1      Link encap:Ethernet  HWaddr 00:14:22:10:96:CA  
          inet addr:192.168.9.66  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96ca/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:4207222 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2482964 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:1156547557 (1.0 GiB)  TX bytes:565900103 (539.6 MiB)
--发现该机器的ip为192.168.9.66，修改hosts文件。
--初步确定出现问题的原因是因为机器迁移，使用了新ip，注释掉了hosts文件中错误ip
[root@gongantest ~]# vi /etc/hosts

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1        localhost.localdomain localhost
192.168.9.66    gongantest

[oracle@gongantest ~]$ ping gongantest
PING gongantest (192.168.9.66) 56(84) bytes of data.
64 bytes from gongantest (192.168.9.66): icmp_seq=1 ttl=64 time=0.037 ms
64 bytes from gongantest (192.168.9.66): icmp_seq=2 ttl=64 time=0.031 ms
64 bytes from gongantest (192.168.9.66): icmp_seq=3 ttl=64 time=0.033 ms

--- gongantest ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2000ms
rtt min/avg/max/mdev = 0.031/0.033/0.037/0.007 ms
--ping测试正常

继续观察

--观察d000进程是否启动
[oracle@gongantest ~]$ ps -ef|grep ora_
oracle   10180     1  0 13:23 ?        00:00:00 ora_pmon_gaxt
oracle   10182     1  0 13:23 ?        00:00:00 ora_psp0_gaxt
oracle   10184     1  0 13:23 ?        00:00:00 ora_mman_gaxt
oracle   10186     1  0 13:23 ?        00:00:00 ora_dbw0_gaxt
oracle   10188     1  0 13:23 ?        00:00:02 ora_lgwr_gaxt
oracle   10190     1  0 13:23 ?        00:00:00 ora_ckpt_gaxt
oracle   10192     1  0 13:23 ?        00:00:00 ora_smon_gaxt
oracle   10194     1  0 13:23 ?        00:00:00 ora_reco_gaxt
oracle   10196     1  0 13:23 ?        00:00:00 ora_cjq0_gaxt
oracle   10198     1  0 13:23 ?        00:00:00 ora_mmon_gaxt
oracle   10200     1  0 13:23 ?        00:00:00 ora_mmnl_gaxt
oracle   10204     1  0 13:23 ?        00:00:00 ora_s000_gaxt
oracle   10210     1  0 13:23 ?        00:00:00 ora_arc0_gaxt
oracle   10212     1  0 13:23 ?        00:00:00 ora_arc1_gaxt
oracle   10214     1  0 13:23 ?        00:00:00 ora_qmnc_gaxt
oracle   10218     1  0 13:23 ?        00:00:00 ora_j000_gaxt
oracle   10222     1  0 13:24 ?        00:00:00 ora_q000_gaxt
oracle   10234     1  0 13:24 ?        00:00:00 ora_q001_gaxt
oracle   10609     1  0 13:32 ?        00:00:00 ora_d000_gaxt
oracle   10639  9962  0 13:35 pts/0    00:00:00 grep ora_

--观察alert日志未出现该错误
Mon Dec 26 13:32:18 2011
Errors in file /opt/oracle/admin/gaxt/bdump/gaxt_ora_10607.trc:
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Mon Dec 26 13:32:21 2011
found dead dispatcher 'D000', pid = (13, 85)
Mon Dec 26 13:36:04 2011
Thread 1 advanced to log sequence 232 (LGWR switch)
  Current log# 1 seq# 232 mem# 0: /opt/oracle/oradata/gaxt/redo01.log

至此ORA-07445[kslgetl()+120]/ORA-00108错误解决