注：脚本第三行中的DB_NAME，需要改为自己的数据库名（show parameter name;）；

oracle用户下新建目录：/home/oracle/DB_NAME/hot_bak（DB_NAME为自己的数据库名字）

[oracle@localhost ~]$ vi hot.sql

set feedback off pagesize 0 heading off verify off linesize 100 trimspool on echo off time off timing off

define DB_NAME=lgr

define BKDIR='/home/oracle/&DB_NAME/hot_bak'

define BKSQL='/home/oracle/&DB_NAME/hot_cmd.sql'

define SPO='&BKDIR/hot_bak.lst'

prompt ***spooling to &bakscp

set serveroutput on

spool &BKSQL

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  not 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||' &BKDIR');

   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;

@&BKSQL

SYS@LGR> @/home/oracle/hot.sql

Enter value for bakscp: hot

***spooling to hot

spool /home/oracle/lgr/hot_bak/hot_bak.lst

alter system switch logfile;

alter tablespace SYSTEM begin backup;

host cp /u01/app/oracle/oradata/lgr/system01.dbf /home/oracle/lgr/hot_bak

alter tablespace SYSTEM end backup;

alter tablespace SYSAUX begin backup;

host cp /u01/app/oracle/oradata/lgr/sysaux01.dbf /home/oracle/lgr/hot_bak

alter tablespace SYSAUX end backup;

alter tablespace USERS begin backup;

host cp /u01/app/oracle/oradata/lgr/users01.dbf /home/oracle/lgr/hot_bak

alter tablespace USERS end backup;

alter tablespace TS_IDX begin backup;

host cp /u01/app/oracle/oradata/lgr/ts_idx02.dbf /home/oracle/lgr/hot_bak

host cp /u01/app/oracle/oradata/lgr/ts1 /home/oracle/lgr/hot_bak

alter tablespace TS_IDX end backup;

alter tablespace TS_USERS begin backup;

host cp /u01/app/oracle/oradata/lgr/ts2 /home/oracle/lgr/hot_bak

alter tablespace TS_USERS end backup;

alter tablespace UNDOTBS2 begin backup;

host cp /u01/app/oracle/oradata/lgr/undotbs02.dbf /home/oracle/lgr/hot_bak

alter tablespace UNDOTBS2 end backup;

archive log list;

spool off;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/oradata/lgr/

Oldest online log sequence     33

Next log sequence to archive   34

Current log sequence           34

[oracle@localhost lgr]$ cd /home/oracle/lgr/hot_bak/

[oracle@localhost hot_bak]$ ll

total 1721068

-rw-r--r-- 1 oracle oinstall        252 Dec 12 23:45 hot_bak.lst

-rw-r----- 1 oracle oinstall  340795392 Dec 12 23:44 sysaux01.dbf

-rw-r----- 1 oracle oinstall 1073750016 Dec 12 23:44 system01.dbf

-rw-r----- 1 oracle oinstall   41951232 Dec 12 23:44 ts1

-rw-r----- 1 oracle oinstall  104865792 Dec 12 23:45 ts2

-rw-r----- 1 oracle oinstall   41951232 Dec 12 23:44 ts_idx02.dbf

-rw-r----- 1 oracle oinstall  104865792 Dec 12 23:45 undotbs02.dbf

-rw-r----- 1 oracle oinstall   52436992 Dec 12 23:44 users01.dbf