Re: Script tp parse control file backup ??
- From: "Faan DeSwardt" <faan@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <premj@xxxxxxxxxxxxxx>
- Date: Thu, 1 Jul 2004 13:43:54 -0700
Prem,
I presume that you want to use the end result to rebuild databases on a regular
interval or schedule completely automated, right?
If so then you can use the following approach as follows which gives you the
required flexibility:
1.. Create a header file (e.g. 'cf_header.sql') that has all the lines up to
and including the 'DATAFILE' line. Make sure that it includes 'STARTUP
NOMOUNT' as the first line. This header file is where you can tweak down
(especially for development or sandbox databases) items like the LOGFILE
section to have less redo log groups, less redo logs per group, smaller redo
logs and also disable archivelog. Also very useful if the new database resides
on a separate host with different file system layout and naming than the source
instance. Here is an example for a 'DEV' database I regularly refresh:
a.. STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 1022
MAXINSTANCES 3
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 ('/u09/oradata/DEV/redoA01.dbf',
'/u07/oradata/DEV/redoB01.dbf') SIZE 50M,
GROUP 2 ('/u09/oradata/DEV/redoA02.dbf',
'/u07/oradata/DEV/redoB02.dbf') SIZE 50M,
GROUP 3 ('/u09/oradata/DEV/redoA03.dbf',
'/u07/oradata/DEV/redoB03.dbf') SIZE 50M
DATAFILE
2.. Now call a shell script (e.g. cf_create.ksh) that generates the file (e.g
cr_DEV.sql) you will use to create the control file with by using the header
file as a start. Then it searches your filesystem for the datafiles via the
following logic 'ls /<MOUNT_POINT_PATTERN_FOR_DATAFILES>/${ORACLE_SID}/*dbf |
grep -v redo | grep -v
<OPTIONALLY_ANY_OTHER_FILE_PATTERNS_YOU_NEED_TO_EXCLUDE>' e.g. 'ls
/u*/oradata/$DB_SID/*f | grep -v redo | grep -v buf'. The output of the 'ls'
command is then used in a loop to create the 'DATAFILE' section. Again this
allows you to compensate for a different file system layout e.g. 8 datafile
locations on the source but 3 locations on the target. Here is an example of
this logic:
a.. cat cf_header.sql > cr_DEV.sql
dbf_count=`ls /u*/oradata/$DB_SID/*.*f | grep -v redo | grep -v buf | wc -l`
echo $dbf_count
(( cnt=1 ))
ls /u*/oradata/$DB_SID/*.*f | grep -v redo | grep -v buf | while read
filename
do
if [ $cnt -eq $dbf_count ]
then
echo "'$filename';" >> cr_DEV.sql
else
echo "'$filename'," >> cr_DEV.sql
fi
(( cnt=cnt+1 ))
done
3.. Now you have a script (e.g. 'cr_DEV.sql') that you can just call from
svrmgrl (pre-9i) or sqlplus (9i+) to start the instance and create the control
file.
4.. Any steps that you want to perform after the 'CREATE CONTROLFILE'
statement like recovering a hotbackup, adding tempfiles, resetting passwords,
etc. is better done in the shell script (e.g. db_create.ksh) that you use to
call the create control file script from. This way you have more control over
the different automated steps when recreating a target database by properly
trapping any failures during the process and taking the appropriate actions
e.g. page DBA, different exit status, etc.
I hope this helps and if you need more details about the 'db_create.ksh' script
then let me know and I will send you a sample to work from?
Good Luck!
-f
----- Original Message -----
From: "Prem Khanna J" <premj@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 30, 2004 10:46 PM
Subject: Script tp parse control file backup ??
> Hi All,
> SQL > alter system backup controlfile to trace;
>
> Does anybody have a script to parse this control file backup ?
> that is, i need to create a neat script that contains just
>
> STARTUP NOMOUNT
> CREATE CONTROLFILE...
> ...
> ...
> CHARACTER SET JA16SJIS;
>
> Can someone share it with me ??
>
> Thanks and Regards,
> Prem.
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
