Hi,
I have a script which we use to take hot backup of oracle database.It connects to database and saves all database related activities in a spool file.Then the spool file is run for taking hot backup. But at one stage it is giving an error.
Please find my hot backup script as below:
/* Directory to hold the backups /
define HOT_BACK_DIR = &HOT_BACK_DIR/
/ Hot Spool File /
--define SCRIPT_FILE = &HOT_BACK_DIR
/ Create Temporary Table with Tablespace Data /
drop table dsc_hot_stage;
create table dsc_hot_stage
(
tablespace_name varchar2(30),
file_name varchar2(200)
);
insert into dsc_hot_stage
select rtrim(tablespace_name),rtrim(file_name) from sys.dba_data_files;
/ Set up the parameters for the spool file */
set feedback off
set heading off
set pagesize 0
set linesize 128
set verify off
set termout on
set echo off
spool /fcdata1/hotbkp_script/hotbkp.sql
select 'conn / as sysdba' from dual;
select '!clear' from dual;
select 'host mkdir '||'&HOT_BACK_DIR'||to_char(sysdate,'yyyymmdd') from dual;
select 'host mkdir '||'&HOT_BACK_DIR'||to_char(sysdate,'yyyymmdd')||'/archive' from dual;
select '!echo "\033[7m ......Hot Backup Started !!....Starting Copying and Zipping of DBF files !!....\033[0m"' from dual;
select 'alter database backup controlfile to trace;' from dual;
select 'alter tablespace '|| tablespace_name||' begin backup;' c1,
'host cp '||file_name||' &HOT_BACK_DIR'||to_char(sysdate,'yyyymmdd') || '/' ||
substr(file_name,instr(rtrim(file_name),'/',-1,1)+1,length(rtrim(file_name))),
'host gzip '||' &HOT_BACK_DIR'||to_char(sysdate,'yyyymmdd') || '/' ||
substr(file_name,instr(rtrim(file_name),'/',-1,1)+1,length(rtrim(file_name)))
from dsc_hot_stage ,dual
union
select 'alter tablespace '|| tablespace_name||' end backup;' c1,
null, null
from dsc_hot_stage
group by tablespace_name,file_name order by 1;
--select 'host sh /fcdata1/hotbkp_script/progress.sh' from dual;
select '!echo " \033[7m......Copying and Zipping of DBF files Completed !!........\033[0m"' from dual;
select '!echo " \033[7m......Copying and Zipping of the Control files started !!......\033[0m" ' from dual;
select 'alter database backup controlfile to '||''''||'&HOT_BACK_DIR'||to_char(sysdate ,'yyyymmdd')||'/controlfile.ctl'||''''||';' from dual;
select '!echo "\033[7m....... Copying and Zipping of Control files completed !!........\033[0m"' from dual;
select '!echo "\033[7m ......Copying and Zipping of Archive Logs Started !!........\033[0m"' from dual;
select '@/fcdata1/hotbkp_script/archive_switch.sql;' from dual;
select '@/fcdata1/hotbkp_script/archive_seq1.sql;' from dual;
select 'host sh /fcdata1/hotbkp_script/archive_files_move/dsbmove.sh' from dual;
select '!echo "\033[7m ......Copying and Zipping of Archive Logs Completed !! ........\033[0m"' from dual;
select '!echo "\033[7m....... Hot Backup Completed Successfully !!.......\033[0m" ' from dual;
spool off
set feedback off
set heading off
set pagesize 0
set linesize 128
set verify off
set termout oN
set echo off
spool /fcdata1/hotbkp_script/archive_files_move/currarchivedate.log
select '&HOT_BACK_DIR'||to_char(sysdate,'yyyymmdd')||'/archive' from dual;
spool off
@/fcdata1/hotbkp_script/archive_switch.sql;
@/fcdata1/hotbkp_script/archive_seq.sql;
@/fcdata1/hotbkp_script/hotbkp.sql;
My spool file is as below:
conn / as sysdba
!clear
host mkdir /fcdata1/hotbkp_script/20080711
host mkdir /fcdata1/hotbkp_script/20080711/archive
!echo "\033[7m ......Hot Backup Started !!....Starting Copying and Zipping of DBF files !!....\033[0m"
alter database backup controlfile to trace;
alter tablespace FCATPROD begin backup;
host cp /fcdata2/fl03r/data/fcatprod.dbf /fcdata1/hotbkp_script/20080711/fcatprod.dbf
host gzip /fcdata1/hotbkp_script/20080711/fcatprod.dbf
alter tablespace FCATPROD end backup;
alter tablespace FCC_DATA_LARGE begin backup;
host cp /fcdata2/fl03r/data/FCC_DATA_LARGE.dbf /fcdata1/hotbkp_script/20080711/FCC_DATA_LARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_DATA_LARGE.dbf
alter tablespace FCC_DATA_LARGE end backup;
alter tablespace FCC_DATA_MEDIUM begin backup;
host cp /fcdata2/fl03r/data/FCC_DATA_MEDIUM.dbf /fcdata1/hotbkp_script/20080711/FCC_DATA_MEDIUM.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_DATA_MEDIUM.dbf
alter tablespace FCC_DATA_MEDIUM end backup;
alter tablespace FCC_DATA_SMALL begin backup;
host cp /fcdata2/fl03r/data/FCC_DATA_SMALL.dbf /fcdata1/hotbkp_script/20080711/FCC_DATA_SMALL.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_DATA_SMALL.dbf
alter tablespace FCC_DATA_SMALL end backup;
alter tablespace FCC_DATA_XLARGE begin backup;
host cp /fcdata2/fl03r/data/FCC_DATA_XLARGE.dbf /fcdata1/hotbkp_script/20080711/FCC_DATA_XLARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_DATA_XLARGE.dbf
alter tablespace FCC_DATA_XLARGE end backup;
alter tablespace FCC_IND_LARGE begin backup;
host cp /fcdata2/fl03r/data/FCC_IND_LARGE.dbf /fcdata1/hotbkp_script/20080711/FCC_IND_LARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_IND_LARGE.dbf
alter tablespace FCC_IND_LARGE end backup;
alter tablespace FCC_IND_MEDIUM begin backup;
host cp /fcdata2/fl03r/data/FCC_IND_MEDIUM.dbf /fcdata1/hotbkp_script/20080711/FCC_IND_MEDIUM.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_IND_MEDIUM.dbf
alter tablespace FCC_IND_MEDIUM end backup;
alter tablespace FCC_IND_SMALL begin backup;
host cp /fcdata2/fl03r/data/FCC_IND_SMALL.dbf /fcdata1/hotbkp_script/20080711/FCC_IND_SMALL.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_IND_SMALL.dbf
alter tablespace FCC_IND_SMALL end backup;
alter tablespace FCC_IND_XLARGE begin backup;
host cp /fcdata2/fl03r/data/FCC_IND_XLARGE.dbf /fcdata1/hotbkp_script/20080711/FCC_IND_XLARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FCC_IND_XLARGE.dbf
alter tablespace FCC_IND_XLARGE end backup;
alter tablespace FLEXML_DATA_LARGE begin backup;
host cp /fcdata2/fl03r/data/FLEXML_DATA_LARGE.dbf /fcdata1/hotbkp_script/20080711/FLEXML_DATA_LARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_DATA_LARGE.dbf
alter tablespace FLEXML_DATA_LARGE end backup;
alter tablespace FLEXML_DATA_MEDIUM begin backup;
host cp /fcdata2/fl03r/data/FLEXML_DATA_MEDIUM.dbf /fcdata1/hotbkp_script/20080711/FLEXML_DATA_MEDIUM.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_DATA_MEDIUM.dbf
alter tablespace FLEXML_DATA_MEDIUM end backup;
alter tablespace FLEXML_DATA_SMALL begin backup;
host cp /fcdata2/fl03r/data/FLEXML_DATA_SMALL.dbf /fcdata1/hotbkp_script/20080711/FLEXML_DATA_SMALL.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_DATA_SMALL.dbf
alter tablespace FLEXML_DATA_SMALL end backup;
alter tablespace FLEXML_DATA_XLARGE begin backup;
host cp /fcdata2/fl03r/data/FLEXML_DATA_XLARGE.dbf /fcdata1/hotbkp_script/20080711/FLEXML_DATA_XLARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_DATA_XLARGE.dbf
alter tablespace FLEXML_DATA_XLARGE end backup;
alter tablespace FLEXML_IND_LARGE begin backup;
host cp /fcdata2/fl03r/data/FLEXML_IND_LARGE.dbf /fcdata1/hotbkp_script/20080711/FLEXML_IND_LARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_IND_LARGE.dbf
alter tablespace FLEXML_IND_LARGE end backup;
alter tablespace FLEXML_IND_MEDIUM begin backup;
host cp /fcdata2/fl03r/data/FLEXML_IND_MEDIUM.dbf /fcdata1/hotbkp_script/20080711/FLEXML_IND_MEDIUM.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_IND_MEDIUM.dbf
alter tablespace FLEXML_IND_MEDIUM end backup;
alter tablespace FLEXML_IND_SMALL begin backup;
host cp /fcdata2/fl03r/data/FLEXML_IND_SMALL.dbf /fcdata1/hotbkp_script/20080711/FLEXML_IND_SMALL.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_IND_SMALL.dbf
alter tablespace FLEXML_IND_SMALL end backup;
alter tablespace FLEXML_IND_XLARGE begin backup;
host cp /fcdata2/fl03r/data/FLEXML_IND_XLARGE.dbf /fcdata1/hotbkp_script/20080711/FLEXML_IND_XLARGE.dbf
host gzip /fcdata1/hotbkp_script/20080711/FLEXML_IND_XLARGE.dbf
alter tablespace FLEXML_IND_XLARGE end backup;
alter tablespace SYSAUX begin backup;
host cp /fcdata1/fl03r/data/sysaux01.dbf /fcdata1/hotbkp_script/20080711/sysaux01.dbf
host gzip /fcdata1/hotbkp_script/20080711/sysaux01.dbf
alter tablespace SYSAUX end backup;
alter tablespace SYSTEM begin backup;
host cp /fcdata1/fl03r/data/system01.dbf /fcdata1/hotbkp_script/20080711/system01.dbf
host gzip /fcdata1/hotbkp_script/20080711/system01.dbf
alter tablespace SYSTEM begin backup;
host cp /fcdata1/fl03r/data/system02.dbf /fcdata1/hotbkp_script/20080711/system02.dbf
host gzip /fcdata1/hotbkp_script/20080711/system02.dbf
alter tablespace SYSTEM end backup;
alter tablespace UNDOTBS1 begin backup;
host cp /fcdata1/fl03r/data/undotbs01.dbf /fcdata1/hotbkp_script/20080711/undotbs01.dbf
host gzip /fcdata1/hotbkp_script/20080711/undotbs01.dbf
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS begin backup;
host cp /fcdata2/fl03r/data/fccprodr01.dbf /fcdata1/hotbkp_script/20080711/fccprodr01.dbf
host gzip /fcdata1/hotbkp_script/20080711/fccprodr01.dbf
alter tablespace USERS end backup;
!echo " \033[7m......Copying and Zipping of DBF files Completed !!........\033[0m"
!echo " \033[7m......Copying and Zipping of the Control files started !!......\033[0m"
alter database backup controlfile to '/fcdata1/hotbkp_script/20080711/controlfile.ctl';
!echo "\033[7m....... Copying and Zipping of Control files completed !!........\033[0m"
!echo "\033[7m ......Copying and Zipping of Archive Logs Started !!........\033[0m"
@/fcdata1/hotbkp_script/archive_switch.sql;
@/fcdata1/hotbkp_script/archive_seq1.sql;
host sh /fcdata1/hotbkp_script/archive_files_move/dsbmove.sh
!echo "\033[7m ......Copying and Zipping of Archive Logs Completed !! ........\033[0m"
!echo "\033[7m....... Hot Backup Completed Successfully !!.......\033[0m"
It runs fine till it executes the above highlited part of the code.
The tablespace 'system' is put in begin backup mode ,copies the system01.dbf file but before copying the system02.dbf file it's again trying to put the tablespace 'system' in begin backup mode,hence throwing an error "The tablespace is already in backup mode". After that it copies the 2nd dbf file.How could I copy all the dbf files residing in a tablespace.
How should I suppress that error. Please help.