表删除的不完全恢复

1、备份数据文件

select 'alter tablespace '||tablespace_name||' begin backup;'

||chr(10)

||'!cp -v '||file_name||' /home/oracle/hotbak/;'

||chr(10)||'alter tablespace '||tablespace_name||' end backup;' 

from dba_data_files;

控制文件：

alter database backup controlfile to '/home/oracle/hotbak/controlfile.ctl';

2、切日志打检查点，尽可多做几次

alter system checkpoint;

alter system switch logfile;

3、删除表

drop table scott.emp01 purge;

4、切日志打检查点，尽可多做几次

alter system checkpoint;

alter system switch logfile;

5、备份归档日志：cp备份之后的文件

！ls -lrt /home/oracle/arclog

！cp /home/oracle/arclog/* /home/oracle/hotbak/

6、志挖掘

SQL> sho parameter arch

SQL> ！ls -lrt /home/oracle/arclog

SQL> !生成挖掘机

exec dbms_logmnr.add_logfile('/home/oracle/arclog/1_1_800202044.dbf',dbms_logmnr.new);

exec dbms_logmnr.add_logfile('/home/oracle/arclog/1_2_800202044.dbf',dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile('/home/oracle/arclog/1_3_800202044.dbf',dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile('/home/oracle/arclog/1_4_800202044.dbf',dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile('/home/oracle/arclog/1_5_800202044.dbf',dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile('/home/oracle/arclog/1_6_800202044.dbf',dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile('/home/oracle/arclog/1_7_800202044.dbf',dbms_logmnr.addfile);

SQL>！开始挖掘 

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

SQL>！查找挖掘结果

SQL> select SCN,TIMESTAMP,TABLE_NAME,SQL_REDO from v$logmnr_contents where sql_redo like '%drop table%';

       SCN TIMESTAMP TABLE_NAME

---------- --------- --------------------------------

SQL_REDO

--------------------------------------------------------------------------------

    877732 24-NOV-12 TT

drop table scott.tt purge;

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

建新的实例

1、cp $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/inittest.ora

修改abcu  dump路径

修改控制文件路径

修改log_archive_dest_10 归档日志路径

增加db_unique_name='test'

2|、启动数据库到mount

startup pfile='$ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/inittest.ora';

3、rname 改数据文件路径

select 'alter database rename file ' ||chr(39)|| name ||chr(39) ||' to ' ||chr(39)||'/home/oracle/t201/'||substr(name,instr(name,'/',-1,1)+1)||chr(39)||';' from v$datafile;

4、offline 非核心数据文件及无需恢复的表空间

alter database datafile '/home/oracle/test/tbs01.dbf' offline for drop;

5、恢复到挖掘到SCN

SQL> recover database using backup controlfile until change 877732;

6、alter database open resetlogs;

7、导出原删除的表

exp userid=scott/tiger tables=emp01 file='/u01/emp01.dmp' log='/u01/emp01.log'

8、导入表到原库

imp userid=scott/tiger tables=emp01 file='/u01/emp01.dmp' log='/u01/emp01.log'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451781/viewspace-749923/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451781/viewspace-749923/