代码改变世界

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

2014-06-10 23:48  AlfredZhao  阅读(...)  评论(...编辑  收藏

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

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

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

1.查看当前redo logfile的信息

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)

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
--可以看到数据库执行删除日志组1的命令后,日志组1对应的系统文件也会被自动删除。

3. 添加日志组1和成员

[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.手工切换日志以应用新加的日志文件组

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。

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,不再赘述。