Solved

Hot Backup Script for Windows 2000.

Posted on 2004-04-15
2
4,364 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
2 Comments
 
LVL 3

Expert Comment

by:dbms_chu
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now