巧遇ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr]

下午开发人员说测试数据库起不来了, 呵呵 开发和测试数据库,我向来不管的,反正JAVA工程师是万能,啥都会!
问他是咋回事呢? 他说是停电了, 欺负我书读的少呢? 上午还给你修改了数据库占用内存的大小.下午整个办公司都没停过电啊? 他也支支吾吾下说不清楚. 我也难去理他.
数据库是安装在VMWARE虚拟机上的. 这个虚拟机又是安装在WIN7上面的,WIN7安装在高档的PC电脑上的.
情况如下
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 8551575552 bytes
Fixed Size 2270360 bytes
Variable Size 1560284008 bytes
Database Buffers 6962544640 bytes
Redo Buffers 26476544 bytes
数据库装载完毕。
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [5203], [1150], [1158], [], [], [], [], [], [], []

SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> exit
[root@svr3 ~]# vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3466.trc

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3466.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db1
System name: Linux
Node name: svr3
Release: 2.6.32-358.el6.x86_64
Version: #1 SMP Fri Feb 22 00:31:26 UTC 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 3466, image: oracle@svr3 (TNS V1-V3)

* 2016-09-05 15:39:54.043
* SESSION ID:(583.5) 2016-09-05 15:39:54.043
* CLIENT ID:() 2016-09-05 15:39:54.043
* SERVICE NAME:() 2016-09-05 15:39:54.043
* MODULE NAME:(sqlplus@svr3 (TNS V1-V3)) 2016-09-05 15:39:54.043
* ACTION NAME:() 2016-09-05 15:39:54.043

Successfully allocated 3 recovery slaves
Using 45 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 5203, block 2, scn 370316938
cache-low rba: logseq 5203, block 341
on-disk rba: logseq 5203, block 1158, scn 370318507
start recovery at logseq 5203, block 341, scn 0

* 2016-09-05 15:39:54.303
Started writing zeroblks thread 1 seq 5203 blocks 1150-1157

* 2016-09-05 15:39:54.305
Completed writing zeroblks thread 1 seq 5203
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
– Redo read_disk statistics –
Read rate (ASYNC): 404Kb in 0.18s => 2.19 Mb/sec
Longest record: 4Kb, moves: 0/1120 (0%)
Change moves: 0/27 (0%), moved: 0Mb
Longest LWN: 8Kb, moves: 0/231 (0%), moved: 0Mb

Last redo scn: 0x0000.16129c9d (370318493)

—– Recovery Hash Table Statistics ———
Hash table buckets = 262144
Longest hash chain = 1
Average hash chain = 202/202 = 1.0
Max compares per lookup = 1

Avg compares per lookup = 2035/2268 = 0.9

WARNING! Crash recovery of thread 1 seq 5203 is
ending at redo block 1150 but should not have ended before
redo block 1158
Incident 195756 created, dump file: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_195756/orcl_ora_3466_i195756.trc
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [5203], [1150], [1158], [], [], [], [], [], [], []

ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [5203], [1150], [1158], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [5203], [1150], [1158], [], [], [], [], [], [], []

仔细阅读下 看到是日志线程1 日志序列号5203,日志块1150到1158无法恢复. 操大爷的事情日志被损坏了.
这可要了小命啊! ORACLE完全靠日志来保证数据一致性,做的到数据不丢失一笔. 这才是它的核心价值.
为什么很多公司的交易系统,支付系统,库存系统都不敢使用MYSQL 呢? 虽然MYSQL很火爆,会MYSQL的DBA月薪3万.

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

Total System Global Area 8551575552 bytes
Fixed Size 2270360 bytes
Variable Size 1560284008 bytes
Database Buffers 6962544640 bytes
Redo Buffers 26476544 bytes
数据库装载完毕。
SQL> alter session set nls_date_format=’yyyy/mm/dd hh24:mi:ss’;

会话已更改。

已用时间: 00: 00: 00.00
SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME

---

 1      1       5203   52428800    512      1 NO  CURRENT          370316937 2016/09/05 14:30:15   2.8147E+14
 3      1       5202   52428800    512      1 NO  INACTIVE         370188161 2016/09/02 14:29:47    370316937 2016/09/05 14:30:15
 2      1       5201   52428800    512      1 NO  INACTIVE         370079147 2016/08/30 18:54:37    370188161 2016/09/02 14:29:47

已用时间: 00: 00: 00.04

MOUNT状态下看到日志1 处于当前状态下. 还是恢复先.

SQL> recover database until cancel using backup controlfile;
ORA-00279: 更改 370316938 (在 09/05/2016 14:30:15 生成) 对于线程 1 是必需的 ORA-00289:
建议: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_09_05/o1_mf_1_5203_%u_.arc
ORA-00280: 更改 370316938 (用于线程 1) 在序列 #5203 中

指定日志: {=suggested | filename | AUTO | CANCEL}
输入: /u01/app/oracle/oradata/orcl/redo01.log

已应用的日志。
完成介质恢复。
SQL> alter database open resetlogs;

数据库已更改。

已用时间: 00: 00: 13.52

是什么情况下造成日志写失败或者损坏呢?
1 LGWR正在写IO的时候 ,这个时候停电了.大约写了50%
那么恢复进行介质恢复到50%就可以了,毕竟磁盘保留了这部分日志
2 就是开启了异步写.LGWR 发起写日志IO请求, OS告诉它 写好了, 实际上它还没有写.或者正在写.
那么日志写完的标记已经写在控制文件中了,实际上日志文件这部分日志没有写完,就断电了.造成了日志丢失.

检查下参数
SQL> show parameter filesystem

NAME TYPE VALUE

---

filesystemio_options string SETALL

这个参数值设置意思说直接IO并且是异步IO

那好吧 把它修改回来 修改成直接IO 速度和安全兼顾
SQL> alter system set filesystemio_options =DIRECTIO scope=spfile;

搞定后继续打破沙锅问到底 JAVA开发工程师说 “服务器负荷太高，电脑电源自动断电的
”
欢迎大家关注我的公众号, 发布些 技术 投资 养生 和思想方面的.