标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,679)
- DB2 (22)
- MySQL (73)
- Oracle (1,541)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
标签归档:ORA-00942
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$表丢失导致数据库无法启动
有网友咨询数据库启动报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 异常恢复案例汇总