oracle-asm诊断运维脚本.txt

forever

739

7页

34次

2024-10-14

10墨值下载

一、asm 运维脚本

--------------------------------------------------------------------------------

--------------------------------------------------------------------

1.check ASM disk space

1) Determine which (if any) disks contain no free space (ie are below the

threshold)

select group_kfdat "group #",

number_kfdat "disk #",

count(*) "# AU's"

from x$kfdat a

where v_kfdat = 'V'

and not exists (select *

from x$kfdat b

where a.group_kfdat = b.group_kfdat

and a.number_kfdat = b.number_kfdat

and b.v_kfdat = 'F')

group by GROUP_KFDAT, number_kfdat;

If no rows are returned ... the following query can also be used

select disk_number "Disk #", free_mb

from v$asm_disk

where group_number = *** disk group number ***

order by 2;

If rows are returned from the first query ... or FREE_MB is less than 100mb in

the second ... then there is probably insufficient disk space to allow a

rebalance to occur ... Note the Disk #'s for later

2) Determine which files have allocation units on the disk(s) that are on

exhausted disks

select name, file_number

from v$asm_alias

where group_number in (select group_kffxp

from x$kffxp

where group_kffxp=*** disk group number ***

and disk_kffxp in (*** disk list from #1 above ***)

and au_kffxp != 4294967294

and number_kffxp >= 256)

and file_number in (select number_kffxp

from x$kffxp

where group_kffxp=*** disk group number ***

and disk_kffxp in (*** disk list from #1 above ***)

and au_kffxp != 4294967294

and number_kffxp >= 256)

and system_created='Y';

3) Free up space so that the rebalance can occur

Using the file list from #2 above ... we will need to either drop or move

tablespace(s)/datafile(s) such that all disks that are exhausted have at least

100mb free ...

NOTE ... the AU count above ... should relate to 1mb AU size ... so if a single

file ... with at least 100 au's can be dropped or moved ... this

should be sufficient to free up enough space to allow the rebalance to occur

Droppable tablespaces may be things like:

* temporary tablespaces

* index tablespaces (assuming you know how to rebuild the indexes)

If none of the tablespaces are droppable then the tablespace(s)/datafile(s) will

need to be

* moved to another diskgroup (at least temporarily) ...

* dropped using RMAN (with the database shutdown) and will be restored later

Note 330103.1 How to Move Asm Database Files From one Diskgroup To Another ?

4) Check to see if there is sufficient FREE_MB on the problem disks

select disk_number "Disk #", free_mb

from v$asm_disk

where disk_group = *** disk group number ***

and disk_number in (*** disk list from #1 above ***)

order by 2;

--------------------------------------------------------------------------------

--------------------------------------------------------------------

2.Check Diskgroup Balance

select disk_kffxp, sum(size_kffxp) from x$kffxp where group_kffxp=AAA and

number_kffxp=BBB and lxn_kffxp=0 group by disk_kffxp order by 2;

--------------------------------------------------------------------------------

--------------------------------------------------------------------

二、asm 诊断脚本

--------------------------------------------

spool asm_diag1.txt

set pagesize 1000

set lines 500

col "Group Name" form a25

col "Disk Name" form a30

col "State" form a15

col "Type" form a7

col "Free GB" form 9,999

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sysdate "Date and Time" from dual;

select * from v$asm_diskgroup order by 1;

select * from v$asm_disk order by 1, 2, 3;

select * from gv$asm_operation order by 1;

select * from v$version where banner like '%Database%' order by 1;

select * from gv$asm_client order by 1;

prompt

prompt ASM Disk Groups

prompt ===============

select group_number "Group"

, name "Group Name"

, state "State"

, type "Type"

, total_mb/1024 "Total GB"

, free_mb/1024 "Free GB"

from v$asm_diskgroup

prompt

prompt ASM Disks

prompt ==============

col "Group" form 999

col "Disk" form 999

col "Header" form a9

col "Mode" form a8

col "Redundancy" form a10

col "Failure Group" form a10

col "Path" form a19

select group_number "Group"

, disk_number "Disk"

, header_status "Header"

, mode_status "Mode"

, state "State"

, redundancy "Redundancy"

, total_mb "Total MB"

, free_mb "Free MB"

, name "Disk Name"

, failgroup "Failure Group"

, path "Path"

from v$asm_disk

order by group_number

, disk_number

prompt

prompt Instances currently accessing these diskgroups

prompt ==============================================

select c.group_number "Group"

, g.name "Group Name"

, c.instance_name "Instance"

from v$asm_client c

, v$asm_diskgroup g

where g.group_number=c.group_number

prompt

prompt Report the Percentage of Imbalance in all Mounted Diskgroups

prompt ==============================================

select dfail, count(dfail) from

(

select disk, count(failgroup) as dfail

from x$kfdpartner, v$asm_disk where

number_kfdpartner=disk_number and grp=group_number

group by disk, failgroup

)

group by dfail;

select g.name as "GROUP", d.name as "DISK", d.failgroup, fcnt, pcnt,

decode(pcnt - fcnt, 0, 'MUST', 'SHOULD') as action from

(select gnum, DISK1, failgroup, count(failgroup) as fcnt from

(select gnum, DISK1

from

(

select d.group_number as gnum, disk as disk1,

count(distinct failgroup) as dfail

from x$kfdpartner, v$asm_disk_stat d where

number_kfdpartner=disk_number and grp=d.group_number

and active_kfdpartner=1

of 7

10墨值下载

oracle oracle asm

相关文档

评论