1. 官方文档描述
2. 故障报错信息
3. 分析解决问题
1. 官方文档描述
关于 Clearing a Redo Log File 的官方文档描述:
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.
- The following statement clears the log files in redo log group number 3:
- ALTER DATABASE CLEAR LOGFILE GROUP 3;
- This statement overcomes two situations where dropping redo logs is not possible:
- If there are only two log groups
- The corrupt redo log file belongs to the current group
- If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.
- ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
- This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.
- If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover.
- Note:
- If you clear an unarchived redo log file, you should make another backup of the database.
To clear an unarchived redo log that is needed to bring an offline tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statement.
If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform an incomplete recovery. Note that tablespaces taken offline normal do not require recovery.
2. 故障报错信息
启动数据库在 open 阶段失败:
- SQL> startup
- ORACLE instance started.
- Total System Global Area 313860096 bytes
- Fixed Size 1344652 bytes
- Variable Size 205523828 bytes
- Database Buffers 100663296 bytes
- Redo Buffers 6328320 bytes
- Database mounted.
- ORA-03113: end-of-file on communication channel
- Process ID: 2039
- Session ID: 102 Serial number: 5
进一步查看 alert 告警日志发现是 redo 日志文件损坏:
- Mon May 20 01:00:52 2019
- alter database open
- Mon May 20 01:00:52 2019
- LGWR: STARTING ARCH PROCESSES
- Mon May 20 01:00:53 2019
- ARC0 started with pid=18, OS id=2389
- ARC0: Archival started
- LGWR: STARTING ARCH PROCESSES COMPLETE
- ARC0: STARTING ARCH PROCESSES
- Mon May 20 01:00:54 2019
- ARC1 started with pid=19, OS id=2391
- Mon May 20 01:00:54 2019
- ARC2 started with pid=20, OS id=2393
- Incomplete read from log member '/u03/oradata/PROD2/redo01.dbf'. Trying next member.
- ARCH: All Archive destinations made inactive due to error 333
- ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1_1008712962.dbf' (error 333) (PROD2)
- Committing creation of archivelog '/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1_1008712962.dbf' (error 333)
- Mon May 20 01:00:54 2019
- ARC3 started with pid=21, OS id=2395
- Errors in file /u01/App/oracle/diag/rdbms/prod2/PROD2/trace/PROD2_ora_2179.trc:
- ORA-16038: log 1 sequence# 1 cannot be archived
- ORA-00333: redo log read error block count
- ORA-00312: online log 1 thread 1: '/u03/oradata/PROD2/redo01.dbf'
- ARC1: Archival started
- ARC2: Archival started
- USER (ospid: 2179): terminating the instance due to error 16038
- Mon May 20 01:00:55 2019
- System state dump requested by (instance=1, osid=2179), summary=[abnormal instance termination].
- System State dumped to trace file /u01/App/oracle/diag/rdbms/prod2/PROD2/trace/PROD2_diag_2131.trc
- Dumping diagnostic data in directory=[cdmp_20190520010055], requested by (instance=1, osid=2179), summary=[abnormal instance termination].
- Instance terminated by USER, pid = 2179
3. 分析解决问题
3.1 再次尝试启动数据库故障可重现
- SQL> startup mount;
- SQL> select checkpoint_change# from v$datafile;
- CHECKPOINT_CHANGE#
- ------------------
- 2126114
- 2126114
- 2126114
- 2126114
- 2126114
- SQL> select checkpoint_change# from v$datafile_header;
- CHECKPOINT_CHANGE#
- ------------------
- 2126114
- 2126114
- 2126114
- 2126114
- 2126114
- SQL> alter database open;
- alter database open
- *
- ERROR at line 1:
- ORA-03113: end-of-file on communication channel
- Process ID: 2179
- Session ID: 102 Serial number: 5
3.2 启动到 mount 尝试 clear 重做日志文件
- SQL> conn /as sysdba
- Connected to an idle instance.
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area 313860096 bytes
- Fixed Size 1344652 bytes
- Variable Size 205523828 bytes
- Database Buffers 100663296 bytes
- Redo Buffers 6328320 bytes
- Database mounted.
- SQL> select * from v$log;
- GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
- 1 1 1 52428800 512 1 NO INACTIVE 2080429 19-MAY-19 2105566 20-MAY-19
- 3 1 3 52428800 512 1 NO CURRENT 2106113 20-MAY-19 2.8147E+14
- 2 1 2 52428800 512 1 NO INACTIVE 2105566 20-MAY-19 2106113 20-MAY-19
- SQL> alter database clear logfile group 1;
- alter database clear logfile group 1
- *
- ERROR at line 1:
- ORA-00350: log 1 of instance PROD2 (thread 1) needs to be archived
- ORA-00312: online log 1 thread 1: '/u03/oradata/PROD2/redo01.dbf'
- SQL> alter database clear unarchived logfile group 1;
- Database altered.
- SQL> select * from v$log;
- GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
- 1 1 0 52428800 512 1 YES UNUSED 2080429 19-MAY-19 2105566 20-MAY-19
- 3 1 3 52428800 512 1 NO CURRENT 2106113 20-MAY-19 2.8147E+14
- 2 1 2 52428800 512 1 NO INACTIVE 2105566 20-MAY-19 2106113 20-MAY-19
由于本次 3 个 redo 日志全部确认损害, 需要依次 clear:
- SQL> alter database clear logfile group 2;
- alter database clear logfile group 2
- *
- ERROR at line 1:
- ORA-00350: log 2 of instance PROD2 (thread 1) needs to be archived
- ORA-00312: online log 2 thread 1: '/u03/oradata/PROD2/redo02.dbf'
- SQL> alter database clear logfile group 3;
- alter database clear logfile group 3
- *
- ERROR at line 1:
- ORA-00350: log 3 of instance PROD2 (thread 1) needs to be archived
- ORA-00312: online log 3 thread 1: '/u03/oradata/PROD2/redo03.dbf'
- SQL> alter database clear unarchived logfile group 2;
- Database altered.
- SQL> alter database clear unarchived logfile group 3;
- Database altered.
- SQL> select * from v$log;
- GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
- 1 1 4 52428800 512 1 NO CURRENT 2106114 20-MAY-19 2.8147E+14
- 3 1 0 52428800 512 1 YES UNUSED 2106113 20-MAY-19 2106114 20-MAY-19
- 2 1 0 52428800 512 1 YES UNUSED 2105566 20-MAY-19 2106113 20-MAY-19
- SQL> alter database open;
- Database altered.
- SQL> select * from v$log;
- GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
- 1 1 4 52428800 512 1 NO CURRENT 2106114 20-MAY-19 2.8147E+14
- 2 1 0 52428800 512 1 YES UNUSED 2105566 20-MAY-19 2106113 20-MAY-19
- 3 1 0 52428800 512 1 YES UNUSED 2106113 20-MAY-19 2106114 20-MAY-19
数据库开启后建议尽快重新执行一次全备.
来源: https://www.cnblogs.com/jyzhao/p/10891892.html