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 upgrade.
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 22.214.171.124 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