ORA-00704 ORA-00604 ORA-00942故障恢复

接到客户请求,oracle数据库停机重启维护之后,无法正常启动,请求我们给予协助
数据库启动报ORA-00704 ORA-00604 ORA-00942错误

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> startup open
ORACLE 例程已经启动。

Total System Global Area 1288949760 bytes
Fixed Size                  1376520 bytes
Variable Size             377491192 bytes
Database Buffers          897581056 bytes
Redo Buffers               12500992 bytes
数据库装载完毕。
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
进程 ID: 2756
会话 ID: 5 序列号: 9

alert日志报错

SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_2756.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在
Errors in file d:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_2756.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在
Error 704 happened during db open, shutting down database
USER (ospid: 2756): terminating the instance due to error 704
Instance terminated by USER, pid = 2756
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (2756) as a result of ORA-1092
Fri Nov 30 12:51:26 2018
ORA-1092 : opitsk aborting process

根据这些年的恢复经验,恢复过相关错误的主要有:
ORA-01092 ORA-00704 ORA-00942
Oracle 11g丢失access$恢复方法
11.1.0.7版本也会出现access$表丢失导致数据库无法启动
总结主要两类:1. 由于某种bug导致access$表丢失的故障,另外一种是由于坏块导致数据库核心基表损坏引起,对于这个库进行分析

dbv 检查坏块

D:\APP\ADMINISTRATOR\ORADATA\ORCL> dbv file=system01.dbf

DBVERIFY: Release 10.2.0.3.0 - Production on 星期五 11月 30 15:11:24 2018

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

DBVERIFY - 开始验证: FILE = system01.dbf


DBVERIFY - 验证完成

检查的页总数: 93440
处理的页总数 (数据): 61979
失败的页总数 (数据): 0
处理的页总数 (索引): 13560
失败的页总数 (索引): 0
处理的页总数 (其它): 3067
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 14834
标记为损坏的总页数: 0
流入的页总数: 0
最高块 SCN            : 659587683 (0.659587683)

通过dbv检查数据库,确定system没有坏块
10046跟踪数据库启动

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1288949760 bytes
Fixed Size                  1376520 bytes
Variable Size             377491192 bytes
Database Buffers          897581056 bytes
Redo Buffers               12500992 bytes
数据库装载完毕。

SQL> oradebug setmypid
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;

会话已更改。

SQL> ALTER SESSION SET EVENTS '704 trace name errorstack level 3';

会话已更改。

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> oradebug TRACEFILE_NAME
d:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_1132.trc

--trace信息
PARSE ERROR #3:len=208 dep=1 uid=0 oct=9 lid=0 tim=5838844893 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))

*** 2018-11-30 13:18:07.593
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在

通过这一步基本上可以判断是由于obj$表丢失导致数据库创建I_OBJ1 index不成功,从而使得数据库无法正常启动。通过一些技巧修复出来obj$表,尝试启动数据库

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [6], [], [], [], [], [], [],
[], [], [], []
进程 ID: 836
会话 ID: 355 序列号: 6027

alert日志报错

Fri Nov 30 15:33:47 2018
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_836.trc  (incident=648694):
ORA-00600: 内部错误代码, 参数: [4000], [6], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\xff\xff\incident\incdir_648694\xff_ora_836_i648694.trc
Errors in file d:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_836.trc:
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [4000], [6], [], [], [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_836.trc:
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [4000], [6], [], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 836): terminating the instance due to error 704
Fri Nov 30 15:33:57 2018
Instance terminated by USER, pid = 836
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (836) as a result of ORA-1092

发现数据库启动报ORA-00704和ORA-00600: internal error code, arguments: [4000], [6], [], [], [], [], [], [],错误,根据以往经验,该问题是由于回滚段异常导致
分析trace信息

Dump continued from file: d:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_2124.trc
ORA-00600: 内部错误代码, 参数: [4000], [6], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 651102 (ORA 600 [4000]) ========

*** 2018-11-30 15:46:32.125
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for txff session (sql_id=6apq2rjyxmxpj) -----
select line#, sql_text from bootstrap$ where obj# != :1

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_skdstdst()+121      CALLrel  _kgdsdst()           E6B1614 2
_ksedst1()+93        CALLrel  _skdstdst()          E6B1614 0 1 436646 435BE2
                                                   436646
_ksedst()+49         CALLrel  _ksedst1()           0 1
_dbkedDefDump()+367  CALLrel  _ksedst()            0
2                                                  
_ksedmp()+44         CALLrel  _dbkedDefDump()      3 2
_ksfdmp()+56         CALLrel  _ksedmp()            3EB
_dbgexPhaseII()+164  CALLreg  00000000             C378A28 3EB
0                                                  
_dbgexProcessError(  CALLrel  _dbgexPhaseII()      E2B0454 E2B6E50 E6B6078
)+2061                                             
_dbgeExecuteForErro  CALLrel  _dbgexProcessError(  E2B0454 E2B6E50 1 0 E2B0454
r()+43                        )                    E2B6E50
__VInfreq__dbgePost  CALLrel  _dbgeExecuteForErro  E2B0454 E2B6E50 0 1 0
ErrorKGE()+260                r()                  
_dbkePostKGE_kgsf()  CALLrel  _dbgePostErrorKGE()  C378A28 E2BD274 258
+56                                                
_kgeade()+299        CALLreg  00000000             C378A28 E2BD274 258
_kgeriv_int()+79     CALLrel  _kgeade()            C378A28 C378B50 E2BD274 258 0
                                                   FA0 0 0 0 1 E6B68A8
_kgeriv()+22         CALLrel  _kgeriv_int()        C378A28 E2BD274 FA0 0 1
                                                   E6B68A8
_kgeasi()+107        CALLrel  _kgeriv()            C378A28 E2BD274 FA0 1 E6B68A8
__VInfreq__ktuGetUs  CALLrel  _kgeasi()            C378A28 E2BD274 FA0 2 1 0 6 0
egDba()+123                                        
_ktrgcm()+5147       CALLrel  _ktuGetUsegDba()     6 E6B6E78 0 0 E6B6F48 0
_ktrget2()+596       CALLrel  _ktrgcm()            F9FCEAC
_kdst_fetch()+816    CALLrel  _ktrget2()           F9FCEAC F9FCE24 303 0
_kdstf11001010000km  CALLrel  _kdst_fetch()        1 F9FCEA8 E6B71C8
()+2806                                            
_kdsttgr()+5944      CALLrel  _kdstf11001010000km  F9FCEA8 0 557B044C F9FCDF8
                              ()                   2F0C3A6 E6B7894
_qertbFetch()+767    CALLrel  _kdsttgr()           F9FCEA8 0 557B044C F9FCDF8
                                                   557B0498 2F0C3A6 E6B7894 1
_opifch2()+2729      CALLptr  00000000             E6E7228 0 0 2 26180001
_opifch()+53         CALLrel  _opifch2()           89 5 E6B7A04
_opiodr()+1248       CALLreg  00000000             5 2 E6B81FC
_rpidrus()+186       CALLrel  _opiodr()            5 2 E6B81FC 2
_rpidru()+90         CALLrel  _rpidrus()           E6B7D58
_rpiswu2()+557       CALLrel  _rpidru()            E6B813C
_rpidrv()+1242       CALLrel  _rpiswu2()           6D9A295C 0 6D9A29A8 2 E6B8184
                                                   0 6D9A2A28 0 0 544632 5448D6
                                                   E6B813C 8
_rpifch()+43         CALLrel  _rpidrv()            2 5 E6B81FC 8
_kqlbebs()+1213      CALLrel  _rpifch()            2 2 2 F013232 FA0 1 0 E6B8634
                                                   0 0 0 0 0
_kqlblfc()+175       CALLrel  _kqlbebs()           0 E6BBBD4
_adbdrv()+16992      CALLrel  _kqlblfc()           0 E6BBBD4
_opiexe()+13594      CALLrel  _adbdrv()            4A 6E6CBC48 6DDEDB6C E6BBD68
                                                   6D60697 6E6CBC48
_opiosq0()+6248      CALLrel  _opiexe()            4 0 E6BC734
_kpooprx()+277       CALLrel  _opiosq0()           3 E E6BC9A0 A4 0
_kpoal8()+632        CALLrel  _kpooprx()           E6BF0A4 E6BD420 1B 1 0 A4
_opiodr()+1248       CALLreg  00000000             5E 1C E6BF0A0
_ttcpip()+1051       CALLreg  00000000             5E 1C E6BF0A0 0
_opitsk()+1404       CALL???  00000000             C3832A8 5E E6BF0A0 0 E6BED30
                                                   E6BF1CC 53E52E 0 E6BF1F8
_opiino()+980        CALLrel  _opitsk()            0 0
_opiodr()+1248       CALLreg  00000000             3C 4 E6BFBF4
_opidrv()+1201       CALLrel  _opiodr()            3C 4 E6BFBF4 0
_sou2o()+55          CALLrel  _opidrv()            3C 4 E6BFBF4
_opimai_real()+124   CALLrel  _sou2o()             E6BFC04 3C 4 E6BFBF4
_opimai()+125        CALLrel  _opimai_real()       2 E6BFC2C
_OracleThreadStart@  CALLrel  _opimai()            2 E6BFF6C 7C9BA7F4 E6BFC34 0
4()+830                                            E6BFD04
7C82484C             CALLreg  00000000             E5BFF9C 0 0 E5BFF9C 0 E6BFFC4
00000000             CALL???  00000000             
 

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


Block header dump:  0x0040020b
 Object id on Block? Y
 seg/obj: 0x3b  csc: 0x00.27508136  itc: 1  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.015.0005e9e9  0x00c0052c.916f.10  --U-    1  fsc 0x0000.27508263

报错比较明显该block比较异常,通过bbed修改相关block信息,相关处理参考:
重现ORA-600 4000异常
通过bbed解决ORA-00600[4000]案例
记录一次ORA-600 4000数据库故障恢复
ORACLE 8.1.7 数据库ORA-600 4000故障恢复

处理之后数据库正常open

Fri Nov 30 15:57:34 2018
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Nov 30 15:57:38 2018
Starting background process QMNC
Fri Nov 30 15:57:38 2018
QMNC started with pid=23, OS id=1152 
Completed: alter database open
发表在 非常规恢复 | 标签为 , , , | 留下评论

ORA-00322 ORA-00312 恢复

数据库mount报ORA-00214错误

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期二 11月 27 14:41:15 2018

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


连接到:
XIFENFEIle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
第 1 行出现错误:
ORA-01507: ??????


SQL> alter database mount;
alter database mount
*
第 1 行出现错误:
ORA-00214: ???? ''D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\CONTROL01.CTL'' ?? 14709
??? ''D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XIFENFEI\CONTROL02.CTL'' ?? 14681 ???

使用其中一个控制文件启动数据库报ORA-00322 ORA-00312错误

SQL> startup mount;
XIFENFEILE 例程已经启动。

Total System Global Area 5127602176 bytes
Fixed Size                  2290120 bytes
Variable Size            1056968248 bytes
Database Buffers         4060086272 bytes
Redo Buffers                8257536 bytes
数据库装载完毕。

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1:
'D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO01.LOG'

alert日志报ORA-00322 ORA-00312 ORA-00314 等错

Tue Nov 27 14:42:44 2018
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 24 slaves
Tue Nov 27 14:42:45 2018
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr00_4168.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO01.LOG'
Tue Nov 27 14:42:45 2018
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_m000_3876.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO01.LOG'
Media Recovery failed with error 322
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr00_4168.trc:
ORA-00283: recovery session canceled due to errors
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO01.LOG'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_m000_3876.trc:
ORA-00314: log 2 of thread 1, expected sequence# 722 doesn't match 719
ORA-00312: online log 2 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO02.LOG'
Checker run found 4 new persistent data failures
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

通过Oracle Database Recovery Check脚本检查数据库结果
datafile-header
logfile


通过这里可以看出来,数据库需要的redo确实是721,但是recover无法应用成功,出现该问题的原因是由于控制文件信息不对导致

使用备份控制文件恢复

D:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期二 11月 27 14:44:00 2018

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


连接到:
XIFENFEIle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile;
ORA-00279: 更改 4034899 (在 11/27/2018 10:37:04 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2018_11_27\O1_MF_1_721_%U_.ARC
ORA-00280: 更改 4034899 (用于线程 1) 在序列 #721 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO02.LOG
ORA-00310: archived log contains sequence 719; sequence 721 required
ORA-00334: archived log: 'D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO02.LOG'


SQL> recover database using backup controlfile;
ORA-00279: 更改 4034899 (在 11/27/2018 10:37:04 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2018_11_27\O1_MF_1_721_%U_.ARC
ORA-00280: 更改 4034899 (用于线程 1) 在序列 #721 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO02.LOG
ORA-00310: archived log contains sequence 719; sequence 721 required
ORA-00334: archived log: 'D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO02.LOG'


SQL> D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO02.LOG
SP2-0734: 未知的命令开头 "D:\APP\ADM..." - 忽略了剩余的行。
SQL> recover database using backup controlfile;
ORA-00279: 更改 4034899 (在 11/27/2018 10:37:04 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2018_11_27\O1_MF_1_721_%U_.ARC
ORA-00280: 更改 4034899 (用于线程 1) 在序列 #721 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XIFENFEI\REDO01.LOG
已应用的日志。
完成介质恢复。
SQL> alter database open resetlogs;

数据库已更改。

实现数据0丢失恢复,数据库open之后可以直接使用

发表在 Oracle备份恢复 | 标签为 , | 留下评论

下调虚拟化资源导致ORA-00494

在虚拟化环境中的Oracle数据库近期报大量ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) 错误,每次客户重启一段时间之后又不行

Mon Nov 26 14:04:39 中国标准时间 2018
Thread 1 advanced to log sequence 97327 (LGWR switch)
  Current log# 1 seq# 97327 mem# 0: D:\ORADATA\xifenfei\REDO01.LOG
Mon Nov 26 14:20:02 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc1_1860.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')

Mon Nov 26 14:20:03 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc4_1872.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')

Mon Nov 26 14:20:03 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc3_1868.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')

Mon Nov 26 14:20:03 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc0_1856.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')

因为这个系统有一段历史,以前是客户的核心生产库,虚拟化资源给的比较多128G,后来核心生产迁移到其他系统,该系统跑一些边缘小业务,所以怀疑客户有可能下调了虚拟化的资源(cpu和内存),检查机器硬件资源

C:\Users\Administrator>systeminfo

主机名:           HIS-VM
OS 名称:          Microsoft Windows Server 2008 R2 Enterprise
OS 版本:          6.1.7601 Service Pack 1 Build 7601
OS 制造商:        Microsoft Corporation
OS 配置:          独立服务器
OS 构件类型:      Multiprocessor Free
注册的所有人:     Windows 用户
注册的组织:
产品 ID:          00486-OEM-8400691-20006
初始安装日期:     2013/4/19, 12:22:44
系统启动时间:     2018/11/27, 8:06:00
系统制造商:       VMware, Inc.
系统型号:         VMware Virtual Platform
系统类型:         x64-based PC
处理器:           安装了 2 个处理器。
                  [01]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel
Mhz
                  [02]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel
Mhz
BIOS 版本:        Phoenix Technologies LTD 6.00, 2014/4/14
Windows 目录:     C:\Windows
系统目录:         C:\Windows\system32
启动设备:         \Device\HarddiskVolume1
系统区域设置:     zh-cn;中文(中国)
输入法区域设置:   zh-cn;中文(中国)
时区:             (UTC+08:00) 北京,重庆,香港特别行政区,乌鲁木齐
物理内存总量:     32,767 MB
可用的物理内存:   22,740 MB
虚拟内存: 最大值: 95,047 MB
虚拟内存: 可用:   10,426 MB
虚拟内存: 使用中: 84,621 MB
页面文件位置:     C:\pagefile.sys
域:               WORKGROUP

果然系统内存从128G下调到了32G,但是数据库sga估计没有对应降低

SQL> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 80G
sga_target                           big integer 80G

基本上就是由于虚拟机内存下调,但是数据库sga配置过大导致该问题.

发表在 ORA-xxxxx | 标签为 , | 留下评论

ORA-600 2131故障说明

oracle 12c数据库启动报ORA-600 2131错误

Mon Nov 26 09:43:57 2018
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\app\Administrator
alter database mount exclusive
Mon Nov 26 09:44:00 2018
Using default pga_aggregate_limit of 2048 MB
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl12c\orcl12c\trace\orcl12c_ora_3040.trc  (incident=375524):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl12c\orcl12c\incident\incdir_375524\orcl12c_ora_3040_i375524.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: alter database mount exclusive...

这个日志比较明显,数据库无法mount,在mount操作的时候报ORA-600 2131错误.
trace文件报错

Error: kccpb_sanity_check_2
Control file sequence number mismatch!
fhcsq: 497844 bhcsq: 497849 cfn 0 rpbn 16

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+92         CALL???  skdstdst()           000000001 000004000 000030000
                                                   016301338
kccpb_sanity_check(  CALL???  ksedst1()            1492761E0 0000798B4 0000798B9
)+834                                              000000000
kccbmp_get()+275     CALL???  kccpb_sanity_check(  000000000 000000000 000000000
                              )                    000004000
kccsed_rbl()+174     CALL???  kccbmp_get()         000017E28 015A67E14 015592200
                                                   000000001
kccocx()+1399        CALL???  kccsed_rbl()         100000010 100000001 0000354D8
                                                   000035508
kccocf()+167         CALL???  kccocx()+528         016303990 000000000
                                                   7FF00000001 000000000
kcfcmb()+1254        CALL???  kccocf()             000000000 000000000 000000000
                                                   000000000
kcfmdb()+69          CALL???  kcfcmb()             000000000 7FF59FFF856
                                                   000000007 7FE00000000
adbdrv_options()+43  CALL???  kcfmdb()             0163083E0 14903FF2C 000000005
724                                                000000000
adbdrv()+149         CALL???  adbdrv_options()     000000000 000000000 0163084A0
                                                   851F2CC90B75
opiexe()+22668       CALL???  adbdrv()             7FF00000023 000000003
                                                   000000000 016309380
opiosq0()+6009       CALL???  opiexe()             000000000 000000000 016309990
                                                   000000000
kpooprx()+410        CALL???  opiosq0()            000000003 000000000 000000000
                                                   0000000A4
kpoal8()+994         CALL???  kpooprx()            0146A57FC 000000001 0146A5820
                                                   000000001
opiodr()+1601        CALL???  kpoal8()             000000000 015523288 015523270
                                                   0159FCDD0
ttcpip()+1223        CALL???  opiodr()             7FE0000005E 00000001F
                                                   01630DA20 7FE00000000
opitsk()+2160        CALL???  ttcpip()             0146C0690 000000000 000000000
                                                   000000000
opiino()+1079        CALL???  opitsk()             000000007 000000000 01630F200
                                                   01630E970
opiodr()+1601        CALL???  opiino()             00000003C 000000000 01630F470
                                                   000000000
opidrv()+842         CALL???  opiodr()             00000003C 000000004 01630F470
                                                   000000000
sou2o()+94           CALL???  opidrv()+156         10000003C 7FE00000004
                                                   01630F470 0154E6A30
opimai_real()+276    CALL???  sou2o()              1D4851F4C467583 00A9D55E0
                                                   8001A000B07E2 1004B0039001E
opimai()+170         CALL???  opimai_real()        000000000 851F2CB1B179
                                                   00A9D55E0 01630F628
OracleThreadStart()  CALL???  opimai()             000000000 149031F90 000000050
+713                                               0000005C8
00000000775259CD     CALL???  OracleThreadStart()  000000000 000000000 000000000
                                                   000000000
000000007765A561     CALL???  00000000775259C0     000000000 000000000 000000000
                                                   000000000
 

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

这个错误和以往版本中的kccpb_sanity_check_2比较类似,由于数据库异常关闭导致ctl写丢失导致
ORA-600 2131/kccpb_sanity_check_2解释

DESCRIPTION:

  This internal error is raised when the sequence number (seq#) of the
  current block of the controlfile is greater than the seq# in the controlfile header.

  The header value should always be equal to, or greater than the value
  held in the control file block(s).

  This extra check was introduced in Oracle 10gR2 to detect lost writes
  or stale reads to the header.

ARGUMENTS:
  Arg [a] seq# in control block header.
  Arg [b] seq# in the control file header.
  Arg 1 

FUNCTIONALITY:
  Kernel Cache layer Control file component.
发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 , , | 留下评论

文件系统损坏导致数据文件异常恢复

今天接到一个客户的服务请求,由于服务器被强制重启,数据库无法启动
ORA-1200报错
ora-1200


这是一个常见的ORA-1200错误,但是文件大小相差的有离谱实际大小729600个block,但是现在只有149760个block.不像是一般主机重启导致的数据库异常.通过再次咨询客户实际是什么情况,客户那边如实描述:三个磁盘的raid 5由于两个盘掉线,后来使用最后一个好的盘和最后掉线的盘,强制拉起raid,系统启动通过长时间的自检之后,就是出现这样的情况.通过进一步检查发现,发现大多数数据文件异常.
文件系统中数据文件截图
dbf-fs

通过查询数据库确定文件大小情况
df_header_check

对比文件系统中的文件和数据库查询结果,可以发现绿色框中的文件全部大于文件系统中文件,蓝色框中的文件在文件系统中丢失.对于这样的情况,由于被强制online的磁盘中有坏道,导致文件系统损坏,从而出现大量文件大小异常甚至文件丢失;或者是由于选择错了上线的磁盘出现该问题(通过分析存在的文件,判断确定上线的盘没有问题),那就是文件系统故障导致.
底层重组恢复
解决该问题的方法是通过底层block恢复,重组oracle数据文件,并且检查恢复文件坏块情况.参考:Oracle 数据文件大小为0kb或者文件丢失恢复
scan-disk

分析文件坏块原因
block-had

由于文件所在block被覆盖或者磁盘坏道导致这部分block直接被空块填充.
这个客户比较幸运,直接open数据库成功,坏坏块进行分析判断业务表空间数据文件的坏块全部为index,业务数据0丢失.

发表在 非常规恢复 | 标签为 , , , , | 留下评论