ADD-DROP Redo Log Files In Oracle Database
In the real world, we may encounter scenarios where we need to or asked to increase/decrease the size of redo log files as log switches creating contentions at the database. For example, by increasing the redo log file size, the number of log switches can be decreased which in turn will improve the database performance. Since there is no provision for resizing the redo log files(members), we need to recreate the redo log groups either by adding groups or dropping groups in order to increase or decrease the size. In this article, we will be discussing how to create and drop redo log groups along with add member to group:
1.Check the location of redo log groups and members. Click here for sample output.
set lines 333 col GROUP# for 9999 col TYPE for a30 col MEMBER for a45 select GROUP#,TYPE,MEMBER from v$logfile; prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
2. Check the status of redo log files.Click here for sample output.
set lines 222 col GROUP# for 99 col THREAD# for 99 col SEQUENCE# for 9999 col MEMBERS for 99 col ARCHIVED for a15 col STATUS for a15 col BLOCKSIZE for 999 col FIRST_CHANGE# for 99999999 col CON_ID for 9 col BYTES for 9999999999 set numwidth 20 select * from v$log; prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
3. Addition and Deletion of Redo log Group
a) To add a new redo log file group
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 ('/oradata/lab03/redo04a.log') size 100M; Database altered. ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('/oradata/lab03/redo05a.log') size 100M; Database altered.
b) Check the status of redo log files again after addition, refer SQL code from step# 2. Click here for sample output
The status can be described as :
UNUSED : This is often the status of a redo log that’s newly added or immediately after RESETLOGS. Online redo has not been written ever.
CURRENT : This can be the status of the redo log where it’s active and this might be either open or closed.
ACTIVE : This can be the status of active and requires for crash recovery also for block recovery. This status may or might not be archived.
CLEARING: Once we execute the SQL command “ALTER DATABASE CLEAR LOGFILE” log will be re-created as an empty log. Once the log is cleared, the status will be changed to UNUSED.
CLEARING_CURRENT: Current log will be cleared of a closed thread. The log can remain in this status due to some failure within the switch like an I/O error while writing the new log header.
INACTIVE: Log isn’t any longer needed for example instance recovery. It’s going to be in use for media recovery and it may or may not be archived.
INVALIDATED : Archived this current redo log without a log switch.
c) To add a member to the newly created redo log file-group i.e. group 5 in the below example:
ALTER DATABASE ADD LOGFILE MEMBER '/u01/lab03/redo05b.log' TO GROUP 5; Database altered. select GROUP#,TYPE,MEMBER from v$logfile; prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$ GROUP# TYPE MEMBER ------ ------------------------------ --------------------------------------------- 1 ONLINE /oradata/lab03/redo01a.log 1 ONLINE /u01/lab03/redo01b.log 2 ONLINE /oradata/lab03/redo02a.log 2 ONLINE /u01/lab03/redo02b.log 3 ONLINE /oradata/lab03/redo03a.log 3 ONLINE /u01/lab03/redo03b.log 4 ONLINE /oradata/lab03/redo04a.log 5 ONLINE /oradata/lab03/redo05a.log 5 ONLINE /u01/lab03/redo05b.log 9 rows selected. SQL> $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
d) To delete a redo log file group, 2 points required to taken into consideration from v$log output:
If these conditions satisfy, we are good to go, else it requires to keep rotating (alter system switch logfile) until the above-mentioned conditions are satisfied i.e. redo group is archived and no active transactions are being written to this group, refer SQL code from step# 2. Click here for sample output.
alter system switch logfile; System altered. select * from v$log;
e) Although, this is an online activity, however, it is advisable to drop/create a redo when the redo is not too busy. So look for the time when there is less activity, to drop the redo log file, use the below command. Click here for sample output.
ALTER DATABASE DROP LOGFILE GROUP 4; Database altered. set lines 333 col GROUP# for 9999 col TYPE for a30 col MEMBER for a45 select GROUP#,TYPE,MEMBER from v$logfile; prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.
826 total views, 1 views today
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
I am Sruti Banerjee, having experience of 3 years as an Oracle DBA, I have good interpersonal skills that can help in attaining the desired level of team coordination, self-starter & quick learner. I am having rich experience in Oracle database management, Backup and Recovery, Security, Installation, Up-gradation, Patching, Migration, Cloning of databases, Dataguard (Physical), and a wide range of other database administration activities.
Thanks for the visits!
Share Learn Grow!