Oracle hot backup script issue


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Oracle hot backup script issue
# 1  
Old 07-11-2008
Oracle hot backup script issue

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.
# 2  
Old 07-11-2008
Code:
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 

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;

try this.
# 3  
Old 07-13-2008
Thanks a lot Jim,
But what modifications should I do so that while copying and zipping od 2nd dbf file ,script should not run the below command again.

alter tablespace SYSTEM begin backup;

Here the script when runs, it creates a spool file which is run immediately to take hot backup.
I hope you understood my proble.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

parameter issue in oracle shell script

Hi All, I am new to this code and shell scripting. I have code like below CREQID=`echo $* | awk '{gsub(/"/, ""); print$9}'` REQID=$3 echo CREQID=$CREQID I have another parameter added to the report/program(interchanged) 1st one is now, order_no, 2nd one is CREQID. Now if... (2 Replies)
Discussion started by: Mist123
2 Replies

2. Shell Programming and Scripting

Shell script to call Oracle archive backup script when file system reaches threshold value

Hello All, I need immediate help in creating shell script to call archivebkup.ksh script when archive file system capacity reaches threshold value or 60% Need to identify the unique file system that reaches threshold value. ex: capacity ... (4 Replies)
Discussion started by: sasikanthdba
4 Replies

3. UNIX for Dummies Questions & Answers

I need perl script on cold backup in Oracle 10 g

Hi , I am new to Perl script.Can u please provide me perl scripts for the following: 1. Perl script on Cold backup in Oracle 10g 2.Perl script on Hot backup in Oracle 10g (0 Replies)
Discussion started by: Niharika Srivas
0 Replies

4. Shell Programming and Scripting

Cold backup oracle and Restore to new db

Hello All, I am very new to Linux i have assignment to take offline backup in linux and restore it to new database oracle 10g? Help me out any script of it and steps i will be very thankful Regards, Sameer (0 Replies)
Discussion started by: sameer_123
0 Replies

5. HP-UX

Can we restore omni oracle db backup

We have omni backup of oracle database about 40Gb, tablespaces are distributed over 2 partitions . But because of some reason both partition are not available more . Now we are in need to restore the entire data on new partitions .Can any guru's confirm if tried so earlier will this going to... (1 Reply)
Discussion started by: Shirishlnx
1 Replies

6. Red Hat

hot-add disk in oracle enterprise linux 5.5

Hi all, I have an Oracle Enterprise Linux 5.5 server which is a virtual guest on a vSphere host. I have provisioned 500GB disk space from SAN. I then migrated the VM from a 200GB disk onto the new 500GB disk. I then increased the disk space allocated to the VMware to the maximum size of... (7 Replies)
Discussion started by: Tommyk
7 Replies

7. Shell Programming and Scripting

Help with sed in Oracle DB backup script.

I have a script like this .. #!/bin/ksh database=$(echo $@ | sed 's/.*-S \(*\).*/\1/') instance=$(grep "$database" /var/opt/oracle/oratab | awk -F : '{print $1}') command=$(echo $@ | sed 's/"$database"/"$instance"/') echo $command when I tried to execute the script like this ... ksh... (5 Replies)
Discussion started by: talashil
5 Replies

8. Programming

Host command NOT getting executed in Hot backup script.

Hi All, i have problem in the following Sql Script. problem is : HOST (!) command is not getting executed.pls de-bugg this HoTBackup Script...... Most urgent...! Thank You very Much !! SQL> set serveroutput on SQL> set trimspool on SQL> set line 500 SQL> set head off SQL> set feed off... (4 Replies)
Discussion started by: user__user3110
4 Replies

9. UNIX for Advanced & Expert Users

Shell script on Oracle hot backup got an error

Hi Unix Shell Script Expert, We have a robust shell script (from old time) for our 9i database hot backup but generated error: Shell Script Content: #check for auto log mode and redirect as early as possible # autolog_enable $AUTOLOG_MODE $ORACLE_BASE/${ORASID_LOW}log/cron_hotbackup.log... (1 Reply)
Discussion started by: oradbus
1 Replies

10. Shell Programming and Scripting

Need help with oracle backup script

I have written a script to perfrom a cold backup on an oracle database. The script is failing everytime. Can anyone tell me what (stupid) error I am making. The output from the uname command follows Linux rayora 2.2.14-6.1.1smp #1 SMP Thu Apr 13 19:55:55 EDT 2000 i686 unknown The error... (8 Replies)
Discussion started by: beilstwh
8 Replies
Login or Register to Ask a Question