Relocate Oracle redo logs

Posted on Posted in Data & Business Intelligence

Redo logs are the most critical structure for recovery operations.
From oracle’s documentation :

“Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments”

This is a short list of things you need to take into account while choosing a location for redo logs:

  • Type of disks – the underlying disks that will store the redo logs should be fast. Really fast. The optimal situation is when the disks can sustain the amount of I/O that needs to be written to the redo logs. If not, you might see ‘log file sync’ wait event, which will slow down the database transactions.
  • Size of disks – the log writer writes to a single redo log file until it’s full or a manual log switch was requested. In a case when a transaction generates a lot of redo log entries before a commit, consider increasing the redo log size. If the size of the redo log isn’t configured properly, it may cause unnecessary log switches – which will slow down the database.
  • Redo logs and other files – you should not mix the redo logs with data files or any other files. Remember: any slowdown in writing to the redo logs will impact the performance, so you wouldn’t want to share the disks on which the redo logs are stored with anything else.

In our case, on our production Oracle RAC (11g) database, our redo logs files weren’t optimal: our redo logs were in the same location as the data files and during peak hours we saw ‘log file sync’ wait events. That’s why we decided to relocate the redo logs.

This is the plan we used (no down time):

Add new disk path to /dev/mapper on all nodes. Make sure that all the nodes in the cluster can see in their /dev/mapper the new disk
Create a new ASM disk:

Run scandisks on the rest of the nodes:

Output:

Make sure that the new disk is visible to ASM instances on all nodes:

Output:

Output:

Create new temp diskgroup to test the ASM disk:

Redirect db_create_online_log_dest_1 to the new Disk Group:

Create the redo log group:

Drop the old redo-log groups: Before every statement make sure the redo log groups are at INACTIVE status at v$log.

If it’s not, run before the drop the following statements:

Drop redo log groups commands:

Make sure all redo logs are being used:
Make sure all redo-log groups are not “UNUSED” in v$log. If there are some UNUSED redo-log groups then perform switch log file commands.

Done!

Leave a Reply

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