ls -al /oracle_crs/product/*/crs_?/log/*/client/oifcfg.l* ls -al /oracle_crs/product/*/crs_?/log/*/client/olsnodes.l* ls -al /oracle_crs/product/*/crs_?/cv/log/cvutrace.log.* ls -al /oracle_crs/product/*/crs_?/log/*/cvu/cvutrc/cvuhelper.log.* ls -al /oracle/product/*/db_*/xdk/admin/XSQLConfig.xml
lst_objs_all
lst_obj_schm
lst_objs_schm_tbls
chg_profile
qry_template_function
lst_users
lst_all_users - uses the function sho_users
drp_app_users
sho_users
sho_users_locked
sho_users_prf_default
sho_quotas
sho_sessions
sho_session_02
sho_session_03
chg_quota_PIPER_D11_OWNER - This should take a parameter for owner schema and tablespace
chg_pword_PIPER_D11_STAGING
This is a library for DDL Operations
This will be the repository of the functions
#!/bin/ksh
######################################################################
gg_ext_det()
{
# read the environment file for gg here
# . /var/opt/oracle/.ggsora12_env
#
# info extract <extract name>
# send EP1ALSDM, report
echo
echo "Extract detail................."
echo
/oracle/product/12.1/gg_1/ggsci <<EOF
sh date
send <extract name>, status
send <extract name>, showtrans
send <extract name> getlag
stats <extract name> totalsonly *.*, reportrate sec
info <extract name>, showch
EOF
}
cr_dba_gg_tst_usr()
{
######################################################################
# function Name: cr_dba_gg_tst_usr
# Description..: Create schema/user for golden gate testing
# Author.......: Michael Culp
# Date.........: 05/09/2016
# Version......: 1.0
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.: DBA_GG_TST
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
######################################################################
sqlplus -s "/ as sysdba" <<EOF
set echo on
set feed on
set time on
set timing on
spool logs/cr_dba_gg_tst_usr.log
-- drop role GG_APP_ROLE;
--
-- GG_APP_ROLE (Role)
--
-- create role CST_L01_APP_ROLE not identified;
-- grant create session to cst_L01_APP_ROLE;
-- grant create synonym to cst_L01_APP_ROLE;
-- drop role cst_L01_VIEW_ROLE;
--
-- cst_L01_VIEW_ROLE (Role)
--
-- create role CST_L01_VIEW_ROLE not identified;
-- grant create session to cst_L01_VIEW_ROLE;
--
-- CREATE USER dba_gg_test (Schema Owner GoldenGate Test user)
--
drop user DBA_GG_TST cascade;
create user DBA_GG_TST identified by gg5d01_o
default tablespace mrcxx_data
temporary tablespace temp
profile exempt_app_profile;
grant create session to DBA_GG_TST;
grant dba to DBA_GG_TST;
alter user DBA_GG_TST quota unlimited on mrcxx_data;
-- grant application_owner_role to cst_L01_owner;
-- revoke unlimited tablespace from cst_L01_owner;
-- alter user cst_L01_owner default role all;
--
-- CREATE USER CST_L01_USER (User Account)
--
-- drop user cst_L01_user cascade;
-- create user cst_L01_user identified by c5td01_u
-- default tablespace mrcxx_data
-- temporary tablespace temp
-- profile exempt_app_profile;
-- grant CST_L01_APP_ROLE to CST_L01_USER;
-- revoke unlimited tablespace from cst_L01_user;
-- alter user cst_L01_user quota unlimited on mrcxx_data;
-- alter user cst_L01_user default role all;
--
-- CREATE USER cst_L01_read (READ ONLY Account)
--
-- drop user cst_L01_read cascade;
-- create user cst_L01_read identified by c5td01_r
-- default tablespace piper_data
-- temporary tablespace temp
-- profile exempt_app_profile;
-- grant cst_L01_VIEW_ROLE to cst_L01_read;
-- revoke unlimited tablespace from cst_L01_read;
-- alter user cst_L01_read quota unlimited on piper_data;
-- alter user cst_L01_read default role all;
commit;
spool off
exit
EOF
}
-- To get the log switch frequency
select to_char(FIRST_TIME, 'MON-DD'),
count(*)
from v$log_history
group by to_char(FIRST_TIME, 'MON-DD')
order by 1;
-- To check lock objects
set linesize 200
set pagesize 80
col inst_id format 999
col sid format 99999
col serial# format 999999
col username format a15
col machine format a15
col status format a12
col sql_id format a15
col prev_sql_id format a15
col object_name format a30
col object_id fromat 9999999
select b.inst_id,
b.sid,
b.serial#,
b.username,
b.machine,
b.status,
b.sql_id,
-- b.prev_sql_id,
-- c.sql_text,
-- d.object_id,
e.object_name,
a.start_time,
b.logon_time
from gv$transaction a,
gv$session b,
gv$sql c,
v$locked_object d,
all_objects e
where a.inst_id = b.inst_id
and a.ses_addr = b.SADDR
and b.prev_sql_addr = c.address(+)
and b.prev_hash_value = c.hash_value(+)
and b.prev_child_number = c.child_number(+)
and b.inst_id = c.inst_id(+)
and b.prev_sql_id=c.sql_id
and d.object_id=e.object_id
and d.session_id=b.sid(+)
/
This is a library for DDL Operations
This will be the repository of the functions
#!/bin/ksh
#################################################################################################
lst_objs_all()
{
##############################
# List objs for schema
##############################
echo
echo
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
column object_name format a25
-- spool <some file name>
set serveroutput on size 1000000
BEGIN
dbms_output.put_line('-----------------------------------------------------------------');
dbms_output.put_line('<<<<<<<<<<<<<<<<<<<< All database objects >>>>>>>>>>>>>');
dbms_output.put_line('-----------------------------------------------------------------');
END;
/
-- from dba_segments
-- where owner='SCHEMA_OWNER';
select table_name,
tablespace_name,
avg_row_len*num_rows
from dba_tables
where owner not in('SYS','SYSTEM','DBSNMP','WMSYS','OUTLN','APPQOSSYS','ORACLE_OCM','CORP_AUDIT')
order by table_name asc;
BEGIN
dbms_output.put_line('------------------------------------------------------------------------');
dbms_output.put_line('<<<<<<<<<<<<< Schema Objects Count by object type >>>>>>>>>>>>>>>>>>>>>>');
dbms_output.put_line('------------------------------------------------------------------------');
END;
/
select owner,
count(object_type) "Count",
object_type
from all_objects
where owner not in('SYS','SYSTEM','DBSNMP','WMSYS','OUTLN','APPQOSSYS','ORACLE_OCM','CORP_AUDIT')
group by owner, object_type
order by owner;
EOF
}
sho_users()
{
##############################################
# Show the quotas for users
##############################################
echo
echo "Show the users "
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
select username
from dba_users
order by 1;
EOF
}
sho_quotas()
{
##############################################
# Show the quotas for users
##############################################
echo
echo "Show the quotas for users "
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
PROMPT
PROMPT Show the quotas for all of the users
PROMPT
select * from dba_ts_quotas
order by tablespace_name;
EOF
}
del_app_users()
#############################################################
# This is a generic drop user cascade function to drop users
#############################################################
{
echo
sqlplus -s "/ as sysdba" ;EOF
set echo on
set feed on
set time on
set timing on
spool
drop role <role name>_ROLE;
commit;
spool off
exit
EOF
}
cr_app_users()
{
echo "Create application users"
}
lst_objs_schm()
{
##############################
# List objs for schema
##############################
SCHM=$1
echo
echo "List objects for schema ${SCHM}..."
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
column object_name format a25
-- spool <some file name>
set serveroutput on size 1000000
BEGIN
dbms_output.put_line('-----------------------------------------------------------------');
dbms_output.put_line('<<<<<<<<<<< ${SCHM} Objects >>>>>>>>>>>>>');
dbms_output.put_line('-----------------------------------------------------------------');
END;
/
-- from dba_segments
-- where owner='${SCHM}';
select table_name,
tablespace_name,
avg_row_len*num_rows
from dba_tables
where owner='${SCHM}'
order by table_name asc;
PROMPT
PROMPT
PROMPT
BEGIN
dbms_output.put_line('-----------------------------------------------------------------');
dbms_output.put_line('<<<<<<<<<<< ${SCHM} Objects Count ${SCHM} >>>>>>>>>>>>>>>>>');
dbms_output.put_line('-----------------------------------------------------------------');
END;
/
select owner,
count(object_type) "Count",
object_type
from all_objects
where owner='${SCHM}'
group by owner, object_type;
EOF
}
sho_seq()
{
echo
echo
echo
schm=$1
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
PROMPT
PROMPT Showing sequences for ${schm}
PROMPT
select count(*) from dba_sequences where sequence_owner='${schm}';
select sequence_name, sequence_owner from dba_sequences where sequence_owner='${schm}';
EOF
}
test_conn_user()
{
###############################################################
# This is the one table currently not being produced by Zafin
# pass the environment name and the password for the user
###############################################################
ENV=$1
PWRD=$2
echo
echo "User : "${ENV}
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
connect ${ENV}/${PWRD}
select owner,
table_name
from all_tables
order by owner;
select object_name
from all_objects
where object_type='SYNONYM';
EOF
}
This script is in a family of 4 overall scripts to complete configuration of nodes, clusters, databases, and ddl. This is the script to create and configure clusters
create the sysctl.conf file
check hugepages
check the tcp settings
adjust the SHMMAX
adjust the SHMALL
create the limits.conf file
Function library called apex_common.ksh (rename it later)
Functions include currently:
######################################################
# SECTION
# Initialize section
# and test functions
#
# mrctest
# root_setup
# init_apex
######################################################
# SECTION GENERAL DB
# Initialize section
# and test functions
#
# db_fixes_ver
######################################################
# SECTION APEX SPECIFIC
# Initialize section and test functions
#
# apex_hc
# lst_db - needs init_apex fn executed first
# lst_db_all
# lst_db_full
# count_db
# count_cls
# count_mch
# find_db
# find_mch
# find_cls
#########################
# Utility
#
# cnt_yn
# gen_srv_lst
# gen_srv_lst_verbose
# lst_type_hm ()
# lst_type_dir ()
# lst_db_apex_id ()
# db_rec_parm()
# db_psu
# sho_env - THIS MAY BE DUPLICATE
# sho_alias - THIS MAY BE DUPLICATE
# ins_audit_old ()
#################################################################
# ASM Section
#
# asm_disk
# asm_disk_sysasm
# sho_asm_disk
# asm_unbal
# asm_dsk_sze
# asm_0002
# asm_disk_ops
# asm_test
######################################
# Utility section #2
#
# chk_sqlp
# next_menu
# chk_sys_pass
# run_sql_statement
# dir_chk
# dir_chk_cr
# remote_connect
# db_switch
######################################################################
# Utility section #3
#
# initialize
# init_mrc
# gen_db_lst
# crs_srv_lst_cfg
# crs_sho_srv_cfg_home
# crs_sho_srvctl_cfg
# crs_sho_srvctl_stat
# crs_sho_srvctl_db_stat
# test_vip
# ocr_chk
# find_db_02
#####################################################################
# Utility functions
#
# save_env
# sho_env
# rest_env
# disp_inst
# rest_save
# chk_db_nm2dblst
# sho_insts
# chg_inst
# chk_db_running
# get_crs_clust_name
# insertMCH_InCentralRepository
# updt_mch_InCentralRepository
# mch_InCentralRepository
# get_inst_details
# insertInst_InCentralRepository
# inst_InCentralRepository
# get_db_details
# update_mach_details
# update_db_details
# cpu_usage_02
# sho_db_details
# updt_db_InCentralRepository
# db_InCentralRepository
# insertDB_InCentralRepository
# insertDB_APEX
# mrc_readdb
# mrc_upd_disk
###########################################################
# Cluster functions
#
# cls_qa_chk () - Cluster QA check
# cls_qa_upd () - Update the cluster record
# cls_qa_rev ()
# cls_rec_disp ()
# db_qa_chk ()
# db_qa_upd()
# db_qa_rev()
# sho_db_ver() - Show database version
# find_db2()
# sho_cls()
# sho_apex_db
# sho_apex_app
###########################################################
# DB Utility
#
# db_ind_size
# db_size_comp
# db_size
# db_loop_inst - Loop through the database instances
# db_stub - Stub function
# dashboard - This is the dashboard function
# menu_sel_text
###########################################################
# ORAchk section
# init_orachk - Sets up the variables needed for running the ORAchk stuff
# start_orachk - Starts ORAchk daemon
# get_all_parms_orachk - Shows all of the parms for ORAchk
# status_orachk - Shows the status of ORAchk
# stop_orachk - Stops the ORAchk daemon
# version_orachk - Shows the version of ORAchk
# all_orachk
# orachk --help
###########################################################
# dcli_ver
# dcli_hlp
# init_exp
# init_exp_scp
# BatchExpert
# Batch_Expert_scp
# dir_cr_apex
# dir_cr_cls
# ins_audit
# clustat
# sho_webdb
# sho_dir
# init_oem
# oem_stat_agt
# oem_stat_agt_hb
# oem_stat_sched
# oem_stat_sched_db
# tblspc_used
# enable_bct
# use_lrg_pgs
# fts_mrc
# lst_dba_users
# lst_any_apex_users
# lst_dba_sys_privs
# add_tgt
# huge_pages
# sho_huge_pages
# sho_alias
# sho_memory
# sho_sysctl
# must_gather
# usage_mg
awr_stats_mrc_fn.ksh - Is the function that uses the functions listed below
# awr_set_env
# awr_get_ver
# awr_inst_no
# awr_dbid
# awr_max_snap
# awr_min_snap
# awr_stat_10 - AWR Stats for Oracle version 10g
# awr_stat_11 - AWR Stats for Oracle version 11g
# awr_stat_12 - AWR Stats for Oracle version 12c
main_menu
menu_02
menu_03 -
menu_perftune - Performance tuning menu
menu_tmplt - menu template
test_load - test load function from the DPODS project
sess_trc - Session trace could be very useful
trnc_tbl - Truncate table function
sho_const - Shows the contraints
buff_blast - This will clear the buffer for the SGA
cannot_use_thi_sql_id_mrc() - Needs some work
find_sql_ids - Find SQL IDs
sho_plan_sql_id - Shows the plan from a SQL ID
Sho_db_param - Shows the current database parameters
Sho_dbid - shows the current database dbid
sho_dbid_fn.ksh - Script that uses the function
chg_awr_days - takes 2 parameters, db_id and retention value in minutes
chk_fra - Check and sho the FRA space
sho_dir_s20 - show the directory for scripts from
chk_interconn - Check the interconnect
chk_interconn_fn.ksh - Script that uses the function
chk_svcs - check the services of a database
#################################################
DDL_COMMON.ksh
#################################################
lst_objs_all
lst_objs_schm - List all objects by schema, schema is the parameter
lst_objs_schm_tbls - uses the LIKE
lst_objs_PIPER
test_conn_user - test what they can read as $1 user, $2 pword
cnt_CST_L01_OWNER
cnt_PIPER_D11_OWNER
qry_template_function
lst_users
lst_all_users
drp_app_users
sho_users
sho_quotas
sho_sessions
sho_session_02
chg_quota_PIPER_D11_OWNER
chg_pword_PIPER_D11_STAGING
chg_pword_all_PIPER_D11
qry_01_test
qry_02_test
qry_03_test
qry_04_test
qry_05_test
qry_06_test
qry_07_test
qry_08_test
chg_pword_all_CST_L01
chg_pword_all_MISITE
chg_pwd
obj_cnt
grant_piper_d11_user_role
cr_ctrl_hst_tab
cr_cntrl_tab - original function NOT USED anymore
test_ctrl_tab
sho_stg_tbl_cnt
sho_tbl_cnt_core
sho_trigger_cnt
sho_seq
grnt_role
test_conn_user
test_conn_core_tbl
cr_dba_gg_tst_usr
cr_dba_gg_tst_objs
sho_gg_test_tbl
test_gg_ins
test_gg_upd
gg_stat
gg_start_extr
gg_cr_extr_parm
gg_cr_repl_parm
test_gg_del
cr_180_trunc
cr_180_stats
test_query_11
gg_chk_ddl
sho_roles
sho_all_db_roles - Shows all roles in the database
cp_ddl_com
sho_db_param
sho_svcs
const
cp_piper_dev
cp_piper_exa_TT
cp_piper_exa_PD
cp_piper_ddl_com_dev
DBADMIN_APEX.ksh
initialize ()
{
initialize code
}
function1 ()
{
function stuff
}
Functions are the key to writing just about ANY program that is longer than a page or so of text. Essentially, its all a matter of breaking up a large program, into smaller, managable chunks. Ideally, functions are sort of like 'objects' for program flow. You pick a part of your program that is pretty much self-contained, and make it into its own 'function'
Why are functions critical?
Properly written functions should exist by themselves, and affect few things external to themselves. You should DOCUMENT what things it changes external to itself. Then you can look very carefully just at the function, and determine whether it actually does what you think it should do.
When your program isn't working properly (WHEN, not if), you can then put in little debug notes to yourself in the approximate section you think is broken. If you suspect a function is not working, then all you have to verify is
Is the INPUT to the function correct?
Is the OUTPUT from the function correct?
Once you have done that, you then know the entire function is correct, for that particular set of input(s), and you can look for errors elsewhere.
A trivial function
printmessage() {
echo "Hello, this is the printmessage function"
}
printmessage
The first part, from the first "printmessage()" all the way through the final '}', is the function definition. It only defines what the function does, when you decide to call it. It does not DO anything, until you actually say "I want to call this function now".
You call a function in ksh, by pretending it is a regular command, as shown above. Just have the function name as the first part of your line. Or any other place commands go. For example,
echo The message is: `printmessage`
Remember: Just like its own separate shellscript. Which means if you access "$1" in a function, it is the first argument passed in to the function, not the shellscript.
Debugging your functions
If you are really really having difficulties, it should be easy to copy the entire function into another file, and test it separately from the main program.
This same type of modularity can be achived by making separate script files, instead of functions. In some ways, that is almost preferable, because it is then easier to test each part by itself. But functions run much faster than separate shellscripts.
A nice way to start a large project is to start with multiple, separate shellscripts, but then encapsulate them into functions in your main script, once you are happy with how they work.
CRITICAL ISSUE: exit vs return
THE main difference when changing between shellscripts and functions, is the use of "exit".
'exit' will exit the entire script, whether it is in a function or not.
'return' will just quit the function. Like 'exit', however, it can return the default "sucess" value of 0, or any number from 1-255 that you specify. You can then check the return value of a function, just in the same way you can check the return value of an external program, with the $? variable.
# This is just a dummy script. It does not DO anything
fatal(){
echo FATAL ERROR
# This will quit the 'fatal' function, and the entire script that
# it is in!
exit
}
lessthanfour(){
if [[ "$1" = "" ]] ; then echo "hey, give me an argument" ; return 1; fi
# we should use 'else' here, but this is just a demonstration
if [[ $1 -lt 4 ]] ; then
echo Argument is less than 4
# We are DONE with this function. Dont do anything else in
# here. But the shellscript will continue at the caller
return
fi
echo Argument is equal to or GREATER than 4
echo We could do other stuff if we wanted to now
}
echo note that the above functions are not even called. They are just
echo defined
A bare "return" in a shellscript is an error. It can only be used inside a function.
CRITICAL ISSUE: "scope" for function variables!
Be warned: Functions act almost just like external scripts... except that by default, all variables are SHARED between the same ksh process! If you change a variable name inside a function.... that variable's value will still be changed after you have left the function!! Run this script to see what I mean.
#!/bin/sh
# Acts the same with /bin/sh, or /bin/ksh, or /bin/bash
subfunc(){
echo sub: var starts as $var
var=2
echo sub: var is now $var
}
var=1
echo var starts as $var, before calling function '"subfunc"'
subfunc # calls the function
echo var after function is now $var
To avoid this behaviour, and give what is known as "local scope" to a variable, you can use the typeset command, to define a variable as local to the function.
#!/bin/ksh
# You must use a modern sh like /bin/ksh, or /bin/bash for this
subfunc(){
typeset var
echo sub: var starts as $var '(empty)'
var=2
echo sub: var is now $var
}
var=1
echo var starts as $var, before calling function '"subfunc"'
subfunc # calls the function
echo var after function is now $var
Another exception to this is if you call a function in the 'background', or as part of a pipe (like echo val | function )
This makes the function be called in a separate ksh process, which cannot dynamically share variables back to the parent shell. Another way that this happens, is if you use backticks to call the function. This treats the function like an external call, and forks a new shell. This means the variable from the parent will not be updated. Eg:
func() { newval=$(($1 + 1)) echo $newval echo in func: newval ends as $newval } newval=1 echo newval in main is $newval output=`func $newval` func $newval echo output is : $output echo newval finishes in main as $newval
Write Comments!
Lastly, as mentioned in the good practices chapter, dont forget to comment your functions! While shellscripts are generally easier to read than most programming languages, you really can't beat actual human language to explain what a function is doing!