ORA-01092 – Database SOS

ORA-01092: ORACLE 例程终止故障恢复

数据库启动报ORA-01092: ORACLE 例程终止。强行断开连接错误

SQL> RECOVER DATABASE;
完成介质恢复。
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR 位于第 1 行:
ORA-01092: ORACLE 例程终止。强行断开连接

查看alert日志

Wed Jul 21 12:32:04 2021
SMON: enabling cache recovery
Wed Jul 21 12:32:04 2021
Errors in file c:\oracle\admin\dcpdm\udump\dcpdm_ora_3004.trc:
ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []

Wed Jul 21 12:32:05 2021
Recovery of Online Redo Log: Thread 1 Group 2 Seq 495 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO02.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 495 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO02.LOG
Wed Jul 21 12:32:05 2021
Errors in file c:\oracle\admin\dcpdm\udump\dcpdm_ora_3004.trc:
ORA-00604: ?? SQL ? 1 ????
ORA-00607: ?????????????
ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Wed Jul 21 12:32:05 2021
Errors in file c:\oracle\admin\dcpdm\bdump\dcpdm_pmon_13020.trc:
ORA-00604: error occurred at recursive SQL level 

Instance terminated by USER, pid = 3004
ORA-1092 signalled during: ALTER DATABASE OPEN...

trace文件信息

*** 2021-07-21 12:32:04.000
ksedmp: internal or fatal error
ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []
Current SQL statement for this session:
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,
scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedmp+147          CALLrel  _ksedst+0            
_ksfdmp.108+e        CALLrel  _ksedmp+0            3
_kgeriv+89           CALLreg  00000000             4E59D98 3
_kseipre.107+3f      CALLrel  _kgeriv+0            
_ksesic2+24          CALLrel  _kseipre.107+0       
__VInfreq__kturdb+8  CALLrel  _ksesic2+0           1062 0 22 0 8
b                                                  
_kcoapl+1df          CALLreg  00000000             2BB0F94 2BB100A 11 6C37C014
_kcbapl+71           CALLrel  _kcoapl+0            2BB0F90 6C37C000 1 0 2000
_kcrfwr+734          CALLrel  _kcbapl+0            2BB0F90 6C3FC788 50D4FA0
_kcbchg1+7ec         CALLrel  _kcrfwr+0            
_ktuchg+630          CALLrel  _kcbchg1+0           0 4 50D5228 50D5240 0 0
_ktbchg2+75          CALLrel  _ktuchg+0            2 66F589A4 1 2C8CD14 2C8CD1C
                                                   2BB0F90 2C8C32C 2BB0ED0 0 0
_kddchg+18f          CALLrel  _ktbchg2+0           0 66F589A4 2C8CD14 2C8CD1C
                                                   2BB0F90 2C8C324 2BB0ED0 0 0
_kduovw.53+6e3       CALLrel  _kddchg+0            2C8C2E8 2C8CD14 2C8CD1C
                                                   2BB0F90 2BB0ED0 0 0
_kduurp.53+61a       CALLrel  _kduovw.53+0         2C8C2E8
_kdusru+aa5          CALLrel  _kduurp.53+0         2C8C2E8 66F589FC
_kauupd+12e          CALLrel  _kdusru+0            2C8C71C 66F589FC 2C8C2E8 0
_updrow+729          CALLrel  _kauupd+0            2C8C718 66F589FC 2C8C2E8 0
                                                   66F58448 E F 66F60EE0 12
                                                   50DBBA4 50DBBA8
_qerupFetch+107      CALLrel  _updrow+0            
_updaul+202          CALL???  00000000             66F58660 0 66F6BC3C 7FFF
_updThreePhaseExe+b  CALLrel  _updaul+0            66F6B9D0 50DBD34 0
6                                                  
_updexe+105          CALLrel  _updThreePhaseExe+0  66F6B9D0 0 2C8C2E8 50DBE10
                                                   66F6B9D0 1 50DBE10 0
_opiexe+f97          CALLrel  _updexe+0            66F6B9D0 50DBF4C
_opiodr+4cd          CALLreg  00000000             4 3 50DC898
_rpidrus.43+99       CALLrel  _opiodr+0            4 3 50DC898 A
_skgmstack+71        CALLreg  00000000             50DC488
_rpidru+6d           CALLrel  _skgmstack+0         50DC4A0 4E59C20 F618 778198
                                                   50DC488
_rpiswu2+17e         CALLreg  00000000             50DC7C0
_rpidrv+109          CALLrel  _rpiswu2+0           
_rpiexe+33           CALLrel  _rpidrv+0            A 4 50DC898 8
_ktuscu+2a8          CALLrel  _rpiexe+0            A
_kqrcmt+2c2          CALL???  00000000             66F6D654 3
..1.18_2.filter.95+  CALLrel  _kqrcmt+0            67B88CD4 1 0 4E59D98 4E59D98
159                                                FF 0 0 0
..1.23_5.filter.99+  CALLrel  _ktcrcm+0            67B88CD4 0 0 0 0 1 0 0
14d                                                
_ktuini+64           CALLrel  _ktuiup.99+0         50DD994
_adbdrv+2665         CALLrel  _ktuini+0            50DD994
..1.5_1.filter.29+2  CALLrel  _adbdrv+0            
9d                                                 
_opiosq0+9a4         CALLrel  _opiexe+0            4 0 50DDDDC
_kpooprx+c6          CALLrel  _opiosq0+0           3 E 50DDE74 24
_kpoal8+225          CALLrel  _kpooprx+0           50DE73C 50DE684 13 1 0 24
_opiodr+4cd          CALLreg  00000000             5E 14 50DE738
_ttcpip+a86          CALLreg  00000000             5E 14 50DE738 0
_opitsk+2f4          CALLrel  _ttcpip+0            
_opiino+5fc          CALLrel  _opitsk+0            0 0 4E5FEE8 2BDF044 F3 0
_opiodr+4cd          CALLreg  00000000             3C 4 50DFBD8
_opidrv+233          CALLrel  _opiodr+0            3C 4 50DFBD8 0
_sou2o+19            CALLrel  _opidrv+0            
_opimai+10a          CALLrel  _sou2o+0             
_OracleThreadStart@  CALLrel  _opimai+0            
4+35c                                              
7C824826             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------

比较明显时候由于在更新undo$的时候需要找前镜像信息

Block image after block recovery:
buffer tsn: 0 rdba: 0x0040018b (1/395)
scn: 0x0000.07d52871 seq: 0x01 flg: 0x04 tail: 0x28710201
frmt: 0x02 chkval: 0xc85e type: 0x02=KTU UNDO BLOCK
 
********************************************************************************
UNDO BLK:  
xid: 0x0000.05a.0000002d  seq: 0x33  cnt: 0x22  irb: 0x22  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f04     0x02 0x1e20     0x03 0x1d3c     0x04 0x1c58     0x05 0x1b74     
0x06 0x1a90     0x07 0x19ac     0x08 0x18c8     0x09 0x17e4     0x0a 0x1700     
0x0b 0x161c     0x0c 0x1538     0x0d 0x1454     0x0e 0x1370     0x0f 0x128c     
0x10 0x11a8     0x11 0x10c4     0x12 0x0fe0     0x13 0x0efc     0x14 0x0e18     
0x15 0x0d34     0x16 0x0c50     0x17 0x0b6c     0x18 0x0a88     0x19 0x09a4     
0x1a 0x08c0     0x1b 0x07dc     0x1c 0x06f8     0x1d 0x0614     0x1e 0x0530     
0x1f 0x044c     0x20 0x0368     0x21 0x0284     0x22 0x01a0     
 
*-----------------------------
* Rec #0x1  slt: 0x0b  objn: 15(0x0000000f)  objd: 15  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00   
Undo type:  Regular undo    Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
uba: 0x0040018a.0033.22 ctl max scn: 0x0000.07853941 prv tx scn: 0x0000.07853943
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
op: L  itl: xid:  0x0000.042.0000002d uba: 0x0040018a.0033.22
                      flg: C---    lkc:  0     scn: 0x0000.07d23460
KDO Op code: URP row dependencies Disabled
  xtype: XA  bdba: 0x0040006a  hdba: 0x00400069
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0
ncol: 17 nnew: 12 size: 0
col  1: [ 9]  5f 53 59 53 53 4d 55 37 24
col  2: [ 2]  c1 02
col  3: [ 2]  c1 03
col  4: [ 3]  c2 02 06
col  5: [ 6]  c5 02 20 14 40 24
col  6: [ 1]  80
col  7: [ 4]  c3 0e 21 2d
col  8: [ 3]  c2 1b 34
col  9: [ 1]  80
col 10: [ 2]  c1 03
col 11: [ 2]  c1 02
col 16: [ 2]  c1 02

这部分信息异常,导致数据库update undo$的时候报ORA-00600: ?????????: [4194], [34], [8], [], [], [], [], []错误,通过修改对应的block信息,数据库正常open成功

SQL> alter database open;

数据库已更改。

但是关闭数据库又报ORA-600 4194错误

SQL> shutdown immediate;
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码，参数: [4194], [94], [61], [], [], [], [], []

alert日志信息

Wed Jul 21 12:58:42 2021
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Wed Jul 21 12:58:45 2021
ALTER DATABASE CLOSE NORMAL
Wed Jul 21 12:58:45 2021
Errors in file c:\oracle\admin\dcpdm\udump\dcpdm_ora_13628.trc:
ORA-00600: 内部错误代码，参数: [4194], [94], [61], [], [], [], [], []

Recovery of Online Redo Log: Thread 1 Group 3 Seq 496 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO03.LOG
Recovery of Online Redo Log: Thread 1 Group 3 Seq 496 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\DCPDM\REDO03.LOG
ORA-607 signalled during: ALTER DATABASE CLOSE NORMAL...

通过重建undo,数据库启动关闭正常,也没有再报其他错误,建议逻辑方式重建库
参考以前的类似文章:
数据库报ORA-00607/ORA-00600[4194]错误
 使用bbed解决ORA-00607/ORA-00600[4194]故障
 使用bbed解决ORA-00607/ORA-00600[4194]故障

ORA-01092 ORA-00704 ORA-00942

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01092 ORA-00704 ORA-00942

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

11.1.0.7版本也会出现access$表丢失导致数据库无法启动

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：11.1.0.7版本也会出现access$表丢失导致数据库无法启动

有网友咨询数据库启动报ora-01092：ORACLE 实例终止。强制断开连接，请求帮忙处理
数据库版本

Trace file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5648.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 1 - type 8664, 1 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:7605M/10239M, Ph+PgF:11979M/20477M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 5648, image: ORACLE.EXE (SHAD)

open数据库报ORA-01092: ORACLE 实例终止。强制断开连接

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

alert日志

Thread 1 opened at log sequence 1008
  Current log# 3 seq# 1008 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3964.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在
Error 704 happened during db open, shutting down database
USER (ospid: 3964): terminating the instance due to error 704
Instance terminated by USER, pid = 3964
ORA-1092 signalled during: ALTER DATABASE OPEN...
ORA-1092 : opiodr aborting process unknown ospid (3384_3964)

做10046分析日志

PARSE ERROR #1:len=56 dep=1 uid=0 oct=3 lid=0 tim=1796038335 err=942
select order#,columns,types from access$ where d_obj#=:1
*** 2015-01-27 21:24:50.794
----- Error Stack Dump -----
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在

通过这里可以知道数据库在启动的过程中由于无法访问access$表从而出现ORA-00942错误,又是由于该sql是数据库内部调用因为出现ORA-00604错误.
出现该错误的原因是由于:BUG:12733463 – ORA-704, ORA-604 AND ORA-942 ON TABLE ACCESS$ DURING STARTUP
官方提供方法

1. Shutdown (abort) the instance and clean up any OS structures used by the instance.
    Eg: Ensure there is no shared memory, semaphores etc.. left lying around
2. Retry the startup.
3. If the error persists try and recover the database or recover from a backup.

惜分飞处理方法

startup  upgrade
 create table access$
 ( d_obj#        number not null,
   order#        number not null,
   columns       raw(126),
   types         number not null)
   storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/
create index i_access1 on
  access$(d_obj#, order#)
  storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

以前类似文章:Oracle 异常恢复案例汇总

数据库启动报ORA-00704 ORA-39714错误解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：数据库启动报ORA-00704 ORA-39714错误解决

数据库启动失败,报ORA-00704、ORA-39714错误

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 7 08:15:35 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> startup
ORACLE instance started.
Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Process ID: 11592
Session ID: 1 Serial number: 5
SQL> startup upgrade
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

alert日志报错

Thu Aug 07 07:42:25 2014
SMON: enabling cache recovery
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Error 704 happened during db open, shutting down database
USER (ospid: 11592): terminating the instance due to error 704

通过分析utlmmig.sql脚本知道,数据库在升级bootstrap$之前会先在props$表中插入BOOTSTRAP_UPGRADE_ERROR相关记录,数据库在启动之时会检测该值,如果发现该值存在,数据库只能以upgrade模式启动,清理掉相关记录,数据库即可正常启动

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 7 07:42:44 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.
SQL>   delete from props$ where name = 'BOOTSTRAP_UPGRADE_ERROR';
1 row deleted.
SQL>   delete from props$ where name = 'LOGMNR_BOOTSTRAP_UPGRADE_ERROR';
0 rows deleted.
SQL>   commit;
Commit complete.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.
SQL>

数据库虽然正常启动成功,但是由于bootstrap$对象升级失败,后续还是有很大风险,建议分析报错原因,解决原因然后继续升级bootstrap$基表

ORACLE丢失各种文件导致数据库不能OPEN恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE丢失各种文件导致数据库不能OPEN恢复

在ORACLE的运行过程中,总会遇到这样那样的故障,本篇主要大概介绍关于因硬件,系统,误删除等各种原因导致数据库的部分文件丢失,这里列出来由于文件丢失而出现的常见错误和基本处理思路

1.丢失数据文件(ORA-01157)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/oracle/oradata/XFF/users01.dbf’
数据文件丢失，处理方法：
1).使用备份还原丢失数据然后
2).非undo,system可以offline 掉该文件继续打开数据库
3).如果是undo需要谨慎,可能导致ORA-00376错误
4).如果是system offline可能导致ORA-01147

2. 丢失redo(ORA-00313)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oracle/oradata/XFF/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
redo文件丢失，处理步骤：
1).查询v$log确认该redo是否是current或者active
2).确定该redo是否被归档
3).如果是inactive使用clear 或者 clear unarchived
4).如果是active或者current,需要通过不完全恢复,甚至隐含参数等方法解决

3. 丢失undo(ORA-01092 ORA-00376)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 – see DBWR trace file
ORA-01110: data file 2: ‘/u01/oracle/oradata/XFF/undotbs01.dbf’

SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01092是前台错误,通过查询alert日志发现后台错误主要是：
Fri Oct 25 08:16:36 2013
Errors in file /u01/oracle/admin/XFF/bdump/xff_smon_7437.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘/u01/oracle/oradata/XFF/undotbs01.dbf’
因为undo文件丢失,有事务无法正常回滚，从而出现该错误,需要通过使用隐含参数屏蔽事务来解决

4. 丢失system(ORA-01147)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘/u01/oracle/oradata/XFF/system01.dbf’

SQL> alter database datafile 1 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: ‘/u01/oracle/oradata/XFF/system01.dbf’
system表空间是系统表空间，该表空间中的数据文件不能被offline,如果该表空间数据文件丢失，数据库无法正常方法,可以考虑使用bbed模拟system文件欺骗数据库(非file# 1)或者使用dul抽取数据

5. 丢失控制文件(ORA-00205 ORA-00202)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
ORA-00205: error in identifying control file, check alert log for more info

ORA-00205是前台错误，具体需要结合日志分析：
Fri Oct 25 08:35:40 2013
ALTER DATABASE MOUNT
Fri Oct 25 08:35:40 2013
ORA-00202: control file: ‘/u01/oracle/oradata/XFF/control01.ctl’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
这里可以看出来，是因为控制问文件丢失该值该错误,处理办法：
1).使用备份控制文件还原
2).查找是否还有其他控制文件，拷贝一份
3).列举数据文件重建控制文件

如果你在使用这些思路进行恢复遇到突发情况不能自行解决,请联系我们,将为您提供专业数据库技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

姊妹篇
undo异常总结和恢复思路
 ORACLE REDO各种异常恢复

标签归档：ORA-01092