(1) Write hot backup script (double spool)
Set feedback off pagesize 0 heading off verify off Linesize-Trimspool on echo off
Define bakdir= '/disk1/backup/anny/hot_bak '
Define bakscp= '/disk1/backup/anny/hot_cmd.sql '
Define spo= ' &bakdir/hot_bak.lst '
Prompt ***spooling to &BAKSCP
Set Serveroutput on
Spool &BAKSCP
Prompt spool &spo
Prompt alter system switch logfile;;
Declare
Cursor Cur_tablespace is
Select Tablespace_name from dba_tablespaces where status <> ' READ only ' and contents ' don't like '%TEMP% ';
Cursor Cur_datafile (TN varchar2) is
Select file_name from Dba_data_files where tablespace_name=tn;
Begin
For CT in Cur_tablespace loop
Dbms_output.put_line (' Alter tablespace ' | | Ct.tablespace_name | | ' begin backup; ');
For CD in Cur_datafile (Ct.tablespace_name) loop
Dbms_output.put_line (' host CP ' | | cd.file_name| | ' &bakdir ');
End Loop;
Dbms_output.put_line (' Alter tablespace ' | | ct.tablespace_name| | "End backup;";
End Loop;
End
/
Prompt archive log list;;
prompt spool off;;
Spool off;
This article URL address: http://www.bianceng.cn/database/Oracle/201410/45397.htm
@&bakscp--generally do not execute the script first, to check if there is no error
(2) Execute the above script, will generate a/disk1/backup/anny/hot_cmd.sql script, used for cold backup
[Oracle@solaris10 Anny] $cat hot_cmd.sql
Spool/disk1/backup/anny/hot_bak/hot_bak.lst
alter system switch logfile;
Alter tablespace SYSTEM begin backup;
Host Cp/u01/app/oracle/oradata/anny/system01.dbf/disk1/backup/anny/hot_bak
Alter tablespace SYSTEM end backup;
Alter tablespace Sysaux begin backup;
Host Cp/u01/app/oracle/oradata/anny/sysaux01.dbf/disk1/backup/anny/hot_bak
Alter tablespace Sysaux end backup;
Alter tablespace USERS begin backup;
Host Cp/u01/app/oracle/oradata/anny/user01.dbf/disk1/backup/anny/hot_bak
Alter tablespace USERS end backup;
Alter tablespace TEXT begin backup;
Host Cp/u01/app/oracle/oradata/anny/text01.dbf/disk1/backup/anny/hot_bak
Alter tablespace TEXT end backup;
Alter tablespace LX01 begin backup;
Host Cp/u01/app/oracle/oradata/anny/lx01.dbf/disk1/backup/anny/hot_bak
Alter tablespace LX01 end backup;
Alter tablespace UNDOTBS begin backup;
Host Cp/u01/app/oracle/oradata/anny/undotbs01.dbf/disk1/backup/anny/hot_bak
Alter tablespace UNDOTBS end backup;
Alter tablespace LX02 begin backup;
Host Cp/u01/app/oracle/oradata/anny/lx02.dbf/disk1/backup/anny/hot_bak
Alter tablespace LX02 end backup;
Alter tablespace LX03 begin backup;
Host Cp/u01/app/oracle/oradata/anny/lx03.dbf/disk1/backup/anny/hot_bak
Alter tablespace LX03 end backup;
Alter tablespace LX04 begin backup;
Host Cp/u01/app/oracle/oradata/anny/lx04.dbf/disk1/backup/anny/hot_bak
Alter tablespace LX04 end backup;
Alter tablespace INDEXES begin backup;
Host Cp/u01/app/oracle/oradata/anny/index01.dbf/disk1/backup/anny/hot_bak