%20(1).png)
How To Maintain and/or Add Redo Logs (Doc ID 602066.1)
The purpose of this document is to
demonstrate:
A. How to add or drop online redo logs.
B. How to change redo log file location.
c. How to determine the optimal size for redo
logs using OPTIMAL_LOGFILE_SIZE.
SOLUTION
A. How
to maintain and/or add redo logs.
1. Review information on existing redo logs.
SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#
2. Add new groups
ALTER DATABASE ADD LOGFILE group 4
('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 5
('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 6
('/log03A.dbf', '/log03B.dbf ') SIZE 512M;
If using ASM, use syntax as below:
SQL> ALTER DATABASE ADD LOGFILE GROUP 4
('+DATA') size 50M;
3. Check the status on all redo logs again.
SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#
4. Drop the online redo log groups that are not needed. You must have the ALTER DATABASE system privilege.
Note: Before dropping an online redo log
group, consider the following restrictions and precautions:
a. An
instance requires at least two groups of online redo log files, regardless of
the number of members in the groups. (A group is one or more members.)
b. You can drop an online redo log group only
if it is INACTIVE. If you need to drop the current group, first force a log
switch to occur.
By using this command :
ALTER SYSTEM SWITCH LOGFILE;
c. Make sure an online redo log group is
archived (if archiving is enabled) before dropping it. This can be determined by:
GROUP# ARC STATUS
---------
--- ----------------
1
YES ACTIVE
2
NO CURRENT
3
YES INACTIVE
4
YES UNUSED
5
YES UNUSED
6
YES UNUSED
d. Check
that the group is inactive and archived before dropping it .
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
ALTER DATABASE DROP LOGFILE GROUP 3;
e.
After dropping an online redo log group, make sure that the drop
completed successfully, and then use the appropriate operating system command
to delete the dropped online redo log files.
For more information refer to Note 395062.1
B. How to change redo log location
For changing the location of Online REDO
Logs, the best option is to create new REDO log Groups and drop the old REDO
log Groups once the old logs status changes to "inactive/unused".
Steps are given in the above section.
C. How to determine the optimal size for redo logs
You can use the V$INSTANCE_RECOVERY view
column OPTIMAL_LOGFILE_SIZE to determine the size of your online redo logs.
This field shows the redo log file size in megabytes that is considered optimal
based on the current setting of FAST_START_MTTR_TARGET. If this field consistently
shows a value greater than the size of your smallest online log, then you
should configure all your online logs
to be at least this size.
Note, however, that the redo log file size
affects the MTTR. In some cases, you may be able to
refine your choice of the optimal
FAST_START_MTTR_TARGET value by re-running the MTTR Advisor with your suggested
optimal log file size.
You can also refer to this Note 1038851.6 -
How to Estimate Size of Redo Logs
Please note that there is no column
OPTIMAL_LOGFILE_SIZE in
V$INSTANCE_RECOVERY view in 9i. It only
applies to 10g.