Tuesday, July 15, 2008

How, then, do you increase the size of your log files ?

The simple answer is - you can't - you have to create new ones and drop the old ones, with an approach something like the following:


-- See what you have at present
select group#, member from v$logfile;

3 C:\ORACLE\ORADATA\O8I\REDO02.LOG
4 C:\ORACLE\ORADATA\O8I\REDO01.LOG

-- add two new files
alter database add logfile 'c:\oracle\oradata\o8i\redo03.log' size 4M ;

Database altered.

alter database add logfile 'c:\oracle\oradata\o8i\redo04.log' size 4M ;

Database altered.

-- Make sure that the current log file is one of the new ones
alter system switch logfile;

System altered.

alter system switch logfile;

System altered.

-- Now drop the old log files
alter database drop logfile 'c:\oracle\oradata\o8i\redo02.log';

Database altered.

alter database drop logfile 'c:\oracle\oradata\o8i\redo01.log';

Database altered.

There are a couple of variations on dropping log files. The example above works if you are not using Oracle's log file duplexing to keep two copies of each log; however if you are using duplexing you have to use slightly different commands; there are a couple of possibilites listed here:

-- See what you have at present
select group#, member from v$logfile;

3 C:\ORACLE\ORADATA\O8I\REDO02A.LOG
3 D:\ORACLE\ORADATA\O8I\REDO02B.LOG
4 C:\ORACLE\ORADATA\O8I\REDO01A.LOG
4 D:\ORACLE\ORADATA\O8I\REDO01B.LOG
-- add two new files

alter database add logfile (
'c:\oracle\oradata\o8i\redo03a.log',
'd:\oracle\oradata\o8i\redo03b.log'
) size 4M reuse
;


Database altered.

alter database add logfile (
'c:\oracle\oradata\o8i\redo04a.log',
'd:\oracle\oradata\o8i\redo04b.log'
) size 4M reuse
;


Database altered.

-- Make sure that the current log file is one of the new ones
alter system switch logfile;

System altered.

alter system switch logfile;

System altered.

-- Now drop the old log files
-- We get rid of the first group in one show (2a and 2b)
-- then one file (member) from the next group
-- then the second group by specifying the name of the one remaining file.
alter database drop logfile group 3;

Database altered.

alter database drop logfile member 'c:\oracle\oradata\o8i\redo01a.log';

Database altered.
alter database drop logfile 'd:\oracle\oradata\o8i\redo01b.log';

Database altered.

No comments: