Youry's Blog

Youry's Blog

Oracle Scripts

leave a comment »

  • Cold Backup

I’ve use scripts from internet with my modifications for our local servers. It’s for myself and my colleagues only to help with day-to-day activity.

at the first I prefer to use a short path to the local folder which keeps necessary scripts. I use c:\scripts

good link is here:

She has a copyright, but because she wrote:

“You will have to create two additional files, shutdown.sql and startup.sql — see comments in the second scipt. You will also need to precreate the backup log and, of course, customize the directories and SID for your system.”  “Note: Proofread any scripts before using. Always try scripts on a test instance first. I’m not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!

I assume I have a right to correct her script for my configuration with appropriate credits for her work. If anybody has any problem with the updated script, please let me know. I’ll be happy to remove the updated script from this blog and to write my own. This is only to save my time, because I already wrote a lot of other scripts in the past, but I’ve lost many of them as well.

--------CUT-----------Updated by YK ------------------CUT---------------------

REM make_cold_backup.sql
REM copyright 2001-2004 N Roshak, updated by YK on Sun Vov 4, 2012
REM this is a sql script to generate a DOS backup script

rem YK's correction
connect / as sysdba
set heading off
set pagesize 0
set echo off
set feedback off
set verify off
set linesize 500
col mything for a500
spool C:\scripts\cold_backup.bat
prompt REM Script to take full cold database backup
prompt REM generated by make_cold_backup_scr.sql
prompt REM does not back up tempfiles
prompt REM
REM updated by YK
prompt set oracle_home=C:\ORACLE\PRODUCT\10.2.0
prompt set backup_dir=Y:\klo-sciora-1_backup\coldSCI254Backup
REM corrected by YK on Nov. 4th, 2012
select distinct 'copy ' || leaf
|| ' %backup_dir%' || substr(leaf,instr(leaf,'\',-1,1), length(leaf))
as  cmdstr
(select name as LEAF from v$datafile
select name from v$controlfile
select member from v$logfile)
prompt REM Copy init.ora and pwd files
prompt copy %oracle_home%\db_1\database\SPFILESCI254.ORA %backup_dir%\SPFILESCI254.ORA
prompt copy %oracle_home%\db_1\database\PWDsci254.ora %backup_dir%\PWDsci254.ora
spool off


REM backup.bat
REM this is a DOS batch script
REM to take a full cold backup
REM copyright 2002-2004 N Roshak, updated by YK on Nov 4th, 2012

REM requires two sql scripts in addition to the above sql script:
REM shutdown.sql (connect / as sysdba, shutdown immediate, exit)
REM startup.sql (connect / as sysdba, startup open, exit)

set oracle_home=C:\ORACLE\PRODUCT\10.2.0\db_1
set script_dir=c:\scripts
set shutdown=%script_dir%\shutdown.sql
set startup=%script_dir%\startup.sql
set scriptgen=%script_dir%\sci254ColdBackup.sql
set log=%script_dir%\backup.log

echo >> %log%
echo ----------BEGIN FULL COLD BACKUP OF DB---------- >> %log%
date /T >> %log%
time /T >> %log%
echo Generating cold backup script... >> %log%
%oracle_home%\bin\sqlplus /nolog @%scriptgen%

date /T >> %log%
time /T >> %log%
echo Shutting down... >> %log%

%oracle_home%\bin\sqlplus /nolog @%shutdown%

echo Backing up... >> %log%
call %SCRIPT_DIR%\cold_Backup.bat >> %log% 2>&1

echo Starting up... >> %log%
%oracle_home%\bin\sqlplus /nolog @%startup%

date /T >> %log%
time /T >> %log%
echo Succesfully completed. >> %log%
echo -----------END FULL COLD BACKUP OF DB----------- >> %log%


Written by youryblog

November 4, 2012 at 5:49 PM

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: