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