【Oracle】恢复重做日志组
我们在Oracle的日常运维中，有可能会遇到重做日志组丢失的情况。下面我将模拟丢失不同状态的日志组，并分别给出解决办法：
重做日志有以下几种状态，如下：
- CURRENT：此状态表示正在被 LGWR 使用的日志组；
- ACTIVE：重做日志组中仍含有实例恢复所需的重做数据；
- INACTIVE：表示实例恢复不再需要此日志组，可以覆盖；
- UNUSED：未使用；
- CLEARING：对已存在的日志组执行 clear 操作的后的状态；
- CLEARING_CURRENT：日志正在清空。当清空出错时，该日志组被置于这种状态。
1. 丢失INACTIVE状态下的日志组
1）查看当前日志组的状态
SYS@lgr> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
SYS@lgr> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/lgr/redo01.log
2 /u01/app/oracle/oradata/lgr/redo02.log
3 /u01/app/oracle/oradata/lgr/redo03.log
2）在操作系统层面删除GROUP2日志组，模拟丢失了INACTIVE状态的日志组
SYS@lgr> !rm /u01/app/oracle/oradata/lgr/redo02.log
SYS@lgr> !ls /u01/app/oracle/oradata/lgr/redo02.log
ls: /u01/app/oracle/oradata/lgr/redo02.log: No such file or directory
3）前两步已经完成了实验环境的准备，重启数据库会报错
SYS@lgr> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@lgr> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 285212672 bytes
Redo Buffers 6586368 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 17099
Session ID: 125 Serial number: 5
4）在报错中不能分析出出错的原因，我们查看alert日志可以看出是丢失了GROUP2日志组
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/lgr/redo02.log'
ORA-27037: unable to obtain file status
5）启动数据库到MOUNT状态
SYS@lgr> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 285212672 bytes
Redo Buffers 6586368 bytes
Database mounted.
6）CLEAR一下GROUP2日志组，并OPEN数据库
SYS@lgr> alter database clear logfile group 2;
Database altered.
SYS@lgr> alter database open;
Database altered.
7）查看日志组状态及物理文件，发现均已正常，至此完成日志组恢复
SYS@lgr> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 INACTIVE
SYS@lgr> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/lgr/redo01.log
2 /u01/app/oracle/oradata/lgr/redo02.log
3 /u01/app/oracle/oradata/lgr/redo03.log
总结：丢失INACTIVE状态的日志组，恢复时需要：①启动数据库到MOUNT状态；②对丢失的日志组执行CLEAR命令；③打开数据库。
2.丢失CURRENT状态下的日志组
1）查看当前日志组的状态
SYS@lgr> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
SYS@lgr> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/lgr/redo03.log
2 /u01/app/oracle/oradata/lgr/redo02.log
1 /u01/app/oracle/oradata/lgr/redo01.log
2）在操作系统层面删除GROUP3日志组，模拟丢失了INACTIVE状态的日志组
SYS@lgr> !rm /u01/app/oracle/oradata/lgr/redo03.log
SYS@lgr> !ls /u01/app/oracle/oradata/lgr/redo03.log
ls: /u01/app/oracle/oradata/lgr/redo03.log: No such file or directory
3）重启数据库到MOUNT状态
SYS@lgr> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@lgr> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 285212672 bytes
Redo Buffers 6586368 bytes
Database mounted.
4）采用 recover 的方式完成重做日志恢复
SYS@lgr> recover database until cancel;
Media recovery complete.
5）resetlogs方式打开数据库
SYS@lgr> alter database open resetlogs;
Database altered.
6）检查日志组状态及物理文件，都已正常，至此完成恢复
SYS@lgr> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 UNUSED
SYS@lgr> col member for a50
SYS@lgr> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/lgr/redo03.log
2 /u01/app/oracle/oradata/lgr/redo02.log
1 /u01/app/oracle/oradata/lgr/redo01.log
总结：丢失CURRENT状态下的日志组，我们需要如下操作：①重启数据库到MOUNT状态；②recover方式完成恢复；③resetlogs方式打开数据库。
