Re: Script tp parse control file backup ??

From: Faan DeSwardt <faan_at_sbcglobal.net>
Date: Thu, 1 Jul 2004 13:43:54 -0700
Message-ID: <00c701c45fac$2195b710$cf63400a@verifone.com>

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_at_rediffmail.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 01 2004 - 15:41:32 CDT