oracle刪除使用者後的恢復測試

goodlatch發表於2015-03-17
原文網址 : http://blog.itpub.net/29339009/viewspace-1462709/
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

  3. SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 10:39:53 2015

  5. Copyright (c) 1982, 2011, Oracle. All rights reserved.


  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

  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

  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);

  32. Table created.

  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 /

  41. PL/SQL procedure successfully completed.

  43. SQL> commit;

  45. Commit complete.

  47. SQL> select * from t1;

  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 /

  3. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 11:38:55 2015

  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

  7. connected to target database: PSD (DBID=2007794869)

  9. RMAN> backup database format '/home/oracle/backup/psd_%U.bak' plus archivelog format '/home/oracle/backup/arch_%U.bak';


  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

  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

  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

  64. RMAN>
  65. RMAN> exit


  68. Recovery Manager complete.
  69. [oracle@psd backup]$ sqlplus /nolog

  71. SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 11:44:27 2015

  73. Copyright (c) 1982, 2011, Oracle. All rights reserved.

  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 /

  84. PL/SQL procedure successfully completed.

  86. SQL> commit;

  88. Commit complete.

  90. SQL> select * from t1;

  92.     ID
  93. ----------
  94.      1
  95.      2
  96.      3
  97.      4
  98.      5
  99.      6
  100.      7
  101.      8
  102.      9
  103.     10

  105. 10 rows selected.

  107. SQL> create table t2 as select * from t1;

  109. Table created.

  111. SQL> conn /as sysdba
  112. Connected.
  113. SQL> drop user scott cascade;

  115. User dropped.

現在scott使用者已經被刪掉了，要為恢復做準備，我們檢視pfile檔案，把相關的目錄在輔助節點（192.168.119.146）都建立好，下面標紅的目錄

  1. SQL> create pfile from spfile;

  3. File created.

  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

  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);

  14. PL/SQL procedure successfully completed.

  16. SQL>
  17. PL/SQL procedure successfully completed.

  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);

  21. PL/SQL procedure successfully completed.

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

  25. PL/SQL procedure successfully completed.

  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%\';

  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


  35. 41 rows selected.

  37. SQL>

從這裡我們可以判斷，drop使用者的時間是 2015-03-17 11:46:13，我的測試機器上沒有多少歸檔，但是在生產環境歸檔會很多，想確定drop的時間就得多看幾個歸檔日誌了。
確定時間，我們就開始在輔助節點做恢復操作


  1. [oracle@test backup]$ rman target /

  3. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 12:27:21 2015

  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

  7. connected to target database (not started)

  9. RMAN> startup nomount;

  11. Oracle instance started

  13. Total System Global Area 413372416 bytes

  15. Fixed Size 2228904 bytes
  16. Variable Size 310381912 bytes
  17. Database Buffers 96468992 bytes
  18. Redo Buffers 4292608 bytes

  20. RMAN> restore controlfile from '/home/oracle/backup/psd_04q224vi_1_1.bak';

  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

  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

  33. RMAN> alter database mount;

  35. database mounted
  36. released channel: ORA_DISK_1

  38. RMAN> restore database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";

  40. Starting restore at 17-MAR-15
  41. using channel ORA_DISK_1

  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

  55. RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";

  57. Starting recover at 17-MAR-15
  58. using channel ORA_DISK_1

  60. starting media recovery

  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';

  3. searching for all files that match the pattern /home/oracle/backup

  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

  15. Do you really want to catalog the above files (enter YES or NO)? yes
  16. cataloging files...
  17. cataloging done

  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

  29. RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";

  31. Starting recover at 17-MAR-15
  32. using channel ORA_DISK_1

  34. starting media recovery

  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

  43. RMAN> alter database open;

  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

  51. RMAN> alter database open resetlogs;

  53. database opened

這時候資料庫已經開啟，我們看看scott使用者下的資料


點選(此處)摺疊或開啟

  1. SQL> conn scott/scott
  2. Connected.
  3. SQL> select * from tab;

  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

  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

  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

  47. TNAME             TABTYPE    CLUSTERID
  48. ------------------------------ ------- ----------
  49. SALGRADE         TABLE
  50. T1             TABLE
  51. T2             TABLE

  53. 36 rows selected.

  55. SQL> select * from t1;

  57.     ID
  58. ----------
  59.      1
  60.      2
  61.      3
  62.      4
  63.      5
  64.      6
  65.      7
  66.      8
  67.      9
  68.     10

  70. 10 rows selected.

  72. SQL> select * from t2;

  74.     ID
  75. ----------
  76.      1
  77.      2
  78.      3
  79.      4
  80.      5
  81.      6
  82.      7
  83.      8
  84.      9
  85.     10

  87. 10 rows selected.

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章