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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: