Experiments from the Field..Based on True Stories

Preface

While cloning, sometimes one may have to compare two database initialization parameter files (init.oras). The parameters may be mentioned without order and even multiple times. This can cause manual difficulty in comparing two init.ora files, which can lead to mistakes.

Scripting to the rescue...

I made this quick script (which can be refined further) to compare each init.ora parameter side by side. Feel free to extend it and add more validation checks etc.

This script can be useful when you have a requirement that the memory area sizing for two instances need to be the same, except for the instance specific changes, of course.

$ more compare_two_initoras.sh
#!/bin/ksh

file1=$1
file2=$2
for parameter in `cat $file1 | grep = | awk '{print $1}' | grep -v '#'`
do
 value1=`grep ^$parameter $file1 | awk 'BEGIN{s1=""} { for (i=3;i<=NF;i++) { if ($i ~ /#/) break; else s1=sprintf(s1"%s ",$i); }
 } END{print s1;}'`
 value2=`grep ^$parameter $file2 | awk 'BEGIN{s2=""} { for (i=3;i<=NF;i++) { if ($i ~ /#/) break; else s2=sprintf(s2"%s ",$i); }
 } END{print s2;}'`
 if [ "$value1" != "$value2" ];
 then
  echo $parameter"="$value1 " - " $file1
  echo $parameter"="$value2 " - " $file2
  echo ---------------------------------------------------
 fi
done

A sample run..

sandbox:qa> ./compare_two_initoras.sh initprod.ora.14thjan initqa.ora

log_archive_start=true  -  initprod.ora.14thjan
log_archive_start=  -  initqa.ora
---------------------------------------------------
nls_length_semantics=byte  -  initprod.ora.14thjan
nls_length_semantics=BYTE  -  initqa.ora
---------------------------------------------------
utl_file_dir='/ORACLE/apps/prod/temp','/var/tmp'  -  initprod.ora.14thjan
utl_file_dir='/ORACLE/apps/qa/appl/temp','/var/tmp'  -  initqa.ora
---------------------------------------------------
control_files=(/ORACLE/data/datafiles/prod/system/control1.ctl,  -  initprod.ora.14thjan
control_files=(/ORACLE/data/datafiles/qa/system/control1.ctl,  -  initqa.ora
---------------------------------------------------
db_name=prod  -  initprod.ora.14thjan
db_name=qa  -  initqa.ora
---------------------------------------------------
db_files=3000  -  initprod.ora.14thjan
db_files=3000  -  initqa.ora
---------------------------------------------------
background_dump_dest=/ORACLE/data/datafiles/prod/admin/bdump  -  initprod.ora.14thjan
background_dump_dest=/ORACLE/data/datafiles/qa/admin/bdump  -  initqa.ora
---------------------------------------------------
core_dump_dest=/ORACLE/data/datafiles/prod/admin/cdump  -  initprod.ora.14thjan
core_dump_dest=/ORACLE/data/datafiles/qa/admin/cdump  -  initqa.ora
---------------------------------------------------
log_archive_dest=/ORACLE/data/datafiles/prod/arch/arch  -  initprod.ora.14thjan
log_archive_dest=/ORACLE/data/datafiles/qa/arch  -  initqa.ora
---------------------------------------------------

Recommendation

One recommendation that I would surely mention is that it would be a good idea to run the same script with the arguments reversed i.e. if you run the script with arguments initqa.ora and initprod.ora, then you should also run it with initprod.ora and initqa.ora, in that order.