Category Archives: Oracle

ORA-704 ORA-604 ORA-942 恢复

Posted on by

接到客户请求,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

ALERT: Database Corruption ORA-600 ORA-7445 errors after applying AIX SP patches – AIX 6.1.9.8 or AIX 7.1.3.8 or AIX 7.1.4.3 or AIX 7.2.0.3 or AIX 7.2.1.0, 01

Posted on by

APPLIES TO:

Oracle Database – Enterprise Edition – Version 11.2.0.3 to 12.2.0.1 [Release 11.2 to 12.2]
IBM AIX on POWER Systems (64-bit)
A problem has been discovered in the latest SP patches for IBM AIX 6.1 and 7.1 (SP 08 and SP 03) where 11.2.0.3, 11.2.0.4, or 12.1 or 12.2 are running. ORA-600 errors and possible database corruption.

upgrade from AIX 6.1.9.7 to SP08
upgrade from AIX 7.1.4.2 to SP03
or running on one of the oslevels listed below in this note.

This is only known to impact Oracle 11.2.0.3.x, 11.2.0.4.x, 12.1.0.2, or 12.2.0.1 on AIX platforms. It has been observed on various Oracle PSU versions.
The symptoms observed so far are ORA-600 memory related failures with examples below.
Additionally, Redo log corruption has been observed in at least two cases.

DESCRIPTION

Database Corruption and/or ORA-600 ORA-7445 errors after applying IBM AIX SP patches – After update from AIX 6.1.9.7 to SP08 or AIX 7.1.4.2 to SP03 (note the earlier service packs (SP 07 or SP 02 are not impacted)

OCCURRENCE

The only changes were upgrades to the latest IBM SP patches.

upgraded from AIX 6.1.9.7 to SP08  –> SP08 has the problem.
upgraded from AIX 7.1.4.2 to SP03  –> SP03 has the problem.

To check for AIX patch levels that are exposed to this risk, run the following command and look for any of the following:

# oslevel -s

If any of the following are listed, exposure to this problem exists:

6100-09-08
7100-03-08
7100-04-03
7200-00-03
7200-01-00
7200-01-01

SYMPTOMS

The following ORA-600 errors have been observed. Note that not all errors are needed, and not all customers have seen all these errors.

=========================================================================================
ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], [0], [0], [], [], [], [], [], [], [], [], []
Optimizer – Maps the structures from memory
=========================================================================================
ORA-00600: internal error code, arguments: [kghssgai2], [1], [32], [], [], [], [], [], [], [], [], []
–looks to be pga related allocations
Generic memory Heap manager -we can’t have both a heap and an allocation function passed in to us
=========================================================================================
ORA-00600: internal error code, arguments: [qkkAssignKey:1], [], [], [], [], [], [], [], [], [], [], []
qkkAssignKey – copy keys from source to destination key
=========================================================================================
ORA-00600: internal error code, arguments: [kclgclks_3], [454], [2431642561], [], [], [], [], [], [], [], [], []
kclgclks – CR Server request
=========================================================================================
ORA-00600: internal error code, arguments: [kkqvmRmViewFromLst1], [], [], [], [], [], [], [], [], [], [], []
View Merging – list management
=========================================================================================
ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x082024000], [rpi role space], [], [], [], [], [], [], [], [], []
shared heap manager Stack segment underflow, failure to follow stack discipline.
assert no previous chunk in this segment
=========================================================================================
ORA-00600: internal error code, arguments: [qerghFetch.y], [], [], [], [], [], [], [], [], [], [], []
Implements hash aggregation for query source
=========================================================================================
ORA-00600: internal error code, arguments: [qeshQBNextLoad.1], [], [], [], [], [], [], [], [], [], [], []
Hash Table Infrastructure -get Next buffer during Load
=========================================================================================
ORA-00600: internal error code, arguments: [qkshtQBGet:1], [], [], [], [], [], [], [], [], [], [], []
gets memory pointer for a query block.
Make sure the query block pointer is not NULL
=========================================================================================
ORA-00600: internal error code, arguments: [qeshIHBuildOnPartition block missed], [], [], [], [], [], [], [], [], [], [], []
Hash Table Infrastructure
update the partition at the end.
=========================================================================================
ORA-00600: internal error code, arguments: [kghssgfr2], [1]
=========================================================================================
ORA-07445: exception encountered: core dump [PC:0x0] [SIGILL] [ADDR:0x0] [PC:0x0] [Illegal opcode]
=========================================================================================
ORA-00600 [kkogbro: no kkoaptyp]
=========================================================================================
ORA-00600: internal error code, arguments: [kewrose_1], [600]
========================================================================================
ORA-00600: internal error code, arguments: [1868], [0x000000000], [], [], [], [], [], [], [], [], [], []
Core dumps are also possible.

—————

Redo log corruption with checksum error has also been observed.

Two known examples below:

example 1:

Alert.log messages:

ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 73804 change 8112409541614 time 12/07/2016 07:12:25
ORA-00334: archived log: ‘/dev/rredo13′
ORA-07445: exception encountered: core dump [pkrdi()+780] [SIGSEGV] [ADDR:0x0] [PC:0x10367B26C] [Invalid permissions for mapped

—————

There have been also transient database block corruptions or control file block corruption with checksum errors in the database where a reread finds valid data.

example 2 (transient database block corruption with checksum error):

Corrupt block relative dba: 0x5a066b2f (file 360, block 420655)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x5a066b2f
last change scn: 0x00cc.6a826294 seq: 0×1 flg: 0×06
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0×62940601
check value in block header: 0x9e7d
computed block checksum: 0×0           —> 0×0 means that checksum is good when printing the error message (transient problem)
Reading datafile ‘Datafile name’ for corruption at rdba: 0x5a066b2f (file 360, block 420655)
Reread (file 360, block 420655) found valid data
Hex dump of (file 360, block 420655) in trace file ….
Repaired corruption at (file 360, block 420655)

example 3 (transient control file corruption with checksum error):

Hex dump of (file 0, block 1) in trace file …
Corrupt block relative dba: 0×00000001 (file 0, block 1)
Bad check value found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0×00000001
last change scn: 0×0000.00000000 seq: 0×1 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0×00001501
check value in block header: 0xca35
computed block checksum: 0×0                 —> 0×0 means that checksum is good when printing the error message (transient problem)
Errors in file ..:
ORA-00202: control file: ‘/oracle/dbs/control_01.ctl’
Errors in file …
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: ‘/oracle/dbs/control_01.ctl’

WORKAROUND

There is no workaround to avoid the problem, but if log corruption is encountered, one possible workaround is to clear the unarchived redo log.  The fix is to rollback the IBM SP or apply the updated fixes.

Syntax to clear logfile:

alter database clear <unarchived> logfile group <integer>;
alter database clear <unarchived> logfile ‘<filename>’;

PATCHES

The fix is now ready from IBM

It can be downloaded for the above releases via:

ftp://aix.software.ibm.com/aix/ifixes/

Affected AIX Levels     Fixed In           iFix / APAR (ftp://aix.software.ibm.com/aix/ifixes/)
6100-09-08               6100-09-09      IV93840
7100-03-08               7100-03-09      IV93884
7100-04-03               7100-04-04      IV93845
7200-00-03               7200-00-04      IV93883
7200-01-01               7200-01-02      IV93885

The fix is included in the next to be released AIX Service Packs.

IBM HIPER APAR
Abstract: PROBLEMS CAN OCCUR WITH THREAD_CPUTIME AND THREAD_CPUTIME_FAST

This APAR corrects an issue with system call thread_cputime_self with floating point registers which is exposed by Oracle Database 11gR2.

PROBLEM SUMMARY:
The thread_cputime or thread_cputime_fast interfaces can
cause invalid data in the FP/VMX/VSX registers if the thread
page faults in this function

For more information see the following from IBM:

http://www-01.ibm.com/support/docview.wss?uid=isg1SSRVPOAIX71HIPER170303-1247

参考：ALERT: Database Corruption ORA-600 ORA-7445 errors after applying AIX SP patches – AIX 6.1.9.8 or AIX 7.1.3.8 or AIX 7.1.4.3 or AIX 7.2.0.3 or AIX 7.2.1.0, 01 (Doc ID 2237498.1)

ORA-600 [kcrfr_update_nab_2]恢复支持

Posted on by

APPLIES TO:

Oracle Database – Enterprise Edition – Version 10.2.0.2 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition – Version: 10.2.0.2 to 10.2.0.4

SYMPTOMS

After Database Crashing or Shutdown abort we are unable to open database, in the alert.log we find the error   ORA-600[kcrfr_update_nab_2]

==> In the alert.log we can see that database crashes while performing instance recovery:

Tue Oct 07 13:30:28 2008
Starting ORACLE instance (normal)
..
ALTER DATABASE OPEN
Tue Oct 07 13:30:39 2008
Beginning crash recovery of 1 threads
Tue Oct 07 13:30:39 2008
Started redo scan
Tue Oct 07 13:30:41 2008
Errors in file ….ORCL_ora_3148.trc:
ORA-00600: internal error code, arguments: [kcrfr_update_nab_2], [0x3C2C5CD0], [2], [], [], []
Tue Oct 07 13:30:46 2008
Aborting crash recovery due to error 600

==> In the trace file we can see the following error stack

start recovery at logseq 18989, block 1312, scn 0
—

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kcrfr_update_nab_2], [0x3C2C5CD0], [2], [], [], [],
Current SQL statement for this session:
alter database open
—– Call Stack Trace:
ksedst <- ksedmp <- ksfdmp <- kgerinv <- kgeasnmierr
<- kcrfr_update_nab <- kcrfr_read <- kcrfr_read_buffer <- kcrfrgv <- kcratr1
<- kcratr <- kctrec <- kcvcrv <- kcfopd <- adbdrv
<- opiexe <- opiosq0 <- kpooprx <- kpoal8 <- opiodr
<- ttcpip <- opitsk <- opiino <- opiodr <- opidrv
<- sou2o <- opimai_real <- opimai <- OracleThreadStart@

CAUSE

This issue has been reported in following bugs:

Bug 5692594
Hdr: 5692594 10.2.0.1 RDBMS 10.2.0.1 RECOVERY PRODID-5 PORTID-226 ORA-600
Abstract: AFTER DATABASE CRASHED DOESN’T OPEN ORA-600 [KCRFR_UPDATE_NAB_2]
Status: 95,Closed, Vendor OS Problem
 

Bug 6655116
Hdr: 6655116 10.2.0.3 RDBMS 10.2.0.3 RECOVERY PRODID-5 PORTID-23
Abstract: INSTANCES CRASH WITH ORA-600 [KCRFR_UPDATE_NAB_2] AFTER DISK FAILURE

Status: 95,Closed, Vendor OS Problem

EXPLANATION
The assert ORA-600: [kcrfr_update_nab_2] is a direct result of a lost write   in the current on line log that we are attempting to resolve.   So, this confirms the theory that this is a OS/hardware   lost write issue not an internal oracle bug. In fact the assert  ORA-600: [kcrfr_update_nab_2] is how we detect a lost log write.

SOLUTION

There are some bugs that match with this issue and all have been closed as Vendor OS/problem.
The error is caused by a corruption in the on line redo-log, probably a lost write in the file.

The best solution in this case is to restore database from backup and recover it until the sequence before the current on line redo-log

 

这类问题，由于写丢失无法直接open成功，如果需要，可以联系我们，提供专业ORACLE数据库恢复技术支持
Phone:13429648788    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

ORA-600 [kcrfrgv_nextlwn_scn]

Posted on by

有朋友公司数据库由于_use_single_log_writer参数,导致 异常断电之后,数据库无法启动,报ORA-600 [kcrfrgv_nextlwn_scn]错误，通过分析是由于该参数导致.

DESCRIPTION

Oracle 12c introduces a new default feature of using multiple LGWRs which may lead to DEADLOCK / Database Hang or ORA-742 “Log read detects lost write” or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery on IBM AIX and potentially on HPUX Itanium 64bit.

The database may become unusable and fail to be OPEN.

OCCURRENCE

This issue is specific to RDBMS version 12c (12.1.0.1 or 12.1.0.2) where the new default feature of using multiple LGWRs is introduced.

It affects databases on IBM AIX and potentially on HPUX Itanium 64bit

SYMPTOMS

ORACLE on IBM AIX or HPUX Itanium 64bit with RDBMS Version 12c.

DEADLOCK or ORA-742 “Log read detects lost write” or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery caused by bug 21915719.

PMON may terminate the instance while extensive block recovery is being performed.

A DEADLOCK example is with LG0[n] waiting on ‘LGWR worker group ordering’. Example from a System State Dump trace file:

PROCESS 18: LG01
SO: 0x7000101f95ad720, type: 4, owner: 0x7000101f84195f8, flag: INIT/-/-/0×00
if: 0×3 c: 0×3
proc=0x7000101f84195f8, name=session, file=ksu.h LINE:13590 ID:, pg=0
conuid=0
(session) sid: 865 ser: 1 trans: 0×0, creator: 0x7000101f84195f
Current Wait Stack:
0: waiting for ‘LGWR worker group ordering’
lwn_id=0×58, phase=0×1, =0×0
wait_id=4947 seq_num=4948 snap_id=1
wait times: snap=13 min 21 sec, exc=13 min 21 sec, total=13 min 21 sec
wait times: max=infinite, heur=13 min 21 sec
wait counts: calls=1 os=267
in_wait=1 iflags=0x5a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 817, ser: 1
Dumping final blocker:
inst: 1, sid: 817, ser: 1
There are 730 sessions blocked by this session.
.
.
PROCESS 17: LG00
SO: 0x7000101f85bcc60, type: 4, owner: 0x7000101f93eeb20, flag: INIT/-/-/0×00
if: 0×3 c: 0×3
proc=0x7000101f93eeb20, name=session, file=ksu.h LINE:13590 ID:, pg=0
conuid=0
(session) sid: 817 ser: 1 trans: 0×0, creator: 0x7000101f93eeb20
ksuxds FALSE at location: 0
service name: SYS$BACKGROUND
Current Wait Stack:
0: waiting for ‘LGWR worker group ordering’
lwn_id=0×56, phase=0×1, =0×0
wait_id=1630680 seq_num=57841 snap_id=1
wait times: snap=13 min 21 sec, exc=13 min 21 sec, total=13 min 21 sec
wait times: max=infinite, heur=13 min 21 sec
wait counts: calls=2 os=268
in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 865, ser: 1
Dumping final blocker:
inst: 1, sid: 865, ser: 1

The instance may fail to OPEN with errors ORA-600 [kcrfrgv_nextlwn_scn]  and/or ORA-600 [krr_process_read_error_2]:

Recovery Session Failed with:ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [krr_process_read_error_2],

Alter database open fails with:

ORA-00600: internal error code, arguments: [kcrfrgv_nextlwn_scn] …..
ORA-600 signalled during: ALTER DATABASE OPEN…

WORKAROUND

Disable the new feature of multiple LGWR slave processes by proactively setting _use_single_log_writer=true.  

Setting _use_single_log_writer = true is a safe workaround; it is the behavior before 12c where multiple LGWR slave groups were not available.

ALTER SYSTEM SET “_use_single_log_writer”=TRUE SID=’*’ SCOPE=SPFILE;
– Restart the database or all instances of the RAC database

Note that while _use_single_log_writer=true is not set, then error ORA-600 [kcrfrgv_nextlwn_scn] might be produced avoiding the database to OPEN.  Once the problem is introduced, _use_single_log_writer=true may not fix it. _use_single_log_writer = true prevents inconsistencies in the redo log to be introduced which causes that error.

If the parameter does not help, because the problem was already introduced when _use_single_log_writer=true had not been proactively set, then Point in Time Recovery (PITR) or Flashback Database are the options to recover from this situation.

这类问题，由于写丢失无法直接open成功，如果需要，可以联系我们，提供专业ORACLE数据库恢复技术支持
Phone:13429648788    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

Alter database open ORA-7445 [kkcnrli0] signalled

Posted on by

APPLIES TO:

Oracle Database – Enterprise Edition – Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.

SYMPTOMS

Alter database open signaled ORA-7445 kkcnrli0

at :

Wed Jan 17 12:09:17 2018
CJQ0 started with pid=174, OS id=71144
Completed: ALTER DATABASE OPEN /* db agent *//* {1:39551:2} */
Wed Jan 17 12:09:18 2018
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x198F36F, kkcnrli0()+639] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/zdlra/zdlra1/trace/zdlra1_q003_71365.trc (incident=247784):
ORA-07445: exception encountered: core dump [kkcnrli0()+639] [SIGSEGV] [ADDR:0x0] [PC:0x198F36F] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/zdlra/zdlra1/incident/incdir_247784/zdlra1_q003_71365_i247784.trc

 

Trace files

shows :

*** SERVICE NAME:(SYS$BACKGROUND) 2018-01-17 12:09:18.108
*** MODULE NAME:(Streams) 2018-01-17 12:09:18.108 <—————-
*** CLIENT DRIVER:() 2018-01-17 12:09:18.108
*** ACTION NAME:(QMON Slave) 2018-01-17 12:09:18.108 <————–

========= Dump for incident 247784 (ORA 7445 [kkcnrli0]) ========

…
….

*** 2018-01-17 12:09:18.110
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0×3, level=3, mask=0×0)
[TOC00004]
—– SQL Statement (None) —–
Current SQL information unavailable – no cursor.
[TOC00004-END]

[TOC00005]
—– Call Stack Trace —–
… kkcnrli0 kkcnrli kponPurgeUnreachLoc kwqmnslv kwsbsmspm
kwsbgcbkms ksvrdp opirip opidrv sou2o
opimai_real ssthrdmain main

 

CAUSE

Bug 17722075 – ORA-7445 [kkcnrli] in Qnnn process during ALTER DATABASE OPEN (Doc ID 17722075.8)

SOLUTION

User  may either upgrade to the releases that were fixed

12.2.0.1 (Base Release)
12.1.0.2.160719 (Jul 2016) Database Patch Set Update (DB PSU)
12.1.0.2.160719 (Jul 2016) Database Proactive Bundle Patch
11.2.0.4.160719 Exadata Database Bundle Patch (Jul 2016)
12.1.0.2.160719 (Jul 2016) Bundle Patch for Windows Platforms

or

check if there were one-off patch according to your RDBMS oraInventory version

or

simply workaorund by restart database.