1 数据库环境
1.1 数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
1.2 日志模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 6
Current log sequence 6
1.3 实验用数据
SQL> select * from test_control;
CURR_TIME
-------------------
2019-07-21 16:25:33
2019-07-27 09:32:21
2019-07-27 09:33:39
2019-08-03 11:52:17
2019-08-03 11:53:05
2019-08-17 16:08:27
2019-08-17 16:08:29
2019-08-17 16:08:29
2019-08-17 16:08:30
2019-08-17 16:53:19
2019-08-17 16:58:00
11 rows selected.
2 当前重做日志组全部成员故障
2.1 查看当前重做日志组及成员
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 INACTIVE
3 CURRENT /* 当前重做日志组为3号
SQL> l
1* select GROUP#, MEMBER from v$logfile order by group#
SQL> /
GROUP# MEMBER
---------- ----------------------------------------------------------------
1 /u01/app/oracle/oradata/orcl11g/redo01.log
2 /u01/app/oracle/oradata/orcl11g/redo02.log
3 /u01/app/oracle/oradata/orcl11g/redo03.log /*3号组有一个成员*/
2.2 插入一些数据到测试表中
SQL> insert into test_control select sysdate from dual;
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
2.3 删除当前归档日志组成员,模拟当前日志组故障
SQL> !rm /u01/app/oracle/oradata/orcl11g/redo03.log
SQL> !ls /u01/app/oracle/oradata/orcl11g/redo03.log
ls: cannot access /u01/app/oracle/oradata/orcl11g/redo03.log: No such file or directory
2.4 提交当前事务
SQL> commit;
Commit complete.
2.5 故障处理
切换重做日志组,至删除的当前日志组时,卡住
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
检查现在日志组状态
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE ~~原来的当前日志组已经变为非活跃~~
清除在线重做日志
SQL> alter database clear logfile group 3;/* 提示log 3 需要归档*/
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance orcl11g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl11g/redo03.log'
SQL> alter database clear unarchived logfile group 3;/*清除非归档重做日志成功*/
Database altered.
2.6 检验数据
SQL> select * from test_control;
CURR_TIME
-------------------
2019-07-21 16:25:33
2019-07-27 09:32:21
2019-07-27 09:33:39
2019-08-03 11:52:17
2019-08-03 11:53:05
2019-08-17 16:08:27
2019-08-17 16:08:29
2019-08-17 16:08:29
2019-08-17 16:08:30
2019-08-17 16:53:19
2019-08-17 16:58:00
CURR_TIME
-------------------
2019-08-18 08:34:45
2019-08-18 08:34:47
2019-08-18 08:34:48
2019-08-18 08:34:49
2019-08-18 08:34:49
2019-08-18 08:34:51
17 rows selected.
没有数据丢失。
2.6 数据库告警日志中的相关内容
Sun Aug 18 08:41:47 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_arc3_2180.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl11g/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master archival failure: 313
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl11g - Archival Error
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl11g/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_arc3_2180.trc:
Master archival failure: 313
Sun Aug 18 08:47:39 2019
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 08/18/2019 08:41:47 (CHANGE 1080523) CANNOT BE USED FOR RECOVERY.
Clearing online log 3 of thread 1 sequence number 6
Errors in file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_2172.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl11g/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_2172.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl11g/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Archived Log entry 38 added for thread 1 sequence 7 ID 0x42f6d474 dest 1:
Archiver process freed from errors. No longer stopped
Completed: alter database clear unarchived logfile group 3
3 非当前重做日志组全部成员丢失
3.1 检查当前测试数据
SQL> select * from test_control;
CURR_TIME
-------------------
2019-07-21 16:25:33
2019-07-27 09:32:21
2019-07-27 09:33:39
2019-08-03 11:52:17
2019-08-03 11:53:05
2019-08-17 16:08:27
2019-08-17 16:08:29
2019-08-17 16:08:29
2019-08-17 16:08:30
2019-08-17 16:53:19
2019-08-17 16:58:00
CURR_TIME
-------------------
2019-08-18 08:34:45
2019-08-18 08:34:47
2019-08-18 08:34:48
2019-08-18 08:34:49
2019-08-18 08:34:49
2019-08-18 08:34:51
17 rows selected.
####### 3.2 向表中插入几行数据
SQL> insert into test_control select sysdate from dual;
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from test_control;
CURR_TIME
-------------------
2019-07-21 16:25:33
2019-07-27 09:32:21
2019-07-27 09:33:39
2019-08-03 11:52:17
2019-08-03 11:53:05
2019-08-17 16:08:27
2019-08-17 16:08:29
2019-08-17 16:08:29
2019-08-17 16:08:30
2019-08-17 16:53:19
2019-08-17 16:58:00
CURR_TIME
-------------------
2019-08-18 08:34:45
2019-08-18 08:34:47
2019-08-18 08:34:48
2019-08-18 08:34:49
2019-08-18 08:34:49
2019-08-18 08:34:51
2019-08-18 09:11:57
2019-08-18 09:11:59
2019-08-18 09:12:00
20 rows selected.
3.3 删除一个非当前日志组所有成员
SQL> select status, group# from v$log;
STATUS GROUP#
---------------- ----------
INACTIVE 1
CURRENT 2
UNUSED 3
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------------------------------
3 /u01/app/oracle/oradata/orcl11g/redo03.log
2 /u01/app/oracle/oradata/orcl11g/redo02.log
1 /u01/app/oracle/oradata/orcl11g/redo01.log
SQL> ! /u01/app/oracle/oradata/orcl11g/redo01.log
/bin/bash: /u01/app/oracle/oradata/orcl11g/redo01.log: Permission denied
SQL> ! rm /u01/app/oracle/oradata/orcl11g/redo01.log
SQL> ! ls /u01/app/oracle/oradata/orcl11g/redo01.log
ls: cannot access /u01/app/oracle/oradata/orcl11g/redo01.log: No such file or directory
3.4 故障处理及检验
SQL> alter database clear logfile group 1;/* 清除故障日志组*/
Database altered.
SQL> ! ls /u01/app/oracle/oradata/orcl11g/redo01.log
/u01/app/oracle/oradata/orcl11g/redo01.log /*故障日志组成员已经重建*/