利用rman恢复被失误drop或者truncate的表

转自  http://blog.itpub.net/15747463/viewspace-1062560/

表被truncate/drop 的恢复方法有:

1 闪回数据库(需要开启flashback)

2 异机数据库不完全恢复(可基于部分表空间),exp导出,再导入源库

3 TSPITR (把表空间的所有表恢复的一个时间点,影响较大)

在没有开启flashback 的情况优先选择第二种方式 第二种方法过程如下:

1 安装与源数据库相同的数据库版本

2 创建必须的目录

mkdir -p /u01/app/oracle/flash_recovery_area

mkdir -p /u01/app/oracle/admin/orcl/udump

mkdir -p /u01/app/oracle/admin/orcl/adump

mkdir -p /u01/app/oracle/admin/orcl/bdump

mkdir -p /u01/app/oracle/admin/orcl/cdump

mkdir –p /u01/app/oracle/oradata/orcl

3 从源库复制参数文件并做相应修改

*.compatible='10.2.0.1.0'

*.control_file_record_keep_time=20

*.control_files='/u01/app/oracle/oradata/orcl/controlfile01.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=8

*.db_files=5000

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.nls_language='SIMPLIFIED CHINESE'

*.open_cursors=3500

*.pga_aggregate_target=3000M

*.processes=5000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=5000

*.sga_max_size=5000M

*.sga_target=5000M

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

4 从源库拷贝备份集和归档日志 或通NFS挂载

如果路径和原来的不同需要 catalog start with进行注册

5 恢复控制文件并加载

restore controlfile to '/u01/app/oracle/oradata/orcl/controlfile01.ctl'  from '/databak/databak166/orclctl.bak';

 

alter database mount clone database

--mount后所有数据文件是offline状态

6 恢复

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

set until time "to_date( '2011-12-02 09:17:00', 'yyyy-mm-dd HH24:MI:SS')";

 

set newname for datafile 1 to '/u01/app/oracle/oradata/orcl/system01.dbf';

set newname for datafile 2 to '/u01/app/oracle/oradata/orcl/undotbs01.dbf';

set newname for datafile 3 to '/u01/app/oracle/oradata/orcl/sysaux.dbf';

set newname for datafile 4 to '/u01/app/oracle/oradata/orcl/users.dbf';

set newname for datafile 5 to '/u01/app/oracle/oradata/orcl/TBS_FANGCHENG40.dbf';

 

restore tablespace system, undotbs1,sysaux,users, TBS_FANGCHENG40;

 

switch datafile all;

 

 

sql "alter database datafile 1,2,3,4,5 online";

 

recover database until time "to_date( '2013-12-02 09:17:00', 'yyyy-mm-dd HH24:MI:SS')" skip forever tablespace tbs_1,tbs_2;

 

 

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo01.log'' to ''/u01/app/oracle/oradata/orcl/REDO01.LOG''";

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo02.log'' to ''/u01/app/oracle/oradata/orcl/REDO02.LOG''";

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo03.log'' to ''/u01/app/oracle/oradata/orcl/REDO03.LOG''";

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo04.log'' to ''/u01/app/oracle/oradata/orcl/REDO04.LOG''";

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo05.log'' to ''/u01/app/oracle/oradata/orcl/REDO05.LOG''";

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo06.log'' to ''/u01/app/oracle/oradata/orcl/REDO06.LOG''";

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo07.log'' to ''/u01/app/oracle/oradata/orcl/REDO07.LOG''";

sql "alter database rename file ''/opt/app/oracle/oradata/orcl/redo08.log'' to ''/u01/app/oracle/oradata/orcl/REDO08.LOG''";

 

release channel c1;

release channel c2;

}

7 打开数据库

alter database open resetlogs;

8 使用exp导出需要恢复的表