On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, August 15, 2008

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

No comments:

Post a Comment