实验记录：Oracle redo logfile的resize过程

转载至http://www.cnblogs.com/jyzhao/p/3781016.html

实验记录：Oracle redo logfile的resize过程。

实验环境：RHEL 6.4 + Oracle 11.2.0.3 单实例 文件系统

实验目的：本实验是修改redo logfile的过程记录，将当前数据库的3组redo logfile由原来的默认50M大小修改为100M。

1.查看当前redo logfile的信息

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

SQL>  set  linesize 160 SQL> col member  for  a60 SQL>  select  *  from  v$logfile;        GROUP # STATUS  TYPE    MEMBER                                                       IS_ ---------- ------- ------- ------------------------------------------------------------ ---           3         ONLINE  /home/oradata/JYZHAO/onlinelog/o1_mf_3_9n7r40xm_.log          NO           3         ONLINE  /u01/app/oracle/fast_recovery_area/JYZHAO/onlinelog/o1_mf_3_ YES                             9n7r412h_.log             2         ONLINE  /home/oradata/JYZHAO/onlinelog/o1_mf_2_9n7r3zyv_.log          NO           2         ONLINE  /u01/app/oracle/fast_recovery_area/JYZHAO/onlinelog/o1_mf_2_ YES                             9n7r403z_.log             1         ONLINE  /home/oradata/JYZHAO/onlinelog/o1_mf_1_9n7r3z5p_.log          NO           1         ONLINE  /u01/app/oracle/fast_recovery_area/JYZHAO/onlinelog/o1_mf_1_ YES                             9n7r3zb8_.log        GROUP # STATUS  TYPE    MEMBER                                                       IS_ ---------- ------- ------- ------------------------------------------------------------ ---     6  rows  selected.   SQL> ! [oracle@JY-DB dbhome_1]$ ls -lh /home/oradata/JYZHAO/onlinelog/ total 151M -rw-r -----. 1 oracle oinstall 51M Jun 10 22:02 o1_mf_1_9n7r3z5p_.log -rw-r -----. 1 oracle oinstall 51M Jun 10 22:34 o1_mf_2_9n7r3zyv_.log -rw-r -----. 1 oracle oinstall 51M Jun 10 18:00 o1_mf_3_9n7r40xm_.log [oracle@JY-DB dbhome_1]$ ls -lh /u01/app/oracle/fast_recovery_area/JYZHAO/onlinelog/ total 151M -rw-r -----. 1 oracle oinstall 51M Jun 10 22:02 o1_mf_1_9n7r3zb8_.log -rw-r -----. 1 oracle oinstall 51M Jun 10 22:34 o1_mf_2_9n7r403z_.log -rw-r -----. 1 oracle oinstall 51M Jun 10 18:00 o1_mf_3_9n7r412h_.log [oracle@JY-DB dbhome_1]$ exit exit   SQL>  select  *  from  v$log;        GROUP #    THREAD#   SEQUENCE #      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------           1          1        883   52428800        512          2 YES INACTIVE              12388912 2014-06-10 18:00:06     12407579 2014-06-10 22:02:06           2          1        884   52428800        512          2  NO   CURRENT                12407579 2014-06-10 22:02:06   2.8147E+14           3          1        882   52428800        512          2 YES INACTIVE              12388904 2014-06-10 18:00:06     12388912 2014-06-10 18:00:06

2.删除redo日志文件组1（确定group1的状态为INACTIVE）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL>  alter  database  drop  logfile  group  1;   Database  altered.   SQL>  select  *  from  v$log;         GROUP #    THREAD#   SEQUENCE #      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------           2          1        884   52428800        512          2  NO   CURRENT                12407579 2014-06-10 22:02:06   2.8147E+14           3          1        882   52428800        512          2 YES INACTIVE              12388904 2014-06-10 18:00:06     12388912 2014-06-10 18:00:06   SQL> ! [oracle@JY-DB dbhome_1]$ ls -lh /home/oradata/JYZHAO/onlinelog/ total 101M -rw-r -----. 1 oracle oinstall 51M Jun 10 22:37 o1_mf_2_9n7r3zyv_.log -rw-r -----. 1 oracle oinstall 51M Jun 10 18:00 o1_mf_3_9n7r40xm_.log [oracle@JY-DB dbhome_1]$ ls -lh  /u01/app/oracle/fast_recovery_area/JYZHAO/onlinelog/ total 101M -rw-r -----. 1 oracle oinstall 51M Jun 10 22:37 o1_mf_2_9n7r403z_.log -rw-r -----. 1 oracle oinstall 51M Jun 10 18:00 o1_mf_3_9n7r412h_.log<br>--可以看到数据库执行删除日志组1的命令后，日志组1对应的系统文件也会被自动删除。

3. 添加日志组1和成员

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[oracle@JY-DB dbhome_1]$ exit exit   SQL>  alter  database  add  logfile  group  1  '/home/oradata/JYZHAO/onlinelog/redo01a.log'  size  100M;   Database  altered.   SQL>   select  *  from  v$log;        GROUP #    THREAD#   SEQUENCE #      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------           1          1          0  104857600        512          1 YES UNUSED                       0                                0           2          1        884   52428800        512          2  NO   CURRENT                12407579 2014-06-10 22:02:06   2.8147E+14           3          1        882   52428800        512          2 YES INACTIVE              12388904 2014-06-10 18:00:06     12388912 2014-06-10 18:00:06   SQL>  alter  database  add  logfile member  '/u01/app/oracle/fast_recovery_area/JYZHAO/onlinelog/redo01b.log'  to  group  1;   Database  altered.

4.手工切换日志以应用新加的日志文件组

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SQL>  select  *  from  v$log;        GROUP #    THREAD#   SEQUENCE #      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------           1          1          0  104857600        512          2 YES UNUSED                       0                                0           2          1        884   52428800        512          2  NO   CURRENT                12407579 2014-06-10 22:02:06   2.8147E+14           3          1        882   52428800        512          2 YES INACTIVE              12388904 2014-06-10 18:00:06     12388912 2014-06-10 18:00:06   SQL>  alter  system switch logfile;   System altered.   SQL>  select  *  from  v$log;        GROUP #    THREAD#   SEQUENCE #      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------           1          1        885  104857600        512          2  NO   CURRENT                12410983 2014-06-10 22:44:14   2.8147E+14           2          1        884   52428800        512          2 YES ACTIVE                12407579 2014-06-10 22:02:06     12410983 2014-06-10 22:44:14           3          1        882   52428800        512          2 YES INACTIVE              12388904 2014-06-10 18:00:06     12388912 2014-06-10 18:00:06

5.手工checkpoint让ACTIVE状态的日志文件组变成INACTIVE。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SQL>  select  *  from  v$log;        GROUP #    THREAD#   SEQUENCE #      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------           1          1        885  104857600        512          2 YES INACTIVE              12410983 2014-06-10 22:44:14     12411004 2014-06-10 22:45:03           2          1        887  104857600        512          2  NO   CURRENT                12411270 2014-06-10 22:51:39   2.8147E+14           3          1        886   52428800        512          2 YES ACTIVE                12411004 2014-06-10 22:45:03     12411270 2014-06-10 22:51:39   SQL>  alter  system  checkpoint ;   System altered.   SQL>  select  *  from  v$log;        GROUP #    THREAD#   SEQUENCE #      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------           1          1        885  104857600        512          2 YES INACTIVE              12410983 2014-06-10 22:44:14     12411004 2014-06-10 22:45:03           2          1        887  104857600        512          2  NO   CURRENT                12411270 2014-06-10 22:51:39   2.8147E+14           3          1        886   52428800        512          2 YES INACTIVE              12411004 2014-06-10 22:45:03     12411270 2014-06-10 22:51:39

6.参照上述步骤完成其他redo日志文件大小的resize，不再赘述。