今天闲来无事，看了一下数据库日志切换的频率，发现出现了来不及切换的情况：
adrci> show alert -p "message_text like '%cannot allocate new log%'" -term
ADR Home = /oracle/db/diag/rdbms/billquery/billquery:
*************************************************************************
2011-07-18 05:26:50.967000 +05:30
Thread 1 cannot allocate new log, sequence 4
2011-07-18 05:27:15.799000 +05:30
Thread 1 cannot allocate new log, sequence 5
2011-09-20 22:43:41.626000 +05:30
Thread 1 cannot allocate new log, sequence 18
2011-09-20 22:43:53.057000 +05:30
Thread 1 cannot allocate new log, sequence 19
2011-09-21 06:24:10.110000 +05:30
Thread 1 cannot allocate new log, sequence 54
2011-09-21 18:00:17.236000 +05:30
Thread 1 cannot allocate new log, sequence 61
2011-10-13 16:00:08.339000 +05:30
Thread 1 cannot allocate new log, sequence 47
说明redo日志的大小或组数需要进行调整。
引用oracle document的原话：
During testing, the easiest way to determine whether the current redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database alert log. If messages indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups.
查看日志，发现有3组日志大小还是50M，于是决定将这3组日志重建为1G：
--查看日志，发现是1，2，3 组日志文件大小为50M
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 67 52428800 1 YES INACTIVE 11999011 10/16/2011 06:00:08
2 1 68 52428800 1 YES INACTIVE 12011491 10/16/2011 11:00:07
3 1 69 52428800 1 YES INACTIVE 12023974 10/16/2011 16:00:07
4 1 70 1063256064 1 YES INACTIVE 12036343 10/16/2011 21:00:07
5 1 71 1063256064 1 NO CURRENT 12091328 10/17/2011 06:00:09
6 1 66 1063256064 1 YES INACTIVE 11994013 10/16/2011 03:58:11
6 rows selected.
--要增加大小，先确保裸设备能有1G的空间
SQL> select * from v$logfile; -->先查出1，2，3组日志文件的裸设备：
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------- ---
3 ONLINE /dev/rlv_redo3 NO
2 ONLINE /dev/rlv_redo2 NO
1 ONLINE /dev/rlv_redo1 NO
4 ONLINE /dev/rlv_redo4 NO
5 ONLINE /dev/rlv_redo5 NO
6 ONLINE /dev/rlv_redo6 NO
6 rows selected.
# lsvg oravg
VOLUME GROUP: oravg VG IDENTIFIER: 00f6aaee00004c0000000132c9245241
VG STATE: active PP SIZE: 256 megabyte(s) -->pp的大小为256M
VG PERMISSION: read/write TOTAL PPs: 139 (35584 megabytes)
MAX LVs: 256 FREE PPs: 4 (1024 megabytes)
LVs: 22 USED PPs: 135 (34560 megabytes)
OPEN LVs: 14 QUORUM: 2 (Enabled)
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: yes
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
PV RESTRICTION: none
# lsvg -l oravg |grep lv_redo --> redo日志都有4个pp,裸设备的空间没问题
lv_redo1 raw 4 4 1 open/syncd N/A
lv_redo2 raw 4 4 1 open/syncd N/A
lv_redo3 raw 4 4 1 open/syncd N/A
lv_redo4 raw 4 4 1 open/syncd N/A
lv_redo5 raw 4 4 1 open/syncd N/A
lv_redo6 raw 4 4 1 open/syncd N/A
--查看当前组，要重建的1，2，3组都不是当前组，可以直接重建
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 67 52428800 1 YES INACTIVE 11999011 10/16/2011 06:00:08
2 1 68 52428800 1 YES INACTIVE 12011491 10/16/2011 11:00:07
3 1 69 52428800 1 YES INACTIVE 12023974 10/16/2011 16:00:07
4 1 70 1063256064 1 YES INACTIVE 12036343 10/16/2011 21:00:07
5 1 71 1063256064 1 NO CURRENT 12091328 10/17/2011 06:00:09
6 1 66 1063256064 1 YES INACTIVE 11994013 10/16/2011 03:58:11
6 rows selected.
--删除日志组1
SQL> alter database drop logfile group 1;
Database altered.
--创建日志组1，发现这里没有报错 ???
SQL> alter database add logfile group 1 '/dev/rlv_redo1' size 1014m;
Database altered.
--查看日志文件，1组的大小已经变为1G
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 0 1063256064 1 YES UNUSED 0
2 1 68 52428800 1 YES INACTIVE 12011491 10/16/2011 11:00:07
3 1 69 52428800 1 YES INACTIVE 12023974 10/16/2011 16:00:07
4 1 70 1063256064 1 YES INACTIVE 12036343 10/16/2011 21:00:07
5 1 71 1063256064 1 NO CURRENT 12091328 10/17/2011 06:00:09
6 1 66 1063256064 1 YES INACTIVE 11994013 10/16/2011 03:58:11
6 rows selected.
--再将2组和3组重建,直接重建都没有报错
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 2 '/dev/rlv_redo2' size 1014m;
SQL> alter database add logfile group 3'/dev/rlv_redo3' size 1014m;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 0 1063256064 1 YES UNUSED 0
2 1 0 1063256064 1 YES UNUSED 0
3 1 0 1063256064 1 YES UNUSED 0
4 1 70 1063256064 1 YES INACTIVE 12036343 10/16/2011 21:00:07
5 1 71 1063256064 1 NO CURRENT 12091328 10/17/2011 06:00:09
6 1 66 1063256064 1 YES INACTIVE 11994013 10/16/2011 03:58:11
6 rows selected.
--切换几次日志，确保新建的日志组都能写
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 72 1063256064 1 YES ACTIVE 12140959 10/17/2011 10:57:54
2 1 73 1063256064 1 YES ACTIVE 12140963 10/17/2011 10:57:59
3 1 74 1063256064 1 YES ACTIVE 12140966 10/17/2011 10:58:00
4 1 76 1063256064 1 NO CURRENT 12140972 10/17/2011 10:58:05
5 1 71 1063256064 1 YES ACTIVE 12091328 10/17/2011 06:00:09
6 1 75 1063256064 1 YES ACTIVE 12140969 10/17/2011 10:58:02
6 rows selected.
这里发现一个问题，以前在10G文件系统测试的时候，drop日志组只是在逻辑上删除，在物理上是没有删除的，要重建一定要原来的日志文件rm掉才能创建，这里没有报错，是11G的原因还是裸设备的原因？
刚好有个建在文件系统上的11G测试库可以拿来测试：
--查看当前组，为1组，决定拿3组做测试
sys@ocm> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE
sys@ocm> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------
F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO03.LOG
F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO02.LOG
F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO01.LOG
--删除日志组3
sys@ocm> alter database drop logfile group 3;
数据库已更改。
--创建日志组3，果然报错
sys@ocm> alter database add logfile group 3 'F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO03.LOG' size 52428800;
alter database add logfile group 3 'F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO03.LOG' size 52428800
*
第 1 行出现错误:
ORA-00301: 添加日志文件 'F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO03.LOG' 时出错 - 无法创建文件
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在
--将原来的日志文件删除后再创建日志组3，没有报错
sys@ocm> ho del F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO03.LOG
sys@ocm> alter database add logfile group 3 'F:\APP\ADMINISTRATOR\ORADATA\OCM\REDO03.LOG' size 52428800;
数据库已更改。
总结：11G drop日志组也只是逻辑上删除，不会物理上删除日志文件。但是日志文件是创建在裸设备上，可以直接重新创建。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
