Windows上装的11.2.0.3的库,由于异常断电,致使一个current日志文件被损坏,导致数据库无法正常启动(错误代码为ORA-00333),客户经过baidu搜索,在没有备份的情况下,已经清除了所有的redo,还加了参数_allow_resetlogs_corruption= TRUE尝试恢复,接手时是由于出现了ORA-600 2662的错误,导致数据库无法打开。
alert日志内容
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production. Windows NT Version V6.1 Service Pack 1 db_recovery_file_dest_size= 4122M _allow_resetlogs_corruption= TRUE ... ... Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\trace\hbdb_ora_6012.trc: ORA-00333: 重做日志读取块 232129 计数 5441 出错 Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\trace\hbdb_ora_6012.trc: ORA-00333: 重做日志读取块 232129 计数 5441 出错 ORA-333 signalled during: alter database open... ... ... alter database clear unarchived logfile group 7 Clearing online log 7 of thread 1 sequence number 30635 ... ... SMON: enabling cache recovery Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\trace\hbdb_ora_3292.trc (incident=147769): ORA-00600: ??????, ??: [2662], [0], [1598856969], [0], [1599017925], [12583040], [], [], [], [], [], [] Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\incident\incdir_147769\hbdb_ora_3292_i147769.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 E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\trace\hbdb_ora_3292.trc: ORA-00600: ??????, ??: [2662], [0], [1598856969], [0], [1599017925], [12583040], [], [], [], [], [], [] Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\trace\hbdb_ora_3292.trc: ORA-00600: ??????, ??: [2662], [0], [1598856969], [0], [1599017925], [12583040], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 3292): terminating the instance due to error 600 Instance terminated by USER, pid = 3292
ora-600 【2662】后参数的解释
ARGUMENTS: Arg [a] Current SCN WRAP Arg [b] Current SCN BASE Arg [c] dependent SCN WRAP Arg [d] dependent SCN BASE Arg [e] Where present this is the DBA where the dependent SCN came from.就是说这个块来自何处
一般来说2662出现,说明当前数据库中某些数据块的SCN早于当前的SCN,这个数据块的地址可以从第五个参数获得。这个数据库来自undo表空间的可能性最大。如果ORA-600 2662错误中SCN差别(第五个参数 – 第三个参数)很小,可以通过多次打开关闭数据库实例的方式来增加SCN;如果比较大,可以通过别的方式推进SCN。之后接着会出现ORA-4193/ORA-4194的错误,这些与undo有关系。
12583040对应的文件及块编号
AODU> rdba 12583040 rdba : 0x00c00080=12583040 (dba=3,128) rfile# : 3 block# : 128 Dump Block : alter system dump datafile 3 block 128; AODU>
select dbms_utility.data_block_address_file(16777954) "file",dbms_utility.data_block_address_block(16777954) "block" from dual;
解决办法
通过使用_minimum_giga_scn,这个在11.2.0.3中使用没有问题,通过alert日志,我们可以看到Advancing SCN to 3221225472 according to _minimum_giga_scn,下面是alert日志内容
db_block_size = 8192 _minimum_giga_scn = 3 compatible = "11.2.0.0.0" alter database open Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 0 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 9, block 3, scn 1598997014 Recovery of Online Redo Log: Thread 1 Group 2 Seq 9 Reading mem 0 Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\hbdb\REDO02.LOG Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 9, block 3, scn 1599017015 0 data blocks read, 0 data blocks written, 0 redo k-bytes read Advancing SCN to 3221225472 according to _minimum_giga_scn SCN被推倒3221225472 Thread 1 advanced to log sequence 10 (thread open) Thread 1 opened at log sequence 10 Current log# 3 seq# 10 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\hbdb\REDO03.LOG Successful open of redo thread 1 SMON: enabling cache recovery [5732] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:20514927 end:20515270 diff:343 (3 seconds) Dictionary check beginning Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active
2662解决,4193出现
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\trace\hbdb_smon_3836.trc (incident=160929): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\incident\incdir_160929\hbdb_smon_3836_i160929.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=22, OS id=4080 Dumping diagnostic data in directory=[cdmp_20170216231122], requested by (instance=1, osid=3836 (SMON)), summary=[incident=160929]. LOGSTDBY: Validating controlfile with logical metadata Block recovery from logseq 10, block 56 to scn 3221225750 Recovery of Online Redo Log: Thread 1 Group 3 Seq 10 Reading mem 0 Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\hbdb\REDO03.LOG LOGSTDBY: Validation complete Block recovery stopped at EOT rba 10.57.16 Block recovery completed at rba 10.57.16, scn 0.3221225749 Block recovery from logseq 10, block 56 to scn 3221225747 Recovery of Online Redo Log: Thread 1 Group 3 Seq 10 Reading mem 0 Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\hbdb\REDO03.LOG Block recovery completed at rba 10.57.16, scn 0.3221225749 Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\trace\hbdb_smon_3836.trc: ORA-01595: error freeing extent (2) of rollback segment (1)) ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\trace\hbdb_ora_5080.trc (incident=160993): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], [] Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\hbdb\hbdb\incident\incdir_160993\hbdb_ora_5080_i160993.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x4D1B3AD4] [PC:0x10845FE, kgegpa()+38] Dump file e:\app\administrator\diag\rdbms\hbdb\hbdb\trace\alert_hbdb.log
4193/4194的解释
ORA-600 [4194] [a] [b] A mismatch has been detected between Redo records and rollback (Undo) records. We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block. This error is reported when the validation fails. ARGUMENTS: Arg [a] Maximum Undo record number in Undo block Arg [b] Undo record number from Redo block ORA-600 [4193] [a] [b] A mismatch has been detected between Redo records and Rollback (Undo) records. We are validating the Undo block sequence number in the undo block against the Redo block sequence number relating to the change being applied. This error is reported when this validation fails. ARGUMENTS: Arg [a] Undo record seq number Arg [b] Redo record seq number
解决ORA-4193
使用下面的两个参数 undo_management = "MANUAL" undo_tablespace = "SYSTEM" Starting background process QMNC QMNC started with pid=22, OS id=5812 Completed: alter database open Starting background process CJQ0 CJQ0 started with pid=28, OS id=5588 db_recovery_file_dest_size of 4122 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.
幸运的话,你可以导出数据,然后重新建库,再导入数据
注意:本文仅供参考,请根据实际情况实际对待
最终用到的参数
_allow_resetlogs_corruption = TRUE
_minimum_giga_scn = 3
undo_management = "MANUAL"
undo_tablespace = "SYSTEM"