作者归档：惜分飞

注意系统bug—linux在E5、E5 V2、E7 V2 cpu之上的bug 765720

发表于 2015 年 3 月 13 日 由 惜分飞

今天晚上群里面兄弟说了一个linux 6上面bug,会导致系统在运行200天以上(hardware uptime),然后进行热重启后,可能在几分钟或者几个小时内出发该bug,导致系统异常.

主要影响条件为:
Red Hat Enterprise Linux 6.1 (kernel-2.6.32-131.26.1.el6 and newer)
Red Hat Enterprise Linux 6.2 (kernel-2.6.32-220.4.2.el6 and newer)
Red Hat Enterprise Linux 6.3 (kernel-2.6.32-279 series)
Red Hat Enterprise Linux 6.4 (kernel-2.6.32-358 series)
Any Intel® Xeon® E5, Intel® Xeon® E5 v2, or Intel® Xeon® E7 v2 series processor
从这里可以看出来该问题主要影响E5、E5 V2、E7 V2 cpu上的redhat 6.1-6.4版本,在6.5版本中修复,具体参考:bug 765720
另外对已ORACLE Linux,如果使用EL Kernel影响和redhat一致,如果使用Unbreakable Enterprise Kernel则在6.2版本中进行了修复该问题。
MOS上类似文章：Oracle Linux 6 RHCK system hang: processes blocked in ext4_file_open(), pick_next_task_fair()

补充说明：
1. 在Red Hat/OEL 5.x版本中不存在。
2. 在32和64位操作系统都有可能发生
3. 鉴于该bug短期内无法修复,而且真的发生了,考虑冷重启主机,临时规避

再次提醒:系统版本选定也很重要,大家在选择Linux版本之时尽量选择避开该bug(el kernel 6.5及其以后版本,uek kernel 6.2及其以后版本)。个人倾向：如果是部署ORACLE db,而且还是redhat系列Linux,更加倾向OEL(省事,相信Oracle)

发表在 Linux | 评论关闭

ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能

发表于 2015 年 3 月 10 日 由 惜分飞

在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能,和mysql,sql server类似功能.
使用语法
ORACLE 12C IDENTITY
GENERATED ALWAYS AS IDENTITY方式测试

C:\Users\ffcheng>sqlplus chf/xifenfei@pdb

SQL*Plus: Release 12.1.0.2.0 Production on 星期二 3月 10 14:34:46 2015

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

上次成功登录时间: 星期五 12月 19 2014 21:00:26 +08:00

连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

XFF_PDB@CHF> select * from v$version;

BANNER                                                 CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.          0
1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production                      0
CORE    12.1.0.2.0      Production                          0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Produ          0
ction

NLSRTL Version 12.1.0.2.0 - Production                      0

XFF_PDB@CHF>  create table t_xifenfei (id number GENERATED ALWAYS AS IDENTITY,na
me varchar2(100));

表已创建。

XFF_PDB@CHF> select object_name,object_type from user_objects;

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
ISEQ$$_91982    SEQUENCE
T_XIFENFEI      TABLE

XFF_PDB@CHF> set long 10000
XFF_PDB@CHF> select dbms_metadata.get_ddl('TABLE','T_XIFENFEI') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_XIFENFEI')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF"."T_XIFENFEI"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE
   9999999999999999999999999999 INCREMENT BY 1 START WITH 1 
   CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
        "NAME" VARCHAR2(100)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "INMEMORY"

XFF_PDB@CHF> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM');
INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM')
*
第 1 行出现错误:
ORA-32795: 无法插入到“始终生成”身份列


XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFNEFEI.COM');

已创建 1 行。

XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.orasos.COM');

已创建 1 行。

XFF_PDB@CHF> commit;

提交完成。

XFF_PDB@CHF> col name for a30
XFF_PDB@CHF> select * from t_xifenfei;

        ID NAME
---------- ------------------------------
         1 WWW.XIFNEFEI.COM
         2 WWW.orasos.COM

XFF_PDB@CHF> update t_xifenfei set id=3 where id=2;
update t_xifenfei set id=3 where id=2
                      *
第 1 行出现错误:
ORA-32796: 无法更新“始终生成”身份列

XFF_PDB@CHF> delete from t_xifenfei where id=1;

已删除 1 行。

XFF_PDB@CHF> commit;

提交完成。

XFF_PDB@CHF> select ISEQ$$_91982.nextval from dual;

   NEXTVAL
----------
         3

XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.ORASOS.COM');

已创建 1 行。

XFF_PDB@CHF> commit;

提交完成。

XFF_PDB@CHF> select * from t_xifenfei;

        ID NAME
---------- ------------------------------
         2 WWW.orasos.COM
         4 WWW.ORASOS.COM

XFF_PDB@CHF> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1
2';

会话已更改。

XFF_PDB@CHF>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------

D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_10628.trc

XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM');

已创建 1 行。

XFF_PDB@CHF> COMMIT;

提交完成。

--跟踪trace文件
PARSING IN CURSOR #688719640 len=55 dep=0 uid=103 oct=2 lid=103 tim=15129490112 hv=961646460 
ad='7ff05d11a18' sqlid='21uzyjhwp33vw'
INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM')
END OF STMT
PARSE #688719640:c=15600,e=18909,p=0,cr=44,cu=0,mis=1,r=0,dep=0,og=1,plh=2541165129,tim=15129490112
EXEC #688719640:c=0,e=347,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=2541165129,tim=15129490731
STAT #688719640 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  T_XIFENFEI (cr=1 pr=0 pw=0 time=296 us)'
STAT #688719640 id=2 cnt=1 pid=1 pos=1 obj=91983 op='SEQUENCE  ISEQ$$_91982 (cr=0 pr=0 pw=0 time=89 us)'
WAIT #688719640: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=15129490971

XFF_PDB@CHF> alter table t_xifenfei2 modify(id number GENERATED ALWAYS AS IDENTI
TY MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE  100);

表已更改。

XFF_PDB@CHF> drop sequence ISEQ$$_91982;
drop sequence ISEQ$$_91982
              *
第 1 行出现错误:
ORA-32794: 无法删除系统生成的序列

XFF_PDB@CHF> drop table t_xifenfei;

表已删除。

XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE

XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE

XFF_PDB@CHF> purge table t_xifenfei;

表已清除。

XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';

未选定行

这里的出来几个结论：
1. GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值
2. GENERATED IDENTITY 本质也是通过sequence实现
3. GENERATED IDENTITY 中sequence不能单独被删除
4. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除
5. GENERATED IDENTITY 中的sequence可以通过select 语句查询
6. 通过alert table 语句来修改GENERATED IDENTITY 的sequence相关值

GENERATED BY DEFAULT AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei2 (id number GENERATED BY DEFAULT AS IDENTI
TY,name varchar2(100)) tablespace users;

表已创建。

XFF_PDB@CHF> insert into t_xifenfei2 values (1,'www.xifenfei.com');

已创建 1 行。

XFF_PDB@CHF> insert into t_xifenfei2(name) values ('www.orasos.com');

已创建 1 行。

XFF_PDB@CHF> col name for a20
XFF_PDB@CHF> select * from t_xifenfei2;

        ID NAME
---------- --------------------
         1 www.xifenfei.com
         1 www.orasos.com

XFF_PDB@CHF> insert into t_xifenfei2 values (null,'www.xifenfei.com');
insert into t_xifenfei2 values (null,'www.xifenfei.com')
                                *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("CHF"."T_XIFENFEI2"."ID")


XFF_PDB@CHF> desc t_xifenfei2
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------

 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

得出结论：
1. GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值
2. GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入

GENERATED BY DEFAULT ON NULL AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei3 (id number GENERATED BY DEFAULT on null A
S IDENTITY,name varchar2(100)) tablespace users;

表已创建。

XFF_PDB@CHF> desc t_xifenfei3
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

XFF_PDB@CHF> insert into t_xifenfei3 values (null,'www.xifenfei.com');

已创建 1 行。

XFF_PDB@CHF> select * from t_xifenfei3;

        ID NAME
---------- --------------------
         1 www.xifenfei.com

测试结论：GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值，只是默认转换为对应的sequence值

传统自增长列实现方法

XFF_PDB@CHF> create table t_xifenfei4 (id number,name varchar2(100)) tablespace
users;

表已创建。

XFF_PDB@CHF> create sequence xff_sequence
  2  increment by 1
  3  minvalue 1
  4  nomaxvalue
  5  start with 1
  6  cache 20
  7  order;

序列已创建。

XFF_PDB@CHF> create or replace trigger xifenfei_id
  2  before insert on t_xifenfei4
  3  for each row
  4  begin
  5  select xff_sequence.nextval into :new.id from dual;
  6  end;
  7  /

触发器已创建

XFF_PDB@CHF> insert into t_xifenfei4(name) values('www.xifenfei.com');

已创建 1 行。

XFF_PDB@CHF> select * from t_xifenfei4;

        ID NAME
---------- --------------------
         1 www.xifenfei.com
发表在 ORACLE 12C | 标签为 , | 评论关闭

记录一次由于坏块和不恰当恢复引起各种ORA-600案例

发表于 2015 年 3 月 7 日 由 惜分飞

朋友让我帮忙处理一个不能open的库,打开alert日志一看,傻眼了,里面是各种ORA-600的错误应有尽有,被折腾的够惨
故障后重启,无法启动主要表现在block坏块,引起的各种ORA-600等错误

Mon Mar 02 16:09:27 2015
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
Started redo scan
Completed redo scan
 read 962 KB redo, 256 data blocks need recovery
Started redo application at
 Thread 1: logseq 726, block 37343
Recovery of Online Redo Log: Thread 1 Group 3 Seq 726 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/oa/redo03.log
Mon Mar 02 16:09:27 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 1673 OF FILE 3
Completed redo application of 0.27MB
Mon Mar 02 16:09:27 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 3104 OF FILE 3
Mon Mar 02 16:09:27 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 3613 OF FILE 3
Mon Mar 02 16:09:28 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 272 OF FILE 3
Mon Mar 02 16:09:28 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 2512 OF FILE 3
Hex dump of (file 2, block 92889) in trace file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_dbw2_4158.trc
Corrupt block relative dba: 0x00816ad9 (file 2, block 92889)
Bad header found during preparing block for write
Data in bad block:
 type: 0 format: 0 rdba: 0x6ad90000
 last change scn: 0x0000.00c6a052 seq: 0x1 flg: 0x00
 spare1: 0x6 spare2: 0xa2 spare3: 0x5d7e
 consistency value in tail: 0xa0520001
 check value in block header: 0x0
 block checksum disabled
Mon Mar 02 16:09:28 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p007_4196.trc  (incident=3833):
ORA-00600: internal error code, arguments: [4502], [1], [], [], [], [], [], [], [], [], [], []
Mon Mar 02 16:09:28 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p013_4208.trc  (incident=3881):
ORA-00600: internal error code, arguments: [2037], [4259067], [4244307968], [159], [243], [0], [2162032704], [100728832], [], [], [], []
Slave exiting with ORA-1172 exception
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p009_4200.trc:
ORA-01172: recovery of thread 1 stuck at block 3613 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p001_4184.trc:
ORA-01172: recovery of thread 1 stuck at block 2512 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p021_4224.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p021_4224.trc:
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_dbw2_4158.trc  (incident=3697):
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oa/oa/incident/incdir_3697/oa_dbw2_4158_i3697.trc
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0xD2DDB7, kcbs_shrink_pool()+705] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_mman_4152.trc  (incident=3673):
ORA-07445: exception encountered: core dump [kcbs_shrink_pool()+705] [SIGSEGV] [ADDR:0x0] [PC:0xD2DDB7] [SI_KERNEL(general_protection)] []
Incident details in: /u01/app/oracle/diag/rdbms/oa/oa/incident/incdir_3673/oa_mman_4152_i3673.trc
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_dbw2_4158.trc:
Mon Mar 02 16:09:34 2015
Instance terminated by DBW2, pid = 4158

第二次重启后增加新错误ORA-00600[17182]

Mon Mar 02 16:39:50 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p002_4321.trc  (incident=4993):
ORA-00600: internal error code, arguments: [17182], [0x7F548C2BDBA8], [], [], [], [], [], [], [], [], [], []

进行了一些恢复处理后,日志中报错
主要体现在进行了不完全恢复,而且应该是对redo进行了重命名或者redo头损坏锁引起的一系列提示

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 962 KB redo, 256 data blocks need recovery
Started redo application at
 Thread 1: logseq 726, block 37343
Recovery of Online Redo Log: Thread 1 Group 3 Seq 726 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/oa/redo03.log
RECOVERY OF THREAD 1 STUCK AT BLOCK 1673 OF FILE 3
Aborting crash recovery due to error 1172
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:
ORA-01172: recovery of thread 1 stuck at block 1673 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:
ORA-01172: recovery of thread 1 stuck at block 1673 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: alter  database open...
Tue Mar 03 11:17:59 2015
Sweep [inc][17178]: completed
Sweep [inc][17177]: completed
Sweep [inc2][17178]: completed
Tue Mar 03 11:18:00 2015
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 24 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Tue Mar 03 11:18:06 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_pr00_6701.trc:
ORA-00266: name of archived log file needed
ORA-266 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_pr00_6701.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/oa/system01.dbf'
Slave exiting with ORA-1547 exception
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_pr00_6701.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/oa/system01.dbf'
ORA-10879 signalled during: ALTER DATABASE RECOVER CANCEL ...
Tue Mar 03 11:18:06 2015
Checker run found 4 new persistent data failures
Tue Mar 03 11:18:13 2015
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 12986989
Resetting resetlogs activation ID 3278679642 (0xc36cae5a)
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oa/redo01.log'
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:

再一步折腾,增加了_allow_resetlogs_corruption= TRUE之后数据库报ORA-600[2662]

Tue Mar 03 11:19:26 2015
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6864.trc  (incident=18195):
ORA-00600: internal error code, arguments: [2662], [0], [13007002], [0], [13016626], [4194545], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oa/oa/incident/incdir_18195/oa_ora_6864_i18195.trc
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6864.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [13007002], [0], [13016626], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 6864): terminating the instance due to error 704
Instance terminated by USER, pid = 6864
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (6864) as a result of ORA-1092
Tue Mar 03 11:19:29 2015
ORA-1092 : opitsk aborting process

进一步折腾,可以看出来undo已经被其offline,无法正常访问,导致系统报ORA-704和ORA-00376

Wed Mar 04 21:10:58 2015
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17074.trc:
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/app/oracle/oradata/oa/undotbs01.dbf'
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17074.trc:
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/app/oracle/oradata/oa/undotbs01.dbf'
Error 704 happened during db open, shutting down database
USER (ospid: 17074): terminating the instance due to error 704
Instance terminated by USER, pid = 17074
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (17074) as a result of ORA-1092
Wed Mar 04 21:11:00 2015
ORA-1092 : opitsk aborting process

通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检测结果见附件(xifenfei_db_recover_20150304),这里可以知道undo 不知道怎么折腾的数据文件scn较大而且还offline,
通过一些列方法(bbed,隐含参数等)调整数据库scn,强制启动数据库,报如下错误

Wed Mar 04 22:50:23 2015
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 3nkd3g3ju5ph1, SCN: 0x0000.4000003e):
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 17807): terminating the instance due to error 704
Instance terminated by USER, pid = 17807
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (17807) as a result of ORA-1092

根据经验,该错误怀疑是文件头scn不够大,块延迟清理导致,进一步增加scn尝试,最后依旧是ORA-00704/ORA-00604/ORA-01555错误

Wed Mar 04 22:50:23 2015
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 3nkd3g3ju5ph1, SCN: 0x0000.4000003e):
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 17807): terminating the instance due to error 704
Instance terminated by USER, pid = 17807
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (17807) as a result of ORA-1092

根据经验,在scn上做手脚估计难以解决给问题,对其启动过程做10046和errorstack分析发现

PARSING IN CURSOR #3 len=202 dep=2 uid=0 oct=3 lid=0 tim=1425481940448439 hv=3819099649 ad='64ff91af8' sqlid='3nkd3g3ju5ph1'
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #3:c=1000,e=334,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=1425481940448439
BINDS #3:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f5b3253a6f0  bln=22  avl=01  flg=05
  value=0
 Bind#1
  oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7f5b3253a6b8  bln=32  avl=06  flg=05
  value="PROPS$"
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f5b3253a688  bln=24  avl=02  flg=05
  value=1
EXEC #3:c=0,e=640,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=2853959010,tim=1425481940449147
WAIT #3: nam='db file sequential read' ela= 5 file#=1 block#=345 blocks=1 obj#=37 tim=1425481940449186
WAIT #3: nam='db file sequential read' ela= 4 file#=1 block#=44528 blocks=1 obj#=37 tim=1425481940449221
WAIT #3: nam='db file sequential read' ela= 3 file#=1 block#=5505 blocks=1 obj#=37 tim=1425481940449247

*** 2015-03-04 23:12:20.450
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
----- Current SQL Statement for this session (sql_id=g64r07v2jn8nq) -----
SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR'

这里可以发现是数据库在启动的过程中需要执行SELECT NULL FROM PROPS$ WHERE NAME=’BOOTSTRAP_UPGRADE_ERROR’语句，而该语句递归调用了select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null 语句。既然这样通过一些方法避免数据库启动之时查询SELECT NULL FROM PROPS$ WHERE NAME=’BOOTSTRAP_UPGRADE_ERROR’语句，果然数据库启动成功。

知识点补充
ORA-600 [4502] [a] 

Arg [a] ITL entry with a lock count
Meaning: During ITL cleanout we clear all row locks but the ITL entry
	 still thinks there is an uncleared lock. Ie: ITL has a locked
	 row but there are no locked rows in the block

大体意思是数据库发现undo 的itl已经被清除，但是block中的itl依然存在，从而出现ORA-600[4502],引起该问题除bug外主要原因是坏块

ORA-600 [2037] [a] [b] {c} [d] [e] [f] [g]

Arg [a] Relative Data Block Address (RDBA) that the redo vector is for
Arg [b] The Block format  
Arg {c} RDBA in the block itself
Arg [d] The block type
Arg [e] The sequence number
Arg [f] Flags, if set  
Arg [g] The return value from the block head/tail checker.
DESCRIPTION:
  During recovery we are examining a block to ensure that it is not
  corrupt prior to applying any change vectors.
  The block has failed this check and this exception is raised

大体意思是在恢复过程中，正在检查的块，以确保它在应用任何变化向量之前不损坏。如果检查失败排除该异常ORA-600[2037],引起该问题除bug外主要原因是坏块

ORA-600 [kcbzpbuf_1],[a],[b]

Arg [a] Corruption reason
Arg [b] Calculate checksum flag

Corruption reason:
#define KCBH_GOOD    0                                     /* block is valid */
#define KCBH_ZERO    1             /* block header was entirely zero on disk */
#define KCBH_BROKEN  2      /* corruption could be from a partial disk write */
#define KCBH_CHKVAL  3               /* The check value for the block failed */
#define KCBH_CORRUPT 4     /* this is the wrong block or is not a data block */
#define KCBH_ZERONG  5               /* all zero block and it is not allowed */

Calculate checksum flag:
The possible values are 1 (Generate Checksum - db_block_checksum is enabled - default value) 
                        0 (do not generate checksum - db_block_checksum=false)

kcbzpbuf_1是该错误的源码函数

ORA-600 [17182] [a] [b] {c} [d] [e]

DESCRIPTION:        
  Oracle has detected that the magic number in a memory chunk header has been overwritten.
  This is a heap (in memory) corruption and there is no underlying data corruption.
  The error may occur in the one of the process specific heaps 
  (the Call heap, PGA heap, or session heap) or in the shared heap (SGA).

ORACLE 发现在内存中重要的块头被重新,但是没有基础数据损坏,大部分和数据块或者内存损坏有关系.

ORA-600 [4552] [a] [b] {c} [d] [e]

DESCRIPTION:
  This assertion is raised because we are trying to unlock the rows in a 
  block, but receive an incorrect block type.
  The second argument is the block type received.

ORACLE尝试对某行进行解锁但是接收到了不正确的数据块类型,Arg [b]是接收到的数据块类型

ORA-600 [2662] [a] [b] {c} [d] [e]

DESCRIPTION:

  A data block SCN is ahead of the current SCN.

  The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN 
  stored in a UGA variable.

  If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
  internal error.

ARGUMENTS:
  Arg [a]  Current SCN WRAP
  Arg [b]  Current SCN BASE
  Arg {c}  dependent SCN WRAP
  Arg [d]  dependent SCN BASE 
  Arg [e]  Where present this is the DBA where the dependent SCN came from.

主要的含义就是oracle文件头scn比某个block dependent scn小从而出现该问题

发表在 Oracle备份恢复 | 标签为 , , , , , , , , | 评论关闭