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