Renaming Logfiles

Renaming logfiles in Oracle database requires several steps to be performed. If you want to move logfile to another destination or you just want to give it the meaningful name  you should do the logfile renaming.

Note that before renaming logfile, target online redo log should exist. Operating system file is not renamed just control file is updated and pointed to the new redo logfile.

First of all, let’s identify existing redo logfiles.

–Query this:

select member
from v$logfile

–My result

MEMBER
------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG

You can’t directly rename it you should do the following steps:

1. Shutdown the database

SQL>shutdown immediate;

2. Copy/rename logfile to the new location by using OS command.

3. Mount the database.

SQL>startup mount;

4. Rename the logfile in the controlfile.

SQL>alter database rename
file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG'
to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG';

5. Open the database

SQL>alter database open;

6. It is recommended to backup the controlfile.

To check that logfile was given the desired name, do the following.

–Query this:

select member
from v$logfile

–My result

MEMBER
------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG

Another way is to add lofile member by desired name and location, then drop the existing logfile member.

Adding a Member

alter database add logfile member
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'
to ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG');

This script adds lofile member to the same redo log group and the size will be same as REDO04.LOG.

–To see the result

select group#
       ,member
from v$logfile

–Result

GROUP#|MEMBER
---------------------------------------------------------
3     | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG
3     | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
2     | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
1     | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG

Deleting a Member

Take into the consideration that you can only drop redo log members that are not in the current or active redo log group.

Current redo log group is the group in which redo data is being written right now.
Active redo log group is the group that are required for instance recovery.
The other one is inactive group, you can only drop member from inactive group.

You can switch between redo log groups and make redo log member inactive. Remember that log switching forces archiver to archive redo log group and also makes log group not current.

alter system switch logfile;

Note that this is the manual switch. In general switching between redo log groups happens in the following cases:

* When the redo log file is full.
* When the instance is shut down with normal, transactional, or immediate.
* When a tablespace is put into backup mode or is changed to read-only.

–To drop the member

alter database drop logfile member
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG';

Note that OS file is not removed you should manually delete it.