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

最新推荐文章于 2021-04-15 09:30:47 发布

congji3615

最新推荐文章于 2021-04-15 09:30:47 发布

阅读量189

点赞数

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;

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;

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

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.

查看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;

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;

SQL> select * from v$logfile;

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博客 ” ，链接：http://blog.itpub.net/24862808/viewspace-759443/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24862808/viewspace-759443/

congji3615

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
inactive状态日志组文件损坏的恢复

1：丢失inactive状态的日志文件如果数据库丢失的是inactive日志组，由于inactive状态的日志组已经完成检查点，数据库不会发生数据丢失，此时只需要通过clear重建该日志组即可SQL> select...
复制链接

扫一扫