ORA-00704 – Database SOS

2019恢复第一单—ORA-704-ORA-702恢复

标题：2019恢复第一单—ORA-704-ORA-702恢复

19年元旦刚过就有客户反馈数据库无法正常启动报ORA-00704和ORA-00702错误

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3756.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3756.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Error 704 happened during db open, shutting down database
USER (ospid: 3756): terminating the instance due to error 704
Instance terminated by USER, pid = 3756

通过分析确认，确认由于数据库遭受到而已脚本注入，破坏数据库基表数据，导致数据库重启之后就无法正常启动，

这类故障可以通过对oracle基表进行恢复，实现数据库完美open，数据0丢失.如果无法自行解决,需要恢复支持请联系我们
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
2019年再次提醒：1)数据库做好备份容灾，2)请使用官方途径下载数据库介质和数据库访问工具，3)定期检查数据库是否被注入恶意脚本

ORA-01092 ORA-00704 ORA-00942

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01092 ORA-00704 ORA-00942

有一朋友数据库启动出现报ORA-01092 ORA-00704 ORA-00942错误

SQL> startup
ORACLE instance started.
Total System Global Area 3056513024 bytes
Fixed Size                  2257152 bytes
Variable Size             704646912 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17575936 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist
Process ID: 31766
Session ID: 191 Serial number: 3

数据库alert日志报错

Sat Feb 22 03:19:04 2014
ARC1 started with pid=22, OS id=31770
Sat Feb 22 03:19:04 2014
ARC2 started with pid=23, OS id=31772
Thread 1 opened at log sequence 38
  Current log# 2 seq# 38 mem# 0: /u01/app/oracle/oradata/xifenfei/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Sat Feb 22 03:19:04 2014
ARC3 started with pid=24, OS id=31774
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_ora_31766.trc:
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_ora_31766.trc:
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist
Error 704 happened during db open, shutting down database
USER (ospid: 31766): terminating the instance due to error 704
Instance terminated by USER, pid = 31766
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (31766) as a result of ORA-1092
Sat Feb 22 03:19:05 2014
ORA-1092 : opitsk aborting process

印象比较深的在某些版本中数据库由于access$丢失会出现类似错误:Oracle 11g丢失access$恢复方法，对数据库进行跟踪分析发现

PARSE ERROR #140521486058480:len=208 dep=1 uid=0 oct=9 lid=0 tim=1393010401966006 err=942
CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 64K NEXT 1024K
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist

创建基表的I_OBJ1失败,这种情况比较少见,难道是obj$表丢失了，或者损坏了？搜索trace文件,发现没有obj$表创建成功

[root@xifenfei trace]# grep -i "CREATE TABLE" xifenfei_ora_31822.trc
create table bootstrap$ (
CREATE TABLE TAB$("OBJ#"
CREATE TABLE CLU$("OBJ#"
CREATE TABLE FET$("TS#" N
CREATE TABLE UET$("SEGFIL
CREATE TABLE SEG$("FILE#"
CREATE TABLE UNDO$("US#"
CREATE TABLE TS$("TS#" NU
CREATE TABLE FILE$("FILE#
CREATE TABLE IND$("OBJ#"
CREATE TABLE ICOL$("OBJ#"
CREATE TABLE COL$("OBJ#"
CREATE TABLE USER$("USER#
CREATE TABLE PROXY_DATA$(
CREATE TABLE PROXY_ROLE_D
CREATE TABLE CON$("OWNER#
CREATE TABLE CDEF$("CON#"
CREATE TABLE CCOL$("CON#"

应该是obj$表没有被创建成功,通过dbv进一步分析

[oracle@xifenfei ~]$ dbv file=/u01/app/oracle/oradata/xifenfei/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Sat Feb 22 05:59:41 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf
Block Checking: DBA = 4194827, Block Type = KTB-managed data block
data header at 0x7f3100234244
kdbchk: the amount of space used is not equal to block size
        used=4595 fsc=646 avsp=3525 dtl=8120
Block 523 failed with check code 6110
DBVERIFY - Verification complete
Total Pages Examined         : 97280
Total Pages Processed (Data) : 64694
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13128
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3569
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 15889
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1836897 (0.1836897)

由于block损坏导致obj$表创建异常,从而使得出现此类问题，通过bbed修复坏块之后

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf
BLOCK = 523
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

然后启动数据库,正常启动成功

SQL> startup
ORACLE instance started.
Total System Global Area 3056513024 bytes
Fixed Size                  2257152 bytes
Variable Size             704646912 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17575936 bytes
Database mounted.
Database opened.

ORA-01092 ORA-00704 ORA-00942错误比较特殊很少见,如果您遇到了类似的,无法自行解决的,请联系我们
Tel:17813235971(同微信) Q Q:107644445 E-Mail:dba@xifenfei.com

恶意删除bootstrap$导致数据库无法正常启动

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：恶意删除bootstrap$导致数据库无法正常启动

有客户10.2.0.5的数据库关闭之后,无法正常启动报ORA-00704 ORA-00702错误.

Fri May 18 22:42:26  2018
ALTER DATABASE OPEN
Fri May 18 22:42:27  2018
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Fri May 18 22:42:27  2018
Started redo scan
Fri May 18 22:42:27  2018
Completed redo scan
 1 redo blocks read, 0 data blocks need recovery
Fri May 18 22:42:27  2018
Started redo application at
 Thread 1: logseq 2, block 2, scn 8448162573
Fri May 18 22:42:27  2018
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\DATABASE\xifenfei\REDO02.LOG
Fri May 18 22:42:27  2018
Completed redo application
Fri May 18 22:42:27  2018
Completed crash recovery at
 Thread 1: logseq 2, block 3, scn 8448182575
 0 data blocks read, 0 data blocks written, 1 redo blocks read
Fri May 18 22:42:28  2018
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=23, OS id=3188
ARC1 started with pid=24, OS id=3168
ARC2 started with pid=25, OS id=996
ARC3 started with pid=26, OS id=432
ARC4 started with pid=27, OS id=3728
Fri May 18 22:42:28  2018
ARC0: Archival started
ARC1: Archival started
ARC5 started with pid=28, OS id=2876
Fri May 18 22:42:28  2018
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Fri May 18 22:42:28  2018
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: D:\DATABASE\xifenfei\REDO03.LOG
Successful open of redo thread 1
Fri May 18 22:42:28  2018
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 18 22:42:28  2018
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri May 18 22:42:28  2018
ARC2: Becoming the heartbeat ARCH
Fri May 18 22:42:28  2018
SMON: enabling cache recovery
Fri May 18 22:42:28  2018
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\udump\xifenfei_ora_3148.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Fri May 18 22:42:28  2018
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 3148
ORA-1092 signalled during: ALTER DATABASE OPEN...

根据以前恢复经验ORA-00702: bootstrap verison ” inconsistent with version ’8.0.0.0.0′,很可能是由于bootstrap$表异常了.
通过dbv检查system文件确认没有坏块

通过bbed分析,确认记录被删除
把数据文件拷贝到本地,通过bbed进行分析,确认记录丢失

BBED> map
 File: d:/system01.dbf (0)
 Block: 379                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 48 bytes                     @20
 struct kdbh, 14 bytes                      @68
 struct kdbt[1], 4 bytes                    @82
 sb2 kdbr[24]                               @86
 ub1 freespace[1158]                        @134
 ub1 rowdata[6896]                          @1292
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[6875]
-------------
ub1 rowdata[6875]                           @8167     0x3c
BBED> x /rnnc
rowdata[6875]                               @8167
-------------
flag@8167: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8168: 0x01
cols@8169:    0

故障原因跟踪
有人在数据库中注入了恶意脚本，导致数据库删除了bootstrap$中数据,关闭之后无法正常启动

处理方法
通过oracle bbed 修复数据字典,正常启动数据库

ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'

数据库启动报ORA-01092 ORA-00704 ORA-00702错误

SQL> startup
ORACLE instance started.
Total System Global Area 3056513024 bytes
Fixed Size                  2257152 bytes
Variable Size             704646912 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17575936 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'
Process ID: 27344
Session ID: 191 Serial number: 3

查看alert日志

Mon Apr 09 16:22:34 2018
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1383493834
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Apr 09 16:22:39 2018
ALTER DATABASE OPEN
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27344.trc:
ORA-00704: bootstrap process failure
ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27344.trc:
ORA-00704: bootstrap process failure
ORA-00702: bootstrap verison '' inconsistent with version '8.0.0.0.0'
Error 704 happened during db open, shutting down database
USER (ospid: 27344): terminating the instance due to error 704
Instance terminated by USER, pid = 27344
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (27344) as a result of ORA-1092
Mon Apr 09 16:22:40 2018
ORA-1092 : opitsk aborting process

错误比较明显是由于数据库open过程中核心基表异常导致,出现此类错误一般是由于软件介质和db不匹配或者字典表的block故障导致.
官方说明

Versions 9.2, 10.1, 10.2, 11.1, 11.2, 12.1
Error:  ORA-00702 bootstrap verison '%s' inconsistent with version '%s'
---------------------------------------------------------------------------
Cause:  The reading version of the boostrap is incompatible with the current
	bootstrap version.
Action: Restore a version of the software that is compatible with this
	bootstrap version

由于核心基表等相关表处理比较特殊,如果您遭遇此类相关异常无法解决,需要恢复支持,请联系我们
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

ORA-00704 ORA-00604 ORA-01406故障分析

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00704 ORA-00604 ORA-01406故障分析

根据客户反馈系统运行的是9.2.0.8版本,但是服务器上面安装有10.2.0.1服务端,由于使用过10.2启动过数据库导致现在9.2.0.8无法启动.
数据库启动报错

Starting up ORACLE RDBMS Version: 9.2.0.8.0.
System parameters with non-default values:
…………
  db_cache_size            = 209715200
  compatible               = 9.2.0.0.0
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
…………
Thread 1 advanced to log sequence 7517
Thread 1 opened at log sequence 7517
  Current log# 1 seq# 7517 mem# 0: F:\ORACLE\ORADATA\ORACLE\REDO01.LOG
Successful open of redo thread 1
Tue Apr 25 21:16:20 2017
SMON: enabling cache recovery
Tue Apr 25 21:16:20 2017
Errors in file f:\oracle\admin\oracle\udump\oracle_ora_3908.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01406: 读取的列值被截断
Tue Apr 25 21:16:20 2017
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Tue Apr 25 21:16:20 2017
Errors in file f:\oracle\admin\oracle\bdump\oracle_pmon_2124.trc:
ORA-00704: bootstrap process failure
Tue Apr 25 21:16:20 2017
Errors in file f:\oracle\admin\oracle\bdump\oracle_reco_2556.trc:
ORA-00704: bootstrap process failure
Tue Apr 25 21:16:20 2017
Errors in file f:\oracle\admin\oracle\bdump\oracle_smon_628.trc:
ORA-00704: bootstrap process failure
Tue Apr 25 21:16:21 2017
Errors in file f:\oracle\admin\oracle\bdump\oracle_ckpt_2212.trc:
ORA-00704: bootstrap process failure
Tue Apr 25 21:16:21 2017
Errors in file f:\oracle\admin\oracle\bdump\oracle_lgwr_2756.trc:
ORA-00704: bootstrap process failure
Tue Apr 25 21:16:21 2017
Errors in file f:\oracle\admin\oracle\bdump\oracle_dbw0_1756.trc:
ORA-00704: bootstrap process failure
Instance terminated by USER, pid = 3908
ORA-1092 signalled during: ALTER DATABASE OPEN...

错误比较明显bootstrap$的相关sql在递归的时候报错(ORA-01406)

我们分析alert日志

---9.2.0.1版本运行了很长时间
Mon Apr 13 20:44:29 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
--然后升级到9.2.0.8
Thu Jun 18 17:32:09 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.8.0.
System parameters with non-default values:
…………
Thu Jun 18 17:32:18 2015
SMON: enabling cache recovery
Thu Jun 18 17:32:19 2015
Successfully onlined Undo Tablespace 1.
Thu Jun 18 17:32:19 2015
SMON: enabling tx recovery
Thu Jun 18 17:32:19 2015
Database Characterset is ZHS16GBK
Updating 9.2.0.1.0 NLS parameters in sys.props$
-- adding 9.2.0.8.0 NLS parameters.
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
…………
Thu Jun 18 17:38:32 2015
Database Characterset is ZHS16GBK
Thu Jun 18 17:38:33 2015
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Thu Jun 18 17:38:33 2015
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;
Thu Jun 18 17:38:33 2015
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE
--再升级到10.2.0.1
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
  timed_statistics         = TRUE
  sga_max_size             = 1610612736
  shared_pool_size         = 209715200
  large_pool_size          = 8388608
  java_pool_size           = 159383552
  streams_pool_size        = 50331648
  control_files            = F:\ORACLE\ORADATA\ORACLE\CONTROL01.CTL
  db_block_size            = 8192
  db_cache_size            = 209715200
  compatible               = 9.2.0.0.0
…………
Thu Jun 18 19:43:30 2015
Database Characterset is ZHS16GBK
Updating 9.2.0.8.0 NLS parameters in sys.props$
-- adding 10.2.0.1.0 NLS parameters.
…………
Thu Jun 18 19:43:44 2015
ALTER SYSTEM enable restricted session;
MMNL started with pid=12, OS id=5212
Thu Jun 18 19:43:44 2015
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Thu Jun 18 19:43:44 2015
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Thu Jun 18 19:43:44 2015
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
Thu Jun 18 19:43:44 2015
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE

这里很明显数据库从2009年4月开始9.2.0.1版本开始运行,然后到2015年6月18日升级到9.2.0.8版本,紧接着升级到10.2.0.1(升级8.2.0.8是为升级10.2.0.1的中间过度操作).然后这个库一直使用10.2.0.1版本运行,这次重启不知道什么原因客户以为是9.2.0.8的数据库版本,然后不管怎么样也无法启动成功(这里不知道什么原因win 服务中使用的9.2.0.8的软件,估计被人误操作了).解决该问题,就是把服务切换成10.2.0.1版本数据库正常启动.
再次提醒大家，在oracle恢复的过程中,需要仔细分析日志,日志不会骗人,不要轻信客户的现场描述

通过拷贝block实现system文件大量坏块恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：通过拷贝block实现system文件大量坏块恢复

有朋友找到我,他有客户库大量坏块,需要我们提供支持,因为这个库里面含有大量的存储过程,包等,要求数据要直接导出,不能使用工具挖.
dbv检查system大量坏块

DBVERIFY: Release 11.2.0.4.0 - Production on 星期二 11月 22 17:17:51 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = h:\oracle\system01.dbf
页 4543 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x004011bf (file 1, block 4543)
Fractured block found during dbv:
Data in bad block:
 type: 0 format: 2 rdba: 0x004011bf
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x7641344a
 check value in block header: 0xb6ff
 computed block checksum: 0x797
页 4544 标记为损坏
Corrupt block relative dba: 0x004011c0 (file 1, block 4544)
Bad header found during dbv:
Data in bad block:
 type: 71 format: 3 rdba: 0x754e362f
 last change scn: 0x7a37.6d424862 seq: 0x39 flg: 0x32
 spare1: 0x35 spare2: 0x32 spare3: 0x3931
 consistency value in tail: 0x7638356c
 check value in block header: 0x4856
 block checksum disabled
 …………
页 4613 标记为损坏
Corrupt block relative dba: 0x00401205 (file 1, block 4613)
Bad header found during dbv:
Data in bad block:
 type: 97 format: 7 rdba: 0x79634449
 last change scn: 0x4364.77426a4c seq: 0x41 flg: 0x35
 spare1: 0x34 spare2: 0x36 spare3: 0x7734
 consistency value in tail: 0x505a4550
 check value in block header: 0x434d
 computed block checksum: 0x6f3f
页 4614 标记为损坏
Corrupt block relative dba: 0x00401206 (file 1, block 4614)
Completely zero block found during dbv:
…………
页 5125 标记为损坏
Corrupt block relative dba: 0x00401405 (file 1, block 5125)
Completely zero block found during dbv:
DBVERIFY - 验证完成
检查的页总数: 124160
处理的页总数 (数据): 90745
失败的页总数 (数据): 0
处理的页总数 (索引): 14417
失败的页总数 (索引): 0
处理的页总数 (其他): 3323
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 15092
标记为损坏的总页数: 583
流入的页总数: 5
加密的总页数        : 0
最高块 SCN            : 1417256245 (2.1417256245)

这里比较明显，一共583个坏块，而且是连续坏块(5125-4543+1)

尝试启动数据库

--直接尝试打开数据库
SQL> RECOVER DATABASE;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01578: ORACLE data block corrupted (file # 1, block # 4575)
ORA-01110: data file 1: 'H:\ORACLE\SYSTEM01.DBF'
进程 ID: 2572
会话 ID: 85 序列号: 1
--跳过坏块event打开库
SQL> startup mount pfile='h:/oracle/pfile.txt'
ORACLE 例程已经启动。
Total System Global Area 2137886720 bytes
Fixed Size                  2282944 bytes
Variable Size             520096320 bytes
Database Buffers         1610612736 bytes
Redo Buffers                4894720 bytes
数据库装载完毕。
SQL> show parameter event;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      43810 trace name context forev
                                                 er, level 10, 10231 trace name
                                                  context forever, level 10, 10
                                                 232 trace name context forever
                                                 , level 10, 10233 trace name c
                                                 ontext forever, level 10, 1004
                                                 1 trace name context forever,
                                                 level 10
xml_db_events                        string      enable
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'H:\ORACLE\SYSTEM01.DBF'
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [],
[], [], [], [], []
进程 ID: 9316
会话 ID: 4 序列号: 3
--upgrade方式打开数据库
SQL> startup mount pfile='h:/oracle/pfile.txt'
ORACLE 例程已经启动。
Total System Global Area 2137886720 bytes
Fixed Size                  2282944 bytes
Variable Size             520096320 bytes
Database Buffers         1610612736 bytes
Redo Buffers                4894720 bytes
数据库装载完毕。
SQL> alter database open upgrade
  2  ;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [],
[], [], [], [], []
进程 ID: 7976
会话 ID: 4 序列号: 3

至此数据库在这种坏块情况下，正常打开相当渺茫,因为报错的这些block 都是非常靠前的,也就是说这个里面很多块在数据库创建好的时候就已经在了(特别是通过模板创建的数据库,这些部分很可能都是固定的),考虑使用其他库的block来替代这些坏块,然后尝试打开库

修复坏块

[oracle@app101-20 ~]$ dd if=/Data/oracle/oradata/txlhdb/system01.dbf of=/tmp/1.dbf skip=4543 bs=8192 count=583
583+0 records in
583+0 records out
4775936 bytes (4.8 MB) copied, 0.0533578 s, 89.5 MB/s
H:\oracle>dd if=d:/temp/1.dbf of=h:\oracle\system01.dbf seek=4543 bs=8192 count=583 conv=notrun
rawwrite dd for windows version 0.6beta3.
Written by John Newbigin <jn@it.swin.edu.au>
This program is covered by terms of the GPL Version 2.
notrun
583+0 records in
583+0 records out
H:\oracle>dbv file=system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on 星期二 11月 22 20:17:51 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = H:\ORACLE\SYSTEM01.DBF
DBVERIFY - 验证完成
检查的页总数: 124160
处理的页总数 (数据): 90761
失败的页总数 (数据): 0
处理的页总数 (索引): 14479
失败的页总数 (索引): 0
处理的页总数 (其他): 3393
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 15527
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 295310052 (11.295310052)

再次尝试打开数据库

C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期二 11月 22 20:18:19 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
已连接到空闲例程。
SQL> startup mount pfile='h:/oracle/pfile.txt';
ORACLE 例程已经启动。
Total System Global Area 2137886720 bytes
Fixed Size                  2282944 bytes
Variable Size             520096320 bytes
Database Buffers         1610612736 bytes
Redo Buffers                4894720 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
数据库已更改。

后续错误ORA-04023处理

C:\Users\XIFENFEI>exp "'/ as sysdba'" owner=XIFENFEI file=d:/full_xff.dmp lo
g=d:/full_xff.log   FEEDBACK=10000  COMPRESS=NO BUFFER=102400000 STATISTICS=none
Export: Release 11.2.0.4.0 - Production on 星期二 11月 22 20:20:27 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00056: 遇到 ORACLE 错误 4023
ORA-04023: 无法验证或授权对象 SELECT xdb_uid FROM SYS.EXU9XDBUID
EXP-00000: 导出终止失败

数据库所有视图无法查询,通过直接对基表user$,obj$,view$等表查询出来视图信息,然后直接编译,然后数据可以完美导出,完成本次恢复

SQL> select 'alter view '||b.name||'.'||c.name||'  compile;'
  2  from view$ a,user$ b,obj$ c
  3  where a.obj#=c.obj#
  4  and c.owner#=b.user#;

数据库启动报ORA-00704 ORA-39714错误解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：数据库启动报ORA-00704 ORA-39714错误解决

数据库启动失败,报ORA-00704、ORA-39714错误

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 7 08:15:35 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> startup
ORACLE instance started.
Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Process ID: 11592
Session ID: 1 Serial number: 5
SQL> startup upgrade
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

alert日志报错

Thu Aug 07 07:42:25 2014
SMON: enabling cache recovery
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Error 704 happened during db open, shutting down database
USER (ospid: 11592): terminating the instance due to error 704

通过分析utlmmig.sql脚本知道,数据库在升级bootstrap$之前会先在props$表中插入BOOTSTRAP_UPGRADE_ERROR相关记录,数据库在启动之时会检测该值,如果发现该值存在,数据库只能以upgrade模式启动,清理掉相关记录,数据库即可正常启动

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 7 07:42:44 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.
SQL>   delete from props$ where name = 'BOOTSTRAP_UPGRADE_ERROR';
1 row deleted.
SQL>   delete from props$ where name = 'LOGMNR_BOOTSTRAP_UPGRADE_ERROR';
0 rows deleted.
SQL>   commit;
Commit complete.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.
SQL>

数据库虽然正常启动成功,但是由于bootstrap$对象升级失败,后续还是有很大风险,建议分析报错原因,解决原因然后继续升级bootstrap$基表

记录一次ORA-600 kccpb_sanity_check_2和ORA-600 kcbgtcr_13 错误恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：记录一次ORA-600 kccpb_sanity_check_2和ORA-600 kcbgtcr_13 错误恢复

晚上朋友告诉我数据库不能open,请求技术支持,检查alert日志发现ORA-00600[kccpb_sanity_check_2]错误导致数据库无法正常mount

Fri Jun  6 23:36:08 2014
alter database mount
Fri Jun  6 23:36:08 2014
This instance was first to mount
Fri Jun  6 23:36:12 2014
Errors in file /on3000/oracle/admin/on3000/udump/on30001_ora_295198.trc:
ORA-00600:内部错误代码, 参数:[kccpb_sanity_check_2], [18045], [17928], [0x000000000], [], [], [], []
ORA-600 signalled during: alter database mount...

依次替换三个控制文件依然无法解决该问题。查询MOS得到解释为[435436.1]

ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is
higher than the seq# of the control file header block. This is indication of
the lost write of the header block during commit of the previous cf transaction.

出现该故障的原因是因为写丢失导致,而解决该故障的方法有

1) restore a backup of a controlfile and recover
OR
2) recreate the controlfile
OR
3) restore the database from last good backup and recover

该数据库为无备份非归档数据库,因此只能重建控制文件来解决ORA-00600[kccpb_sanity_check_2]故障,通过重建控制文件数据库mount成功.但是在open的过程中又出现需要一个不存在的归档日志(数据库一个节点5月5日异常,另外一个节点5月23日异常,到6月6日我接手中间进行了N多操作,未细细分析原因).
Oracle Database Recovery Check Result检查结果
数据库SCN

控制文件中关于数据文件SCN

数据文件头SCN

REDO SCN

这里明显表示thread#缺少归档,导致恢复过程出现如下提示

最后没有办法只能使用_allow_resetlogs_corruption参数跳过redo,然后去open数据库,很不幸出现更加悲催的ORA-00704和ORA-00600[kcbgtcr_13]错误,导致数据库open失败

Sat Jun  7 00:28:58 2014
SMON: enabling cache recovery
Sat Jun  7 00:28:58 2014
Errors in file /on3000/oracle/admin/on3000/udump/on30001_ora_344084.trc:
ORA-00600: 内部错误代码, 参数: [kcbgtcr_13], [], [], [], [], [], [], []
Sat Jun  7 00:28:59 2014
Errors in file /on3000/oracle/admin/on3000/udump/on30001_ora_344084.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [kcbgtcr_13], [], [], [], [], [], [], []
Sat Jun  7 00:28:59 2014
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 344084
ORA-1092 signalled during: alter database open...

对启动过程做10046发现

*** 2014-06-07 00:28:58.528
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [kcbgtcr_13], [], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000004 ? 10538629C ?
ksedmp+0290          bl       ksedst               104A2CDB0 ?
ksfdmp+0018          bl       03F2735C
kgerinv+00dc         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              000000000 ? 10564B4CC ?
                                                   000000004 ? 70000006D3FD6F0 ?
                                                   000000000 ?
kcbassertbd+0074     bl       kgeasnmierr          110195490 ? 110486310 ?
                                                   10564BD54 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 11043DC90 ?
kcbgtcr+2a68         bl       kcbassertbd          FFFFFFFFFFE7D00 ?
                                                   FFFFFFFFFE7D28 ?
kturbk1+0258         bl       kcbgtcr              000000000 ? 104D23384 ?
                                                   104D2330C ? 000000000 ?
ktrgcm+1294          bl       kturbk1              F0000000F ? FFFFFFFFFFE84B8 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFE84D0 ? 000000000 ?
ktrgtc+0660          bl       ktrgcm               1104B7600 ?
kdsgrp+0094          bl       ktrgtc               0000006C8 ? 000000000 ?
                                                   FFFFFFFFFFE8D80 ?
                                                   28242043335E5162 ?
                                                   103A06D48 ? 70000006F6C87B8 ?
                                                   1051C3528 ?
kdsfbrcb+0298        bl       kdsgrp               1044726E4 ?
                                                   433000000000003B ?
                                                   FFFFFFFFFFE8E30 ?
qertbFetchByRowID+0  bl       03F27E18
69c
qerstFetch+00ec      bl       01F9482C
opifch2+141c         bl       03F25E6C
opifch+003c          bl       opifch2              FFFFFFFFFFEC3D8 ? 000000000 ?
                                                   FFFFFFFFFFEA360 ?
opiodr+0ae0          bl       _ptrgl
rpidrus+01bc         bl       opiodr               5FFFEC840 ? 200000000 ?
                                                   FFFFFFFFFFEC850 ? 500000000 ?
skgmstack+00c8       bl       _ptrgl
rpidru+0088          bl       skgmstack            11049A820 ? 110195490 ?
                                                   000000002 ? 000000000 ?
                                                   FFFFFFFFFFEC3E8 ?
rpiswu2+034c         bl       _ptrgl
rpidrv+095c          bl       rpiswu2              70000006F6C7250 ? 1104851E8 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   1101B8C48 ? 000000000 ?
rpifch+0050          bl       rpidrv               5FFFEC840 ? 500000000 ?
                                                   FFFFFFFFFFEC850 ? 06CD75F70 ?
kqdpts+0134          bl       rpifch               11022A3E0 ?
kqrlfc+0258          bl       kqdpts               000000000 ?
kqlbplc+00b4         bl       03F28AF0
kqlblfc+0204         bl       kqlbplc              10011A9A8 ?
adbdrv+1978          bl       01F944B4
opiexe+2c08          bl       adbdrv
opiosq0+19f0         bl       opiexe               FFFFFFFFFFF9550 ? 100000000 ?
                                                   FFFFFFFFFFF8F20 ?
kpooprx+0168         bl       opiosq0              3693644A8 ? 700000010003520 ?
                                                   700000069364428 ?
                                                   A4000110195490 ?
kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB774 ?
                                                   FFFFFFFFFFFB500 ?
                                                   1B0000001B ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
                                                   000000000 ? 11039FA18 ?
opiodr+0ae0          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+1124          bl       01F971E8
opiino+0990          bl       opitsk               1E00000000 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl
opidrv+0484          bl       01F96034
sou2o+0090           bl       opidrv               3C02D9A29C ? 4A006E298 ?
                                                   FFFFFFFFFFFF6B0 ?
opimai_real+01bc     bl       01F939B4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0070         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
--undo cr构造记录
Dump of buffer cache at level 1 for tsn=4, rdba=16777293
BH (700000035fb77b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b4000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f969c8,70000006d3fd868] lru: [700000035fb7728,70000006d6acff0]
  ckptq: [NULL] fileq: [NULL] objq: [700000035fb7798,7000000693932c0]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fb],[sfl: 0x0]
  flags:
BH (700000035f969c8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000353d6000
  set: 9 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035ff9398,700000035fb77b8] lru: [700000035f96938,70000006d6aca98]
  ckptq: [NULL] fileq: [NULL] objq: [700000035f969a8,700000069390250]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fa],[sfl: 0x0]
  flags:
BH (700000035ff9398) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 700000035f70000
  set: 12 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035fd86b8,700000035f969c8] lru: [700000035ff9528,70000006d6adaa0]
  ckptq: [NULL] fileq: [NULL] objq: [7000000693973c8,7000000693973c8]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f9],[sfl: 0x0]
  flags:
BH (700000035fd86b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 700000035b94000
  set: 11 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035fb76a8,700000035ff9398] lru: [700000035fd8848,70000006d6ad548]
  ckptq: [NULL] fileq: [NULL] objq: [700000069396398,700000069396398]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f8],[sfl: 0x0]
  flags:
BH (700000035fb76a8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b2000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f968b8,700000035fd86b8] lru: [700000035fb7948,700000035fb7838]
  ckptq: [NULL] fileq: [NULL] objq: [7000000693932c0,700000035fb78a8]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f7],[sfl: 0x0]
  flags:
BH (700000035f968b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000353d4000
  set: 9 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [70000006d3fd868,700000035fb76a8] lru: [700000035f96b58,700000035f96a48]
  ckptq: [NULL] fileq: [NULL] objq: [700000069390250,700000035f96ab8]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f6],[sfl: 0x0]
  flags:
WAIT #5: nam='db file sequential read' ela= 135 file#=4 block#=77 blocks=1 obj#=-1 tim=1107709395109
on-disk scn: 0x0.19af5d47
BH (700000035fb77b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b4000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f969c8,70000006d3fd868] lru: [700000035fb7728,70000006d6acff0]
  ckptq: [NULL] fileq: [NULL] objq: [700000035fb7798,7000000693932c0]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fb],[sfl: 0x0]
  flags:
Dump of buffer cache at level 10 for tsn=4, rdba=16777293
BH (700000035fb77b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b4000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f969c8,70000006d3fd868] lru: [700000035fb7728,70000006d6acff0]
  ckptq: [NULL] fileq: [NULL] objq: [700000035fb7798,7000000693932c0]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fb],[sfl: 0x0]
  flags:
  buffer tsn: 4 rdba: 0x0100004d (4/77)
  scn: 0x0000.19af5d47 seq: 0x01 flg: 0x04 tail: 0x5d470201
  frmt: 0x02 chkval: 0x6d2e type: 0x02=KTU UNDO BLOCK
--obj$ block dump记录
Block header dump:  0x0040007a
 Object id on Block? Y
 seg/obj: 0x12  csc: 0x00.19afb597  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000f.012.0002c79c  0x0100004d.6113.1c  --U-    1  fsc 0x0000.19afb598

这里可以知道,数据库在读取obj$的时候使用到了undo cr块的构造,由于某种原因导致构造cr块失败,从而出现ORA-00600[kcbgtcr_13]错误,而因为obj$又在bootstarp$里面,因此又出现ORA-704.所以解决该问题的方法就是让数据库在查询obj$表的时候不再进行cr块构造,比如使用bbed提交事务等方法解决.这里使用bbed提交事务(bbed模拟提交事务一之修改itl),数据库启动成功

SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1610612736 bytes
Fixed Size                  2084400 bytes
Variable Size             973078992 bytes
Database Buffers          620756992 bytes
Redo Buffers               14692352 bytes
数据库装载完毕。
数据库已经打开。

undo异常总结和恢复思路

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：undo异常总结和恢复思路

UNDO异常报错千奇百怪,针对本人遇到的比较常见的undo异常报错进行汇总，仅供参考，数据库恢复过程是千奇百怪的，不能照搬硬套.
ORA-00704/ORA-00376
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: ‘/u01/oracle/oradata/ora11g/undotbs01.dbf’
Error 704 happened during db open, shutting down database
USER (ospid: 17864): terminating the instance due to error 704
Instance terminated by USER, pid = 17864
ORA-1092 signalled during: alter database open…
opiodr aborting process unknown ospid (17864) as a result of ORA-1092

ORA-00600[4097]
Fri Aug 31 23:14:10 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Fri Aug 31 23:14:12 2012
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.

ORA-01595/ORA-00600[4194]
Fri Aug 31 23:14:14 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-01595: error freeing extent (2) of rollback segment (4))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []

0RA-00600[4193]
Tue Feb 14 09:35:34 2012
Errors in file d:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2824.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [2005], [2008], [], [], [], [], []

ORA-00600[kcfrbd_3]
Wed Dec 05 10:26:35 2012
SMON: enabling tx recovery
Wed Dec 05 10:26:35 2012
Database Characterset is ZHS16GBK
Wed Dec 05 10:26:35 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []
SMON: terminating instance due to error 474

ORA-00600[4137]
Fri Jul 6 18:00:40 2012
SMON: ignoring slave err,downgrading to serial rollback
Fri Jul 6 18:00:41 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_smon_16636.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
ORACLE Instance techdb (pid = 8) – Error 600 encountered while recovering transaction (3, 17).

ORA-01595/ORA-01594
Sat May 12 21:54:17 2012
Errors in file /oracle/app/admin/prmdb/bdump/prmdb2_smon_483522.trc:
ORA-01595: error freeing extent (2) of rollback segment (19))
ORA-01594: attempt to wrap into rollback segment (19) extent (2) which is being freed

ORA-00704/ORA-01555
Fri May 4 21:04:21 2012
select ctime, mtime, stime from obj$ where obj# = :1
Fri May 4 21:04:21 2012
Errors in file /oracle/admin/standdb/udump/perfdb_ora_1286288.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 40 with name “_SYSSMU40$” too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 1286288
ORA-1092 signalled during: alter database open resetlogs…

ORA-00607/ORA-00600[4194]
Block recovery completed at rba 3994.5.16, scn 0.89979533
Thu Jul 26 13:21:11 2012
Errors in file /orasvr/admin/mispdata/udump/mispdata_ora_2865.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], [31], [2], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 2865
ORA-1092 signalled during: ALTER DATABASE OPEN…

ORA-00704/ORA-00600[4000]
Thu Feb 28 19:29:13 2013
Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], []
Thu Feb 28 19:29:13 2013
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 20989
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS…

undo异常恢复处理思路
除了极少数undo坏块,undo文件丢失外，大部分undo异常是因为redo未被正常进行前滚，从而导致undo回滚异常数据库无法open，解决此类问题，需要结合一般需要结合redo异常处理技巧在其中，一般undo异常处理思路
1.切换undo_management= MANUAL尝试启动数据库，如果不成功进入2
2.设置10513 等event尝试启动数据库，如果不成功进入3
3.使用_offline_rollback_segments/_corrupted_rollback_segments屏蔽回滚段
4.如果依然不能open数据库,考虑使用bbed工具提交事务，修改回滚段状态等操作
5.如果依然还不能open数据库，考虑使用dul

如果您按照上述步骤还不能解决,请联系我们,将为您提供专业数据库技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

姊妹篇
ORACLE REDO各种异常恢复
 ORACLE丢失各种文件导致数据库不能OPEN恢复

记录一次ORA-00316 ORA-00312 redo异常恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：记录一次ORA-00316 ORA-00312 redo异常恢复

正常运行的数据库报突然报ORA-00316: log 1 of thread 1, type 286 in header is not log file,异常终止

Fri Feb 21 08:44:42 2014
Thread 1 advanced to log sequence 591 (LGWR switch)
  Current log# 1 seq# 591 mem# 0: J:\ORADATA\ORCL\REDO01.LOG
Fri Feb 21 15:31:20 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_10312.trc:
ORA-00316: log 1 of thread 1, type 286 in header is not log file
ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'
Fri Feb 21 15:31:20 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_10312.trc:
ORA-00316: log 1 of thread 1, type 286 in header is not log file
ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'
Fri Feb 21 15:31:20 2014
LGWR: terminating instance due to error 316
Fri Feb 21 15:31:20 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j001_11328.trc:
ORA-00316: log  of thread , type  in header is not log file
Fri Feb 21 15:31:20 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_14116.trc:
ORA-00316: 日志  (用于线程 ) 标头中的类型  不是日志文件
Fri Feb 21 15:31:20 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw1_8964.trc:
ORA-00316: log  of thread , type  in header is not log file
Fri Feb 21 15:31:22 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_10592.trc:
ORA-00316: log  of thread , type  in header is not log file
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
Fri Feb 21 15:31:41 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_11112.trc:
ORA-00316: log  of thread , type  in header is not log file
Fri Feb 21 15:31:41 2014
Instance terminated by LGWR, pid = 10312

数据库启动报ORA-00316错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00316: log 1 of thread 1, type 286 in header is not log file
ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'

alert日志信息,报ORA-00316 ORA-00312

Sun Feb 23 13:54:08 2014
Started redo scan
Sun Feb 23 13:54:08 2014
Errors in file e:\oracle\product\10.2.0\admin\ora10g\udump\orcl_ora_5544.trc:
ORA-00316: log 1 of thread 1, type 286 in header is not log file
ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'
Sun Feb 23 13:54:08 2014
Aborting crash recovery due to error 316
Sun Feb 23 13:54:08 2014
Errors in file e:\oracle\product\10.2.0\admin\ora10g\udump\orcl_ora_5544.trc:
ORA-00316: log 1 of thread 1, type 286 in header is not log file
ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'
ORA-316 signalled during: alter database open...

通过dump redo header可以看出来redo header完全混乱了,里面很多数据文件内容在里面,初步估计系统或者硬件有问题(不稳定)导致该问题

LOG FILE #1:
  (name #3) J:\ORADATA\ORCL\REDO01.LOG
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x7d000 seq: 0x0000024f hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.4f4a1951
 Low scn: 0x0000.4f4e5400 02/21/2014 08:44:42
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
 FILE HEADER:
	Software vsn=4280360459=0xff211e0b, Compatibility Vsn=103886850=0x6313002
	Db ID=842019892=0x32303434, Db Name='01a0001'
	Activation ID=1107558657=0x42040101
	Control Seq=842019892=0x32303434, File size=808464688=0x30303130
	File Number=2417, Blksiz=2013738803, File Type=286 UNKNOWN
 descrip:"00-440201|广东省xxxxxx研究院|00014402010037"
 thread: 767 nab: 0x534e4906 seq: 0xbcfebcbd hws: 0xffffffff eot: 55 dis: 66
 reset logs count: 0x7545245 scn: 0x0101.1e097178
 Low scn: 0x4537.2022002c 01/15/2022 20:59:37
 Next scn: 0x3734.46463135 04/30/2017 17:34:49
 Enabled scn: 0x4345.37333038 10/22/2015 02:04:16
 Thread closed scn: 0x3939.43303143 08/25/2024 00:11:31
 Log format vsn: 0x46343835 Disk cksum: 0x1d09 Calc cksum: 0x70ed
 Terminal Recovery Stop scn: 0x3734.35304141
 Terminal Recovery Stamp  03/17/2014 19:59:48
 Most recent redo scn: 0x3636.31303134
 Largest LWN: 758788710 blocks
 Miscellaneous flags: 0x41444534
 Thread internal enable indicator: thr: 263902399, seq: 808464496 scn: 0x3032.30343431

因为当前redo完全损坏,尝试不完全恢复并结合隐含参数(_allow_resetlogs_corruption)拉库,出现错误ORA-00704 ORA-00604 ORA-01555

Sun Feb 23 14:03:37 2014
SMON: enabling cache recovery
Sun Feb 23 14:03:39 2014
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.4f4e5405):
Sun Feb 23 14:03:39 2014
select ctime, mtime, stime from obj$ where obj# = :1
Sun Feb 23 14:03:39 2014
Errors in file e:\oracle\product\10.2.0\admin\ora10g\udump\orcl_ora_5504.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704

通过修改scn,让数据库顺利open

标签归档：ORA-00704