Script to check for the number of control files in a database and create minimum two if only one is found

A script that checks for the number of controlfiles and creates two new ones if only one is found.

# 080224: Created 
# Check if there is only one controlfile present. If yes, create two new ones in the desired locations
#!/bin/bash
#cleanup old files
chkcntrfiles() {
rm -f /tmp/chk_cntrfile_location.sql
rm -f /tmp/chk_cntrfile_location.log
sqlfile=/tmp/chk_cntrfile_location.sql
logfile=/tmp/chk_cntrfile_location.log
echo "Check controlfile locations in database " $ORACLE_SID
cat << EoF > ${sqlfile}
   set lines 200 heading off feedback off verify off echo off termout off
   set pages 0
   set trimspool on
   col name format a70
   spool ${logfile}
select name
from v\$controlfile;
exit
EoF

cd /home/oracle/&&sqlplus -s -L / as sysdba @${sqlfile}
}
chkcntrfiles

if [ `cat ${logfile} | wc -l` -ge 2 ]; then
  echo "There are at least 2 controlfiles present"
else
  echo "There is only one controfile present."
  echo "Will now shutdown db and make another copy"
  echo "shutdown immediate" | sqlplus / as sysdba
  export org_contr_file=`cat ${logfile}`
  cp -v $org_contr_file /disk1/oradata/$ORACLE_SID/control01.ctl
  cp -v $org_contr_file /disk2/fra/$ORACLE_SID/control02.ctl
  echo "startup nomount" | sqlplus / as sysdba
  echo "alter system set control_files='/disk1/oradata/$ORACLE_SID/control01.ctl','/disk2/fra/$ORACLE_SID/control02.ctl' scope=spfile;" | sqlplus / as sysdba
  echo "startup mount force;" | sqlplus / as sysdba
  echo "Check new controlfile locations in database " $ORACLE_SID
  chkcntrfiles
  if [ ! `cat ${logfile} | wc -l` -ge 2 ]; then
   echo "We tried creating two new controlfiles but failed for some reason. Manual checkup required."
   exit 1
  fi
  echo "Alles OK!"
  echo "alter database open;" | sqlplus / as sysdba
fi

exit

How to change the control_files parameter used in the spfile

Mind your syntax when you are changing certain multi-value parameters directly in the spfile. Oracle will tell you that the syntax is accepted, but it will give you an error later. The following example is from an attempt to change the control_files parameter.

Won't work:

SQL> alter system set control_files='/oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl' scope=spfile;

System altered.

During mount stage, this will give you the following message in the database's alert log:

ORA-205 signalled during: ALTER DATABASE   MOUNT...
ORA-00202: control file: /oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl
ORA-27037: cannot obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Works:

SQL> alter system set control_files='/oradata/proddb01/control01.ctl','/fra/proddb01/control02.ctl' scope=spfile;

System altered.

You should enclose both values within ' ' apostroph characters.