Sunday, November 5

Oracle controlfile backup script

Wrote a small script to get a "usable" copy of the "CREATE CONTROLFILE" statement one can backup to trace. In a heavily used database it can be quite tricky to find the control file backup without actually "looking for it", in the sense of actually query the database for your thread number.
The script will dump the controlfile to trace and copy it to the local directory with the file name control_INSTANCENAME_TIMESTAMP.sql, should be fairly simple to integrate the script in to a generic backup process.
#!/bin/bash
ORACLE_HOME=/u01/oracle/10g
ORACLE_SID=peggy
PATH="$PATH:$ORACLE_HOME/bin"
export PATH ORACLE_HOME ORACLE_SID

sqlplus -S / as sysdba <<EOF >/dev/null
set echo off feedback off verify off pages 0
column TRCLOC new_value TRCFILE
column TRCDATE new_value FILEDATE
column INSTNAME new_value INSTANCE
select to_char(sysdate, 'YYYYMMDD_HH24MISS') TRCDATE
from dual;
select instance INSTNAME
from v\$thread;
alter database backup controlfile to trace;
select
c.value || '/' ||
instance || '_ora_' ||
ltrim(to_char(a.spid,'fm99999')) ||
'.trc' TRCLOC
from
v\$process a, v\$session b,
v\$parameter c, v\$thread c
where
a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
host cp &TRCFILE control_&instance._&filedate..sql
exit

EOF

Example usage:
oracle@htpc:bin$ ls
backup_controlfile.sh
oracle@htpc:bin$ ./backup_controlfile.sh
oracle@htpc:bin$ ls -1
backup_controlfile.sh
control_peggy_20061105_225110.sql
oracle@htpc:bin$ grep -m1 "CREATE CONTROLFILE" control*
CREATE CONTROLFILE REUSE DATABASE "PEGGY" NORESETLOGS ARCHIVELOG
oracle@htpc:bin$
Found the trace location query over at Asktom.

Quick update:
The blogger editor buggered up the script the first time around, had to use ampersand encoding to get the << and > to work properly.

2 comments:

Amelia said...

Definitely its a nice script that will ease all in copying the control file. I will try this script and will experiment how it will prove to be beneficial to me. From your explanation and usage of this script I am fairly impressed. Thanks for the code.
sap testing tools

Amelia said...

Definitely its a nice script that will ease all in copying the control file. I will try this script and will experiment how it will prove to be beneficial to me. From your explanation and usage of this script I am fairly impressed. Thanks for the code.
sap testing tools