Oracle : Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use incase of High or Normal Redundancy

TOTAL_MB:- Refers to Total Capacity of the Diskgroup
FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its calculated as :-

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy
USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy


Query to Run:

column total format 999,999 Heading "Total(G)"
column free format 999,999 Heading "Free (G)"
column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"
column Usable_GB format 999,999 Heading "Space Available |to Use(G)"
column pct format 999.0 Heading "% Free |in DG" 
column pct2 format 999.0 Heading "Real % Free |in DG" 
column type format a10
column name format a20
set linesize 200
set colsep '|'
prompt
Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"
prompt
select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2  from v$asm_diskgroup;


Sample Output :

"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"


                    |          |        |        |     Space Used |Space Available |% Free |Real % Free
NAME                |TYPE      |Total(G)|Free (G)|for Mirroring(G)|       to Use(G)|  in DG|       in DG
--------------------|----------|--------|--------|----------------|----------------|-------|------------
DATA1              |HIGH      | 260,496|  57,951|          14,472|          14,493|   22.2|         5.6
REDO1              |HIGH      |  65,124|  33,322|           3,618|           9,901|   51.2|        15.2

Oracle : Check Hidden Parameter Values

You can run the below query to find the value of the Hidden parameters set on the database

undef 1 
set lines 150 
column value for a40 
col Parameter for a50 
col "Default Value" for a13 
col "Session Value" for a13 
col "Instance Value" for a13 
col IS_SESSION_MODIFIABLE for a25 
col IS_SYSTEM_MODIFIABLE for a24 

SELECT distinct a.ksppinm "Parameter", b.KSPPSTDF "Default Value", 
b.ksppstvl "Session Value", 
c.ksppstvl "Instance Value", 
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE, 
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE 
FROM x$ksppi a, 
x$ksppcv b, 
x$ksppsv c 
WHERE a.indx = b.indx 
AND a.indx = c.indx 
AND a.ksppinm LIKE '_rollback_segment_%' 
/ 



Parameter                                          Default Value Session Value Instance Valu IS_SESSION_MODIFIABLE     IS_SYSTEM_MODIFIABLE

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

_rollback_segment_count                            FALSE         4000          4000          FALSE                     IMMEDIATE


_rollback_segment_initial                          TRUE          1             1             FALSE                     FALSE

Oracle : Script which shows who owns which objects and where

select
  substrb(decode(grouping(owner),1,'{all}',owner),1,10) OWNER,
  substrb(decode(grouping(tablespace_name),1,'{all}',tablespace_name),1,13) TS_NAME,
  substrb(decode(floor(sum(bytes)/1073741824),0,
    decode(floor(sum(bytes)/1048576),0,
      to_char(sum(bytes)/1024,'9999')||'K',
      to_char(sum(bytes)/1048576,'9999')||'M'),
      to_char(sum(bytes)/1073741824,'99.9')||'G'),2,6) "SIZE",
  to_char(sum(decode(segment_type,'TABLE',1,0)),'999999') TAB,
  to_char(sum(decode(segment_type,'INDEX',1,0)),'999999') IDX,
  substrb(to_char(sum(decode(segment_type,'ROLLBACK',1,0)),'999'),2,3) ROL,
  substrb(to_char(sum(decode(segment_type,'TEMPORARY',1,0)),'999'),2,3) TMP,
  substrb(to_char(sum(decode(segment_type,'CACHE',1,0)),'999'),2,3) CAC,
  substrb(to_char(sum(decode(segment_type,'CLUSTER',1,0)),'999'),2,3) CLU,
  substrb(to_char(sum(decode(substrb(segment_type,1,3),'LOB',1,0)),'999'),2,3) LOB,
  to_char(count(*),'9999999') TOTAL
from dba_segments
group by rollup( owner, tablespace_name )
order by 1,2,3 desc,4 desc
/

Oracle : Script shows object locks currently held in the database

select /*+ rule */
substrb(vlo.oracle_username,1,10) db_user,
substrb(vlo.os_user_name,1,10) os_user,
substrb(to_char(vlo.session_id),1,4) sid,
decode(vs.lockwait,null,substrb(vs.status,1,3),'WAI') "STA",
substrb(dbo.owner,1,10) owner,
substrb(dbo.object_name,1,25) obj_name,
substrb(dbo.object_type,1,3) typ,
substrb(decode(locked_mode,0,'NONE',1,'NULL',2,'ROWS',3,'ROWX',
  4,'SHAR',5,'SRWX',6,'EXCL',to_char(vlo.locked_mode)),1,4) "MODE"
from v$locked_object vlo, v$session vs, dba_objects dbo
where vlo.session_id = vs.sid
and vs.type != 'BACKGROUND'
and vlo.object_id = dbo.object_id
order by decode(vs.lockwait,null,decode(vs.status,'ACTIVE',2,3),1),3,1
/