11.2.0.4 ASM RAC 恢复一个例子
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 11.2.0.4 ASM RAC 恢复一个例子
这是一个朋友的客户的数据库，数据库出故障之后，无法顺利打开，如下是数据库在open的时候所报的错误:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /space/sys_software/oracle/app/product/11.2.0/db_1/dbs/arch1_1_885005686.dbf Errors with log /space/sys_software/oracle/app/product/11.2.0/db_1/dbs/arch1_1_885005686.dbf Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_pr00_43377.trc: ORA-00308: cannot open archived log '/space/sys_software/oracle/app/product/11.2.0/db_1/dbs/arch1_1_885005686.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CANCEL Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_pr00_43377.trc: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+RDBDATADG/bexasmdb/datafile/system.dbf' Slave exiting with ORA-1547 exception Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_pr00_43377.trc: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+RDBDATADG/bexasmdb/datafile/system.dbf' ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ... |
我们可以看到，通过不完全恢复之后，通过加入隐含参数强制拉库，发现仍然报如下的错误：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Thu Jul 16 07:21:58 2015 SMON: enabling cache recovery ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x002c.880f33fc): select ctime, mtime, stime from obj$ where obj# = :1 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_40577.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 503 with name "_SYSSMU503_2368473065$" too small Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_40577.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 503 with name "_SYSSMU503_2368473065$" too small Error 704 happened during db open, shutting down database USER (ospid: 40577): terminating the instance due to error 704 Thu Jul 16 07:21:59 2015 opiodr aborting process unknown ospid (45403) as a result of ORA-1092 Instance terminated by USER, pid = 40577 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (40577) as a result of ORA-1092 Thu Jul 16 07:22:10 2015 ORA-1092 : opitsk aborting process |
据朋友讲，多次尝试之后仍然报上述错误，我建议通过10046 trace发现如下的几个block有问题：
|
1 2 3 |
WAIT #139668522497552: nam='db file sequential read' ela= 234 file#=1 block#=122911 blocks=1 obj#=36 tim=1436836317152403 WAIT #139668522497552: nam='db file sequential read' ela= 245 file#=1 block#=338 blocks=1 obj#=36 tim=1436836317152765 WAIT #139668522497552: nam='db file sequential read' ela= 160 file#=1 block#=241 blocks=1 obj#=18 tim=1436836317153036 |
通过bbed 检查发生上述几个block，发现确实存在活动事务。 通过bbed手工提交事务之后，尝试open发现报如下错误：
|
1 2 3 4 5 6 7 8 9 10 |
Thu Jul 16 07:39:52 2015 Media Recovery failed with error 16433 Slave exiting with ORA-283 exception Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_pr00_48904.trc: ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode. Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ... Thu Jul 16 07:40:08 2015 Shutting down instance (abort) |
这个错误其实很简单，是因为需要重建一下控制文件，然后再次尝试open数据库即可。不幸的是，再次open发现报ORA-00600 [2662]错误：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
hu Jul 16 07:51:11 2015 SMON: enabling cache recovery Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_50314.trc (incident=1796477): ORA-00600: internal error code, arguments: [2662], [44], [2282697729], [44], [2503605680], [4194545], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_1796477/bexasmdb1_ora_50314_i1796477.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 /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_50314.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [2662], [44], [2282697729], [44], [2503605680], [4194545], [], [], [], [], [], [] Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_50314.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [2662], [44], [2282697729], [44], [2503605680], [4194545], [], [], [], [], [], [] Error 704 happened during db open, shutting down database USER (ospid: 50314): terminating the instance due to error 704 Instance terminated by USER, pid = 50314 |
由于他这里的环境是11.2.0.4版本，因此老的推进scn的方式已经不行了，后面我建议通过oradebug 直接修改scn来拉库，如下：
|
1 |
oradebug poke 0x060019598 8 0x37881E7641 |
通过上述命令修改之后，再次进行open，发现顺利打开数据库：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
Thu Jul 16 08:35:26 2015 Setting recovery target incarnation to 2 Thu Jul 16 08:35:26 2015 Assigning activation ID 1153859453 (0x44c67f7d) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: +RDBDATADG/bexasmdb/onlinelog/redo_g01t01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Thu Jul 16 08:35:26 2015 SMON: enabling cache recovery [59018] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:2407522882 end:2407523992 diff:1110 (11 seconds) Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery ********************************************************************* WARNING: The following temporary tablespaces contain no files. This condition can occur when a backup controlfile has been restored. It may be necessary to add files to these tablespaces. That can be done using the SQL statement: ALTER TABLESPACE <tablespace_name> ADD TEMPFILE Alternatively, if these temporary tablespaces are no longer needed, then they can be dropped. Empty temporary tablespace: TEMP ********************************************************************* Database Characterset is AL32UTF8 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_smon_58973.trc (incident=2076389): ORA-00600: internal error code, arguments: [4137], [474.1.368214], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2076389/bexasmdb1_smon_58973_i2076389.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Not initializing the resource manager because _resource_manager_always_on=FALSE replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Thu Jul 16 08:35:29 2015 QMNC started with pid=36, OS id=65502 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete ORACLE Instance bexasmdb1 (pid = 22) - Error 600 encountered while recovering transaction (474, 1). Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_smon_58973.trc: ORA-00600: internal error code, arguments: [4137], [474.1.368214], [0], [0], [], [], [], [], [], [], [], [] Thu Jul 16 08:35:29 2015 Dumping diagnostic data in directory=[cdmp_20150716083529], requested by (instance=1, osid=58973 (SMON)), summary=[incident=2076389]. Thu Jul 16 08:35:29 2015 Sweep [inc][2076389]: completed Thu Jul 16 08:35:29 2015 Sweep [inc2][2076389]: completed Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x42E37DA4] [PC:0x932F97E, kgegpa()+40] [flags: 0x0, count: 1] Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x42E37DA4] [PC:0x932DF87, kgebse()+771] [flags: 0x2, count: 2] Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x42E37DA4] [PC:0x932DF87, kgebse()+771] [flags: 0x2, count: 2] Thu Jul 16 08:35:30 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_mmon_58981.trc (incident=2076421): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2076421/bexasmdb1_mmon_58981_i2076421.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Jul 16 08:35:30 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_65525.trc (incident=2076549): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Thu Jul 16 08:35:30 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_65527.trc (incident=2076557): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2076549/bexasmdb1_ora_65525_i2076549.trc Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2076557/bexasmdb1_ora_65527_i2076557.trc Thu Jul 16 08:35:30 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_65529.trc (incident=2076533): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Dumping diagnostic data in directory=[cdmp_20150716083530], requested by (instance=1, osid=58973 (SMON)), summary=[abnormal process termination].Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2076533/bexasmdb1_ora_65529_i2076533.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Jul 16 08:35:31 2015 Block recovery from logseq 1, block 380 to scn 238506899377 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0 Mem# 0: +RDBDATADG/bexasmdb/onlinelog/redo_g01t01.log Block recovery completed at rba 1.416.16, scn 55.2283698098 Block recovery from logseq 1, block 380 to scn 238506899350 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0 Mem# 0: +RDBDATADG/bexasmdb/onlinelog/redo_g01t01.log Block recovery completed at rba 1.382.16, scn 55.2283698072 Thu Jul 16 08:35:31 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_65583.trc (incident=2076677): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2076677/bexasmdb1_ora_65583_i2076677.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Dumping diagnostic data in directory=[cdmp_20150716083532], requested by (instance=1, osid=65529), summary=[incident=2076533]. Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_65529.trc (incident=2076534): ORA-00600: internal error code, arguments: [504], [0x06000F0F0], [1], [0], [ksv instance latch], [0], [0], [0x2FC57D92F8], [], [], [], [] ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] ...... ...... Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_65525.trc: ORA-00600: internal error code, arguments: [504], [0x06000F0F0], [1], [0], [ksv instance latch], [0], [0], [0x2FC57D9438], [], [], [], [] ORA-00600: internal error code, arguments: [504], [0x06000F0F0], [1], [0], [ksv instance latch], [0], [0], [0x2FC57D9438], [], [], [], [] ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x3BAF488E] [PC:0x932F97E, kgegpa()+40] [flags: 0x0, count: 1] Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x3BAF488E] [PC:0x932DF87, kgebse()+771] [flags: 0x2, count: 2] Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_mmon_58981.trc (incident=2076428): ORA-00603: ORACLE server session terminated by fatal error ORA-24557: error 600 encountered while handling error 600; exiting server process ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] |
虽然数据库能够打开，据朋友反应，很快数据库就会挂掉。从上述日志来看，open之后报错undo 相关错误。这就更容易处理了。通过undo_management参数改成manual即可，然后open数据库，重建undo表空间，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 |
hu Jul 16 08:40:43 2015 QMNC started with pid=72, OS id=66934 Completed: ALTER DATABASE OPEN Thu Jul 16 08:40:44 2015 minact-scn: got error during useg scan e:1555 usn:405 minact-scn: useg scan erroring out with error e:1555 ORACLE Instance bexasmdb1 (pid = 22) - Error 600 encountered while recovering transaction (405, 33). Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_smon_66678.trc: ORA-00600: internal error code, arguments: [4137], [405.33.408826], [0], [0], [], [], [], [], [], [], [], [] Thu Jul 16 08:40:44 2015 Dumping diagnostic data in directory=[cdmp_20150716084044], requested by (instance=1, osid=66678 (SMON)), summary=[incident=2196402]. Thu Jul 16 08:40:44 2015 Starting background process CJQ0 Thu Jul 16 08:40:44 2015 CJQ0 started with pid=88, OS id=66995 Dumping diagnostic data in directory=[cdmp_20150716084045], requested by (instance=1, osid=66678 (SMON)), summary=[abnormal process termination]. Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_smon_66678.trc (incident=2196403): ORA-00600: internal error code, arguments: [4137], [408.23.372933], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2196403/bexasmdb1_smon_66678_i2196403.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Jul 16 08:40:45 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_67001.trc (incident=2196954): ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2196954/bexasmdb1_ora_67001_i2196954.trc Thu Jul 16 08:40:45 2015 Sweep [inc][2196402]: completed Thu Jul 16 08:40:45 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_m000_66993.trc: ORA-25153: Temporary Tablespace is Empty ORACLE Instance bexasmdb1 (pid = 22) - Error 600 encountered while recovering transaction (408, 23). Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_smon_66678.trc: ORA-00600: internal error code, arguments: [4137], [408.23.372933], [0], [0], [], [], [], [], [], [], [], [] Thu Jul 16 08:40:46 2015 Sweep [inc][2196403]: completed Dumping diagnostic data in directory=[cdmp_20150716084046], requested by (instance=1, osid=66678 (SMON)), summary=[incident=2196403]. 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 /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_smon_66678.trc (incident=2196404): ORA-00600: internal error code, arguments: [4137], [411.5.413464], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2196404/bexasmdb1_smon_66678_i2196404.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Jul 16 08:40:47 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_67019.trc (incident=2196962): ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2196962/bexasmdb1_ora_67019_i2196962.trc Dumping diagnostic data in directory=[cdmp_20150716084047], requested by (instance=1, osid=66678 (SMON)), summary=[abnormal process termination]. ORACLE Instance bexasmdb1 (pid = 22) - Error 600 encountered while recovering transaction (411, 5). Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_smon_66678.trc: ORA-00600: internal error code, arguments: [4137], [411.5.413464], [0], [0], [], [], [], [], [], [], [], [] Thu Jul 16 08:40:48 2015 Sweep [inc][2196404]: completed Thu Jul 16 08:40:48 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j002_67033.trc: ORA-12012: error on auto execute of job 25 ORA-01552: cannot use system rollback segment for non-system tablespace 'SPC_SDB_SOD_DATA' ORA-06512: at "USR_SOD.OPERDEL", line 3 ORA-06512: at line 1 Dumping diagnostic data in directory=[cdmp_20150716084048], requested by (instance=1, osid=66678 (SMON)), summary=[incident=2196404]. Thu Jul 16 08:40:48 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j004_67043.trc: ORA-12012: error on auto execute of job 103 ORA-01552: cannot use system rollback segment for non-system tablespace 'SPC_SDB_DPC_DATA' ORA-06512: at "USR_DPC.DPC_PARTITION_DEL", line 95 ORA-01552: cannot use system rollback segment for non-system tablespace 'SPC_SDB_DPC_DATA' ORA-06512: at line 1 Thu Jul 16 08:40:48 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j005_67045.trc: ORA-12012: error on auto execute of job 85 ORA-01552: cannot use system rollback segment for non-system tablespace 'SPC_SDB_DPC_DATA' ORA-06512: at "USR_DPC.DPC_PARTITION_ADD", line 154 ORA-01552: cannot use system rollback segment for non-system tablespace 'SPC_SDB_DPC_DATA' ORA-06512: at line 1 Thu Jul 16 08:40:48 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j007_67049.trc: ORA-12012: error on auto execute of job 24 ORA-01552: cannot use system rollback segment for non-system tablespace 'SPC_SDB_SOD_DATA' ORA-06512: at "USR_SOD.OPERDEL", line 3 ORA-06512: at line 1 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j002_67033.trc: Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Jul 16 08:40:49 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_67055.trc (incident=2197066): ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_2197066/bexasmdb1_ora_67055_i2197066.trc Thu Jul 16 08:40:49 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j001_67030.trc: ORA-12012: error on auto execute of job 36 ORA-12008: error in materialized view refresh path ORA-01552: cannot use system rollback segment for non-system tablespace 'SPC_SDB_MCP_DATA' ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776 ORA-06512: at "SYS.DBMS_IREFRESH", line 685 ORA-06512: at "SYS.DBMS_REFRESH", line 195 ORA-06512: at line 1 ...... ...... Thu Jul 16 08:41:34 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_67263.trc (incident=2196986): ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Jul 16 08:41:35 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_67265.trc (incident=2196995): ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ...... ...... Thu Jul 16 08:42:36 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_67419.trc (incident=2197091): ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] 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 /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j002_67383.trc: ORA-12012: error on auto execute of job 4002 ORA-08102: index key not found, obj# 290, file 1, block 2033 (2) Thu Jul 16 08:42:36 2015 Sweep [inc][2197186]: completed Sweep [inc][2197179]: completed Sweep [inc][2197146]: completed Sweep [inc][2197138]: completed Sweep [inc][2197130]: completed Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_j000_67379.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 290, file 1, block 2033 (2) ORA-12012: error on auto execute of job 3 ORA-08102: index key not found, obj# 290, file 1, block 2033 (2) |
最后打开之后，仍然发现有一些问题，重建index发现都报错错误。如下：
|
1 2 3 4 5 |
SQL> CREATE INDEX "USR_MCP"."IDX_QRTZ_T_NEXT_FIRE_TIME" ON "USR_MCP"."QRTZ_TRIGGERS" ("NEXT_FIRE_TIME"); CREATE INDEX "USR_MCP"."IDX_QRTZ_T_NEXT_FIRE_TIME" ON "USR_MCP"."QRTZ_TRIGGERS" ("NEXT_FIRE_TIME") * ERROR at line 1: ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] |
这实际上是存在坏块，通过检查相关对象，发现数据字典表其实存在问题，此时检查发现alert log也存在相关错误，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
Dumping diagnostic data in directory=[cdmp_20150717024616], requested by (instance=1, osid=76873), summary=[abnormal process termination]. Fri Jul 17 02:46:20 2015 alter tablespace temp add tempfile '+RDBDATADG' size 10G autoextend on Completed: alter tablespace temp add tempfile '+RDBDATADG' size 10G autoextend on Fri Jul 17 02:46:30 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_77293.trc (incident=3236693): ORA-00600: internal error code, arguments: [ktsplbfmb-dblfree], [0], [96608622], [96608439], [183], [0], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_3236693/bexasmdb1_ora_77293_i3236693.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fri Jul 17 02:46:33 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_76873.trc: Fri Jul 17 02:46:34 2015 Sweep [inc][3236693]: completed Sweep [inc2][3236693]: completed Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_77293.trc (incident=3236694): ORA-00600: internal error code, arguments: [ktsplbfmb-dblfree], [0], [96608622], [96608439], [183], [0], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_3236694/bexasmdb1_ora_77293_i3236694.trc Fri Jul 17 02:46:37 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_76814.trc (incident=3236638): ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_3236638/bexasmdb1_ora_76814_i3236638.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 /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_77293.trc (incident=3236695): ORA-00600: internal error code, arguments: [6002], [32], [32], [2], [0], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_3236695/bexasmdb1_ora_77293_i3236695.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fri Jul 17 02:46:40 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_77293.trc (incident=3236696): ORA-00600: internal error code, arguments: [6002], [32], [32], [2], [0], [], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_3236696/bexasmdb1_ora_77293_i3236696.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 /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_77293.trc (incident=3236697): ORA-00600: internal error code, arguments: [ktsplbfmb-dblfree], [0], [96608622], [96608439], [183], [0], [], [], [], [], [], [] Incident details in: /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/incident/incdir_3236697/bexasmdb1_ora_77293_i3236697.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fri Jul 17 02:46:52 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_76873.trc: Fri Jul 17 02:46:53 2015 Errors in file /space/sys_software/oracle/app/diag/rdbms/bexasmdb/bexasmdb1/trace/bexasmdb1_ora_77293.trc (incident=3236698): |
据我分析，其实完全可以通过bbed修复obj$的index，来完成这个工作。然而朋友不熟，考虑到index结构的复杂性，因此后面直接建议他exp导出重建数据库算了。
我博客中也有相关针对ora-08102错误，修复Index的情况，请参考！类似这样专业的数据恢复，请联系我们：云和恩墨！
Leave a Reply
You must be logged in to post a comment.