• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Database: Redo log operations (Add/Drop/Change Location)

by admin

Question: How to add or drop online redo logs? How to change redo log file location? How to determine the optimal size for redo logs using OPTIMAL_LOGFILE_SIZE?

How to add or drop online 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.

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.

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle RMAN Backup Shell Script Example
  2. Oracle Golden gate GGSCI commands quick reference (Cheat Sheet)
  3. Oracle Database 18c : How to Merge Partitions And Subpartitions Online
  4. How to get the Values Assigned by Default to a Profile in Oracle Database
  5. SQL query error when Using Shell Script
  6. How to Recover DROPPED PDB After Flashback of CDB
  7. How to solve ORA-28000: the account is locked
  8. Automatic Stop of Oracle Database (dbshut) not working in CentOS/RHEL 7 with systemd
  9. Oracle Tablespace Transport for a Single Partition
  10. How to check Oracle Database uptime

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright