转自 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导出需要恢复的表