Redo Log resizing

If you want resize the redo log file, you should drop the current log file.

 First you should switch the log file using this command

 sql> ALTER SYSTEM SWITCH LOGFILE;

SYSTEM ALTERED.

When an online redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping an online redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log file.

 To drop a member of an active group, you must first force a log switch.

 You cannot drop an active redo log group.

SQL> alter database drop logfile group 1;

SQL> alter system switch logfile;

By doing consecutive logfile switches (or sometimes more than twice) the next available log file group becomes active. At this stage you will be able to drop the group which was previously active.

Use the command line rm command or platform specific delete command to delete the redo log file at the OS level.

SQL> !rm /redo1/romford/redo01a_romford.log /redo2/romford/redo01b_romford.log

Add a new log file group 1 with desired size

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/redo1/romford/redo01a_romford.log’,’/redo2/romford/redo01b_romford.log’) size 100M;

Query v$log and v$logfile views to check the active log file group and its members.

Advertisements

About mpoojari
Oracle Database/Applications Administrator

Leave a Reply

Please log in using one of these methods to post your comment:

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: