PL/SQL script: Resizing Oracle Redo Logs
This PL/SQL script can be used to resize Oracle Redo Logfiles. All existing logfile members of all groups will be resized to a specific size.
Table of Contents
Features
- The script brings all redo log files to the same size
- The new size of the logfile groups can be specified in the script
- The script works on regular databases and on RAC databases
- If a redo log switch is needed, the script will do it.
- It can be used on databases in archivelog and non archivelog mode
- There is a dryrun mode that only shows the SQL commands without actually resizing the log files
- It works with OMF and non OMF redo log files
Limitations
- The script cannot be used to resize standby redo logs
- The script does not work in a Data Guard/Oracle Standby environment
- The database needs to have at least 3 existing logfile groups (per thread)
Usage
- Connect as a database user with dba privileges (e.g. system or sys)
- Adjust the new redo log file size in the script
- Maybe: Run the script with l_dryrun_on := true to just show the commands without performing any changes on the logfiles
- Run the script
- In case of problems you can enable debug mode (set l_debug_on to true)
- As always: run the script on a test system first to verify if it works as intended (and let me know if it doesn’t)
Script
set serveroutput on
declare
l_nsize number := 128; -- new redo log file size in mb
l_redo_switch_timeout number := 60; -- seconds to wait for a redo log switch in case of RAC on a remote instance
l_debug_on boolean := false; -- turn debug output on (true) or off (false)
l_dryrun_on boolean := false; -- set to true to show SQL commands without resizing the redo log files
type t_array is table of varchar2(513) index by binary_integer;
l_members t_array;
l_index binary_integer;
l_nsizeb number := l_nsize * 1024 * 1024;
l_status varchar2(16);
l_mingrps number;
l_omf number;
l_connected_instance number;
l_group_instance number;
l_message varchar2(1800 byte);
l_alert_status integer;
l_cmd varchar2(512);
begin
-- prechecks and initialization
select min(count(*)) into l_mingrps from v$log group by thread#;
if l_mingrps < 3
then
raise_application_error(-20500, 'Must have at least 3 redo log groups per thread');
end if;
select instance_number into l_connected_instance from v$instance;
dbms_alert.register('logfileswitch',true);
dbms_output.put_line('Redo Logs before resize:');
dbms_output.new_line;
for v_rec in (select l.thread#, lf.group#, l.bytes/1024/1024 size_mb, lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by l.thread#, lf.group#) loop
dbms_output.put_line('Thread: '||v_rec.thread#||' Group: '||v_rec.group#||' Size: '||v_rec.size_mb||' MB redo log filename: '||v_rec.member);
end loop;
dbms_output.new_line;
dbms_output.put_line('Redo Logs will be resized to: '||l_nsize||' MB ('||l_nsizeb||' bytes)');
dbms_output.new_line;
for l_thrgrp in (select thread#, group# from v$log order by group# desc)
loop
dbms_output.put_line('Resizing Logfile Group: '||l_thrgrp.group#||' Thread: '||l_thrgrp.thread#);
-- check if group contains OMF files
l_omf := 0;
for l_member in (select member from v$logfile lf, v$log l where lf.group#=l.group# and l.thread#=l_thrgrp.thread# and l.group#=l_thrgrp.group#)
loop
l_omf := l_omf + sys.dbms_metadata_util.is_omf(l_member.member);
end loop;
if l_debug_on then dbms_output.put_line('Group '||l_thrgrp.group#||' contains OMF files (0=no,>0=yes): '||l_omf); end if;
-- make log group inactive
select status into l_status from v$log where thread#=l_thrgrp.thread# and group#=l_thrgrp.group#;
if l_debug_on then dbms_output.put_line(l_status); end if;
if l_status = 'CURRENT' then
select instance_number into l_group_instance from gv$instance where thread#=l_thrgrp.thread#;
if l_group_instance<>l_connected_instance then -- we need to run a switch logfile on another RAC instance. alter system archive log curent is not used since it requires the database to be in archivelog mode.
if not l_dryrun_on then
dbms_scheduler.create_job(
job_name => 'switchlogfile',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
execute immediate ''alter system switch logfile'';
dbms_alert.signal(''logfileswitch'',''done'');
END;',
start_date => systimestamp,
enabled => TRUE);
dbms_scheduler.set_attribute(
name => 'switchlogfile',
attribute => 'instance_id',
value => l_group_instance);
dbms_alert.waitone('logfileswitch', l_message, l_alert_status, l_redo_switch_timeout);
if l_alert_status=1 then
raise_application_error(-20500, 'Timeout ('||l_redo_switch_timeout||' seconds) occured while waiting for redo log switch. You might want to increase l_redo_switch_timeout');
end if;
else
dbms_output.put_line('dryrun: alter system switch logfile; on RAC instance: '||l_group_instance);
end if;
if l_debug_on then dbms_output.put_line('performed RAC remote instance logfile switch'); end if;
else -- we run the switch logfile on the local instance
l_cmd:='alter system switch logfile';
if not l_dryrun_on then
execute immediate l_cmd;
else
dbms_output.put_line('dryrun: '||l_cmd||'; on the connected instance');
end if;
if l_debug_on then dbms_output.put_line('performed local instance logfile switch'); end if;
end if;
select status into l_status from v$log where thread#=l_thrgrp.thread# and group#=l_thrgrp.group#;
end if;
if l_status = 'ACTIVE' then
l_cmd:='alter system checkpoint';
if not l_dryrun_on then
execute immediate l_cmd;
else
dbms_output.put_line('dryrun: '||l_cmd||';');
end if;
if l_debug_on then dbms_output.put_line('performed checkpoint'); end if;
select status into l_status from v$log where thread#=l_thrgrp.thread# and group#=l_thrgrp.group#;
end if;
if l_debug_on then dbms_output.put_line(l_status); end if;
-- store existing member filenames (non OMF)
select member bulk collect into l_members from v$logfile lf, v$log l where lf.group#=l.group# and l.thread#=l_thrgrp.thread# and l.group#=l_thrgrp.group# and sys.dbms_metadata_util.is_omf(lf.member) = 0;
l_index := l_members.first;
-- drop log group
l_cmd:='alter database drop logfile group '||l_thrgrp.group#;
if not l_dryrun_on then
execute immediate l_cmd;
else
dbms_output.put_line('dryrun: '||l_cmd||';');
end if;
-- (re) create log group
if l_omf > 0 then -- for log group containing OMF files
l_cmd:='alter database add logfile thread '||l_thrgrp.thread#||' group '||l_thrgrp.group#||' size '||l_nsizeb;
if not l_dryrun_on then
execute immediate l_cmd;
else
dbms_output.put_line('dryrun: '||l_cmd||';');
end if;
else -- for log groups containing only non OMF files
l_cmd:='alter database add logfile thread '||l_thrgrp.thread#||' group '||l_thrgrp.group#||' ('''||l_members(1)||''') size '||l_nsizeb||' reuse';
if not l_dryrun_on then
execute immediate l_cmd;
else
dbms_output.put_line('dryrun: '||l_cmd||';');
end if;
l_index := l_members.next(l_index);
end if;
-- add additional non OMF members
while l_index is not null loop
l_cmd:='alter database add logfile member '''||l_members(l_index)||''' reuse to group '||l_thrgrp.group#;
if not l_dryrun_on then
execute immediate l_cmd;
else
dbms_output.put_line('dryrun: '||l_cmd||';');
end if;
l_index := l_members.next(l_index);
end loop;
dbms_output.new_line;
end loop;
dbms_alert.remove('logfileswitch');
dbms_output.put_line('Redo Logs after resize:');
dbms_output.new_line;
for v_rec in (select l.thread#, lf.group#, l.bytes/1024/1024 size_mb, lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by l.thread#, lf.group#) loop
dbms_output.put_line('Thread: '||v_rec.thread#||' Group: '||v_rec.group#||' Size: '||v_rec.size_mb||' MB redo log filename: '||v_rec.member);
end loop;
end;
/
Sample Output:
Redo Logs before resize:
Thread: 1 Group: 1 Size: 56 MB redo log filename: +DATA/ORCL/ONLINELOG/group_1.314.1200988351
Thread: 1 Group: 1 Size: 56 MB redo log filename: +FRA/ORCL/ONLINELOG/group_1.266.1200988353
Thread: 1 Group: 2 Size: 56 MB redo log filename: +FRA/ORCL/ONLINELOG/group_2.265.1200988341
Thread: 1 Group: 2 Size: 56 MB redo log filename: +DATA/ORCL/ONLINELOG/group_2.313.1200988341
Thread: 1 Group: 5 Size: 56 MB redo log filename: +DATA/ORCL/ONLINELOG/group_5.306.1200988331
Thread: 1 Group: 5 Size: 56 MB redo log filename: +FRA/ORCL/ONLINELOG/group_5.261.1200988331
Thread: 2 Group: 3 Size: 56 MB redo log filename: +DATA/ORCL/ONLINELOG/group_3.297.1200988335
Thread: 2 Group: 3 Size: 56 MB redo log filename: +FRA/ORCL/ONLINELOG/group_3.259.1200988335
Thread: 2 Group: 4 Size: 56 MB redo log filename: +DATA/ORCL/ONLINELOG/group_4.298.1200988331
Thread: 2 Group: 4 Size: 56 MB redo log filename: +FRA/ORCL/ONLINELOG/group_4.260.1200988331
Thread: 2 Group: 6 Size: 56 MB redo log filename: +FRA/ORCL/ONLINELOG/group_6.262.1200988329
Thread: 2 Group: 6 Size: 56 MB redo log filename: +DATA/ORCL/ONLINELOG/group_6.307.1200988329
Redo Logs will be resized to: 50 MB (52428800 bytes)
Resizing Logfile Group: 6 Thread: 2
Resizing Logfile Group: 5 Thread: 1
Resizing Logfile Group: 4 Thread: 2
Resizing Logfile Group: 3 Thread: 2
Resizing Logfile Group: 2 Thread: 1
Resizing Logfile Group: 1 Thread: 1
Redo Logs after resize:
Thread: 1 Group: 1 Size: 50 MB redo log filename: +DATA/ORCL/ONLINELOG/group_1.315.1200988661
Thread: 1 Group: 1 Size: 50 MB redo log filename: +FRA/ORCL/ONLINELOG/group_1.267.1200988661
Thread: 1 Group: 2 Size: 50 MB redo log filename: +FRA/ORCL/ONLINELOG/group_2.265.1200988651
Thread: 1 Group: 2 Size: 50 MB redo log filename: +DATA/ORCL/ONLINELOG/group_2.313.1200988651
Thread: 1 Group: 5 Size: 50 MB redo log filename: +DATA/ORCL/ONLINELOG/group_5.306.1200988639
Thread: 1 Group: 5 Size: 50 MB redo log filename: +FRA/ORCL/ONLINELOG/group_5.261.1200988639
Thread: 2 Group: 3 Size: 50 MB redo log filename: +DATA/ORCL/ONLINELOG/group_3.297.1200988645
Thread: 2 Group: 3 Size: 50 MB redo log filename: +FRA/ORCL/ONLINELOG/group_3.259.1200988645
Thread: 2 Group: 4 Size: 50 MB redo log filename: +DATA/ORCL/ONLINELOG/group_4.298.1200988641
Thread: 2 Group: 4 Size: 50 MB redo log filename: +FRA/ORCL/ONLINELOG/group_4.260.1200988641
Thread: 2 Group: 6 Size: 50 MB redo log filename: +FRA/ORCL/ONLINELOG/group_6.262.1200988639
Thread: 2 Group: 6 Size: 50 MB redo log filename: +DATA/ORCL/ONLINELOG/group_6.307.1200988639
PL/SQL procedure successfully completed.
Leave a Reply