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

最新推荐文章于 2023-04-10 13:21:55 发布

victoruu

最新推荐文章于 2023-04-10 13:21:55 发布

阅读量804

点赞数

分类专栏： Oracle undo和表空间文章标签： oracle

Oracle undo和表空间专栏收录该内容

17 篇文章

订阅专栏

本文记录了在 RHEL6.4 + Oracle11.2.0.3 环境下，如何将三组 Redo Log 文件从 50MB 扩容到 100MB 的全过程。实验包括查看当前 Redo Log 信息、删除及添加日志文件组、手动切换日志文件等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转载至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的信息

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<br>--可以看到数据库执行删除日志组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