分类目录归档：Oracle备份恢复

记录正常open库报ORA-600 2662

发表于 2018 年 8 月 1 日 由 惜分飞

数据库版本10.2.0.3 32位

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

数据库启动alert日志报大量ORA-600和ORA-07445错误

Tue Jul 31 09:56:45 2018
Started redo application at
 Thread 1: logseq 7593, block 46691
Tue Jul 31 09:56:45 2018
Recovery of Online Redo Log: Thread 1 Group 2 Seq 7593 Reading mem 0
  Mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Tue Jul 31 09:56:45 2018
RECOVERY OF THREAD 1 STUCK AT BLOCK 779 OF FILE 2
Tue Jul 31 09:56:45 2018
RECOVERY OF THREAD 1 STUCK AT BLOCK 4430 OF FILE 2
Tue Jul 31 09:56:45 2018
Hex dump of (file 3, block 23704) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p002_248.trc
Corrupt block relative dba: 0x00c05c98 (file 3, block 23704)
Fractured block found during crash/instance recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x00c05c98
 last change scn: 0x0000.05c3cad2 seq: 0x16 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xe4be0601
 check value in block header: 0x5c03
 computed block checksum: 0x2e7b
Tue Jul 31 09:56:45 2018
Completed redo application
Tue Jul 31 09:56:46 2018
Reread of rdba: 0x00c05c98 (file 3, block 23704) found same corrupted data
RECOVERY OF THREAD 1 STUCK AT BLOCK 40841 OF FILE 8
Tue Jul 31 09:56:46 2018
RECOVERY OF THREAD 1 STUCK AT BLOCK 297 OF FILE 2
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p002_248.trc:
ORA-00600: internal error code, arguments: [kssadpm1], [], [], [], [], [], [], []

Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p001_2320.trc:
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [16], [], [], [], []
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [16], [], [], [], []
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [16], [], [], [], []
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [8], [], [], [], []

Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p004_2116.trc:
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C3429C1] [ADDR:0xCA800000] [UNABLE_TO_READ] []

Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p000_220.trc:
ORA-00600: internal error code, arguments: [ksfdchkfobrerr1], [0xBB9852DC], [0xA7EC530C], [], [], [], [], []

Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p003_252.trc:
ORA-00600: internal error code, arguments: [kssrc_test_cleanup:popall], [0xCA000304], [], [], [], [], [], []

Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p001_228.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kghalf+895] [PC:0x603BEBF3] [ADDR:0xFFFFFFF8] [UNABLE_TO_READ] []

Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_1848.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ksarcv+243] [PC:0x5B2223] [ADDR:0x206C000C] [UNABLE_TO_READ] []

Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p000_220.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kslwlmod+166] [PC:0x46946A] [ADDR:0x54F8] [UNABLE_TO_WRITE] []
ORA-00081: address range [0x75C80047, 0x75C8004B) is not readable
ORA-00600: internal error code, arguments: [ksfdchkfobrerr1], [0xBB9852DC], [0xA7EC530C], [], [], [], [], []

Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_1852.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kcbs_dump_adv_state+471] [PC:0x59B403] [ADDR:0xCC60CBAD] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kssrc_test_cleanup:popall], [0xCA000304], [], [], [], [], [], []

Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_1828.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kews_idle_wait+378] [PC:0x60BE2E] [ADDR:0x1820D468] [UNABLE_TO_WRITE] []

Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p004_236.trc:
ORA-00081: address range [0x75C80041, 0x75C80045) is not readable
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C3429C1] [ADDR:0xCA800000] [UNABLE_TO_READ] []

检查主要坏块
主要影响数据库恢复的坏块,system不言而喻,block 2也比较敏感

E:\>dbv file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT

DBVERIFY: Release 10.2.0.3.0 - Production on 星期三 8月 1 00:24:52 2018

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

DBVERIFY - 开始验证: FILE = E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT
页 2 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x02000002 (file 8, block 2)
Fractured block found during dbv:
Data in bad block:
 type: 29 format: 2 rdba: 0x02000002
 last change scn: 0x0000.05c45a54 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x9e601d02
 check value in block header: 0xea86
 computed block checksum: 0xc434



DBVERIFY - 验证完成

检查的页总数: 121600
处理的页总数 (数据): 58606
失败的页总数 (数据): 0
处理的页总数 (索引): 45192
失败的页总数 (索引): 0
处理的页总数 (其它): 4453
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 13348
标记为损坏的总页数: 1
流入的页总数: 1
最高块 SCN            : 96716847 (0.96716847)

E:\>dbv FILE = E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

DBVERIFY: Release 10.2.0.3.0 - Production on 星期三 8月 1 00:28:28 2018

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

DBVERIFY - 开始验证: FILE = E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
页 68377 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00410b19 (file 1, block 68377)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x00410b19
 last change scn: 0x0000.05c45bdf seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4f3b0601
 check value in block header: 0x465b
 computed block checksum: 0x11c7


DBV-00200: 块 dba 4262777 已标记为损坏


DBVERIFY - 验证完成

检查的页总数: 74240
处理的页总数 (数据): 46209
失败的页总数 (数据): 0
处理的页总数 (索引): 9729
失败的页总数 (索引): 0
处理的页总数 (其它): 1923
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 16378
标记为损坏的总页数: 2
流入的页总数: 1
最高块 SCN            : 96757289 (0.96757289)

尝试recover数据库,open数据库

E:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 7月 31 15:40:05 2018

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

数据库报ORA-600 2662错误
该数据库没有增加隐含隐含参数(屏蔽一致性,屏蔽事务),数据库直接启动之后报ORA-600 2662

Tue Jul 31 15:40:15 2018
SMON: enabling cache recovery
Tue Jul 31 15:40:16 2018
Successfully onlined Undo Tablespace 1.
Tue Jul 31 15:40:16 2018
SMON: enabling tx recovery
Tue Jul 31 15:40:16 2018
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=32, OS id=1152
Tue Jul 31 15:40:17 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736891], [0], [96752794], [4264138], [], []

Tue Jul 31 15:40:18 2018
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:18 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736892], [0], [96752794], [4264138], [], []

Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 3 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:19 2018
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Hex dump of (file 8, block 2) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mmon_1740.trc
Corrupt block relative dba: 0x02000002 (file 8, block 2)
Fractured block found during buffer read
Data in bad block:
 type: 29 format: 2 rdba: 0x02000002
 last change scn: 0x0000.05c45a54 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x9e601d02
 check value in block header: 0xea86
 computed block checksum: 0xc434
Reread of rdba: 0x02000002 (file 8, block 2) found same corrupted data
Tue Jul 31 15:40:20 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736902], [0], [96752794], [4264138], [], []

Tue Jul 31 15:40:20 2018
Completed: alter database open
Tue Jul 31 15:40:20 2018
Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 4 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:21 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736922], [0], [96752794], [4264138], [], []

Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 5 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:22 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_1076.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [96736954], [0], [96754130], [12615382], [], []

Tue Jul 31 15:40:22 2018
DEBUG: Replaying xcb 0xbba2d2e4, pmd 0xab1920fc for failed op 8
Reconstructing  Uhdr 0x800019 for xcb 0xbba2d2e4, pmd 0xab1920fc
Doing block recovery for file 2 block 25
Block recovery from logseq 7594, block 63 to scn 96736910
Tue Jul 31 15:40:22 2018
Recovery of Online Redo Log: Thread 1 Group 3 Seq 7594 Reading mem 0
  Mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 7594.82.16, scn 0.96736911
DEBUG: Restoring block headers for xcb 0xbba2d2e4, pmd 0xab1920fc
DEBUG: Finished replay for xcb 0xbba2d2e4, pmd 0xab1920fc for failed op 8
Tue Jul 31 15:40:22 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_1076.trc:
ORA-00603: ORACLE 服务器会话因致命错误而终止
ORA-00600: 内部错误代码, 参数: [2662], [0], [96736955], [0], [96754130], [12615382], [], []
ORA-00600: 内部错误代码, 参数: [2662], [0], [96736954], [0], [96754130], [12615382], [], []

Tue Jul 31 15:40:31 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736963], [0], [96752794], [4264138], [], []

Tue Jul 31 15:40:31 2018
Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 6 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:41 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736967], [0], [96752794], [4264138], [], []

Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_1448.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:03 2018
PMON: terminating instance due to error 474
Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j004_1344.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j003_2104.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j002_932.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:04 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j001_1680.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:04 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_q000_2336.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_552.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_1420.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw1_2556.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_1812.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mman_1924.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_2940.trc:
ORA-00474: SMON process terminated with error

Tue Jul 31 15:41:10 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_reco_660.trc:
ORA-00474: SMON process terminated with error

Instance terminated by PMON, pid = 1448
Tue Jul 31 15:41:57 2018

这是一个不常见的错误,没有使用隐含参数强制拉库,数据库正常open成功,但是由于system坏块,导致数据库启动之后报ORA-600 2662错误(再次证明2662不一定只有强制拉库发生,正常open的库也有可能,主要取决block scn和datafile scn,如果不是open过程必须要访问的block,那可能在open之后由于访问需要再报出来该错误).这个问题比较简单,因为open成功之后再crash,而且该坏块引起smon报错但是并没有ora-600 4xxx相关错误,因此根据经验,直接在数据库open之后,处理掉system异常报错对象和坏块对象即可.另外block 2 比较特殊,需要对其上面的对象进行处理

Tue Jul 31 15:55:54 2018
Hex dump of (file 8, block 2) in trace file e:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3664.trc
Corrupt block relative dba: 0x02000002 (file 8, block 2)
Completely zero block found during reading space header
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Tue Jul 31 15:55:54 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3664.trc:
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [_krbodmpcx+243] [PC:0x2317857] [ADDR:0x8] [UNABLE_TO_READ] []
ORA-19880: 数据文件 E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT 的空间标头已损坏, 块 2 备份已中止

补充ora-600 2662 block记录

          ----------------------------------------
          SO: BB97A97C, type: 24, owner: BB882B10, flag: INIT/-/-/0x00
          (buffer) (CR) PR: BB8029D8 FLG: 0x100000
          class bit: 00000000
          kcbbfbp: [BH: B9FCF268, LINK: BB97A9A0]
          where: kdswh06: kdscgr, why: 0
          BH (B9FCF268) file#: 1 rdba: 0x004110ca (1/69834) class: 1 ba: B9832000
            set: 9 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
            dbwrid: 0 obj: 573 objn: 575 tsn: 0 afn: 1
            hash: [bb9151ec,bb9151ec] lru: [b9fcf378,b9fcf200]
            ckptq: [NULL] fileq: [NULL] objq: [b9fcf3d0,aa776524]
            use: [bb97a9a0,bb97a9a0] wait: [NULL]
            st: XCURRENT md: SHR tch: 0
            flags:
            LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
            Using State Objects
              ----------------------------------------
              SO: BB97A97C, type: 24, owner: BB882B10, flag: INIT/-/-/0x00
              (buffer) (CR) PR: BB8029D8 FLG: 0x100000
              class bit: 00000000
              kcbbfbp: [BH: B9FCF268, LINK: BB97A9A0]
              where: kdswh06: kdscgr, why: 0
            buffer tsn: 0 rdba: 0x004110ca (1/69834)
            scn: 0x0000.05c4549a seq: 0x01 flg: 0x06 tail: 0x549a0601
            frmt: 0x02 chkval: 0xc60a type: 0x06=trans data
发表在 Oracle备份恢复 | 标签为 , , , , , , , , , | 评论关闭

open数据库遭遇ORA-00913错误恢复

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

由于字典被人恶意进行损坏,进行了一系列恢复之后,数据库依旧无法正常启动,而且出现比较诡异的错误ORA-00913: too many values
数据库启动报ORA-00913错

Starting background process MMNL
Mon Jul 16 11:55:30 2018
MMNL started with pid=30, OS id=37580 
ALTER SYSTEM enable restricted session;
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off. 
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Resource Manager disabled during database migration
Errors in file /s01/orabase/diag/rdbms/xff/xff1/trace/xff1_ora_37231.trc:
ORA-00913: too many values
Errors in file /s01/orabase/diag/rdbms/xff/xff1/trace/xff1_ora_37231.trc:
ORA-00913: too many values
Error 913 happened during db open, shutting down database
USER (ospid: 37231): terminating the instance due to error 913
Instance terminated by USER, pid = 37231
ORA-1092 signalled during: alter database open upgrade...
opiodr aborting process unknown ospid (37231) as a result of ORA-1092
Mon Jul 16 11:55:32 2018
ORA-1092 : opitsk aborting process

通过跟踪启动过程发现对SYS_FBA_TRACKEDTABLES表的插入报错

PARSE ERROR #140275116997968:len=70 dep=1 uid=0 oct=2 lid=0 tim=1531713543033057 err=913
insert into SYS_FBA_TRACKEDTABLES values (-1, -1, 0, '', '', 1, NULL)
Flashback Archive: Error ORA-913 in SQL 
insert into SYS_FBA_TRACKEDTABLES values (-1, -1, 0, '', '', 1, NULL)

通过工具分析问题
 

DUL> desc sys.SYS_FBA_TRACKEDTABLES

object_id: 1304, dataobj#: 1304, cluster tab#: 0
segment header: (ts#: 0, rfile#: 0, block#: 0))

Name                 Null?           Type
-------------------- --------------- --------------
OBJ#                 NOT NULL        NUMBER
FA#                  NOT NULL        NUMBER
DROPSCN                              NUMBER
OBJNAME                              VARCHAR2(30)
OWNERNAME                            VARCHAR2(30)
FLAGS                                NUMBER
SPARE                                NUMBER

发现奇怪segment header记录为空,根据经验很可能tab$中该记录丢失,通过分析验证,果然是tab$中记录丢失

C:\Users\Xifenfei>cat D:\RECOVER\dul\obj.dat |grep 1304
1304|1304|0|SYS_FBA_TRACKEDTABLES|1||2||
11304||1|USER_SQLTUNE_RATIONALE_PLAN|1||5||
13040||0|ORA$AUTOTASK_CLEAN|1||66||
13041||0|AUTO_TASK_CONSUMER_GROUP|24||48||
13042||0|WEEKNIGHT_WINDOW|1||69||
13043||0|WEEKEND_WINDOW|1||69||
13044||0|HM_CREATE_OFFLINE_DICTIONARY|1||66||
13045||0|DRA_REEVALUATE_OPEN_FAILURES|1||66||
13046|13046|0|ALERT_QT|1||2||
13049|13049|0|SYS_C003549|4||1||
13048|13048|0|SYS_IL0000013046C00070$$|4||1||
13047|13047|0|SYS_LOB0000013046C00070$$|8||21||
13065||0|QT13046_BUFFER|1||4||
16281||0|/e5913043_DualReaderBuilderSub|1||29||
16282||1|/e5913043_DualReaderBuilderSub|1||5||
21304||0|/997def1d_MetalIconFactoryInte|1||29||
31304||0|/859af54a_AppOutputStream|1||29||
41304||0|/38d9d6d4_JAXBContextImpl7|1||29||
51304||0|sun/awt/FontConfiguration$2|1||29||
61304||0|/8cc2fced_CacheCustomizerError|1||29||
71304||51|/ec0dfc12_AnnotationHandlerExt|1||29||
81304|81304|63|WWV_FLOW_PAGE_PLUG_IDX2|4||1||

C:\Users\Xifenfei>cat D:\RECOVER\dul\tab.dat |grep 1304
5323|5323|0|1|11304|||7|2|1|536870912|
13046|13046|1|2|2258|||29|||539101186|

解决方法
通过一些内部方法,挂起数据库(不让他在open的过程报错),通过其他正常库获得该条tab$记录,然后插入数据库中(insert into sys.tab$ (OBJ#, DATAOBJ#, TS#, FILE#, BLOCK#, BOBJ#, TAB#, COLS, CLUCOLS, PCTFREE$, PCTUSED$, INITRANS, MAXTRANS, FLAGS, AUDIT$, ROWCNT, BLKCNT, EMPCNT, AVGSPC, CHNCNT, AVGRLN, AVGSPC_FLB, FLBCNT, ANALYZETIME, SAMPLESIZE, DEGREE, INSTANCES, INTCOLS, KERNELCOLS, PROPERTY, TRIGFLAG, SPARE1, SPARE2, SPARE3, SPARE4, SPARE5, SPARE6) values (1304, 1304, 0, 1, 8120, null, null, 7, null, 10, 40, 1, 255, 529, ‘————————————–’, 1, 1, 0, 0, 0, 13, 0, 0, to_date(’24-08-2013 11:43:19′, ‘dd-mm-yyyy hh24:mi:ss’), 1, null, null, 7, 7, 9126805504, 0, 736, null, null, null, null, to_date(’24-08-2013 18:37:48′, ‘dd-mm-yyyy hh24:mi:ss’));
),再重启系统数据库,恢复正常
20180718104445

该库有可能还有很多字典不一致问题,建议尽快逻辑方式重建该库.

发表在 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备份恢复 | 标签为 , , , | 评论关闭