Solved

Hot Backup Script for Windows 2000.

Posted on 2004-04-15
2
4,402 Views
Last Modified: 2010-06-29
Hello All:

I need to do a Hot Backup of an Oracle database.  I know the script I need to create but its for Unix and when I create a similar batch file for Windows I can't do:

SVRMGRL
connect internal/<password> as sysdba
alter tablespace TOOLS begin backup;
etc
etc

How do you do a similar thing for Windows?

Thanks,

Simon.
0
Comment
Question by:simonk_klammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10837837
Our oracle backups on windows are very similar to our oracle backups on unix except that on windows, we ran into a problem where copy cannot copy a file that oracle has open.

So we have:
alter tablespace xxx begin backup
host start /wait ocopy oradata\xxx.dbf e:\backup;
alter tablespace xxx end backup
0
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 125 total points
ID: 10839184
Hope this script helps you.

This script will create the scripts necessary for a complete hot backup of
an Oracle database on NT.

Datafiles and controlfiles are backed up in this script. These scripts can
then be run in batch. Use the AT scheduler to schedule the backup job.

NOTE:

1) Don't use the script when using raw partitions.
2) To be able to take valid hot backups, your database must be running
in archivelog mode.
3) The Oracle user must have SYSDBA or SYSOPER system priviledge.

Product Name, Product Version
Oracle Server version 7.3.4 to 9.2.0 Platform MS Windows NT/2000
Date Created 11/8/2002
Instructions
Execution Environment:
     SQL, SQL*Plus, iSQL*Plus

Access
Privileges:
     Requires dba privileges on the target database.

Usage:
    To run the backup, issue the "hot_backup.cmd" from within a DOS
command window.
It can also be scheduled with the NT AT command (for
help on this command, type
AT /? from a DOS window).
Instructions:


PROOFREAD THIS SCRIPT BEFORE
USING IT! Due to differences in the way text
editors, e-mail packages, and operating
systems handle text formatting (spaces,
tabs, and carriage returns), this script
may not be in an executable state
when you first receive it. Check over the script
to ensure that errors of
this type are corrected.The script will produce an output
file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description
-------------------------
HOT_GEN.CMD SCRIPT NOTES
-------------------------
This
script creates the scripts needed to perfom a hot (online) database
backup of
an Oracle/NT database.  All datafiles, init files, and control
files are backed
up (including a backup of the control file to trace).

SETUP
-----
This
script must be run while the database is up and running and can
be run while
other users are accessing data.

SCRIPT EXPLANATION
------------------
This
script creates a number of scripts in the specified SCRIPT_TARGET location. A description and utilization of each script follows:

hot_backup.cmd
This cmd file is a single executable that performs the complete hot backup of the
Oracle database. It copies all init, control, anD datafiles to the specified
BACKUP_TARGET (as well as the control file to trace).

svrmgr1.sql
This sql performs log switches and backs up the datafiles svrmgr2.sql This script backs up the control files and control file to trace.

plus1.sql This script is used to generate the svrmgr1.sql script.

plus2.sql This script is used to generate the svrmgr2.sql script.

SECURITY NOTE
-------------

The generation scripts and the resulting output script files contain unencrypted
passwords for the oracle internal user, and as a result access to these files
should be restricted to only those users with Administrative rights to the
Oracle database.   This can be done with OS level file or directory permissions.  

References
Oracle8 Backup and Recovery, Release 8.0
Oracle8i Backup and Recovery Guide, Release 8.1.5

Script
@echo off
rem This script will create the scripts necessary for
a complete hot backup of an Oracle database on NT.
rem Datafiles and controlfiles
are backed up in this script.
rem These scripts can then be run in batch.  Use
the AT scheduler to schedule the backup job.
rem
rem Edit the SID, CONNECT
and INIT strings used in this command file.
rem
rem Author: Craig MacPherson- Oracle Corporation Canada Inc.  June/97
rem Edited: Stephen Morse - Oracle Corporation US, November 97
echo.
REM echo HOT_GEN.CMD Usage:
REM echo Enter HOT_GEN
 SCRIPT_TARGET BACKUP_TARGET
REM echo where SCRIPT_TARGET is the location for
the backup scripts e.g. c:\oraback\sid\COLD
REM echo and BACKUP_TARGET is the
location for the Oracle datafile backups when batch is executed
REM echo.
REM
pause

REM setlocal
REM 10-DEC-1999
REM 1) commented the above help and
pause out
REM 2) replaced the connects as sysdba by connect internal
REM 25-JAN-2000
REM
added set ORACLE_SID=<SID> in hot_backup.cmd

REM example uses SID=TTV817

REM              %ORACLE_HOME%=c:\oracle\ora817

REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM These values cannot be derived, please set them to reflect your environment
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

set
ORACLE_SID=TTV817
set ORACLE_HOME=c:\oracle\ora817
set O_CONNECT="ttran/ttran
as sysdba"
set O_INIT=C:\oracle\admin\TTV817\pfile\initTTV817.ora

rem Oracle
Binaries
set O_PLUS=%ORACLE_HOME%\bin\sqlplus.exe
set O_COPY=%ORACLE_HOME%\bin\ocopy.exe

set
O_BACKPATH=%2
set O_SCRIPTPATH=%1
if %O_SCRIPTPATH%.==. goto help
if %O_BACKPATH%.==.
goto help

rem *************************************************************
remHOT BACKUP OF THE DATABASE
rem *************************************************************
echo.
echo**********************************************************
echo - Create hot_backup.CMD script to coordinate all backup activities - for AT scheduling
echo **********************************************************
echo.
ECHO
REM Backup the init file >%O_SCRIPTPATH%\hot_backup.CMD
echo copy %O_INIT%
%O_BACKPATH% >>%O_SCRIPTPATH%\hot_backup.CMD
echo SET ORACLE_SID=%ORACLE_SID%
>>%O_SCRIPTPATH%\hot_backup.CMD
ECHO REM  Run the svrmgr script to backup
the datafiles >>%O_SCRIPTPATH%\hot_backup.CMD
echo %O_PLUS% %O_CONNECT%
the svrmgr2.sql script to backup the controlfiles >>%O_SCRIPTPATH%\hot_backup.CMD
echo
%O_PLUS% %O_CONNECT% @%O_SCRIPTPATH%\svrmgr2.sql >>%O_SCRIPTPATH%\hot_backup.CMD

echo.
echo **********************************************************
ECHO -- Create a SQL*PLUS script for the datafile backups
echo **********************************************************
echo.
echo
set heading off; >%O_SCRIPTPATH%\plus1.sql
echo set feedback off; >>%O_SCRIPTPATH%\plus1.sql
echo
set linesize 1000; >>%O_SCRIPTPATH%\plus1.sql
echo spool %O_SCRIPTPATH%\svrmgr1.sql;
>>%O_SCRIPTPATH%\plus1.sql
echo select 'alter system switch logfile;' from
dual; >>%O_SCRIPTPATH%\plus1.sql
echo select 'alter tablespace '^|^|tablespace_name^|^|'
begin backup;'^|^|' >>%O_SCRIPTPATH%\plus1.sql
echo '^|^|'host start /wait
%O_COPY% '^|^|file_name^|^|' %O_BACKPATH%;'^|^|' >>%O_SCRIPTPATH%\plus1.sql
echo
'^|^|'alter tablespace '^|^|tablespace_name^|^|' end backup;' from dba_data_files;
>>%O_SCRIPTPATH%\plus1.sql
echo select 'alter system switch logfile;' from
dual; >>%O_SCRIPTPATH%\plus1.sql
echo select 'exit;' from dual; >>%O_SCRIPTPATH%\plus1.sql
echo
exit; >>%O_SCRIPTPATH%\plus1.sql

echo.
echo **********************************************************
ECHO-- Run the sql*plus script to create the svrmgr1.sql script
echo **********************************************************
echo.
%O_PLUS%
%O_CONNECT% @%O_SCRIPTPATH%\plus1.sql

echo.
echo **********************************************************
ECHO -- Create a SQL*PLUS script for the control files
echo **********************************************************
echo.
echo
set heading off; >%O_SCRIPTPATH%\plus2.sql
echo set feedback off; >>%O_SCRIPTPATH%\plus2.sql
echo
set linesize 1000; >>%O_SCRIPTPATH%\plus2.sql
echo spool %O_SCRIPTPATH%\svrmgr2.sql;
>>%O_SCRIPTPATH%\plus2.sql
echo select 'alter database backup controlfile
to '''^|^|'%O_BACKPATH%\'^|^|substr(name,instr(name,'\',-1)+1)^|^|''' REUSE;' from
v$controlfile; >>%O_SCRIPTPATH%\plus2.sql
echo select 'alter database backup
controlfile to trace;' from dual; >>%O_SCRIPTPATH%\plus2.sql
echo select
'exit;' from dual; >>%O_SCRIPTPATH%\plus2.sql
echo spool off; >>%O_SCRIPTPATH%\plus2.sql
echo
exit; >>%O_SCRIPTPATH%\plus2.sql

echo.
echo **********************************************************
ECHO-- Run the sql*plus script to create the svrmgr2.sql scripts
echo **********************************************************
echo.
%O_PLUS%
%O_CONNECT% @%O_SCRIPTPATH%\plus2.sql

echo.
echo **********************************************************
ECHO
-- Hot Backup Complete
echo **********************************************************
echo.
goto
END_OF_FILE;

rem ****************************************************************
rem USER HELP
rem *******************************************************************
:HELP
echo.
echo
HOT_GEN.CMD Usage:
echo Enter HOT_GEN  SCRIPT_TARGET BACKUP_TARGET
echo where
SCRIPT_TARGET is the location for the backup scripts e.g. c:\oraback\sid\HOT
echo
and BACKUP_TARGET is the location for the Oracle datafile backups when batch is executed
echo.
goto
END_OF_FILE

:HELP2
echo.
echo Error - Cannot write to %O_BACKPATH%
echo.
goto
END_OF_FILE


rem **********************************************************************
remHANDLE ERRORS HERE
rem *********************************************************************
findstr
/in "error" %O_BACKPATH%\backup.log && findstr /in "error" %O_BACKPATH%\backup.log
>%O_BACKPATH%\error.log
findstr /in "ora-" %O_BACKPATH%\backup.log &&
findstr /in "ora-" %O_BACKPATH%\backup.log >%O_BACKPATH%\error.log
findstr
/in "cannot" %O_BACKPATH%\backup.log && findstr /in "cannot" %O_BACKPATH%\backup.log
>%O_BACKPATH%\error.log
findstr /in "not logged" %O_BACKPATH%\backup.log &&
findstr /in "not logged" %O_BACKPATH%\backup.log >%O_BACKPATH%\error.log
findstr
/in "failure" %O_BACKPATH%\backup.log && findstr /in "failure" %O_BACKPATH%\backup.log
>%O_BACKPATH%\error.log
if exist %O_BACKPATH%\error.log c:\ntreskit\logevent
-s E "BACKUP FAILURE!"
pause

endlocal
:END_OF_FILE
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question