Move REDO logs to a SSD Disk in a RAC/ASM env

Overview

  • SSD disk may have some outliers but overall performance still much better as using disks
  • Exadata is using Smart Flash Log feature transparently
  • This feature uses Flash as a parallel write cache to disk controller cache
  • Whichever write completes first wins (disk or flash)
  • Smart Flash Log is 3x faster by generating lower outliers
Check current redo log configuration
SQL> select l.thread#, group#, l.status,  member from v$logfile inner join v$log l 
          using (group#) order by  l.thread#, group#;
   THREAD#     GROUP# STATUS           MEMBER
---------- ---------- ---------------- ---------------------------------------------
     1              1 CURRENT          +SSD/grac4/onlinelog/group_1.258.831669597
     1              2 INACTIVE         +SSD/grac4/onlinelog/group_2.259.831669619
     2              3 CURRENT          +DATA/grac4/onlinelog/group_3.265.826111821
     2              4 INACTIVE         +DATA/grac4/onlinelog/group_4.266.826111827
     3              5 CURRENT          +DATA/grac4/onlinelog/group_5.269.826220095
     3              6 INACTIVE         +DATA/grac4/onlinelog/group_6.270.826220101

Connect to instance 2 and verify the current thread# number:
SQL> select instance_number, host_name,instance_name,thread# from v$instance;
INSTANCE_NUMBER HOST_NAME               INSTANCE_NAME       THREAD#
--------------- ------------------------------ ---------------- ----------
          2 grac42.example.com           grac42             2

Move thread# 2 from  USB3-Disk based redo logs ( datagroup +DATA )  to SSD disk-based datagroup +SSD
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 98 ('+SSD') SIZE 50M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 99 ('+SSD') SIZE 50M;

Switch logfile until our newly added logfile group# 98 becomes current: 
SQL>  alter system switch logfile;
System altered.
SQL>  select l.thread#, group#, l.status,  member from v$logfile inner join v$log l using (group#)    
      where  l.thread# = 2  order by  l.thread#, group#
   THREAD#     GROUP# STATUS           MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
     2        3 INACTIVE              +DATA/grac4/onlinelog/group_3.265.826111821
     2        4 INACTIVE              +DATA/grac4/onlinelog/group_4.266.826111827
     2       98 CURRENT               +SSD/grac4/onlinelog/group_98.256.831827421
     2       99 ACTIVE                +SSD/grac4/onlinelog/group_99.257.831826577

To avoid potential ORA-1624 checkpoint the database 
SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

Now drop the old USB3-disk  redologs and verify the current setup
SQL>  alter database drop logfile group 3;
SQL>  alter database drop logfile group 4;
Database altered.
SQL> select l.thread#, group#, l.status,  member from v$logfile inner join v$log l 
     using (group#)    where  l.thread# = 2  order by  l.thread#, group#;
   THREAD#     GROUP# STATUS           MEMBER
---------- ---------- ---------------- ---------------------------------------------
     2       98 CURRENT                +SSD/grac4/onlinelog/group_98.256.831827421
     2       99 INACTIVE               +SSD/grac4/onlinelog/group_99.257.831826577

Leave a Reply

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