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

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s