redo log管理维护

和redo log相关的视图有如下两个：

V$LOG

V$LOGFILE

 

重做日志组有四个状态：

Active：正在归档

Inactive：归档完成

Current：正在使用

Unuse：新添加的重做日志组和clear archived之后的状态

 

SQL> desc v$log;  名称                                    是否为空? 类型  ----------------------------------------- -------- ----------------------------  GROUP#                                             NUMBER  THREAD#                                            NUMBER  SEQUENCE#                                          NUMBER  BYTES                                              NUMBER  BLOCKSIZE                                          NUMBER  MEMBERS                                            NUMBER  ARCHIVED                                           VARCHAR2(3)  STATUS                                             VARCHAR2(16)  FIRST_CHANGE#                                      NUMBER  FIRST_TIME                                         DATE  NEXT_CHANGE#                                       NUMBER  NEXT_TIME                                          DATE   SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;       GROUP#    MEMBERS STATUS           ARC ---------- ---------- ---------------- ---          1          1 INACTIVE         YES          2          1 CURRENT          NO          3          1 INACTIVE         YES   SQL>

可以看到数据库里面一共有三个重做日志组，每组仅有一个member。

现在看一下重做日志组中member的路径

SQL> desc v$logfile  名称                                    是否为空? 类型  ----------------------------------------- -------- ----------------------------  GROUP#                                             NUMBER  STATUS                                             VARCHAR2(7)  TYPE                                               VARCHAR2(7)  MEMBER                                             VARCHAR2(513)  IS_RECOVERY_DEST_FILE                              VARCHAR2(3)   SQL> set linesize 200 SQL> col member for a80 SQL> select GROUP#,STATUS,MEMBER from v$logfile;       GROUP# STATUS  MEMBER ---------- ------- --------------------------------------------------------------------------------          3         /u01/app/oracle/oradata/test/redo03.log          2         /u01/app/oracle/oradata/test/redo02.log          1         /u01/app/oracle/oradata/test/redo01.log   SQL>

 

 

现在我们添加一个重做日志组4，并且使每个重做日志组有三个member

[oracle@oracle ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on 星期二 6月 28 21:18:44 2016   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/test/redo04.log' size 50m;   数据库已更改。   SQL> SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;       GROUP#    MEMBERS STATUS           ARC ---------- ---------- ---------------- ---          1          1 INACTIVE         YES          2          1 CURRENT          NO          3          1 INACTIVE         YES          4          1 UNUSED           YES   SQL>

可以看到刚添加的group 4是UNUSED状态。

现在使其变为其他状态

SQL> alter system switch logfile;   系统已更改。   SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;   GROUP#    MEMBERS STATUS           ARC ---------- ---------- ---------------- ---          1          1 INACTIVE         YES          2          1 ACTIVE           NO          3          1 INACTIVE         YES          4          1 CURRENT          NO   SQL>

 

现在添加member

SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo041.log' to group 4;   数据库已更改。   SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo042.log' to group 4;   数据库已更改。   SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo031.log' to group 3;   数据库已更改。   SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo032.log' to group 3;   数据库已更改。   SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo021.log' to group 2;   数据库已更改。   SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo022.log' to group 2;   数据库已更改。   SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo011.log' to group 1;   数据库已更改。   SQL> alter database add logfile member '/u01/app/oracle/oradata/test/redo012.log' to group 1;   数据库已更改。   SQL>

可以看到我在已有的logfile group添加member的时候并没有指定大小，这是因为同一个group中所有member必须相等，因此新添加的member默认和已有的member相等。

查询下member和redo log状态

SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;       GROUP#    MEMBERS STATUS           ARC ---------- ---------- ---------------- ---          1          3 INACTIVE         YES          2          3 INACTIVE         YES          3          3 INACTIVE         YES          4          3 CURRENT          NO   SQL> select GROUP#,STATUS,MEMBER from v$logfile;       GROUP# STATUS  MEMBER ---------- ------- --------------------------------------------------------------------------------          3         /u01/app/oracle/oradata/test/redo03.log          2         /u01/app/oracle/oradata/test/redo02.log          1         /u01/app/oracle/oradata/test/redo01.log          4         /u01/app/oracle/oradata/test/redo04.log          4 INVALID /u01/app/oracle/oradata/test/redo041.log          4 INVALID /u01/app/oracle/oradata/test/redo042.log          3 INVALID /u01/app/oracle/oradata/test/redo031.log          3 INVALID /u01/app/oracle/oradata/test/redo032.log          2 INVALID /u01/app/oracle/oradata/test/redo021.log          2 INVALID /u01/app/oracle/oradata/test/redo022.log          1 INVALID /u01/app/oracle/oradata/test/redo011.log       GROUP# STATUS  MEMBER ---------- ------- --------------------------------------------------------------------------------          1 INVALID /u01/app/oracle/oradata/test/redo012.log   已选择12行。   SQL>

 

可以看到新添加的member都是INVALID状态，让其生效：

SQL> alter system switch logfile;   系统已更改。   SQL> /   系统已更改。   SQL> /   系统已更改。   SQL> /   系统已更改。   SQL> 系统已更改。   SQL> 系统已更改。   SQL>

查看状态：

SQL> select GROUP#,STATUS,MEMBER from v$logfile;       GROUP# STATUS  MEMBER ---------- ------- --------------------------------------------------------------------------------          3         /u01/app/oracle/oradata/test/redo03.log          2         /u01/app/oracle/oradata/test/redo02.log          1         /u01/app/oracle/oradata/test/redo01.log          4         /u01/app/oracle/oradata/test/redo04.log          4         /u01/app/oracle/oradata/test/redo041.log          4         /u01/app/oracle/oradata/test/redo042.log          3         /u01/app/oracle/oradata/test/redo031.log          3         /u01/app/oracle/oradata/test/redo032.log          2         /u01/app/oracle/oradata/test/redo021.log          2         /u01/app/oracle/oradata/test/redo022.log          1         /u01/app/oracle/oradata/test/redo011.log       GROUP# STATUS  MEMBER ---------- ------- --------------------------------------------------------------------------------          1         /u01/app/oracle/oradata/test/redo012.log   已选择12行。   SQL>

 

删除重做日志组：

SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;   GROUP#    MEMBERS STATUS           ARC ---------- ---------- ---------------- ---          1          3 CURRENT          NO          2          3 INACTIVE         YES          3          3 INACTIVE         YES          4          3 INACTIVE         YES   SQL>

模拟删除4

SQL> alter database drop logfile group 4; alter database drop logfile group 4 * 第 1 行出现错误: ORA-01623: 日志 4 是实例 test (线程 1) 的当前日志 - 无法删除 ORA-00312: 联机日志 4 线程 1: '/u01/app/oracle/oradata/test/redo04.log' ORA-00312: 联机日志 4 线程 1: '/u01/app/oracle/oradata/test/redo041.log' ORA-00312: 联机日志 4 线程 1: '/u01/app/oracle/oradata/test/redo042.log'     SQL>

因为logfile 4是current不能删除，因此切换日志

SQL> alter system switch logfile;   系统已更改。   SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;       GROUP#    MEMBERS STATUS           ARC ---------- ---------- ---------------- ---          1          3 CURRENT          NO          2          3 INACTIVE         YES          3          3 ACTIVE           YES          4          3 ACTIVE           YES

现在删除试试

SQL> alter database drop logfile group 4; alter database drop logfile group 4 * 第 1 行出现错误: ORA-01624: 日志 4 是紧急恢复实例 test (线程 1) 所必需的 ORA-00312: 联机日志 4 线程 1: '/u01/app/oracle/oradata/test/redo04.log' ORA-00312: 联机日志 4 线程 1: '/u01/app/oracle/oradata/test/redo041.log' ORA-00312: 联机日志 4 线程 1: '/u01/app/oracle/oradata/test/redo042.log'     SQL>

因为active状态正在归档日志，必须等到归档完成切换为inactive状态才能删除，如果一定要删除，可以先clear unarchived再去drop

SQL> alter database clear unarchived logfile group 4;   数据库已更改。   SQL> alter database drop logfile group 4;   数据库已更改。   SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;       GROUP#    MEMBERS STATUS           ARC ---------- ---------- ---------------- ---          1          3 CURRENT          NO          2          3 INACTIVE         YES          3          3 INACTIVE         YES   SQL> select GROUP#,STATUS,MEMBER from v$logfile;       GROUP# STATUS  MEMBER ---------- ------- --------------------------------------------------------------------------------          3         /u01/app/oracle/oradata/test/redo03.log          2         /u01/app/oracle/oradata/test/redo02.log          1         /u01/app/oracle/oradata/test/redo01.log          3         /u01/app/oracle/oradata/test/redo031.log          3         /u01/app/oracle/oradata/test/redo032.log          2         /u01/app/oracle/oradata/test/redo021.log          2         /u01/app/oracle/oradata/test/redo022.log          1         /u01/app/oracle/oradata/test/redo011.log          1         /u01/app/oracle/oradata/test/redo012.log   已选择9行。

 

可以看到drop成功，现在有三个重做日志组，每组有三个member。

补充：当clear unarchived之后，日志组状态变为unused，如果继续切换日志，该日志组还可以继续使用，这也是平常遇到不能归档redolog时常用的处理方法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29335043/viewspace-2121143/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29335043/viewspace-2121143/