oracle 添加冗余redo log

目标
1、明确为什么要添加冗余redolog
2、怎样添加redolog
3、怎样删除redolog成员
Maintenance of Online Redo Log Groups and Members (文档 ID 102995.1)

一 为什么添加redolog

数据库只有将块更改写入到redolog中才能认为事务已经提交完成，当redo由于误删、磁盘坏块等问题损坏，数据库实例将崩溃，并且这个REDO文件中包含的事务更改会丢失。
    但是假如我们添加了REDO的冗余，当其中一个REDO受到损坏时，虽然数据库实例仍将崩溃，但是我们只需要查看alert日志，复制正常的REDO文件替换损坏的REDO文件，再将数据库启动即可，停机时间短并且不会丢失数据。

二 添加冗余

2.1 查询当前redo信息

 

SQL> set linesize 300
SQL> col status format a10
SQL> select thread#, GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS,FIRST_TIME from v$log;

   THREAD#     GROUP#  SEQUENCE# BYTES/1024/1024 STATUS     FIRST_TIM
---------- ---------- ---------- --------------- ---------- ---------
         1          1        817              50 INACTIVE   16-JAN-19
         1          2        818              50 CURRENT    16-JAN-19
         1          3        816              50 INACTIVE   16-JAN-19

SQL> col member format a100
SQL> select * from v$logfile;

    GROUP# STATUS     TYPE    MEMBER                                                          IS_
---------- ---------- ------- --------------------------------------------------------------- ---
         3            ONLINE  /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_f347m86l_.log    NO
         2            ONLINE  /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_f347m709_.log    NO
         1            ONLINE  /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_f347m6fr_.log    NO
         4            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo04.log          NO
         5            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo05.log          NO
         6            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo06.log          NO
         7            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo07.log          NO

7 rows selected.

我们只需要关注TYPE是ONLINE的redo，可以看到当前GROUP为1,2,3，位置在/u01/app/oracle/oradata/ORCL/onlinelog/，每个50MB

2.2 添加冗余日志成员

我们最好将redo日志记录到其他磁盘路径下，或者其他目录下，防止磁盘损坏或者误删。
此处假设放到/oradata/redo/下。
添加成员时，只需要指定添加到的位置和GROUP，无需指定thread和size，oracle会自动向组中添加相同的redo副本。

ALTER DATABASE ADD LOGFILE MEMBER '/oradata/redo/online01.redo' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/oradata/redo/online02.redo' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/oradata/redo/online03.redo' TO GROUP 3;

SQL> select * from v$logfile;

    GROUP# STATUS     TYPE    MEMBER                                                        IS_
---------- ---------- ------- ------------------------------------------------------------- ---
         3            ONLINE  /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_f347m86l_.log  NO
         2            ONLINE  /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_f347m709_.log  NO
         1            ONLINE  /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_f347m6fr_.log  NO
         4            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo04.log        NO
         5            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo05.log        NO
         6            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo06.log        NO
         7            STANDBY /u01/app/oracle/oradata/ORCL/onlinelog/stdbyredo07.log        NO
         1 INVALID    ONLINE  /oradata/redo/online01.redo                                   NO
         2 INVALID    ONLINE  /oradata/redo/online02.redo                                   NO
         3 INVALID    ONLINE  /oradata/redo/online03.redo                                   NO

10 rows selected.

已经添加，状态是INVAILD，只需要切换几次redo即可。
 

SQL> alter system switch logfile;

RAC环境和单机环境的区别就是多个THREAD和ASM路径不同，但是添加方法和单机相同，指定group和磁盘组就能为指定的group添加冗余。
例如：

ALTER DATABASE ADD LOGFILE MEMBER '+FRA' TO GROUP 1;

或者
 

ALTER DATABASE ADD LOGFILE MEMBER '+FRA/ORCL/redo/group01.redo' TO GROUP 1;

三 怎样删除冗余日志成员？

选定需要删除的redo日志，直接删除即可，要注意每组日志最少保留1个成员
 

SQL> ALTER DATABASE DROP LOGFILE MEMBER '+FRA/orcl/onlinelog/group_1.284.970131507';
Database altered.

假如出现如下报错：
 

SQL> ALTER DATABASE DROP LOGFILE MEMBER '+FRA/orcl/onlinelog/group_2.283.970131509' ;
ALTER DATABASE DROP LOGFILE MEMBER '+FRA/orcl/onlinelog/group_2.283.970131509'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '+DATA/orcl/onlinelog/group_2.262.970131507'
ORA-00312: online log 2 thread 1: '+FRA/orcl/onlinelog/group_2.283.970131509'

说明当前日志正在CURRENT模式，只需
 

alter system switch logfile;

手动切换redo日志，将该日志所在的组切换为INACTIVE即可正常删除。