Oracle redo日志的管理

一、重做日志的作用
二、重做日志组
三、查询重做日志文件的信息
四、添加和删除联机重做日志组
- 4.1添加新的重做日志组
- 4.2删除重做日志组
五、添加和删除联机重做日志组成员
- 5.1添加重做日志成员
- 5.2删除重做日志成员
六、联机重做日志组的恢复

一、重做日志的作用

重做日志记录了对数据库的更新、插入等修改操作，在数据库崩溃时能够恢复已提交的数据，所以重做日志的目的是数据库的恢复。

二、重做日志组

如图，每个重做日志文件（redo log）叫做成员，每个方框为一个重做日志组（group）。同组中每个成员中所存的信息完全相同，要保证Oracle数据库正常工作需要至少两个重做日志组。每次对数据进行DML操作，都会产生日志信息，并存储在重做日志缓冲区（redo log buffer），重做日志写进程（LGWR）将日志信息从重做日志缓冲区中复制到该组的每个成员中，LGWR在任意时刻只能写一组重做日志组。它是以循环的方式写重做日志组，当LGWR写满了一个日志组，就开始写下一个日志组。当写满了最后一个日志组，LGWR将开始重写第一组重做日志。这称为日志切换（switch）。
如果Oracle数据库运行在归档模式下时，当LGWR的写操作从一个重做日志组切换到另一个重做日志组后，归档写进程（ARCH）就会将原来的重做日志文件复制到归档日志文件中。Oracle服务器保证在归档写进程完成复制之前，LGWR不能再写这组重做日志文件。

三、查询重做日志文件的信息

v$log：查看重做日志组的信息和状态
select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS STATUS ARC

     1         13          1 CURRENT          NO
     2         11          2 INACTIVE         YES
     3         12          1 INACTIVE         YES

状态列（status）中状态的含义如下：

inactive：非活动的，表示实例恢复已不再需要这组联机重做日志组。
active：活动的，表示这组联机重做日志组是活动的但不是当前组，在实例恢复时需要这重做日志组，如这组重做日志组正在归档。
current：表示这组联机重做日志组是当前组。
unused：表示从来没写过该组联机重做日志组，这是重做日志组刚被添加到数据库的状态。

v$logfile：查看重做日志组的成员信息和状态
select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1             ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     3 STALE ONLINE  /u01/app/oracle/oradata/orcl/redo03a.log NO
     2             ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2             ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO

状态列（status）中状态的含义如下：

空白：表示该文件正在使用。
stale：表示文件中的内容是不完全的。
invalid：表示文件不可被访问。
deleted：表示该文件已不再有用。

四、添加和删除联机重做日志组

4.1添加新的重做日志组

添加的命令格式如下：
alter database [数据库名称]
　　add logfile[group 正整数] 文件名称
　　[,[group 正整数]文件名称]]

查看当前数据库的重做日志组的信息：
SQL>select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         19          1   52428800 INACTIVE         YES
     2         20          2   52428800 INACTIVE         YES
     3         21          1   52428800 CURRENT          NO

SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     3         ONLINE  /u01/app/oracle/oradata/orcl/redo03a.log NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO

添加一个新的重做日志组group 4：
SQL>alter database add logfile group 4('/u01/app/oracle/oradata/orcl/redo04a.log','/u01/app/oracle/oradata/orcl/redo04b.log') size 50m;
Database altered.

查看新添加的重做日志组：
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         19          1   52428800 INACTIVE         YES
     2         20          2   52428800 INACTIVE         YES
     3         21          1   52428800 CURRENT          NO
     4          0          2   52428800 UNUSED           YES           ---状态为unused，表示日志组没写入过，新添加到数据库

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     3         ONLINE  /u01/app/oracle/oradata/orcl/redo03a.log NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04a.log NO        ---显示成员已经成功添加
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log NO

4.2删除重做日志组

删除的命令格式如下：
alter database[数据库名称]
　　drop logfile{group 正整数|('文件名称')}
　　　　[,{group 正整数|('文件名称')}]

删除重做日志组前，确保已经归档并且不是当前使用的重做日志组：
SQL>alter system switch logfile; ---多次切换
SQL>alter system checkpoint; ---手动执行检查点
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         23          1   52428800 INACTIVE         YES
     2         24          2   52428800 CURRENT          NO
     3         21          1   52428800 INACTIVE         YES
     4         22          2   52428800 INACTIVE         YES

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     3         ONLINE  /u01/app/oracle/oradata/orcl/redo03a.log NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log NO

删除重做日志组group 3：
SQL> alter database drop logfile group 3;
Database altered.

查看删除后情况：
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         23          1   52428800 INACTIVE         YES
     2         24          2   52428800 CURRENT          NO
     4         22          2   52428800 INACTIVE         YES

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log NO

注意：删除重做日志组后，相对应的系统文件依然存在，需要手动删除

五、添加和删除联机重做日志组成员

5.1添加重做日志成员

添加重做日志成员命令格式如下：
alter database [数据库名称]
　　add logfile member
　　['文件名称'[reuse]
　　[,'文件名称'[reuse]]
　　　　to {group 组号
　　　　|('文件名称'[,'文件名称']....)
　　　　}
　　]....

查看当前重做日志组的信息：
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         23          1   52428800 INACTIVE         YES             ----group 1只有1个成员
     2         24          2   52428800 CURRENT          NO
     4         22          2   52428800 INACTIVE         YES

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log NO

添加一个成员到group 1：
SQL> alter database add logfile member '/u01/app/oracle/oradata/orcl/redo01a.log' to group 1;
Database altered.

查看添加后的状态：
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         23          2   52428800 INACTIVE         YES              ----group 1成员为2
     2         24          2   52428800 CURRENT          NO
     4         22          2   52428800 INACTIVE         YES

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     1 INVALID ONLINE  /u01/app/oracle/oradata/orcl/redo01a.log NO        ----新添加的成员
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log NO

5.2删除重做日志成员

删除成员命令格式如下：
alter database[数据库名称] drop logfile member '文件名称'[,'文件名称']....

删除group 4的一个成员：
SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo04b.log';
Database altered.

查看删除后状态：
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         26          2   52428800 INACTIVE         YES
     2         27          2   52428800 CURRENT          NO
     4         25          1   52428800 INACTIVE         YES

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01a.log NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04a.log NO

六、联机重做日志组的恢复

6.1丢失非活动日志文件的恢复

如果丢失的日志文件状态为INACTIVE，表示该重做日志组已经完成检查点，数据库不会丢失数据，但不能忽视，因为当切换到该日志时会出错。
查看重做日志组的信息：
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC

     1         32          2   52428800 CURRENT          NO
     2         30          2   52428800 INACTIVE         YES
     4         31          1   52428800 INACTIVE         YES

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO
     1         ONLINE  /u01/app/oracle/oradata/orcl/redo01a.log NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
     2         ONLINE  /u01/app/oracle/oradata/orcl/redo02a.log NO
     4         ONLINE  /u01/app/oracle/oradata/orcl/redo04a.log NO

模拟日志文件丢失：
[oracle@shao orcl]$ rm -f redo02a.log

告警日志得到以下信息：
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc1_2436.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

恢复的三种方法：
1.直接删除丢失的重做日志组，然后重新添加重做日志组：
SQL> alter database drop logfile group 2; ---删除
Database altered.

SQL>alter database add logfile group 2 ('/u01/oradata/orcl/redo2.log','/u01/oradata/orcl/redo02.log') size 50m; ---添加
Database altered.

2.在丢失的重做日志组中增加同样大小的成员，然后删除丢失的成员。
SQL> alter database add logfile member '/u01/app/oracle/oradata/orcl/redo02b.log' to group 2; ---添加新的成员
Database altered.

SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02a.log'; ---删除丢失的成员
Database altered.

这种方法只适合丢失的文件组中至少还有一个成员是可用的，不然会报错：
SQL> alter database add logfile member '/u01/app/oracle/oradata/orcl/redo02c.log' to group 2;
alter database add logfile member '/u01/app/oracle/oradata/orcl/redo02c.log' to group 2
*ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

3.清除重做日志组
SQL> alter database clear logfile group 2;
Database altered.

如果丢失的重做日志组未归档，需要加unarchived：
SQL> alter database clear unarchived logfile group 2;

6.2丢失活动的重做日志文件

执行检查点：
SQL> alter system checkpoint;
命令成功执行，会将所有已经提交的更改写入磁盘的数据文件中。就跟INACTIVE重做日志组处理流程一致了，如果命令执行出现故障，就只能执行不完整恢复。

6.2丢失当前的重做日志文件

模拟删除当前日志文件，并非正常关闭数据库，启动数据库报错：
SQL> startup force
ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size 2214936 bytes
Variable Size 461374440 bytes
Database Buffers 50331648 bytes
Redo Buffers 8015872 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

尝试清除重做日志组，同样报错，因为数据文件状态不一致，需要使用当前日志进行实例恢复，所以无法通过清除日志命令进行清除：
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01a.log'

需要执行不完全恢复，并使用resetlogs打开数据库
SQL> recover database until cancel; ---执行命令，使用归档日志进行恢复，一直回车，直到没有归档日志文件可用为止
ORA-00279: change 3501021 generated at 11/07/2017 23:47:53 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_11_07/o1_mf_1_79_%u_.ar
c
ORA-00280: change 3501021 for thread 1 is in sequence #79
Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_11_07/o1_mf_1_79_%u_.a
rc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

SQL> alter database open resetlogs; ---使用resetlogs打开数据库失败
alter database open resetlogs
*ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

这时我们只能通过应用隐含参数，通过隐含参数跳过一致性验证，使状态不一致的数据库打开
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; ---修改隐藏参数
System altered.

SQL> recover database until cancel;
省略输出

SQL> alter database open resetlogs; ----成功用resetlogs打开数据库
Database altered.

成功打开数据库后，对数据库做全备，并关闭之前修改的隐藏参数：
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.