#!/bin/bash
##################################################################################
# Script file name ..: menu_multi_template.sh
# Description........: script file for MRC Consulting LLC
# : this is a menu file for root stored in a central location
# : this file is called from ./mr.sh located in the
# Customer...........: MRC Consulting
##################################################################################
# Author.......: Michael Culp
# Contact......: Michael Culp MRC Consulting LLC
# Phone/text...: 864.248.6284 or 864.630.2118
# email........: mike.culp@gmail.com mculp@it-remote.com
# Created......: 10/16/2017
# Last modified: 10/30/2019
# Modified By..: Michael Culp
# Comments.....: Add an ELSEIF section as seen below for the hostname
# : you would like to add
# : in the .bash_profile source the mrcenv_root file
# : even if there is no share use this method and when a share location
# : becomes available you can easily switch
# : then simply . ./mrcenv in order to source it or set it here
# : When this is sourced it makes a copy of itself locally to /root
# : If the shared location is not available it can use the local copy
# : we can add code to check share drive and if not available simply use correct file
# Script type..: root environment source file
# Dependency...: none
# : mrcenv_root_001
# :
# How to implement:
# : change the ./bash_profile to source this file
# : be sure to change the else if to accommodate the hostname
# : we set the env variables based on hostname
##################################################################################
# This originally started in the local server /root
# directory, to provide more reuse it is now running
# in /oracle/admin/scripts/menu
#####################################################
mrc_scripts()
###################################################
# Check to see if MRC scripts are installed
###################################################
{
if [ ! -d "/oracle/admin/scripts" ]; then
echo "mrc scripts are not installed ..."
export MRC_SCR=N
# exit 1
else
echo "mrc scripts are installed ......."
export MRC_SCR=Y
fi
echo "MRC Scripts installed?...: "$MRC_SCR
}
############################################################################# # Script name..: mrcenv # Description..: environment file for mrc consulting llc # : use this file so as not to impact / change # : customer .profile or .bash_profile # Customer.....: <customer name> ############################################################################# # Author.......: Michael Culp # Contact......: # Phone/text...: 864.999.9999 or 864.999.9999 # email........: mike.culp@gmail.com # Created......: 10/16/2007 # Last modified: 10/17/2019 # Modified By..: Michael Culp # Comments.....: Change the ORACLE_HOME, ORACLE_BASE, and GGS_HOME before using # : then simply . ./mrcenv in order to source it # Script type..: environment source file # Dependency...: none ################################################################################ # Revision History: # # REV DATE BY DESCRIPTION # --- --------- -------------- ------------------------------------------ # 1.0 09/17/2019 Michael Culp Initial version of this # Change the values of vars for this customer # 1.1 10/17/2019 Michael Culp General clean-up ################################################################################
In order to get set -o vi
This should be set at the .kshrc or .bashrc level
# M Culp # .kshrc # Source global definitions if [ -f /etc/kshrc ]; then . /etc/kshrc fi # use emacs editing mode by default # set -o emacs set -o vi # User specific aliases and functions
For the bash folks here is the .bashrc file
# .bashrc # User specific aliases and functions alias rm='rm -i' alias cp='cp -i' alias mv='mv -i' # Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi set -o vi
##########################################################
#
# MCulp 06/2019
# gi_common.ksh
# Author: Michael Culp mike.culp@gmail.com
# : 864-630-2118
#
##########################################################
gi_init() # Set parameters here for other functions to use
{
clear
echo
echo "Test..."
echo
GRID_HOME=/manh/oracle_crs/product/18.1.0/crs_1a
}
gi_net_test()
{
scnm=$1
clear
echo
echo "Testing "$scnm" 6 times"
echo
# nslookup fadmanh18-scan
nslookup $scnm
nslookup $scnm
nslookup $scnm
nslookup $scnm
nslookup $scnm
nslookup $scnm
#ping 10.0.0.22
#ping 10.0.0.40
#ping 10.0.0.61
}
gi_cr_dirs()
###########################################
# Execute as root
###########################################
{
echo
echo "Creating directories applicable for RAC 18c..."
echo
# Create grid dirs
# Inventory location
mkdir -p /manh/oraInventory
# mkdir -p /u01/app/18.0.0/grid
mkdir -p /manh/18.0.0/grid
# mkdir -p /u01/app/grid
mkdir -p /manh/grid
# mkdir -p /u01/app/oracle
mkdir -p /manh/oracle
# This for the GRID software
mkdir -p /manh/oracle_crs/product/18.1.0/crs_1a
mkdir -p /manh/oracle_crs/product/18.1.0/crs_1b
# This for the DB software
mkdir -p /manh/oracle/product/18.4.0/db_1a
mkdir -p /manh/oracle/product/18.4.0/db_1b
# chown -R grid:oinstall /u01
chown -R grid:oinstall /manh
# chown oracle:oinstall /u01/app/oracle
chown -R oracle:oinstall /manh/oracle
# chmod -R 775 /u01
chmod -R 775 /manh
echo
echo "Directories created...."
echo
}
gi_cpy_rsp_fl()
{
echo
echo "test cp..."
echo
cp /ora01/Oracle_SW/fd_response_files/fd_fadmanh_180_grid_setup.rsp /manh/oracle_crs/product/18.1.0/crs_1a/install/response
chown grid:oinstall /manh/oracle_crs/product/18.1.0/crs_1a/install/response/fd_fadmanh_180_grid_setup.rsp
}
gi_inst()
##########################################################
# Install GRID via response file
##########################################################
{
GRID_HOME=/oracle_crs/
# Copy response file to Grid installation folder
cp /ora01/Oracle_SW/fd_response_files/fd_fadmanh_180_grid_setup.rsp /manh/oracle_crs/product/18.1.0/crs_1a/install/response
chown grid:oinstall /manh/oracle_crs/product/18.1.0/crs_1a/install/response/fd_fadmanh_180_grid_setup.rsp
# Set GRID_HOME first in gi_init or
# here before executing
cd $GRID_HOME
unset ORACLE_BASE
cd /manh/oracle_crs/product/18.1.0/crs_1a
$GRID_HOME/gridSetup.sh -silent -responseFile /ora01/Oracle_SW/fd_response_files/fd_fadmanh_180_grid_setup.rsp
# Install Grid software
$GRID_HOME/gridSetup.sh -silent -responseFile /manh/oracle_crs/product/18.1.0/crs_1a/install/response/fd_fadmanh_180_grid_setup.rsp
}
gi_extr_sw_fn()
#####################################################
# unzip the distribution files
#####################################################
{
# Could do this next in one step
# gi_cr_dirs
unzip -u -o /ora01/Oracle_SW/gi180/LINUX.X64_180000_grid_home.zip -d /manh/oracle_crs/product/18.1.0/crs_1a
chown -R grid:oinstall /manh/oracle_crs/product/18.1.0/crs_1a
}
gi_deinst()
############################################################
# Login as grid
############################################################
{
cd /manh/oracle_crs/product/18.1.0/crs_1a/deinstall
./deinstall
rm -fr /manh/oraInventory
rm /etc/oraInst.loc
}
gi_symlink()
############################################################
# Create symlinks to bounce between versions
# use crs_1 to install
############################################################
{
ln -s /manh/oracle_crs/product/18.1.0/crs_1a /manh/oracle_crs/product/18.1.0/crs_1
ln -s /manh/oracle_crs/product/18.1.0/crs_1a /manh/oracle_crs/product/18.1.0/crs_1
tar -cvf
}
gi_cv_hwos()
###############################################
# Cluster Verify Network Verification
# su to grid user prior to run
# Can run as the oracle user
###############################################
{
#
ndlist=$1
echo
echo "Cluster verify output..."
echo "Nodes : "$ndlist
echo
cd /ora01/Oracle_SW/gi180
# ./runcluvfy.sh stage -post hwos -n fd-dcomsdev1t,fd-dcomsdev2t -verbose > hwos.log
# ./runcluvfy.sh stage -post hwos -n fd-dcomsdev1t,fd-dcomsdev2t -verbose
./runcluvfy.sh stage -post hwos -n $ndlist -verbose
}
gi_cv_crsinst()
{
#
ndlist=$1
echo
echo "Cluster verify output...pre crsinst...."
echo "Nodes : "$ndlist
echo
cd /ora01/Oracle_SW/gi180
# ./runcluvfy stage -pre crsinst -n fd-dcomsdev1t,fd-dcomsdev2t -verbose
./runcluvfy.sh stage -pre crsinst -n $ndlist -verbose
}
#!/bin/ksh
#############################################################################
# #
# FILENAME: space.ksh [ORACLE_SID] #
# #
# Purpose: Generate a space report for an Oracle instance. #
# #
# This report will work for Versions 8i, 9i,10g,11g,12c of Oracle. #
# #
# Parameters: Passing the ORACLE_SID is optional. If left off, it will #
# run the space report for the currently-defined SID, otherwise #
# it will source in the environment for the argument passed. #
# #
# *** DO NOT HAND EDIT THIS FILE *** #
# *** IF YOU NEED A CHANGE TO THIS FILE, CONTACT INFRASTRUCTURE *** #
#############################################################################
# #
# Revision History: #
# #
# REV DATE BY DESCRIPTION #
# --- ------ -------------- ------------------------------------------- #
# #
REV=1.2 #
#############################################################################
#############################################################################################################################################################################################
initialize ()
{
KEEP=30 # Number of log files to keep online
LOGDATE=$(date '+%m%d%Y_%H%M%S')
D=$(date '+%Y%m%d') # Date extension, No Minutes
DAY=$(date '+%m%d%Y_%H%M') # Date extension
OUTPUT=$DBA/output/$DB_NAME # Output directory
LOG=$OUTPUT/space_$LOGDATE.log # Set up log file
if [[ ! -d $OUTPUT ]] ; then # Test if standard output dir exists
check_error 10
fi
if [[ -s $LOG ]] ; then # Log file exists
/bin/rm -f $LOG # Clean up
fi
touch $LOG # Create the file
chmod 700 $LOG # Lock down permissions
}
# ----------------------------------------------------------------------------
check_newer_version()
{
# Check if Newer Version exists
if [[ ${STD:-0} = 0 ]] ; then # If STD is not set
check_error 20
fi
CHECK_NEWER_VERSION=$STD/check_newer_version.ksh
if [[ -s $CHECK_NEWER_VERSION ]] ; then
$CHECK_NEWER_VERSION $1 # Check if newer file exists
else
check_error 30
fi
}
# ---------------------------------------------------------------------------
check_error()
{
MESSAGE=$1
print " "
case $MESSAGE in
5) # Note: cannot tee to log as log has not been created yet
print "\nError: Invalid number of parameters."
print " Usage: ddl_extract.ksh <SID>" ;;
6) # Note: cannot tee to log as log has not been created yet
print "\nError: Oracle environment is not set."
print "Possibly due to invalid ORACLE_SID argument passed." ;;
10) # Note: cannot tee to log as log has not been created yet
print "\nDirectory does not exist: $OUTPUT"
print "Error: Oracle standard environment is not set." ;;
20) # Note: cannot tee to log as log has not been created yet
print "Environment variable STD... NOT defined."
print "Please verify Oracle standard environment" ;;
30) # Note: cannot tee to log as log has not been created yet
print "Missing standard script - $CHECK_NEWER_VERSION"
print "Please configure the environment using the Oracle standards" ;;
40) print "\nError: Could not execute sqlplus" | tee -a $LOG ;;
50) print "\nError: The file $STD/space.sql does not exist." | tee -a $LOG ;;
85) print "\nThe environment variable ORA_VER is not set" | tee -a $LOG
print "Please verify Oracle standard environment" | tee -a $LOG ;;
90) print "\nThe database ($ORACLE_SID) is not running." | tee -a $LOG ;;
*) print "\nError: Unknown error code passed." | tee -a $LOG ;;
esac
print "\nExiting..."
exit 1
}
#------------------------------------------------------------------------------
check_db()
{
# The database will be checked to see if it is running.
CHK_DB="$(ps -ef | grep -w ora_smon_$ORACLE_SID | grep -v grep)"
if [ "NULL$CHK_DB" != "NULL" ]; then # Value of CHK_DB is set
print "\nThe database ($ORACLE_SID) is up and running.\n" | tee -a $LOG
else
check_error 90
fi
}
#-----------------------------------------------------------------------------
get_db_type()
{
# Get DB type i.e. CDB or a non-CDB
if [[ "$ORA_VER" == 12* ]]; then
OUT=`$ORACLE_HOME/bin/sqlplus -s / as sysdba <<-EOF
set time off veri off head off feed off
select 'CDB='||cdb from v\\$database;
EOF
`
CDB=$(print $OUT | grep CDB= | cut -d= -f2)
fi
}
#------------------------------------------------------------------------------
execute_space()
{
# check of $ORA_VER is set
if [[ "NULL$ORA_VER" = "NULL" ]] ; then
check_error 85
fi
ORAVERNO=$(print $ORA_VER | awk -F. '{print $1$2}')
case $ORAVERNO in
81|90|91|92|101|102|111|112|121|122) # Oracle 8i, 9i, 10gR1, 10gR2, 11gR2, 12cR1, 12cR2
# check if space sql script exists
if [[ ! -s $STD/space.sql ]] ; then # Check if file exists
check_error 50
fi
print "Running Space Report...\n"
CDB=NO
get_db_type
if [[ "$CDB" == "YES" ]]; then
sqlplus -s "/ as sysdba" <<!! | tee -a $LOG
@$STD/cdb_space.sql
!!
else
sqlplus -s "/ as sysdba" <<!! | tee -a $LOG
@$STD/space.sql
!!
fi
SQL_STATUS=$?
if [[ $SQL_STATUS -eq 1 ]] ; then
check_error 40
fi ;;
*)
print "\nSpace Report only supports Oracle 8i or higher\n" | tee -a $LOG
esac
}
# ------------------------------[ Main ]-------------------------------------
# ************
# Main Program
# ************
print "\nspace.ksh Rev. $REV\n"
RETCODE=0
case $1 in
"") # Assume that current ORACLE_SID is the one that we want
# to do our space report on
print " " > /dev/null ;;
*) # Read in environment
export NEW_ORACLE_SID=$1; . $HOME/.profile > /dev/null 2>&1 ;;
esac
if [ ! -d $ORACLE_HOME ] ; then
check_error 6
fi
check_newer_version $0 # Check for newer version of this file
initialize $1 $2 $3 $4 $5 $6 $7 $8 $9
print "\nStarting at `date`" | tee -a $LOG
check_db # Check that database is up
execute_space
print "\nDone at `date`" | tee -a $LOG
print "\nLog is: $LOG"
# Purge file logs
$STD/purge_files.ksh $OUTPUT space $KEEP > /dev/null 2>&1
exit $RETCODE
#!/bin/ksh
############################################################################
# Script Name..: qa_common.ksh
# Description..:
# Author.......: Michael Culp
# Date.........: 04/20/2013
# Version......: .08
# Modified By..: Michael Culp
# Date Modified: 11/02/2015
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
# :
# Functions : next_menu
# : get_db_details - gets information from the v$database table
# : creates variables to use in this script
# Dev Notes : function to update the cluster and add clusters from here
# : will be forthcoming, should be in the QA menu
#---------------------------------------------------------------------------
# Features 2 add :
# This script need to check to see if the TNS entries are there for the APEX database
# Add TNS names entries for the BRAIN1D database if not found
# Also create a .env file dbadmin_apex.conf
############################################################################
# This is the menuing system for the DBA general stuff
# automate a lot of the DBA tasks
# Michael Culp 2010
# Make sure the EOF end is aligned on the 1st column
# If you want the sys password used, create the .syspasswd
############################################################################
# Next line uncomment for debugging purposes only
# set -xv
# Look for SQL Plus
############################################################################
qa_init()
{
echo
echo "QA Init function......"
echo
}
qa_dbrd()
{
echo
echo "QA Dashboard function......"
echo
}
qa_sho_cmd()
{
echo
echo "QA Show commands and aliases....."
echo
}
set head off pagesize 0 linesize 500
(select dbname from v$database) || ',' || tablespace_name || ',' || round(current_usage_gb) space_used_gb || ',' || round(current_free_gb) || ',' || round(maxgb)
from
(
with tbsname as
(
select tablespace_name from dba_tablespaces
),
auto_ex_no as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name
, sum(bytes) bytes
from dba_data_files a
where autoextensible='NO'
group by tablespace_name
)
),
auto_ex_yes as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(maxbytes) bytes
from dba_data_files a
where autoextensible='YES'
group by tablespace_name
)
),
auto_ex_temp_no as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(bytes) bytes
from dba_temp_files a
where autoextensible='NO'
group by tablespace_name
)
),
auto_ex_temp_yes as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(maxbytes) bytes
from dba_temp_files a
where autoextensible='YES'
group by tablespace_name
)
)
,
real_tablespac_usage as
(
select tablespace_name,
sum(bytes) bytes
from dba_segments
group by tablespace_name
)
select
tbs.tablespace_name,
case
when tbs.tablespace_name='TEMP' THEN
((round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0),3)))/1073741824
when tbs.tablespace_name='SYSTEM' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
when tbs.tablespace_name='SYSAUX' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
when tbs.tablespace_name like 'UNDO%' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
ELSE
(round((coalesce(an.maxbytes,0)+coalesce(ay.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0)-coalesce(tu.bytes,0)),3))/1073741824
END AS current_usage_gb,
(round((coalesce(tu.bytes,0)),3))/1073741824 current_free_gb,
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0),3))/1073741824 maxgb
from tbsname tbs
full join auto_ex_yes ay
on tbs.tablespace_name=ay.tablespace_name
full join auto_ex_no an
on tbs.tablespace_name=an.tablespace_name
full join auto_ex_temp_no ant
on tbs.tablespace_name=ant.tablespace_name
full join auto_ex_temp_yes ayt
on tbs.tablespace_name=ayt.tablespace_name
full join real_tablespac_usage tu
on tbs.tablespace_name=tu.tablespace_name
);
set linesize 200 col owner for a39 col table_name for a39 select (select db_unique_name from v$database) dbname,owner, table_name, degree from dba_tables where degree > 1 and owner not in ( 'APPQOSSYS', 'DBA_RO','DBSNMP','DIP', 'FOGLIGHT5X','FOGLIGHT5', 'GEMS_READ','ORACLE','ORACLE_OCM', 'OUTLN','SQLTXADMIN','SQLTXPLAIN','SYS', 'SYSTEM','WMSYS','TRCANLZR','PERFSTAT', 'SITEADM','QUEST','INETDBA','ORACHK_CM','FLOW_FILES','APEX_040200' ) order by owner;
set head off
set feedback off
set serveroutput on
whenever sqlerror exit;
set termout on
set scan off
set linesize 128
declare
v_hostname sys.v$instance.host_name%TYPE;
v_dbname sys.v$database.db_unique_name%TYPE;
v_db_role sys.v$database.database_role%TYPE;
ex_custom exception;
rowcnt number := 0;
v_message varchar2(50);
v_installed number := 0;
v_used number := 0;
v_ac_cnt number := 0;
sql_stmt1 varchar2(1000) := 'WITH TABLE_COMPRESSION as (' ||
' SELECT OWNER' ||
' FROM DBA_TABLES' ||
' WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
' UNION ALL' ||
' SELECT TABLE_OWNER AS OWNER' ||
' FROM DBA_TAB_PARTITIONS' ||
' WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
' UNION ALL' ||
' SELECT TABLE_OWNER AS OWNER' ||
' FROM DBA_TAB_SUBPARTITIONS' ||
' WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
' )' ||
' SELECT' ||
' (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
' COALESCE((SELECT COUNT(*) FROM TABLE_COMPRESSION WHERE OWNER NOT IN (''SYSMAN'')),0) as Used' ||
' FROM DUAL';
-- AdvancedCompression_ByLogArchiveCompression
sql_stmt2 varchar2(1000) := 'WITH LOGARCHIVE_COMPRESSION as (' ||
' SELECT NAME' ||
' FROM V$PARAMETER' ||
' WHERE UPPER(name) LIKE ''%LOG_ARCHIVE_DEST%'' AND UPPER(value) LIKE ''%COMPRESSION=ENABLE%''' ||
' )' ||
' SELECT' ||
' (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
' COALESCE((SELECT COUNT(*) FROM LOGARCHIVE_COMPRESSION),0) as Used' ||
' FROM DUAL';
-- advancedCompression_ByFeatureName11gR2
sql_stmt3 varchar2(1000) := 'WITH OPTION_DATA as (' ||
' SELECT ' ||
' DECODE(CURRENTLY_USED,''TRUE'',1,0) as USED' ||
' FROM DBA_FEATURE_USAGE_STATISTICS' ||
' WHERE NAME IN (''HeapCompression'', ''Backup ZLIB Compression'',' ||
' ''Backup HIGH Compression'', ''Backup LOW Compression'', ''Backup MEDIUM Compression'',' ||
' ''SecureFile Compression (user)'', ''SecureFile Deduplication (user)'')' ||
' )' ||
' SELECT ' ||
' (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
' COALESCE((SELECT SUM(USED) FROM OPTION_DATA),0) as Used' ||
' FROM DUAL';
--AdvancedCompression_ByFeatureDataPumpExport 11.2
sql_stmt4 varchar2(1000) := 'WITH OPTION_DATA as ( ' ||
' SELECT ' ||
' 1 as USED' ||
' FROM' ||
' DBA_FEATURE_USAGE_STATISTICS' ||
' WHERE name = ''Oracle Utility Datapump (Export)''' ||
' AND FEATURE_INFO IS NOT NULL' ||
' AND REGEXP_LIKE(lower(to_char(FEATURE_INFO)), ''compression used: [1-9]\d* times'')' ||
' )' ||
' SELECT ' ||
' (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
' COALESCE((SELECT SUM(USED) FROM OPTION_DATA),0) as Used' ||
' FROM DUAL';
BEGIN
select host_name into v_hostname from v$instance;
select db_unique_name into v_dbname from v$database;
select decode(database_role, 'PRIMARY', 'P', 'S') into v_db_role from v$database ;
if v_db_role <> 'P' then
raise ex_custom;
end if;
EXECUTE IMMEDIATE sql_stmt1 INTO v_installed,v_used;
if v_used > 0 then
v_message := v_message || 'TableCompression';
v_ac_cnt := v_ac_cnt+1;
end if;
-- AdvancedCompression_ByLogArchiveCompression
EXECUTE IMMEDIATE sql_stmt2 INTO v_installed,v_used;
if v_used > 0 then
v_message := v_message || 'LogArchiveCompression';
v_ac_cnt := v_ac_cnt+1;
end if;
EXECUTE IMMEDIATE sql_stmt3 INTO v_installed,v_used;
if v_used > 0 then
v_message := v_message || 'BackupCompression';
v_ac_cnt := v_ac_cnt+1;
end if;
EXECUTE IMMEDIATE sql_stmt4 INTO v_installed,v_used;
if v_used > 0 then
v_message := v_message || 'DataPumpCompression';
v_ac_cnt := v_ac_cnt+1;
end if;
if v_ac_cnt > 0 then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || v_message ||
':' || v_used);
else
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'AdvancedCompression_Not_Used' ||
':' || v_used);
end if;
exception
when ex_custom then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'Standby_Database' ||
':' || 'Standby_Database');
when NO_DATA_FOUND then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'No_AC' ||
':' || 'No_AC');
end;
/
#!/bin/ksh ############################################################################ # Script Name..: cleanup_audit_files.ksh # Description..: Script to delete .aud and .xml logs older than 1 day. # Author.......: # Date.........: 02/06/2014 # Version......: # Modified By..: # Date Modified: # Comments.....: # Schema owner.: # alter session set current # Login User...: # Run Order....: # Dependent on.: # Script type..: # ############################################################################ date # Whatever the CRS directory is find /oracle_crs/product/11.2.0/crs_2/rdbms/audit/ -name "*.aud" -mtime +1 -type f -print -delete find /oracle/admin/*/adump/ -name "*.aud" -mtime +1 -type f -print -delete find /oracle/admin/*/adump/ -name "*.xml" -mtime +1 -type f -print -delete find /ora01/adump/ -name "*.aud" -mtime +1 -type f -print -delete find /ora01/adump/ -name "*.xml" -mtime +1 -type f -print -delete find /ora01/audit/ -name "*.aud" -mtime +1 -type f -print -delete find /ora01/audit/ -name "*.xml" -mtime +1 -type f -print -delete find /ora01/audit_maint/ -name "*.aud" -mtime +1 -type f -print -delete find /ora01/audit_maint/ -name "*.xml" -mtime +1 -type f -print -delete find /ora01/audit_test/ -name "*.aud" -mtime +1 -type f -print -delete find /ora01/audit_test/ -name "*.xml" -mtime +1 -type f -print -delete find /oracle_crs/product/11.2.0/crs_2/rdbms/audit/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete find /oracle/admin/*/adump/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete find /ora01/adump/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete find /ora01/audit/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete find /ora01/audit_maint/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete find /ora01/audit_test/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete date