Oracle suggest to
configure the size of redo log files to switch once an hour during presented
load and they should no switch more frequently than every 20 minutes during
peak time therefore, we need to resize our log files to hold this redo information.
We cannot just resize our redo
log files, we have to drop and recreate them so we require at least two groups
of redo log files.
Let’s check how many log
file do we per hour for past two days:
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0))
"MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0))
"1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0))
"2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0))
"3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0))
"4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0))
"5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0))
"6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0))
"7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0))
"8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0))
"9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0))
"10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0))
"11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0))
"Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0))
"1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0))
"2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0))
"3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0))
"4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0))
"5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0))
"6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0))
"7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0))
"8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0))
"9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0))
"10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0))
"11PM"
from v$log_history
where trunc(first_time)>sysdate-2
group by to_char(first_time,'mm/dd/yy')
order by 1
LOGDATE MidN 1AM 2AM
3AM 4AM 5AM 6AM 7AM
8AM 9AM 10AM 11AM Noon 1PM 2PM 3PM 4PM 5PM 6PM 7PM 8PM 9PM 10PM 11PM
-------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
----------
06/16/13 8 7 7 10 8 7 7 7
10 7 13 8 7 8 7 7 8 7 7 7 7 7 7
8
06/17/13 7 7 7 8 8 7 7 10 7 7 9 7 8 7 10 0 0 0 0 0 0
0 0 0
Check the current size of
redo log file:
SQL> SELECT a.group#,
a.member, b.bytes/1024/1024 MByte FROM v$logfile a, v$log b WHERE a.group# =
b.group#;
GROUP# MEMBER MBYTE
------
------------------------------ -----
4 D:\APP\FINPROD\REDO04.LOG 50
3 D:\APP\FINPROD\REDO03.LOG 50
2 D:\APP\FINPROD\REDO02.LOG 50
1 D:\APP\FINPROD\REDO01.LOG 50
To determine the size of
log files, simply take the median of switches per hour which is 10 multiply by
50MB to result 500MB.
Now let’s start our steps:
1. Check the status of redo log files:
SQL> select group#,
status from v$log;
GROUP# STATUS
------ ----------------
1 INACTIVE
2 INACTIVE
3
INACTIVE
4 CURRENT
Note:
In case you find
any active status you would drop just issue:
SQL> alter system checkpoint global;
system altered.
2. Drop all inactive log groups
SQL> alter database
drop logfile group 4;
Database altered.
3. Re-create dropped redo log group
SQL> alter database add
logfile group 4 ('D:\APP\FINPROD\REDO04.LOG') size 500m reuse;
Database altered.
SQL> select
group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE#
BYTES ARC STATUS
------ ----------
---------- --- ----------------
1
96646 52428800 NO CURRENT
2
96644 52428800 YES INACTIVE
3
96645 52428800 YES INACTIVE
4 0
524288000 YES UNUSED
4. Switch redo log group to make the newly added group used (Current)
SQL> alter system
switch logfile;
System altered.
SQL> select
group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE#
BYTES ARC STATUS
------ ----------
---------- --- ----------------
1
96646 52428800 YES ACTIVE
2
96644 52428800 YES INACTIVE
3
96645 52428800 YES INACTIVE
4
96647 524288000 NO CURRENT
5. Repeat the same steps to rebuild all redo log groups
SQL> alter database drop logfile group 2;
Database altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP#
SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 96646
52428800 YES ACTIVE
3 96645
52428800 YES INACTIVE
4 96647
524288000 NO CURRENT
SQL> alter database add logfile group 2 ('D:\APP\FINPROD\REDO02.LOG')
size 500m reuse;
Database altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP#
SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 96646
52428800 YES ACTIVE
2 0
524288000 YES UNUSED
3 96645
52428800 YES INACTIVE
4 96647
524288000 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP#
SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 96646
52428800 YES ACTIVE
2 96648
524288000 NO CURRENT
3 96645
52428800 YES INACTIVE
4 96647
524288000 YES ACTIVE
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3
('D:\APP\FINPROD\REDO03.LOG') size 500m reuse;
Database altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP#
SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 96646
52428800 YES ACTIVE
2 96648
524288000 NO CURRENT
3 0
524288000 YES UNUSED
4 96647
524288000 YES ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP#
SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 96646
52428800 YES ACTIVE
2 96648
524288000 YES ACTIVE
3 96649
524288000 NO CURRENT
4 96647
524288000 YES ACTIVE
SQL> alter system checkpoint global ;
System altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 96646
52428800 YES INACTIVE
2 96648
524288000 YES INACTIVE
3 96649
524288000 NO CURRENT
4 96647
524288000 YES INACTIVE
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1
('D:\APP\FINPROD\REDO01.LOG') size 500m reuse;
Database altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP#
SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 0
524288000 YES UNUSED
2 96648
524288000 YES INACTIVE
3 96649
524288000 NO CURRENT
4 96647
524288000 YES INACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status
from v$log;
GROUP#
SEQUENCE# BYTES ARC STATUS
------ ---------- ---------- --- ----------------
1 96650
524288000 NO CURRENT
2 96648
524288000 YES INACTIVE
3 96649
524288000 YES ACTIVE
4 96647
524288000 YES INACTIVE
No comments:
Post a Comment