change the redo log size in Oracle11g RAC database

Posted on by

#Check the gv$Logfile and gv$log

Step-1 select inst_id,group#,status,type,member from gv$logfile;

SQL> select inst_id,group#,status,type,member from gv$logfile;

INST_ID     GROUP# STATUS  TYPE    MEMBER
--------------------------------------------------------------------------------
2         11         ONLINE    /u01/oradata/orcldb/redo011.log
2         10         ONLINE    /u01/oradata/orcldb/redo10.log
2          5         ONLINE    /u01/oradata/orcldb/redo05.log
2          6         ONLINE    /u01/oradata/orcldb/redo06.log
1         11         ONLINE    /u01/oradata/orcldb/redo011.log
1         10         ONLINE    /u01/oradata/orcldb/redo10.log
1          5         ONLINE    /u01/oradata/orcldb/redo05.log
1          6         ONLINE    /u01/oradata/orcldb/redo06.log

Step-2 select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;

INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS    STATUS
----------------------------------------------------------------------------------------------
2          5          1       5529   10485760          1    INACTIVE
2          6          1       5530   10485760          1    CURRENT
2         10          2       2395   10485760          1    INACTIVE
2         11          2       2396   10485760          1    CURRENT
1          5          1       5529   10485760          1    INACTIVE
1          6          1       5530   10485760          1    CURRENT
1         10          2       2395   10485760          1    INACTIVE
1         11          2       2396   10485760          1    CURRENT

#Add new redo log files

Step-3#Node-1
alter database add logfile thread 2 group 12 '/u01/oradata/orcldb/redo12.log' size 15M;
alter database add logfile thread 2 group 13 '/u01/oradata/orcldb/redo13.log' size 15M;

Step-4#Node-2
alter database add logfile thread 1 group 14 '/u01/oradata/orcldb/redo14.log' size 15M;
alter database add logfile thread 1 group 15 '/u01/oradata/orcldb/redo15.log' size 15M;

Check the status by gv$log

Step-5 select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;

INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS    STATUS
—————————————————————————————————
2          5          1       5529   10485760        1        INACTIVE
2          6          1       5530   10485760        1        CURRENT
2         10          2       2395   10485760      1        INACTIVE
2         11          2       2396   10485760       1        CURRENT
2         12          2          0   15728640          1        UNUSED
2         13          2          0   15728640          1        UNUSED
2         14          1          0   15728640          1        UNUSED
2         15          1          0   15728640          1        UNUSED
1          5          1       5529   10485760      1        INACTIVE
1          6          1       5530   10485760      1        CURRENT
1         10          2       2395   10485760    1        INACTIVE
1         11          2       2396   10485760     1        CURRENT
1         12          2          0   15728640          1        UNUSED
1         13          2          0   15728640          1        UNUSED
1         14          1          0   15728640          1        UNUSED
1         15          1          0   15728640          1        UNUSED

Step-6 Steps before drop the Redo Logs

#Perform the log switch 
alter system switch logfile;

#Check the CURRENT Redo logs
select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log
where status='CURRENT';

#Perform the global checkpoint
alter system checkpoint global;

#Check the INACTIVE Redo logs
select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log
where status='INACTIVE';

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log
2  where status=’INACTIVE’;

INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS    STATUS
————————————————————————————–
1          5          1       5529   10485760          1    INACTIVE
1         10          2       2395   10485760          1    INACTIVE
1         11          2       2396   10485760          1    INACTIVE
2          5          1       5529   10485760          1    INACTIVE
2         10          2       2395   10485760          1    INACTIVE
2         11          2       2396   10485760          1    INACTIVE

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;
1 6 1 5530 10485760 1 CURRENT
1 12 2 2397 15728640 1 CURRENT
1 13 2 0 15728640 1 UNUSED
1 14 1 0 15728640 1 UNUSED
1 15 1 0 15728640 1 UNUSED
2 6 1 5530 10485760 1 CURRENT
2 12 2 2397 15728640 1 CURRENT
2 13 2 0 15728640 1 UNUSED
2 14 1 0 15728640 1 UNUSED
2 15 1 0 15728640 1 UNUSED

#Step-7 Drop the logfiles

    

    alter database drop logfile group 5;
    alter database drop logfile group 10;
    alter database drop logfile group 11;

Step-8 when you drop the specified Group then associated redo log file is deleted if oracle managed file take care of it. otherwise you could delete them manually
#rm -rf redo05.log redo06.log redo10.log redo011.log

Step-9 Check the status

SQL>  select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;
2         12          2       2399   15728640          1    CURRENT
2         13          2       2398   15728640          1    INACTIVE
2         14          1       5531   15728640          1    CURRENT
2         15          1          0   15728640          1    UNUSED
1         12          2       2399   15728640          1    CURRENT
1         13          2       2398   15728640          1    INACTIVE
1         14          1       5531   15728640          1    CURRENT
1         15          1          0   15728640          1    UNUSED

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;
2         12          2       2399   15728640          1    ACTIVE
2         13          2       2400   15728640          1    CURRENT
2         14          1       5531   15728640          1    ACTIVE
2         15          1       5532   15728640          1    CURRENT
1         12          2       2399   15728640          1    ACTIVE
1         13          2       2400   15728640          1    CURRENT
1         14          1       5531   15728640          1    ACTIVE
1         15          1       5532   15728640          1    CURRENT

srvctl stop database -d orcldb

srvctl start database -d orcldb

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;
2         12          2       2399   15728640          1    INACTIVE
2         13          2       2400   15728640          1    CURRENT
2         14          1       5531   15728640          1    INACTIVE
2         15          1       5532   15728640          1    CURRENT
1         12          2       2399   15728640          1    INACTIVE
1         13          2       2400   15728640          1    CURRENT
1         14          1       5531   15728640          1    INACTIVE
1         15          1       5532   15728640          1    CURRENT

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.