oracle restart single change hostname

deconfig [code] $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/ -deconfig -force [/code] config [code] $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/ [/code] add resources change hostname in $ORACLE_HOME/netowrk/admin/listener.ora grid user [code] srvctl add listener srvctl add asm -d ‘/dev/oracleasm/disks/*’ srvctl start asm srvctl start listener [/code] oracle user: [code] srvctl add database -d $ORACLE_SID -o $ORACLE_HOME [/code]

find current effective hidden param

[code language=»sql»] col PARAMETER format a20 col SESSION_VALUE format a20 col INSTANCE_VALUE format a20 col DEFAULT_VALUE format a20 col DESCRIPTION format a100 select a.ksppinm as Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value , b.ksppstdf Default_value, decode(bitand(a.ksppiflg/256,3),1, ‘True’, ‘False’) SESSMOD, decode(bitand(a.ksppiflg/65536,3),1,’IMMEDIATE’,2,’DEFERRED’,3,’IMMEDIATE’,’FALSE’) SYSMOD, a.ksppdesc Description from sys.x$ksppi a, sys.x$ksppcv b , sys.x$ksppsv c where a.indx = b.indx and a.indx […]

script to analyze partition tables data distribution

[code language=»sql»] select table_owner||’.’||table_name,partition_name,100* (round (num_rows/ case when sum(num_rows) over( partition by table_name) >0 then sum(num_rows) over( partition by table_name) else 1 end ,3)) as pct_of_rows ,num_rows, sum(num_rows) over( partition by table_name) total_row_cnt from dba_tab_partitions where table_name in ( select table_name from DBA_PART_TABLES dpt where dpt.owner not like ‘SYS%’ and dpt.interval is null and dpt.partitioning_type […]

disk latency from ash by time

some queryes to measure disk io [code language=»sql»] select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(wait_class,’CPU’) wait_class from v$active_session_history group by wait_class order by 1 desc; select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(event,’CPU’) event from v$active_session_history where wait_class like ‘%I/O%’ group by event order by 1 desc; [/code] generate pivot […]