实验记录: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
- 实验记录:Oracle redo logfile的resize过程
- Oracle Online redo logfile 配置
- oracle redo internal 之 dump logfile (dump redo logfile)
- 如何调整Oracle Redo Logfile日志文件的大小?
- oracle实验记录 oracle 详细分析redo
- redo logfile
- ORACLE入门之简析REDO LOGFILE
- 如何查询redo logfile的使用率
- [转]redo logfile 的不同状态
- 如何查询redo logfile的使用率
- 【Oracle Log file 恢复】 已归档、非当前的 Redo LogFile 损坏
- Oracle Using the Undo and Redo Logfile Size Advisors
- Redo logfile recover
- redo logfile operation codes
- 查询redo logfile的使用率 tablespace 的自由空间
- 在联机状态改变redo logfile大小的方法
- current online redo logfile 丢失的处理方法
- Oracle Dataguard Standby Redo Log的两个实验 --博主
- JQuery EasyUI combobox动态添加option
- 磁盘
- openwrt和srun的两三事
- poj2718(子集生成)
- 【技巧】数组作为另一个数组的下标
- 实验记录:Oracle redo logfile的resize过程
- Ubuntu PPA用法简介
- ML-kNN 多标签k近邻算法
- 观察者模式!
- V4L2框架分析学习
- [leetcode]Binary Tree Maximum Path Sum
- RadioButton和CheckBox
- 59. web 攻击技术(3)
- HashMap源码解析-jdk8