分类目录归档：Oracle

ORA-01092 ORA-00704 ORA-00942

发表于 2018 年 7 月 13 日 由 惜分飞

有一朋友数据库启动出现报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:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

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

DBV-00600: 致命错误 – [21] [5] [0] [0] 错误原因

发表于 2018 年 7 月 4 日 由 惜分飞

11.2.0.4 dbv检查

C:\Users\Administrator>dbv file=D:\TEMP\example01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.4.0 - Production on 星期三 7月 4 18:04:34 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


DBV-00600: 致命错误 - [21] [5] [0] [0]

12.2 dbv检查

oracle@localhost ~]$ dbv file=/tmp/example01.dbf

DBVERIFY: Release 12.2.0.1.0 - Production on Wed Jul 4 06:28:51 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


DBV-00113: FILE (/tmp/example01.dbf) file size is not a multiple of block size

OS文件大小

---linux
[oracle@localhost ~]$ ls -l /tmp/example01.dbf 
-rwxrwxrwx 1 root root 104865793 Jul  3 11:46 /tmp/example01.dbf


---win
C:\Users\Administrator>dir D:\TEMP\example01.dbf
 驱动器 D 中的卷是 本地硬盘
 卷的序列号是 EECC-4D96

 D:\TEMP 的目录

2018-07-03  23:46       104,865,793 example01.dbf
               1 个文件    104,865,793 字节
               0 个目录 2,192,213,327,872 可用字节

数据库记录文件大小

DUL> dump datafile 5 block 1
Block Header:
block type=0x0b (file header)
block format=0xa2 (oracle 10)
block rdba=0x01400001 (file#=5, block#=1)
scn=0x0000.00000000, seq=1, tail=0x00000b01
block checksum value=0xebd0=60368, flag=4
File Header:
Db Id=0x58a78964=1487374692, Db Name=ORCL, Root Dba=0x0
Software vsn=0x0, Compatibility Vsn=0xb200000, File Size=0x3200=12800 Blocks
File Type=0x3 (data file), File Number=5, Block Size=8192
Tablespace #6 - EXAMPLE rel_fn:5

比较明显数据文件头记录文件大小为12800*8192+8192(block 0)=104865792,而文件系统中显示的文件大小为104865793,不是8192的整数倍。因此出现相关错误DBV-00600: 致命错误 – [21] [5] [0] [0] 或者 DBV-00113(通过mos确认Bug 18232647 – DBVerify reports DBV-600 [21] [5] [0] [0] when datafile size is beyond expected – produce a more meaningful error (Doc ID 18232647.8))

发表在 Oracle | 标签为 , | 评论关闭

ORA-00600: internal error code, arguments: [kcvorl_2]

发表于 2018 年 7 月 4 日 由 惜分飞

一个朋友的数据库,由于redo损坏,经过一系列恢复,当我接手之时,已经是ORA-00283和ORA-16433错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORCL\SYSTEM01.DBF'

SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

通过重建控制文件继续恢复,遭遇ORA-00600 kcvorl_2错误

SQL> startup nomount pfile='d:/pfile.txt';
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1224738384 bytes
Database Buffers          905969664 bytes
Redo Buffers                5001216 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\ORCL\redo01.log'  SIZE 50M,
  9    GROUP 2 'D:\ORCL\redo02.log'  SIZE 50M,
 10    GROUP 3 'D:\ORCL\redo03.log'  SIZE 50M
 11  DATAFILE
 12  'D:\ORCL\SYSTEM01.DBF',
 13  'D:\ORCL\SYSAUX01.DBF',
 14  'D:\ORCL\UNDOTBS01.DBF',
 15  'D:\ORCL\USERS01.DBF',
 16  'D:\ORCL\XIFENFEI1',
 17  'D:\ORCL\XIFENFEI2'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcvorl_2], [0], [54271176], [0],[54271176], [], [], [], [], [], [], []

查询了mos发现该错误一般是由于Bug 20562968 – ORA-600 [KCVORL_2] DURING SWITCHOVER AFTER DOWNGRADING TO 11.2.0.1导致主库在switchover的时候可能会遇到该错误,还是第一次遇到数据库在resetlogs 打开的时候遭遇该错误.分析trace文件

Dump continued from file: e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_1892.trc
ORA-00600: internal error code, arguments: [kcvorl_2], [0], [54271176], [0], [54271176], [], [], [], [], [], [], []

========= Dump for incident 3738 (ORA 600 [kcvorl_2]) ========

*** 2018-07-03 16:35:41.404
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+129        CALL???  skdstdst()           009233DA2 000000000 000000000
                                                   000000000
ksedst()+69          CALL???  ksedst1()            000000002 000000000 006F605E0
                                                   000000000
dbkedDefDump()+4536  CALL???  ksedst()             000000287 000000000 000000000
                                                   000000000
ksedmp()+43          CALL???  dbkedDefDump()       000000003 000000002 000000000
                                                   000468E71
ksfdmp()+87          CALL???  ksedmp()             000000000 000000000 000000000
                                                   000000000
dbgexPhaseII()+1819  CALL???  ksfdmp()             000000000 000000000 000000000
                                                   000000000
dbgexProcessError()  CALL???  dbgexPhaseII()       00C9B0570 00C9BD448 000000E9A
+2563                                              000000002
dbgeExecuteForError  CALL???  dbgexProcessError()  00C9B0570 00C9B7540 000000001
()+65                                              000000000
dbgePostErrorKGE()+  CALL???  dbgeExecuteForError  BC30C65D3 019606FF0 018881658
1726                          ()                   000502034
dbkePostKGE_kgsf()+  CALL???  dbgePostErrorKGE()   0196075B0 00CB80040 000000258
75                                                 BA268928B586
kgeadse()+342        CALL???  dbkePostKGE_kgsf()   000000000 000000000
                                                   BA2688AA3890 7FFFB6A1728
kgerinv_internal()+  CALL???  kgeadse()            006F8C5A8 0196075B0 000000000
76                                                 0196072B0
kgerinv()+49         CALL???  kgerinv_internal()   018883960 0071C3480 000000000
                                                   000000000
kgeasnmierr()+64     CALL???  kgerinv()            0014B18A0 0071C3480 018881C20
                                                   000000000
kcvorl()+8957        CALL???  kgeasnmierr()        0071C4650 018882B00 000000000
                                                   000000004
adbdrv()+54131       CALL???  kcvorl()+428         000000008 018883E20 018887A88
                                                   078136DFB
opiexe()+20842       CALL???  adbdrv()             000000023 000000003
                                                   7FF00000102 000000000
opiosq0()+5129       CALL???  opiexe()+16981       000000004 000000000 01888A8E0
                                                   009361AB3
kpooprx()+357        CALL???  opiosq0()            000000003 00000000E 01888ABB0
                                                   0000000A4
kpoal8()+940         CALL???  kpooprx()            000020C80 008832840 00CBD1A48
                                                   000000001
opiodr()+1662        CALL???  kpoal8()             00000005E 00000001C 01888E120
                                                   00CA5BAA8
ttcpip()+1325        CALL???  opiodr()             480000000000005E
                                                   49004D000000001C 01888E120
                                                   4100200000000000
opitsk()+2040        CALL???  ttcpip()             0196212D0 000000000 000000000
                                                   000000000
opiino()+1258        CALL???  opitsk()             00000001E 000000000 000000000
                                                   01888FA18
opiodr()+1662        CALL???  opiino()             00000003C 000000004 01888FAD0
                                                   000000000
opidrv()+864         CALL???  opiodr()             00000003C 000000004 01888FAD0
                                                   6F5C3A6500000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 01888FAD0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              01888FB00 01888FBC4
                                                   100003000707E2 202020029001D
opimai()+191         CALL???  opimai_real()        00000001A 01888FC88 000000034
                                                   000000000
OracleThreadStart()  CALL???  opimai()             01888FE90 01211FF38 000000002
+724                                               01888FC88
0000000078D3B6DA     CALL???  OracleThreadStart()  01211FF38 000000000 000000000
                                                   01888FFA8
 

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

通过分析发现其中一个文件scn不对

SQL> SELECT status,
  2  checkpoint_change#,
  3  checkpoint_time,FUZZY,
  4  count(*) ROW_NUM
  5  FROM v$datafile_header
  6  GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME    FUZ    ROW_NUM
------- ------------------ ------------------ --- ----------
ONLINE            54271175 27-MAY-18          YES          1
ONLINE            54271179 25-JUN-18          YES          5

SQL> set numw 16
SQL> SELECT status,
  2  checkpoint_change#,
  3  checkpoint_time,last_change#,
  4  count(*) ROW_NUM
  5  FROM v$datafile
  6  GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME        LAST_CHANGE#          ROW_NUM
------- ------------------ ------------------ ---------------- ----------------
RECOVER           54271175 27-MAY-18                                          1
RECOVER           54271179 25-JUN-18                                          4
SYSTEM            54271179 25-JUN-18                                          1

SQL> set linesize 150
SQL> select ts#,file#,TABLESPACE_NAME,status,
  2  to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,
  3  to_char(checkpoint_change#,'9999999999999999') "SCN",
  4  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
  5  from v$datafile_header;

             TS#            FILE# TABLESPACE_NAME                STATUS  CREATE_
TIME         SCN               RESETLOGS SCN     FUZ
---------------- ---------------- ------------------------------ ------- -------
------------ ----------------- ----------------- ---
               0                1 SYSTEM                         ONLINE  2010-03
-30 10:07:48          54271179          54271176 YES
               1                2 SYSAUX                         ONLINE  2010-03
-30 10:07:52          54271175          54271176 YES
               2                3 UNDOTBS1                       ONLINE  2010-03
-30 11:07:21          54271179          54271176 YES
               4                4 USERS                          ONLINE  2010-03
-30 10:08:04          54271179          54271176 YES
               6                5 XIFENFEI1                      ONLINE  2016-08
-02 18:52:23          54271179          54271176 YES
               7                6 XIFENFEI2                      ONLINE  2016-08
-02 18:52:31          54271179          54271176 YES

6 rows selected.

有SYSAUX数据文件的scn不对,通过bbed修改scn继续恢复成功

SQL> alter database open resetlogs;

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