Steps to add and drop redo log in database using ASM
1. Check redo log file destination
sql> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +ORADATA001
db_create_online_log_dest_1 string +ORADATA001
db_create_online_log_dest_2 string +ORAFLASH001
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
2. set redo log file destination, when necessary
sql> ALTER SYSTEM SET db_create_online_log_dest_1='+ORADATA001' SCOPE=BOTH;
sql> ALTER SYSTEM SET db_create_online_log_dest_2='+ORAFLASH001' SCOPE=BOTH;
3. check current members
sql> col member format a50
sql> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +ORADATA001/tmct/onlinelog/group_1.357.732462337 NO
1 ONLINE +ORAFLASH001/tmct/onlinelog/group_1.282.732462339 NO
2 ONLINE +ORADATA001/tmct/onlinelog/group_2.358.732462339 NO
2 ONLINE +ORAFLASH001/tmct/onlinelog/group_2.283.732462341 NO
3 ONLINE +ORADATA001/tmct/onlinelog/group_3.359.732462341 NO
3 ONLINE +ORAFLASH001/tmct/onlinelog/group_3.284.732462341 NO
6 rows selected.
4. add new redo log file group
sql> alter database add logfile group 4;
Database altered.
5. check after adding new member
sql> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +ORADATA001/tmct/onlinelog/group_1.357.732462337 NO
1 ONLINE +ORAFLASH001/tmct/onlinelog/group_1.282.732462339 NO
2 ONLINE +ORADATA001/tmct/onlinelog/group_2.358.732462339 NO
2 ONLINE +ORAFLASH001/tmct/onlinelog/group_2.283.732462341 NO
3 ONLINE +ORADATA001/tmct/onlinelog/group_3.359.732462341 NO
3 ONLINE +ORAFLASH001/tmct/onlinelog/group_3.284.732462341 NO
4 ONLINE +ORADATA001/tmct/onlinelog/group_4.368.733426529 NO
4 ONLINE +ORAFLASH001/tmct/onlinelog/group_4.280.733426551 NO
8 rows selected.
Steps to drop redo log file group from database using ASM
- check current status of redo log file.
sql> select GROUP#, THREAD#, MEMBERS, STATUS from v$log;
GROUP# THREAD# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 1 2 ACTIVE
2 1 2 CURRENT
3 1 2 INACTIVE
4 1 2 UNUSED
IMPORTANT: Redo log group of status INACTIVE or UNUSED may be dropped. Dropping ACTIVE or CUREENT may corrupt database.
2. drop redo log group
sql> alter database drop logfile group 4;
Database altered.
In RAC environment, THREAD# is the instance # of the redo log file
To add redo log file, login to instance, in which you want to add redo log file and then add redo log file using
alter database add logfile group
sql> select GROUP#, THREAD#, MEMBERS, STATUS from v$log;
GROUP# THREAD# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 1 2 UNUSED
2 1 2 UNUSED
3 1 2 UNUSED
4 1 2 CURRENT
5 2 2 ACTIVE
6 2 2 CURRENT
7 2 2 UNUSED
8 2 2 INACTIVE
8 rows selected.
Hope this help. Regards Rupam
