脚本-LIUNX oracle热备份

LINUX：热备份脚本

mkdir -P /oracle_backup/hot_backup_dir/
touch /home/oracle/BACKUP_SCRIPT/hot_backup.sh
touch /home/oracle/BACKUP_SCRIPT/hot_backup.sql

hot_backup.sh 内容如下：

#!/bin/bash
. /home/oracle/.bash_profile
cd /oracle_backup
export ORACLE_SID=你的SID
rm -Rf hot_backup_dir
mkdir hot_backup_dir
echo off
sqlplus "sys/你的密码 as sysdba" <<EOF
@/home/oracle/BACKUP_SCRIPT/hot_backup.sql;
EOF

hot_backup.sql 内容如下：

set heading off   
set feedback on  
set linesize 121
set termout on
set serveroutput on
begin                                                                                      
for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )     
loop                                                                                       
        dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' begin backup');    
        execute immediate 'alter tablespace ' || x.tablespace_name || ' begin backup';       
end loop;         
end; 
/
set heading off   
set feedback off  
set linesize 121  
set termout off   
--generation  host backup sql scripts
spool /opt/oracle/BACKUP_SCRIPT/hot_01.log
select '--'||ts.name tsname,'host cp '||df.name||' /oracle_backup/hot_backup_dir/'
   ||substr(df.name,instr(df.name,'/',-1) + 1) 
from v$datafile df,v$tablespace ts
where ts.ts# not in (select distinct ts# from v$tempfile)
  and df.ts# = ts.ts#
  order by 1;
select 'host cp '||name||' /oracle_backup/hot_backup_dir/' from v$controlfile;  
spool off
--generation recovery sql scripts
spool /opt/oracle/BACKUP_SCRIPT/hot_02.log
select '--'||ts.name tsname,'host cp /oracle_backup/hot_backup_dir/'||substr(df.name,instr(df.name,'/',-1) + 1)||' '||df.name
from v$datafile df,v$tablespace ts
where ts.ts# not in (select distinct ts# from v$tempfile)
  and df.ts# = ts.ts#
  order by 1;
select 'host cp /oracle_backup/hot_backup_dir/'||substr(name,instr(name,'/',-1) + 1)||' '||name from v$controlfile;  
spool off
--execute auto hot backup
@/opt/oracle/BACKUP_SCRIPT/hot_01.log
set termout on
begin                                                                                     
for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )    
loop                                                                                      
        dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' end backup'); 
        execute immediate 'alter tablespace ' || x.tablespace_name || ' end backup';      
end loop;                                                                                 
end;
/
exit