Sometimes you just need a quick answer to a deceptively simple question:
How big is this Oracle database?
That question can actually mean several different things:
I originally wrote a small script for this purpose years ago. The following is an updated version that provides a much more complete picture while remaining simple enough to run from the command line.
db_overall_size.ksh#!/bin/ksh
###############################################################################
# Script Name..: db_overall_size.ksh
# Description..: Displays Oracle database storage usage by component
# Author.......:
# Original Date: 04/10/2013
# Version......: 2.0
# Modified.....: Modernized database sizing / capacity version
#
# Reports:
# - Database identification
# - SYSTEM / SYSAUX / UNDO / USERS-TOOLS / Application Data
# - Allocated, used and free datafile space
# - TEMP allocation
# - Online redo physical footprint
# - Control file physical footprint
# - Current database physical footprint
# - Datafile autoextend growth ceiling
# - FRA total, used, free and reclaimable space
# - FRA usage by file type
#
# Notes:
# "Used" datafile space is calculated as allocated space minus free extents.
#
# The physical database footprint shown here does NOT include the FRA.
# The FRA is reported separately because it can contain backups,
# archived logs, flashback logs and other recovery-related files.
###############################################################################
RUN_TS=$(date '+%Y%m%d_%H%M%S')
OUTFILE="db_size_${ORACLE_SID}_${RUN_TS}.txt"
echo "Creating database size report: ${OUTFILE}"
sqlplus -s "/ as sysdba" <<EOF
set linesize 220
set pagesize 200
set trimspool on
set tab off
set feedback off
set verify off
spool ${OUTFILE}
prompt
prompt ================================================================================
prompt DATABASE IDENTIFICATION
prompt ================================================================================
column db_name format a14
column db_unique_name format a25
column database_role format a20
column open_mode format a20
column instance_name format a16
column host_name format a35
column version format a15
select d.name db_name,
d.db_unique_name,
d.database_role,
d.open_mode,
i.instance_name,
i.host_name,
i.version
from v\$database d,
v\$instance i;
prompt
prompt ================================================================================
prompt DATAFILE SPACE BY DATABASE COMPONENT
prompt ================================================================================
column component format a25 heading "Database Component"
column alloc_gb format 999,999,990.99 heading "Allocated|GB"
column used_gb format 999,999,990.99 heading "Used|GB"
column free_gb format 999,999,990.99 heading "Free|GB"
column pct_used format 990.99 heading "Used|%"
with
df as
(
select tablespace_name,
sum(bytes) / power(1024,3) alloc_gb
from dba_data_files
group by tablespace_name
),
fs as
(
select tablespace_name,
sum(bytes) / power(1024,3) free_gb
from dba_free_space
group by tablespace_name
),
ts as
(
select d.tablespace_name,
d.alloc_gb,
d.alloc_gb - nvl(f.free_gb,0) used_gb,
nvl(f.free_gb,0) free_gb
from df d
left join fs f
on f.tablespace_name = d.tablespace_name
),
components as
(
select case
when tablespace_name = 'SYSTEM'
then 'SYSTEM'
when tablespace_name = 'SYSAUX'
then 'SYSAUX'
when tablespace_name like 'UNDO%'
then 'UNDO'
when tablespace_name in ('USERS','TOOLS')
then 'USERS / TOOLS'
else 'APPLICATION DATA'
end component,
sum(alloc_gb) alloc_gb,
sum(used_gb) used_gb,
sum(free_gb) free_gb
from ts
group by
case
when tablespace_name = 'SYSTEM'
then 'SYSTEM'
when tablespace_name = 'SYSAUX'
then 'SYSAUX'
when tablespace_name like 'UNDO%'
then 'UNDO'
when tablespace_name in ('USERS','TOOLS')
then 'USERS / TOOLS'
else 'APPLICATION DATA'
end
)
select component,
alloc_gb,
used_gb,
free_gb,
case
when alloc_gb > 0
then used_gb / alloc_gb * 100
end pct_used
from components
order by
case component
when 'SYSTEM' then 1
when 'SYSAUX' then 2
when 'UNDO' then 3
when 'USERS / TOOLS' then 4
when 'APPLICATION DATA' then 5
else 6
end;
prompt
prompt ================================================================================
prompt OVERALL DATAFILE UTILIZATION
prompt ================================================================================
column current_gb format 999,999,990.99 heading "Current|Allocated GB"
column used_gb format 999,999,990.99 heading "Used|GB"
column free_gb format 999,999,990.99 heading "Free|GB"
column max_gb format 999,999,990.99 heading "Autoextend|Maximum GB"
column growth_gb format 999,999,990.99 heading "Potential|Growth GB"
column pct_used format 990.99 heading "Used|%"
with
df as
(
select sum(bytes) current_bytes,
sum(
case
when autoextensible = 'YES'
then greatest(bytes,maxbytes)
else bytes
end
) max_bytes
from dba_data_files
),
fs as
(
select nvl(sum(bytes),0) free_bytes
from dba_free_space
)
select df.current_bytes / power(1024,3) current_gb,
(df.current_bytes-fs.free_bytes) / power(1024,3) used_gb,
fs.free_bytes / power(1024,3) free_gb,
df.max_bytes / power(1024,3) max_gb,
(df.max_bytes-df.current_bytes) / power(1024,3) growth_gb,
case
when df.current_bytes > 0
then (df.current_bytes-fs.free_bytes) /
df.current_bytes * 100
end pct_used
from df, fs;
prompt
prompt ================================================================================
prompt PHYSICAL DATABASE FOOTPRINT
prompt ================================================================================
column component format a30
column size_gb format 999,999,990.99 heading "Size|GB"
with
datafiles as
(
select nvl(sum(bytes),0) bytes
from dba_data_files
),
tempfiles as
(
select nvl(sum(bytes),0) bytes
from dba_temp_files
),
redo as
(
/*
* MEMBERS is included so multiplexed redo members are represented
* in the physical footprint.
*/
select nvl(sum(bytes * members),0) bytes
from v\$log
),
controlfiles as
(
/*
* V\$CONTROLFILE contains each control file copy.
*/
select nvl(sum(block_size * file_size_blks),0) bytes
from v\$controlfile
)
select 'Datafiles' component,
datafiles.bytes / power(1024,3) size_gb
from datafiles
union all
select 'Tempfiles',
tempfiles.bytes / power(1024,3)
from tempfiles
union all
select 'Online Redo - Physical',
redo.bytes / power(1024,3)
from redo
union all
select 'Control Files - Physical',
controlfiles.bytes / power(1024,3)
from controlfiles
union all
select 'TOTAL DB FOOTPRINT',
(datafiles.bytes +
tempfiles.bytes +
redo.bytes +
controlfiles.bytes) / power(1024,3)
from datafiles,
tempfiles,
redo,
controlfiles;
prompt
prompt ================================================================================
prompt TEMPORARY TABLESPACE
prompt ================================================================================
column temp_gb format 999,999,990.99 heading "TEMP|Total GB"
column temp_used_gb format 999,999,990.99 heading "TEMP|Used GB"
column temp_free_gb format 999,999,990.99 heading "TEMP|Free GB"
column pct_used format 990.99 heading "Used|%"
select sum(tablespace_size) / power(1024,3) temp_gb,
sum(tablespace_size-free_space) / power(1024,3) temp_used_gb,
sum(free_space) / power(1024,3) temp_free_gb,
case
when sum(tablespace_size) > 0
then sum(tablespace_size-free_space) /
sum(tablespace_size) * 100
end pct_used
from dba_temp_free_space;
prompt
prompt ================================================================================
prompt FAST RECOVERY AREA
prompt ================================================================================
column fra_name format a60 heading "FRA Location"
column fra_total_gb format 999,999,990.99 heading "FRA|Limit GB"
column fra_used_gb format 999,999,990.99 heading "Used|GB"
column fra_free_gb format 999,999,990.99 heading "Available|GB"
column reclaimable_gb format 999,999,990.99 heading "Reclaimable|GB"
column pct_used format 990.99 heading "Used|%"
select name fra_name,
space_limit / power(1024,3) fra_total_gb,
space_used / power(1024,3) fra_used_gb,
greatest(space_limit-space_used,0) /
power(1024,3) fra_free_gb,
space_reclaimable / power(1024,3) reclaimable_gb,
case
when space_limit > 0
then space_used / space_limit * 100
end pct_used
from v\$recovery_file_dest;
prompt
prompt ================================================================================
prompt FRA CONTENT BY FILE TYPE
prompt ================================================================================
column file_type format a28
column pct_used format 990.99 heading "FRA|Used %"
column pct_reclaim format 990.99 heading "FRA|Reclaimable %"
column number_of_files format 999,999 heading "Files"
select file_type,
percent_space_used pct_used,
percent_space_reclaimable pct_reclaim,
number_of_files
from v\$recovery_area_usage
where percent_space_used > 0
or percent_space_reclaimable > 0
or number_of_files > 0
order by percent_space_used desc;
prompt
prompt ================================================================================
prompt REPORT COMPLETE
prompt ================================================================================
select 'Generated: ' ||
to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
from dual;
spool off
exit
EOF
echo
echo "Database size report complete."
echo "Report: ${OUTFILE}"
The original script answered the basic sizing question, but this version separates several concepts that are often mistakenly treated as the same number.
Allocated datafile space tells us how large the current Oracle datafiles are.
Used datafile space estimates how much of that allocation is occupied by Oracle segments by subtracting available free extents.
Maximum autoextend size is especially important for capacity planning. A 4 TB database may currently occupy 4 TB while its datafiles are configured to grow to 9 TB. The second number is often more important when determining whether the underlying ASM disk group has sufficient capacity.
Physical database footprint includes datafiles, tempfiles, redo members, and control-file copies. The Fast Recovery Area is intentionally kept separate because adding the FRA to the database size can double-count storage associated with archived logs, backups, flashback logs, and recovery-related files.
The script also shows FRA reclaimable space. This can be extremely useful when investigating storage pressure because "FRA used" does not necessarily mean all of that space must remain permanently occupied.
With one report a DBA can quickly determine:
That makes the script useful not only for troubleshooting, but also for capacity planning, database migrations, Exadata sizing, cloud migrations, storage forecasting, and environment comparisons.
A single database-size report is useful.
A history of those reports is considerably more valuable.
If the results are collected periodically into a central repository, it becomes possible to calculate:
At that point this little DBA utility becomes the foundation of a simple database capacity-management system rather than merely a one-time sizing script.
#!/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;
/