ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > inactive状态日志组文件损坏的恢复

inactive状态日志组文件损坏的恢复

原创 Linux操作系统 作者：sxitsxit 时间：2013-04-27 16:16:12 0 删除 编辑
1：丢失inactive状态的日志文件
如果数据库丢失的是inactive日志组，由于inactive状态的日志组已经完成检查点，
数据库不会发生数据丢失，此时只需要通过clear重建该日志组即可
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         45 CURRENT                 879651   2.8147E+14
         2         44 ACTIVE                  879534       879651
         3         43 INACTIVE                879527       879534
        
SQL> col member for a50;
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /home/oracle/oradata/ora11g/redo03.log             NO
         2         ONLINE  /home/oracle/oradata/ora11g/redo02.log             NO
         1         ONLINE  /home/oracle/oradata/ora11g/redo01.log             NO
         1         ONLINE  /home/oracle/oradata/ora11g/redo01a.log            NO
         2         ONLINE  /home/oracle/oradata/ora11g/redo02a.log            NO
         3         ONLINE  /home/oracle/oradata/ora11g/redo03a.log            NO
6 rows selected.
        
        
2：将原来的日志组3的两个文件做备份，然后删除
SQL> !cp /home/oracle/oradata/ora11g/redo03.log /home/oracle/oradata/ora11g/redo03.log.bak
SQL> !cp /home/oracle/oradata/ora11g/redo03a.log /home/oracle/oradata/ora11g/redo03a.log.bak
SQL> !rm /home/oracle/oradata/ora11g/redo03.log
SQL> !rm /home/oracle/oradata/ora11g/redo03a.log
SQL>

3：如果数据库发生多次日志切换，使用到该日志组3时候，则数据库可能马上崩溃
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         45 CURRENT                 879651   2.8147E+14
         2         44 ACTIVE                  879534       879651
         3         43 INACTIVE                879527       879534
SQL>
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         45 ACTIVE                  879651       879780
         2         44 INACTIVE                879534       879651
         3         46 CURRENT                 879780   2.8147E+14
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         45 ACTIVE                  879651       879780
         2         47 CURRENT                 879812   2.8147E+14
         3         46 ACTIVE                  879780       879812
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         45 ACTIVE                  879651       879780
         2         47 CURRENT                 879812   2.8147E+14
         3         46 ACTIVE                  879780       879812
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         48 CURRENT                 879848   2.8147E+14
         2         47 INACTIVE                879812       879848
         3         46 INACTIVE                879780       879812
SQL>  alter system switch logfile;
（一直没有反应，数据库hang住了）
在进行多次切换之后，数据库hang住了
查看alter.log文件（v$diag_info视图中/home/oracle/product/diag/rdbms/ora11g/ora11g/trace）
有如下详细信息
Sat Apr 27 15:52:58 2013
Errors in file /home/oracle/product/diag/rdbms/ora11g/ora11g/trace/ora11g_arc0_907.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3    

4：另开一个会话，关闭db，然后启动db，启动的过程中会报错
[oracle@sp ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 27 11:08:07 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1322467328 bytes
Fixed Size                  1336316 bytes
Variable Size             452987908 bytes
Database Buffers          855638016 bytes
Redo Buffers               12505088 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 741
Session ID: 1 Serial number: 5

SQL>

5：首先启动到mount状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1322467328 bytes
Fixed Size                  1336316 bytes
Variable Size             452987908 bytes
Database Buffers          855638016 bytes
Redo Buffers               12505088 bytes
Database mounted.
查看log组的状态

SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         48 CURRENT                 879848   2.8147E+14
         3         46 INACTIVE                879780       879812
         2         47 INACTIVE                879812       879848

SQL> col member for a50;
SQL> set linesize 200;
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /home/oracle/oradata/ora11g/redo03.log             NO
         2         ONLINE  /home/oracle/oradata/ora11g/redo02.log             NO
         1         ONLINE  /home/oracle/oradata/ora11g/redo01.log             NO
         1         ONLINE  /home/oracle/oradata/ora11g/redo01a.log            NO
         2         ONLINE  /home/oracle/oradata/ora11g/redo02a.log            NO
         3         ONLINE  /home/oracle/oradata/ora11g/redo03a.log            NO
6 rows selected.
SQL>

手动删除损坏的日志组3，报错

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance ora11g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03.log'
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03a.log'

如果在归档模式下，并且损坏的日志组还没有完成归档，则需要使用clear unarchived 命令强制删除
SQL> alter database clear unarchived logfile group 3;
Database altered.
（执行完上述命令之后，在/home/oracle/oradata/ora11g/目录下
又重新自动创建了两个日志文件redo03a.log、redo03.log）
打开db

SQL>
SQL> alter database open;
Database altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         48 CURRENT                 879848   2.8147E+14
         2         47 INACTIVE                879812       879848
         3          0 UNUSED                  879780       879812
SQL>  select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /home/oracle/oradata/ora11g/redo03.log             NO
         2         ONLINE  /home/oracle/oradata/ora11g/redo02.log             NO
         1         ONLINE  /home/oracle/oradata/ora11g/redo01.log             NO
         1         ONLINE  /home/oracle/oradata/ora11g/redo01a.log            NO
         2         ONLINE  /home/oracle/oradata/ora11g/redo02a.log            NO
         3         ONLINE  /home/oracle/oradata/ora11g/redo03a.log            NO
6 rows selected.

再次切换日志组
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         48 ACTIVE                  879848       880139
         2         47 INACTIVE                879812       879848
         3         49 CURRENT                 880139   2.8147E+14
SQL>  alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         48 ACTIVE                  879848       880139
         2         50 CURRENT                 880145   2.8147E+14
         3         49 ACTIVE                  880139       880145
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         51 CURRENT                 880150   2.8147E+14
         2         50 ACTIVE                  880145       880150
         3         49 INACTIVE                880139       880145
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1         51 ACTIVE                  880150       880153
         2         50 INACTIVE                880145       880150
         3         52 CURRENT                 880153   2.8147E+14

马上对数据库做一次全备份
 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24862808/viewspace-759443/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： nested loops 和hash join的一点测试
下一篇： rac环境下修改归档路径，并设置远程传输属性
请登录后发表评论 登录
全部评论
sxitsxit

注册时间：2011-04-14

  • 博文量
    98
  • 访问量
    323764

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593