11G RAC库 ORA-00600[ktubko_1]错误

数据库版本信息

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
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;
www.xifenfei.com
-------------------
2012-06-12 14:44:53

数据库启动报ORA-00600[ktubko_1]错误

Database Characterset is ZHS16GBK
Errors in file /u01/diag/rdbms/xff/XFF1/trace/XFF1_smon_17248.trc  (incident=21754):
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/diag/rdbms/xff/XFF1/incident/incdir_21754/XFF1_smon_17248_i21754.trc
Tue Jun 12 10:37:10 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance XFF1 (pid = 19) - Error 600 encountered while recovering transaction (4, 2) on object 5887.
Errors in file /u01/diag/rdbms/xff/XFF1/trace/XFF1_smon_17248.trc:
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []

查看trace文件

Incorrect next uba in kturCurrBackoutOneChg while backing out xid: 0x0004.002.0000022b uba: 0x00c02068.00af.3b
Undo record:
ktubu redo: slt: 2 rci: 58 opc: 10.22 objn: 5887 objd: 5887 tsn: 1
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0003.003.0000030a uba: 0x00c0200c.010a.15
                      flg: C---    lkc:  0     scn: 0x0000.00118c55
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x800df2 block=0x00800df3
(kdxlre): restore leaf row (clear leaf delete flags)
key :(5):  02 c1 0d 01 80
keydata/bitmap: (6):  00 81 0f 41 00 01
Undo block: tsn 0x2 rdba: 0xc02068
Dump of buffer cache at level 4 for tsn=2 rdba=12591208
BH (0x33ff7264) file#: 3 rdba: 0x00c02068 (3/8296) class: 24 ba: 0x33f24000
*** 2012-06-12 10:36:40.265
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
  hash: [0x3e78a49c,0x3e78a49c] lru: [0x33ff73ec,0x33ff723c]
  obj-flags: object_ckpt_list
  ckptq:[0x3e05cd68,0x33ff7f0c]fileq:[0x3e05cda4,0x3e05cda4]objq:[0x3b9094a4,0x3b9094a4]objaq:[0x33ff7acc,0x3b909494]
  st: XCURRENT md: NULL fpin: 'ktuwh23: ktubko' tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0x15.26.0] LSCN: [0x0.11acb6] HSCN: [0x0.11acb6] HSUB: [1]
Data block dump: tsn: 0x1 rdba: 0x800df3
Dump of buffer cache at level 3 for tsn=1 rdba=8392179
BH (0x33ff70b4) file#: 2 rdba: 0x00800df3 (2/3571) class: 1 ba: 0x33f20000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: 5887 objn: 5887 tsn: 1 afn: 2 hint: f
  hash: [0x3e7d85e4,0x3e7d85e4] lru: [0x33ff723c,0x3e05c760]
  ckptq: [NULL] fileq: [NULL] objq: [0x3b9092a8,0x3b9092a8] objaq: [0x3b9092a0,0x3b9092a0]
  st: XCURRENT md: NULL fpin: 'kdiwh27: kdiulk' tch: 1
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  buffer tsn: 1 rdba: 0x00800df3 (2/3571)
  scn: 0x0000.00118c67 seq: 0x01 flg: 0x06 tail: 0x8c670601
  frmt: 0x02 chkval: 0x5d04 type: 0x06=trans data
Block header dump:  0x00800df3
 Object id on Block? Y
 seg/obj: 0x16ff  csc: 0x00.118c60  itc: 3  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x800df0 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   0x0006.01d.00000319  0x00c00332.009b.15  --U-    1  fsc 0x000f.00118c67
0x03   0x0003.003.0000030a  0x00c0200c.010a.15  C---    0  scn 0x0000.00118c55
kcra_dump_redo_internal: skipped for critical process
Dumping redo for undo$
kcra_dump_redo_internal: skipped for critical process
*** 2012-06-12 10:36:43.906
Incident 21754 created, dump file: /u01/diag/rdbms/xff/XFF1/incident/incdir_21754/XFF1_smon_17248_i21754.trc
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
ORACLE Instance XFF1 (pid = 19) - Error 600 encountered while recovering transaction (4, 2) on object 5887.
*** 2012-06-12 10:37:10.646
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41        call     kgdsdst()            BFFD84FC ? 2 ?
ksedst1()+77         call     skdstdst()           BFFD84FC ? 0 ? 1 ? 8592A48 ?
                                                   85928C6 ? 8592A48 ?
ksedst()+33          call     ksedst1()            0 ? 1 ?
dbkedDefDump()+2704  call     ksedst()             0 ? 0 ? 3FFE17CC ? 3E7C5344 ?
                                                   1 ? 1 ?
ksedmp()+47          call     dbkedDefDump()       3 ? 0 ?
kturRecoverTxn()+52  call     ksedmp()             3 ? B6B3FEA4 ? 2C ?
471                                                B6B3FE38 ? 1 ? B6B3FE58 ?
kturRecoverUndoSegm  call     kturRecoverTxn()     BFFD9078 ? 2 ? 1 ? 0 ? 11 ?
ent()+1091                                         4 ? 1 ?
kturRecoverActiveTx  call     kturRecoverUndoSegm  4 ? 0 ? 1 ? 0 ? FFFF ? 11 ?
ns()+931                      ent()                4 ?
ktprbeg()+281        call     kturRecoverActiveTx  10 ? 0 ?
                              ns()
ktmmon()+13050       call     ktprbeg()            0 ? 1 ? 0 ? B6B5B72C ? 0 ?
                                                   0 ?
ktmSmonMain()+174    call     ktmmon()             20018C2C ? B6B46D2C ?
                                                   114E7B00 ? 0 ? 0 ? B6B59F50 ?
ksbrdp()+826         call     00000000             20018C2C ? 432A884E ? 0 ? 0 ?
                                                   0 ? 0 ?
opirip()+559         call     ksbrdp()             0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
opidrv()+515         call     opirip()             32 ? 4 ? BFFDB20C ?
sou2o()+80           call     opidrv()             32 ? 4 ? BFFDB20C ?
opimai_real()+230    call     sou2o()              BFFDB1F0 ? 32 ? 4 ?
                                                   BFFDB20C ?
ssthrdmain()+212     call     00000000             3 ? BFFDB338 ? 0 ? 4318AF14 ?
                                                   BFFDB2F4 ? 4317E670 ?
main()+147           call     ssthrdmain()         3 ? BFFDB338 ?
__libc_start_main()  call     00000000             1 ? BFFDB434 ? BFFDB43C ?
+220                                               4317E828 ? 0 ? 1 ?
_start()+33          call     __libc_start_main()  856F1C4 ? 1 ? BFFDB434 ?
                                                   BCF1440 ? BCF1430 ?
                                                   43170790 ?
--------------------- Binary Stack Dump ---------------------

通过上面的trace可以看出是2/3571中包含了事务,但是和3/8296[4号回滚段]回滚中的信息不相符,从而出现了在数据库启动回滚的时候出现该错误.查询mos[ID 1318986.1]发现这个是数据库的Bug 10205230比较相似,虽说在11.2.0.2中修复而且在asm中不受该影响,我这里库是11.2.0.3的asm rac照样出现该bug.

解决方法
通过alert日志提示object可以找到object_id=5887.当然也可以通过trace中的rdba来确定

SQL> col OBJECT_NAME for a30
SQL> select object_name,object_type,owner from dba_objects where object_id=5887;
OBJECT_NAME                    OBJECT_TYPE         OWNER
------------------------------ ------------------- ------------------------------
WRI$_ADV_MESSAGE_GROUPS_PK     INDEX               SYS
SQL> alter index sys.WRI$_ADV_MESSAGE_GROUPS_PK rebuild online;
Index altered.

补充说明:如果损坏对象是表,需要使用DBMS_REPAIR跳过坏块,然后重建表

重启数据库观察
数据库已经正常,开始报undo回滚段错误的记录已经不再存在,数据库恢复正常

Tue Jun 12 13:50:43 2012
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Tue Jun 12 13:51:11 2012
No Resource Manager plan active
Tue Jun 12 13:52:01 2012
Starting background process GTX0
Tue Jun 12 13:52:01 2012
GTX0 started with pid=29, OS id=14234
Starting background process RCBG
Tue Jun 12 13:52:04 2012
RCBG started with pid=41, OS id=14238
replication_dependency_tracking turned off (no async multimaster replication found)
Tue Jun 12 13:54:01 2012
Starting background process QMNC
Tue Jun 12 13:54:01 2012
QMNC started with pid=42, OS id=14279
Tue Jun 12 13:57:26 2012
Completed: ALTER DATABASE OPEN

使用bbed让rac中的sysaux数据文件online

一个朋友的11g rac库的sysaux表空间因某种原因缺少历史归档,导致无法正常online,是的数据库的很多功能受限.通过实现展示恢复过程.
模拟环境

SQL> select name,file#,status from v$datafile;
NAME                                                      FILE# STATUS
---------------------------------------------------- ---------- -------
+XIFENFEI/xff/datafile/system.256.776961315                   1 SYSTEM
+XIFENFEI/xff/datafile/sysaux.257.776961315                   2 ONLINE
+XIFENFEI/xff/datafile/undotbs1.258.776961317                 3 ONLINE
+XIFENFEI/xff/datafile/user_dd.dbf                            4 ONLINE
+XIFENFEI/xff/datafile/undotbs2.264.776961693                 5 ONLINE
+XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893           6 ONLINE
6 rows selected.
SQL> alter database datafile 2 offline;
Database altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   15
Current log sequence           15
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Next log sequence to archive   19
Current log sequence           20
--删除部分归档日志
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
XIFENFEI/
ASMCMD> cd data
ASMCMD> ls
XFF/
rac-cluster/
ASMCMD> cd xff
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
ONLINELOG/
ASMCMD> cd archivelog
ASMCMD> ls
2012_03_03/
2012_04_13/
2012_04_30/
2012_05_01/
2012_05_24/
2012_06_12/
ASMCMD> cd 2012_06_12
ASMCMD> ls
thread_1_seq_15.280.785752747
thread_1_seq_16.281.785752845
thread_1_seq_17.282.785752929
thread_1_seq_18.283.785753043
thread_1_seq_19.284.785753115
ASMCMD> rm thread_1_seq_16.281.785752845
ASMCMD> rm thread_1_seq_15.280.785752747

尝试online 数据文件

SQL> alter database datafile 2 online;
alter database datafile 2 online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '+XIFENFEI/xff/datafile/sysaux.257.776961315'
SQL> recover datafile 2;
ORA-00279: change 1155352 generated at 06/12/2012 08:20:10 needed for thread 1
ORA-00289: suggestion :
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-00280: change 1155352 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-15012: ASM file
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist
ORA-00308: cannot open archived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-15012: ASM file
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist

准备bbed修改数据文件
现在datafile 2不能恢复,我们需要修改的就是该datafile header 相关的scn等信息,另外拷贝一个数据文件出来做修改时候参考

RMAN> copy datafile 2 to  '/tmp/auxsys.dbf_rman';
Starting backup at 2012-06-12 08:59:07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 instance=XFF1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+XIFENFEI/xff/datafile/sysaux.257.776961315
output file name=/tmp/auxsys.dbf_rman tag=TAG20120612T090029 RECID=1 STAMP=785754322
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:50
Finished backup at 2012-06-12 09:05:36
RMAN>  copy datafile 4 to '/tmp/user.dbf_rman';
Starting backup at 2012-06-12 09:09:28
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+XIFENFEI/xff/datafile/user_dd.dbf
output file name=/tmp/user.dbf_rman tag=TAG20120612T090932 RECID=2 STAMP=785754582
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 2012-06-12 09:09:48

bbed修改datafile header

[oracle@rac1 tmp]$ bbed password=blockedit listfile=/tmp/o_bbed  mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Jun 12 09:37:30 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /tmp/auxsys.dbf_rman                                                 0
     2  /tmp/user.dbf_rman                                                   0
BBED> set file 2 block 1
        FILE#           2
        BLOCK#          1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
   struct kcvcpscn, 8 bytes                 @484
      ub4 kscnbas                           @484      0x0011a787
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x2ed5a9cd
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500
      struct kcvcprba, 12 bytes             @500
         ub4 kcrbaseq                       @500      0x00000014
         ub4 kcrbabno                       @504      0x000000c5
         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> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000086
BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000085
BBED> set file 1 block 1
        FILE#           1
        BLOCK#          1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
   struct kcvcpscn, 8 bytes                 @484
      ub4 kscnbas                           @484      0x0011a118
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x2ed59e3a
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500
      struct kcvcprba, 12 bytes             @500
         ub4 kcrbaseq                       @500      0x0000000f
         ub4 kcrbabno                       @504      0x0000c4ed
         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> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000079
BBED>  p kcvfhccc
ub4 kcvfhccc                                @148      0x00000078
/*
确定需要修改项kscnbas/kcvcptim/kcvfhcpc/kcvfhccc的相关信息
*/
BBED> set count 16
        COUNT           16
BBED> d file 2 block 1 offset 484
 File: /tmp/user.dbf_rman (2)
 Block: 1                Offsets:  484 to  499           Dba:0x00800001
------------------------------------------------------------------------
 87a71100 00001000 cda9d52e 01000000
 <32 bytes per line>
BBED> m /x 87a71100 file 1 block 1 offset 484
BBED-00209: invalid number (87a71100)
BBED> m /x 87a7 file 1 block 1 offset 484
 File: /tmp/auxsys.dbf_rman (1)
 Block: 1                Offsets:  484 to  499           Dba:0x00400001
------------------------------------------------------------------------
 87a71100 00000000 3a9ed52e 01000000
 <32 bytes per line>
BBED> d file 2 block 1 offset 492
 File: /tmp/user.dbf_rman (2)
 Block: 1                Offsets:  492 to  507           Dba:0x00800001
------------------------------------------------------------------------
 cda9d52e 01000000 14000000 c5000000
 <32 bytes per line>
BBED> m /x cda9d52e file 1 block 1 offset 492
BBED-00209: invalid number (cda9d52e)
BBED> d file 1 block 1 offset 492
 File: /tmp/auxsys.dbf_rman (1)
 Block: 1                Offsets:  492 to  507           Dba:0x00400001
------------------------------------------------------------------------
 3a9ed52e 01000000 0f000000 edc40000
 <32 bytes per line>
BBED> m /x cda9 file 1 block 1 offset 492
 File: /tmp/auxsys.dbf_rman (1)
 Block: 1                Offsets:  492 to  507           Dba:0x00400001
------------------------------------------------------------------------
 cda9d52e 01000000 0f000000 edc40000
 <32 bytes per line>
BBED> d file 1 block 1 offset 140
 File: /tmp/auxsys.dbf_rman (1)
 Block: 1                Offsets:  140 to  155           Dba:0x00400001
------------------------------------------------------------------------
 79000000 2970bc2e 78000000 00000000
 <32 bytes per line>
BBED> d file 2 block 1 offset 140
 File: /tmp/user.dbf_rman (2)
 Block: 1                Offsets:  140 to  155           Dba:0x00800001
------------------------------------------------------------------------
 86000000 2970bc2e 85000000 00000000
 <32 bytes per line>
BBED> m /x 86000000 file 1 block 1 offset 140
BBED-00209: invalid number (86000000)
BBED> m /x 8600 file 1 block 1 offset 140
 File: /tmp/auxsys.dbf_rman (1)
 Block: 1                Offsets:  140 to  155           Dba:0x00400001
------------------------------------------------------------------------
 86000000 2970bc2e 78000000 00000000
 <32 bytes per line>
BBED> d file 2 block 1 offset 148
 File: /tmp/user.dbf_rman (2)
 Block: 1                Offsets:  148 to  163           Dba:0x00800001
------------------------------------------------------------------------
 85000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 8500 file 1 block 1 offset 148
 File: /tmp/auxsys.dbf_rman (1)
 Block: 1                Offsets:  148 to  163           Dba:0x00400001
------------------------------------------------------------------------
 85000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> set file 1 block 1
        FILE#           1
        BLOCK#          1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
   struct kcvcpscn, 8 bytes                 @484
      ub4 kscnbas                           @484      0x0011a787
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x2ed5a9cd
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500
      struct kcvcprba, 12 bytes             @500
         ub4 kcrbaseq                       @500      0x0000000f
         ub4 kcrbabno                       @504      0x0000c4ed
         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> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000086
BBED>  p kcvfhccc
ub4 kcvfhccc                                @148      0x00000085
BBED> sum apply
Check value for File 1, Block 1:
current = 0x48c4, required = 0x48c4

使用修改后数据文件尝试online

SQL> alter database rename file '+XIFENFEI/xff/datafile/sysaux.257.776961315' to '/tmp/auxsys.dbf_rman';
Database altered.
SQL> recover database datafile 2 ;
ORA-00274: illegal recovery option DATAFILE
SQL> recover database datafile 2;
ORA-00274: illegal recovery option DATAFILE
SQL> recover datafile 2;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/tmp/auxsys.dbf_rman'
ORA-01207: file is more recent than control file - old control file

尝试重建控制文件

SQL> alter database backup controlfile to trace as '/tmp/xifenfei.ctl';
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
SQL> @xifenfei_ctl
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
--在rac中重建控制文件需要设置cluster_database=FALSE
SQL> alter system set  cluster_database=FALSE scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
SQL> @xifenfei_ctl
Control file created.

online数据文件
重建控制文件恢复数据库之后 datafile 2自动online成功,省去了手工处理麻烦,如果没有自动online,请手工处理

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> col name for a52
SQL> select name,file#,status from v$datafile;
NAME                                                      FILE# STATUS
---------------------------------------------------- ---------- -------
+XIFENFEI/xff/datafile/system.256.776961315                   1 SYSTEM
/tmp/auxsys.dbf_rman                                          2 ONLINE
+XIFENFEI/xff/datafile/undotbs1.258.776961317                 3 ONLINE
+XIFENFEI/xff/datafile/user_dd.dbf                            4 ONLINE
+XIFENFEI/xff/datafile/undotbs2.264.776961693                 5 ONLINE
+XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893           6 ONLINE
6 rows selected.

文件系统中的datafile 2 恢复到asm中

SQL> alter database datafile 2 offline;
Database altered.
RMAN> copy datafile 2 to '+XIFENFEI';
Starting backup at 2012-06-12 10:55:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/tmp/auxsys.dbf_rman
output file name=+XIFENFEI/xff/datafile/sysaux.257.785761227 tag=TAG20120612T105800 RECID=1 STAMP=785762097
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:16:24
Finished backup at 2012-06-12 11:15:05
RMAN> switch datafile 2 to copy;
datafile 2 switched to datafile copy "+XIFENFEI/xff/datafile/sysaux.257.785761227"
RMAN> recover datafile 2;
Starting recover at 2012-06-12 11:30:32
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:01:30
Finished recover at 2012-06-12 11:34:11
RMAN> sql 'alter database datafile 2 online';
sql statement: alter database datafile 2 online

验证和收尾工作

SQL> select name,file#,status from v$datafile;
NAME                                                      FILE# STATUS
---------------------------------------------------- ---------- -------
+XIFENFEI/xff/datafile/system.256.776961315                   1 SYSTEM
+XIFENFEI/xff/datafile/sysaux.257.785761227                   2 ONLINE
+XIFENFEI/xff/datafile/undotbs1.258.776961317                 3 ONLINE
+XIFENFEI/xff/datafile/user_dd.dbf                            4 ONLINE
+XIFENFEI/xff/datafile/undotbs2.264.776961693                 5 ONLINE
+XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893           6 ONLINE
SQL> alter system set  cluster_database=true scope=spfile;
System altered.
--然后重启节点

resmgr:cpu quantum等待

昨天晚上数据库升级(使用exp/imp从9i升级到11g),开启业务,数据库出现很多resmgr:cpu quantum等待

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> SELECT sid,event FROM v$session WHERE wait_class#<>6;
       SID EVENT
---------- ----------------------------------------------------------------
        27 resmgr:cpu quantum
        38 resmgr:cpu quantum
        43 resmgr:cpu quantum
        46 resmgr:cpu quantum
       113 resmgr:cpu quantum
       118 resmgr:cpu quantum
       125 resmgr:cpu quantum
       140 resmgr:cpu quantum
       143 resmgr:cpu quantum
       199 resmgr:cpu quantum
       205 resmgr:cpu quantum
       SID EVENT
---------- ----------------------------------------------------------------
       212 resmgr:cpu quantum
       220 resmgr:cpu quantum
       221 resmgr:cpu quantum
       223 resmgr:cpu quantum
       238 resmgr:cpu quantum
       241 resmgr:cpu quantum
       301 resmgr:cpu quantum
       313 resmgr:cpu quantum
       314 resmgr:cpu quantum
       405 resmgr:cpu quantum
       410 resmgr:cpu quantum
       SID EVENT
---------- ----------------------------------------------------------------
       415 resmgr:cpu quantum
       435 resmgr:cpu quantum
       502 resmgr:cpu quantum
       503 resmgr:cpu quantum
       509 resmgr:cpu quantum
       510 resmgr:cpu quantum
       512 resmgr:cpu quantum
       521 resmgr:cpu quantum
       526 resmgr:cpu quantum
       528 resmgr:cpu quantum
       532 resmgr:cpu quantum
       SID EVENT
---------- ----------------------------------------------------------------
       533 enq: TX - row lock contention
       589 resmgr:cpu quantum
       596 resmgr:cpu quantum
       600 resmgr:cpu quantum
       609 resmgr:cpu quantum
       611 resmgr:cpu quantum
       625 resmgr:cpu quantum
       635 null event
       707 resmgr:cpu quantum
       727 resmgr:cpu quantum
       731 SQL*Net message to client
44 rows selected.

查询alert日志

Sat Jun 09 06:00:00 2012
Setting Resource Manager plan SCHEDULER[0x310C]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Jun 10 02:00:00 2012
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Sun Jun 10 06:00:00 2012
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon Jun 11 02:00:00 2012
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Mon Jun 11 22:00:00 2012
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Tue Jun 12 02:00:00 2012
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Tue Jun 12 22:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3108]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Wed Jun 13 02:00:00 2012
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter

从这里可以看出来，因为SCHEDULER定时启动和关闭资源管理的DEFAULT_MAINTENANCE_PLAN从而导致在晚上10点到2点Resource Manager plan处于启用状态.上线测试刚好在晚上2点之前,所有当时查询的时候发现很多resmgr:cpu quantum等待是因为Resource Manager plan启用导致(使用SCHEDULER控制其启用和关闭),很多情况下数据库跑的应用比较单一，不是十分的需要启动资源管理.
在11g中关闭方法如下

1. Set the current resource manager plan to null (or another plan that is not restrictive):
alter system set resource_manager_plan='' scope=both;
2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:
execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');
SQL> select WINDOW_NAME  from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME
------------------------------
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW
WEEKNIGHT_WINDOW
WEEKEND_WINDOW
9 rows selected.

OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ参数说明

OPTIMIZER_INDEX_COST_ADJ参数说明
OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
这个数反映执行多块IO（全表扫描）的成本与执行单个IO（索引读取）的成本。保持为100，则多块IO与单块IO成本相同。设为50优化程序认为访问单块IO的成本为多块IO的一半。

OPTIMIZER_INDEX_COST_ADJ参数试验

SQL> create table t_xifenfei
  2  as
  3   select object_id,object_name from dba_objects where rownum<101;
Table created.
SQL> create index ind_t_xifenfei on t_xifenfei(object_id);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL>  show parameter OPTIMIZER_INDEX;
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_index_caching              integer                0
optimizer_index_cost_adj             integer                100
SQL> set linesize 150
SQL> set autot trace ext
SQL> select object_name from t_xifenfei where object_id>100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2444553208
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI     |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">100)
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=1000;
Session altered.
SQL> select object_name from t_xifenfei where object_id>100;
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    11 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    11 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID">100)

通过试验证明修改OPTIMIZER_INDEX_COST_ADJ会导致执行计划走index还是全表扫描

OPTIMIZER_INDEX_CACHING参数说明
OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.You can modify the optimizer’s assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.
这个表明的是在nested loops joins and IN-list iterators的时候,如果使用了OPTIMIZER_INDEX_CACHING参数,表明两个表关联的时候优化器考虑index cache的比例,从而选择不同的执行计划.而不是网上所说的优化器考虑所有情况下的index的cache情况(这个参数只有在nested loops joins and IN-list iterators表关联的时候的index才会被优化器考虑[index cache的比例]).进一步说明:这个参数影响两个表关联的时候是选择hash jion还是nested loops joins/sort-merge joins

总结说明
1.关于OPTIMIZER_INDEX_CACHING的参数效果我未试验出来(可能方法不正确)
2.根据网上建议在oltp系统中设置
optimizer_index_caching = 0 optimizer_index_cost_adj = 100 的默认值，一般时候数据仓库报表系统。
optimizer_index_caching = 90 optimizer_index_cost_adj = 25-50 一般时候事务处理/OLTP系统
3.设置这些参数可能存在bug

因IPC导致多个监听不能正常启动

在一台机器上装了9.2.0.4和10.1.0.4数据库,启动监听时候发现有一个启动不了
9i监听配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei.com)(PORT = 1522))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = xifenfei)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (SID_NAME = xff)
    )
  )

10g监听配置

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u02/app/oracle/db10g)
      (PROGRAM = extproc)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei.com)(PORT = 1521))
      )
    )
  )

分别启动监听
启动10g监听

[oraem@xifenfei ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.1.0.4.0 - Production on 12-JUN-2012 15:28:51
Copyright (c) 1991, 2004, Oracle.  All rights reserved.
Starting /u02/app/oracle/db10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.1.0.4.0 - Production
System parameter file is /u02/app/oracle/db10g/network/admin/listener.ora
Log messages written to /u02/app/oracle/db10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.1.0.4.0 - Production
Start Date                12-JUN-2012 15:28:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/db10g/network/admin/listener.ora
Listener Log File         /u02/app/oracle/db10g/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

启动9i监听

[oracle@xifenfei ~]$ lsnrctl start
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:01:44
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
Starting /u01/oracle/9.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use
Listener failed to start. See the error message(s) above...

这里很奇怪两个监听使用不同的端口,为什么不能提示Address被占用呢,难道9i的已经启动了

查看9i的监听状态

[oracle@xifenfei ~]$ lsnrctl status
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:02:23
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1522)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12618: TNS:versions are incompatible

9i监听不能启动原因
这里有重大发现TNS:versions are incompatible.我这台服务器只有一个9i和一个10g的数据库出现版本不兼容,那就是说这个命令可能访问了10g的库中的每个监听信息.继续往上看,发现(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))).
When a process is on the same machine as the server, use the IPC protocol for connectivity instead of TCP. Inner Process Communication on the same machine does not have the overhead of packet building and deciphering that TCP has.
通过这段话可以看出IPC(Inner Process Communication)是使用于本机的内部通讯,不用包的封装,可以很大程度上提高程序执行效率.看到上面的IPC提示,我们可以确定该错误是因为10g中已经启用了IPC,然后9i的监听也要来启动这个,从而导致该错误.

解决办法
因为在同一个机器上,只能其中的一个数据库启用IPC.修改9i的监听配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      )
    #  (ADDRESS_LIST =
    #    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    #  )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = xifenfei)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (SID_NAME = xff)
    )
  )

启动9i监听

[oracle@xifenfei ~]$ lsnrctl
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:12:55
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start
Starting /u01/oracle/9.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                12-JUN-2012 15:12:57
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

查看两个监听进程

[oracle@xifenfei ~]$ ps -ef|grep tns|grep -v grep
oraem    18099     1  0 13:27 ?        00:00:00 /u02/app/oracle/db10g/bin/tnslsnr LISTENER -inherit
oracle   24312     1  0 15:12 pts/0    00:00:00 /u01/oracle/9.2.0/db_1/bin/tnslsnr LISTENER -inherit

因使用OEM引起ORA-00600[12761]

alert日志报ORA-00600[12761]错

Sun Jun 10 13:52:56 2012
Errors in file e:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_19840.trc:
ORA-04030: 在尝试分配 82444 字节 (pga heap,control file i/o buffer) 时进程内存不足
ORA-00600: 内部错误代码, 参数: [12761], [], [], [], [], [], [], []
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-04030: 在尝试分配 123404 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足

数据库版本信息

Sun Jun 10 13:52:56 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V5.2 Service Pack 2
CPU                 : 8 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1263M/4095M, Ph+PgF:2716M/5976M, VA:19M/2047M
Instance name: interlib

trace信息

*** 2012-06-10 13:52:56.763
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [12761], [], [], [], [], [], [], []
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-04030: 在尝试分配 123404 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足
Current SQL statement for this session:
BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0BC35C44         1  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
00404686             CALLrel  00404694             0 1
0040307E             CALLrel  00404660             0
0043AB6A             CALLrel  00402CFC             3
603A816A             CALLreg  00000000             6190E2E0 3
603A8550             CALLrel  603A80D8             6190E2E0 5E340020 31D9 0
                                                   5ED6CDF8
031B7197             CALLrel  025FA21E             6190E2E0 5E340020 31D9 2 0
02C92859             CALLrel  02C92360             5ED6D2B4 5ED6D3CC 2 61BA71E4
                                                   0 5ED6CEFA
60BAD7C6             CALL???  00000000             5ED6D2B4 5ED6D3CC 3 61BA71E4
                                                   0 5ED6CEFA
60C41C40             CALLrel  60BAD758             5D9356A0 F1 3 5E344888
60C3C780             CALL???  00000000             5D9356A0 951F190 5D9356DC
60C3D1BD             CALLrel  60C3C748             5D9356A0 95A97F0 5D9356DC
60BB0392             CALLrel  60C3CEB0             5D9356A0
60B89393             CALLrel  60BB00B0             5D9356A0 1 0
02600CD9             CALLrel  0260F22C
0140AF2C             CALLrel  02600B3C             4E8EC08
013CBFEC             CALLrel  01409984             49 3 5ED6DB14
0085174B             CALLreg  00000000             5E 17 5ED6F6F8
60FEFF8D             CALLreg  00000000             5E 17 5ED6F6F8 0
00850A69             CALL???  00000000
0122134B             CALLrel  00850670             0 0
0085174B             CALLreg  00000000             3C 4 5ED6FC90
00420E53             CALLrel  00851300             3C 4 5ED6FC90 0
00421645             CALLrel  00420B20             3C 4 5ED6FC90
0040116C             CALLrel  00421618             5ED6FC84 3C 4 5ED6FC90
0040105C             CALLrel  004010FC             2 5ED6FCBC
00401900             CALLrel  00401000
7C82482C             CALLreg  00000000
--------------------- Binary Stack Dump ---------------------
--会话信息
    (session) sid: 525 trans: 00000000, creator: 7AE024D8, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-001B-00000004, short-term DID: 0000-0000-00000000
              txn branch: 00000000
              oct: 47, prv: 0, sql: 7A0F0A38, psql: 7A0A2430, user: 51/SYSMAN
    O/S info: user: , term: , ospid: 1234, machine: tushuguan01
              program: OMS
    client info: tushuguan01_Management_Service
    application name: OEM.SystemPool, hash value=2960518376

通过这里我们可以得到几个信息
1.数据库先发生了ORA-00600[12761],然后引发了ORA-04030
2.引发ORA-00600[12761]错误的原因是因为OEM的某种操作导致
3.未知因某种原因导致Call Stack Trace信息不完善,无法准确评估bug情况
4.查询数据库当前最大使用使用pga为250M,数据库配置pga为500M,原则上讲不是pga消耗完导致4030错误,可能是这个会话在执行某个基表的查询时候的hash-jion运算时pga不足导致.
5.查询dba_users发现EM_PING不是数据库用户,查询dba_source发现RECORD_BATCH_HEARTBEAT不是plsql名称,从这里可以看出OEM调用程序有一定特殊性

对于该问题的解决方案
1.因为OEM功能不太使用,建议直接关闭该进程,并设置为开机不自动启动
2.因为信息不完善,无法确定具体bug,但目前数据库版本为10.2.0.1,强烈建议升级到新版本

autotrace显示Statistics很多信息为0

一朋友使用autotrace查看数据库执行计划发现结果如下,Statistics中很多信息为0,这个肯定是不正常现象,什么都可以为0,consistent gets也不可能为0.

SQL> set autot on
SQL> select count(*) from RACV_DATA.PARTY_DUMMY;
  COUNT(*)
----------
        47
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3621440939
--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| PARTY_DUMMY |    47 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

解决方法

SQL>  select owner,object_name from dba_objects where object_name='PLAN_TABLE';
no rows selected
SQL> @?/rdbms/admin/utlxplan.sql
Table created.

重新查看Statistics信息

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

记录一次ORA-00600[2252]故障解决

数据库alert日志报ORA-00600[2252]

Wed Jun 06 08:56:02 2012
Thread 1 cannot allocate new log, sequence 552
Checkpoint not complete
  Current log# 1 seq# 551 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Thread 1 advanced to log sequence 552
  Current log# 2 seq# 552 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Sun Jun 06 09:39:19 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_3344.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076554712], [], [], [], [], []
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_3344.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076554712], [], [], [], [], []
Sun Jun 06 10:19:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4904.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555573], [], [], [], [], []
Sun Jun 06 10:20:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5984.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555594], [], [], [], [], []
Sun Jun 06 10:21:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4204.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555614], [], [], [], [], []
Sun Jun 06 10:22:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5896.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555634], [], [], [], [], []
Sun Jun 06 10:23:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4612.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555654], [], [], [], [], []
Sun Jun 06 10:24:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4696.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555676], [], [], [], [], []
Sun Jun 06 10:25:50 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5568.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555696], [], [], [], [], []
Sun Jun 06 10:26:50 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5776.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555716], [], [], [], [], []
--启动数据库
Mon Jun 07 09:18:39 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Mon Jun 07 09:18:49 2010
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 100663296
  __large_pool_size        = 12582912
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  spfile                   = D:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
  nls_language             = SIMPLIFIED CHINESE
  nls_territory            = CHINA
  sga_target               = 452984832
  control_files            = D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
  db_block_size            = 8192
  __db_cache_size          = 327155712
  compatible               = 10.2.0.1.0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = D:\oracle\product\10.2.0/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (protocol=TCP)
  shared_servers           = 1
  job_queue_processes      = 10
  audit_file_dest          = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
  background_dump_dest     = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
  user_dump_dest           = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
  core_dump_dest           = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
  db_name                  = orcl
  open_cursors             = 300
  pga_aggregate_target     = 149946368
PSP0 started with pid=3, OS id=3028
MMAN started with pid=4, OS id=3528
PMON started with pid=2, OS id=2772
DBW0 started with pid=5, OS id=816
CKPT started with pid=7, OS id=3372
SMON started with pid=8, OS id=2584
RECO started with pid=9, OS id=3976
CJQ0 started with pid=10, OS id=1912
MMON started with pid=11, OS id=624
Mon Jun 07 09:19:00 2010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=2696
Mon Jun 07 09:19:00 2010
starting up 1 shared server(s) ...
LGWR started with pid=6, OS id=3128
Mon Jun 07 09:19:04 2010
alter database mount exclusive
Mon Jun 07 09:19:09 2010
Setting recovery target incarnation to 2
Mon Jun 07 09:19:10 2010
Successful mount of redo thread 1, with mount id 1248834568
Mon Jun 07 09:19:10 2010
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Mon Jun 07 09:19:10 2010
alter database open
Mon Jun 07 09:19:15 2010
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Mon Jun 07 09:19:18 2010
Started redo scan
Mon Jun 07 09:19:19 2010
Completed redo scan
 13 redo blocks read, 7 data blocks need recovery
Mon Jun 07 09:19:20 2010
Started redo application at
 Thread 1: logseq 552, block 28631
Mon Jun 07 09:19:20 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 552 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Mon Jun 07 09:19:20 2010
Completed redo application
Mon Jun 07 09:19:20 2010
Completed crash recovery at
 Thread 1: logseq 552, block 28644, scn 12176013920948
 7 data blocks read, 7 data blocks written, 13 redo blocks read
Mon Jun 07 09:19:28 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2688.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076604085], [], [], [], [], []

通过这些日志可以看出数据库一直在报ORA-00600[2252],在后来因某种原因数据库异常重启后启动不了.仔细观察可以发现系统显示的时间是2010年6月7日和当前时间相差了整整两年.
At any point in time, the Oracle Database calculates a “not to exceed” limit for the number of SCNs a database can have used, based on the number of seconds elapsed since 1988, multiplied by 16,384. This is known as the database’s current maximum SCN limit. Doing this ensures that Oracle Databases will ration SCNs over time, allowing over 500 years of data processing for any Oracle Database.

错误原因
根据错误提示计算scn(2834为现在系统的SCN WRAP，4076604085就是BASE)=2834*2^32+4076604085=12176013920949
根据数据库日志显示系统时间计算最大scn值:

SQL>select to_number( ((to_date('20100607 09:19:28','yyyymmdd hh24:mi:ss')-
  2 to_date('19880101','yyyymmdd'))*24*3600*16*1024),'999999999999999999') max_scn from dual;
   MAX_SCN
--------------
11598377254912

通过这里的计算可以知道数据库当前的SCN大于系统时间点上允许的最大时间的SCN,从而出现ORA-00600[2252]错误.

解决方法
知道了数据库报该错误的原因,那么解决该问题很简单,修改系统时间到正确的时间点即可

SQL> select to_number(((sysdate-to_date('19880101','yyyymmdd'))*24*3600*16*1024),
   2 '999999999999999999') max_scn from dual;
   MAX_SCN
--------------
12634899464192

该SCN大于数据库当前SCN所有数据库不会报ORA-00600[2252]错误可以正常启动.

记录另一起ORA-00600[13013]处理

发现ORA-00600[13013]错误
During the execution of an UPDATE statement, after several attempts (Arg [a] passcount) we are unable to get a stable set of rows that conform to the WHERE clause.

Fri Jun  1 03:00:33 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_12104.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Sat Jun  2 03:01:05 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_1052.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Sun Jun  3 15:00:50 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_13876.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12603219], [17], []
Mon Jun  4 03:01:05 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_7704.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Tue Jun  5 03:00:35 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_27983.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Wed Jun  6 03:01:07 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_19204.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Thu Jun  7 03:00:37 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_7273.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12605556], [17], []

以前处理过一次ORA-600[13013],里面包含了各参数含义,这次也按照常规方法处理,分析如下:
1.通过trace文件找出对应表

*** 2012-06-01 03:00:33.325
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Current SQL statement for this session:
UPDATE WRH$_SQL_BIND_METADATA SET snap_id = :lah_snap_id  WHERE dbid = :dbid    AND (SQL_ID)
IN (SELECT STR1_KEWRATTR FROM X$KEWRATTRSTALE)

2.通过ORA-600[13013]中表示rdba参数找出表

SQL> select  DBMS_UTILITY.data_block_address_file (12596577) "file#",
  2  DBMS_UTILITY.data_block_address_block (12596577) "block#"
  3  from dual;
     file#     block#
---------- ----------
         3      13665
SQL> select * from dba_extents where 13665 between block_id and block_id + blocks and file_id=3;
OWNER      SEGMENT_NAME           SEGMENT_TYPE     EXTENT_ID    FILE_ID   BLOCK_ID    BLOCKS
---------- --------------------  --------------- --------------- ---------- -------  ------
SYS        SYS_LOB0000008933C00  LOBSEGMENT           7          3      13657         8
SYS        WRH$_SQL_BIND_METADA  TABLE                1          3      13665         8

检查对象WRH$_SQL_BIND_METADA是否有坏块或者表和index不一致

SQL> analyze table SYS.WRH$_SQL_BIND_METADATA validate structure cascade online;
Table analyzed.

这里分析WRH$_SQL_BIND_METADA表正常,但是通过上面的查询证明WRH$_SQL_BIND_METADA的第一个extent的第一个数据块上可能出现问题,使得analyze未检查(自己猜猜,未做深入验证).针对这个问题,直接备份WRH$_SQL_BIND_METADATA表,truncate掉该表,然后重新插入数据(注意操作时间避开awr插入数据时间段)

create table SQL_BIND_METADATA_BAK
AS
SELECT * FROM SYS.WRH$_SQL_BIND_METADATA;
TRUNCATE TABLE SYS.WRH$_SQL_BIND_METADATA;
INSERT INTO SYS.WRH$_SQL_BIND_METADATA
SELECT * FROM SQL_BIND_METADATA_BAK;
DROP TABBLE SQL_BIND_METADATA_BAK PURGE;

ORA-600[6749] 发生在 SYSMAN.MGMT_METRICS_RAW表

数据库alert日志长时间出现ORA-00600[6749]错误
日志报错如下

Fri Jun  1 12:01:30 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_396.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []
Fri Jun  1 12:01:34 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_396.trc:
Fri Jun  1 13:01:06 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_13226.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []
Fri Jun  1 13:01:10 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_13226.trc:
Fri Jun  1 14:01:46 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_26691.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []
Fri Jun  1 14:01:51 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_26691.trc:
Fri Jun  1 15:01:21 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_7119.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []

查看trace日志

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/product/10/oraapp
System name:	Linux
Node name:	oracle2
Release:	2.6.18-92.el5
Version:	#1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:	x86_64
Instance name: oraapp
Redo thread mounted by this instance: 1
Oracle process number: 44
Unix process pid: 26691, image: oracle@oracle2 (J000)
*** ACTION NAME:(target 5) 2012-06-01 14:01:00.298
*** MODULE NAME:(Oracle Enterprise Manager.rollup) 2012-06-01 14:01:00.298
*** SERVICE NAME:(SYS$USERS) 2012-06-01 14:01:00.298
*** SESSION ID:(406.24103) 2012-06-01 14:01:00.298
Dumping current redo log in thread 1
DUMP OF REDO FROM FILE '/opt/oracle/oradata/oraapp/systable/redo03.log'
 Opcodes 11.*
 DBAs (file#, block#):
      (3, 13970)
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
	Compatibility Vsn = 169869568=0xa200100
	Db ID=1462349529=0x5729aed9, Db Name='ORAAPP'
	Activation ID=1462334681=0x572974d9
	Control Seq=2614156=0x27e38c, File size=245760=0x3c000
	File Number=3, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000003963, SCN 0x0000129fc9df-0xffffffffffff"

猜测ORA-600[6749]部分参数

SQL> select  DBMS_UTILITY.data_block_address_file (12596882) "file#",
  2  DBMS_UTILITY.data_block_address_block (12596882) "block#"
  3  from dual;
     file#     block#
---------- ----------
         3      13970

ORA-600[6749][a][b]{c}
这里证明c表示rdba

根据dba查询对象

SQL> select * from dba_extents where 13970 between block_id and block_id + blocks and file_id=3;
OWNER      SEGMENT_NAME                     SEGMENT_TYPE
---------- ------------------------------- -------------------
SYSMAN        SYS_IOT_OVER_10448                 TABLE
SQL> select owner,iot_name from dba_tables where table_name = 'SYS_IOT_OVER_10448';
OWNER                          IOT_NAME
------------------------------ ------------------------------
SYSMAN                         MGMT_METRICS_RAW
SQL>  ANALYZE TABLE SYSMAN.MGMT_METRICS_RAW  VALIDATE STRUCTURE CASCADE;
Table analyzed.

按照常理ORA-00600[6749]错误是因为坏块或者表和索引数据不一致导致,通过ANALYZE可以检查出来.这里显示正常,那可能是其他原因导致,查询MOS果然发现是ORA-600 [6749] Occurring on SYSMAN.MGMT_METRICS_RAW [ID 467439.1]

解决方法

The following workaround may resolve the problem temporarily:
1. Ensure you have a good backup before proceeding.
2. Create a copy of the SYSMAN.MGMT_METRICS_RAW table:
SQL> create table SYSMAN.MGMT_METRICS_RAW_COPY
as select * from SYSMAN.MGMT_METRICS_RAW;
3. Truncate the table:
SQL> truncate table SYSMAN.MGMT_METRICS_RAW;
May need  to disable trigger: "sysman.raw_metrics_after_insert" before proceeding.
Re-enable after the insert.
4. Re-insert the rows:
SQL> insert into SYSMAN.MGMT_METRICS_RAW
select * from SYSMAN.MGMT_METRICS_RAW_COPY;
SQL> commit;
5. Drop the copy table:
SQL> drop table SYSMAN.MGMT_METRICS_RAW_COPY;