Category Archives: Scripts

Header for MRCENV file


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

 

.kshrc and .bashrc for persistent settings

In order to get set -o vi
This should be set at the .kshrc or .bashrc level

# .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

</pre>
# .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
<pre>

gi_common.ksh


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

}

space.ksh



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



 

QA Common Library

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

}

tablespace.sql


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
);

table_degrees.sql


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;

check_ac.sql adv compression


&nbsp;

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;

&nbsp;

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;

&nbsp;

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

Cleanup Script


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

Install SQLT Script


#!/bin/ksh
#
#   Script to install SQLT
#
# -- usage: /oracle/admin/scripts/install_sqlt.ksh <DBNAME>
#
####################################################################################

DBNAME=$1
APP_ID=$2


log=/oracle/admin/scripts/install_sqlt_$DBNAME.log
mkdir -p /ora01/SQLT/$DBNAME
cp /oracle/admin/scripts/sqlt.zip  /ora01/SQLT/$DBNAME

cd /ora01/SQLT/$DBNAME
unzip sqlt.zip
cd /ora01/SQLT/$DBNAME/sqlt/install

sqlplus -s "/ as sysdba" <<EOF
@sqdrop.sql
exit
EOF

sqlplus -s "/ as sysdba" <<EOF
@sqcinternal.sql
exit
EOF


exit