Category Archives: Scripts

TOP5.ksh


#!/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

DB Remove 01


#!/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;

Standard CRON


#!/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 function


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

}

db_switch func

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 > dblst
#echo
#echo “Databases listed in clusterware”
#cat dblst
#echo

export NEW_ORACLE_SID=FYTEST1; . ~/.profile > /dev/null

sqlplus -s “/ as sysdba” < /dev/null

sqlplus -s “/ as sysdba” < /dev/null

sqlplus -s “/ as sysdba” < /dev/null
dashboard

# end of function
}

sho_db_param.ksh


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 function


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

}