[转载]truncate 表之后，采用的恢复方法

原文地址：truncate 表之后，采用的恢复方法作者：ban仙

一 模拟环境

   1.Create environment

      sqlplus / as sysdba

      sql> create tablespace myexample

      sql> conn tiger/tiger
      sql> create table mysales tablespace myexample   as select * from dba_tables;
      sql> select count(*) from mysales;

   2 备份

     [oracle@paynode2 new]$ rman targetsys@PAYRAC

     RMAN>backup database format '+ARCHIVELOG/back/%d_%p_%s.FULL'

   3.truncate 表

    sqlplus / as sysdba

    sql > alter system switch logfile;

    sql > /

    sql >  select current_scn from v$database;

    sql >  select count(*) from  table tiger. mysales;

    sql >  set time on

   10:15:41 SQL> select current_scn from v$database;

              CURRENT_SCN
              -----------
              317123764

  10:15:51 SQL> truncate table tiger. mysales;

 

二 利用rman备份和TSPITR 恢复rac truncate的表数据

(1). 建立密码文件

  orapwd file=initaux.ora entries=4 password=aux

(2).建立参数文件
  
   1.连接主库，建立参数文件
   
     sqlplussys/man@payracas sysdba
 
     create pfile='/data/app/oracle/product/11.2.0/dbhome_1/dbs/initaux.ora' from spfile;
  
   2.编辑 initaux.ora 文件

    修改
     *.log_file_name_convert=('+DATA_FILE','/data/aux','+ARCHIVELOG','/data/aux')
     *.db_file_name_convert=('+DATA_FILE','/data/aux')
     *.control_files='/data/aux/aux.ctl'
    *.db_unique_name='aux'

    加入

     *.instance_number=1

    改小

      *.memory_target=3488881664

    删除有关内存分配的参数

(3).restore 文件

    rman targetsys/man@payrac

    rman > list backup of datafile 1,3,4,22;

    1       Full 317120994  21-OCT-11 +DATA_FILE/payrac/datafile/system.259.735856809
    3       Full 317120994  21-OCT-11 +DATA_FILE/payrac/datafile/undotbs1.261.735856815
    4       Full 317120994  21-OCT-11 +DATA_FILE/payrac/datafile/undotbs2.263.735856825
    22      Full 317120994  21-OCT-11 +DATA_FILE/payrac/datafile/myexample.264.764951973

    ( 注：另开一个窗口         mkdir -p /data/aux/payrac/datafile)

   rman >  restore controlfile to '/data/aux/aux.ctl' from '+ARCHIVELOG/back/control.c-1544998241-20111021-00';

  RMAN> run {
              set newname for datafile 1 to   '/data/aux/payrac/datafile/system.259.735856809';
              set newname for datafile 3 to   '/data/aux/payrac/datafile/undotbs1.261.735856815';
              set newname for datafile 4 to   '/data/aux/payrac/datafile/undotbs2.263.735856825';
              set newname for datafile 22 to  '/data/aux/payrac/datafile/myexample.264.764951973'
              restore datafile 1,3,4,22;
              switch datafile all;
             }

(4). 启动辅助例程aux

 export ORACLE_SID=aux
 sqlplus / as sysdba

 sql >  startup nomount pfile=/data/app/oracle/product/11.2.0/dbhome_1/dbs/initaux.ora

 sql >  alter database mount clone database;

 sql > alter database datafile 1 online;
 sql > alter database datafile 3 online;
 sql > alter database datafile 4 online;
 sql > alter database datafile 22 online;

(5). 修改 log_archive_dest_1之后 做恢复

  sql >  alter system set log_archive_dest_1='LOCATION=+ARCHIVELOG/arch';

  sql >  recover database until change 317123764 using backup controlfile;

(6).  打开数据库

   sql > alter database open read only;

(7). 导出表数据，并在正式库上导入

    exp system/oracle   tablespace=MYEXAMPLE file=use01.dmp

   或者

    exp system/oracle   tables=tiger.mysales  file=use02.dmp

   或者
       expdp system/oracle

   用imp,impdp 导入即可