« Oracle Diagnostics:How to deal with ORA-19815 |
Blog首页
| Still not got my DBA »
How Many knowledge you do not Know from this Operation?
由于日志切换过于频繁,今天调整了一下日志文件大小,数据库为Oracle10g 10.1.0.3.0.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Solaris: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
|
调整步骤如下:
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS
---------- ------- ------- ---------------------------------------- ---
3 ONLINE +DATADG/mmsdb/onlinelog/group_3.274.1 NO
3 ONLINE +DATADG/mmsdb/onlinelog/group_3.275.1 NO
2 ONLINE +DATADG/mmsdb/onlinelog/group_2.272.1 NO
2 ONLINE +DATADG/mmsdb/onlinelog/group_2.273.1 NO
1 ONLINE +DATADG/mmsdb/onlinelog/group_1.270.1 NO
1 ONLINE +DATADG/mmsdb/onlinelog/group_1.271.1 NO
1 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_1_15dbl80x_.log YES
2 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_2_15dbl8l1_.log YES
3 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_3_15dbl93s_.log YES
9 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------
1 1 17717 10485760 3 NO CURRENT 178164478 06-DEC-05
2 1 17715 10485760 3 YES INACTIVE 178137459 06-DEC-05
3 1 17716 10485760 3 YES ACTIVE 178149970 06-DEC-05
SQL> alter database add logfile group 4 size 30m;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE +DATADG/mmsdb/onlinelog/group_3.274.1 NO
3 ONLINE +DATADG/mmsdb/onlinelog/group_3.275.1 NO
2 ONLINE +DATADG/mmsdb/onlinelog/group_2.272.1 NO
2 ONLINE +DATADG/mmsdb/onlinelog/group_2.273.1 NO
1 ONLINE +DATADG/mmsdb/onlinelog/group_1.270.1 NO
1 ONLINE +DATADG/mmsdb/onlinelog/group_1.271.1 NO
1 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_1_15dbl80x_.log YES
2 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_2_15dbl8l1_.log YES
3 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_3_15dbl93s_.log YES
4 ONLINE +DATADG/mmsdb/onlinelog/group_4.291.1 NO
4 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_4_1s9yx01j_.log YES
11 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------
1 1 17759 10485760 3 YES INACTIVE 178672877 06-DEC-05
2 1 17760 10485760 3 YES INACTIVE 178683645 06-DEC-05
3 1 17761 10485760 3 YES ACTIVE 178694045 06-DEC-05
4 1 17762 31457280 2 NO CURRENT 178703002 06-DEC-05
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 30m;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ----------- ------------- -----------
1 1 17759 10485760 3 YES INACTIVE 178672877 06-DEC-05
2 1 0 31457280 2 YES UNUSED 0
3 1 17761 10485760 3 YES INACTIVE 178694045 06-DEC-05
4 1 17762 31457280 2 NO CURRENT 178703002 06-DEC-05
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 30m;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------
1 1 17759 10485760 3 YES INACTIVE 178672877 06-DEC-05
2 1 0 31457280 2 YES UNUSED 0
3 1 0 31457280 2 YES UNUSED 0
4 1 17762 31457280 2 NO CURRENT 178703002 06-DEC-05
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 30m;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE +DATADG/mmsdb/onlinelog/group_3.275.3 NO
3 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_3_1s9z8pb6_.log YES
2 ONLINE +DATADG/mmsdb/onlinelog/group_2.273.3 NO
2 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_2_1s9yzv0f_.log YES
1 ONLINE +DATADG/mmsdb/onlinelog/group_1.271.3 NO
1 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_1_1s9z9rpy_.log YES
4 ONLINE +DATADG/mmsdb/onlinelog/group_4.291.1 NO
4 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_4_1s9yx01j_.log YES
8 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------
1 1 0 31457280 2 YES UNUSED 0
2 1 0 31457280 2 YES UNUSED 0
3 1 0 31457280 2 YES UNUSED 0
4 1 17762 31457280 2 NO CURRENT 178703002 06-DEC-05
|
Ok,现在开始提问,在以上的输出中,你可以指出多少个知识点?
如果我就以上输出来提问,会有多少知识是你不知道的?
历史上的今天...
>>
2019-12-06文章:
>>
2016-12-06文章:
>>
2010-12-06文章:
>>
2007-12-06文章:
>>
2006-12-06文章:
By eygle on 2005-12-06 16:28 |
Comments (16) |
FAQ | 564 |
Redo
老大提问啦,小弟捧个场.:)
以上是过程,主要做了是将一个原有三组日志文件,每组三个镜像文件,每个日志文件10M大小的系统,调整
为四组日志文件每组日志文件两个镜像,每个文件30M大小的系统.
其中有个知识点在v$log中的STATUS列,
CURRENT表示当前使用的自然是不可以drop的,
ACTIVE的表示非当前但是还是处于活动状态,日志中指向的缓存中的脏数据块还没有完全被刷到磁盘上,所
以也是不可以drop的,
INACTIVE表示不活动的,相应数据块都写入磁盘,这种状态如果需要归档并且已经归档,文件就可以drop了.
最后新增上去的日志组,在一次还没使用前处于的是一个UNUSED状态
ACTIVE和INACTIVE状态都可能未归档,是否归档可以看ARCHIVED列
另一个小知识点,这个系统使用了OMF和ASM
最后,每组日志文件有一个镜像处于 flash recovery area .
抛块砖头,大家继续,老大多指教.:)
谢谢捧场,后面的只说前面漏掉的就好了:)
呵呵,中间那段是前两天给一个工程人员的解释(也是在增redo log),copy过来的.
老大好眼力哦:)
v$logfile 中 ，
1 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_1_15dbl80x_.log YES 2 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_2_15dbl8l1_.log YES 3 ONLINE /msflsh/MMSDB/onlinelog/o1_mf_3_15dbl93s_.log YES
这个就是上面说的 “每组日志文件有一个镜像处于 flash recovery area ”吧？
它是干啥用的？
它的路径格式是谁定的？最后一个字段是啥意思？
它在 add logfile group 的时候就自动出现了，但是没有显示到 v$log 中。
这个请帅哥讲一讲呀。。。。。。
哇啊啊，啥时候加验证码了？
没有使用
alter system switch logfile
命令进行日志切换吗
switch logfile不是必需的,只是可以改变UNUSED的状态.
你新加了group 4 ,如果不用alter system switch logfile,那group 4 的status 怎么变成CURRENT的呢,刚刚加入应该为UNUSED的呀?
还有个问题,因为我没有用ASM,我每次用alter database add logfile group 4 size 50m; 命令去加日志组的时候,都只有一个文件产生?/orasys/flash_recovery_area/TEST/onlinelog/o1_mf_5_1shc37c7_.log,不像你那样有两个member,是因为ASM的原因吗?如果我设置db_create_online_log_dest_1 ,也只有一个member产生.
还有个问题,因为我没有用ASM,我每次用alter database add logfile group 4 size 50m; 命令去加日志组的时候,都只有一个文件产生?/orasys/flash_recovery_area/TEST/onlinelog/o1_mf_5_1shc37c7_.log,不像你那样有两个member,是因为ASM的原因吗?如果我设置db_create_online_log_dest_1 ,也只有一个member产生.
那个是数据库自动切换的,该数据库已经非常繁忙了.
我又可以跟着免费学点东东了哦～！好啊！我懂的不多！
col member for a50
SQL> select * from v$logfile;这是一个SQL句吗?
如果我用OEM连上管理,我能在直接进行更改他的大小和删减日志组吗?
替eygle老大回答ruanrong：
设置
db_create_online_log_dest_1
db_create_online_log_dest_2
即可.
问一下老大：
我在试验的时候，出现一个问题，就是一个日志组一直处于active状态，等了2分钟左右，是个人的数据库没有什么事务发生，而且switch的时候会触发checkpoint的啊，直到我显示的发出了checkpoint才显示为inactive，这是为什么呢？谢谢！
SQL> alter database drop logfile group 2;
数据库已更改。
SQL> alter database add logfile group 2 size 10m;
数据库已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 0 10485760 2 YES UNUSED 0
3 1 0 10485760 2 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -------------------------------------------------------------------------------------------
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0100_.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0101_.LOG
SQL> alter system switch logfile;
系统已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> alter system checkpoint;
系统已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES INACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL>
ruanrong的问题应该也是omf的范畴吧？好像应该是db_create_online_log_dest_n参数。
3 1 17716 10485760 3 YES ACTIVE 178149970 06-DEC-05
这一句看，group 3 已经归档了(arc:yes)，可他还在active,他在干什么呢？
在以上操作过程中没有体现出来将每个日志组3个镜像文件调整为2个镜像文件，其中删除那几个镜像文件是怎么实现的没有看出来？请指示，谢谢！！