Backup your Spfile, and Controlfile, it does not cost money and may save a lot of time.

There are several things that should be included on the install kit of RAC and ASM.
One of them is the automatic backup of  the Spfile, and Backups of the Controfile.

An invalid parameter introduced by mistake on the spfile may force us to  manually create a pfile to bring up the instance and change the parameter, in some cases this may take a while. Having a backup of the previous spfile version may help us to quickly solve the issue.

The same is valid also for the controlfile. Losing the controlfile is not that big issue if we have a script ready to recreate it, or available backups.

The following script can be run from crontab to provide protection for both issues, it create spfile and controlfile backups inside ASM and on File System at the Oracle_home/dbs directory. Makes a create control file script and moves it from udump directory to the Oracle_home/dbs directory:

-------- script start on next line -----------
#!/usr/bin/ksh
# set -x
# "backfiles"
# This script backup the controlfile and spfile to filesystem and ASM
# it requires sid as parameter
# Alejandro

if [ $# -lt 1 ]; then
clear
echo this script needs the Oracle SID as a parameter
exit
fi

# Environment Variables (change them to suit your needs)
# --------------------
export ORACLE_BASE=/oradisk/app01/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/10gDB
export ORACLE_SID=$1
export BASE_PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
export PATH=${ORACLE_HOME}/bin:${BASE_PATH}

# Script Variables (change them to suit your needs)
# ----------------
v_timest=`date +%d%m%Y%H%M`             # Time Stamp
v_bacds1='+datadg/racdbtst/backup'      # Backup destination ASM
v_bacds2=$ORACLE_HOME/dbs               # Backup destination File System
v_spname=spf_$v_timest                  # Backup name for Spfile
v_ctname=ctf_$v_timest                  # Backup name for Control File
v_ctscpt=cts_$v_timest                  # Backup name for Create Control File Script
v_orasid=$1                             # Oracle SID
v_newsid=`echo $sid | tr '[a-z]' '[A-Z]'`
# -----------------
sqlplus -s " / as sysdba" <<eof1
set pages 50000 lines 80 echo on
spool /tmp/backfiles.log
select sysdate,name from v$database
/
create pfile='/tmp/tempfile' from spfile
/
create spfile='$v_bacds1/$v_spname' from pfile='/tmp/tempfile'
/
create pfile='$ORACLE_HOME/dbs/$v_spname' from spfile
/
alter database backup controlfile to '$v_bacds1/$v_ctname'
/
alter database backup controlfile to '$ORACLE_HOME/dbs/$v_ctname'
/
spool off
exit
eof1
v_tab=v$parameter
v_uddest=`echo "select rtrim(ltrim(VALUE)) from $v_tab where name='user_dump_dest';" | sqlplus -s " / as sysdba" | grep udump`
cd $v_uddest
echo "alter database backup controlfile to trace;" | sqlplus -s " / as sysdba"
v_tracename=`ls -tr | tail -1`
mv $v_tracename $ORACLE_HOME/dbs/$v_ctscpt
# eof "backfiles"
------- script ends on previous line ----------------

Example of run:

{oracle} /oradisk/app01/oracle/scripts/av [vmractest1.partnergsm.co.il] > ./backfiles racdbtst1

SYSDATE   NAME
--------- ---------
28-JAN-07 RACDBTST

File created.
File created.
File created.
Database altered.
Database altered.
Database altered.


Check the created files on Oracle_home/dbs:

{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest1.partnergsm.co.il] > ls -ltr | grep 280120071134
-rw-r--r--    1 oracle   dba          1457 Jan 28 11:34 spf_280120071134
-rw-r-----    1 oracle   dba      15286272 Jan 28 11:34 ctf_280120071134
drwxr-xr-x    2 oracle   dba          4096 Jan 28 11:34 cts_280120071134

Note that cts_280120071134 is a directory, inside you will find the trace that contains the create controlfile script.

Check the created files on ASM:

[vmractest1.partnergsm.co.il] > asmcmd ls +datadg/racdbtst/backup
ctf_280120071134
spf_280120071134

Comments:

Isn't it easier to setup autobackup of controlfile and spfile using RMAN?

Posted by Pawel Barut on January 30, 2007 at 04:30 PM IST #

Using Rman for this purpose is a very good idea.

Posted by Alejandro Vargas on January 31, 2007 at 06:36 AM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today