Search This Blog

Total Pageviews

Saturday 10 March 2012

asmcmd with awk

 

export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
export PATH=${PATH}:${ORACLE_HOME}/bin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export LC_ALL=en_GB

echo
echo
echo " ASM Disk % Full Used (MB) Free (MB) Total (MB) "
echo "======================================================================"

asmcmd lsdg | \
awk -v sq="'" 'BEGIN { getline } {
printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
}'

#awk -F|sq="'" 'BEGIN { getline } {
# printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
# }'

echo
echo





[oracle@oel5u4-+ASM dbhome_1] sh Asm.sh


ASM Disk % Full Used (MB) Free (MB) Total (MB)
======================================================================
DATA/ 84% 4,761 896 5,657
FRA/ 100% 8,998 0 8,998







==================

export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
export PATH=${PATH}:${ORACLE_HOME}/bin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export LC_ALL=en_GB

echo
echo
echo " ASM Disk % Full Used (MB) Free (MB) Total (MB) " >asm.txt
echo "======================================================================" >>asm.txt

asmcmd lsdg | \
awk -v sq="'" 'BEGIN { getline } {
printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
}' >>asm.txt

#awk -F|sq="'" 'BEGIN { getline } {
# printf" %s \t %d%% \t %9"sq"d \t %9"sq"d \t %12"sq"d \n",$13,100-100*($9/$8),$8-$9,$9,$8
# }'

echo >>asm.txt
echo >>asm.txt






[oracle@oel5u4-+ASM dbhome_1] cat asm.txt


ASM Disk % Full Used (MB) Free (MB) Total (MB)
======================================================================
DATA/ 84% 4,761 896 5,657
FRA/ 100% 8,998 0 8,998



ASM parameters and underscore parameters

 
ASM parameters and underscore parameters


http://anuj-singh.blogspot.com/2011/11/oracle-hidden-parameters.html


col "Instance Value" format a20
col parameter format a30
set pagesize 200

select a.ksppinm "Parameter", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and ksppinm like '%asm%'
order by a.ksppinm;

Parameter Instance Value
------------------------------ --------------------
_asm_acd_chunks 1
_asm_allow_only_raw_disks TRUE
_asm_allow_resilver_corruption FALSE
_asm_ausize 1048576
_asm_blksize 4096
_asm_disk_repair_time 14400
_asm_droptimeout 60
_asm_emulmax 10000
_asm_emultimeout 0
_asm_kfdpevent 0
_asm_libraries ufs
_asm_maxio 1048576
_asm_stripesize 131072
_asm_stripewidth 8
_asm_wait_time 18
_asmlib_test 0
_asmsid asm
asm_diskgroups DATA
asm_diskstring /dev/raw/raw1
asm_power_limit 1

20 rows selected.


=====================


set pagesize 200
column parameter format a37
column description format a30 word_wrapped
column "Session Value" format a10
column "Instance Value" format a10
select
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from
x$ksppi a,
x$ksppcv b,
x$ksppsv c
where
a.indx = b.indx
and a.indx = c.indx
and a.ksppinm like '%asm%' escape '\'
order by 1
/






SQL> set pagesize 200
SQL> /

Parameter Description Session Va Instance V
------------------------------------- ------------------------------ ---------- ----------
_asm_acd_chunks initial ACD chunks created 1 1
_asm_allow_only_raw_disks Discovery only raw devices TRUE TRUE
_asm_allow_resilver_corruption Enable disk resilvering for FALSE FALSE
external redundancy

_asm_ausize allocation unit size 1048576 1048576
_asm_blksize metadata block size 4096 4096
_asm_disk_repair_time seconds to wait before 14400 14400
dropping a failing disk

_asm_droptimeout timeout before offlined disks 60 60
get dropped (in 3s ticks)

_asm_emulmax max number of concurrent disks 10000 10000
to emulate I/O errors

_asm_emultimeout timeout before emulation 0 0
begins (in 3s ticks)

_asm_kfdpevent KFDP event 0 0
_asm_libraries library search order for ufs ufs
discovery

_asm_maxio Maximum size of individual I/O 1048576 1048576
request

_asm_stripesize ASM file stripe size 131072 131072
_asm_stripewidth ASM file stripe width 8 8
_asm_wait_time Max/imum time to wait before 18 18
asmb exits

_asmlib_test Osmlib test event 0 0
_asmsid ASM instance id asm asm
asm_diskgroups disk groups to mount DATA DATA
automatically

asm_diskstring disk set locations for /dev/raw/r /dev/raw/r
discovery aw1 aw1

asm_power_limit number of processes for disk 1 1
rebalancing


20 rows selected.


----
for fix control
set pages 5000 lines 200
col name format a30
col cur_val format a20

select i.ksppinm name , v.ksppstvl cur_val, v.ksppstdf default_val, v.ksppstvf
from x$ksppi i, x$ksppcv v
where i.indx = v.indx
and i.ksppinm = '_fix_control';
NAME                           CUR_VAL              DEFAULT_V   KSPPSTVF
------------------------------ -------------------- --------- ----------
_fix_control                   9550277:OFF          FALSE              0





SELECT i.instance_name instance,
  -- b.ksppstvl "Session_Value",
  c.ksppstvl value,c.ksppstvl/1024/1024 meg,c.ksppstvl/1024/1024/1024 gig,
  -- above is instance_value
  a.ksppinm "PARAMETER",KSPPDESC "DESCRIPTION"
  FROM
  x$ksppi a,
  x$ksppcv b,
  x$ksppsv c,
  v$instance i
  WHERE
  a.indx = b.indx
  AND
  a.indx = c.indx
  AND
  (a.ksppinm LIKE '/_/_%' escape '/' or a.ksppinm LIKE 'db_keep_cache_size' or a.ksppinm LIKE 'db_recycle_cache_size')
  and a.ksppinm not in ('__oracle_base')
  -- and a.ksppinm in ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target','_pga_limit_target_perc')
  and c.ksppstvl not in ('TRUE','FALSE')
  order by 3
  /
  
  

col DESCRIPTION for a30 
col VALUE for a20 
col PARAMETER for a30
SELECT i.instance_name instance,
  -- b.ksppstvl "Session_Value",
  c.ksppstvl value,c.ksppstvl/1024/1024 meg,c.ksppstvl/1024/1024/1024 gig,
  -- above is instance_value
  a.ksppinm "PARAMETER",KSPPDESC "DESCRIPTION"
  FROM
  x$ksppi a,
  x$ksppcv b,
  x$ksppsv c,
  v$instance i
  WHERE
  a.indx = b.indx
  AND
  a.indx = c.indx
  AND
  (a.ksppinm LIKE '/_/_%' escape '/' or a.ksppinm LIKE 'db_keep_cache_size' or a.ksppinm LIKE 'db_recycle_cache_size')
  and a.ksppinm not in ('__oracle_base')
  and c.ksppstvl not in ('TRUE','FALSE')
  order by 3
  /
  

===


set linesize 300 pagesize 300 col parameter for a30 col session for a28 col instance for a12 col "Session Value" for a15 col "Instance Value" for a15 col IS_SESSION_MODIFIABLE for a25 col IS_SYSTEM_MODIFIABLE for a25 col description for a70 col comment for a30 col "Default Value" for a15 SELECT 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 '/_%' escape '/' and upper(a.ksppinm) like upper('%&1%') /


=== set linesize 300 pagesize 300 col name for a40 col VALUE for a100 col "THREAD#" for a10 column num noprint SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE' OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%' MINUS SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') UNION SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2; THREAD# NAME VALUE ---------- ---------------------------------------- ---------------------------------------------------------------------------------------------------- * processes 3000 * sga_max_size 204010946560 * large_pool_size 2684354560 * pga_aggregate_limit 42949672960 * _ksb_restart_policy_times 0, 60, 120, 240




======

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;




set linesize 300 pagesiz 300
col NAME for a45
col BVAL for a20
col EVAL for a20
select e.parameter_name             name
         , b.value                      bval
         , decode(b.value, e.value, NULL, e.value) eval
      from dba_hist_parameter b
         , dba_hist_parameter e
     where b.snap_id(+)         = :BgnSnap
       and e.snap_id            = :EndSnap
       and b.dbid(+)            = :DID 
       and e.dbid               = :DID 
       and b.instance_number(+) = 1
       and e.instance_number    = 1
       and b.parameter_hash(+)  = e.parameter_hash
       and (   nvl(b.isdefault, 'X')   = 'FALSE'
            or nvl(b.ismodified,'X')  != 'FALSE'
            or     e.ismodified       != 'FALSE'
            or nvl(e.value,0)         != nvl(b.value,0)
           )
       and e.parameter_name not like '\_\_%' escape '\'
     order by e.parameter_name;
 
 

ASM reading files with direct OS access

 

 


 


 ASM reading files with direct OS access




SQL> select GROUP_KFFXP,DISK_KFFXP,AU_KFFXP from x$kffxp where number_kffxp in (select file_number from v$asm_alias where name like 'spfile%' );


GROUP_KFFXP DISK_KFFXP AU_KFFXP
----------- ---------- ----------
1 0 994



SQL> select GROUP_KFFXP,DISK_KFFXP,AU_KFFXP from x$kffxp where number_kffxp in (select file_number from v$asm_alias where name like 'spfile%' );


GROUP_KFFXP DISK_KFFXP AU_KFFXP
----------- ---------- ----------
1 0 994




col path format a30
select disk_number,path from v$asm_disk where GROUP_NUMBER=1 and disk_number in (0,994);

DISK_NUMBER PATH
----------- ------------------------------
0 /dev/raw/raw1


dd if=/dev/raw/raw1 bs=1024k count=1 skip=101|strings|more





SQL> !dd if=/dev/raw/raw1 bs=1024k count=1 skip=101|strings|more
U !
Is the column updatable?,
Column name,
Table name,
Table owner,
$Description of all updatable columns,
Is the column deletable?,
Is the column insertable?,
Is the column updatable?,
Column name,
Table name,
Table owner,
Description of updatable columns,
status of release,
version number,
product name,
5version and status information for component products,
global database name,
global database name,
8transaction branch ID at this database of the connection,
1session number at this database of the connection,
2the database id at the other end of the connection,
G"C" for request commit, else "N" for prepare or request readonly commit,
-in: name of local user; out: owner of db link,
/in: client database name; out: outgoing db link,
1"in" for incoming connections, "out" for outgoing,
Linformation about incoming and outgoing connections for pending transactions,
/global commit number for committed transactions,
8Oracle user name of the end-user at the topmost database,
)name of the host machine for the end-user,
8operating system specific name for the end-user terminal,
/operating system specific name for the end-user,
Dtime automatic recovery (RECO) last tried to recover the transaction,
:time of manual force decision (null if not forced locally),
Bvalue of SYSDATE when the row was inserted (tx or system recovery),
%text for "commit work comment <text>",
'C for commit, R for rollback, else null,
qyes => part of the transaction committed and part rolled back (commit or rollback with the FORCE option was used),
Bcollecting, prepared, committed, forced commit, or forced rollback,
globally unique transaction id,
!string of form: n.n.n, n a number,
5info about distributed transactions awaiting recovery,
Timestamp for the grant,
,Permission to make REFERENCES to the column?,
Permission to UPDATE the column?,

Oracle DBA

anuj blog Archive