Databases Reference
In-Depth Information
The unarchived log group may be needed for media recovery if the last database backups were taken before the
redo information in the log was created. This means if you attempt to perform media recovery, you won't be able to
recover any information in the damaged log file or any transactions that were created after that log.
If the clear logfile command does not succeed because of an I/O error and it's a permanent problem, then you
will need to consider dropping the log group and re-creating it in a different location. See the next two subsections for
directions on how to drop and re-create a log file group.
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
log file 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:
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;
