eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 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,现在开始提问,在以上的输出中,你可以指出多少个知识点?

如果我就以上输出来提问,会有多少知识是你不知道的?


历史上的今天...
    >> 2016-12-06文章:
    >> 2010-12-06文章:
    >> 2007-12-06文章:
           DBA警世录:bootstrap$的禁忌
    >> 2006-12-06文章:

无觅

By eygle on 2005-12-06 16:28 | Comments (16) | FAQ | 564 |

16 Comments

老大提问啦,小弟捧个场.:)

以上是过程,主要做了是将一个原有三组日志文件,每组三个镜像文件,每个日志文件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个镜像文件,其中删除那几个镜像文件是怎么实现的没有看出来?请指示,谢谢!!


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com