ASM丢魂记（一）
1、现象：
昨天，一个朋友很着急给我打电话，说客户有个RAC数据库突然的宕掉了，请我帮忙，通过远程连接去查看用户的数据库。由于我公司是电信的线路，无法通过远程登录。然后，我让朋友给我弄出了ALERT日志来。
2、分析原因：
通过ALERT日志，经过初步判断是磁盘空间不足的问题。
Tue Mar 3 14:42:52 2009
Errors in file /u01/app/oracle/admin/orcl/udump/orcl2_ora_15166.trc:
ORA-19504: 无法创建文件"+DATA/orcl/2_30388_640712653.dbf"
ORA-17502: ksfdcre: 4 未能创建文件 +DATA/orcl/2_30388_640712653.dbf
ORA-15041: diskgroup space exhausted
Tue Mar 3 14:42:52 2009
ARCH: Error 19504 Creating archive log file to '+DATA/orcl/2_30388_640712653.dbf'
ARCH: Failed to archive thread 2 sequence 30388 (19504)
Tue Mar 3 14:42:52 2009
Errors in file /u01/app/oracle/admin/orcl/udump/orcl2_ora_15166.trc:
ORA-16038: 日志 4 序列号 30388 无法归档
ORA-19504: 无法创建文件""
ORA-00312: 联机日志 4 线程 2: '+DATA/orcl/onlinelog/group_4.293.640717141'
3、初步问题解决：
关闭数据库归档模式。随后重新启动数据库，结果数据库能够正常启动。
4、遗留问题：
用户是LIUNX下做的RAC,使用的是ASM磁盘组。目前有几个疑问：
1、ASM磁盘组的磁盘耗尽，出现数据库宕机。
2.也许是磁盘空间还有，但是某个数据块有问题，造成归档日志无法写入。
3.ASM本身有BUG，磁盘空间还有，但是没有BALANCE。
5、后续解决：
分析是否磁盘空间有剩余：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> select total_mb,free_mb from v$asm_diskgroup;
TOTAL_MB FREE_MB
---------- ----------
1048570 23
SQL> select total_mb,free_mb from v$asm_disk;
TOTAL_MB FREE_MB
---------- ----------
1048570 0
从这里看出，磁盘空间已经没有。那莫排除后2种情况。对我来说，我最希望的是第一种情况。如果是后两种，麻烦多多。
6、那莫是什么占用空间了呢?
list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1364 Incr 0 27.50M SBT_TAPE 00:00:08 08-NOV-08
BP Key: 1364 Status: AVAILABLE Compressed: NO Tag: TAG20081108T021409
Handle: aojv59nu_1_1 Media:
SPFILE Included: Modification time: 08-NOV-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1366 Incr 0 27.50M SBT_TAPE 00:00:07 09-NOV-08
BP Key: 1366 Status: AVAILABLE Compressed: NO Tag: TAG20081109T021431
Handle: aqjv7u4t_1_1 Media:
SPFILE Included: Modification time: 08-NOV-08
。。。。。
------- ---- -- ---------- ----------- ------------ ---------------
1456 Incr 0 27.50M SBT_TAPE 00:00:06 22-DEC-08
BP Key: 1456 Status: AVAILABLE Compressed: NO Tag: TAG20081222T021449
Handle: drk2rv96_1_1 Media:
SPFILE Included: Modification time: 22-DEC-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1458 Incr 0 27.50M SBT_TAPE 00:00:06 23-DEC-08
BP Key: 1458 Status: AVAILABLE Compressed: NO Tag: TAG20081223T021531
Handle: dtk2ujp1_1_1 Media:
SPFILE Included: Modification time: 23-DEC-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1459 Incr 0 56.61G SBT_TAPE 00:29:59 24-DEC-08
BP Key: 1459 Status: AVAILABLE Compressed: NO Tag: TAG20081224T021519
Handle: duk316do_1_1 Media:
List of Datafiles in backup set 1459
RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 instance=orcl2 devtype=DISK
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16745_640712653.dbf recid=43748 stamp=674876551
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16746_640712653.dbf recid=43749 stamp=674876587
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16747_640712653.dbf recid=43750 stamp=674876612
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16748_640712653.dbf recid=43752 stamp=674876629
。。。。。。。。。。
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30380_640712653.dbf recid=52475 stamp=680487056
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30381_640712653.dbf recid=52479 stamp=680487245
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30382_640712653.dbf recid=52483 stamp=680487335
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30383_640712653.dbf recid=52487 stamp=680487518
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30384_640712653.dbf recid=52492 stamp=680487611
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30385_640712653.dbf recid=52495 stamp=680487686
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30386_640712653.dbf recid=52500 stamp=680487707
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30387_640712653.dbf recid=52503 stamp=680488729
Crosschecked 8756 objects
30387-16745=1.4W个归档日志。
由于归档日志太多，造成控制文件被覆盖，只有显示8756个归档日志，实际我认为有1.4W个。
查看
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0
CKPT PROGRESS 8180 35 0 0
0 0
REDO THREAD 256 32 2 0
0 2
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
REDO LOG 72 192 4 0
0 4
DATAFILE 428 1024 22 0
0 8552
FILENAME 524 4674 27 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
TABLESPACE 68 1024 23 0
0 35
TEMPORARY FILENAME 56 1024 1 0
0 1
RMAN CONFIGURATION 1108 50 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
LOG HISTORY 56 8700 8700 4700
4699 52549
OFFLINE RANGE 200 1063 0 0
0 0
ARCHIVED LOG 584 8756 8756 4346
4345 52503
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
BACKUP SET 40 1227 1227 340
339 1566
BACKUP PIECE 736 1000 1000 567
566 1566
BACKUP DATAFILE 116 1128 1128 124
123 4635
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
BACKUP REDOLOG 76 11540 11540 9104
9103 26413
DATAFILE COPY 660 1016 1 1
1 1
BACKUP CORRUPTION 44 1115 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
COPY CORRUPTION 40 1227 0 0
0 0
DELETED OBJECT 20 10862 10862 2541
2540 29695
PROXY COPY 852 1017 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
BACKUP SPFILE 36 454 245 1
245 245
DATABASE INCARNATION 56 292 1 1
1 1
FLASHBACK LOG 84 2048 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
RECOVERY DESTINATION 180 1 0 0
0 0
INSTANCE SPACE RESERVATION 28 1055 2 0
0 0
REMOVABLE RECOVERY FILES 32 1000 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
RMAN STATUS 116 141 141 5
4 1837
THREAD INSTANCE NAME MAPPING 80 32 32 0
0 0
MTTR 100 32 2 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATAFILE HISTORY 568 57 0 0
0 0
STANDBY DATABASE MATRIX 400 10 10 0
0 0
GUARANTEED RESTORE POINT 212 2048 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
RESTORE POINT 212 2083 0 0
0 0
看来这个命令也是只显示控制文件归档日志的数量。
解决问题：有两种途径：
1.通过ORACLE内部的SQL语句删除日志。
2.通过ASMCMD界面来删除，就能解决。
问题貌似在这里就可以结束。。。。。。。。。。。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8334342/viewspace-561444/ ，如需转载，请注明出处，否则将追究法律责任。
