Script to create a restore.bat file

Here is a script to create a restore.bat file.

While doing a database restructure for the purpose of upgrading a client, I had a need to restore the database from backup several times because of a flaw in the applications database restructure program. After the second time, I wrote a quick little script to create a restore.bat file, then thought "why not add this to all my scripts?" In particular, it's useful for cold backup scripts when a restore is needed after a failed upgra...

de.

To make the script more "dynamic" I created a simple table to hold the backup directory as well as a day_of_week column so that if I wanted I could easily have the backup go to a different location on a given day of the week. These values could also be hard coded instead, but I preferred the dyanmic approach.

The table holds two columns -- day_of_week and backup_dir -- and a variable for the backup_dir is defined at the start of the script. This is written for SQL*PLUS for simplicity, and its for the Windows platform, but could easily be modified to work on Unix. It also assumes that your init.ora is init<sid>.ora in the default location, and your datafile locations are in the same type of directory structure for the substr function to work. If the day of week feature is not wanted it's very easy to modify the code to fit. I find this script very handy for taking a backup just before an upgrade or restructure to the database.

#create a table to hold the backup #directory and day of week, table
#could be just backup dir if wanted
CREATE TABLE RESTORE
(DAY_OF_WEEK VARCHAR2(10), 
 BACKUP_DIR VARCHAR2(30)) 
TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 2 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) 
#end of create table 
======================================
#populate table 
insert into restore values 
('Monday', '&yourbackupdir')
#run this for each day of week
======================================
#start of backup creation script

#Coldbackup script to create a backup and restore script, based on day of week
#Written by Glen Stromquist, April 2 2003
#tested on 8.1.7.3 running on windows 2000 advanced server
#primary purpose is to take a cold backup and create a restore script
#this could be easily modified to fit an online backup script as well - GS


--connect to database and set sqlplus session
connect username/pwd@database
set showmode off
set verify off
set linesize 100
set pages 0
set termout off
set heading off
set feed off
--create a variable for the backup directory based on day of week
--if day of week is not used delete the where clause!!
column backup_dir new_value bd
select backup_dir from restore 
where day_of_week = rtrim(to_char(sysdate,'Day'));

--start spooling the backup script
spool c:\$orascripts\<sid>\coldback.sql

select 'connect system/password@database;' from dual
/
--create line to create a logfile for your backup
select 'spool '||'&bd'||'backup.log;' from restore where day_of_week = 

rtrim(to_char(sysdate,'Day'))
/
--backup the controlfile to trace
select 'alter database backup controlfile to trace;' from dual
/

--shutdownthe database
select 'shutdown immediate;' from dual
/

--delete the previous backup files
select 'host del /q '||'&bd'||'*.*;' from dual
/

prompt

--create the lines to copy the datafiles, controlfiles, init file and redo logs 
select 'host xcopy '||file_name||' &bd;' from dba_data_files
/
select 'host xcopy '||name||' &bd;' from v$controlfile
/
select 'host xcopy '||member||' &bd;' from v$logfile
/
select 'host xcopy c:oracleadmin'||name||'pfileinit'||name||'.ora &bd;' from 

v$database
/
prompt

--create line to restart the database
select 'startup pfile=c:\oracle\admin\eirdpfileinit'||name||'.ora' from 

v$database
/
select 'spool off;' from dual
/ 

prompt
select 'exit' from dual
/


spool off

--end of backup script and start of restore script creation
--you may want to change this file location to your backup dir...

spool c:\$orascripts\<sid>\restore.bat

--create line to delete all the current database files
--this assumes that the database is shutdown before running the created script!

select 'del '||file_name from dba_data_files
/

--create lines to restore the datafiles, controlfiles, redo logs and init.ora 
select 'xcopy '||'&bd'||substr(file_name,24)||' '||file_name from dba_data_files
/
select 'xcopy '||'&bd'||substr(name,24)||' '||name from v$controlfile
/
select 'xcopy '||'&bd'||substr(member,24)||' '||member from v$logfile
/
select 'xcopy '||'&bd'||'init'||name||'.ora c:oracleadmin'||name||'pfile;' 

from v$database 
/


spool off

--end of restore.bat creation script

========================================

About the Author

Glen Stromquist is a DBA for Alberta Pacific Forest Industries and has been working with Oracle full time since Sept 1999. He looks after eight Oracle databases and four SQL Server databases, all running on Windows platforms except for a GIS database, which runs on Solaris. He recently received his Oracle 8i OCP designation and hopes to upgrade to 9i later this year. In his "spare" time, he likes to work with linux/Oracle and hopes to delve into MySQL and PostgreSQL more.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in April 2003

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close