Estimate tablespaces usage before database refresh
We have been facing missing tablespaces/inadequate space in tablespaces issue while performing database refresh. The import jobs are failed or suspended due to tablespaces issue it leads delay
In database refresh. we have developed the scripts(tablespaces_usage.sh & compare_files.pl) to work around the issue. These scripts are generate a report about missing tablespaces/inadequate space in tablespaces in target database. I hope this will help resolve our issue.
tablespaces_usage.sh:
#!/bin/bash
#######################################################################
#Programmer : ADINARAYANA
#Purpose : Estimate tablespace usages in source & target databases
########################################################################
echo "1) Source Database:"
echo "2) Target Database:"
echo "3) EXIT:"
read -p 'Please Enter the choice :' CHOICE
case $CHOICE in
1)
read -p 'Please Enter ORACLE Sid : ' sid
read -p 'Please Enter ORACLE Home: ' home
read -p 'Please Enter Schema: ' user
stty -echo
read -p 'Please Enter Schema Password: ' pass
stty echo
echo ""
export ORACLE_SID=$sid
export ORACLE_HOME=$home
file=Tablespace_$sid.log
if [ -f $file ]; then
{
/dev/null > $file 2>/dev/null
}
fi
stat_1="select tablespace_name,sum(bytes/1024/1024) from user_segments group by tablespace_name order by tablespace_name";
read_sql_stmt() {
typeset stmt=$1
typeset login=$2
echo "set feedback off verify off heading off pagesize 0
$stmt;
exit
" | $ORACLE_HOME/bin/sqlplus -s $login
}
read_sql_stmt "$stat_1" "$user/$pass" | while read var_1 var_2
do
echo "$var_1 , $var_2" >> $file
done
;;
2)
read -p 'Please Enter ORACLE Sid : ' sid
read -p 'Please Enter ORACLE Home: ' home
read -p 'Please Enter the FileName: ' filename
read -p 'Please Enter Schema: ' user
stty -echo
read -p 'Please Enter Schema Password: ' pass
stty echo
echo ""
export ORACLE_SID=$sid
export ORACLE_HOME=$home
export resultset=$user
file=Tablespace_$sid.log
if [ -f $file ]; then
{
/dev/null > $file 2>/dev/null
}
fi
read_sql_stmt() {
typeset stmt=$1
typeset login=$2
echo "set feedback off verify off heading off pagesize 0
define app_user='$resultset';
$stmt;
exit
" | $ORACLE_HOME/bin/sqlplus -s $login
}
read_sql_stmt "select b.tablespace_name ,a.free_space , b.tmax_size,b.tbs_size from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space where tablespace_name in(select distinct(TABLESPACE_NAME) from dba_segments where OWNER=upper('&app_user')) group by tablespace_name) a,(select tablespace_name, sum(bytes)/1024/1024 as tbs_size,sum(MAXBYTES)/1024/1024as tmax_size from dba_data_files where tablespace_name in(select distinct(TABLESPACE_NAME) from dba_segments where OWNER=upper('&app_user')) group by tablespace_name) b where a.tablespace_name(+)=b.tablespace_name order by tablespace_name" "$user/$pass" | while read var_1 var_2 var_3 var_4
do
echo "$var_1 , $var_2, $var_3, $var_4" >> $file
done
;;
*) exit ;;
esac
compare_files.pl:
#!/usr/bin/perl
######################################################################
#Programmer : ADINARAYANA
#Purpose : Compare Sourcefile & Target file
######################################################################
my $var_1 = 0;
my ($day,$month,$date,$hour,$year) = split /\s+/,scalar localtime;
my $output_file = 'report_tablespaces-'.$date.'.'.$month.'.log';
print "Enter the source filename: ";
my $source_file = <STDIN>;
print "Enter the target filename: ";
my $target_file = <STDIN>;
my %comp;
my %myhash;
my %atthash;
open (LOGFILE, ">$output_file") or die ("Could not open $output_file!");
open (R, $source_file) or die ("Could not open $source_file!");
while(<R>){
chomp;
my ($k,$v) = split(',');
#my ($k,$v1,$v2) = split(',');
push (@{$comp{'R'}{$k}},$v);
}
close(R);
open (P, $target_file) or die ("Could not open $target_file!");
while(<P>){
chomp;
my ($k,$v1,$v2,$v3) = split(',');
push (@{$comp{'P'}{$k}},$v1);
push (@{$myhash{$k}},$v2);
push (@{$atthash{$k}},$v3);
}
close(P);
#foreach my $Z (keys%{$comp{'P'}}) {
#printf "%s %s\n",$Z,"Key";
#printf "%s %s\n",@{$comp{'P'}{$Z}},"Value";
#}
printf LOGFILE "************MISSING TABLESPACES**************\n";
foreach my $T (keys%{$comp{'R'}}) {
if ( !exists($comp{'P'}{$T})) {
$var_1++;
printf LOGFILE "%s %g %s\n",$T, @{$comp{'R'}{$T}},"MB";
}
}
if($var_1 == 0){
printf LOGFILE "---------THERE ARE NO MISSING TABLESPACES-----------\n";
}
printf LOGFILE "*****************************************************************************************************************\n";
printf LOGFILE "TablespaceName\t\tSize(MB)\t\tFreeSpace(MB)\t\tMaxSize(MB)\t\tAdditionalSpaceRequired for Import(MB)\n";
printf LOGFILE "*****************************************************************************************************************\n";
foreach my $A (keys%{$comp{'R'}}) {
if (exists $comp{'P'}{$A}) {
foreach my $classR (@{$comp{'R'}{$A}}) {
foreach my $classP ( @{$comp{'P'}{$A}} ) {
if($classR > $classP) {
my $B = $A;
$B =~ s/\s+$//;
#printf "%s %g %s %g %s\n","Need additional",$classR-$classP,"MB space is required in $B tablespace for import.Tablespace maxsize is",@{$myhash{$A}},"MB";
printf LOGFILE "%-9s %s %-6g %s %-6g %s %-6g %s %-6g\n",$B,"\t\t",@{$atthash{$A}},"\t\t",@{$comp{'P'}{$A}},"\t\t",@{$myhash{$A}},"\t\t",$classR-$classP ;
}
}
}
}
}
close(LOGFILE);
1. Source database(BETA):
Source tablespace log:
3
. Target database(ALPHA):
Note: Run this script after target database objects(schema/tables) are dropped.
Target tablespace log:
Compare Source &target tablespace logs:
Final results log file:
If any tablespaces are missed out it will generate output something below.
To prepare this script took over a moth to test and deliver it successfully after so many testings between Source & Destination database. Happy Reading!!! 🙂
