Database Reference
In-Depth Information
Dropping a Log File Group
The alternative to clearing a logfile group (which tells Oracle to re-create the logfile) is to drop and re-create the
logfile group. You might need to do this if you need to re-create the logfile group in a different location because the
original location is damaged or not available.
A log group has to have an inactive status before you can drop it. You can check the status of the log group, as
shown here:
SQL> select group#, status, archived, thread#, sequence# from v$log;
You can drop a log group with the
drop logfile group
command:
SQL> alter database drop logfile group <group #>;
If you attempt to drop the current online log group, Oracle will return an ORA-01623 error stating that you cannot
drop the current group. Use the
alert system switch logfile
command to switch the logs and make the next group
the current group.
After a log switch, the log group that was previously the current group will retain an active status as long as it
contains redo that Oracle requires to perform crash recovery. If you attempt to drop a log group with an active status,
Oracle will throw an ORA-01624 error stating that the log group is required for crash recovery. Issue an
alter system
checkpoint
command to make the log group inactive.
Additionally, you cannot issue a drop logfile group command if it leaves you with only one log group left in
your database. If you attempt to do this, Oracle will throw an ORA-01567 error and inform you that dropping the log
group is not permitted because it would leave you with less than two logs groups for your database (Oracle minimally
requires two log groups to function).
Adding a Log File Group
You can add a new log group with the
add logfile group
command:
SQL> alter database add logfile group <group_#>
('/directory/file') SIZE <bytes> K|M|G;
You can specify the size of the log file in bytes, kilobytes, megabytes, or gigabytes. The following example adds a
log group with two members sized at 50MB:
SQL> alter database add logfile group 2
('/u01/oraredo/O12C/redo02a.rdo',
'/u02/oraredo/O12C/redo02b.rdo') SIZE 50M;
If for some reason the log file members already exist on disk, you can use the reuse clause to overwrite them:
alter database add logfile group 2
('/u01/oraredo/O12C/redo02a.rdo',
'/u02/oraredo/O12C/redo02b.rdo') SIZE 50M reuse;
■
Tip
see Chapter 2 for an example of moving an online redo log group.
