Skip to content
Home » Oracle » How to Move Redo Log Location in Oracle

How to Move Redo Log Location in Oracle

Move LogFile

Performance Considerations

To improve overall performance, sometimes, we may consider to move redo logs to the location with faster disks, like Solid-State Drive (SSD) or NVMe.

Once you decide to move redo log files to another location, you have to plan when and how to move them. Sometimes, it could be a critical job to do.

ALTER DATABASE MOVE LOGFILE

Since 12c, we used to move datafiles to another place by ALTER DATABASE MOVE DATAFILE statement which totally simplifies the procedure to move datafiles from one to another.

As for logfiles, I'm sorry, ALTER DATABASE MOVE LOGFILE syntax is unsupported in Oracle. If you did it, you get the error ORA-00905: missing keyword.

Renaming or Replacing?

We used 2 techniques to move redo logs to another place:

  • Renaming Redo Log File
  • It requires the database to be offline in order to move the actual files to another path, then rename them in the control file.

  • Replacing Redo Log File
  • We can do it while the database is online, the concept is to add a new redo member in the new location, then drop the old ones.

In this post, I will introduce some ways to move redo log files in single-instance databases and RAC databases.

  1. Single-Instance Database
  2. RAC Database

If you'd like to move them as well as increase the size of redo logfiles for some reason, you need another procedure to do it.

Single-Instance Database

There're two approaches that can change the location of redo log files of a single-instance database.

  1. Renaming Redo Log File
  2. Replacing Redo Log File

Moving Redo Logs of a Single-instance Database

It's actually a moving process of redo log files.

Check current redo logs.

SQL> column member format a50;
SQL> select group#, member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/ORCLCDB/redo01.log
         2 /u01/app/oracle/oradata/ORCLCDB/redo02.log
         3 /u01/app/oracle/oradata/ORCLCDB/redo03.log

Shutdown the database.

SQL> shutdown immediate;

Copy redo log files.

[oracle@test ~]$ cp -pi /u01/app/oracle/oradata/ORCLCDB/redo0[1-3].log /oradata/ORCLCDB/
[oracle@test ~]$ ll /oradata/ORCLCDB/redo0[1-3].log
-rw-r----- 1 oracle oinstall 1073742336 Jan 12 21:17 /oradata/ORCLCDB/redo01.log
-rw-r----- 1 oracle oinstall 1073742336 Jan 12 21:19 /oradata/ORCLCDB/redo02.log
-rw-r----- 1 oracle oinstall 1073742336 Jan 12 21:17 /oradata/ORCLCDB/redo03.log

Startup the database to MOUNT.

SQL> startup mount;

Rename Logfile

I'm sorry, there's no ALTER DATABASE RENAME LOGFILE specific for logfiles, because ALTER DATABASE RENAME FILE takes care of all types of files including datafiles, tempfiles and logfiles.

SQL> alter database rename file '/u01/app/oracle/oradata/ORCLCDB/redo01.log','/u01/app/oracle/oradata/ORCLCDB/redo02.log','/u01/app/oracle/oradata/ORCLCDB/redo03.log' to '/oradata/ORCLCDB/redo01.log','/oradata/ORCLCDB/redo02.log','/oradata/ORCLCDB/redo03.log';

Database altered.

Open the database.

SQL> alter database open;

Database altered.

Check current redo log file location again.

SQL> select group#, member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /oradata/ORCLCDB/redo01.log
         2 /oradata/ORCLCDB/redo02.log
         3 /oradata/ORCLCDB/redo03.log

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   READ WRITE

As we can see, all log files are in the new location.

Replacing Redo Log Location of a Single-instance Database

Our tactic is to add a new member for each group to the new location, then drop all log files in the old location.

Check current redo logs.

SQL> column member format a50;
SQL> select group#, member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/ORA19C1/redo01.log
         2 /u01/app/oracle/oradata/ORA19C1/redo02.log
         3 /u01/app/oracle/oradata/ORA19C1/redo03.log

Add a member for each group to the new location.

We add a member for each group with the same file name, but different location.

SQL> alter database add logfile member '/oradata/ORCLCDB/redo01.log' to group 1;

Database altered.

SQL> alter database add logfile member '/oradata/ORCLCDB/redo02.log' to group 2;

Database altered.

SQL> alter database add logfile member '/oradata/ORCLCDB/redo03.log' to group 3;

Database altered.

SQL> select group#, member from v$logfile order by 1,2;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /oradata/ORCLCDB/redo01.log
         1 /u01/app/oracle/oradata/ORA19C1/redo01.log
         2 /oradata/ORCLCDB/redo02.log
         2 /u01/app/oracle/oradata/ORA19C1/redo02.log
         3 /oradata/ORCLCDB/redo03.log
         3 /u01/app/oracle/oradata/ORA19C1/redo03.log

6 rows selected.

Check statuses of groups.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

As we can see, group 1 and 3 are available (INACTIVE) to do some operations on them. So we do them first.

Drop old redo logs.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile member '/u01/app/oracle/oradata/ORA19C1/redo01.log';

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database drop logfile member '/u01/app/oracle/oradata/ORA19C1/redo03.log';

Database altered.

Make a logfile switch.

We are going to drop the rest redo log which is CURRENT now, so we need to switch redo log group to make it available.

SQL> alter system archive log current;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 UNUSED

Making a log switch could be somewhat different between ALTER SYSTEM ARCHIVE LOG CURRENT and ALTER SYSTEM SWITCH LOGFILE.

Please note that, in a RAC database, you may need to use ALTER SYSTEM CHECKPOINT GLOBAL to make all instances checkpoint at the same time.

Drop rest old redo logs.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database drop logfile member '/u01/app/oracle/oradata/ORA19C1/redo02.log';

Database altered.

Check current redo logs again.

SQL> select group#, member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /oradata/ORCLCDB/redo01.log
         2 /oradata/ORCLCDB/redo02.log
         3 /oradata/ORCLCDB/redo03.log

As you can see, we change the location of log files by replacing old log files with new ones.

RAC Database

Since direct copying redo log files in ASM disk groups has some side effects for a RAC database, so we focus only on replacing old redo logs with new ones.

  1. Replacing Redo Log Files of a RAC Database

Replacing Redo Log Location of a RAC Database

Check current redo logs.

SQL> show parameter db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TESTCDB

SQL> column member format a50;
SQL> select group#, member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 +DATA/ORCLCDB/redo01.log
         2 +DATA/ORCLCDB/redo02.log
         3 +DATA/ORCLCDB/redo03.log
         4 +DATA/ORCLCDB/redo04.log
         5 +DATA/ORCLCDB/redo05.log
         6 +DATA/ORCLCDB/redo06.log

6 rows selected.

As you can see, DB_UNIQIE_NAME is TESTCDB, but redo logs are in +DATA/ORCLCDB. Our goal is to make all redo logs go to +DATA/TESTCDB.

Furthermore, there're 6 redo log groups in the RAC database, 3 groups per thread. Which is, group 1, 3, 5 are for node 1, group 2, 4, 6 are for node 2.

Add a member for each group to the new location.

SQL> alter database add logfile member '+DATA/TESTCDB/redo01.log' to group 1;

Database altered.

SQL> alter database add logfile member '+DATA/TESTCDB/redo02.log' to group 2;

Database altered.

SQL> alter database add logfile member '+DATA/TESTCDB/redo03.log' to group 3;

Database altered.

SQL> alter database add logfile member '+DATA/TESTCDB/redo04.log' to group 4;

Database altered.

SQL> alter database add logfile member '+DATA/TESTCDB/redo05.log' to group 5;

Database altered.

SQL> alter database add logfile member '+DATA/TESTCDB/redo06.log' to group 6;

Database altered.

SQL> set pagesize 100;
SQL> select group#, member from v$logfile order by 1,2;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 +DATA/ORCLCDB/redo01.log
         1 +DATA/TESTCDB/redo01.log
         2 +DATA/ORCLCDB/redo02.log
         2 +DATA/TESTCDB/redo02.log
         3 +DATA/ORCLCDB/redo03.log
         3 +DATA/TESTCDB/redo03.log
         4 +DATA/ORCLCDB/redo04.log
         4 +DATA/TESTCDB/redo04.log
         5 +DATA/ORCLCDB/redo05.log
         5 +DATA/TESTCDB/redo05.log
         6 +DATA/ORCLCDB/redo06.log
         6 +DATA/TESTCDB/redo06.log

12 rows selected.

Now check the statues of all groups before dropping old redo logs.

SQL> select thread#, group#, status from v$log order by 1,2;

   THREAD#     GROUP# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         1          3 INACTIVE
         1          5 CURRENT
         2          2 CURRENT
         2          4 INACTIVE
         2          6 INACTIVE

6 rows selected.

As we can see, group 1, 3, 4 and 6 are available (INACTIVE) for us to do some operations on them.

Drop old redo logs.

In this case, we drop old redo logs in group 1, 3, 4 and 6 below.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile member '+DATA/ORCLCDB/redo01.log';

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database drop logfile member '+DATA/ORCLCDB/redo03.log';

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database drop logfile member '+DATA/ORCLCDB/redo04.log';

Database altered.

SQL> alter database clear logfile group 6;

Database altered.

SQL> alter database drop logfile member '+DATA/ORCLCDB/redo06.log';

Database altered.

Make a logfile switch.

We are going to drop the rest redo logs which are CURRENT now, so we need to switch redo log group to make them available.

SQL> alter system archive log current;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select thread#, group#, status from v$log order by 1,2;

   THREAD#     GROUP# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         1          3 UNUSED
         1          5 INACTIVE
         2          2 INACTIVE
         2          4 CURRENT
         2          6 UNUSED

6 rows selected.

Making a log switch could be somewhat different between ALTER SYSTEM ARCHIVE LOG CURRENT and ALTER SYSTEM SWITCH LOGFILE.

Please note that, we use ALTER SYSTEM CHECKPOINT after switching redo to force all changes to the database write into data files to prevent ORA-01624.

Drop rest old redo logs.

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database drop logfile member '+DATA/ORCLCDB/redo05.log';

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database drop logfile member '+DATA/ORCLCDB/redo02.log';

Database altered.

Check current redo logs again.

SQL> select group#, member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 +DATA/TESTCDB/redo01.log
         2 +DATA/TESTCDB/redo02.log
         3 +DATA/TESTCDB/redo03.log
         4 +DATA/TESTCDB/redo04.log
         5 +DATA/TESTCDB/redo05.log
         6 +DATA/TESTCDB/redo06.log

6 rows selected.

We furthermore check the physical files in ASM.

[grid@primary01 ~]$ asmcmd ls -l +DATA/TESTCDB/redo*
Type       Redund  Striped  Time             Sys  Name
ONLINELOG  UNPROT  COARSE   JAN 13 13:00:00  N    redo01.log => +DATA/TESTCDB/ONLINELOG/group_1.308.1061731047
ONLINELOG  UNPROT  COARSE   JAN 13 13:00:00  N    redo02.log => +DATA/TESTCDB/ONLINELOG/group_2.309.1061731173
ONLINELOG  UNPROT  COARSE   JAN 13 13:00:00  N    redo03.log => +DATA/TESTCDB/ONLINELOG/group_3.310.1061731217
ONLINELOG  UNPROT  COARSE   JAN 13 13:00:00  N    redo04.log => +DATA/TESTCDB/ONLINELOG/group_4.311.1061731291
ONLINELOG  UNPROT  COARSE   JAN 13 13:00:00  N    redo05.log => +DATA/TESTCDB/ONLINELOG/group_5.312.1061731371
ONLINELOG  UNPROT  COARSE   JAN 13 13:00:00  N    redo06.log => +DATA/TESTCDB/ONLINELOG/group_6.313.1061731443

ASM knows how to put them in the correct way.

Leave a Reply

Your email address will not be published. Required fields are marked *