How to Change Redo Log Location in Oracle

  • by

To improve overall performance, sometimes, we may consider to move redo logs to the location with faster disks, like Solid-State Drive (SSD). 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.

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

  1. Change Redo Location of a Single-Instance Database
  2. Change Redo Location of a RAC Database

A. Single-Instance Database

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

  1. Moving Redo Log Files Offline of a Single-instance Database
  2. Replacing Redo Log Files Online of a Single-instance Database

1. 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 redo log files.

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.

2. 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) for us to do some operations on them.

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.

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.

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

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

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 *