Hot Backup Help Needed

From: David R. Barstis <dbarstis_at_saintmarys.edu>
Date: Fri, 2 Jul 1999 14:30:43 -0500
Message-ID: <Pine.GSO.4.05.9907021422090.26572-100000@jade.saintmarys.edu>

After taking the Backup and Recovery class over 3 years ago and not doing a thing with Oracle since, I've suddenly found myself in an Acting DBA role and need to set up a hot backup for a new database. I've written the following script and was wondering if the real Oracle DBA's out there can make sure I have all the bases covered.

Any and all help, advice, comments, and criticism is appreciated.

Also, the next thing is a cold backup. Any script help there would also be welcome.

Thanks,
Dave Barstis
Saint Mary's College

rem -----------------------------------------------------------------------

rem backup.sql
rem
rem This script will create a file called hotbackup.sql rem and run it to begin backing up all tablespaces.

rem -----------------------------------------------------------------------

column cmd format a80 heading "Text"
set feedback off
set heading off
set pagesize 0

spool hotbackup.sql

rem -----------------------------------------------------------------------

rem Begin backup

rem -----------------------------------------------------------------------

select 'alter tablespace '||a.tablespace_name||' begin backup;' cmd from sys.dba_data_files a
group by a.tablespace_name
/

rem -----------------------------------------------------------------------

rem Remove old log entries

rem -----------------------------------------------------------------------

select 'host rm '|| substr(a.file_name,instr(a.file_name,'/',-1,1)+1)||
       '.'|| to_char(add_months(sysdate,-2),'MM')||'*' cmd

from sys.dba_data_files a
group by a.file_name
/

rem -----------------------------------------------------------------------

rem Rename latest backups

rem -----------------------------------------------------------------------

select 'host mv '|| substr(a.file_name,instr(a.file_name,'/',-1,1)+1)||
       ' '|| substr(a.file_name,instr(a.file_name,'/',-1,1)+1)||
       '.'|| to_char(sysdate,'MM-DD-YYYY') cmd

from sys.dba_data_files a
group by a.file_name
/

rem -----------------------------------------------------------------------

rem Create new backups

rem -----------------------------------------------------------------------

select 'host cp '||a.file_name||' /usr/oradata/backups/INFOTECH' cmd from sys.dba_data_files a
group by a.file_name
/

rem -----------------------------------------------------------------------

rem End backup

rem -----------------------------------------------------------------------

select 'alter tablespace '||a.tablespace_name||' end backup;' cmd from sys.dba_data_files a, sys.v_$backup b where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name
/

rem -----------------------------------------------------------------------

rem Remove old archive logs

rem -----------------------------------------------------------------------

select 'host rm '||archive_name cmd
from v$log_history
where trunc(to_date(substr(time,1,8),'MM/DD/YY')) <= add_months(sysdate,-1)
/

rem -----------------------------------------------------------------------

rem Switch the archive log

rem -----------------------------------------------------------------------

select 'alter system switch logfile;' from dual
/

rem -----------------------------------------------------------------------

rem Remove old control files

rem -----------------------------------------------------------------------

select 'host rm control01.ctl.'||
       to_char(add_months(sysdate,-2),'MM')||'*' cmd

from dual
/

rem -----------------------------------------------------------------------

rem Rename latest control file

rem -----------------------------------------------------------------------

select 'host mv control01.ctl control01.ctl.'||to_char(sysdate,'MM-DD-YYYY') cmd from dual
/

rem -----------------------------------------------------------------------

rem Backup the control file

rem -----------------------------------------------------------------------

select 'alter database backup controlfile to ''/usr/oradata/backups/INFOTECH/control01.ctl'';' from dual
/

select 'alter database backup controlfile to trace;' from dual
/

spool off

set feedback on
set heading on
set pagesize 24
set termout on

start hotbackup.sql

exit

--

---

David R. Barstis                   E-mail: dbarstis_at_saintmarys.edu
Dept. of Information Technologies   Voice: (219) 284-5318
Saint Mary's College                  Fax: (219) 284-4716
Notre Dame IN 46556                   URL: http://www.saintmarys.edu/~dbarstis


      The word "listen" contains the same letters as the word "silent".
------------------------------------------------------------------------------

Received on Fri Jul 02 1999 - 14:30:43 CDT