The following command line will install WebLogic
/oracle/product/11.2.0/db_1/jdk18/bin/java -jar /oracle/product/11.2.0/db_1/jdk18/fmw_12.2.1.0.0_wls.jar -silent -responseFile /ora01/software/weblogic_install_resp_file.rsp -force -loglevel finest -logfile /oracle/product/11.2.0/db_1/jdk18/bin/java/weblogic12c_install.log
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