Removing Logfile Member from ASM using ASMCMD

This post is just about my play around with logfile members under ASM.

22:58:54 SYS at matrix >select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA1/matrix/onlinelog/group_3.263.732594715      NO
         3         ONLINE  +DATA2/matrix/onlinelog/group_3.259.732594719      NO
         2         ONLINE  +DATA1/matrix/onlinelog/group_2.262.732594707      NO
         2         ONLINE  +DATA2/matrix/onlinelog/group_2.258.732594711      NO
         1         ONLINE  +DATA1/matrix/onlinelog/group_1.261.732594697      NO
         1         ONLINE  +DATA2/matrix/onlinelog/group_1.257.732594701      NO
6 rows selected.
Elapsed: 00:00:00.09
22:58:57 SYS at matrix >select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         60   52428800          2 NO  CURRENT                1190887 03-NOV-10
         2          1         57   52428800          2 YES INACTIVE               1190865 03-NOV-10
         3          1         58   52428800          2 YES INACTIVE               1190871 03-NOV-10
Elapsed: 00:00:00.04
22:59:01 SYS at matrix >alter database add logfile group 4 size 100M;
Database altered.
Elapsed: 00:00:22.01
22:59:36 SYS at matrix >
22:59:47 SYS at matrix >
22:59:47 SYS at matrix >select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         60   52428800          2 NO  CURRENT                1190887 03-NOV-10
         2          1         57   52428800          2 YES INACTIVE               1190865 03-NOV-10
         3          1         58   52428800          2 YES INACTIVE               1190871 03-NOV-10
         4          1          0  104857600          2 YES UNUSED                       0
Elapsed: 00:00:00.06
22:59:49 SYS at matrix >select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA1/matrix/onlinelog/group_3.263.732594715      NO
         3         ONLINE  +DATA2/matrix/onlinelog/group_3.259.732594719      NO
         2         ONLINE  +DATA1/matrix/onlinelog/group_2.262.732594707      NO
         2         ONLINE  +DATA2/matrix/onlinelog/group_2.258.732594711      NO
         1         ONLINE  +DATA1/matrix/onlinelog/group_1.261.732594697      NO
         1         ONLINE  +DATA2/matrix/onlinelog/group_1.257.732594701      NO
         4         ONLINE  +DATA1/matrix/onlinelog/group_4.272.734137155      NO
         4         ONLINE  +DATA2/matrix/onlinelog/group_4.260.734137163      NO
8 rows selected.
Elapsed: 00:00:00.09

Did few log switches to make the newly added group 4 status active.

22:59:52 SYS at matrix >alter system switch logfile;
System altered.
Elapsed: 00:00:00.17
23:00:01 SYS at matrix >alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
23:00:06 SYS at matrix >alter system switch logfile;
System altered.
Elapsed: 00:00:00.03
23:00:07 SYS at matrix >select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         60   52428800          2 YES ACTIVE                 1190887 03-NOV-10
         2          1         62   52428800          2 YES ACTIVE                 1190929 03-NOV-10
         3          1         63   52428800          2 NO  CURRENT                1190931 03-NOV-10
         4          1         61  104857600          2 YES ACTIVE                 1190926 03-NOV-10
Elapsed: 00:00:00.06

Did one more switch to change the status to INACTIVE and then tried to remove one of the logfile member of group 4 directly from the ASM using rm command (ASMCMD utility).Was i able to do it???

23:00:09 SYS at matrix >alter system switch logfile;
System altered.
Elapsed: 00:00:01.68
23:00:27 SYS at matrix >select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         64   52428800          2 NO  CURRENT                1190939 03-NOV-10
         2          1         62   52428800          2 YES INACTIVE               1190929 03-NOV-10
         3          1         63   52428800          2 YES ACTIVE                 1190931 03-NOV-10
         4          1         61  104857600          2 YES INACTIVE               1190926 03-NOV-10
Elapsed: 00:00:00.04
23:00:28 SYS at matrix >select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA1/matrix/onlinelog/group_3.263.732594715      NO
         3         ONLINE  +DATA2/matrix/onlinelog/group_3.259.732594719      NO
         2         ONLINE  +DATA1/matrix/onlinelog/group_2.262.732594707      NO
         2         ONLINE  +DATA2/matrix/onlinelog/group_2.258.732594711      NO
         1         ONLINE  +DATA1/matrix/onlinelog/group_1.261.732594697      NO
         1         ONLINE  +DATA2/matrix/onlinelog/group_1.257.732594701      NO
         4         ONLINE  +DATA1/matrix/onlinelog/group_4.272.734137155      NO
         4         ONLINE  +DATA2/matrix/onlinelog/group_4.260.734137163      NO
8 rows selected.
Elapsed: 00:00:00.07
===========================
ASMCMD [+DATA2/MATRIX/ONLINELOG] > ls -lrt
Type       Redund  Striped  Time             Sys  Name
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_1.257.732594701
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_2.258.732594711
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_3.259.732594719
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_4.260.734137163
ASMCMD [+DATA2/MATRIX/ONLINELOG] >
ASMCMD [+DATA2/MATRIX/ONLINELOG] >
ASMCMD [+DATA2/MATRIX/ONLINELOG] >
ASMCMD [+DATA2/MATRIX/ONLINELOG] >
ASMCMD [+DATA2/MATRIX/ONLINELOG] > rm  group_4.260.734137163
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA2/MATRIX/ONLINELOG/group_4.260.734137163' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

Ok, so i am unable to remove the logfile member from ASM directly.
Can i remove the member while the status is “UNUSED”.If yes, would there be any error when switch happens.

23:06:00 SYS at matrix >alter database add logfile group 5 size 100M;
Database altered.
Elapsed: 00:00:23.01
23:06:32 SYS at matrix >select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         64   52428800          2 NO  CURRENT                1190939 03-NOV-10
         2          1         62   52428800          2 YES INACTIVE               1190929 03-NOV-10
         3          1         63   52428800          2 YES INACTIVE               1190931 03-NOV-10
         4          1         61  104857600          2 YES INACTIVE               1190926 03-NOV-10
         5          1          0  104857600          2 YES UNUSED                       0
Elapsed: 00:00:00.18
23:13:20 SYS at matrix >select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA1/matrix/onlinelog/group_3.263.732594715      NO
         3         ONLINE  +DATA2/matrix/onlinelog/group_3.259.732594719      NO
         2         ONLINE  +DATA1/matrix/onlinelog/group_2.262.732594707      NO
         2         ONLINE  +DATA2/matrix/onlinelog/group_2.258.732594711      NO
         1         ONLINE  +DATA1/matrix/onlinelog/group_1.261.732594697      NO
         1         ONLINE  +DATA2/matrix/onlinelog/group_1.257.732594701      NO
         4         ONLINE  +DATA1/matrix/onlinelog/group_4.272.734137155      NO
         4         ONLINE  +DATA2/matrix/onlinelog/group_4.260.734137163      NO
         5         ONLINE  +DATA1/matrix/onlinelog/group_5.273.734137569      NO
         5         ONLINE  +DATA2/matrix/onlinelog/group_5.262.734137579      NO
10 rows selected.
Elapsed: 00:00:00.14

Created a new redo logfile group 5.The status is “UNUSED”.Using the ASMCMD utility i try to remove the one of the member.Can i?

ASMCMD [+DATA2/MATRIX/ONLINELOG] > ls -lrt
Type       Redund  Striped  Time             Sys  Name
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_1.257.732594701
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_2.258.732594711
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_3.259.732594719
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_4.260.734137163
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_5.262.734137579
ASMCMD [+DATA2/MATRIX/ONLINELOG] >
ASMCMD [+DATA2/MATRIX/ONLINELOG] > rm group_5.262.734137579
ASMCMD [+DATA2/MATRIX/ONLINELOG] > ls -lrt
Type       Redund  Striped  Time             Sys  Name
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_1.257.732594701
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_2.258.732594711
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_3.259.732594719
ONLINELOG  UNPROT  FINE     NOV 03 23:00:00  Y    group_4.260.734137163
ASMCMD [+DATA2/MATRIX/ONLINELOG] >

Yes, i am able to remove it.The ASM alert log would show

Wed Nov 03 23:13:29 2010
SQL> alter diskgroup 'DATA2' drop file '+DATA2/MATRIX/ONLINELOG/group_5.262.734137579'

What happens when i switch and this log group is used for the first time.

23:13:34 SYS at matrix >alter system switch logfile;
System altered.
Elapsed: 00:00:00.20
23:13:52 SYS at matrix >alter system switch logfile;
System altered.
Elapsed: 00:00:00.09
23:13:53 SYS at matrix >select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         64   52428800          2 YES INACTIVE               1190939 03-NOV-10
         2          1         62   52428800          2 YES INACTIVE               1190929 03-NOV-10
         3          1         63   52428800          2 YES INACTIVE               1190931 03-NOV-10
         4          1         66  104857600          2 NO  CURRENT                1191231 03-NOV-10
         5          1         65  104857600          2 YES ACTIVE                 1191229 03-NOV-10
Elapsed: 00:00:00.04
23:13:55 SYS at matrix >select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         64   52428800          2 YES INACTIVE               1190939 03-NOV-10
         2          1         62   52428800          2 YES INACTIVE               1190929 03-NOV-10
         3          1         63   52428800          2 YES INACTIVE               1190931 03-NOV-10
         4          1         66  104857600          2 NO  CURRENT                1191231 03-NOV-10
         5          1         65  104857600          2 YES INACTIVE               1191229 03-NOV-10
Elapsed: 00:00:00.10
23:14:27 SYS at matrix >select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA1/matrix/onlinelog/group_3.263.732594715      NO
         3         ONLINE  +DATA2/matrix/onlinelog/group_3.259.732594719      NO
         2         ONLINE  +DATA1/matrix/onlinelog/group_2.262.732594707      NO
         2         ONLINE  +DATA2/matrix/onlinelog/group_2.258.732594711      NO
         1         ONLINE  +DATA1/matrix/onlinelog/group_1.261.732594697      NO
         1         ONLINE  +DATA2/matrix/onlinelog/group_1.257.732594701      NO
         4         ONLINE  +DATA1/matrix/onlinelog/group_4.272.734137155      NO
         4         ONLINE  +DATA2/matrix/onlinelog/group_4.260.734137163      NO
         5         ONLINE  +DATA1/matrix/onlinelog/group_5.273.734137569      NO
         5 INVALID ONLINE  +DATA2/matrix/onlinelog/group_5.262.734137579      NO
10 rows selected.
Elapsed: 00:00:00.09
23:14:44 SYS at matrix >

The status in V$LOGFILE shows “INVALID”.The status INVALID mean “File is inaccessible” , which is very true in this case.
The database alert log shows –

Wed Nov 03 23:06:09 2010
alter database add logfile group 5 size 100M
Wed Nov 03 23:06:28 2010
Starting control autobackup
Control autobackup written to DISK device
 handle 'D:\DB_BACKUP\RMAN\C-2272919429-20101103-03'
Completed: alter database add logfile group 5 size 100M
Wed Nov 03 23:13:52 2010
Errors in file d:\oracle\product\10.2.0\admin\matrix\bdump\matrix_lgwr_3780.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '+DATA2/matrix/onlinelog/group_5.262.734137579'
ORA-17503: ksfdopn:2 Failed to open file +DATA2/matrix/onlinelog/group_5.262.734137579
ORA-15012: ASM file '+DATA2/matrix/onlinelog/group_5.262.734137579' does not exist
Wed Nov 03 23:13:52 2010
Errors in file d:\oracle\product\10.2.0\admin\matrix\bdump\matrix_lgwr_3780.trc:
ORA-00321: log 5 of thread 1, cannot update log file header
ORA-00312: online log 5 thread 1: '+DATA2/matrix/onlinelog/group_5.262.734137579'
Wed Nov 03 23:13:52 2010
Errors in file d:\oracle\product\10.2.0\admin\matrix\bdump\matrix_lgwr_3780.trc:
ORA-00313: open failed for members of log group 5 of thread 1
Wed Nov 03 23:13:52 2010
Thread 1 advanced to log sequence 65 (LGWR switch)
 Current log# 5 seq# 65 mem# 0: +DATA1/matrix/onlinelog/group_5.273.734137569
Wed Nov 03 23:13:53 2010
Thread 1 advanced to log sequence 66 (LGWR switch)
 Current log# 4 seq# 66 mem# 0: +DATA1/matrix/onlinelog/group_4.272.734137155
 Current log# 4 seq# 66 mem# 1: +DATA2/matrix/onlinelog/group_4.260.734137163
Wed Nov 03 23:13:53 2010
Errors in file d:\oracle\product\10.2.0\admin\matrix\bdump\matrix_arc0_316.trc:
ORA-00313: open failed for members of log group 5 of thread 1
Wed Nov 03 23:13:53 2010
Errors in file d:\oracle\product\10.2.0\admin\matrix\bdump\matrix_arc0_316.trc:
ORA-00313: open failed for members of log group 5 of thread 1

One thought on “Removing Logfile Member from ASM using ASMCMD”

Ajinkya says:
November 10, 2010 at 11:38 am
Thanks for sharing this Anand!! I have a small query on the same topic however, its related to standby. I have created a standby on ASM, I took my prod backup and restored the same on my standby server, took a fresh standby controlfile and replaced it at standby, however now I am getting the following error in my standby alert log:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘+ASMFLASH/configdb/onlinelog/group_2.258.656445109’
ORA-17503: ksfdopn:2 Failed to open file +ASMFLASH/configdb/onlinelog/group_2.258.656445109
ORA-15173: entry ‘group_2.258.656445109’ does not exist in directory ‘onlinelog’
ORA-00312: online log 2 thread 1: ‘+ASMDB/configdb/onlinelog/group_2.262.656445109’
ORA-17503: ksfdopn:2 Failed to open file +ASMDB/configdb/onlinelog/group_2.262.656445109
ORA-15173: entry ‘group_2.262.656445109’ does not exist in directory ‘onlinelog’
Clearing online redo logfile 2 complete
Now, what I am more concerned about is whether this would give me an error on trying to activate the database? Ideally I was thinking to rename the logfiles, so which would be the best way of going forward with it. Should I either do “alter database rename file” or “catalog the unknown files” (I know its possible for datafiles, but does it work the same way for online logs?)
Awaiting a pointer from your side.
Thanks,
Ajinkya

Anand's Data Stories

Learn. Share. Repeat.

Removing Logfile Member from ASM using ASMCMD

Related

One thought on “Removing Logfile Member from ASM using ASMCMD”

Leave a Reply Cancel reply

Share this:

Related

One thought on “Removing Logfile Member from ASM using ASMCMD”

Leave a Reply Cancel reply