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

来源：互联网发布：mac office 彻底关闭编辑：程序博客网时间：2024/04/30 07:54

转载至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，不再赘述。

0 0