ITRemote

Remote IT Services

#!/bin/ksh 
############################################################################ 
# Script Name..: 
# Description..: 
# Author.......: Michael Culp 
# Date.........: / /2012 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
#    alter session set current 
# Login User...: 
# Run Order....: 
# Dependent on.: 
# Script type..: # 
############################################################################ 

echo "ASM File types stored in this instance" 
echo ----------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    
set lines 120    
set pagesize 66    
column name format a25    
column type format a20    
select distinct type      
  from v\$asm_file; 
EOF 

echo "ASM files stored in ASM instance (better)" 
echo ----------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF 
SET LINESIZE  150 
SET PAGESIZE  9999 
SET VERIFY    off 
COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name' 
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?' 
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes' 
COLUMN blocks                 FORMAT 9,999,999,999,999    HEAD 'Blocks' 
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space' 
COLUMN type                   FORMAT a18                  HEAD 'File Type' 
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy' 
COLUMN striped                FORMAT a8                   HEAD 'Striped' 
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint BREAK ON report ON disk_group_name 
SKIP 1 compute sum label ""            of bytes space on disk_group_name 
compute sum label "Grand Total: " of bytes space on report 

SELECT CONCAT('+' || disk_group_name
     , SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path   
     , bytes   
     , space   
     , NVL(LPAD(type, 18)
     , '&lt;DIRECTORY&gt;')  type   
     , creation_date   
     , disk_group_name   
     , LPAD(system_created, 4) system_created 
FROM ( SELECT g.name disk_group_name
            , a.parent_index    pindex
            , a.name            alias_name 
            , a.reference_index rindex         
            , a.system_created  system_created         
            , f.bytes           bytes         
            , f.blocks          blocks         
            , f.space           space         
            , f.type            type         
            , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date       
FROM v\$asm_file f RIGHT OUTER JOIN 
     v\$asm_alias a USING (group_number, file_number)   JOIN 
     v\$asm_diskgroup g USING (group_number)) 
WHERE type IS NOT NULL START WITH (MOD(pindex, POWER(2, 24))) = 0     
CONNECT BY PRIOR rindex = pindex; 


echo "Amount of space left on individual ASM disk devices" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    
set pagesize 66    
set lines 100    
column path format a20    

select path
       , state
       , total_mb
       , free_mb
  from v\$asm_disk; 

EOF 



echo "IO Stats ASM disks" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    
set pagesize 66    
set lines 120    
column path format a20    
column read_time heading "Read Time" format 999,999.99    
column write_time                    format 999,999.99    
column writes                        format 999,999,999,999    
column reads                         format 999,999,999,999   
column bytes_written                 format 999,999,999,999    
column bytes_read                    format 999,999,999,999    

select path
      ,reads
      ,read_time
      ,bytes_read,           writes
,           write_time
,           bytes_written      
from v\$asm_disk; 



echo "Amount of space left on individual ASM disk devices" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" &lt;&lt;EOF    
set pagesize 66    
set lines 100    
column path format a20    
select path
      ,state
      ,total_mb
      ,free_mb
 from v\$asm_disk; 

EOF 

echo "IO Stats ASM disks" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    

set pagesize 66    
set lines 120    
column path format a20    
column read_time heading "Read Time" format 999,999.99    
column write_time                    format 999,999.99    
column writes                        format 999,999,999,999    
column reads                         format 999,999,999,999    
column bytes_written                 format 999,999,999,999    
column bytes_read                    format 999,999,999,999    
select path
,           reads
,           read_time
,           bytes_read
,           writes
,           write_time
,           bytes_written      
from v\$asm_disk; 
EOF 

#!/bin/ksh 
############################################################################ 
# Script Name..: 
# Description..: 
# Author.......: Michael Culp 
# Date.........: / /2012 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
#    alter session set current 
# Login User...: 
# Run Order....: 
# Dependent on.: 
# Script type..: 
# ############################################################################ 
echo "ASM File types stored in this instance" 
echo ----------------------------------------------------- 
sqlplus -s "/ as sysdba" >>EOF    
set lines 120    
set pagesize 66    
column name format a25    
column type format a20    
select distinct type      
from v\$asm_file; 
EOF echo "ASM files stored in ASM instance (better)" 
echo ----------------------------------------------------- 

sqlplus -s "/ as sysdba" &lt;&lt;EOF 
SET LINESIZE  150 
SET PAGESIZE  9999 
SET VERIFY    off 
COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name' 
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?' 
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes' 
COLUMN blocks                 FORMAT 9,999,999,999,999    HEAD 'Blocks' 
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space' 
COLUMN type                   FORMAT a18                  HEAD 'File Type' COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy' COLUMN striped                FORMAT a8                   HEAD 'Striped' COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date' COLUMN disk_group_name        noprint BREAK ON report ON disk_group_name SKIP 1 compute sum label ""              of bytes space on disk_group_name compute sum label "Grand Total: " of bytes space on report SELECT     CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path   , bytes   , space   , NVL(LPAD(type, 18), '&lt;DIRECTORY&gt;')  type   , creation_date   , disk_group_name   , LPAD(system_created, 4) system_created FROM     ( SELECT g.name           disk_group_name,              a.parent_index   pindex,              a.name               alias_name ,              a.reference_index    rindex         , a.system_created     system_created         , f.bytes              bytes         , f.blocks             blocks         , f.space              space         , f.type               type         , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date       FROM           v\$asm_file f RIGHT OUTER JOIN v\$asm_alias     a USING (group_number, file_number)                                     JOIN v\$asm_diskgroup g USING (group_number)     ) WHERE type IS NOT NULL START WITH (MOD(pindex, POWER(2, 24))) = 0     CONNECT BY PRIOR rindex = pindex; echo "Amount of space left on individual ASM disk devices" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 100    column path format a20    select path,           state,           total_mb,           free_mb      from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 120    column path format a20    column read_time heading "Read Time" format 999,999.99    column write_time                    format 999,999.99    column writes                        format 999,999,999,999    column reads                         format 999,999,999,999    column bytes_written                 format 999,999,999,999    column bytes_read                    format 999,999,999,999    select path,           reads,           read_time,           bytes_read,           writes,           write_time,           bytes_written      from v\$asm_disk; EOF                                                                                                123,1         Bot echo "Amount of space left on individual ASM disk devices" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 100    column path format a20    select path,           state,           total_mb,           free_mb      from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 120    column path format a20    column read_time heading "Read Time" format 999,999.99    column write_time                    format 999,999.99    column writes                        format 999,999,999,999    column reads                         format 999,999,999,999    column bytes_written                 format 999,999,999,999    column bytes_read                    format 999,999,999,999    select path,           reads,           read_time,           bytes_read,           writes,           write_time,           bytes_written      from v\$asm_disk; EOF

Top 5 


#!/bin/ksh
############################################################################
# Script Name..: top5.ksh
# Description..:
# Author.......: Michael Culp
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
spool top5.log

select day
,      event_name
,      Total_wait from ( select day
                         ,      event_name
                         ,      sum(event_time_waited) total_wait
                         ,      row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT   to_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy') day,s.begin_interval_time, m.*
FROM (SELECT ee.instance_number
      ,      ee.snap_id
      ,      ee.event_name
      ,      ROUND (ee.event_time_waited / 1000000) event_time_waited,
ee.total_waits,
ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
1
) pct,
ROUND ((ee.event_time_waited / ee.total_waits) / 1000
) avg_wait
FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
ee1.time_waited_micro
- ee2.time_waited_micro event_time_waited,
ee1.total_waits - ee2.total_waits total_waits
FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
ON ee1.snap_id = ee2.snap_id + 1
AND ee1.instance_number = ee2.instance_number
AND ee1.event_id = ee2.event_id
AND ee1.wait_class_id <> 2723168908
AND ee1.time_waited_micro - ee2.time_waited_micro > 0
UNION
SELECT st1.instance_number, st1.snap_id,
st1.stat_name event_name,
st1.VALUE - st2.VALUE event_time_waited,
1 total_waits
FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
ON st1.instance_number = st2.instance_number
AND st1.snap_id = st2.snap_id + 1
AND st1.stat_id = st2.stat_id
AND st1.stat_name = 'DB CPU'
AND st1.VALUE - st2.VALUE > 0
) ee
JOIN
(SELECT et1.instance_number, et1.snap_id,
et1.VALUE - et2.VALUE total_time_waited
FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
ON et1.snap_id = et2.snap_id + 1
AND et1.instance_number = et2.instance_number
AND et1.stat_id = et2.stat_id
AND et1.stat_name = 'DB time'
AND et1.VALUE - et2.VALUE > 0
) et
ON ee.instance_number = et.instance_number
AND ee.snap_id = et.snap_id
) m
JOIN
dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
)where rn < 6;

spool off
EOF



#!/bin/ksh

echo "Find the database you want to delete"

# srvctl stop database -d  <database name>
# srvctl remove instance -d <database name> -i <instance name>
# srvctl remove instance -d WSRRXT01 -i WSRRXT012
# srvctl remove database -d WSRRXT01

&nbsp;



#!/bin/ksh
############################################################################
# Script Name..: standard_cron.ksh
# Description..:
# Author.......: Michael Culp
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################



asm_dsk_sze()
{

####################################################
# Shows the sizes of the diskgroups
# No prereq for ASM env
####################################################
clear

sqlplus -s "/ as sysdba" <<EOF
set wrap off
set lines 155 pages 9999
col "Group" for 999
col "Group Name" for a25 Head "Group|Name"
col "Disk Name" for a10
col "State" for a10
col "Type" for a10 Head "Diskgroup|Redundancy"
col "Total GB" for 9,999,990 Head "Total|GB"
col "Free GB" for 9,999,990 Head "Free|GB"
col "Imbalance" for 99.9 Head "Percent|Imbalance"
col "Variance" for 99.9 Head "Percent|Disk Size|Variance"
col "MinFree" for 99.9 Head "Minimum|Percent|Free"
col "MaxFree" for 99.9 Head "Maximum|Percent|Free"
col "DiskCnt" for 9999 Head "Disk|Count"

prompt
prompt ASM Disk Groups
prompt ===============

SELECT g.group_number "Group"
, g.name "Group Name"
, g.state "State"
, g.type "Type"
, g.total_mb/1024 "Total GB"
, g.free_mb/1024 "Free GB"
, 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"
, 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"
, 100*(min(d.free_mb/d.total_mb)) "MinFree"
, 100*(max(d.free_mb/d.total_mb)) "MaxFree"
, count(*) "DiskCnt"
FROM v\$asm_disk d, v\$asm_diskgroup g
WHERE d.group_number = g.group_number
and d.group_number <> 0
and d.state = 'NORMAL'
and d.mount_status = 'CACHED'
GROUP BY g.group_number
, g.name
, g.state
, g.type
, g.total_mb
, g.free_mb
ORDER BY 1;

EOF

}




function db_switch
{
###############################################################################
# function switches the database environments for any databases on the cluster
#
###############################################################################

#cd /var/opt/oracle/admin/scripts
#. ./env_generic

# export NEW_ORACLE_SID=ASM1+; . ~/.profile > /dev/null

echo
echo "Check database(s) in OCR"
echo ---------------------------------------
echo

gen_db_lst

#srvctl config &gt; dblst
#echo
#echo "Databases listed in clusterware"
#cat dblst
#echo

export NEW_ORACLE_SID=FYTEST1; . ~/.profile &gt; /dev/null

sqlplus -s "/ as sysdba" &lt; /dev/null

sqlplus -s "/ as sysdba" &lt; /dev/null

sqlplus -s "/ as sysdba" &lt; /dev/null
dashboard

# end of function
}

function next_menu
{
echo
echo "# ----------------------------------------------------------------------- #"
echo "[ ... Press any key to continue ... ]"
read next
}


sho_db_param()
{

sqlplus -s "/ as sysdba" <<EOF
set echo on
set feedback on
set linesize 500

show parameter sga_max_size
show parameter sga_target

-- alter system set sga_max_size=4G scope=spfile sid='*';
-- alter system set sga_target=4G scope=spfile sid='*';

show parameter ldap

-- alter system set ldap_directory_access=PASSWORD scope=both sid='*';

show parameter ldap

-- CREATE USER NBKGYQP IDENTIFIED GLOBALLY AS 'cn=nbkgyqp,cn=Users,dc=bankofamerica,dc=com'
--   DEFAULT TABLESPACE users
--   TEMPORARY TABLESPACE temp
--   PROFILE password_profile;

-- GRANT CREATE SESSION TO NBKGYQP;

show parameter cpu_count
show parameter db_block_size

show parameter statistics_level

show parameter nls

EOF

}



chg_awr_days()
{

db_id=$1
ret=$2

echo "Change the AWR number of days"

sqlplus -s "/ as sysdba" <<EOF

-- retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800)
-- retention=>value in minutes so (30 days * 24 (hours per day) * 60 minutes per hour = 43200)
-- max value can be set by passing a value of 0 which means forever
-- . internal=>60min (snap at this interval), a value of 0 will turn off AWR
-- . topnsql . top N sql size, specify value of NULL will keep the current setting

exec dbms_workload_repository.modify_snapshot_settings(retention=>${ret}, interval=>60, topnsql=>100, dbid=>${db_id});

EOF

}

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram