Dear Readers,
In this article, we will see the following Important ASM Commands.
Tab Name : asm_free
|
1 2 |
set linesize 300 col name for a30 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ; NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB PCT_FREE ------------------------------ -------------------- ----------- ------ ---------- ---------- ---------- REDO01 4194304 CONNECTED HIGH 3051520 2657044 87.0728031 DATA01 4194304 CONNECTED NORMAL 21368320 18917476 88.5304788 RECO01 4194304 CONNECTED NORMAL 9154560 8677040 94.7838017 select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB/1024 TOTAL_GB ,FREE_MB/1024 FREE_GB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ; SQL> NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_GB FREE_GB PCT_FREE ------------------------------ -------------------- ----------- ------ ---------- ---------- ---------- REDO01 4194304 CONNECTED HIGH 2980 2594.76953 87.0728031 DATA01 4194304 CONNECTED NORMAL 20867.5 18474.0977 88.5304788 RECO01 4194304 CONNECTED NORMAL 8940 8473.67188 94.7838017 |
Tab Name : ASMCMD>find file
find +DATA01 undo*
Tab Name : WhatInstances
|
1 2 3 4 |
set linesize 300 col instance_name for a15 col SOFTWARE_VERSION for a15 col COMPATIBLE_VERSION for a15 |
|
1 2 3 4 5 6 7 |
SELECT * FROM V$ASM_CLIENT; GROUP_NUMBER INSTANCE_NAME DB_NAME CLUSTER_NAME STATUS SOFTWARE_VERSIO COMPATIBLE_VERS CON_ID ------------ --------------- -------- ------------------------------- ------------ --------------- --------------- ---------- 3 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0 2 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0 1 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0 |
Tab Name : Rebalance_status
|
1 2 |
set linesize 300 select * from gv$asm_operation; |
Tab Name : ASM_Candidate
|
1 2 3 4 5 6 7 |
BREAK ON report ON disk_group_name SKIP 1 set pagesize 50 set linesize 300 col DISK_GROUP_NAME for a20 col DISK_FILE_PATH for a50 col DISK_FILE_FAIL_GROUP for a40 col DISK_FILE_NAME for a25 |
|
1 2 3 4 5 6 7 8 9 |
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name , b.path disk_file_path , b.name disk_file_name , b.failgroup disk_file_fail_group FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) ORDER BY a.name; |
Tab Name : Check_former
|
1 |
select path from v$asm_disk where header_status='FORMER' order by path; |
Tab Name : ASM_DISKGROUPS_Info
|
1 2 3 4 5 6 |
set pagesize 100 set linesize 300 col DISK_GROUP_NAME for a20 col DISK_FILE_PATH for a50 col DISK_FILE_FAIL_GROUP for a40 col DISK_FILE_NAME for a25 |
|
1 2 3 4 5 6 7 8 9 |
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name , b.path disk_file_path , b.name disk_file_name , b.failgroup disk_file_fail_group FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) ORDER BY a.name; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : ASM Disk Groups | PROMPT | Instance : ¤t_instance | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN group_name FORMAT a25 HEAD 'Disk Group|Name' COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size' COLUMN block_size FORMAT 99,999 HEAD 'Block|Size' COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size' COLUMN state FORMAT a11 HEAD 'State' COLUMN type FORMAT a6 HEAD 'Type' COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)' COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)' COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used' BREAK ON report ON disk_group_name SKIP 1 COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report SELECT name group_name , sector_size sector_size , block_size block_size , allocation_unit_size allocation_unit_size , state state , type type , total_mb total_mb , (total_mb - free_mb) used_mb , ROUND((1- (free_mb / total_mb))*100, 2) pct_used FROM v$asm_diskgroup WHERE total_mb != 0 ORDER BY name / Disk Group Sector Block Allocation Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used ------------------------- ------- ------- ------------ ----------- ------ --------------- -------------- --------- DATA01 512 4,096 4,194,304 CONNECTED NORMAL 21,368,320 2,450,844 11.47 RECO01 512 4,096 4,194,304 CONNECTED NORMAL 9,154,560 477,520 5.22 REDO01 512 4,096 4,194,304 CONNECTED HIGH 3,051,520 394,476 12.93 --------------- -------------- Grand Total: 33,574,400 3,322,840 |
Tab Name : Check_Grp_number_for_Inst
|
1 2 3 4 5 6 7 8 9 |
select distinct GROUP_NUMBER,db_name from gV$ASM_CLIENT where INSTANCE_NAME like '%&inst_name%' order by group_number; Enter value for inst_name: ASM1 GROUP_NUMBER DB_NAME ------------ -------- 1 ORCL123 2 ORCL123 3 ORCL123 |
Tab Name : Name Group#
|
1 2 3 4 5 6 7 |
select name,group_number from v$asm_diskgroup; NAME GROUP_NUMBER ------------------------------ ------------ DATA01 1 RECO01 2 REDO01 3 |
Tab Name : Name_Path_for_GrpNum
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
set linesize 300 col path for a75 select name,path from v$asm_disk where group_number=&GRP_Num; Enter value for grp_num: 1 NAME PATH ------------------------------ --------------------------------------------------------------------------- SSD_KT_S00_2701241896Z10 ABC:SSD_KT_S00_2701241896Z10 SSD_KT_S04_2701241948Z10 ABC:SSD_KT_S04_2701241948Z10 SSD_KT_S02_2701241868Z10 ABC:SSD_KT_S02_2701241868Z10 SSD_KT_S01_2701251772Z10 ABC:SSD_KT_S01_2701251772Z10 SSD_KT_S03_2701253192Z10 ABC:SSD_KT_S03_2701253192Z10 SSD_KT_S06_2349020000Z10 ABC:SSD_KT_S06_2349020000Z10 SSD_KT_S05_2349020004Z10 ABC:SSD_KT_S05_2349020004Z10 SSD_KT_S09_2349021544Z10 ABC:SSD_KT_S09_2349021544Z10 SSD_KT_S08_2349022264Z10 ABC:SSD_KT_S08_2349022264Z10 SSD_KT_S07_2701257960Z10 ABC:SSD_KT_S07_2701257960Z10 |
Tab Name : DG_Details_For_Given_Path
|
1 2 3 4 5 6 7 8 |
set linesize 300 col path for a60 select a.group_number,b.name,a.path,a.header_status from v$asm_disk a,v$asm_diskgroup b where a.path in ('&PATH') and a.group_number=b.group_number; Enter value for path: ABC:SSD_KT_S00_2701241896Z101 GROUP_NUMBER NAME PATH HEADER_STATU ------------ ------------------------------ ------------------------------------------------------------ ------------ 1 DATA ABC:SSD_KT_S00_2701241896Z10 MEMBER |
Tab Name : ASM_Compatibility_Version
|
1 2 3 4 5 6 7 8 |
set linesize 300 select group_number, name,compatibility, database_compatibility from v$asm_diskgroup; GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY ------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ 1 DATA01 18.0.0.0.0 12.1.0.2.0 2 RECO01 18.0.0.0.0 12.1.0.2.0 3 REDO01 18.0.0.0.0 12.1.0.2.0 |
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Follow Me
Linkedin : Venkat Vinod Kumar Siram
Note: Please test scripts in Non Prod before trying in Production.
Loading...
