How to resize a redolog file.
1) Check the redo log groups, their members and size
set linesize 300
col group# format 99
col member format a70
col bytes format 999999,9999
SELECT a.group#, a.member, b.bytes/1024/1024 as megas
FROM v$logfile a, v$log b
WHERE a.group# = b.group#;
GROUP# MEMBER MEGAS
------ ---------------------------------------------------------------------- ----------
3 /data/oracle11/test/redo03.log 50
2 /data/oracle11/test/redo02.log 50
1 /data/oracle11/test/redo01.log 50
2) Check which redo logs groups are status active current and inactive.
select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
3) We will do all in the group order 1, 2 and 3 respectively
alter system switch logfile; // We perform this comand only if group 1 is inactive, in this case it is not necessary.
Only if appears this error : "ORA-01624: log 1 needed for crash recovery of instance ORA920" when we perform the group drop, then execute "ALTER SYSTEM CHECKPOINT GLOBAL" command.
In case does not appears the error proceed directly with "ALTER DATABASE DROP LOGFILE GROUP 1" command
3.1)
A) The group 1 will be created with a size of 256 megas.ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER DATABASE DROP LOGFILE GROUP 1;
alter database add logfile group 1 ('/data/oracle11/test/redo01.log) size 256m reuse;
3.2)
A) The group 2 will be created with a size of 256 megas.
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER DATABASE DROP LOGFILE GROUP 2;
alter database add logfile group 2 ('/data/oracle11/test/redo02.log) size 256m reuse;
3.3)
A) The group 3 will be created with a size of 256 megas.
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER DATABASE DROP LOGFILE GROUP 3;
alter database add logfile group 3 ('/data/oracle11/test/redo03.log) size 256m reuse;
4- Check the redo logs size again
set linesize 300
col group# format 99
col member format a70
col bytes format 999999,9999
SELECT a.group#, a.member, b.bytes/1024/1024 as megas
FROM v$logfile a, v$log b
WHERE a.group# = b.group#;
GROUP# MEMBER MEGAS
------ ---------------------------------------------------------------------- ----------
3 /data/oracle11/test/redo03.log 256
2 /data/oracle11/test/redo02.log 256
1 /data/oracle11/test/redo01.log 256
Comments