• 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

How to relocate the redo log files to a different location on disk

by admin

Offline Method

1. Shut down the database.

2. Copy the online redo log files to the new location. Operating system files, such as online redo log members, must be copied using the appropriate operating system commands.

The following example uses operating system commands (UNIX) to move the online redo log members to a new location:

$ mv /diska/logs/log1a.rdo   /diskc/logs/log1c.rdo
$ mv /diska/logs/log2a.rdo   /diskc/logs/log2c.rdo

3. Startup the database in mount mode, but do not open it.

SQL> CONNECT / as SYSDBA
SQL> STARTUP MOUNT

4. Rename the online redo log members.

SQL> ALTER DATABASE RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';

5. Open the database for normal operation.

SQL> ALTER DATABASE OPEN;

Online Method

If the database cannot be shut down, it will not possible to rename the files directly. In this case, first add new groups and then drop the non-needed ones.

1. Add as many new groups as the ones to be renamed with the following commands:

SQL> ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
SQL> ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
SQL> ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;

2. Then drop the online redo log groups that are not needed anymore: you must have the ALTER DATABASE system privilege. Before dropping an online redo log group, consider the following restrictions and precautions:

– 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.)
– 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.
Make sure an online redo log group is archived (if archiving is enabled) before dropping it.

To see whether this has happened, use the V$LOG view :

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP#    ARC STATUS
--------- --- ----------------
1         YES ACTIVE
2         NO  CURRENT
3         YES INACTIVE
4         YES UNUSED
5         YES UNUSED
6         YES UNUSED

As group 3 is in INACTIVE mode, you can drop it :

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

3. 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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Database Crashed With ORA-19815, ORA-19809, ORA-16038
  2. How to Enable or Disable Veritas ODM for Oracle database 11g
  3. Oracle Database – Measuring Network Capacity using oratcptest
  4. Common Init.ora Parameters and Unix, Linux Kernel Parameters and Relationship Between Them
  5. Rman Backups Failing with RMAN-20004
  6. Oracle RAC: How to modify private hostname, Private network IP & MTU
  7. Oracle 19c New Feature – SQL_DIAGNOSE_AND_REPAIR (New Automatic Diagnostics and Repair Function)
  8. Starting, Stopping, and Checking the Status of the EM Cloud Control OMS
  9. How to Force ASM to Scan the Multipathed Device First using ASMLIB/oracleasm
  10. How to Resize the Undo Tablespace in Oracle Database

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