• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to add or drop redo log groups in Oracle RAC

by admin

redo in RAC environment

In RAC, each instance has a redo thread; when you start the instance, it mounts one and only one thread. Therefore if you want to add logs or increase the size, you must do it for each thread. The goal is normally to have the same number and size of logs for each thread.

First, determine what log members each thread has. Here is a sample of a script to show what log members you currently have and their sizes:

-- Script begins here --
--
-- Please note, this is a sample script
-- provided for educational purposes only
-- and is not certified by Oracle Support for any purpose.

spool log.lst
set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/

spool off

-- End of script --

Sample output:

GROUP# THREAD# MEMBER                             ARCHIVED STATUS    MB
------ ------- ---------------------------------- -------- --------- ---
     3       2 /u04/oradata/redologs/redo03a.log  NO       INACTIVE   10
     3       2 /u04/oradata/redologs/redo03b.log  NO       INACTIVE   10
     4       2 /u04/oradata/redologs/redo04a.log  NO       CURRENT    10
     4       2 /u04/oradata/redologs/redo04b.log  NO       CURRENT    10
     5       1 /u04/oradata/redologs/redo05a.log  NO       CURRENT   100
     5       1 /u04/oradata/redologs/redo05b.log  NO       CURRENT   100
     6       1 /u04/oradata/redologs/redo06a.log  NO       INACTIVE  100
     6       1 /u04/oradata/redologs/redo06b.log  NO       INACTIVE  100
     7       1 /u04/oradata/redologs/redo07a.log  NO       INACTIVE  100
     7       1 /u04/oradata/redologs/redo07b.log  NO       INACTIVE  100
     8       1 /u04/oradata/redologs/redo08a.log  NO       INACTIVE  100
     8       1 /u04/oradata/redologs/redo08b.log  NO       INACTIVE  100

Steps to add redo log groups to Oracle RAC setup

1. Check existing redo log groups & redo log file size.

SQL> column REDOLOG_FILE_NAME format a50;
SQL> set lines 1000
SQL> SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
 a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

 GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NE              SIZE_MB
------- ---------- ---------- --- ---------------- --------------------------- ----------
      1          1          3 YES ACTIVE           +REDO/boston/redo01.log      100
      2          1          4 NO  CURRENT          +REDO/boston/redo02.log      100
      3          2          1 YES INACTIVE         +REDO/boston/redo03.log      100
      4          2          2 NO  CURRENT          +REDO/boston/redo04.log      100

2. As we checked redo log file location & size, we will proceed for redo log group addtions.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('+REDO/boston/redo05.log') SIZE 100m,
GROUP 6 ('+REDO/boston/redo06.log') SIZE 100m;

Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 7 ('+REDO/boston/redo07.log') SIZE 100m,
GROUP 8 ('+REDO/boston/redo08.log') SIZE 100m;

Database altered.

We can use below commands also:

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 '+REDO' SIZE 100m;

3. Check for newly added redo log groups:

SQL> column REDOLOG_FILE_NAME format a50;
SQL> set lines 1000
SQL> SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
 a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NAME                                     SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
         1          1          3 YES INACTIVE         +REDO/boston/redo01.log                                 100
         2          1          4 NO  CURRENT          +REDO/boston/redo02.log                                 100
         3          2          1 YES INACTIVE         +REDO/boston/redo03.log                                 100
         4          2          2 NO  CURRENT          +REDO/boston/redo04.log                                 100
         5          1          0 YES UNUSED           +REDO/boston/redo05.log                                 100
         6          1          0 YES UNUSED           +REDO/boston/redo06.log                                 100
         7          2          0 YES UNUSED           +REDO/boston/redo07.log                                 100
         8          2          0 YES UNUSED           +REDO/boston/redo08.log                                 100

Steps to drop redo log groups

1. While dropping redo log group & we need to check the status of redo log groups. redo log group status should be INACTIVE or UNUSED.

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

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

2. To drop inactive/unused redo log group:

SQL> Alter database drop logfile group 1;
Database altered.

3. Check again to verify the redo log groups:

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

Filed Under: oracle

Some more articles you might also be interested in …

  1. Starting, Stopping, and Checking the Status of the EM Cloud Control OMS
  2. How to Delete ASM Disk on Multipath Device in CentOS/RHEL
  3. How to Change Timezone Settings on Exadata
  4. ORA-14652: reference partitioning foreign key is not supported
  5. ORA-01031 When Compiling A Synonym
  6. How to Create and Manage Snapshot Standby Database
  7. How to rename a Pluggable Database, along with the respective directories, in Oracle 12c
  8. Interview Questions : Oracle Flex ASM 12c
  9. How To Disable Advanced Analytics in Oracle Database 12c?
  10. How to Recover DROPPED PDB After Flashback of CDB

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright