前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >记录一则clear重做日志文件的案例

记录一则clear重做日志文件的案例

作者头像
Alfred Zhao
发布2019-05-22 14:55:19
4910
发布2019-05-22 14:55:19
举报

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阶段失败:

代码语言:javascript
复制
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日志文件损坏:

代码语言:javascript
复制
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 再次尝试启动数据库故障可重现

代码语言:javascript
复制
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重做日志文件

代码语言:javascript
复制
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:

代码语言:javascript
复制
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

数据库开启后建议尽快重新执行一次全备。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2019-05-20 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1.官方文档描述
  • 2.故障报错信息
  • 3.分析解决问题
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档