Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Auto Backup Data base

Re: Auto Backup Data base

From: B.Sc Yassir Khogaly <yassir_at_khogaly.freeserve.co.uk>
Date: Tue, 3 Nov 1998 16:23:50 -0000
Message-ID: <71nan3$gmv$1@newsreader2.core.theplanet.net>


Hi,

You can try this utility, But note that this script is Not Supported By Oracle World Wide Support Team.

Regards

@echo off
rem Edit the command file, modify ORACLE_SID and the O_CONNECT string parameters.
rem Launch the script:

rem DB_BACKUP HOT Target_Backup_Path
rem DB_BACKUP COLD Target_Backup_Path
rem DB_BACKUP EXPORT Target_Backup_Path Complete,Incremental or Differential

if %1.==DOIT. goto %2
rem This script will complete Hot, Cold or an Export backup of an Oracle database on NT.
rem
rem This script was designed to run in batch. Use the AT scheduler to schedule the backup job.
rem
rem OS Authentication is prefered to which eliminates userid/passwords from this script file.
rem If useing OS authentication, make sure you have a loop back alias defined for the database.
rem Also ensure the AT scheduler runs under an account that has DBA authority (is in the ORA_OPER and ORA_DBA groups). rem The account must also be granted DBA or restricted session and exp_full_database for exports to work properly. rem
rem Edit the SID and CONNECT and INIT strings used in this command file. rem
rem Craig MacPherson - Oracle Corporation Canada Inc rem Yassir Khogaly - Oracle Corporation (CTS) rem
setlocal

REM These values cannot be derived, please set them to reflect your environment
set ORACLE_SID=CDM1
set O_CONNECT=/@cdm1
set O_INIT=c:\orabase\admin\%ORACLE_SID%\pfile\init.ora

rem Oracle Binaries

set O_SVRMGR=c:\orant\bin\svrmgr23.exe
set O_EXPORT=c:\orant\bin\exp73.exe
set O_PLUS=c:\orant\bin\plus33.exe
set O_COPY=c:\orant\bin\ocopy73.exe

REM COMPLETE, CUMULATIVE, or INCREMENTAL - default the inctype for exports set O_INCTYPE=COMPLETE

set O_BACKPATH=%2
if %O_BACKPATH%.==. goto help
echo. >>%O_BACKPATH%\test.tst||goto help2 del %O_BACKPATH%\test.tst

if %1.==EXPORT. goto start
if %1.==HOT. goto start
if %1.==COLD. goto start

goto HELP

rem



rem START OF THE BACKUP PROCESS
rem

:START
REM Adjust the target path for the backups rem set O_BACKPATH=%O_BACKPATH%\%1

REM Create DB_START.CMD script to start the database back up echo connect %O_CONNECT% as sysoper; >db_start.sql echo startup pfile=%O_INIT% >>db_start.sql echo exit >>db_start.sql

REM Create DB_DOWN.CMD script to shutdown the database echo connect %O_CONNECT% as sysoper; >db_down.sql echo shutdown immediate >>db_down.sql
echo exit >>db_down.sql

REM Create DB_RESTRICT.CMD script to shutdown the database and startup in restricted mode
echo connect %O_CONNECT% as sysoper; >db_restrict.sql echo shutdown immediate; >>db_restrict.sql echo startup pfile=%O_INIT% restrict; >>db_restrict.sql echo exit >>db_restrict.sql

REM ********************************************
REM Call this command file again and run the job call %0.cmd DOIT %1 %3 >%O_BACKPATH%\backup.log||call %0 DOIT %1 %3 >%O_BACKPATH%\backup.log
goto CLEANUP

rem



rem HOT BACKUP OF THE DATABASE
rem

:HOT ECHO -- Shutdown the database and startup in restricted mode
%O_SVRMGR% @db_restrict.sql

ECHO -- Backup the init file
copy %O_INIT% %O_BACKPATH%

ECHO -- Create a SQL*PLUS script for the datafile backups and get min log sequence number
echo set heading off; >%O_BACKPATH%\plus1.sql echo set feedback off; >>%O_BACKPATH%\plus1.sql echo spool %O_BACKPATH%\backup1.cmd; >>%O_BACKPATH%\plus1.sql echo select 'set vminlog='^|^|min(sequence#) from v$log where UPPER(status) = UPPER('INACTIVE'); >>%O_BACKPATH%\plus1.sql echo spool off; >>%O_BACKPATH%\plus1.sql echo spool %O_BACKPATH%\svrmgr1.sql; >>%O_BACKPATH%\plus1.sql echo select 'connect %O_CONNECT% as sysdba;' from dual; >>%O_BACKPATH%\plus1.sql
echo select 'alter tablespace '^|^|tablespace_name^|^|' begin backup;'^|^|' >>%O_BACKPATH%\plus1.sql
echo '^|^|'host start /wait %O_COPY% '^|^|file_name^|^|' %O_BACKPATH%;'^|^|' >>%O_BACKPATH%\plus1.sql
echo '^|^|'alter tablespace '^|^|tablespace_name^|^|' end backup;' from dba_data_files; >>%O_BACKPATH%\plus1.sql echo select 'alter system switch logfile;' from dual; >>%O_BACKPATH%\plus1.sql
echo select 'exit;' from dual; >>%O_BACKPATH%\plus1.sql echo exit; >>%O_BACKPATH%\plus1.sql

ECHO -- Run the sql*plus script to create the backup1.cdm and svrmgr1.sql scripts
%O_PLUS% %O_CONNECT% @%O_BACKPATH%\plus1.sql

ECHO -- Run the backup command script to set vminlog call %O_BACKPATH%\backup1.cmd

ECHO -- Run the svrmgr script to backup the datafiles
%O_SVRMGR% @%O_BACKPATH%\svrmgr1.sql

ECHO -- Create a SQL*PLUS script for the vmaxlog and control files echo set heading off; >%O_BACKPATH%\plus2.sql echo set feedback off; >>%O_BACKPATH%\plus2.sql echo spool %O_BACKPATH%\backup2.cmd; >>%O_BACKPATH%\plus2.sql echo select 'set vmaxlog='^|^|max(sequence#) from v$log where UPPER(status) = UPPER('CURRENT'); >>%O_BACKPATH%\plus2.sql echo spool off; >>%O_BACKPATH%\plus2.sql echo spool %O_BACKPATH%\svrmgr2.sql; >>%O_BACKPATH%\plus2.sql echo select 'connect %O_CONNECT% as sysdba;' from dual; >>%O_BACKPATH%\plus2.sql
echo select 'alter database backup controlfile to '''^|^|'%O_BACKPATH%\'^|^|substr(name,instr(name,'\',-1)+1)^|^|''' REUSE;' from v$controlfile; >>%O_BACKPATH%\plus2.sql echo spool off; >>%O_BACKPATH%\plus2.sql echo exit; >>%O_BACKPATH%\plus2.sql

ECHO -- Run the sql*plus script to create backup2.cmd and svrmgr2.sql scripts
%O_PLUS% %O_CONNECT% @%O_BACKPATH%\plus2.sql

ECHO -- Run the backup2.cmd command script to set vmaxlog call %O_BACKPATH%\backup2.cmd

ECHO -- Run the svrmgr2.sql script to backup the controlfiles
%O_SVRMGR% @%O_BACKPATH%\svrmgr2.sql

ECHO -- Create a SQL*PLUS script for the archive logs echo set heading off; >%O_BACKPATH%\plus3.sql echo set feedback off; >>%O_BACKPATH%\plus3.sql echo spool %O_BACKPATH%\backup3.cmd; >>%O_BACKPATH%\plus3.sql echo select 'copy '^|^|archive_name^|^|' %O_BACKPATH%' from v$log_history where sequence# between %vminlog% and %vmaxlog%+1; >>%O_BACKPATH%\plus3.sql echo spool off; >>%O_BACKPATH%\plus3.sql echo exit; >>%O_BACKPATH%\plus3.sql

ECHO -- Run the sql*plus script to create the backup3.cmd script
%O_PLUS% %O_CONNECT% @%O_BACKPATH%\plus3.sql

ECHO -- Run the backup command script to copy the archive logs call %O_BACKPATH%\backup3.cmd

ECHO -- Hot Backup Complete
goto END_OF_FILE;

rem



rem COLD BACKUP OF THE DATABASE
rem

:COLD ECHO -- Shutdown the database and startup in restricted mode
%O_SVRMGR% @db_restrict.sql

ECHO -- Create a SQL*PLUS script for the cold backup echo set heading off; >%O_BACKPATH%\backup.sql echo set feedback off; >>%O_BACKPATH%\backup.sql echo spool %O_BACKPATH%\backup.cmd; >>%O_BACKPATH%\backup.sql echo select 'copy '^|^|member^|^|' %O_BACKPATH%' from v$logfile; >>%O_BACKPATH%\backup.sql
echo select 'copy '^|^|name^|^|' %O_BACKPATH%' from v$controlfile; >>%O_BACKPATH%\backup.sql
echo select 'copy '^|^|file_name^|^|' %O_BACKPATH%' from dba_data_files; >>%O_BACKPATH%\backup.sql
echo spool off; >>%O_BACKPATH%\backup.sql echo exit; >>%O_BACKPATH%\backup.sql

ECHO -- Run SQL*PLUS to create the backup command script
%O_PLUS% %O_CONNECT% @%O_BACKPATH%\backup.sql

ECHO -- Shutdown the database
%O_SVRMGR% @db_down.sql

ECHO -- Run the backup script
call %O_BACKPATH%\backup.cmd

ECHO -- Optionaly bounce the Oracle Process here net stop OracleService%ORACLE_SID%
net start OracleService%ORACLE_SID%

ECHO -- Start the database
%O_SVRMGR% @db_start.sql

ECHO -- Cold Backup Complete
goto END_OF_FILE

rem



rem EXPORT THE DATABASE
rem

:EXPORT if %3.==CUMULATIVE. set O_INCTYPE=CUMULATIVE if %3.==INCREMENTAL. set O_INCTYPE=INCREMENTAL

ECHO -- Shutdown database and startup in restricted mode
%O_SVRMGR% @db_restrict.sql

ECHO -- Run Create the export parameter file echo COMPRESS=Y >%O_BACKPATH%\export.inp echo CONSISTENT=N >>%O_BACKPATH%\export.inp echo CONSTRAINTS=Y >>%O_BACKPATH%\export.inp echo DIRECT=Y >>%O_BACKPATH%\export.inp echo FULL=Y >>%O_BACKPATH%\export.inp

echo GRANTS=Y >>%O_BACKPATH%\export.inp
echo INDEXES=Y >>%O_BACKPATH%\export.inp
echo RECORD=Y >>%O_BACKPATH%\export.inp
echo ROWS=Y >>%O_BACKPATH%\export.inp
echo FEEDBACK=0 >>%O_BACKPATH%\export.inp echo INCTYPE=%O_INCTYPE% >>%O_BACKPATH%\export.inp echo STATISTICS=ESTIMATE >>%O_BACKPATH%\export.inp echo LOG=%O_BACKPATH%\EXPORT.LOG >>%O_BACKPATH%\export.inp echo FILE=%O_BACKPATH%\EXPDAT.DMP >>%O_BACKPATH%\export.inp

ECHO -- Start the export with the parameter file that was created
%O_EXPORT% %O_CONNECT% parfile=%O_BACKPATH%\export.inp

ECHO -- Shutdown the database
%O_SVRMGR% @db_down.sql

ECHO -- Optionaly bounce the Oracle process here net stop OracleService%ORACLE_SID%
net start OracleService%ORACLE_SID%

ECHO -- Startup the database
%O_SVRMGR% @db_start.sql

ECHO -- Export Backup Complete
goto END_OF_FILE

rem



rem USER HELP
rem

:HELP
echo.
echo B.CMD Usage:
echo Enter BACKUP TYPE TARGET [MODE]
echo Where TYPE is either HOT, COLD or EXPORT echo and TARGET is the location for the backup files e.g. c:\oraback\sid\HOT echo and if type is EXPORT, MODE is either COMPLETE, CUMULATIVE or INCREMENTAL
echo.
goto END_OF_FILE
:HELP2
echo.
echo Error - Cannot write to %O_BACKPATH% echo.
goto END_OF_FILE

rem



rem CLEANUP FILES
rem

:CLEANUP
if exist db_start.sql del db_start.sql
if exist db_down.sql del db_down.sql
if exist db_restrict.sql del db_restrict.sql

rem



rem HANDLE ERRORS HERE
rem

findstr /in "e
jackson wrote in message <71lkrs$qjv_at_netnews.hinet.net>...
>Hi Every:
>How to setup the auto-backup schedule for Oracle workgroup server 7.3
>(Windows NT version) ?
>Please give some example.
>Thanks in advanced!
>kc_at_ty.tnc.com.tw
>
>
>


begin 666 Yassir Khogaly.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DMH;V=A;'D[66%S<VER#0I& M3CI987-S:7(@2VAO9V%L>0T*5$E43$4Z4V5N:6]R($]R86-L92!$0D$-"E1% M3#M(3TU%.U9/24-%.BLT-"@P*3$X,2 T-C T,#0R#0I414P[0T5,3#M63TE# M13HK-#0T,3$Y,#8W-S8-"D%$4CM(3TU%.CL[.SM+96YT.SM%;F=L86YD#0I, M04)%3#M(3TU%.T5.0T]$24Y'/5%53U1%1"U04DE.5$%"3$4Z2V5N=#TP1#TP M045N9VQA;F0-"E523#IH='1P.B\O=W=W+FMH;V=A;'DN9G)E97-E<G9E+F-O M+G5K#0I54DPZ:'1T<#HO+W=W=RYA965U+F]R9RYU:PT*14U!24P[4%)%1CM) M3E1%4DY%5#IY87-S:7) :VAO9V%L>2YF<F5E<V5R=F4N8V\N=6L-"E)%5CHQ <.3DX,3$P,U0Q-C(S-#E:#0I%3D0Z5D-!4D0-"@`` `
end Received on Tue Nov 03 1998 - 10:23:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US