[oracle@RHEL5 shujukuai]$ mkdir /u01/backup_dir
[oracle@RHEL5 shujukuai]$ vi /u01/backup_dir/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
 --生成备份的sql
spool /u01/backup_dir/01.log
select '--'||ts.name tsname,'host cp '||df.name||' /u01/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||' /u01/backup_dir/' from v$controlfile;
spool off
--生成恢复的sql
spool /u01/02.log
select '--'||ts.name tsname,'host cp /u01/backup_script/'||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 /u01/backup_dir/'||substr(name,instr(name,'/',-1) + 1)||' '||name from v$controlfile;
 spool off
--执行自动热备份
@/u01/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

[oracle@RHEL5 shujukuai]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 1 00:29:39 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/backup_dir/hot_backup.sql
alter tablespace SYSTEM begin backup
alter tablespace UNDOTBS1 begin backup
alter tablespace SYSAUX begin backup
alter tablespace USERS begin backup
alter tablespace TBS01 begin backup

PL/SQL procedure successfully completed.

alter tablespace SYSTEM end backup
alter tablespace UNDOTBS1 end backup
alter tablespace SYSAUX end backup
alter tablespace USERS end backup
alter tablespace TBS01 end backup
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@RHEL5 shujukuai]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 1 00:31:06 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> create tablespace test datafile '/u01/app/oracle/oradata/shujukuai/test.dbf' size 10m;

Tablespace created.

SQL> select file#,status$,CRSCNBAS from file$;

     FILE#    STATUS$   CRSCNBAS
---------- ---------- ----------
         1          2          5
         2          2     600647
         3          2       6678
         4          2      10685
         5          2     631813
         6          2     672690

6 rows selected.

SQL> create table a tablespace test as select * from dict;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  672763

SQL> alter system switch logfile;                 --这里反复地切换日志，就是为了归档撒。

System altered.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select file#,status$,CRSCNBAS from file$;

     FILE#    STATUS$   CRSCNBAS
---------- ---------- ----------
         1          2          5
         2          2     600647
         3          2       6678
         4          2      10685
         5          2     631813
         6          1     672690                 --这里数据文件6的状态变为1了，说明这个文件已经被删除。 我们还会发现这个SCN与删除前的没有改变，因为数据文件都删除了撒，数据文件还做啥检查点呢

6 rows selected.

SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 NO         672770         16 CURRENT
         2   52428800 YES        672553         14 ACTIVE
         3   52428800 YES        672751         15 ACTIVE

SQL> col dname for a40
SQL> set linesize 170
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,CREATION_CHANGE#,status from v$datafile;

DNAME                                    CHECKPOINT_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
---------------------------------------- ------------------ --------------- ---------------- -------
/u01/app/oracle/oradata/shujukuai/system             672553               0                5 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotb             672553               0           600647 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux             672553               0             6678 ONLINE
/u01/app/oracle/oradata/shujukuai/users0             672553               0            10685 ONLINE
/u01/app/oracle/oradata/shujukuai/tbs01.             672553               0           631813 ONLINE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

--这里开始使用备份恢复数据文件控制文件

未完成