ASM sql's from rdbms instance <<<<<<<<<<<<<<<<<<
SQL> show parameter instance_type
NAME Type VALUE
------------------------------------ ------ ------------------------------
instance_type string RDBMS
COLUMN GROUP_NUMBER FORMAT 999999 HEADING GROUP_|NUMBER
COLUMN INSTANCE_NAME FORMAT A7 HEADING INSTANC|E_NAME
COLUMN DB_NAME FORMAT A7
COLUMN STATUS FORMAT A9
COLUMN SOFTWARE_VERSION FORMAT A16
COLUMN COMPATIBLE_VERSION FORMAT A18
set pagesize 200
SET VERIFY OFF
SET LINESIZE 80
-- SPOOL txt
SELECT
GROUP_NUMBER
,INSTANCE_NAME
,DB_NAME
,STATUS
,SOFTWARE_VERSION
,COMPATIBLE_VERSION
FROM sys.V_$ASM_CLIENT ;
GROUP_ INSTANC
NUMBER E_NAME DB_NAME STATUS SOFTWARE_VERSION COMPATIBLE_VERSION
------- ------- ------- --------- ---------------- ------------------
1 +ASM orcl CONNECTED 11.2.0.1.0 11.2.0.0.0
2 +ASM orcl CONNECTED 11.2.0.1.0 11.2.0.0.0
SET VERIFY ON
SET LINESIZE 1500
COLUMN GROUP_NUMBER FORMAT 999999 HEADING GROUP_|NUMBER
COLUMN NAME FORMAT A12
COLUMN SECTOR_SIZE FORMAT 999999 HEADING SECTOR|_SIZE
COLUMN BLOCK_SIZE FORMAT 99999 HEADING BLOCK|_SIZE
COLUMN ALLOCATION_UNIT_SIZE FORMAT 9999999999 HEADING ALLOCATION|_UNIT_SIZE
COLUMN STATE FORMAT A9
COLUMN TYPE FORMAT A6
COLUMN TOTAL_MB FORMAT 99999999
COLUMN FREE_MB FORMAT 9999999
COLUMN REQUIRED_MIRROR_FREE_MB FORMAT 999999999999 HEADING REQUIRED_MIR|ROR_FREE_MB
COLUMN USABLE_FILE_MB FORMAT 9999999 HEADING USABLE_|FILE_MB
COLUMN OFFLINE_DISKS FORMAT 9999999 HEADING OFFLINE|_DISKS
COLUMN UNBALANCED FORMAT A5 HEADING UNBAL|ANCED
COLUMN COMPATIBILITY FORMAT A13
COLUMN DATABASE_COMPATIBILITY FORMAT A11 HEADING DATABASE_CO|MPATIBILITY
SET VERIFY OFF
SET LINESIZE 158
-- SPOOL .txt
SELECT
GROUP_NUMBER
,NAME
,SECTOR_SIZE
,BLOCK_SIZE
,ALLOCATION_UNIT_SIZE
,STATE
,TYPE
,TOTAL_MB
,FREE_MB
,REQUIRED_MIRROR_FREE_MB
,USABLE_FILE_MB
,OFFLINE_DISKS
-- ,UNBALANCED
,COMPATIBILITY
,DATABASE_COMPATIBILITY
FROM SYS.V_$ASM_DISKGROUP ;
GROUP_ SECTOR BLOCK ALLOCATION REQUIRED_MIR USABLE_ OFFLINE DATABASE_CO
NUMBER NAME _SIZE _SIZE _UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB ROR_FREE_MB FILE_MB _DISKS COMPATIBILITY MPATIBILITY
------- ------------ ------- ------ ----------- --------- ------ --------- -------- ------------- -------- -------- ------------- -----------
1 DATA 512 4096 1048576 CONNECTED NORMAL 9216 5657 896 2380 0 11.2.0.0.0 10.1.0.0.0
2 FRA 512 4096 1048576 CONNECTED EXTERN 9216 8998 0 8998 0 11.2.0.0.0 10.1.0.0.0
COLUMN GROUP_NUMBER FORMAT 999999999 HEADING GROUP_|NUMBER
COLUMN FILE_NUMBER FORMAT 999999999 HEADING FILE_N|UMBER
COLUMN COMPOUND_INDEX FORMAT 9999999999 HEADING COMPOUN|D_INDEX
COLUMN INCARNATION FORMAT 999999999 HEADING INCARN|ATION
COLUMN BLOCK_SIZE FORMAT 999999999 HEADING BLOCK|_SIZE
COLUMN BLOCKS FORMAT 999999999 HEADING BLO|CKS
COLUMN BYTES FORMAT 999999999 HEADING BYT|ES
COLUMN SPACE FORMAT 999999999 HEADING SPA|CE
COLUMN TYPE FORMAT A2 HEADING TY|PE
COLUMN REDUNDANCY FORMAT A5 HEADING REDUN|DANCY
COLUMN STRIPED FORMAT A4 HEADING STRI|PED
COLUMN CREATION_DATE FORMAT A7 HEADING CREATIO|N_DATE
COLUMN MODIFICATION_DATE FORMAT A9 HEADING MODIFICAT|ION_DATE
COLUMN REDUNDANCY_LOWERED FORMAT A9 HEADING REDUNDANC|Y_LOWERED
SET VERIFY OFF
SET LINESIZE 200
set pagesize 200
-- SPOOL .txt
SELECT
GROUP_NUMBER
,FILE_NUMBER
,COMPOUND_INDEX
,INCARNATION
,BLOCK_SIZE
,BLOCKS
,BYTES
,SPACE
,TYPE
,REDUNDANCY
,STRIPED
,CREATION_DATE
,MODIFICATION_DATE
,REDUNDANCY_LOWERED
FROM SYS.V_$ASM_FILE ;
-- WHERE ROWNUM<=100;
-- SPOOL OFF
-- CLEAR COLUMNS
SET VERIFY ON
SET LINESIZE 1500
-- UNDEFINE YOUR_VAR
GROUP_ FILE_N COMPOUN INCARN BLOCK BLO BYT SPA TY REDUN STRI CREATIO MODIFICAT REDUNDANC
NUMBER UMBER D_INDEX ATION _SIZE CKS ES CE PE DANCY PED N_DATE ION_DATE Y_LOWERED
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- -- ----- ---- ------- --------- ---------
1 253 16777469 731105699 512 3 1536 2097152 AS MIRRO COAR 30-SEP- 30-SEP-10 U
MP R SE 10
AR
AM
ET
ER
FI
LE
1 256 16777472 731226373 8192 88321 723525632 ########## DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 257 16777473 731226431 8192 70401 576724992 ########## DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 258 16777474 731226451 8192 12161 99622912 204472320 DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 259 16777475 731226455 8192 641 5251072 12582912 DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 260 16777476 731227229 16384 595 9748480 50331648 CO HIGH FINE 01-OCT- 15-MAR-12 U
NT 10
RO
LF
IL
E
1 261 16777477 731227231 16384 595 9748480 50331648 CO HIGH FINE 01-OCT- 15-MAR-12 U
NT 10
RO
LF
IL
E
1 262 16777478 731227291 512 102401 52429312 110100480 ON MIRRO COAR 01-OCT- 15-MAR-12 U
LI R SE 10
NE
LO
G
1 263 16777479 731227315 512 102401 52429312 110100480 ON MIRRO COAR 01-OCT- 15-MAR-12 U
LI R SE 10
NE
LO
G
1 264 16777480 731227335 512 102401 52429312 110100480 ON MIRRO COAR 01-OCT- 15-MAR-12 U
LI R SE 10
NE
LO
G
1 265 16777481 731227415 8192 3713 30416896 62914560 TE MIRRO COAR 01-OCT- 15-MAR-12 U
MP R SE 10
FI
LE
1 266 16777482 731227433 8192 12801 104865792 214958080 DA MIRRO COAR 01-OCT- 15-MAR-12 U
TA R SE 10
FI
LE
1 267 16777483 731228339 512 5 2560 2097152 PA MIRRO COAR 01-OCT- 15-MAR-12 U
RA R SE 10
ME
TE
RF
IL
E
2 256 33554688 741266959 512 102401 52429312 53477376 ON UNPRO COAR 24-JAN- 15-MAR-12 U
LI T SE 11
NE
LO
G
2 257 33554689 741266861 512 102401 52429312 53477376 ON UNPRO COAR 24-JAN- 15-MAR-12 U
LI T SE 11
NE
LO
G
2 258 33554690 741267209 512 102401 52429312 53477376 ON UNPRO COAR 24-JAN- 15-MAR-12 U
LI T SE 11
NE
LO
G
COLUMN GROUP_NUMBER FORMAT 999999 HEADING GROUP_|NUMBER
COLUMN ENTRY_NUMBER FORMAT 999999 HEADING ENTRY_|NUMBER
COLUMN REDUNDANCY FORMAT A10
COLUMN STRIPE FORMAT A6
COLUMN SYSTEM FORMAT A3 HEADING SYS|TEM
COLUMN NAME FORMAT A20
SET VERIFY OFF
SET LINESIZE 70
set pagesize 200
SELECT
GROUP_NUMBER
,ENTRY_NUMBER
,REDUNDANCY
,STRIPE
,SYSTEM
,NAME
FROM SYS.V_$ASM_TEMPLATE ;
GROUP_ ENTRY_ REDUN SYS
NUMBER NUMBER DANCY STRIPE TEM Disk Group
------- ------- ---------- ------ --- --------------------
1 60 MIRROR COARSE Y PARAMETERFILE
1 61 MIRROR COARSE Y ASMPARAMETERFILE
1 62 MIRROR COARSE Y ASMPARAMETERBAKFILE
1 63 MIRROR COARSE Y DUMPSET
1 64 HIGH FINE Y CONTROLFILE
1 65 MIRROR COARSE Y FLASHFILE
1 66 MIRROR COARSE Y ARCHIVELOG
1 67 MIRROR COARSE Y ONLINELOG
1 68 MIRROR COARSE Y DATAFILE
1 69 MIRROR COARSE Y TEMPFILE
1 170 MIRROR COARSE Y BACKUPSET
1 171 MIRROR COARSE Y AUTOBACKUP
1 172 MIRROR COARSE Y XTRANSPORT
1 173 MIRROR COARSE Y CHANGETRACKING
1 174 MIRROR COARSE Y FLASHBACK
1 175 MIRROR COARSE Y DATAGUARDCONFIG
1 176 MIRROR COARSE Y OCRFILE
1 177 MIRROR COARSE Y OCRBACKUP
1 178 HIGH COARSE Y ASM_STALE
2 60 UNPROT COARSE Y PARAMETERFILE
2 61 UNPROT COARSE Y ASMPARAMETERFILE
2 62 UNPROT COARSE Y ASMPARAMETERBAKFILE
2 63 UNPROT COARSE Y DUMPSET
2 64 UNPROT FINE Y CONTROLFILE
2 65 UNPROT COARSE Y FLASHFILE
2 66 UNPROT COARSE Y ARCHIVELOG
2 67 UNPROT COARSE Y ONLINELOG
2 68 UNPROT COARSE Y DATAFILE
2 69 UNPROT COARSE Y TEMPFILE
2 170 UNPROT COARSE Y BACKUPSET
2 171 UNPROT COARSE Y AUTOBACKUP
2 172 UNPROT COARSE Y XTRANSPORT
2 173 UNPROT COARSE Y CHANGETRACKING
2 174 UNPROT COARSE Y FLASHBACK
2 175 UNPROT COARSE Y DATAGUARDCONFIG
2 176 UNPROT COARSE Y OCRFILE
2 177 UNPROT COARSE Y OCRBACKUP
2 178 UNPROT COARSE Y ASM_STALE
38 rows selected.
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 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
ORDER BY name
Disk Group Sector Block Allocation
Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
DATA 512 4,096 1,048,576 CONNECTED NORMAL 9,216 3,559 38.62
FRA 512 4,096 1,048,576 CONNECTED EXTERN 9,216 218 2.37
--------------- --------------
Grand Total: 18,432 3,777
COL name FORMAT A15 HEADING 'Diskgroup|Name' WRAP
COL database_compatibility FORMAT A10 HEADING 'Diskgroup|Version'
COL instance_name FORMAT A20 HEADING 'Serviced Database|Client' WRAP
COL db_name FORMAT A8 HEADING 'Database|Name'
COL status FORMAT A12 HEADING 'Status'
COL software_version FORMAT A10 HEADING 'Database|Version'
Set linesize 120
SELECT
g.name
,g.database_compatibility
,c.instance_name
,c.db_name
,c.status
,c.software_version
FROM v_$asm_client c, v_$asm_diskgroup_stat g
where c.group_number = g.group_number;
Diskgroup Diskgroup Serviced Database Database Database
Name Version Client Name Status Version
--------------- ---------- -------------------- -------- ------------ ----------
DATA 10.1.0.0.0 +ASM orcl CONNECTED 11.2.0.1.0
FRA 10.1.0.0.0 +ASM orcl CONNECTED 11.2.0.1.0
TTITLE 'ASM disk statistics (From v$asm_disk_stat)'
col path format a50
select PATH, READS, WRITES, READ_TIME, WRITE_TIME,
READ_TIME/decode(READS,0,1,READS) "avgrdtime ",
WRITE_TIME/decode(WRITES,0,1,WRITES) "avgwrtime"
from v_$asm_disk_stat;
PATH READS WRITES READ_TIME WRITE_TIME avgrdtime avgwrtime
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
ORCL:ASMDISK01 2153 886 495.332 32.228 .230065954 .036374718
ORCL:ASMDISK02 2834 2081 845.988 77.66 .298513761 .037318597
ORCL:ASMDISK03 2995 2243 564.308 115.436 .188416694 .051465002
ORCL:ASMDISK04 2226 1615 459.576 97.564 .206458221 .060411146
ORCL:ASMDISK05 62 571 23.84 2.812 .384516129 .004924694
ORCL:ASMDISK06 22 28 22.688 1.032 1.03127273 .036857143
ORCL:ASMDISK07 44 173 19.392 4.784 .440727273 .027653179
ORCL:ASMDISK08 63 217 8.404 15.944 .133396825 .073474654
8 rows selected.
set pagesize 200
break on inst_id skip 1
column inst_id format 9999999 heading "Instance ID" justify left
column name format a15 heading "Disk Group" justify left
column total_mb format 999,999,999 heading "Total (MB)" justify right
column free_mb format 999,999,999 heading "Free (MB)" justify right
column pct_free format 999.99 heading "% Free" justify right
prompt ======================================================================
select inst_id, name, total_mb, free_mb, round((free_mb/total_mb)*100,2) pct_free from gv$asm_diskgroup
where total_mb != 0
order by inst_id, name;
SQL>
Instance ID Disk Group Total (MB) Free (MB) % Free
----------- --------------- ------------ ------------ -------
1 DATA 9,216 5,657 61.38
FRA 9,216 8,998 97.63
ASM disk size
PROMPT
PROMPT
PROMPT
PROMPT =======================================
PROMPT
SELECT DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb"
FROM DUAL,
(SELECT sum(a.bytes) TOTAL
FROM DBA_DATA_FILES a
WHERE (a.TABLESPACE_NAME IN ('SYSTEM','SYSAUX')
OR a.TABLESPACE_NAME LIKE '%UNDO%')) DF,
(SELECT sum(b.bytes) TOTAL FROM V$Log b) LOG,
(SELECT sum((cffsz+1)*cfbsz) TOTAL FROM X$Kcccf c) CONTROL;
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
1335 150 18.59375 1503.59375
SELECT NAME, STATE, TOTAL_MB, FREE_MB FROM v$asm_diskgroup;
Disk Group State Total (MB) Free (MB)
--------------- ----------- ------------ ------------
DATA CONNECTED 9,216 5,657
FRA CONNECTED 9,216 8,998
Col "ASIGNADO" FOR 9999999990 head "ASIGNADO|(Mb)"
Col "LIBRE" FOR 9999999990 head "LIBRE|(Mb)"
Col "OCUPADO" FOR 9999999990 head "OCUPADO|(Mb)"
SELECT INST_ID, NAME, TOTAL_MB "ASIGNADO", FREE_MB "LIBRE", (TOTAL_MB - FREE_MB) "OCUPADO"
FROM GV$ASM_DISK
ORDER BY INST_ID, NAME;
ASIGNADO LIBRE OCUPADO
Instance ID Disk Group (Mb) (Mb) (Mb)
----------- --------------- ----------- ----------- -----------
1 ASMDISK01 2304 1425 879
ASMDISK02 2304 1408 896
ASMDISK03 2304 1415 889
ASMDISK04 2304 1409 895
ASMDISK05 2304 2250 54
ASMDISK06 2304 2251 53
ASMDISK07 2304 2249 55
ASMDISK08 2304 2248 56
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
13 rows selected.
Thursday, 15 March 2012
ASM SQLs' from RDBMS Instance
Subscribe to:
Posts (Atom)
