Oracle备份恢复 – 第17页

正常open库报ORA-600 2662,ORA-600 4097错误

又一起数据库正常open之后继续报ORA-600 2662

Sat Dec 29 04:30:40 2018
QMNC started with pid=36, OS id=12985
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open
Sat Dec 29 04:30:42 2018
Starting background process CJQ0
Sat Dec 29 04:30:42 2018
CJQ0 started with pid=41, OS id=12999
Sat Dec 29 04:30:43 2018
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_m000_12989.trc  (incident=365101):
ORA-00600: internal error code, arguments: [2662], [0], [193646286], [0], [193662077], [50331650], [], [], [], [], [], []
Sat Dec 29 04:30:43 2018
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_m002_12993.trc  (incident=365117):
ORA-00600: internal error code, arguments: [2662], [0], [193646286], [0], [193662077], [50331650], [], [], [], [], [], []
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_365101/orcl_m000_12989_i365101.trc
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_365117/orcl_m002_12993_i365117.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_m000_12989.trc  (incident=365102):
ORA-00600: internal error code, arguments: [2662], [0], [193646286], [0], [193662077], [50331650], [], [], [], [], [], []
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_365102/orcl_m000_12989_i365102.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_m002_12993.trc  (incident=365118):
ORA-00600: internal error code, arguments: [2662], [0], [193646286], [0], [193662077], [50331650], [], [], [], [], [], []
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_365118/orcl_m002_12993_i365118.trc

在open状态处理掉ORA-00600 2662错误之后,又出现了ORA-600 4097 以及ORA-00353 ORA-00312错误,这个也是一种不常见的现象,一般都是数据库open之前出现类似错误,但是这里在open之后出现(以前类似处理参考:ORA-600 4097故障处理和ORA-00322 ORA-00312 ,ORA-00354 ORA-00353 ORA-00312异常处理）

Sat Dec 29 05:55:22 2018
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_j000_19136.trc  (incident=432567):
ORA-00600: internal error code, arguments: [4097], [3], [15], [76480], [], [], [], [], [], [], [], []
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_432567/orcl_j000_19136_i432567.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_j000_19136.trc  (incident=432568):
ORA-00353: log corruption near block 15682 change 193647101 time 12/29/2018 04:40:31
ORA-00312: online log 3 thread 1: '/home/app/oradata/orcl/redo03.log'
ORA-00312: online log 3 thread 1: '/home/app/oradata/orcl/redo03.log'
ORA-00600: internal error code, arguments: [4097], [3], [15], [76480], [], [], [], [], [], [], [], []
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_432568/orcl_j000_19136_i432568.trc
Errors in file /home/app/diag/rdbms/orcl/orcl/incident/incdir_432567/orcl_j000_19136_i432567.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 15682 change 193647101 time 12/29/2018 04:40:31
ORA-00312: online log 3 thread 1: '/home/app/oradata/orcl/redo03.log'
ORA-00312: online log 3 thread 1: '/home/app/oradata/orcl/redo03.log'
ORA-00600: internal error code, arguments: [4097], [3], [15], [76480], [], [], [], [], [], [], [], []
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_j000_19136.trc  (incident=432569):
ORA-00353: log corruption near block 15682 change 193647101 time 12/29/2018 04:40:31
ORA-00334: archived log: '/home/app/oradata/orcl/redo03.log'
ORA-00312: online log 3 thread 1: '/home/app/oradata/orcl/redo03.log'
ORA-00600: internal error code, arguments: [4097], [3], [15], [76480], [], [], [], [], [], [], [], []
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_432569/orcl_j000_19136_i432569.trc

处理完成上面报错之后,继续出现比较少见的ORA-600 ktecgsc:kcbz_objdchk错误,具体参考:ORA-00600 [ktecgsc:kcbz_objdchk] on 11.2 (Doc ID 1562473.1)

Sat Dec 29 06:00:17 2018
DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_j001_19436.trc:
ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
Sat Dec 29 06:00:21 2018
Errors in file /home/app/diag/rdbms/orcl/orcl/trace/orcl_j002_19479.trc  (incident=432631):
ORA-00600: internal error code, arguments: [ktecgsc:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []
Incident details in: /home/app/diag/rdbms/orcl/orcl/incident/incdir_432631/orcl_j002_19479_i432631.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

ORA-600 2131故障说明

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

标题：ORA-600 2131故障说明

oracle 12c数据库启动报ORA-600 2131错误

Mon Nov 26 09:43:57 2018
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\app\Administrator
alter database mount exclusive
Mon Nov 26 09:44:00 2018
Using default pga_aggregate_limit of 2048 MB
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl12c\orcl12c\trace\orcl12c_ora_3040.trc  (incident=375524):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl12c\orcl12c\incident\incdir_375524\orcl12c_ora_3040_i375524.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: alter database mount exclusive...

这个日志比较明显,数据库无法mount,在mount操作的时候报ORA-600 2131错误.
trace文件报错

Error: kccpb_sanity_check_2
Control file sequence number mismatch!
fhcsq: 497844 bhcsq: 497849 cfn 0 rpbn 16
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+92         CALL???  skdstdst()           000000001 000004000 000030000
                                                   016301338
kccpb_sanity_check(  CALL???  ksedst1()            1492761E0 0000798B4 0000798B9
)+834                                              000000000
kccbmp_get()+275     CALL???  kccpb_sanity_check(  000000000 000000000 000000000
                              )                    000004000
kccsed_rbl()+174     CALL???  kccbmp_get()         000017E28 015A67E14 015592200
                                                   000000001
kccocx()+1399        CALL???  kccsed_rbl()         100000010 100000001 0000354D8
                                                   000035508
kccocf()+167         CALL???  kccocx()+528         016303990 000000000
                                                   7FF00000001 000000000
kcfcmb()+1254        CALL???  kccocf()             000000000 000000000 000000000
                                                   000000000
kcfmdb()+69          CALL???  kcfcmb()             000000000 7FF59FFF856
                                                   000000007 7FE00000000
adbdrv_options()+43  CALL???  kcfmdb()             0163083E0 14903FF2C 000000005
724                                                000000000
adbdrv()+149         CALL???  adbdrv_options()     000000000 000000000 0163084A0
                                                   851F2CC90B75
opiexe()+22668       CALL???  adbdrv()             7FF00000023 000000003
                                                   000000000 016309380
opiosq0()+6009       CALL???  opiexe()             000000000 000000000 016309990
                                                   000000000
kpooprx()+410        CALL???  opiosq0()            000000003 000000000 000000000
                                                   0000000A4
kpoal8()+994         CALL???  kpooprx()            0146A57FC 000000001 0146A5820
                                                   000000001
opiodr()+1601        CALL???  kpoal8()             000000000 015523288 015523270
                                                   0159FCDD0
ttcpip()+1223        CALL???  opiodr()             7FE0000005E 00000001F
                                                   01630DA20 7FE00000000
opitsk()+2160        CALL???  ttcpip()             0146C0690 000000000 000000000
                                                   000000000
opiino()+1079        CALL???  opitsk()             000000007 000000000 01630F200
                                                   01630E970
opiodr()+1601        CALL???  opiino()             00000003C 000000000 01630F470
                                                   000000000
opidrv()+842         CALL???  opiodr()             00000003C 000000004 01630F470
                                                   000000000
sou2o()+94           CALL???  opidrv()+156         10000003C 7FE00000004
                                                   01630F470 0154E6A30
opimai_real()+276    CALL???  sou2o()              1D4851F4C467583 00A9D55E0
                                                   8001A000B07E2 1004B0039001E
opimai()+170         CALL???  opimai_real()        000000000 851F2CB1B179
                                                   00A9D55E0 01630F628
OracleThreadStart()  CALL???  opimai()             000000000 149031F90 000000050
+713                                               0000005C8
00000000775259CD     CALL???  OracleThreadStart()  000000000 000000000 000000000
                                                   000000000
000000007765A561     CALL???  00000000775259C0     000000000 000000000 000000000
                                                   000000000
--------------------- Binary Stack Dump ---------------------

这个错误和以往版本中的kccpb_sanity_check_2比较类似,由于数据库异常关闭导致ctl写丢失导致
ORA-600 2131/kccpb_sanity_check_2解释

DESCRIPTION:
  This internal error is raised when the sequence number (seq#) of the
  current block of the controlfile is greater than the seq# in the controlfile header.
  The header value should always be equal to, or greater than the value
  held in the control file block(s).
  This extra check was introduced in Oracle 10gR2 to detect lost writes
  or stale reads to the header.
ARGUMENTS:
  Arg [a] seq# in control block header.
  Arg [b] seq# in the control file header.
  Arg 
FUNCTIONALITY:
  Kernel Cache layer Control file component.

GANDCRAB V5.0.4 比特币加密oracle数据库恢复

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

标题：GANDCRAB V5.0.4 比特币加密oracle数据库恢复

接到朋友的恢复请求,win服务器文件被GANDCRAB V5.0.4的比特币勒索加密的oracle数据库（中联his[大量中文表名/xml类型]）,让我们对其分析,判断是否可以恢复

通过工具对其分析,发现需要是文件头和数据文件空间使用位图相关block进行重构,主要业务数据理论上应该是好的.通过分析数据库表空间、数据文件等相关的数据库基础信息,通过人工重构,重建控制文件,经过一系列恢复,数据库强制open成功

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORCLNEW1\SYSTEM01.DBF.HKNWFZ
E:\ORCLNEW1\SYSAUX01.DBF.HKNWFZ
E:\ORCLNEW1\UNDOTBS01.DBF.HKNWFZ
E:\ORCLNEW1\USERS01.DBF.HKNWFZ
E:\ORCLNEW1\BHDATA.DBF.HKNWFZ
E:\ORCLNEW1\BHMAIL.DBF.HKNWFZ
E:\ORCLNEW1\BHINDEX.DBF.HKNWFZ
E:\ORCLNEW1\ZHBASIS.DBF.HKNWFZ
E:\ORCLNEW1\ZHARCHIVES.DBF.HKNWFZ
E:\ORCLNEW1\ZHSERVICES.DBF.HKNWFZ
E:\ORCLNEW1\ZHADVICES.DBF.HKNWFZ
E:\ORCLNEW1\ZHEXPENSES.DBF.HKNWFZ
E:\ORCLNEW1\ZHMEDICINE.DBF.HKNWFZ
E:\ORCLNEW1\ZHLAB.DBF.HKNWFZ
E:\ORCLNEW1\ZHCHECK.DBF.HKNWFZ
E:\ORCLNEW1\ZHLOB.DBF.HKNWFZ
E:\ORCLNEW1\ZHINDEX.DBF.HKNWFZ
E:\ORCLNEW1\SLREPORT.DBF.HKNWFZ
E:\ORCLNEW1\ZHMATERIAL.DBF.HKNWFZ
E:\ORCLNEW1\ZHMEDREC.DBF.HKNWFZ
E:\ORCLNEW1\ZHINSURE.DBF.HKNWFZ

由于该客户的数据库中有大量的xml列类型,导致exp无法导出,只能使用expdp进行导出,因为expdp在导出过程中会创建中间表,因此又对数据库进行一些修复,确定数据库能够正常写入对象,并且数据库导出成功

ORA-00600 dbkif_find_next_record_1

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

标题：ORA-00600 dbkif_find_next_record_1

数据库版本信息

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

数据库启动报ORA-00600 dbkif_find_next_record_1错误

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 133 KB redo, 72 data blocks need recovery
Started redo application at
 Thread 1: logseq 49070, block 14720
Recovery of Online Redo Log: Thread 1 Group 2 Seq 49070 Reading mem 0
  Mem# 0: D:\APP\xff\ORADATA\ORCL\REDO02.LOG
Completed redo application of 0.09MB
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc  (incident=477756):
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\xff\diag\rdbms\orcl\orcl\incident\incdir_477756\orcl_ora_2340_i477756.trc
Wed Sep 26 10:52:07 2018
Trace dumping is performing id=[cdmp_20180926105207]
Wed Sep 26 10:52:08 2018
Aborting crash recovery due to error 600
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc:
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc:
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...

这里比较明显，数据库是在做实例恢复的时候遭遇到ORA-600 dbkif_find_next_record_1错误,无法正常应用日志导致该错误,具体原因由于:the NAB and finds it is less than the block# from the on-disk-RBA recorded，对于这种问题，通过人工修改next available block# in the Online Redo Log的相关记录,即可正常open数据库,而且理论上数据0丢失

记录正常open库报ORA-600 2662

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

标题：记录正常open库报ORA-600 2662

数据库版本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

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

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

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

由于字典被人恶意进行损坏,进行了一系列恢复之后,数据库依旧无法正常启动,而且出现比较诡异的错误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’));
),再重启系统数据库,恢复正常

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

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

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

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

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

一个朋友的数据库,由于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.

truncate table 无论drop storage还是reuse storage不影响数据恢复

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

标题：truncate table 无论drop storage还是reuse storage不影响数据恢复

今天有朋友咨询,truncate table有drop storage和reuse storage方式,是否影响后续的数据恢复(在没有其他覆盖的情况下),我做了一个简单的测试证明,这些都不影响truncate table的数据库恢复
创建测试环境

SQL> create table t_xifenfei tablespace USERNEW
  2   as select * from dba_objects;
Table created.
SQL> create table t_xifenfei2  tablespace USERNEW
  2   as select * from dba_objects;
Table created.
SQL>  create table t_xifenfei3  tablespace USERNEW
  2  as select * from dba_objects;
Table created.
SQL> alter system checkpoint;
System altered.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     86348
SQL>  select count(*) from t_xifenfei2;
  COUNT(*)
----------
     86349
SQL>  select count(*) from t_xifenfei3;
  COUNT(*)
----------
     86350
SQL> select object_id,data_object_id,object_name from dba_objects where object_name like 't_xifenfei%';
 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
     88205          88205 t_xifenfei
     88206          88206 t_xifenfei2
     88207          88207 t_xifenfei3
SQL> truncate table t_xifenfei;
Table truncated.
SQL> truncate table t_xifenfei2 drop storage;
Table truncated.
SQL> truncate table t_xifenfei3 reuse storage;
Table truncated.

使用dul进行恢复

DUL>  bootstrap;
DUL> scan database;
scanning database...
scanning database finished.
DUL> unload table sys.t_xifenfei object 88205;
Unloading table: t_xifenfei,object ID: 88205
Unloading segment,storage(Obj#=88205 DataObj#=88205 TS#=6 File#=5 Block#=1410 Cluster=0)
86348 rows unloaded
DUL> unload table sys.t_xifenfei2 object 88206;
Unloading table: t_xifenfei2,object ID: 88206
Unloading segment,storage(Obj#=88206 DataObj#=88206 TS#=6 File#=5 Block#=2690 Cluster=0)
86349 rows unloaded
DUL> unload table sys.t_xifenfei3 object 88207;
Unloading table: t_xifenfei3,object ID: 88207
Unloading segment,storage(Obj#=88207 DataObj#=88207 TS#=6 File#=5 Block#=3970 Cluster=0)
86350 rows unloaded

这里证明truncate table不管是drop storage还是reuse storage或者默认,在没有被覆盖的情况下，数据均完全恢复出来

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