oracle删除用户后的恢复测试

用到2个机子
主节点 192.168.119.145 hostname=psd
辅助节点192.168.119.146 hostname=test
要求有rman备份

方法一：如果库上只有这一个业务用户，就可以直接用rman 做全库的时间点恢复。
方法二：如果有多个用户，大致思路，在psd节点上备份数据库，然后删除用户，之后将备份恢复到test节点上，然后将test节点的用户数据逻辑导出，最后导入到psd节点。

方法二的测试

确认数据库是归档模式，首先在scott用户下创建一个t1表，并插入数据

1. [oracle@psd ~]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 10:39:53 2015
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
11. 
    
12. SQL> archive log list
    
13. Database log mode     Archive Mode
    
14. Automatic archival     Enabled
    
15. Archive destination     USE_DB_RECOVERY_FILE_DEST
    
16. Oldest online log sequence 14
    
17. Next log sequence to archive 16
    
18. Current log sequence     16
    
19. SQL>
    
20. SQL> show parameter db_re
    
21. 
    
22. NAME                 TYPE     VALUE
    
23. ------------------------------------ ----------- ------------------------------
    
24. db_recovery_file_dest         string     /oracle/fast_recovery_area
    
25. db_recovery_file_dest_size     big integer 4122M
    
26. db_recycle_cache_size         big integer 0
    
27. SQL>
    
28. SQL> conn scott/scott
    
29. Connected.
    
30. SQL> create table t1 (id number);
    
31. 
    
32. Table created.
    
33. 
    
34. SQL> begin
    
35.   2 for i in 1..5 loop
    
36.   3 insert into t1 values (i);
    
37.   4 end loop;
    
38.   5 end;
    
39.   6 /
    
40. 
    
41. PL/SQL procedure successfully completed.
    
42. 
    
43. SQL> commit;
    
44. 
    
45. Commit complete.
    
46. 
    
47. SQL> select * from t1;
    
48. 
    
49.     ID
    
50. ----------
    
51.      1
    
52.      2
    
53.      3
    
54.      4
    
55.      5
    
56. SQL>
    
57. SQL> exit
    
58. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
59. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
60. [oracle@psd backup]$

接下来开始备份数据库，备份完在向t1表插入数据，然后创建t2表，之后删掉scott用户

1. [oracle@psd backup]$ rman target /
   
2. 
   
3. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 11:38:55 2015
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. connected to target database: PSD (DBID=2007794869)
   
8. 
   
9. RMAN> backup database format '/home/oracle/backup/psd_%U.bak' plus archivelog format '/home/oracle/backup/arch_%U.bak';
   
10. 
    
11. 
    
12. Starting backup at 17-MAR-15
    
13. current log archived
    
14. using target database control file instead of recovery catalog
    
15. allocated channel: ORA_DISK_1
    
16. channel ORA_DISK_1: SID=41 device type=DISK
    
17. channel ORA_DISK_1: starting archived log backup set
    
18. channel ORA_DISK_1: specifying archived log(s) in backup set
    
19. input archived log thread=1 sequence=12 RECID=11 STAMP=873219765
    
20. input archived log thread=1 sequence=13 RECID=12 STAMP=874489166
    
21. input archived log thread=1 sequence=14 RECID=13 STAMP=874489173
    
22. input archived log thread=1 sequence=15 RECID=14 STAMP=874533680
    
23. input archived log thread=1 sequence=16 RECID=15 STAMP=874582892
    
24. channel ORA_DISK_1: starting piece 1 at 17-MAR-15
    
25. channel ORA_DISK_1: finished piece 1 at 17-MAR-15
    
26. piece handle=/home/oracle/backup/arch_02q224re_1_1.bak tag=TAG20150317T114134 comment=NONE
    
27. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
    
28. Finished backup at 17-MAR-15
    
29. 
    
30. Starting backup at 17-MAR-15
    
31. using channel ORA_DISK_1
    
32. channel ORA_DISK_1: starting full datafile backup set
    
33. channel ORA_DISK_1: specifying datafile(s) in backup set
    
34. input datafile file number=00001 name=/oracle/oradata/psd/system01.dbf
    
35. input datafile file number=00002 name=/oracle/oradata/psd/sysaux01.dbf
    
36. input datafile file number=00003 name=/oracle/oradata/psd/undotbs01.dbf
    
37. input datafile file number=00004 name=/oracle/oradata/psd/users01.dbf
    
38. channel ORA_DISK_1: starting piece 1 at 17-MAR-15
    
39. channel ORA_DISK_1: finished piece 1 at 17-MAR-15
    
40. piece handle=/home/oracle/backup/psd_03q224s8_1_1.bak tag=TAG20150317T114200 comment=NONE
    
41. channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
    
42. channel ORA_DISK_1: starting full datafile backup set
    
43. channel ORA_DISK_1: specifying datafile(s) in backup set
    
44. including current control file in backup set
    
45. including current SPFILE in backup set
    
46. channel ORA_DISK_1: starting piece 1 at 17-MAR-15
    
47. channel ORA_DISK_1: finished piece 1 at 17-MAR-15
    
48. piece handle=/home/oracle/backup/psd_04q224vi_1_1.bak tag=TAG20150317T114200 comment=NONE
    
49. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    
50. Finished backup at 17-MAR-15
    
51. 
    
52. Starting backup at 17-MAR-15
    
53. current log archived
    
54. using channel ORA_DISK_1
    
55. channel ORA_DISK_1: starting archived log backup set
    
56. channel ORA_DISK_1: specifying archived log(s) in backup set
    
57. input archived log thread=1 sequence=17 RECID=16 STAMP=874583030
    
58. channel ORA_DISK_1: starting piece 1 at 17-MAR-15
    
59. channel ORA_DISK_1: finished piece 1 at 17-MAR-15
    
60. piece handle=/home/oracle/backup/arch_05q224vm_1_1.bak tag=TAG20150317T114350 comment=NONE
    
61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    
62. Finished backup at 17-MAR-15
    
63. 
    
64. RMAN>
    
65. RMAN> exit
    
66. 
    
67. 
    
68. Recovery Manager complete.
    
69. [oracle@psd backup]$ sqlplus /nolog
    
70. 
    
71. SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 11:44:27 2015
    
72. 
    
73. Copyright (c) 1982, 2011, Oracle. All rights reserved.
    
74. 
    
75. SQL> conn scott/scott
    
76. Connected.
    
77. SQL> begin
    
78.   2 for i in 6..10 loop
    
79.   3 insert into t1 values (i);
    
80.   4 end loop;
    
81.   5 end;
    
82.   6 /
    
83. 
    
84. PL/SQL procedure successfully completed.
    
85. 
    
86. SQL> commit;
    
87. 
    
88. Commit complete.
    
89. 
    
90. SQL> select * from t1;
    
91. 
    
92.     ID
    
93. ----------
    
94.      1
    
95.      2
    
96.      3
    
97.      4
    
98.      5
    
99.      6
    
100.      7
     
101.      8
     
102.      9
     
103.     10
     
104. 
     
105. 10 rows selected.
     
106. 
     
107. SQL> create table t2 as select * from t1;
     
108. 
     
109. Table created.
     
110. 
     
111. SQL> conn /as sysdba
     
112. Connected.
     
113. SQL> drop user scott cascade;
     
114. 
     
115. User dropped.

现在scott用户已经被删掉了，要为恢复做准备，我们查看pfile文件，把相关的目录在辅助节点（192.168.119.146）都创建好，下面标红的目录

1. SQL> create pfile from spfile;
   
2. 
   
3. File created.
   
4. 
   
5. SQL> exit
   
6. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
7. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
8. [oracle@psd backup]$ cd $ORACLE_HOME/dbs
   
9. [oracle@psd dbs]$ cat initpsd.ora
   
10. psd.__db_cache_size=96468992
    
11. psd.__java_pool_size=4194304
    
12. psd.__large_pool_size=4194304
    
13. psd.__oracle_base='/oracle'#ORACLE_BASE set from environment
    
14. psd.__pga_aggregate_target=142606336
    
15. psd.__sga_target=272629760
    
16. psd.__shared_io_pool_size=0
    
17. psd.__shared_pool_size=159383552
    
18. psd.__streams_pool_size=0
    
19. *.audit_file_dest='/oracle/admin/psd/adump'
    
20. *.audit_trail='db'
    
21. *.compatible='11.2.0.0.0'
    
22. *.control_files='/oracle/oradata/psd/control01.ctl','/oracle/fast_recovery_area/psd/control02.ctl'
23. *.db_block_size=8192
    
24. *.db_domain=''
    
25. *.db_name='psd'
    
26. *.db_recovery_file_dest='/oracle/fast_recovery_area'
    
27. *.db_recovery_file_dest_size=4322230272
    
28. *.diagnostic_dest='/oracle'
    
29. *.dispatchers='(PROTOCOL=TCP) (SERVICE=psdXDB)'
    
30. *.memory_target=414187520
    
31. *.open_cursors=300
    
32. *.processes=150
    
33. *.remote_login_passwordfile='EXCLUSIVE'
    
34. *.undo_tablespace='UNDOTBS1'

把刚刚的rman备份、pfile文件以及密码文件拷贝到192.168.119.146上，然后我们就要确定drop scott的具体时间，用logminer来搞定

1. [oracle@psd backup]$ cd /oracle/fast_recovery_area/PSD/archivelog/
   
2. [oracle@psd archivelog]$ cd 2015_03_17/
   
3. [oracle@psd 2015_03_17]$ ll
   
4. total 10248
   
5. -rw-r-----. 1 oracle oinstall 7795200 Mar 17 11:41 o1_mf_1_16_bjh8mbov_.arc
   
6. -rw-r-----. 1 oracle oinstall 2048 Mar 17 11:43 o1_mf_1_17_bjh8qp80_.arc
   
7. -rw-r-----. 1 oracle oinstall 2674688 Mar 17 12:03 o1_mf_1_18_bjh9wyqj_.arc
   
8. -rw-r-----. 1 oracle oinstall 1024 Mar 17 12:03 o1_mf_1_19_bjh9x0hy_.arc
   
9. -rw-r-----. 1 oracle oinstall 9728 Mar 17 12:03 o1_mf_1_20_bjh9x3xb_.arc
   
10. 
    
11. 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);
    
12. 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);
    
13. 
    
14. PL/SQL procedure successfully completed.
    
15. 
    
16. SQL>
    
17. PL/SQL procedure successfully completed.
    
18. 
    
19. 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);
    
20. 
    
21. PL/SQL procedure successfully completed.
    
22. 
    
23. SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
    
24. 
    
25. PL/SQL procedure successfully completed.
    
26. 
    
27. SQL> col sql_redo format a50
    
28. SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
    
29. 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%\';
    
30. 
    
31. SQL_REDO                             SCN START_SCN TIMESTAMP     START_TIMESTAMP
    
32. ------------------------------------------------------------ ---------- ---------- ------------------- -------------------
    
33. 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 forc 1279424   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
    
    
34. 
    
35. 41 rows selected.
    
36. 
    
37. SQL>

从这里我们可以判断，drop用户的时间是 2015-03-17 11:46:13，我的测试机器上没有多少归档，但是在生产环境归档会很多，想确定drop的时间就得多看几个归档日志了。
确定时间，我们就开始在辅助节点做恢复操作

1. [oracle@test backup]$ rman target /
   
2. 
   
3. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 12:27:21 2015
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. connected to target database (not started)
   
8. 
   
9. RMAN> startup nomount;
   
10. 
    
11. Oracle instance started
    
12. 
    
13. Total System Global Area 413372416 bytes
    
14. 
    
15. Fixed Size 2228904 bytes
    
16. Variable Size 310381912 bytes
    
17. Database Buffers 96468992 bytes
    
18. Redo Buffers 4292608 bytes
    
19. 
    
20. RMAN> restore controlfile from '/home/oracle/backup/psd_04q224vi_1_1.bak';
    
21. 
    
22. Starting restore at 17-MAR-15
    
23. using target database control file instead of recovery catalog
    
24. allocated channel: ORA_DISK_1
    
25. channel ORA_DISK_1: SID=19 device type=DISK
    
26. 
    
27. channel ORA_DISK_1: restoring control file
    
28. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    
29. output file name=/oracle/oradata/psd/control01.ctl
    
30. output file name=/oracle/fast_recovery_area/psd/control02.ctl
    
31. Finished restore at 17-MAR-15
    
32. 
    
33. RMAN> alter database mount;
    
34. 
    
35. database mounted
    
36. released channel: ORA_DISK_1
    
37. 
    
38. RMAN> restore database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
    
39. 
    
40. Starting restore at 17-MAR-15
    
41. using channel ORA_DISK_1
    
42. 
    
43. channel ORA_DISK_1: starting datafile backup set restore
    
44. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    
45. channel ORA_DISK_1: restoring datafile 00001 to /oracle/oradata/psd/system01.dbf
    
46. channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/psd/sysaux01.dbf
    
47. channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/psd/undotbs01.dbf
    
48. channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/psd/users01.dbf
    
49. channel ORA_DISK_1: reading from backup piece /home/oracle/backup/psd_03q224s8_1_1.bak
    
50. channel ORA_DISK_1: piece handle=/home/oracle/backup/psd_03q224s8_1_1.bak tag=TAG20150317T114200
    
51. channel ORA_DISK_1: restored backup piece 1
    
52. channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
    
53. Finished restore at 17-MAR-15
    
54. 
    
55. RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
    
56. 
    
57. Starting recover at 17-MAR-15
    
58. using channel ORA_DISK_1
    
59. 
    
60. starting media recovery
    
61. 
    
62. unable to find archived log
    
63. archived log thread=1 sequence=17
    
64. RMAN-00571: ===========================================================
    
65. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    
66. RMAN-00571: ===========================================================
    
67. RMAN-03002: failure of recover command at 03/17/2015 12:35:48
    
68. RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 1278496

这是报了个错无，说17号归档找不到，我们把主库的17号归档考过来，我考到了/home/oracle/backup 下面了，然后继续恢复

1. RMAN> catalog start with '/home/oracle/backup';
   
2. 
   
3. searching for all files that match the pattern /home/oracle/backup
   
4. 
   
5. List of Files Unknown to the Database
   
6. =====================================
   
7. File Name: /home/oracle/backup/psd_04q224vi_1_1.bak
   
8. File Name: /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
   
9. File Name: /home/oracle/backup/o1_mf_1_16_bjh8mbov_.arc
   
10. File Name: /home/oracle/backup/o1_mf_1_20_bjh9x3xb_.arc
    
11. File Name: /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
    
12. File Name: /home/oracle/backup/o1_mf_1_19_bjh9x0hy_.arc
    
13. File Name: /home/oracle/backup/arch_05q224vm_1_1.bak
    
14. 
    
15. Do you really want to catalog the above files (enter YES or NO)? yes
    
16. cataloging files...
    
17. cataloging done
    
18. 
    
19. List of Cataloged Files
    
20. =======================
    
21. File Name: /home/oracle/backup/psd_04q224vi_1_1.bak
    
22. File Name: /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
    
23. File Name: /home/oracle/backup/o1_mf_1_16_bjh8mbov_.arc
    
24. File Name: /home/oracle/backup/o1_mf_1_20_bjh9x3xb_.arc
    
25. File Name: /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
    
26. File Name: /home/oracle/backup/o1_mf_1_19_bjh9x0hy_.arc
    
27. File Name: /home/oracle/backup/arch_05q224vm_1_1.bak
    
28. 
    
29. RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
    
30. 
    
31. Starting recover at 17-MAR-15
    
32. using channel ORA_DISK_1
    
33. 
    
34. starting media recovery
    
35. 
    
36. archived log for thread 1 with sequence 17 is already on disk as file /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
    
37. archived log for thread 1 with sequence 18 is already on disk as file /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
    
38. archived log file name=/home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc thread=1 sequence=17
    
39. archived log file name=/home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc thread=1 sequence=18
    
40. media recovery complete, elapsed time: 00:00:01
    
41. Finished recover at 17-MAR-15
    
42. 
    
43. RMAN> alter database open;
    
44. 
    
45. RMAN-00571: ===========================================================
    
46. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    
47. RMAN-00571: ===========================================================
    
48. RMAN-03002: failure of alter db command at 03/17/2015 12:38:58
    
49. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    
50. 
    
51. RMAN> alter database open resetlogs;
    
52. 
    
53. database opened

这时候数据库已经打开，我们看看scott用户下的数据

点击(此处)折叠或打开

1. SQL> conn scott/scott
   
2. Connected.
   
3. SQL> select * from tab;
   
4. 
   
5. TNAME             TABTYPE    CLUSTERID
   
6. ------------------------------ ------- ----------
   
7. BIN$EWNb3bwua9TgU5F3qMA7BA==$0 TABLE
   
8. BIN$EWNb3bwva9TgU5F3qMA7BA==$0 TABLE
   
9. BIN$EWNzFGZja8TgU5F3qMDpoQ==$0 TABLE
   
10. BIN$EWNzFGZka8TgU5F3qMDpoQ==$0 TABLE
    
11. BIN$EWOQLk80bF3gU5F3qMBi4g==$0 TABLE
    
12. BIN$EWOQLk81bF3gU5F3qMBi4g==$0 TABLE
    
13. BIN$EWOU7d6fbGPgU5F3qMA5vw==$0 TABLE
    
14. BIN$EWOniXMLbHvgU5F3qMAS1g==$0 TABLE
    
15. BIN$EWOniXMMbHvgU5F3qMAS1g==$0 TABLE
    
16. BIN$EWOniXMNbHvgU5F3qMAS1g==$0 TABLE
    
17. BIN$EWOniXMObHvgU5F3qMAS1g==$0 TABLE
    
18. 
    
19. TNAME             TABTYPE    CLUSTERID
    
20. ------------------------------ ------- ----------
    
21. BIN$EWPg+H8lbNLgU5F3qMCj4g==$0 TABLE
    
22. BIN$EWPg+H8mbNLgU5F3qMCj4g==$0 TABLE
    
23. BIN$EWPg+H8nbNLgU5F3qMCj4g==$0 TABLE
    
24. BIN$EWTPDfF1bmPgU5F3qMBohA==$0 TABLE
    
25. BIN$EWTPDfF2bmPgU5F3qMBohA==$0 TABLE
    
26. BIN$EWTPDfF3bmPgU5F3qMBohA==$0 TABLE
    
27. BIN$EWTSV1AibnLgU5F3qMCKBg==$0 TABLE
    
28. BIN$EWTaumLtbn7gU5F3qMAgNQ==$0 TABLE
    
29. BIN$EWTdffDnboXgU5F3qMCDZg==$0 TABLE
    
30. BIN$EWTdffDoboXgU5F3qMCDZg==$0 TABLE
    
31. BIN$EWTdffDpboXgU5F3qMCDZg==$0 TABLE
    
32. 
    
33. TNAME             TABTYPE    CLUSTERID
    
34. ------------------------------ ------- ----------
    
35. BIN$EWVlDuUgb2jgU5F3qMAyEQ==$0 TABLE
    
36. BIN$EWi11zOecYPgU5F3qMAYuA==$0 TABLE
    
37. BIN$EWjjuPdrc7fgU5F3qMD+ag==$0 TABLE
    
38. BIN$EWjqnThDc77gU5F3qMC90A==$0 TABLE
    
39. BIN$EWjqnThEc77gU5F3qMC90A==$0 TABLE
    
40. BIN$EWlYdSbqdmHgU5F3qMBLJg==$0 TABLE
    
41. BIN$EWlpmtpQdoXgU5F3qMAiMw==$0 TABLE
    
42. BONUS             TABLE
    
43. DEPT             TABLE
    
44. DEPT1             TABLE
    
45. EMP             TABLE
    
46. 
    
47. TNAME             TABTYPE    CLUSTERID
    
48. ------------------------------ ------- ----------
    
49. SALGRADE         TABLE
    
50. T1             TABLE
    
51. T2             TABLE
    
52. 
    
53. 36 rows selected.
    
54. 
    
55. SQL> select * from t1;
    
56. 
    
57.     ID
    
58. ----------
    
59.      1
    
60.      2
    
61.      3
    
62.      4
    
63.      5
    
64.      6
    
65.      7
    
66.      8
    
67.      9
    
68.     10
    
69. 
    
70. 10 rows selected.
    
71. 
    
72. SQL> select * from t2;
    
73. 
    
74.     ID
    
75. ----------
    
76.      1
    
77.      2
    
78.      3
    
79.      4
    
80.      5
    
81.      6
    
82.      7
    
83.      8
    
84.      9
    
85.     10
    
86. 
    
87. 10 rows selected.
    
88. 
    
89. SQL>

发现scott回收站里有很多垃圾表，可以无视，我们发现t1,t2的数据都正常，这时候只要把scott的数据导出来再导入到主库就好了

1. [oracle@test ~]$ expdp scott/scott dumpfile=scott.dmp directory=dir schemas=scott;
3. Export: Release 11.2.0.3.0 - Production on Tue Mar 17 13:17:05 2015
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
8. With the Partitioning, OLAP, Data Mining and Real Application Testing options
9. Starting \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\": scott/******** dumpfile=scott.dmp directory=dir schemas=scott
10. Estimate in progress using BLOCKS method...
11. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
12. Total estimation using BLOCKS method: 384 KB
13. Processing object type SCHEMA_EXPORT/USER
14. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15. Processing object type SCHEMA_EXPORT/ROLE_GRANT
16. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
17. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
18. Processing object type SCHEMA_EXPORT/TABLE/TABLE
19. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
20. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
21. Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
22. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
23. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
24. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
25. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
26. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
27. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
28. . . exported \"SCOTT\".\"DEPT\" 5.937 KB 4 rows
29. . . exported \"SCOTT\".\"DEPT1\" 5.515 KB 4 rows
30. . . exported \"SCOTT\".\"EMP\" 8.570 KB 14 rows
31. . . exported \"SCOTT\".\"SALGRADE\" 5.867 KB 5 rows
32. . . exported \"SCOTT\".\"T1\" 5.070 KB 10 rows
33. . . exported \"SCOTT\".\"T2\" 5.070 KB 10 rows
34. . . exported \"SCOTT\".\"BONUS\" 0 KB 0 rows
35. Master table \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded
36. ******************************************************************************
37. Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
38. /home/oracle/scott.dmp
39. Job \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at 13:18:34
41. [oracle@test ~]$ scp scott.dmp psd:/home/oracle
42. The authenticity of host \'psd (192.168.119.145)\' can\'t be established.
43. RSA key fingerprint is 67:e2:04:8e:aa:42:f1:97:c6:14:69:36:ef:86:2e:b0.
44. Are you sure you want to continue connecting (yes/no)? yes
45. Warning: Permanently added \'psd,192.168.119.145\' (RSA) to the list of known hosts.
46. oracle@psd\'s password:
47. scott.dmp 100% 288KB 288.0KB/s 00:00
48. [oracle@test ~]$
50. [oracle@psd ~]$ impdp system/oracle dumpfile=scott.dmp directory=dir ;
52. Import: Release 11.2.0.3.0 - Production on Tue Mar 17 13:20:05 2015
54. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
56. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
57. With the Partitioning, OLAP, Data Mining and Real Application Testing options
58. Master table \"SYSTEM\".\"SYS_IMPORT_FULL_01\" successfully loaded/unloaded
59. Starting \"SYSTEM\".\"SYS_IMPORT_FULL_01\": system/******** dumpfile=scott.dmp directory=dir
60. Processing object type SCHEMA_EXPORT/USER
61. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
62. Processing object type SCHEMA_EXPORT/ROLE_GRANT
63. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
64. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
65. Processing object type SCHEMA_EXPORT/TABLE/TABLE
66. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
67. . . imported \"SCOTT\".\"DEPT\" 5.937 KB 4 rows
68. . . imported \"SCOTT\".\"DEPT1\" 5.515 KB 4 rows
69. . . imported \"SCOTT\".\"EMP\" 8.570 KB 14 rows
70. . . imported \"SCOTT\".\"SALGRADE\" 5.867 KB 5 rows
71. . . imported \"SCOTT\".\"T1\" 5.070 KB 10 rows
72. . . imported \"SCOTT\".\"T2\" 5.070 KB 10 rows
73. . . imported \"SCOTT\".\"BONUS\" 0 KB 0 rows
74. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
75. Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
76. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
77. ORA-39082: Object type ALTER_PROCEDURE:\"SCOTT\".\"P_INSERT\" created with compilation warnings
78. ORA-39082: Object type ALTER_PROCEDURE:\"SCOTT\".\"P_INSERT_T\" created with compilation warnings
79. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
80. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
81. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
82. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
83. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
84. Job \"SYSTEM\".\"SYS_IMPORT_FULL_01\" completed with 2 error(s) at 13:20:33
86. [oracle@psd ~]$ sqlplus scott/scott
88. SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 13:20:47 2015
90. Copyright (c) 1982, 2011, Oracle. All rights reserved.
93. Connected to:
94. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
95. With the Partitioning, OLAP, Data Mining and Real Application Testing options
97. SQL> select * from t1;
99. ID
100. ----------
101. 1
102. 2
103. 3
104. 4
105. 5
106. 6
107. 7
108. 8
109. 9
110. 10
112. 10 rows selected.
114. SQL> select * from t2;
116. ID
117. ----------
118. 1
119. 2
120. 3
121. 4
122. 5
123. 6
124. 7
125. 8
126. 9
127. 10
129. 10 rows selected.

用户误删恢复完成。

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

转载于:http://blog.itpub.net/29339009/viewspace-1462709/