在线重做日志丢失后的恢复
原创
©著作权归作者所有：来自51CTO博客作者dber_ablewang的原创作品，请联系作者获取转载授权，否则将追究法律责任
- 丢失多路复用日志组成员的恢复
a. 准备数据
#创建一个新的日志组
SQL> select group#, member from v$logfile;
GROUP# MEMBER
-----------------------------------------------
3 /opt/oracle/oradata/O19C/redo03.log
2 /opt/oracle/oradata/O19C/redo02.log
1 /opt/oracle/oradata/O19C/redo01.log
alter database add logfile group 4('/opt/oracle/oradata/O19C/redo04_1.log','/opt/oracle/oradata/O19C/redo04_2.log') size 50M;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- --------------------------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
#切换日志确保新添加的日志组被使用
SQL> alter system switch logfile;
SQL> /
SQL> select group#,status from v$log;
GROUP# STATUS
---------- --------------------------------
1 INACTIVE
2 ACTIVE
3 ACTIVE
4 CURRENT
b. 删除日志组4的一个文件，模拟故障
[oracle@oracle19c O19C]$ rm -rf redo04_1.log
SQL> alter system switch logfile;
SQL> /
SQL> /
SQL> /
SQL> /
2024-09-12T11:12:42.396073+08:00
Errors in file /opt/oracle/diag/rdbms/o19c/o19c/trace/o19c_arc0_6818.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/opt/oracle/oradata/O19C/redo04_1.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
c. 恢复
SQL> select group#,status from v$log; #如果组4当前再使用，不能进行恢复操作
GROUP# STATUS
---------- --------------------------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 INACTIVE
SQL> alter database drop logfile member '/opt/oracle/oradata/O19C/redo04_1.log';
SQL> alter database add logfile member '/opt/oracle/oradata/O19C/redo04_1.log' to group 4;
- 在线重做日志组所有成员都丢失后的恢复
a. 丢失inactive在线重做日志组
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARCHIV
---------- -------------------------------- ------
1 ACTIVE YES
2 CURRENT NO
3 INACTIVE YES
4 INACTIVE YES
SQL> host rm /opt/oracle/oradata/O19C/redo03.log
SQL> host ls /opt/oracle/oradata/O19C/redo03.log
ls: cannot access /opt/oracle/oradata/O19C/redo03.log: No such file or directory
SQL> alter database clear logfile group 3;
#如果inactive的日志组没有被归档，则使用命令
alter database clear unarchived logfile group 3;
SQL> host ls /opt/oracle/oradata/O19C/redo03.log
/opt/oracle/oradata/O19C/redo03.log
b. 丢失active或者current在线重做日志组
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARCHIV
---------- -------------------------------- ------
1 CURRENT NO
2 INACTIVE YES
3 INACTIVE YES
4 INACTIVE YES
SQL> host rm /opt/oracle/oradata/O19C/redo01.log
SQL> host ls /opt/oracle/oradata/O19C/redo01.log
ls: cannot access /opt/oracle/oradata/O19C/redo01.log: No such file or directory
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance o19c (thread 1)
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/O19C/redo01.log'
SQL> alter system checkpoint;
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARCHIV
---------- -------------------------------- ------
1 CURRENT NO
2 INACTIVE YES
3 INACTIVE YES
4 INACTIVE YES
SQL> alter system switch logfile;
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARCHIV
---------- -------------------------------- ------
1 ACTIVE NO
2 CURRENT NO
3 INACTIVE YES
4 INACTIVE YES
SQL> alter database clear unarchived logfile group 1;
SQL> host ls /opt/oracle/oradata/O19C/redo01.log
/opt/oracle/oradata/O19C/redo01.log
c. 丢失多个重做日志组
如果丢失了多个在线重做日志组，那么恢复的方法就是先从最难恢复的日志开始，从难到易的顺序如下：
§ current在线重做日志
§ active在线重做日志
§ unarchived在线重做日志
§ inactive在线重做日志
