联系：手机/微信(+86 17813235971) QQ(107644445)
标题：commit_wait和commit_logging设置不当导致数据库无法正常启动
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户数据库设置以下参数，突然掉电之后，数据库无法正常启动
commit_wait = "NOWAIT" commit_logging = "BATCH"
数据库open报错
alter database open Block change tracking file is current. Beginning crash recovery of 1 threads parallel recovery started with 31 processes Started redo scan Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc (incident=8002955): ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51 ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log' Aborting crash recovery due to error 399 Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc: ORA-00399: corrupt change description in redo log ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51 ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log' Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc: ORA-00399: corrupt change description in redo log ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51 ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log' ORA-399 signalled during: alter database open...
报错信息比较明显是由于redo损坏导致,尝试强制open库
Sun Mar 20 18:32:35 2022 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 17372812227456 Resetting resetlogs activation ID 1627598093 (0x61032d0d) Sun Mar 20 18:34:08 2022 Setting recovery target incarnation to 2 Sun Mar 20 18:34:08 2022 Initializing SCN for created control file Database SCN compatibility initialized to 3 Warning - High Database SCN: Current SCN value is 17372812227459, threshold SCN value is 0 If you have not previously reported this warning on this database, please notify Oracle Support so that additional diagnosis can be performed. Sun Mar 20 18:34:08 2022 Assigning activation ID 1627615603 (0x61037173) Thread 1 opened at log sequence 1 Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun Mar 20 18:34:08 2022 SMON: enabling cache recovery Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc (incident=8003142): ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc (incident=8003143): ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10 ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log' ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003143/orcl_ora_14369_i8003143.trc Errors in file /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc: ORA-00399: corrupt change description in redo log ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10 ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log' ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc (incident=8003144): ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10 ORA-00334: archived log: '/media/oracle/redolog/redo02.log' ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003144/orcl_ora_14369_i8003144.trc Sun Mar 20 18:34:11 2022 ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [4044], [3964482444], [4044], [3964488833], [12669344], ORA-00600: internal error code, arguments: [2662], [4044], [3964482443], [4044], [3964488833], [12669344], ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344],
强制open数据库报ora-600 2662错误,比较常见,通过修改scn再尝试open库
Sun Mar 20 18:38:43 2022
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 17372812227460
Resetting resetlogs activation ID 1627615603 (0x61037173)
Sun Mar 20 18:40:02 2022
Setting recovery target incarnation to 2
Sun Mar 20 18:40:02 2022
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 17372812227463, threshold SCN value is 0
If you have not previously reported this warning on this database,
please notify Oracle Support so that additional diagnosis can be performed.
Sun Mar 20 18:40:02 2022
Assigning activation ID 1627669665 (0x610444a1)
Thread 1 opened at log sequence 1
Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 20 18:40:02 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:779809538 end:779809788 diff:250 (2 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
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Mar 20 18:40:04 2022
QMNC started with pid=55, OS id=16232
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Mar 20 18:40:05 2022
db_recovery_file_dest_size of 3882 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.
Sun Mar 20 18:40:05 2022
Starting background process CJQ0
Sun Mar 20 18:40:05 2022
CJQ0 started with pid=58, OS id=16251
Completed: alter database open resetlogs
后续增加temp,导出数据到新库,恢复完成