DATAGUARD中MAXIMUM AVAILABILITY+LGWR SYNC导致主库不能启动

qq群里面的一朋友的的DG因为备库已经下架,主库重启的时候不能正常启动,帮忙处理结果如下
版本相关信息

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /export/home/oracle/product/9.2.0
System name:    SunOS
Node name:      eTermSrv2
Release:        5.10
Version:        Generic
Machine:        sun4u
Instance name: abcd

数据库不能启动日志

Fri Aug 10 12:37:56 2012
ALTER DATABASE OPEN
Fri Aug 10 12:37:56 2012
LGWR: Primary database is in CLUSTER CONSISTENT mode
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNS0 started with pid=16
Fri Aug 10 12:37:59 2012
ORA-1013 signalled during: ALTER DATABASE OPEN...
Fri Aug 10 12:41:45 2012
LGWR: Error 12535 verifying archivelog destination LOG_ARCHIVE_DEST_2
LGWR: Continuing...
Fri Aug 10 12:41:45 2012
Errors in file /export/home/oracle/admin/abcd/bdump/abcd_lgwr_11504.trc:
ORA-12535: TNS:operation timed out
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Creating archive destination LOG_ARCHIVE_DEST_2: 'ora9i'
LGWR: Error 12535 creating archivelog file 'ora9i'
Fri Aug 10 12:45:32 2012
Errors in file /export/home/oracle/admin/abcd/bdump/abcd_lgwr_11504.trc:
ORA-12535: TNS:operation timed out
LGWR: Completed archiving  log 1 thread 1 sequence 6808
Thread 1 advanced to log sequence 6808
Fri Aug 10 12:45:32 2012
Errors in file /export/home/oracle/admin/abcd/bdump/abcd_lgwr_11504.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/export/home/oracle/oradata/abcd/system01.dbf'
LGWR: terminating instance due to error 1157
Instance terminated by LGWR, pid = 11504

通过这里大概看出数据库原dg配置是MAXIMUM AVAILABILITY,因为备机下架,导致ora9i的tns不能访问,从而出现一些列错误,其中使得lgwr异常,因为oracle的某种内部机制,导致dbwr不能访问数据文件(这里体现出来是system01.dbf不能访问,但实际上应该是所有所有数据文件均不能访问,因为system01.dbf位于第一,所以报出该错误.)

*** SESSION ID:(3.1) 2012-08-10 12:37:56.847
Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
*** 2012-08-10 12:41:45.614
Error 12535 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'ora9i'
*** 2012-08-10 12:41:45.615
LGWR: Error 12535 verifying archivelog destination LOG_ARCHIVE_DEST_2
Continuing...
ORA-12535: TNS:operation timed out
*** 2012-08-10 12:45:32.514
Error 12535 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'ora9i'
Error 12535 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'ora9i'
*** 2012-08-10 12:45:32.514
LGWR: Error 12535 creating archivelog file 'ora9i'
*** 2012-08-10 12:45:32.514
kcrrfail: dest:2 err:12535 force:0
ORA-12535: TNS:operation timed out
error 1157 detected in background process
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/export/home/oracle/oradata/abcd/system01.dbf'

通过trace文件,更加清楚的说明,可能是因为lgwr异常导致dbwr访问数据文件出现问题.

问题分析/解决汇总

SQL> show parameter log_archive_dest_state_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1             string      enable

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      enable

SQL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/export/home/oracle/o
                                                 radata/abcd/archive

SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=ora9i LGWR SYNC AFFIRM

SQL>select  protection_mode,database_role from v$database;

PROTECTION_MODE      DATABASE_ROLE
-------------------- ----------------
MAXIMUM AVAILABILITY PRIMARY

SQL>   show parameter succ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest         integer     1

通过上面的sql查询结果和alert日志与trace文件结合分析,大体结论是:
数据库的dg的保护模式为:MAXIMUM AVAILABILITY
数据库的日志传输方式是:LGWR SYNC AFFIRM
现在因为备机下架,主库LGWR不能通过tns访问备库,从而导致数据库的dbwr进程访问异常,是的数据库不能正常被open
这里的故障有一些巧合:MAXIMUM AVAILABILITY+LGWR SYNC AFFIRM+9.2.0.1+SunOS
我不清楚是不是ORACLE的bug导致,但是这个问题解决起来比较简单,只需要修改log_archive_dest_state_2=defer,使得log_archive_dest_2参数不生效,让lgwr不再访问备机

处理smon清理临时段导致数据库异常案例

一个朋友的数据库在经过自己的千辛万苦终于open成功,但是几分钟就down掉,使得他想导出数据重建库的目标不能实现.让我帮忙处理
alert日志报ORA-00600[kafspa:columnBuffer1]

Wed Aug  8 10:55:31 2012
Completed: ALTER DATABASE OPEN
Wed Aug  8 10:55:41 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_12160.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:55:47 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 2 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:55:47 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:55:58 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 3 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:55:59 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:56:10 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 4 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:56:11 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:56:22 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 5 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:56:32 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:56:43 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 6 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:56:53 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:57:04 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 7 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:57:14 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:57:25 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 8 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:57:35 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:57:38 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_pmon_8856.trc:
ORA-00474: SMON process terminated with error
Wed Aug  8 10:57:38 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 8856

这里出现ORA-00600[kafspa:columnBuffer1],一个未知的错误,但是根据相关的提示,可以大概猜出来是什么原因导致数据库异常

Non-fatal internal error happenned while SMON was doing temporary segment drop.

出现这个错误,使得我们想到一个smon的功能,清理临时段.该数据库down掉很可能和smon清理临时段的过程发生失败有关系

SMON encountered 8 out of maximum 100 non-fatal internal errors.

这个错误提示是因为smon内部最多允许发生100次错误,记录错误发生了8次,当然这次数据库down掉是smon还没有达到100次就直接abort掉

SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  
SQL> /
Enter value for param: smon_internal_errlimit
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%smon_internal_errlimit%')

NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ -----------------------------------
_smon_internal_errlimit          100                      limit of SMON internal errors

分析trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/ora10/product
System name:	Linux
Node name:	DBN-HLD-155-2
Release:	2.6.18-92.el5PAE
Version:	#1 SMP Tue Apr 29 13:31:02 EDT 2008
Machine:	i686
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 8941, image: oracle@DBN-HLD-155-2 (SMON)

*** SERVICE NAME:() 2012-08-08 10:55:20.208
*** SESSION ID:(274.1) 2012-08-08 10:55:20.208
*** 2012-08-08 10:55:20.208
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [], []
Current SQL statement for this session:
select file#, block#, ts# from seg$ where type# = 3

果然是smon在查询type#=3的时候发现异常,出现ORA-00600[25027]错误.通过对seg$相关视图分析,可以知道type#=3表示临时段,也就是说数据库smon在查询哪些segment是临时段的时候发生意外,而结合alert日志,完整的错误应该就是:数据库启动后,smon进程为了清理临时段,需要通过select file#, block#, ts# from seg$ where type# = 3查询临时段,但是在查询的时候发生错误.而这个错误累积几次导致数据库异常关闭.

解决问题

--修改两个参数
event='10061 trace name context forever, level 10'
_smon_internal_errlimit=1000000

--启动数据库
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  1267716 bytes
Variable Size             385878012 bytes
Database Buffers         1174405120 bytes
Redo Buffers               15507456 bytes
Database mounted.
Database opened.

因为屏蔽了smon回收临时段,数据库未出现开始时错误,观察数据库几个小时,运行正常,到此基本上解决了此次异常,通过exp可以顺利导出数据然后导入到新库中.

导致该异常sql分析

SQL> select file#, block#, ts# from seg$ where type# = 3;
select file#, block#, ts# from seg$ where type# = 3
                               *
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [],
[]


SQL> set autot trace exp
SQL> select file#, block#, ts# from seg$ where type# = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 1605285479

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   150 |   389   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| SEG$ |    10 |   150 |   389   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TYPE#"=3)

SQL> select count(rowid) from seg$;

Execution Plan
----------------------------------------------------------
Plan hash value: 763549841

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    12 |   389   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |    12 |            |          |
|   2 |   TABLE ACCESS FULL| SEG$ | 45682 |   535K|   389   (1)| 00:00:05 |
---------------------------------------------------------------------------

SQL> select /*+ full(t) */ count(*) from seg$;

Execution Plan
----------------------------------------------------------
Plan hash value: 763549841

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| SEG$ |  3223 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL> select ts# from seg$;

Execution Plan
----------------------------------------------------------
Plan hash value: 1605285479

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3223 |  9669 |    29   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| SEG$ |  3223 |  9669 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> set autot off
SQL> select count(rowid) from seg$;                                                                                 
SQL> /

COUNT(ROWID)
------------
       45727

SQL> select /*+ full(t) */ count(*) from seg$;

  COUNT(*)
----------
     45727

SQL> select  ts# from seg$;
0
0
……
7
7

ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [],
[]

SQL> !dbv file='/oracle/ora10/oradata/ora10g/system01.dbf'

DBVERIFY: Release 10.2.0.4.0 - Production on Thu Aug 9 14:05:09 2012

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

DBVERIFY - Verification starting : FILE = /oracle/ora10/oradata/ora10g/system01.dbf
Block Checking: DBA = 4225673, Block Type = KTB-managed data block
data header at 0xb6d8225c
kdbchk: bad row offset slot 6 offs 3030 fseo 3752 dtl 8168 bhs 72
Page 31369 failed with check code 6135
Block Checking: DBA = 4236289, Block Type = KTB-managed data block
data header at 0xb6d7225c
kdbchk: tosp bad (-13399)
Page 41985 failed with check code 6127


DBVERIFY - Verification complete

Total Pages Examined         : 192000
Total Pages Processed (Data) : 47588
Total Pages Failing   (Data) : 2
Total Pages Processed (Index): 40929
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1784
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 101699
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 1775671440 (0.1775671440)

SQL> ANALYZE TABLE sys.SEG$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.SEG$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01498: block check failure - see trace file

通过这里的分析大概可以确定是由于两块KTB-managed data block数据块异常,导致直接对seg$进行TABLE ACCESS FULL操作的时候发生异常.因为这个库已经破坏了数据一致性,先导出来数据,至于出现该错误的原因,后续继续关注分析

使用bbed解决ORA-00607/ORA-00600[4194]故障

ORA-00607/ORA-00600[4194]错误
数据库启动因为出现ORA-00607/ORA-00600[4194],导致数据库不能正常open

Fri Nov  4 23:10:37 2011
SMON: enabling cache recovery
Fri Nov  4 23:10:37 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=21535
Fri Nov  4 23:10:38 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc:
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Fri Nov  4 23:10:41 2011
Doing block recovery for file 1 block 18
Block recovery from logseq 2, block 48668 to scn 458453
Fri Nov  4 23:10:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log
Block recovery stopped at EOT rba 2.48670.16
Block recovery completed at rba 2.48670.16, scn 0.458451
Doing block recovery for file 1 block 9
Block recovery from logseq 2, block 48668 to scn 458450
Fri Nov  4 23:10:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log
Block recovery completed at rba 2.48670.16, scn 0.458451
Fri Nov  4 23:10:41 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 21529
ORA-1092 signalled during: ALTER DATABASE OPEN...

分析trace文件

*** SESSION ID:(159.3) 2011-11-04 23:10:37.648
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** ktuc_diag_dmp: dump of current change vector
ktudb redo: siz: 252 spc: 7200 flg: 0x0012 seq: 0x0037 rec: 0x06
            xid:  0x0000.022.00000028  
ktubl redo: slt: 34 rci: 0 opc: 11.1 objn: 15 objd: 15 tsn: 0
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00400012.0037.1f 
prev ctl max cmt scn:  0x0000.0006c75b  prev tx cmt scn:  0x0000.0006c75d 
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 4194318  prev bcl: 0 KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
op: L  itl: xid:  0x0000.020.00000029 uba: 0x00400013.0037.05
                      flg: C---    lkc:  0     scn: 0x0000.0006fecb
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040006a  hdba: 0x00400069
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 191
ncol: 17 nnew: 12 size: 0
col  1: [ 9]  5f 53 59 53 53 4d 55 31 24
col  2: [ 2]  c1 02
col  3: [ 2]  c1 03
col  4: [ 2]  c1 0a
col  5: [ 4]  c3 2e 55 0a
col  6: [ 1]  80
col  7: [ 3]  c2 02 59
col  8: [ 3]  c2 02 02
col  9: [ 1]  80
col 10: [ 2]  c1 03
col 11: [ 2]  c1 02
col 16: [ 2]  c1 02

*** 2011-11-04 23:10:38.086
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Current SQL statement for this session:
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,
scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+27          call     ksedst1()            0 ? 1 ?
ksedmp()+557         call     ksedst()             0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksfdmp()+19          call     ksedmp()             3 ? BFFA8C28 ? AC152C0 ?
                                                   CBD2DA0 ? 3 ? BFFA9764 ?
kgeriv()+188         call     00000000             CBD2DA0 ? 3 ?
kseipre()+42         call     kgeriv()             CBD2DA0 ? B6A50020 ? 1062 ?
                                                   2 ? BFFA8C68 ? BFFA8C5C ?
ksesic2()+21         call     kseipre()            1062 ? 2 ? BFFA8C68 ?
                                                   32B36940 ? BFFA8D38 ?
                                                   8C4A3A9 ?
kturdb()+1757        call     ksesic2()            1062 ? 0 ? 23 ? 0 ? 0 ? 6 ?
                                                   0 ?
kco_issue_callback(  call     00000000             B6A09FA4 ? B6A0A01E ? 11 ?
)+176                                              2D306014 ? B6A387C0 ?
kcoapl()+2440        call     kco_issue_callback(  B6A09FA0 ? 2D306000 ?
                              )                    B6A387C0 ?
kcbapl()+322         call     kcoapl()             B6A09FA0 ? 2D306000 ? 1 ? 0 ?
                                                   2000 ? 0 ? B6A387C0 ?
kcrfw_redo_gen()+94  call     kcbapl()             B6A09FA0 ? 2D3F6A1C ?
10                                                 CBE3AE8 ? 0 ? B6A387C0 ?
kcbchg1_main()+8669  call     kcrfw_redo_gen()     3 ? BFFA9358 ? BFFA9370 ?
                                                   CBE3AE8 ? 0 ? BFFA9390 ?
kcbchg1()+63         call     kcbchg1_main()       0 ? 3 ? BFFA97B0 ? BFFA9798 ?
                                                   0 ? 0 ?
ktuchg()+3344        call     kcbchg1()            0 ? 3 ? BFFA97B0 ? BFFA9798 ?
                                                   0 ? 0 ?
ktbchg2()+493        call     ktuchg()             2 ? 2F9EEF8C ? 3 ? B6A0CA98 ?
                                                   B6A0CAA0 ? B6A09FA0 ?
                                                   B6A387C0 ? B6A0C7A0 ? 0 ? 0 ?
kddchg()+1661        call     ktbchg2()            0 ? 2F9EEF8C ? B6A0CA98 ?
                                                   B6A0CAA0 ? B6A09FA0 ?
                                                   B6A387B8 ? B6A0C7A0 ? 0 ? 0 ?
kduovw()+7960        call     kddchg()             B6A3877C ? B6A0CA98 ?
                                                   B6A0CAA0 ? B6A09FA0 ?
                                                   B6A0C7A0 ? 0 ? 0 ? BFFA9C58 ?
kduurp()+2316        call     kduovw()             B6A3877C ? 0 ? 10 ?
                                                   B6A357A4 ? 0 ? B6A3877C ?
kdusru()+4339        call     kduurp()             B6A3877C ? 958412D ?
                                                   CBDC720 ? BFFA9FEC ? B8 ?
                                                   B6A40380 ?
kauupd()+366         call     kdusru()             B6A357A4 ? 2F9EEFF8 ?
                                                   B6A3877C ? 0 ?
updrow()+5889        call     kauupd()             B6A357A0 ? 2F9EEFF8 ?
                                                   B6A3877C ? 0 ? 2FA479FC ? E ?
                                                   F ? 2F9EF31C ? 12 ?
                                                   BFFB0544 ? BFFB04E4 ?
qerupRowProcedure()  call     updrow()             2F9E5B64 ? 7FFF ? DB4 ? 48 ?
+62                                                2F9EFBF4 ? BFFB08B4 ?
qerupFetch()+1187    call     00000000             2F9EF4B0 ? 7FFF ?
updaul()+3474        call     00000000             2F9EF4B0 ? 0 ? 2F9EF370 ?
                                                   7FFF ?
updThreePhaseExe()+  call     updaul()             2F9E5B64 ? BFFB0D2C ? 0 ?
3470
updexe()+813         call     updThreePhaseExe()   2F9E5B64 ? 0 ? B6A3877C ?
                                                   BFFB0E00 ? 2F9E5B64 ? 1 ?
                                                   BFFB0E00 ? 0 ?
opiexe()+17967       call     updexe()             2F9E5B64 ? BFFB1074 ?
opiodr()+2347        call     00000000             4 ? 4 ? BFFB25A8 ?
rpidrus()+434        call     opiodr()             4 ? 4 ? BFFB25A8 ? 2 ?
skgmstack()+210      call     00000000             BFFB2004 ? 97492FE ?
                                                   CBD2E9C ? BFFB1FE8 ?
                                                   BFFB24EC ? BFFB2004 ?
rpidru()+98          call     skgmstack()          BFFB1FE8 ? CBD2B60 ? F618 ?
                                                   9749546 ? BFFB2004 ?
rpiswu2()+1061       call     00000000             BFFB24EC ? BFFB25E8 ?
                                                   BFFB2500 ? 2 ? BFFB24B0 ?
                                                   5953 ?
rpidrv()+1915        call     rpiswu2()            32F0A1D4 ? 0 ? BFFB24B0 ? 2 ?
                                                   BFFB2528 ? 0 ? BFFB24B0 ? 0 ?
                                                   9749800 ? 97498DC ?
                                                   BFFB24EC ? 8 ?
rpiexe()+65          call     rpidrv()             2 ? 4 ? BFFB25A8 ? 8 ?
ktuscu()+697         call     rpiexe()             2 ? 1C ? 2A ? 32FF3404 ? 0 ?
                                                   BFFB2710 ?
kqrcmt()+945         call     00000000             32AFA70C ? 3 ?
ktcrcm()+945         call     kqrcmt()             31A2B84C ? 1 ? 0 ?
ktuswr()+1855        call     ktcrcm()             31A2B84C ? 0 ? 0 ? 0 ? 0 ?
                                                   1 ? 0 ? 0 ?
ktusmous_online_und  call     ktuswr()             1 ? 0 ? 0 ? 0 ? 0 ? 0 ?
oseg()+951
ktusmout_online_ut(  call     ktusmous_online_und  1 ? A ? 0 ? 3 ?
)+737                         oseg()
ktusmiut_init_ut()+  call     ktusmout_online_ut(  1 ? 0 ? 0 ?
1084                          )
ktuini()+688         call     ktusmiut_init_ut()   0 ? BFFB4744 ? CBD2E9C ?
                                                   CBD2E9C ? CBD2DA0 ? 7 ?
adbdrv()+5699        call     ktuini()             0 ? 0 ? 0 ? 0 ? 64000000 ?
                                                   3 ?
opiexe()+18301       call     adbdrv()             59D4 ? 0 ? 9EE16E2F ? 494C4 ?
                                                   32B33CD0 ? 0 ?
opiosq0()+3918       call     opiexe()             4 ? 0 ? BFFB8988 ?
kpooprx()+250        call     opiosq0()            3 ? E ? BFFB8B90 ? A4 ?
kpoal8()+867         call     kpooprx()            BFFBAD68 ? BFFB990C ? 13 ?
                                                   1 ? 0 ? A4 ?
opiodr()+2347        call     00000000             5E ? 17 ? BFFBAD64 ?
ttcpip()+4227        call     00000000             5E ? 17 ? BFFBAD64 ? 0 ?
                                                   DABCA66 ? 93 ?
opitsk()+1991        call     ttcpip()             CBDA5A0 ? 5E ? BFFBAD64 ? 0 ?
                                                   BFFBA244 ? BFFBAE88 ?
opiino()+1387        call     opitsk()             0 ? 0 ?
opiodr()+2347        call     00000000             3C ? 4 ? BFFBB950 ?
opidrv()+915         call     opiodr()             3C ? 4 ? BFFBB950 ? 0 ?
sou2o()+113          call     opidrv()             3C ? 4 ? BFFBB950 ?
opimai_real()+212    call     sou2o()              BFFBB934 ? 3C ? 4 ?
                                                   BFFBB950 ?
main()+111           call     opimai_real()        2 ? BFFBB980 ?
__libc_start_main()  call     00000000             2 ? BFFBBA44 ? BFFBBA50 ?
+220                                               47D9A828 ? 0 ? 1 ?

--------------------- Binary Stack Dump ---------------------

数据库在open的时候,需要去修改undo$对象的状态,从2该为3(offline->online)这个时候需要使用到系统回滚段,但是在使用系统回滚段的时候,使用uba=0x00400012的时候发生异常,导致数据库不能正常open,从而出现了ORA-00600[4194]的错误.而出现这个故障的原因,很可能是由于file 1 block 18块的异常导致.我们需要做的,就是让数据库启动的时候不使用file 1 block 18的block,而让数据库去另外的分配一个undo块.

bbed清除rollback分配块信息

[oracle@xifenfei ~]$ bbed listfile=list mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Sat Nov 5 01:11:49 2011

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

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

BBED> set file 1 block 9
        FILE#           1
        BLOCK#          9

BBED> map
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 9                                     Dba:0x00400009
------------------------------------------------------------
 Unlimited Undo Segment Header

 struct kcbh, 20 bytes                      @0       

 struct ktech, 72 bytes                     @20      

 struct ktemh, 16 bytes                     @92      

 struct ktetb[6], 48 bytes                  @108     

 struct ktuxc, 104 bytes                    @4148    

 struct ktuxe[255], 10200 bytes             @4252    

 ub4 tailchk                                @8188    


BBED> p ktuxc
struct ktuxc, 104 bytes                     @4148    
   struct ktuxcscn, 8 bytes                 @4148    
      ub4 kscnbas                           @4148     0x0006c75b
      ub2 kscnwrp                           @4152     0x0000
   struct ktuxcuba, 8 bytes                 @4156    
      ub4 kubadba                           @4156     0x00400012
      ub2 kubaseq                           @4160     0x0037
      ub1 kubarec                           @4162     0x1f
   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)
   ub2 ktuxcseq                             @4166     0x0037
   sb2 ktuxcnfb                             @4168     1         
   ub4 ktuxcinc                             @4172     0x00000000
   sb2 ktuxcchd                             @4176     34
   sb2 ktuxcctl                             @4178     32
   ub2 ktuxcmgc                             @4180     0x8002
   ub4 ktuxcopt                             @4188     0x7ffffffe
   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x00400012
         ub2 kubaseq                        @4196     0x0037
         ub1 kubarec                        @4198     0x05
      sb2 ktufbext                          @4200     1
      sb2 ktufbspc                          @4202     7200
   struct ktuxcfbp[1], 12 bytes             @4204    
      struct ktufbuba, 8 bytes              @4204    
         ub4 kubadba                        @4204     0x00000000
         ub2 kubaseq                        @4208     0x0035
         ub1 kubarec                        @4210     0x2a
      sb2 ktufbext                          @4212     5
      sb2 ktufbspc                          @4214     3446
   struct ktuxcfbp[2], 12 bytes             @4216    
      struct ktufbuba, 8 bytes              @4216    
         ub4 kubadba                        @4216     0x00000000
         ub2 kubaseq                        @4220     0x0035
         ub1 kubarec                        @4222     0x37
      sb2 ktufbext                          @4224     5
      sb2 ktufbspc                          @4226     1336
   struct ktuxcfbp[3], 12 bytes             @4228    
      struct ktufbuba, 8 bytes              @4228    
         ub4 kubadba                        @4228     0x00000000
         ub2 kubaseq                        @4232     0x0000
         ub1 kubarec                        @4234     0x00
      sb2 ktufbext                          @4236     0
      sb2 ktufbspc                          @4238     0
   struct ktuxcfbp[4], 12 bytes             @4240    
      struct ktufbuba, 8 bytes              @4240    
         ub4 kubadba                        @4240     0x00000000
         ub2 kubaseq                        @4244     0x0000
         ub1 kubarec                        @4246     0x00
      sb2 ktufbext                          @4248     0
      sb2 ktufbspc                          @4250     0

BBED> set count 16
        COUNT           16

########################################################
使用bbed修改相关参数
########################################################

启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size              96470440 bytes
Database Buffers          213909504 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

通过bbed模拟ORA-00607/ORA-00600 4194 故障

在数据库恢复的案例中,遇到system rollback异常的故障算是中彩票了.处理起来比较麻烦,有些情况甚至是无法处理.这里通过试验模拟ORA-00607/ORA-00600[4194].类此的错误在一次银联的数据库恢复中也遇到过,不过当时由于功底不深,理解出现部分误差.
通过bbed模拟ORA-00607/ORA-00600[4194]错误

[oracle@xifenfei ~]$ bbed listfile=list mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Fri Nov 4 22:59:51 2011

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

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

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/XFF/system01.dbf                                 0
     2  /u01/oracle/oradata/XFF/undotbs01.dbf                                0
     3  /u01/oracle/oradata/XFF/sysaux01.dbf                                 0
     4  /u01/oracle/oradata/XFF/users01.dbf                                  0
     5  /u01/oracle/oradata/XFF/datfttuser.dbf                               0

BBED> set block 9
        BLOCK#          9

BBED> map
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 9                                     Dba:0x00400009
------------------------------------------------------------
 Unlimited Undo Segment Header

 struct kcbh, 20 bytes                      @0       

 struct ktech, 72 bytes                     @20      

 struct ktemh, 16 bytes                     @92      

 struct ktetb[6], 48 bytes                  @108     

 struct ktuxc, 104 bytes                    @4148    

 struct ktuxe[255], 10200 bytes             @4252    

 ub4 tailchk                                @8188    


BBED> p ktuxc
struct ktuxc, 104 bytes                     @4148    
   struct ktuxcscn, 8 bytes                 @4148    
      ub4 kscnbas                           @4148     0x0006c75b
      ub2 kscnwrp                           @4152     0x0000
   struct ktuxcuba, 8 bytes                 @4156    
      ub4 kubadba                           @4156     0x00400012
      ub2 kubaseq                           @4160     0x0037
      ub1 kubarec                           @4162     0x1f
   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)
   ub2 ktuxcseq                             @4166     0x0037
   sb2 ktuxcnfb                             @4168     1            <==free undo block num
   ub4 ktuxcinc                             @4172     0x00000000
   sb2 ktuxcchd                             @4176     34
   sb2 ktuxcctl                             @4178     32
   ub2 ktuxcmgc                             @4180     0x8002
   ub4 ktuxcopt                             @4188     0x7ffffffe
   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x00400013    <==uba (模拟试验修改为其他uba地址)
         ub2 kubaseq                        @4196     0x0037        <==uba sequence
         ub1 kubarec                        @4198     0x05
      sb2 ktufbext                          @4200     1
      sb2 ktufbspc                          @4202     7200
   struct ktuxcfbp[1], 12 bytes             @4204    
      struct ktufbuba, 8 bytes              @4204    
         ub4 kubadba                        @4204     0x00000000
         ub2 kubaseq                        @4208     0x0035
         ub1 kubarec                        @4210     0x2a
      sb2 ktufbext                          @4212     5
      sb2 ktufbspc                          @4214     3446
   struct ktuxcfbp[2], 12 bytes             @4216    
      struct ktufbuba, 8 bytes              @4216    
         ub4 kubadba                        @4216     0x00000000
         ub2 kubaseq                        @4220     0x0035
         ub1 kubarec                        @4222     0x37
      sb2 ktufbext                          @4224     5
      sb2 ktufbspc                          @4226     1336
   struct ktuxcfbp[3], 12 bytes             @4228    
      struct ktufbuba, 8 bytes              @4228    
         ub4 kubadba                        @4228     0x00000000
         ub2 kubaseq                        @4232     0x0000
         ub1 kubarec                        @4234     0x00
      sb2 ktufbext                          @4236     0
      sb2 ktufbspc                          @4238     0
   struct ktuxcfbp[4], 12 bytes             @4240    
      struct ktufbuba, 8 bytes              @4240    
         ub4 kubadba                        @4240     0x00000000
         ub2 kubaseq                        @4244     0x0000
         ub1 kubarec                        @4246     0x00
      sb2 ktufbext                          @4248     0
      sb2 ktufbspc                          @4250     0

BBED> set dba 0x00400013
        DBA             0x00400013 (4194323 1,19)

BBED> p ktubh
struct ktubh, 26 bytes                      @20      
   struct ktubhxid, 8 bytes                 @20      
      ub2 kxidusn                           @20       0x0000
      ub2 kxidslt                           @22       0x0020
      ub4 kxidsqn                           @24       0x00000029
   ub2 ktubhseq                             @28       0x0037    <==uba seq
   ub1 ktubhcnt                             @30       0x05
   ub1 ktubhirb                             @31       0x05
   ub1 ktubhicl                             @32       0x00
   ub1 ktubhflg                             @33       0x00
   ub2 ktubhidx[0]                          @34       0x1fe8
   ub2 ktubhidx[1]                          @36       0x1f2c
   ub2 ktubhidx[2]                          @38       0x1e70
   ub2 ktubhidx[3]                          @40       0x1db4
   ub2 ktubhidx[4]                          @42       0x1cf8
   ub2 ktubhidx[5]                          @44       0x1c3c

BBED> set dba 0x00400012
        DBA             0x00400012 (4194322 1,18)

BBED> p ktubh
struct ktubh, 86 bytes                      @20      
   struct ktubhxid, 8 bytes                 @20      
      ub2 kxidusn                           @20       0x0000
      ub2 kxidslt                           @22       0x0020
      ub4 kxidsqn                           @24       0x00000029
   ub2 ktubhseq                             @28       0x0037
   ub1 ktubhcnt                             @30       0x23
   ub1 ktubhirb                             @31       0x23
   ub1 ktubhicl                             @32       0x00
   ub1 ktubhflg                             @33       0x00
   ub2 ktubhidx[0]                          @34       0x1fe8
   …………
   ub2 ktubhidx[35]                         @104      0x00b4

BBED> set block 9
        BLOCK#          9

BBED> set count 16
        COUNT           16

BBED> m /x 12004000 offset 4192
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 9                Offsets: 4192 to 4207           Dba:0x00400009
------------------------------------------------------------------------
 12004000 37000500 0100201c 00000000 

 <32 bytes per line>

BBED>  p ktuxc
struct ktuxc, 104 bytes                     @4148    
   struct ktuxcscn, 8 bytes                 @4148    
      ub4 kscnbas                           @4148     0x0006c75b
      ub2 kscnwrp                           @4152     0x0000
   struct ktuxcuba, 8 bytes                 @4156    
      ub4 kubadba                           @4156     0x00400012
      ub2 kubaseq                           @4160     0x0037
      ub1 kubarec                           @4162     0x1f
   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)
   ub2 ktuxcseq                             @4166     0x0037
   sb2 ktuxcnfb                             @4168     1
   ub4 ktuxcinc                             @4172     0x00000000
   sb2 ktuxcchd                             @4176     34
   sb2 ktuxcctl                             @4178     32
   ub2 ktuxcmgc                             @4180     0x8002
   ub4 ktuxcopt                             @4188     0x7ffffffe
   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x00400012  <==uba已经被修改
         ub2 kubaseq                        @4196     0x0037
         ub1 kubarec                        @4198     0x05
      sb2 ktufbext                          @4200     1
      sb2 ktufbspc                          @4202     7200
   struct ktuxcfbp[1], 12 bytes             @4204    
      struct ktufbuba, 8 bytes              @4204    
         ub4 kubadba                        @4204     0x00000000
         ub2 kubaseq                        @4208     0x0035
         ub1 kubarec                        @4210     0x2a
      sb2 ktufbext                          @4212     5
      sb2 ktufbspc                          @4214     3446
   struct ktuxcfbp[2], 12 bytes             @4216    
      struct ktufbuba, 8 bytes              @4216    
         ub4 kubadba                        @4216     0x00000000
         ub2 kubaseq                        @4220     0x0035
         ub1 kubarec                        @4222     0x37
      sb2 ktufbext                          @4224     5
      sb2 ktufbspc                          @4226     1336
   struct ktuxcfbp[3], 12 bytes             @4228    
      struct ktufbuba, 8 bytes              @4228    
         ub4 kubadba                        @4228     0x00000000
         ub2 kubaseq                        @4232     0x0000
         ub1 kubarec                        @4234     0x00
      sb2 ktufbext                          @4236     0
      sb2 ktufbspc                          @4238     0
   struct ktuxcfbp[4], 12 bytes             @4240    
      struct ktufbuba, 8 bytes              @4240    
         ub4 kubadba                        @4240     0x00000000
         ub2 kubaseq                        @4244     0x0000
         ub1 kubarec                        @4246     0x00
      sb2 ktufbext                          @4248     0
      sb2 ktufbspc                          @4250     0

BBED> sum apply
Check value for File 1, Block 9:
current = 0xe686, required = 0xe686

启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size              96470440 bytes
Database Buffers          213909504 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

alert日志

Fri Nov  4 23:10:37 2011
SMON: enabling cache recovery
Fri Nov  4 23:10:37 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=21535
Fri Nov  4 23:10:38 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc:
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Fri Nov  4 23:10:41 2011
Doing block recovery for file 1 block 18
Block recovery from logseq 2, block 48668 to scn 458453
Fri Nov  4 23:10:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log
Block recovery stopped at EOT rba 2.48670.16
Block recovery completed at rba 2.48670.16, scn 0.458451
Doing block recovery for file 1 block 9
Block recovery from logseq 2, block 48668 to scn 458450
Fri Nov  4 23:10:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log
Block recovery completed at rba 2.48670.16, scn 0.458451
Fri Nov  4 23:10:41 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 21529
ORA-1092 signalled during: ALTER DATABASE OPEN...

创建控制文件遭遇ORA-00600[3753]故障解决

一位网友的数据库正常关闭,然后控制文件意外丢失,需要通过trace中的信息重建控制文件,但是在重建的过程中,出现ORA-00600[3753]错误,远程帮忙处理,记录处理过程如下
1.启动数据库至nomount状态,然后尝试noresetlogs模式重建控制文件

SQL>@XFF_NORESETLOGS_CTL.sql
CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE 失败
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []

2.检查alert日志

Tue Aug 07 20:40:47 2012
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Tue Aug 07 20:40:48 2012
Errors in file d:\oracle\product\10.2.0\db_1\admin\ora10g\udump\ora10g_ora_11596.trc:
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []

Tue Aug 07 20:40:53 2012
Errors in file d:\oracle\product\10.2.0\db_1\admin\ora10g\udump\ora10g_ora_11596.trc:
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []

ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG

3.分析trace文件

Tue Aug 07 20:40:48 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 V6.1 Service Pack 1
CPU                 : 2 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:166M/1901M, Ph+PgF:619M/5536M, VA:812M/2047M
Instance name: ora10g

Redo thread mounted by this instance: 0 <none>

Oracle process number: 16

Windows thread id: 11596, image: ORACLE.EXE (SHAD)


*** SERVICE NAME:() 2012-08-07 20:40:48.413
*** SESSION ID:(158.7) 2012-08-07 20:40:48.413
*** 2012-08-07 20:40:48.413
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []
Current SQL statement for this session:
CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG
…………
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedst+38           CALLrel  _ksedst1+0           0 1
_ksedmp+898          CALLrel  _ksedst+0            0
_ksfdmp+14           CALLrel  _ksedmp+0            3
603A816A             CALLreg  00000000             87CF110 3
603A83FF             CALLrel  603A80D8             87CF110 8191090 EA9 2 8CCC438
_ksesic2+59          CALLrel  _kgesiv+0            87CF110 8191090 EA9 2 8CCC438
                                                   EA9 2 8CCC438
__VInfreq__kctbce+1  CALLrel  _ksesic2+0           EA9 0 3 0 0 2 0
63                                                 
_kcfccfl+356         CALLrel  _kctbce+0            543414C 81DB8A8
_cdbdrv+1037         CALLrel  _kcfccfl+0           543414C 1 8CCD060 8CCD04C
                                                   19000 3
_opiexe+11999        CALLrel  _cdbdrv+0            1
_opiosq0+6088        CALLrel  _opiexe+0            4 0 8CCD894
_kpooprx+232         CALLrel  _opiosq0+0           3 E 8CCD9AC A4
_kpoal8+775          CALLrel  _kpooprx+0           8CCF6CC 8196414 A16 1 0 A4
_opiodr+1099         CALLreg  00000000             5E 17 8CCF6C8
60FEFF8D             CALLreg  00000000             5E 17 8CCF6C8 0
_opitsk+1017         CALL???  00000000             
_opiino+1087         CALLrel  _opitsk+0            0 0
_opiodr+1099         CALLreg  00000000             3C 4 8CCFC60
_opidrv+819          CALLrel  _opiodr+0            3C 4 8CCFC60 0
_sou2o+45            CALLrel  _opidrv+0            3C 4 8CCFC60
_opimai_real+112     CALLrel  _sou2o+0             8CCFC54 3C 4 8CCFC60
_opimai+92           CALLrel  _opimai_real+0       2 8CCFC8C
_OracleThreadStart@  CALLrel  _opimai+0            
4+708                                              
__pRawDllMain+10931  CALLptr  00000000             
2903                                               
__pRawDllMain+12925  CALLreg  00000000             
4809                                               
__pRawDllMain+12925  CALLrel  __pRawDllMain+12925  
4761                          4772                 
 
--------------------- Binary Stack Dump ---------------------

    ----------------------------------------
    SO: 4FB3DF5C, type: 4, owner: 4FA4CBFC, flag: INIT/-/-/0x00
    (session) sid: 158 trans: 4EBB8954, creator: 4FA4CBFC, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0000-0010-0000000A, short-term DID: 0000-0000-00000000
              txn branch: 00000000
              oct: 0, prv: 0, sql: 00000000, psql: 4F707298, user: 0/SYS
    O/S info: user: superv06-PC\superv06, term: SUPERV06-PC, ospid: 7788:11636, machine: WORKGROUP\SUPERV06-PC
              program: sqlplus.exe
    application name: sqlplus.exe, hash value=0
    last wait for 'log file sequential read' blocking sess=0x00000000 seq=31 
    wait_time=159 seconds since wait started=0
                log#=0, block#=1, blocks=1
    Dumping Session Wait History
     for 'log file sequential read' count=1 wait_time=159
                log#=0, block#=1, blocks=1
     for 'log file sequential read' count=1 wait_time=502
                log#=0, block#=1, blocks=1
     for 'log file sequential read' count=1 wait_time=163
                log#=0, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=18840
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=254
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=7654
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=150
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=102
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=123
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=14010
                file#=ffffffff, block#=1, blocks=1

通过这里我们发现创建控制文件的进程在读取redo log的时候出现了等待比较多而且时间比较长,而对于ORA-00600[3753]错误互联网上没有任何更多的信息.通过对于创建控制文件时候因为使用noresetlogs的分析:这种模式下需要读取redo log,所以导致等待较多,从而出现ORA-00600[3753]错误使得创建控制文件失败.因为本库是shutdown immediate关闭,所以我们完全可以通过resetlogs模式来创建控制文件,从而避免读取redo log.

4.创建resetlogs控制文件

SQL>@XFF_RESETLOGS_CTL.sql

Control file created.

5.然后不完全恢复使用resetlogs open数据库

这次的处理我也没有什么经验可以借鉴,MOS和互联网上没有该错误的任何信息,解决这个问题关键凭的是自己对于noresetlogs和resetlogs的理解.对于数据库原理的理解,对解决一些陌生问题帮助很大;在学习ORACLE过程中注重对原理的理解和消化