Create a Backup Script

This script will create a DOS batch file that copies data files, control files, log files, and the .ora files. Figure 1 shows the instructions displayed at the SQL prompt after BKU.SQL is run. Figure 2 shows the directory created, and the DOS batch file in it. Figure 3 shows a sample DOS batch file.

Define OnOff = OFF
rem +------------------------------------------------------------------+
rem | Script ID:    bku.sql                                         1.1
rem |
rem | Purpose:      Backup up important files on the database
rem |        
rem | Developer:    Lynn Tobias
rem | Script Date:  7/30/2008
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual, Dba_Data_Files, V$Logfile, V$Controlfile
rem |         
rem | Called by:     n/a
rem |
rem | Calls:         MkDirBat.Bat     (DOS) Checks directory created
rem |                BkUp-&Today..Bat (DOS) the batch file with items to copy
rem |
rem | Variables:     xFile    - the name of the group of files used as
rem |                           documentation in the dos batch file
rem |                BkUp_Dir - the backup directory entered
rem |                Today    - date to put in the batch file
rem |                OnOff    - set up for debuggin purposes.  When you want to
rem |                           see what's happening, set to ON
rem |                CopyName - Alias for any filename retrieved. Set once to A60
rem |
rem | Output:        MkDirBat.Bat
rem |
rem | Revisions: Dvl Date   Ver Comment/Change
rem |            --- ------ --- ----------------------------------+
rem |            lct  080808 1.1 - Changed _Date to &Today
rem |                            - stop script after batch file created
rem |                            - get directory name before creating batch file
rem |                            - added instructions at end
rem |                            - combined xcopy for all *.ora files
rem +------------------------------------------------------------------+

Clear Screen
Column CopyName Format A60

Ttitle 'REM '                                                            skip 1-
       'REM ***********************************************************' skip 1-
       'REM * ' xFile                                                    skip 1-
       'REM ***********************************************************' skip 1-
       'REM '

--+- 1 -----------------------------------------------------------------
--| Write MkDirBat.Bat. Couldn't figure how else to get it to pause so we
--| had a chance to see if the 'md' worked. (MD doesn't work it if already
--| exists -- some cleanup might need to be done.)
--+---------------------------------------------------------------------
Set Term On
Accept Bkup_Dir Prompt 'Enter Backup directory (i.e., d:\ or c:\bkup): '

Set Echo &OnOff Term &OnOff Pagesize 0 Verify &OnOff Feedback &OnOff

Spool mkdirbat.bat Replace
Select 'md ' || '&Bkup_Dir'
             ||  chr(10)
             || 'pause'
From Dual;
Spool Off

--+- 2 -----------------------------------------------------------------
--| Run the batch file just created to show status on the make directory.
--| This will display an error in the Dos window if this directory exists.
--+---------------------------------------------------------------------

Host MkDirBat

--+- 3 -----------------------------------------------------------------
--| Get todays date for the batch file name. Start spooling.
--+---------------------------------------------------------------------

Set Term Off Pagesize 9999 Heading Off

Column Today New_Value Today
Select To_Char(Sysdate,'mmddyy') Today
From Dual;

Spool BkUp-&Today..Bat

--+- 4 -----------------------------------------------------------------
--| Start writing out the file names to be copied: Data Files
--+---------------------------------------------------------------------

Define xFile = 'Data Files'

Select 'Copy',
       File_Name  As CopyName,
       '&Bkup_Dir'
From Dba_Data_Files;

--+- 5 -----------------------------------------------------------------
--| Log Files
--+---------------------------------------------------------------------

Define xFile = 'Redo Log Files'

Select 'Copy',
       Member     As CopyName,
       '&Bkup_Dir'
From V$Logfile;

--+- 6 -----------------------------------------------------------------
--| Control Files
--+---------------------------------------------------------------------

Define xFile = 'Control Files'

Select 'Copy',
        Name       As CopyName,
       '&Bkup_Dir'
From V$Controlfile;

--+- 7 -----------------------------------------------------------------
--| Copy the *.ORA files: Password (PWD), Init, SPFile, TNSNames
--| (Done this way because there can be more than one with the same name.
--+  Xcopy saves under the various directories so nothing gets overwritten.)
--+---------------------------------------------------------------------

Define xFile = '*.ORA'

Select 'xcopy c:\oracle\*.ora /s &Bkup_Dir'
from dual;

--+- 8 -----------------------------------------------------------------
--| Spool off and further instructions
--+---------------------------------------------------------------------

Spool Off

Host move BkUp-&Today..Bat &Bkup_Dir

Set Term On
Prompt
Prompt +--------------------------------------------------------------------------+
Prompt | Your DOS Batch File has been moved the backup directory                  |
Prompt | To continue with process:                                                |
Prompt +--------------------------------------------------------------------------+
Prompt | 1) Conn sys/sys as sysdba                                                |
Prompt | 2) SHUTDOWN IMMEDIATE                                                    |
Prompt | 3) Open a DOS window                                                     |
Prompt | 4) Change to directory specified in script with (cd &Bkup_Dir)    
Prompt | 5) Run batch file. Type the first portion of the file name (BkUp-&Today) |
Prompt | 6) Have a good night! ;-)                                                |
Prompt +--------------------------------------------------------------------------+
Prompt

--+- 9 -----------------------------------------------------------------
--| Clean up
--+---------------------------------------------------------------------

Undefine xFile BkUp_Dir Today
Ttitle Off
Set Heading On Feedback On Verify On
host del MkDirBat.Bat