ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 归档模式下的日志组丢member恢复

归档模式下的日志组丢member恢复

原创 Linux操作系统 作者:ilsyx 时间:2012-06-11 20:09:07 0 删除 编辑

一 总体描述

本实验模拟数据库归档模式下的日志组丢失某个member,在数据库一致性关闭与非一致性关闭的情况下对数据库恢复的情况,因日志组有多个member,所以此实验属非关键性故障模拟.

二 操作环境

OS

$cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.6 (Tikanga)
$uname -a
Linux stu00 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux

DB

SQL> set lines 150
COL PRODUCT FORMAT A55
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;
archive log list;SQL> SQL> SQL> SQL>
PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  10.2.0.4.0      Production
Oracle Database 10g Enterprise Edition                  10.2.0.4.0      Prod
PL/SQL                                                  10.2.0.4.0      Production
TNS for Linux:                                          10.2.0.4.0      Production

SQL>
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle
Oldest online log sequence     111
Next log sequence to archive   114
Current log sequence           114
SQL>

other

三 结构设计

a 模拟环境

1.向各日志组中添加member (每组至少一个) (此步文档中略掉,添加成员例句: alter database add logfile member  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log' to group 2; )
2.操作系统中使用rm命令删除当前日志组下的某个member.
3.查看状态

b 在2的条件下以shutdown immediate的形式关闭数据库,启动数据库进行恢复.

c 在2的条件下以shutdown abort的形式关闭数据库,启动数据库进行恢复.


四 详细步骤

a模拟日志组某一成员丢失

a.1 查看数据库当前日志组及日志组成员信息.(每组两个member)
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ---------
         1          1        127   52428800          2 YES INACTIVE              1476205 10-JUN-12
         2          1        129   52428800          2 NO  CURRENT               1476209 10-JUN-12
         3          1        126   52428800          2 YES INACTIVE              1476203 10-JUN-12
         5          1        128   62914560          2 YES INACTIVE              1476207 10-JUN-12

SQL> select * from v$logfile;

    GROUP# STATUS          TYPE    MEMBER                                                                           IS_
---------- --------------- ------- -------------------------------------------------------------------------------- ---
         1                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log                      NO
         5                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log                      NO
         3                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log                     NO
         2                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log                     NO
         1                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log                    NO
         2                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log                    NO
         3                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log                    NO
         5                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log                    NO

8 rows selected.

SQL>


a.2 使用操作系统命令删除当前日志组的一个member
SQL> !rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log

a.3 此时使用日志切换,aler日志中会出现以下信息
Sun Jun 10 22:21:26 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_arc1_4140.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

a.4 多次切换日志组,直到将丢失member的日志组状态切换到current
SQL> alter system switch logfile;
....
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ---------
         1          1        135   52428800          2 YES ACTIVE                1476696 10-JUN-12
         2          1        137   52428800          2 NO  CURRENT               1476704 10-JUN-12
         3          1        134   52428800          2 YES ACTIVE                1476461 10-JUN-12
         5          1        136   62914560          2 YES ACTIVE                1476702 10-JUN-12

SQL>

(以上环境模拟完成)

b.在a的基础上一致性关库的恢复

一致性的停止数据库,再启动数据库,查看日志组member状态.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1267068 bytes
Variable Size             125831812 bytes
Database Buffers          155189248 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL>
状态正常,去看看alert日志中都有什么记录.
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Sun Jun 10 22:32:27 2012
ALTER DATABASE OPEN
Sun Jun 10 22:32:27 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=4618
Sun Jun 10 22:32:28 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=4620
Sun Jun 10 22:32:28 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_lgwr_4598.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sun Jun 10 22:32:28 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_lgwr_4598.trc:
ORA-00321: log 2 of thread 1, cannot update log file header
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log'
Sun Jun 10 22:32:28 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_lgwr_4598.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Sun Jun 10 22:32:29 2012
Thread 1 opened at log sequence 137
  Current log# 2 seq# 137 mem# 1: /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log
Successful open of redo thread 1
alert日志记录中依然是日志文件丢失,找不到相应的日志文件.

b.1 此时尝试执行恢复日志操作会报错,因丢失日志组member的状态在current.
SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance testb (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log'

SQL>
b.2 进行日志切换,然后再执行日志组的恢复,日志恢复成功.(在切换日志之前应该查看一下日志状态,这里忘记查看了,后面实验补上)
SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance testb (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log'


SQL> alter system switch logfile;

System altered.

SQL>  alter database clear unarchived logfile group 2;

Database altered.

SQL>

c.在a的基础上非一致性关库的恢复

非一致性的关闭数据库,再启动数据库,查看日志组member状态.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1267068 bytes
Variable Size             125831812 bytes
Database Buffers          155189248 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        135   52428800          2 YES INACTIVE               1476696 10-JUN-12
         2          1        137   52428800          2 YES INACTIVE               1476704 10-JUN-12
         3          1        138   52428800          2 NO  CURRENT                1496929 10-JUN-12
         5          1        136   62914560          2 YES INACTIVE               1476702 10-JUN-12

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log                      NO
         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log                      NO
         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log                     NO
         2 STALE   ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log                     NO
         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log                    NO
         2 INVALID ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log                    NO
         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log                    NO
         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log                    NO

8 rows selected.

此时alert日志启动部分内容(与一致性停库一样,这里不记录了)

c.1 由于此时丢失member的日志组状态已不是current,因此执行重建日志命令可以成功,记录如下.
SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log                      NO
         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log                      NO
         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log                     NO
         2         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log                     NO
         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log                    NO
         2         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log                    NO
         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log                    NO
         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log                    NO

8 rows selected.

SQL>
此时查看操作系统日志文件已经创建回来.

五 个人总结

在归档日志下丢失redolog组的member,当发生日志切换时会向alert日志中添加错误信息.ora-00313,ora-00312.虽然丢失日志组某一个成员在无论是数据库一致性关闭还是数据库不一致性关闭的情况都对数据库没有影响,也不影响数据库的正常使用,如若放任错误不管,当发生某一日志组成员全部丢失时,做恢复操作就要麻烦的多.所以问题发生就要及早解决.做到平时用监控工具时刻"盯"着alert的报错.

六 资料参考引用

Loss Of Online Redo Log And ORA-312 And ORA-313 [ID 117481.1]


 
  
 

 
  Normal
  0
  
  
  
  7.8 磅
  0
  2
  
  false
  false
  false
  
  EN-US
  ZH-CN
  X-NONE

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

请登录后发表评论 登录
全部评论

注册时间:2009-06-12

  • 博文量
    194
  • 访问量
    578484