Monday, April 16, 2012

Resize Redo log for RAC

In RAC, you have seperate log groups for each instance (called threads). You can see which group belongs to which thread by querying v$log. When you add new loggroups, you specify which thread it should belong to. The syntax is

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('firstlocation','secondlocation') SIZE 1280M;

SQL> select instance_number, instance_name, thread# from gv$instance;
Show Instance with thread ID

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

Check number groups in each of thread.

On RAC, you have to add redo log Group each of node (each of thread )

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

From your environment ,I think you have 2 nodes = 2 thread
If these're thread 1, 2
So, add redo group should:

Example:
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ( '+DATA','+DATA') SIZE 300M,
                                    GROUP 12 ( '+DATA','+DATA') SIZE 300M reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 13 ( '+DATA','+DATA') SIZE 300M,
                                    GROUP 14 ( '+DATA','+DATA') SIZE 300M reuse;

You should check each thread has >= 2 groups and that group had "INACTIVE" status before drop:
SQL> select group#, thread#, members ,status from v$log;

now run "alter system switch logfile;ALTER SYSTEM CHECKPOINT GLOBAL;" on all instances:
alter database drop logfile group 1 ,group 2,group 3,group 4,group 5,group 6;


remove the actuall files (GROUP 1-6) from the ASM:
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM2
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo01.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo02.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo05.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo06.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo03.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo04.log';
exit

verify files were removed
##

asmcmd
ASMCMD> cd REDOA
ASMCMD> find . *
+REDOA/DBNAME/
+REDOA/DBNAME/CONTROLFILE/
+REDOA/DBNAME/CONTROLFILE/Current.256.666981619
+REDOA/DBNAME/ONLINELOG/
+REDOA/DBNAME/ONLINELOG/group_10.271.667595443
+REDOA/DBNAME/ONLINELOG/group_20.272.667595443
+REDOA/DBNAME/ONLINELOG/group_30.265.667595411
+REDOA/DBNAME/ONLINELOG/group_40.266.667595411
+REDOA/DBNAME/ONLINELOG/group_50.267.667595411
+REDOA/DBNAME/ONLINELOG/group_60.268.667595411
+REDOA/DBNAME/ONLINELOG/group_70.269.667595413
+REDOA/DBNAME/ONLINELOG/group_80.270.667595413
+REDOA/DBNAME/control01.ctl
+REDOA/DBNAME/redo01-tmp.log
+REDOA/DBNAME/redo02-tmp.log
+REDOA/DBNAME/redo03-tmp.log
+REDOA/DBNAME/redo04-tmp.log
+REDOA/DBNAME/redo05-tmp.log
+REDOA/DBNAME/redo06-tmp.log
+REDOA/DBNAME/redo07-tmp.log
+REDOA/DBNAME/redo08-tmp.log
ASMCMD> exit

now re-create it with "dual" members and bigger size:
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ( '+DATA','+DATA') SIZE 300M,
                                    GROUP 2 ( '+DATA','+DATA') SIZE 300M,
                                    GROUP 3 ( '+DATA','+DATA') SIZE 300M reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ( '+DATA','+DATA') SIZE 300M,
                                    GROUP 5 ( '+DATA','+DATA') SIZE 300M,
                                    GROUP 6 ( '+DATA','+DATA') SIZE 300M reuse;
alter database drop logfile group 11, group 12, group 13, group 14;

No comments:

Post a Comment