一 总体描述
本实验模拟数据库归档模式下的日志组丢失某个member,在数据库一致性关闭与非一致性关闭的情况下对数据库恢复的情况,因日志组有多个member,所以此实验属非关键性故障模拟.
二 操作环境
OS
$cat /etc/redhat-releaseRed 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 150COL 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-732495/