今天闲来无事,看了一下数据库日志切换的频率,发现出现了来不及切换的情况:
adrci> show alert -p "message_text like '%cannot allocate new log%'" -term
ADR Home = /oracle/db/diag/rdbms/billquery/billquery:
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.