ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ASM丢魂记(一)

ASM丢魂记(一)

原创 Linux操作系统 作者:anycall2010 时间:2009-03-05 02:02:30 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/8334342/viewspace-561444/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-24

  • 博文量
    182
  • 访问量
    561712