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”
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