然后删除oracle用户,oracle删除用户后的恢复测试

[oracle@psd backup]$ cd /oracle/fast_recovery_area/PSD/archivelog/

[oracle@psd archivelog]$ cd 2015_03_17/

[oracle@psd 2015_03_17]$ ll

total 10248

-rw-r-----. 1 oracle oinstall 7795200 Mar 17 11:41 o1_mf_1_16_bjh8mbov_.arc

-rw-r-----. 1 oracle oinstall 2048 Mar 17 11:43 o1_mf_1_17_bjh8qp80_.arc

-rw-r-----. 1 oracle oinstall 2674688 Mar 17 12:03 o1_mf_1_18_bjh9wyqj_.arc

-rw-r-----. 1 oracle oinstall 1024 Mar 17 12:03 o1_mf_1_19_bjh9x0hy_.arc

-rw-r-----. 1 oracle oinstall 9728 Mar 17 12:03 o1_mf_1_20_bjh9x3xb_.arc

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_16_bjh8mbov_.arc',OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_17_bjh8qp80_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_18_bjh9wyqj_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> col sql_redo format a50

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

SQL> select sql_redo,scn,start_scn,TIMESTAMP,START_TIMESTAMP from V$LOGMNR_CONTENTS where lower(sql_redo) like \'%drop%\' and lower(sql_redo) like \'%scott%\';

SQL_REDO                             SCN START_SCN TIMESTAMP     START_TIMESTAMP

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

drop table scott.emp1 AS "BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" ;1272611   2015-03-16 22:10:29

drop table "SCOTT"."BIN$EWNzFGZja8TgU5F3qMDpoQ==$0" purge;1278724   2015-03-17 11:46:13

drop table "SCOTT"."BIN$EWOQLk80bF3gU5F3qMBi4g==$0" purge;1278746   2015-03-17 11:46:14

drop table "SCOTT"."BIN$EWNb3bwua9TgU5F3qMA7BA==$0" purge;1278770   2015-03-17 11:46:14

drop table "SCOTT"."BIN$EWNb3bwva9TgU5F3qMA7BA==$0" purge;1278791   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWNzFGZka8TgU5F3qMDpoQ==$0" purge;1278813   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWOU7d6fbGPgU5F3qMA5vw==$0" purge;1278834   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWOQLk81bF3gU5F3qMBi4g==$0" purge;1278856   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWOniXMLbHvgU5F3qMAS1g==$0" purge;1278878   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWOniXMMbHvgU5F3qMAS1g==$0" purge;1278900   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWOniXMNbHvgU5F3qMAS1g==$0" purge;1278921   2015-03-17 11:46:15

SQL_REDO    SCN  START_SCN TIMESTAMP       START_TIMESTAMP

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

drop table "SCOTT"."BIN$EWOniXMObHvgU5F3qMAS1g==$0" purge;1278943   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWPg+H8lbNLgU5F3qMCj4g==$0" purge;1278965   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWPg+H8mbNLgU5F3qMCj4g==$0" purge;1278986   2015-03-17 11:46:15

drop table "SCOTT"."BIN$EWPg+H8nbNLgU5F3qMCj4g==$0" purge;1279008   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWTPDfF1bmPgU5F3qMBohA==$0" purge;1279030   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWTPDfF2bmPgU5F3qMBohA==$0" purge;1279052   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWTPDfF3bmPgU5F3qMBohA==$0" purge;1279074   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWTSV1AibnLgU5F3qMCKBg==$0" purge;1279096   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWTaumLtbn7gU5F3qMAgNQ==$0" purge;1279118   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWTdffDnboXgU5F3qMCDZg==$0" purge;1279140   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWTdffDoboXgU5F3qMCDZg==$0" purge;1279162   2015-03-17 11:46:16

SQL_REDO    SCN  START_SCN TIMESTAMP       START_TIMESTAMP

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

drop table "SCOTT"."BIN$EWTdffDpboXgU5F3qMCDZg==$0" purge;1279184   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWVlDuUgb2jgU5F3qMAyEQ==$0" purge;1279206   2015-03-17 11:46:16

drop table "SCOTT"."BIN$EWi11zOecYPgU5F3qMAYuA==$0" purge;1279228   2015-03-17 11:46:17

drop table "SCOTT"."BIN$EWjjuPdrc7fgU5F3qMD+ag==$0" purge;1279250   2015-03-17 11:46:17

drop table "SCOTT"."BIN$EWjqnThDc77gU5F3qMC90A==$0" purge;1279272   2015-03-17 11:46:17

drop table "SCOTT"."BIN$EWjqnThEc77gU5F3qMC90A==$0" purge;1279294   2015-03-17 11:46:17

drop table "SCOTT"."BIN$EWlYdSbqdmHgU5F3qMBLJg==$0" purge;1279314   2015-03-17 11:46:17

drop table "SCOTT"."BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" purge;1279336   2015-03-17 11:46:17

drop table "SCOTT"."T2" cascade constraints purge force;1279359   2015-03-17 11:46:22

drop table "SCOTT"."T1" cascade constraints purge force;1279379   2015-03-17 11:46:22

drop table "SCOTT"."DEPT1" cascade constraints purge force;1279401   2015-03-17 11:46:22

SQL_REDO    SCN  START_SCN TIMESTAMP       START_TIMESTAMP

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

drop table "SCOTT"."SALGRADE" cascade constraints purge forc1279424   2015-03-17 11:46:22

e;

drop table "SCOTT"."BONUS" cascade constraints purge force;1279447   2015-03-17 11:46:22

drop table "SCOTT"."EMP" cascade constraints purge force;1279459   2015-03-17 11:46:23

drop table "SCOTT"."DEPT" cascade constraints purge force;1279497   2015-03-17 11:46:23

drop procedure "SCOTT"."P_EMP1";1279533   2015-03-17 11:46:23

drop procedure "SCOTT"."P_INSERT";1279555   2015-03-17 11:46:24

drop procedure "SCOTT"."P_INSERT_T";1279573   2015-03-17 11:46:24

drop user scott cascade;1279609   2015-03-17 11:46:40

41 rows selected.

SQL>