Vergroten Redo logs in ASM

script to check the frequencey of oracle log switches

select to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v$log_history
group by to_char(first_time,'YYYY-MON-DD');



select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            IS_RECOVERY_DEST_FILE
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------
         1         ONLINE  +DATA/jmt/onlinelog/group_1.257.818339419                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         NO                  
         2         ONLINE  +DATA/jmt/onlinelog/group_2.258.818339421                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         NO                  
         3         ONLINE  +DATA/jmt/onlinelog/group_3.259.818339421    


select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ---------- ------------ ---------
         1          1       1396   52428800        512          1 NO       INACTIVE            5806213150 08-04-14     5806231379 08-04-14
         2          1       1397   52428800        512          1 NO       INACTIVE            5806231379 08-04-14     5806253167 08-04-14
         3          1       1398   52428800        512          1 NO       CURRENT             5806253167 08-04-14     2,8147E+14         
 

voeg grotere logfiles toe

alter database add logfile group 4 ('+DATA') size 500M;
alter database add logfile group 5 ('+DATA') size 500M;
alter database add logfile group 6 ('+DATA') size 500M; 


zorg dat alle oude logfiles OP INACTIVE komen. met alter system switch logfile; ALS DIT NIET WERKT DAN MET alter system CHECKPOINT;

Gooi dan de oude logfiles weg

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;




PS stel dat je zowel in FRA als in ASM redo logs hebt dan volgende opmerking

met commando 
alter database add logfile group 6 size 150M;
creeer je zowel in FRA als ASM een redo log van group 6