Category Archives: Oracle

清理表部分数据方法

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错误解决

记录一次oer 8102.2处理

Posted on by

1.alert日志

Tue Dec 20 22:09:45 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_27442.trc:
Wed Dec 21 22:10:45 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_32761.trc:
Thu Dec 22 22:11:46 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_5935.trc:
Fri Dec 23 22:12:47 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_11382.trc:

Mnnn performs manageability tasks dispatched to them by MMON. Tasks performed include taking Automatic Workload Repository snapshots and Automatic Database Diagnostic Monitor analysis.
从这个时间点来看，应该是数据库启动GATHER_STATS_JOB收集统计信息时发现这个错误。

2.bas_m000_11382.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      bas
Release:        2.6.9-78.ELsmp
Version:        #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine:        x86_64
Instance name: BAS
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 11382, image: oracle@bas (m000)

*** ACTION NAME:(Auto-Purge Slave Action) 2011-12-23 22:12:47.074
*** MODULE NAME:(MMON_SLAVE) 2011-12-23 22:12:47.074
*** SERVICE NAME:(SYS$BACKGROUND) 2011-12-23 22:12:47.074
*** SESSION ID:(5465.2033) 2011-12-23 22:12:47.074
oer 8102.2 - obj# 4152, rdba: 0x00401f7c(afn 1, blk# 8060)
kdk key 8102.2:
  ncol: 2, len: 10
  key: (10):  02 c1 0a 06 00 c0 04 dc 00 00
  mask: (4096): 
 09 00 00 00 00 fb d1 c0 00 00 00 00 00 70 f8 fe bf 7f 00 00 00 cd 7d 5d 01

oer 8102.<code> - obj# <object id>, rdba: <rdba value>(afn <file#>, blk# <block#>)
kdk key 8102.2:
ncol: <number of columns in the key including the rowid>, len: <key length>
key: (<length>):<hexadecimal value>

obj#:   object_id for the affected index in dba_objects.
rdba:   relative data block address where the key is supposed to be stored in the index.
afn:     absolute file number where the affected index block is stored. 
(file_id in dba_data_files, file# in v$datafile).
blk#:   Index block number where the key is supposed to be stored.

出现oer 8102.2的错误，有两种可能:1.坏块,2.表和索引数据不一致

3.找出相关对象

SQL> col object_name for a30
SQL> col owner for a10
SQL> select object_name,owner,object_type
  2  from dba_objects where object_id=4152;

OBJECT_NAME                    OWNER      OBJECT_TYPE
------------------------------ ---------- -------------------
WRI$_SEGADV_OBJLIST_IDX_TS     SYS        INDEX

SQL> select OWNER,TABLE_NAME from dba_indexes 
   2 where index_name='WRI$_SEGADV_OBJLIST_IDX_TS';

OWNER      TABLE_NAME
---------- ------------------------------
SYS        WRI$_SEGADV_OBJLIST

SQL> ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

4.分析坏块(逻辑/物理)

SQL> ANALYZE INDEX WRI$_SEGADV_OBJLIST_IDX_TS VALIDATE STRUCTURE;

Index analyzed.

SQL> ANALYZE TABLE WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE;

Table analyzed.

[oracle@bas bdump]$ dbv file=/opt/app/oracle/oradata/BAS/system01.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Sat Dec 24 21:14:38 2011

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

DBVERIFY - Verification starting : FILE = /opt/app/oracle/oradata/BAS/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 552960
Total Pages Processed (Data) : 360156
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 167596
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1961
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23247
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 2890198330 (2750.2890198330)

检测证明，对象以及对象所属的数据文件，无坏块现象

5.分析表和index不一致

--找出index对应列
SQL> SELECT table_name , column_name from dba_ind_columns  
  2  WHERE index_name='WRI$_SEGADV_OBJLIST_IDX_TS' order by table_name; 

TABLE_NAME                     COLUMN_NAME
------------------------------ --------------------
WRI$_SEGADV_OBJLIST            TS_ID

--确定对应列是否允许为null
SQL> desc WRI$_SEGADV_OBJLIST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AUTO_TASKID                                        NUMBER
 TS_ID                                              NUMBER
 OBJN                                               NUMBER
 OBJD                                               NUMBER
 STATUS                                             VARCHAR2(40)
 TASK_ID                                            NUMBER
 REASON                                             VARCHAR2(40)
 REASON_VALUE                                       NUMBER
 CREATION_TIME                                      TIMESTAMP(6)
 PROC_TASKID                                        NUMBER
 END_TIME                                           TIMESTAMP(6)
 SEGMENT_OWNER                                      VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)

--确认在表中对应列是否有空值
SQL> SELECT /*+ FULL(t1) */ count(TS_ID)
  2   FROM WRI$_SEGADV_OBJLIST t1 
  3    WHERE t1.TS_ID IS NULL;

COUNT(TS_ID)
------------
           0
--表比index多数据
SQL> SELECT /*+ FULL(t1) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t1
  3  MINUS
  4  SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t where  ts_id is not null;

no rows selected


--index中数据条数
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)
  2  FROM WRI$_SEGADV_OBJLIST t
  3  where ts_id is not null;

COUNT(TS_ID)
------------
         901

--表中数据条数
SQL> SELECT /*+ FULL(t1) */ count(TS_ID)
  2  FROM WRI$_SEGADV_OBJLIST t1 ;

COUNT(TS_ID)
------------
         937

--index中不同值数量
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ 
  2  COUNT(DISTINCT TS_ID)
  3  FROM WRI$_SEGADV_OBJLIST t WHERE TS_ID IS NOT NULL;

COUNT(DISTINCTTS_ID)
--------------------
                   5
--表中不同值数量
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3  MINUS
  4  SELECT /*+ FULL(t1) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t1 ;

     TS_ID
----------
         4
--对比可以知道index中的唯一值比表中，这个也就解释了，为什么表中总条数多，
--但是他们两做减法的时候，记录为空

--索引表比表多数据
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3  MINUS
  4  SELECT /*+ FULL(t1) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t1 ;

     TS_ID
----------
         4

上面的检测证明:1.表中有索引中无的数据,2.索引中有表中不存在数据

6.解决问题

SQL> alter index WRI$_SEGADV_OBJLIST_IDX_TS rebuild online;

Index altered.

--测试index中总条数
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)
  2   FROM WRI$_SEGADV_OBJLIST t
  3    where  ts_id is not null;

COUNT(TS_ID)
------------
         937

--无多余index项(以前唯一值为4的记录已经不存在)
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2      FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3    MINUS
  4      SELECT /*+ FULL(t1) */ TS_ID
  5      FROM WRI$_SEGADV_OBJLIST t1 ;

no rows selected

--通过上述测试，证明表和index不一致问题解决

通过ROWID找回坏块数据

Posted on by

一.准备环境

C:\Users\XIFENFEI>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 23 10:49:52 2011

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create tablespace t_xff datafile 'E:\ORACLE\ORADATA\XFF\t_xff01.dbf'
  2  size 10m autoextend on next 10m maxsize 1g;

表空间已创建。

SQL> create table t_xifenfei tablespace t_xff
  2  as
  3  select * from dba_objects;

表已创建。

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     73286

二.发现坏块
使用ULtraEdit破坏数据(关闭数据库执行)

SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 13, 块号 373)
ORA-01110: 数据文件 13: 'E:\ORACLE\ORADATA\XFF\T_XFF01.DBF'

三.查询坏块相关信息

The "LOW_RID" is the lowest rowid INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>,0) LOW_RID
from DUAL;

The "HI_RID" is the first rowid AFTER the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>+1,0) HI_RID
from DUAL;

SQL> col tablespace_name for a30
SQL> col segment_type for a5
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2                 FROM dba_extents
  3                WHERE file_id =13
  4  AND 373 between block_id AND block_id + blocks - 1 ;

TABLESPACE_NAME                SEGME OWNER      SEGMENT_NAME
------------------------------ ----- ---------- --------------------
T_XFF                          TABLE CHF        T_XIFENFEI

SQL> SELECT data_object_id
  2            FROM dba_objects
  3   WHERE object_name = 'T_XIFENFEI'  and owner='CHF';

DATA_OBJECT_ID
--------------
         77759
--坏块的最小rowid
SQL> select dbms_rowid.rowid_create(1, 77759,13,373,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF1AAA

坏块的最大rowid(block+1得到)
SQL> select dbms_rowid.rowid_create(1, 77759,13,374,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF2AAA

四.根据rowid找回数据

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE ROWID>='AAAS+/AANAAAAF2AAA';

  COUNT(*)
----------
     55858

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';

  COUNT(*)
----------
     17358

SQL> SELECT 77759-55858-17358 from dual;

77759-55858-17358
-----------------
             4543

SQL> CREATE TABLE T_XIFENFEI_BAK  TABLESPACE T_XFF
  2  AS
  3  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  4  WHERE ROWID>='AAAS+/AANAAAAF2AAA';

表已创建。

SQL> INSERT INTO  T_XIFENFEI_BAK
  2  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  3  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';

已创建17358行。

SQL> COMMIT;

提交完成。

SQL> SELECT COUNT(*) FROM T_XIFENFEI_BAK;

  COUNT(*)
----------
     73216

五.和dbms_repair解决坏块对比

SQL> CONN / AS SYSDBA
已连接。
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL 过程已成功完成。

SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI';

SKIP_COR
--------
ENABLED

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     73216

通过跳过坏块和rowid功能对比可以看出，两者丢失的数据是相同的，如果有index，同样利用rowid结合index，可能会找回部分数据。当然dbms_repair也提供了类此的功能。两种方法的使用看个人的爱好与习惯。

使用bbed解决ORA-00600[2662]

Posted on by

一、数据库启动报ORA-00600[2662]

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 22 14:37:00 2011

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2],
[2164287937], [4194432], [], [], [], [], [], []
Process ID: 16829
Session ID: 96 Serial number: 3

二.alert日志错误显示

Thu Dec 22 14:37:09 2011
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Thu Dec 22 14:37:09 2011
ARC0 started with pid=20, OS id=16831 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Dec 22 14:37:10 2011
ARC1 started with pid=21, OS id=16833 
Thu Dec 22 14:37:10 2011
ARC2 started with pid=22, OS id=16835 
Thu Dec 22 14:37:10 2011
ARC3 started with pid=23, OS id=16837 
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 17
  Current log# 2 seq# 17 mem# 0: /opt/oracle/oradata/ora11g/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc  (incident=36156):
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_36156/ora11g_ora_16829_i36156.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc  (incident=36157):
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_36157/ora11g_ora_16829_i36157.trc
Dumping diagnostic data in directory=[cdmp_20111222143713], requested by (instance=1, osid=16829), summary=[incident=36156].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Undo initialization errored: err:600 serial:0 start:176607884 end:176611234 diff:3350 (33 seconds)
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc:
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc:
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 16829): terminating the instance due to error 600
Instance terminated by USER, pid = 16829
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (16829) as a result of ORA-1092
Thu Dec 22 14:37:15 2011
ORA-1092 : opitsk aborting process

三.分析日志
ORA-00600[2662]主要参数说明见:ORA-00600 [2662]
这里补充说明:e表示出现异常问题的数据块的DBA,这里的4194432就是一个数据块的DBA

--通过DBA地址查询数据块和文件号
SQL> select dbms_utility.data_block_address_block(4194432) "blick",
  2    dbms_utility.data_block_address_file(4194432) "file" from dual;

     blick       file
---------- ----------
       128          1

--当前数据库SCN
SQL> select to_char(2147510731,'xxxxxxxxxxx') from dual;

TO_CHAR(2147
------------
    800069cb

--当前数据块SCN
SQL> select to_char(2164287937,'xxxxxxxxxxx') from dual;

TO_CHAR(2164
------------
    810069c1

四.bbed查看相关SCN

[oracle@node1 ora11g]$ bbed
Password: 
BBED-00113: Invalid password. Please rerun utility with the correct password.

[oracle@node1 ora11g]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Dec 22 14:49:24 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename "/opt/oracle/oradata/ora11g/system01.dbf"
        FILENAME        /opt/oracle/oradata/ora11g/system01.dbf

BBED> set block 1
        BLOCK#          1

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x800069c8
      ub2 kscnwrp                           @488      0x0002
   ub4 kcvcptim                             @492      0x2dedee96
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000011
         ub4 kcrbabno                       @504      0x0000210f
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00

BBED> set block 128
        BLOCK#          128

BBED> p bas_kcbh
ub4 bas_kcbh                                @8        0x810069c1

BBED> p wrp_kcbh
ub2 wrp_kcbh                                @12       0x0002

这里看到的SCN(16进制)和我们在alert日志中看到的有一定的出入原因是在数据库启动的时候，当前SCN增加了，但是因为数据库直接abort，没有写入到数据文件中。导致数据文件头部的SCN比alert中显示的稍微小一点(还有可能，系统当前的scn比system01.dbf的scn大一点)。通过对比数据块和数据文件头部的SCN也可以说明当数据块的SCN>数据块当前SCN导致ORA-00600[2662]

五.bbed修改数据块的SCN

BBED> set offset 8
        OFFSET          8

BBED> m /x c8690080
BBED-00215: editing not allowed in BROWSE mode


BBED> set mode edit
        MODE            Edit

BBED> m /x c8690080
BBED-00209: invalid number (c8690080)
--分开修改，曲线救国策略

BBED> m /x c869
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 c8690081 02000104 2f8f0000 00000000 00000000 00000000 00000000 06000000 
 2f000000 20100000 00000000 00000000 07000000 81004000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 06000000 00000000 00000000 
 00000040 81004000 07000000 88004000 08000000 10024000 08000000 18024000 
 08000000 20024000 08000000 28024000 08000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 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>

BBED> set offset +2
        OFFSET          10

BBED> m /x 0080
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets:   10 to  521           Dba:0x00000000
------------------------------------------------------------------------
 00800200 01042f8f 00000000 00000000 00000000 00000000 00000600 00002f00 
 00002010 00000000 00000000 00000700 00008100 40000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000600 00000000 00000000 00000000 
 00408100 40000700 00008800 40000800 00001002 40000800 00001802 40000800 
 00002002 40000800 00002802 40000800 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 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>

BBED> p tailchk
ub4 tailchk                                 @8188     0x69c10e01

BBED> set offset 8188
        OFFSET          8188

BBED> m /x 010ec869
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010ec869 

 <32 bytes per line>

BBED> p tailchk
ub4 tailchk                                 @8188     0x69c80e01

BBED> p bas_kcbh
ub4 bas_kcbh                                @8        0x800069c8

BBED> sum apply
Check value for File 0, Block 128:
current = 0x8e2f, required = 0x8e2f

BBED> exit

六.启动数据库

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 22 14:58:10 2011

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.

七.补充说明
一般遇到ORA-00600[2662]都是使用alter session set events ‘10015 trace name adjust_scn level N’;方法处理，但是有时候会遇到ORA-01031错误，那就需要请bbed帮忙处理

OS Pid: 30268 executed alter session set events '10051 trace name adjust_scn level 2'
Thu Dec 22 12:04:07 2011
Errors in file /ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_30268.trc:
ORA-01031: insufficient privileges
Thu Dec 22 12:04:43 2011
Errors in file /ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_846.trc:
ORA-01031: insufficient privileges