Information to gather when diagnosing ASM space issues [ID 351117.1]

Information to gather when diagnosing ASM space issues [ID 351117.1][@more@] Information to gather when diagnosing ASM space issues [ID 351117.1] 修改时间 26-SEP-2008 类型 TROUBLESHOOTING 状态 PUBLISHED

In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7
Information in this document applies to any platform.
ASM 10.X.X.X

Purpose

This is a guide to provide all the information needed to troubleshoot Automatic Storage Management (ASM) space issues, which include:

ASM level errors ORA-15041 ORA-15047 RDBMS level errors when storage is on ASM Inconsistencies between what is perceived as the available space Inconsistencies between V$ASM_DISKGROUP and X$ views The next section has a reference to the code of the scripts used to collect the information. Those scripts can be downloaded from the following links:

asmdebug.sql -- To be executed on the ASM instance

rdbmsdebug.sql -- To be executed on the RDBMS instance

The next section lists the information to gather. Be sure to gather the information from the correct instance. Also, keep in mind that if normal redundancy is used you need to multiply the file size as shown from the database times two, and for high redundancy times three.

Last Review Date

December 28, 2005

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Code to be run on the ASM instance. Use file asmdebug.sql.zip

set newpage none
set linesize 100
spool /tmp/asmdebug.out
--
-- Get a timestamp
select rpad('>', 10, '>'), to_char(sysdate, 'MON DD HH24:MM:SS') from dual;
--
-- Diskgroup information
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb from
v$asm_diskgroup;
--
-- Get the # of Allocation Units per DG
set head off
select 'Number of AUs per diskgroup' from dual;
set head on
select count(number_kfdat) AU_count, group_kfdat DG# from x$kfdat
group by group_kfdat;
--
-- Get the # of Allocation Units per DiskGroup and Disk
set head off
select 'Number of AUs per Diskgroup,Disk' from dual;
col "group#,disk#" for a30
set head on
select count(*)AU_count, GROUP_KFDAT||','||number_kfdat "group#,disk#" from x$kfdat group by GROUP_KFDAT,number_kfdat;
--
-- Get the # of allocated (V) and free (F) Allocation Units
set head off
select 'Number of allocated (V) and free (F) Allocation Units' from dual;
col "VF" for a2
set head on
select GROUP_KFDAT "group#", number_kfdat "disk#", v_kfdat "VF", count(*)
from x$kfdat
group by GROUP_KFDAT, number_kfdat, v_kfdat;
--
-- Get the # of Allocation Units per ASM file
set head off
select 'Number of AUs per ASM file ordered by AU count for metadata only'
from dual;
set head on
select count(XNUM_KFFXP) AU_count, NUMBER_KFFXP file#, GROUP_KFFXP DG# from x$kffxp where NUMBER_KFFXP < 256
group by NUMBER_KFFXP, GROUP_KFFXP
order by count(XNUM_KFFXP) ;
--
-- Get the # of Allocation Units per ASM file by file alias. Change the
-- system_created Y|N depending if you want the short or long ASM name
set head off
select 'Number of AUs per ASM file ordered by AU count. This is for non
metadata' from dual;
col name format a60
set head on
select GROUP_KFFXP, NUMBER_KFFXP, name, count(*)
from x$kffxp, v$asm_alias
where GROUP_KFFXP=GROUP_NUMBER and NUMBER_KFFXP=FILE_NUMBER and
system_created='Y'
group by GROUP_KFFXP, NUMBER_KFFXP, name
order by GROUP_KFFXP, NUMBER_KFFXP;
--
-- Get partner information. This is really only useful if redundancy is other than
-- external.
set head off
select 'The following shows the disk to partner relationship. This is really only
useful if using normal or high redundancy.' from dual;
set head on
select grp DG#, disk, NUMBER_KFDPARTNER partner, PARITY_KFDPARTNER parity, ACTIVE_KFDPARTNER active
from x$kfdpartner;
--
-- Another look at file utilization.
set head off
set linesize 132
select 'bytes is the sum of AUs with data in them * 1024^2
space is the sum of all AUs allocated for this file * 1024^2'
from dual;
set head on
col Name format a60
select f.group_number, f.file_number, bytes, space, space/(1024*1024) "InMB", a.name "Name"
from v$asm_file f, v$asm_alias a
where f.group_number=a.group_number and f.file_number=a.file_number
and system_created='Y'
order by f.group_number, f.file_number;
--
-- Get robust disk information
set linesize 400
col failgroup format a20
col label format a20
col name format a40
col path format a40
set head off
select 'Robust disk information' from dual;
set head on
select GROUP_NUMBER, DISK_NUMBER, INCARNATION, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, LIBRARY, TOTAL_MB, FREE_MB,
NAME, FAILGROUP, LABEL, PATH, CREATE_DATE, MOUNT_DATE, READS,
WRITES, READ_ERRS, WRITE_ERRS, READ_TIME, WRITE_TIME, BYTES_READ, BYTES_WRITTEN
from v$asm_disk;
--
spool off

Code to be executed on the database instances using this ASM instance. Use file rdbmsdebug.sql.zip

set newpage none
spool /tmp/rdbmsdebug.out
--
-- Get a timestamp
select rpad('>', 10, '>'), to_char(sysdate, 'MON DD HH24:MM:SS') from dual;
--
-- Get datafile information as the database sees it
set head off
select 'V$DATAFILE information' from dual;
set head on
set linesize 132
col name format a60
select file#, name, block_size, blocks, bytes, bytes/(1024*1024) "InMB",
status from v$datafile;
--
-- Get controlfile information as the database sees it
set head off
select 'V$CONTROLFILE information' from dual;
set head on
select * from v$controlfile;
--
-- Get archivelog information as the database sees it
set head off
select 'GV$ARCHIVED_LOG information' from dual;
set head on
select name, thread#, sequence#, blocks*block_size "size", status
status from gv$archived_log
order by thread#,sequence#;
--
-- Get redolog information as the database sees it
set head off
select 'v$log and v$logfile information' from dual;
set head on
col member format a60
select a.group#, member, thread#, sequence#, bytes, a.status
from v$log a, v$logfile b where a.group# = b.group#
order by thread#;
--
-- Get tempfiles information as the database sees it
set head off
select 'GV$TEMPFILE information' from dual;
set head on
col name format a60
select INST_ID,TS#,FILE#, RFILE#,NAME, CREATION_CHANGE# , CREATION_TIME, STATUS, BYTES, BLOCKS, CREATE_BYTES, BLOCK_SIZE
from gv$tempfile order by inst_id, file#;
spool off

Other items to collect:
System error logs Alert logs from all database and ASM instances All recent trace files from all databases involved and all of the ASM instances All ".trc" files from the ASM instances

附件

asmdebug.sql.zip (1.36 KB)rdbmsdebug.sql.zip (713 B)

相关的

产品 Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 关键字 ASM; METADATA; V$ASM_DISKGROUP; V$ASM_DISK; V$DATAFILE; V$ASM_FILE; V$ASM_ALIAS; V$ASM_DISKGROUP错误 ORA-15047; ORA-15041

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94384/viewspace-1028853/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94384/viewspace-1028853/